Workload Repository (WR) periodically collects data from performance views. It persists and displays the collected active session history (ASH) data, statistics, wait events, and SQL execution information.
Query historical session performance
Query the
oceanbase.CDB_WR_ACTIVE_SESSION_HISTORYview for persisted ASH data of all tenants.obclient [oceanbase]> SELECT * FROM oceanbase.CDB_WR_ACTIVE_SESSION_HISTORY limit 1;The return result is as follows:
+------------+-----------+---------+-----------+----------+-----------+------------+----------------------------+---------+--------------+---------------+----------------------------------+------------------------------------+----------+-------------+------+------+------+------------------+------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+--------+--------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+ | cluster_id | tenant_id | snap_id | svr_ip | svr_port | smaple_id | session_id | SAMPLE_TIME | USER_ID | SESSION_TYPE | SESSION_STATE | SQL_ID | TRACE_ID | EVENT_NO | TIME_WAITED | P1 | P2 | P3 | SQL_PLAN_LINE_ID | TIME_MODEL | IN_PARSE | IN_PL_PARSE | IN_PLAN_CACHE | IN_SQL_OPTIMIZE | IN_SQL_EXECUTION | IN_PX_EXECUTION | IN_SEQUENCE_LOAD | MODULE | ACTION | CLIENT_ID | BACKTRACE | PLAN_ID | +------------+-----------+---------+-----------+----------+-----------+------------+----------------------------+---------+--------------+---------------+----------------------------------+------------------------------------+----------+-------------+------+------+------+------------------+------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+--------+--------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+ | 1 | 1004 | 4373 | xx.xx.xx.xx | 49700 | 1 | 3221487621 | 2023-08-06 11:42:54.970554 | 200001 | 0 | 0 | B9866D3A1A68153AD58193F60CCD40AA | YC225645868B4-0005F816208F16B9-0-0 | 20 | 89267 | 592 | 378 | 0 | NULL | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | NULL | NULL | NULL | 0x11882b55 0x113d68b3 0x113d680a 0x42a89e6 0x455534d 0x538cf24 0x538bfd8 0x538bcc2 0x52a3673 0xa35d2a0 0xa35b8f0 0x92a2537 0x4313d3b 0x43125e2 0x4244faf 0x422e878 0x435a80d 0x4201726 0x41fd8dc 0x41fb111 0x71a8caa 0x41facb3 0x71a7a21 0x41f9c9a 0x71a80fc 0x | 1453 | +------------+-----------+---------+-----------+----------+-----------+------------+----------------------------+---------+--------------+---------------+----------------------------------+------------------------------------+----------+-------------+------+------+------+------------------+------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+--------+--------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+The following table describes the fields.
Field Description CLUSTER_ID The ID of the cluster. TENANT_ID The ID of the tenant. SNAP_ID The ID of the snapshot. SVR_IP The IP address of the server. SVR_PORT The port of the server. SAMPLE_ID The sampling ID. SESSION_ID The ID of the sampled session. SAMPLE_TIME The sampling time. USER_ID The user ID of the sampled session. SESSION_TYPE The type of the session. Valid values: N: FOREGROUND, indicating a foreground session, namely, a user session.Y: BACKGROUND, indicating a background session.
SESSION_STATE The status of the session when it was being sampled. Valid values: EVENT_NO == 0: "ON CPU" indicates that no wait event occurs and that the SQL logic is being executed.EVENT_NO ≠ 0: "WAITTING" indicates that a wait event occurs. The wait details are provided in theEVENTfield.
SQL_ID The SQL ID. TRACE_ID The trace ID. EVENT_NO The internal number of the wait event, which is used for JOIN queries with other tables. TIME_WAITED The total wait time of the wait event. Unit: us. P1 The value of the first parameter of the wait event. P2 The value of the second parameter of the wait event. P3 The value of the third parameter of the wait event. SQL_PLAN_LINE_ID The ID of the SQL operator during sampling. If no operator is used, the value is NULL.TIME_MODEL The time model. IN_PARSE Indicates whether the current session is performing SQL parsing during sampling. IN_PL_PARSE Indicates whether the current session is performing SQL PL parsing during sampling. IN_PLAN_CACHE Indicates whether the current session is using the plan cache during sampling. IN_SQL_OPTIMIZE Indicates whether the current session is performing SQL tuning during sampling. IN_SQL_EXECUTION Indicates whether the current session is executing an SQL statement during sampling. IN_PX_EXECUTION Indicates whether the current session is performing parallel SQL execution during sampling. If the current session is in this state, it must be also in the IN_SQL_EXECUTION state. IN_SEQUENCE_LOAD Indicates whether the current session is generating values for an auto-increment column or a sequence during sampling. IN_COMMITTING Indicates whether the current sampling point is during the transaction commit phase. IN_STORAGE_READ Indicates whether the current sampling point is in the storage read phase. IN_STORAGE_WRITE Indicates whether the current sampling point is in the storage write phase. IN_REMOTE_DAS_EXECUTION Indicates whether the current sampling point is in the remote execution phase of the data access service (DAS). MODULE The MODULE value recorded in the current session when being sampled. ACTION The ACTION value recorded in the current session when being sampled. CLIENT_ID The CLIENT_ID value recorded in the current session when being sampled. BACKTRACE An auxiliary debugging field, which records the code call stack when an event occurs. PLAN_ID The plan ID of the sampled SQL statement in the plan cache, which is used to associate the sampling point with the plan. For more information about the
oceanbase.CDB_WR_ACTIVE_SESSION_HISTORYview, see oceanbase.CDB_WR_ACTIVE_SESSION_HISTORY.Query the
oceanbase.DBA_WR_ACTIVE_SESSION_HISTORYview for persisted ASH data of the current tenant.obclient [oceanbase]> SELECT * FROM oceanbase.DBA_WR_ACTIVE_SESSION_HISTORY limit 1;The return result is as follows:
+------------+-----------+---------+-----------+----------+-----------+------------+----------------------------+---------+--------------+---------------+----------------------------------+------------------------------------+----------+-------------+------+------+------+------------------+------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+--------+--------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+ | cluster_id | tenant_id | snap_id | svr_ip | svr_port | smaple_id | session_id | SAMPLE_TIME | USER_ID | SESSION_TYPE | SESSION_STATE | SQL_ID | TRACE_ID | EVENT_NO | TIME_WAITED | P1 | P2 | P3 | SQL_PLAN_LINE_ID | TIME_MODEL | IN_PARSE | IN_PL_PARSE | IN_PLAN_CACHE | IN_SQL_OPTIMIZE | IN_SQL_EXECUTION | IN_PX_EXECUTION | IN_SEQUENCE_LOAD | MODULE | ACTION | CLIENT_ID | BACKTRACE | PLAN_ID | +------------+-----------+---------+-----------+----------+-----------+------------+----------------------------+---------+--------------+---------------+----------------------------------+------------------------------------+----------+-------------+------+------+------+------------------+------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+--------+--------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+ | 1 | 1004 | 4373 | xx.xx.xx.xx | 49700 | 1 | 3221487621 | 2023-08-06 11:42:54.970554 | 200001 | 0 | 0 | B9866D3A1A68153AD58193F60CCD40AA | YC225645868B4-0005F816208F16B9-0-0 | 20 | 89267 | 592 | 378 | 0 | NULL | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | NULL | NULL | NULL | 0x11882b55 0x113d68b3 0x113d680a 0x42a89e6 0x455534d 0x538cf24 0x538bfd8 0x538bcc2 0x52a3673 0xa35d2a0 0xa35b8f0 0x92a2537 0x4313d3b 0x43125e2 0x4244faf 0x422e878 0x435a80d 0x4201726 0x41fd8dc 0x41fb111 0x71a8caa 0x41facb3 0x71a7a21 0x41f9c9a 0x71a80fc 0x | 1453 | +------------+-----------+---------+-----------+----------+-----------+------------+----------------------------+---------+--------------+---------------+----------------------------------+------------------------------------+----------+-------------+------+------+------+------------------+------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+--------+--------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+The following table describes the fields.
Field Description CLUSTER_ID The ID of the cluster. TENANT_ID The ID of the tenant. SNAP_ID The ID of the snapshot. SVR_IP The IP address of the server. SVR_PORT The port of the server. SAMPLE_ID The sampling ID. SESSION_ID The ID of the sampled session. SAMPLE_TIME The sampling time. USER_ID The user ID of the sampled session. SESSION_TYPE The type of the session. Valid values: N: FOREGROUND, indicating a foreground session, namely, a user session.Y: BACKGROUND, indicating a background session.
SESSION_STATE The status of the session when it was being sampled. Valid values: EVENT_NO == 0: "ON CPU" indicates that no wait event occurs and that the SQL logic is being executed.EVENT_NO ≠ 0: "WAITTING" indicates that a wait event occurs. The wait details are provided in theEVENTfield.
SQL_ID The SQL ID. TRACE_ID The trace ID. EVENT_NO The internal number of the wait event, which is used for JOIN queries with other tables. TIME_WAITED The total wait time of the wait event. Unit: us. P1 The value of the first parameter of the wait event. P2 The value of the second parameter of the wait event. P3 The value of the third parameter of the wait event. SQL_PLAN_LINE_ID The ID of the SQL operator during sampling. If no operator is used, the value is NULL.TIME_MODEL The time model. IN_PARSE Indicates whether the current session is performing SQL parsing during sampling. IN_PL_PARSE Indicates whether the current session is performing SQL PL parsing during sampling. IN_PLAN_CACHE Indicates whether the current session is using the plan cache during sampling. IN_SQL_OPTIMIZE Indicates whether the current session is performing SQL tuning during sampling. IN_SQL_EXECUTION Indicates whether the current session is executing an SQL statement during sampling. IN_PX_EXECUTION Indicates whether the current session is performing parallel SQL execution during sampling. If the current session is in this state, it must be also in the IN_SQL_EXECUTION state. IN_SEQUENCE_LOAD Indicates whether the current session is generating values for an auto-increment column or a sequence during sampling. IN_COMMITTING Indicates whether the current sampling point is during the transaction commit phase. null Indicates whether the current sampling point is in the storage read phase. IN_STORAGE_WRITE Indicates whether the current sampling point is in the storage write phase. IN_REMOTE_DAS_EXECUTION Indicates whether the current sampling point is in the remote execution phase of the data access service (DAS). MODULE The MODULE value recorded in the current session when being sampled. ACTION The ACTION value recorded in the current session when being sampled. CLIENT_ID The CLIENT_ID value recorded in the current session when being sampled. BACKTRACE An auxiliary debugging field, which records the code call stack when an event occurs. PLAN_ID The plan ID of the sampled SQL statement in the plan cache, which is used to associate the sampling point with the plan. For more information about the
DBA_WR_ACTIVE_SESSION_HISTORYview, see oceanbase.DBA_WR_ACTIVE_SESSION_HISTORY and DBA_WR_ACTIVE_SESSION_HISTORY.
View session statistical items
Query the
oceanbase.CDB_WR_STATNAMEview for names of system statistical items of all tenants.obclient [oceanbase]> SELECT * FROM oceanbase.CDB_WR_STATNAME limit 1;The return result is as follows:
+------------+-----------+---------+---------------+ | CLUSTER_ID | TENANT_ID | STAT_ID | STAT_NAME | +------------+-----------+---------+---------------+ | 4000 | 1 | 10000 | rpc packet in | +------------+-----------+---------+---------------+ 1 row in set (0.001 sec)The following table describes the fields.
Field Description CLUSTER_ID The ID of the cluster. TENANT_ID The ID of the tenant. STAT_ID The ID of the statistical item. STAT_NAME The name of the statistical item. For more information about the
oceanbase.CDB_WR_STATNAMEview, see oceanbase.CDB_WR_STATNAME.Query the
oceanbase.DBA_WR_STATNAMEview for names of system statistical items of the current tenant.obclient [oceanbase]> SELECT * FROM oceanbase.DBA_WR_STATNAME limit 1;The return result is as follows:
+------------+-----------+---------+---------------+ | CLUSTER_ID | TENANT_ID | STAT_ID | STAT_NAME | +------------+-----------+---------+---------------+ | 4000 | 1 | 10000 | rpc packet in | +------------+-----------+---------+---------------+ 1 row in set (0.004 sec)For more information about the
DBA_WR_STATNAMEview, see oceanbase.DBA_WR_STATNAME and DBA_WR_STATNAME.Query the
oceanbase.CDB_WR_SYSSTATview for details about system statistical items of all tenants.obclient [oceanbase]> SELECT * FROM oceanbase.CDB_WR_SYSSTAT limit 1;The return result is as follows:
+------------+-----------+---------+----------------+----------+---------+--------+ | CLUSTER_ID | TENANT_ID | SNAP_ID | SVR_IP | SVR_PORT | STAT_ID | VALUE | +------------+-----------+---------+----------------+----------+---------+--------+ | 4000 | 1 | 114 | xx.xx.xx.xx | 2882 | 10000 | 228856 | +------------+-----------+---------+----------------+----------+---------+--------+ 1 row in set (0.036 sec)The following table describes the fields.
Field Description CLUSTER_ID The ID of the cluster. TENANT_ID The ID of the tenant. SNAP_ID The ID of the snapshot. SVR_IP The IP address of the server. SVR_PORT The port of the server. STAT_ID The ID of the statistical item. VALUE The value of the statistical item. For more information about the
oceanbase.CDB_WR_SYSSTATview, see oceanbase.CDB_WR_SYSSTAT.Query the
oceanbase.DBA_WR_SYSSTATview for details about system statistical items of the current tenant.obclient [oceanbase]> SELECT * FROM oceanbase.DBA_WR_SYSSTAT limit 1;The return result is as follows:
+------------+-----------+---------+----------------+----------+---------+--------+ | CLUSTER_ID | TENANT_ID | SNAP_ID | SVR_IP | SVR_PORT | STAT_ID | VALUE | +------------+-----------+---------+----------------+----------+---------+--------+ | 4000 | 1 | 114 | xx.xx.xx.xx | 2882 | 10000 | 228856 | +------------+-----------+---------+----------------+----------+---------+--------+ 1 row in set (0.026 sec)The following table describes the fields.
Field Description CLUSTER_ID The ID of the cluster. TENANT_ID The ID of the tenant. SNAP_ID The ID of the snapshot. SVR_IP The IP address of the server. SVR_PORT The port of the server. STAT_ID The ID of the statistical item. VALUE The value of the statistical item. For more information about the
DBA_WR_STATNAMEview, see oceanbase.DBA_WR_SYSSTAT and DBA_WR_SYSSTAT.