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 data
Query the
oceanbase.CDB_WR_ACTIVE_SESSION_HISTORYview for persisted ASH data of all tenants.+------------+-----------+---------+----------------+----------+-----------+---------------+----------------------------+---------+--------------+---------------+--------+------------------------------------+----------+----------+-------------+------+------+------+------------------+------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+---------------+-----------------+------------------+-------------------------+----------------+-----------------------------+--------+--------+-----------+-----------+---------+---------------+-------------------+------------------+------------------+----------------------+--------------------+-----------------------+---------------------------+-----------------------------+-----------------+---------------------+-----------------------+ | CLUSTER_ID | TENANT_ID | SNAP_ID | SVR_IP | SVR_PORT | SAMPLE_ID | SESSION_ID | SAMPLE_TIME | USER_ID | SESSION_TYPE | SESSION_STATE | SQL_ID | TRACE_ID | EVENT_NO | EVENT_ID | 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 | IN_COMMITTING | IN_STORAGE_READ | IN_STORAGE_WRITE | IN_REMOTE_DAS_EXECUTION | IN_FILTER_ROWS | PROGRAM | MODULE | ACTION | CLIENT_ID | BACKTRACE | PLAN_ID | TM_DELTA_TIME | TM_DELTA_CPU_TIME | TM_DELTA_DB_TIME | TOP_LEVEL_SQL_ID | IN_PLSQL_COMPILATION | IN_PLSQL_EXECUTION | PLSQL_ENTRY_OBJECT_ID | PLSQL_ENTRY_SUBPROGRAM_ID | PLSQL_ENTRY_SUBPROGRAM_NAME | PLSQL_OBJECT_ID | PLSQL_SUBPROGRAM_ID | PLSQL_SUBPROGRAM_NAME | +------------+-----------+---------+----------------+----------+-----------+---------------+----------------------------+---------+--------------+---------------+--------+------------------------------------+----------+----------+-------------+------+------+------+------------------+------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+---------------+-----------------+------------------+-------------------------+----------------+-----------------------------+--------+--------+-----------+-----------+---------+---------------+-------------------+------------------+------------------+----------------------+--------------------+-----------------------+---------------------------+-----------------------------+-----------------+---------------------+-----------------------+ | 40001 | 1 | 1 | xx.xx.xx.xx | 28824 | 90 | 1748051689472 | 2024-01-05 14:32:42.111942 | 0 | 1 | ON CPU | | Y70980BA1CCFB-00060E2D0029C856-0-0 | 0 | -1 | 0 | 0 | 0 | 0 | NULL | 0 | N | N | N | N | N | N | N | N | N | N | N | N | RPC PROCESS (T1_L0_G0)_ 257 | NULL | NULL | NULL | NULL | 0 | 0 | 0 | 0 | NULL | N | N | NULL | NULL | NULL | NULL | NULL | NULL | +------------+-----------+---------+----------------+----------+-----------+---------------+----------------------------+---------+--------------+---------------+--------+------------------------------------+----------+----------+-------------+------+------+------+------------------+------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+---------------+-----------------+------------------+-------------------------+----------------+-----------------------------+--------+--------+-----------+-----------+---------+---------------+-------------------+------------------+------------------+----------------------+--------------------+-----------------------+---------------------------+-----------------------------+-----------------+---------------------+-----------------------+ 1 row in set (0.097 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. 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. TM_DELTA_TIME The time interval for calculating time model, in microseconds. TM_DELTA_CPU_TIME The amount of time spent on the CPU during the past TM_DELTA_TIMEperiod.TM_DELTA_DB_TIME The amount of time spent in database calls during the past TM_DELTA_TIMEperiod.TOP_LEVEL_SQL_ID The top-level SQL ID. IN_PLSQL_COMPILATION The current PL compilation status: Y/N. IN_PLSQL_EXECUTION The current PL execution status: Y/N. PLSQL_ENTRY_OBJECT_ID The object ID of the top-level PL. PLSQL_ENTRY_SUBPROGRAM_ID The subprogram ID of the top-level PL. PLSQL_ENTRY_SUBPROGRAM_NAME The subprogram name of the top-level PL. PLSQL_OBJECT_ID The object ID of the PL that is being exeucted currently. PLSQL_SUBPROGRAM_ID The subprogram ID of the PL that is being exeucted currently. PLSQL_SUBPROGRAM_NAME The subprogram name of the PL that is being exeucted currently. 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 | SAMPLE_ID | SESSION_ID | SAMPLE_TIME | USER_ID | SESSION_TYPE | SESSION_STATE | SQL_ID | TRACE_ID | EVENT_NO | EVENT_ID | 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 | IN_COMMITTING | IN_STORAGE_READ | IN_STORAGE_WRITE | IN_REMOTE_DAS_EXECUTION | IN_FILTER_ROWS | PROGRAM | MODULE | ACTION | CLIENT_ID | BACKTRACE | PLAN_ID | TM_DELTA_TIME | TM_DELTA_CPU_TIME | TM_DELTA_DB_TIME | TOP_LEVEL_SQL_ID | IN_PLSQL_COMPILATION | IN_PLSQL_EXECUTION | PLSQL_ENTRY_OBJECT_ID | PLSQL_ENTRY_SUBPROGRAM_ID | PLSQL_ENTRY_SUBPROGRAM_NAME | PLSQL_OBJECT_ID | PLSQL_SUBPROGRAM_ID | PLSQL_SUBPROGRAM_NAME | +------------+-----------+---------+----------------+----------+-----------+---------------+----------------------------+---------+--------------+---------------+--------+-------------------------+----------+----------+-------------+------+------+------+------------------+------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+---------------+-----------------+------------------+-------------------------+----------------+---------------+--------+--------+-----------+-----------+---------+---------------+-------------------+------------------+------------------+----------------------+--------------------+-----------------------+---------------------------+-----------------------------+-----------------+---------------------+-----------------------+ | 40001 | 1002 | 1 | xx.xx.xx.xx | 28824 | 640 | 2830383448064 | 2024-01-05 14:34:28.277178 | 0 | 1 | ON CPU | | Y0-0000000000000000-0-0 | 0 | -1 | 0 | 0 | 0 | 0 | NULL | 0 | N | N | N | N | N | N | N | N | N | N | N | N | T1002_ObTimer | NULL | NULL | NULL | NULL | 0 | 825 | 825 | 825 | NULL | N | N | NULL | NULL | NULL | NULL | NULL | NULL | +------------+-----------+---------+----------------+----------+-----------+---------------+----------------------------+---------+--------------+---------------+--------+-------------------------+----------+----------+-------------+------+------+------+------------------+------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+---------------+-----------------+------------------+-------------------------+----------------+---------------+--------+--------+-----------+-----------+---------+---------------+-------------------+------------------+------------------+----------------------+--------------------+-----------------------+---------------------------+-----------------------------+-----------------+---------------------+-----------------------+ 1 row in set (0.095 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. 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. TM_DELTA_TIME The time interval for calculating time model, in microseconds. TM_DELTA_CPU_TIME The amount of time spent on the CPU during the past TM_DELTA_TIMEperiod.TM_DELTA_DB_TIME The amount of time spent in database calls during the past TM_DELTA_TIMEperiod.TOP_LEVEL_SQL_ID The top-level SQL ID. IN_PLSQL_COMPILATION The current PL compilation status: Y/N. IN_PLSQL_EXECUTION The current PL execution status: Y/N. PLSQL_ENTRY_OBJECT_ID The object ID of the top-level PL. PLSQL_ENTRY_SUBPROGRAM_ID The subprogram ID of the top-level PL. PLSQL_ENTRY_SUBPROGRAM_NAME The subprogram name of the top-level PL. PLSQL_OBJECT_ID The object ID of the PL that is being exeucted currently. PLSQL_SUBPROGRAM_ID The subprogram ID of the PL that is being exeucted currently. PLSQL_SUBPROGRAM_NAME The subprogram name of the PL that is being exeucted currently. 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.