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.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 | 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, which indicates a foreground session, namely, a user session.Y: BACKGROUND, which indicates a background session.
SESSION_STATE The status of the session when 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, in μs. 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 optimization 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 in 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 the time model, in μs. TM_DELTA_CPU_TIME The CPU time consumed within TM_DELTA_TIME.TM_DELTA_DB_TIME The time consumed by database calls within TM_DELTA_TIME.TOP_LEVEL_SQL_ID The SQL ID of the top-level statement. IN_PLSQL_COMPILATION The current PL compilation status. Valid values: Y and N. IN_PLSQL_EXECUTION The current PL execution status. Valid values: Y and N. PLSQL_ENTRY_OBJECT_ID The object ID of the top-level PL subprogram. PLSQL_ENTRY_SUBPROGRAM_ID The subprogram ID of the top-level PL subprogram. PLSQL_ENTRY_SUBPROGRAM_NAME The subprogram name of the top-level PL subprogram. PLSQL_OBJECT_ID The object ID of the currently executing PL subprogram. PLSQL_SUBPROGRAM_ID The subprogram ID of the currently executing PL subprogram. PLSQL_SUBPROGRAM_NAME The subprogram name of the currently executing PL subprogram. 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, which indicates a foreground session, namely, a user session.Y: BACKGROUND, which indicates a background session.
SESSION_STATE The status of the session when 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, in μs. 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 optimization 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 in 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 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 the time model, in μs. TM_DELTA_CPU_TIME The CPU time consumed within TM_DELTA_TIME.TM_DELTA_DB_TIME The time consumed by database calls within TM_DELTA_TIME.TOP_LEVEL_SQL_ID The SQL ID of the top-level statement. IN_PLSQL_COMPILATION The current PL compilation status. Valid values: Y and N. IN_PLSQL_EXECUTION The current PL execution status. Valid values: Y and N. PLSQL_ENTRY_OBJECT_ID The object ID of the top-level PL subprogram. PLSQL_ENTRY_SUBPROGRAM_ID The subprogram ID of the top-level PL subprogram. PLSQL_ENTRY_SUBPROGRAM_NAME The subprogram name of the top-level PL subprogram. PLSQL_OBJECT_ID The object ID of the currently executing PL subprogram. PLSQL_SUBPROGRAM_ID The subprogram ID of the currently executing PL subprogram. PLSQL_SUBPROGRAM_NAME The subprogram name of the currently executing PL subprogram. For more information about the
DBA_WR_ACTIVE_SESSION_HISTORYview, see oceanbase.DBA_WR_ACTIVE_SESSION_HISTORY and DBA_WR_ACTIVE_SESSION_HISTORY.Query the
GV$OB_ACTIVE_SESSION_HISTORYview for ASH data of all tenants.obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_ACTIVE_SESSION_HISTORY limit 2;The query result is as follows:
+----------------+----------+-----------+---------------------+--------+---------+--------------+--------------+---------------+--------+---------+-------------------------+-------+----------+----------+--------+------+--------+------+--------+------+------------+---------------+-------------+------------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+---------------+-----------------+------------------+-------------------------+----------------+-------------+--------+--------+-----------+-----------+---------------+-------------------+------------------+------------------+----------------------+--------------------+-----------------------+---------------------------+-----------------------------+-----------------+---------------------+-----------------------+ | SVR_IP | SVR_PORT | SAMPLE_ID | SAMPLE_TIME | CON_ID | USER_ID | SESSION_ID | SESSION_TYPE | SESSION_STATE | SQL_ID | PLAN_ID | TRACE_ID | EVENT | EVENT_NO | EVENT_ID | P1TEXT | P1 | P2TEXT | P2 | P3TEXT | P3 | WAIT_CLASS | WAIT_CLASS_ID | TIME_WAITED | SQL_PLAN_LINE_ID | 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 | 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 | +----------------+----------+-----------+---------------------+--------+---------+--------------+--------------+---------------+--------+---------+-------------------------+-------+----------+----------+--------+------+--------+------+--------+------+------------+---------------+-------------+------------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+---------------+-----------------+------------------+-------------------------+----------------+-------------+--------+--------+-----------+-----------+---------------+-------------------+------------------+------------------+----------------------+--------------------+-----------------------+---------------------------+-----------------------------+-----------------+---------------------+-----------------------+ | xx.xx.xx.xx | 2882 | 3905827 | 2024-01-03 10:24:22 | 500 | 0 | 141733920768 | BACKGROUND | ON CPU | | 0 | Y0-0000000000000000-0-0 | | 0 | NULL | | 0 | | 0 | | 0 | OTHER | 100 | 0 | NULL | N | N | N | N | N | N | N | N | N | N | N | N | KVCacheWash | NULL | NULL | NULL | NULL | 393102 | 393102 | 393102 | NULL | N | N | NULL | NULL | NULL | NULL | NULL | NULL | | xx.xx.xx.xx | 2882 | 3905828 | 2024-01-03 10:24:22 | 500 | 0 | 34359738368 | BACKGROUND | ON CPU | | 0 | Y0-0000000000000000-0-0 | | 0 | NULL | | 0 | | 0 | | 0 | OTHER | 100 | 0 | NULL | N | N | N | N | N | N | N | N | N | N | N | N | ConfigMgr | NULL | NULL | NULL | NULL | 1000097 | 997585 | 1000097 | NULL | N | N | NULL | NULL | NULL | NULL | NULL | NULL | +----------------+----------+-----------+---------------------+--------+---------+--------------+--------------+---------------+--------+---------+-------------------------+-------+----------+----------+--------+------+--------+------+--------+------+------------+---------------+-------------+------------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+---------------+-----------------+------------------+-------------------------+----------------+-------------+--------+--------+-----------+-----------+---------------+-------------------+------------------+------------------+----------------------+--------------------+-----------------------+---------------------------+-----------------------------+-----------------+---------------------+-----------------------+ 2 rows in set (0.113 sec)The following table describes the fields.
Field Description SVR_IP The IP address of the server to which the sample belongs. SVR_PORT The port number of the server to which the sample belongs. SAMPLE_ID The unique ID of the sample. SAMPLE_TIME The sampling time. CON_ID The ID of the tenant. USER_ID The ID of the user whose session is sampled. SESSION_ID The ID of the sampled session. SESSION_TYPE The session type. Valid values: FOREGROUND: foreground session, that is, user session.BACKGROUND: background session, which is not supported for now.
SESSION_STATE The session status. Valid values: ON CPU: The SQL logic is being executed without waiting.WAITING: A wait event occurs. The wait details are provided in theEVENTfield.
SQL_ID The SQL ID. 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. TRACE_ID The trace ID of the operator. EVENT The description of the wait event. EVENT_NO The internal number of the wait event, which is used for join queries with other tables. EVENT_ID The ID of the current wait event. P1TEXT The name of the first parameter of the wait event. The parameter name varies with the event. P1 The value of the first parameter of the wait event. P2TEXT The name of the second parameter of the wait event. The parameter name varies with the event. P2 The value of the second parameter of the wait event. P3TEXT The name of the third parameter of the wait event. The parameter name varies with the event. P3 The value of the third parameter of the wait event. WAIT_CLASS The class of the wait event. WAIT_CLASS_ID The ID of the class of the wait event, which is used for join queries with other tables. TIME_WAITED The total wait time of the wait event, in μs. SQL_PLAN_LINE_ID The ID of the SQL operator in the SQL plan when the session is being sampled. IN_PARSE Indicates whether SQL parsing is being performed in the session during sampling. IN_PL_PARSE Indicates whether SQL PL parsing is being performed in the session during sampling. IN_PLAN_CACHE Indicates whether plan caching is being performed in the session during sampling. IN_SQL_OPTIMIZE Indicates whether SQL parsing and tuning are being performed in the session during sampling. IN_SQL_EXECUTION Indicates whether an SQL statement is being executed in the session during sampling. IN_PX_EXECUTION Indicates whether parallel SQL execution is being performed in the session is during sampling. If the session is in this state, it must also be in the state of SQL statement executing. IN_SEQUENCE_LOAD Indicates whether values are being generated for an auto-increment column or a sequence in the session during sampling. IN_COMMITTING Indicates whether the current sampling point is in 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 DAS. IN_FILTER_ROWS Indicates whether the current sampling point is in the execution phase of storage push-down. PROGRAM The name of the program being executed at the current sampling point. - Background thread: the thread name, such as
observerandWrTimer. - Foreground thread:
user@client_ip (thread_name)for a common requestINNER SQL REMOTE EXEC (thread_name)forinner_sqlremote executionDAS REMOTE EXEC (thread_name)for DAS remote execution
MODULE The MODULEvalue recorded during session sampling.ACTION The ACTIONvalue recorded during session sampling. The field value is always NULL.CLIENT_ID The CLIENT_IDvalue recorded during session sampling. The field value is always NULL.BACKTRACE An auxiliary debugging field, which records the code call stack when an event occurs. The field value is always NULL. TM_DELTA_TIME The time interval for calculating the time model, in μs. TM_DELTA_CPU_TIME The CPU time consumed within TM_DELTA_TIME.TM_DELTA_DB_TIME The time consumed by database calls within TM_DELTA_TIME.TOP_LEVEL_SQL_ID The SQL ID of the top-level statement. IN_PLSQL_COMPILATION The current PL compilation status. Valid values: Y and N. IN_PLSQL_EXECUTION The current PL execution status. Valid values: Y and N. PLSQL_ENTRY_OBJECT_ID The object ID of the top-level PL subprogram. PLSQL_ENTRY_SUBPROGRAM_ID The subprogram ID of the top-level PL subprogram. PLSQL_ENTRY_SUBPROGRAM_NAME The subprogram name of the top-level PL subprogram. PLSQL_OBJECT_ID The object ID of the currently executing PL subprogram. PLSQL_SUBPROGRAM_ID The subprogram ID of the currently executing PL subprogram. PLSQL_SUBPROGRAM_NAME The subprogram name of the currently executing PL subprogram. For more information about the
GV$OB_ACTIVE_SESSION_HISTORYview, see GV$OB_ACTIVE_SESSION_HISTORY.Query the
V$OB_ACTIVE_SESSION_HISTORYview for ASH data of the current tenant.obclient [oceanbase]> SELECT * FROM oceanbase.V$OB_ACTIVE_SESSION_HISTORY limit 2;The query result is as follows:
+----------------+----------+-----------+---------------------+--------+---------+--------------+--------------+---------------+--------+---------+-------------------------+-------+----------+----------+--------+------+--------+------+--------+------+------------+---------------+-------------+------------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+---------------+-----------------+------------------+-------------------------+----------------+-------------+--------+--------+-----------+-----------+---------------+-------------------+------------------+------------------+----------------------+--------------------+-----------------------+---------------------------+-----------------------------+-----------------+---------------------+-----------------------+ | SVR_IP | SVR_PORT | SAMPLE_ID | SAMPLE_TIME | CON_ID | USER_ID | SESSION_ID | SESSION_TYPE | SESSION_STATE | SQL_ID | PLAN_ID | TRACE_ID | EVENT | EVENT_NO | EVENT_ID | P1TEXT | P1 | P2TEXT | P2 | P3TEXT | P3 | WAIT_CLASS | WAIT_CLASS_ID | TIME_WAITED | SQL_PLAN_LINE_ID | 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 | 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 | +----------------+----------+-----------+---------------------+--------+---------+--------------+--------------+---------------+--------+---------+-------------------------+-------+----------+----------+--------+------+--------+------+--------+------+------------+---------------+-------------+------------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+---------------+-----------------+------------------+-------------------------+----------------+-------------+--------+--------+-----------+-----------+---------------+-------------------+------------------+------------------+----------------------+--------------------+-----------------------+---------------------------+-----------------------------+-----------------+---------------------+-----------------------+ | xx.xx.xx.xx | 2882 | 3906741 | 2024-01-03 10:26:12 | 500 | 0 | 141733920768 | BACKGROUND | ON CPU | | 0 | Y0-0000000000000000-0-0 | | 0 | NULL | | 0 | | 0 | | 0 | OTHER | 100 | 0 | NULL | N | N | N | N | N | N | N | N | N | N | N | N | KVCacheWash | NULL | NULL | NULL | NULL | 3336060 | 3336060 | 3336060 | NULL | N | N | NULL | NULL | NULL | NULL | NULL | NULL | | xx.xx.xx.xx | 2882 | 3906742 | 2024-01-03 10:26:12 | 500 | 0 | 34359738368 | BACKGROUND | ON CPU | | 0 | Y0-0000000000000000-0-0 | | 0 | NULL | | 0 | | 0 | | 0 | OTHER | 100 | 0 | NULL | N | N | N | N | N | N | N | N | N | N | N | N | ConfigMgr | NULL | NULL | NULL | NULL | 1007028 | 1004378 | 1007028 | NULL | N | N | NULL | NULL | NULL | NULL | NULL | NULL | +----------------+----------+-----------+---------------------+--------+---------+--------------+--------------+---------------+--------+---------+-------------------------+-------+----------+----------+--------+------+--------+------+--------+------+------------+---------------+-------------+------------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+---------------+-----------------+------------------+-------------------------+----------------+-------------+--------+--------+-----------+-----------+---------------+-------------------+------------------+------------------+----------------------+--------------------+-----------------------+---------------------------+-----------------------------+-----------------+---------------------+-----------------------+ 2 rows in set (0.137 sec)The following table describes the fields.
Field Description SVR_IP The IP address of the server to which the sample belongs. SVR_PORT The port number of the server to which the sample belongs. SAMPLE_ID The unique ID of the sample. SAMPLE_TIME The sampling time. CON_ID The ID of the tenant. USER_ID The ID of the user whose session is sampled. SESSION_ID The ID of the sampled session. SESSION_TYPE The session type. Valid values: FOREGROUND: foreground session, that is, user session.BACKGROUND: background session, which is not supported for now.
SESSION_STATE The session status. Valid values: ON CPU: The SQL logic is being executed without waiting.WAITING: A wait event occurs. The wait details are provided in theEVENTfield.
SQL_ID The SQL ID. 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. TRACE_ID The trace ID of the operator. EVENT The description of the wait event. EVENT_NO The internal number of the wait event, which is used for join queries with other tables. EVENT_ID The ID of the current wait event. P1TEXT The name of the first parameter of the wait event. The parameter name varies with the event. P1 The value of the first parameter of the wait event. P2TEXT The name of the second parameter of the wait event. The parameter name varies with the event. P2 The value of the second parameter of the wait event. P3TEXT The name of the third parameter of the wait event. The parameter name varies with the event. P3 The value of the third parameter of the wait event. WAIT_CLASS The class of the wait event. WAIT_CLASS_ID The ID of the class of the wait event, which is used for join queries with other tables. TIME_WAITED The total wait time of the wait event, in μs. SQL_PLAN_LINE_ID The ID of the SQL operator in the SQL plan when the session is being sampled. IN_PARSE Indicates whether SQL parsing is being performed in the session during sampling. IN_PL_PARSE Indicates whether SQL PL parsing is being performed in the session during sampling. IN_PLAN_CACHE Indicates whether plan caching is being performed in the session during sampling. IN_SQL_OPTIMIZE Indicates whether SQL parsing and tuning are being performed in the session during sampling. IN_SQL_EXECUTION Indicates whether an SQL statement is being executed in the session during sampling. IN_PX_EXECUTION Indicates whether parallel SQL execution is being performed in the session is during sampling. If the session is in this state, it must also be in the state of SQL statement executing. IN_SEQUENCE_LOAD Indicates whether values are being generated for an auto-increment column or a sequence in the session during sampling. IN_COMMITTING Indicates whether the current sampling point is in 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 DAS. IN_FILTER_ROWS Indicates whether the current sampling point is in the execution phase of storage push-down. PROGRAM The name of the program being executed at the current sampling point. - Background thread: the thread name, such as
observerandWrTimer. - Foreground thread:
user@client_ip (thread_name)for a common requestINNER SQL REMOTE EXEC (thread_name)forinner_sqlremote executionDAS REMOTE EXEC (thread_name)for DAS remote execution
MODULE The MODULEvalue recorded during session sampling.ACTION The ACTIONvalue recorded during session sampling. The field value is always NULL.CLIENT_ID The CLIENT_IDvalue recorded during session sampling. The field value is always NULL.BACKTRACE An auxiliary debugging field, which records the code call stack when an event occurs. The field value is always NULL. TM_DELTA_TIME The time interval for calculating the time model, in μs. TM_DELTA_CPU_TIME The CPU time consumed within TM_DELTA_TIME.TM_DELTA_DB_TIME The time consumed by database calls within TM_DELTA_TIME.TOP_LEVEL_SQL_ID The SQL ID of the top-level statement. IN_PLSQL_COMPILATION The current PL compilation status. Valid values: Y and N. IN_PLSQL_EXECUTION The current PL execution status. Valid values: Y and N. PLSQL_ENTRY_OBJECT_ID The object ID of the top-level PL subprogram. PLSQL_ENTRY_SUBPROGRAM_ID The subprogram ID of the top-level PL subprogram. PLSQL_ENTRY_SUBPROGRAM_NAME The subprogram name of the top-level PL subprogram. PLSQL_OBJECT_ID The object ID of the currently executing PL subprogram. PLSQL_SUBPROGRAM_ID The subprogram ID of the currently executing PL subprogram. PLSQL_SUBPROGRAM_NAME The subprogram name of the currently executing PL subprogram. For more information about the
V$OB_ACTIVE_SESSION_HISTORYview, see V$OB_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)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
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_SYSSTATview, see oceanbase.DBA_WR_SYSSTAT and DBA_WR_SYSSTAT.
View time model statistical items
Query the
GV$OB_SESS_TIME_MODELview for details about time model statistical items at the session level in the cluster.obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_SESS_TIME_MODEL LIMIT 10;The return result is as follows:
+---------------+-----------+----------------+----------+---------+--------------------+-------+ | SID | TENANT_ID | SVR_IP | SVR_PORT | STAT_ID | STAT_NAME | VALUE | +---------------+-----------+----------------+----------+---------+--------------------+-------+ | 3298534883328 | 1002 | 172.xx.xxx.xxx | 2882 | 200010 | non idle wait time | 0 | | 3298534883328 | 1002 | 172.xx.xxx.xxx | 2882 | 200011 | idle wait time | 0 | | 3848290697216 | 1001 | 172.xx.xxx.xxx | 2882 | 200010 | non idle wait time | 0 | | 3848290697216 | 1001 | 172.xx.xxx.xxx | 2882 | 200011 | idle wait time | 0 | | 3221659555 | 1 | 172.xx.xxx.xxx | 2882 | 200010 | non idle wait time | 3 | | 3221659555 | 1 | 172.xx.xxx.xxx | 2882 | 200011 | idle wait time | 0 | | 4398046511104 | 1001 | 172.xx.xxx.xxx | 2882 | 200010 | non idle wait time | 0 | | 4398046511104 | 1001 | 172.xx.xxx.xxx | 2882 | 200011 | idle wait time | 0 | | 4947802324992 | 1004 | 172.xx.xxx.xxx | 2882 | 200010 | non idle wait time | 0 | | 4947802324992 | 1004 | 172.xx.xxx.xxx | 2882 | 200011 | idle wait time | 0 | +---------------+-----------+----------------+----------+---------+--------------------+-------+ 10 rows in setThe following table describes the fields.
Field Description SID The session ID. TENANT_ID The ID of the tenant. SVR_IP The IP address of the server. SVR_PORT The port number of the server. STAT_ID The ID of the statistical item. STAT_NAME The name of the statistical item. VALUE The value of the statistical item. For more information about the
GV$OB_SESS_TIME_MODELview, see GV$OB_SESS_TIME_MODEL.Query the
V$OB_SESS_TIME_MODELview for details about time model statistical items at the session level on the current node.obclient [oceanbase]> SELECT * FROM oceanbase.V$OB_SESS_TIME_MODEL LIMIT 10;The query result is as follows:
+---------------+-----------+----------------+----------+---------+--------------------+-------+ | SID | TENANT_ID | SVR_IP | SVR_PORT | STAT_ID | STAT_NAME | VALUE | +---------------+-----------+----------------+----------+---------+--------------------+-------+ | 3298534883328 | 1002 | 172.xx.xxx.xxx | 2882 | 200010 | non idle wait time | 0 | | 3298534883328 | 1002 | 172.xx.xxx.xxx | 2882 | 200011 | idle wait time | 0 | | 3848290697216 | 1001 | 172.xx.xxx.xxx | 2882 | 200010 | non idle wait time | 0 | | 3848290697216 | 1001 | 172.xx.xxx.xxx | 2882 | 200011 | idle wait time | 0 | | 4398046511104 | 1001 | 172.xx.xxx.xxx | 2882 | 200010 | non idle wait time | 0 | | 4398046511104 | 1001 | 172.xx.xxx.xxx | 2882 | 200011 | idle wait time | 0 | | 3221667747 | 1 | 172.xx.xxx.xxx | 2882 | 200010 | non idle wait time | 0 | | 3221667747 | 1 | 172.xx.xxx.xxx | 2882 | 200011 | idle wait time | 0 | | 4947802324992 | 1004 | 172.xx.xxx.xxx | 2882 | 200010 | non idle wait time | 0 | | 4947802324992 | 1004 | 172.xx.xxx.xxx | 2882 | 200011 | idle wait time | 0 | +---------------+-----------+----------------+----------+---------+--------------------+-------+ 10 rows in setThe following table describes the fields.
Field Description SID The session ID. TENANT_ID The ID of the tenant. SVR_IP The IP address of the server. SVR_PORT The port number of the server. STAT_ID The ID of the statistical item. STAT_NAME The name of the statistical item. VALUE The value of the statistical item. For more information about the
V$OB_SESS_TIME_MODELview, see V$OB_SESS_TIME_MODEL.Query the
GV$OB_SYS_TIME_MODELview for details about time model statistical items at the tenant level in the cluster.obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_SYS_TIME_MODEL;The query result is as follows:
+-----------+----------------+----------+---------+-------------------------+------------+ | TENANT_ID | SVR_IP | SVR_PORT | STAT_ID | STAT_NAME | VALUE | +-----------+----------------+----------+---------+-------------------------+------------+ | 1002 | 172.xx.xxx.xxx | 2882 | 200001 | DB time | 6135115 | | 1002 | 172.xx.xxx.xxx | 2882 | 200002 | DB CPU | 5500079 | | 1002 | 172.xx.xxx.xxx | 2882 | 200005 | background elapsed time | 3398189713 | | 1002 | 172.xx.xxx.xxx | 2882 | 200006 | background cpu time | 3088552955 | | 1002 | 172.xx.xxx.xxx | 2882 | 200010 | non idle wait time | 635036 | | 1002 | 172.xx.xxx.xxx | 2882 | 200011 | idle wait time | 80414 | +-----------+----------------+----------+---------+-------------------------+------------+ 6 rows in setThe following table describes the fields.
Field Description TENANT_ID The ID of the tenant. SVR_IP The IP address of the server. SVR_PORT The port number of the server. STAT_ID The ID of the statistical item. STAT_NAME The name of the statistical item. VALUE The value of the statistical item. For more information about the
GV$OB_SYS_TIME_MODELview, see GV$OB_SYS_TIME_MODEL.Query the
V$OB_SYS_TIME_MODELview for details about time model statistical items at the tenant level on the current node.obclient [oceanbase]> SELECT * FROM oceanbase.V$OB_SYS_TIME_MODEL;The query result is as follows:
+-----------+----------------+----------+---------+-------------------------+------------+ | TENANT_ID | SVR_IP | SVR_PORT | STAT_ID | STAT_NAME | VALUE | +-----------+----------------+----------+---------+-------------------------+------------+ | 1002 | 172.xx.xxx.xxx | 2882 | 200001 | DB time | 6227784 | | 1002 | 172.xx.xxx.xxx | 2882 | 200002 | DB CPU | 5586418 | | 1002 | 172.xx.xxx.xxx | 2882 | 200005 | background elapsed time | 3399416601 | | 1002 | 172.xx.xxx.xxx | 2882 | 200006 | background cpu time | 3089779843 | | 1002 | 172.xx.xxx.xxx | 2882 | 200010 | non idle wait time | 641366 | | 1002 | 172.xx.xxx.xxx | 2882 | 200011 | idle wait time | 80414 | +-----------+----------------+----------+---------+-------------------------+------------+ 6 rows in setThe following table describes the fields.
Field Description TENANT_ID The ID of the tenant. SVR_IP The IP address of the server. SVR_PORT The port number of the server. STAT_ID The ID of the statistical item. STAT_NAME The name of the statistical item. VALUE The value of the statistical item. For more information about the
V$OB_SYS_TIME_MODELview, see V$OB_SYS_TIME_MODEL.Query the
oceanbase.CDB_WR_SYS_TIME_MODELview for the WR data of time model statistical items of all tenants.obclient [oceanbase]> SELECT * FROM oceanbase.CDB_WR_SYS_TIME_MODEL LIMIT 10;The query result is as follows:
+------------+-----------+---------+----------------+----------+---------+-------------------------+-----------+ | CLUSTER_ID | TENANT_ID | SNAP_ID | SVR_IP | SVR_PORT | STAT_ID | STAT_NAME | VALUE | +------------+-----------+---------+----------------+----------+---------+-------------------------+-----------+ | 10001 | 1002 | 2 | 172.xx.xxx.xxx | 2882 | 200001 | DB time | 34505 | | 10001 | 1002 | 1 | 172.xx.xxx.xxx | 2882 | 200001 | DB time | 34505 | | 10001 | 1002 | 2 | 172.xx.xxx.xxx | 2882 | 200002 | DB CPU | 1966 | | 10001 | 1002 | 1 | 172.xx.xxx.xxx | 2882 | 200002 | DB CPU | 1966 | | 10001 | 1002 | 2 | 172.xx.xxx.xxx | 2882 | 200005 | background elapsed time | 233110335 | | 10001 | 1002 | 1 | 172.xx.xxx.xxx | 2882 | 200005 | background elapsed time | 116267272 | | 10001 | 1002 | 2 | 172.xx.xxx.xxx | 2882 | 200006 | background cpu time | 209738761 | | 10001 | 1002 | 1 | 172.xx.xxx.xxx | 2882 | 200006 | background cpu time | 104674501 | | 10001 | 1002 | 1 | 172.xx.xxx.xxx | 2882 | 200010 | non idle wait time | 32539 | | 10001 | 1002 | 1 | 172.xx.xxx.xxx | 2882 | 200011 | idle wait time | 0 | +------------+-----------+---------+----------------+----------+---------+-------------------------+-----------+ 10 rows in setThe 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 number of the server. STAT_ID The ID of the statistical item. STAT_NAME The name of the statistical item. VALUE The value of the statistical item. For more information about the
oceanbase.CDB_WR_SYS_TIME_MODELview, see oceanbase.CDB_WR_SYS_TIME_MODEL.Query the
oceanbase.DBA_WR_SYS_TIME_MODELview for the WR data of time model statistical items of the current tenant.obclient [oceanbase]> SELECT * FROM oceanbase.DBA_WR_SYS_TIME_MODEL LIMIT 10;The query result is as follows:
+---------+----------------+----------+---------+-------------------------+-----------+ | SNAP_ID | SVR_IP | SVR_PORT | STAT_ID | STAT_NAME | VALUE | +---------+----------------+----------+---------+-------------------------+-----------+ | 2 | 172.xx.xxx.xxx | 2882 | 200001 | DB time | 34505 | | 1 | 172.xx.xxx.xxx | 2882 | 200001 | DB time | 34505 | | 2 | 172.xx.xxx.xxx | 2882 | 200002 | DB CPU | 1966 | | 1 | 172.xx.xxx.xxx | 2882 | 200002 | DB CPU | 1966 | | 2 | 172.xx.xxx.xxx | 2882 | 200005 | background elapsed time | 233110335 | | 1 | 172.xx.xxx.xxx | 2882 | 200005 | background elapsed time | 116267272 | | 2 | 172.xx.xxx.xxx | 2882 | 200006 | background cpu time | 209738761 | | 1 | 172.xx.xxx.xxx | 2882 | 200006 | background cpu time | 104674501 | | 1 | 172.xx.xxx.xxx | 2882 | 200010 | non idle wait time | 32539 | | 1 | 172.xx.xxx.xxx | 2882 | 200011 | idle wait time | 0 | +---------+----------------+----------+---------+-------------------------+-----------+ 10 rows in setThe following table describes the fields.
Field Description SNAP_ID The ID of the snapshot. SVR_IP The IP address of the server. SVR_PORT The port number of the server. STAT_ID The ID of the statistical item. STAT_NAME The name of the statistical item. VALUE The value of the statistical item. For more information about the
DBA_WR_SYS_TIME_MODELview, see oceanbase.DBA_WR_SYS_TIME_MODEL and DBA_WR_SYS_TIME_MODEL.
Query SQLSTAT data
Query the
GV$OB_SQLSTATview for the basic performance statistics of SQL statements in the cluster. Each SQL statement occupies one row, that is, each unique pair ofSQL_IDandPLAN_HASHoccupies one row. A column whose name contains_DELTAindicates the increment of the statistical value from the last WR snapshot collection time to the present time.obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_SQLSTAT LIMIT 1\GThe query result is as follows:
*************************** 1. row *************************** SVR_IP: 172.xx.xxx.xxx SVR_PORT: 2882 TENANT_ID: 1002 SQL_ID: B7A6FA97FEC98C06F9586D23935AC4C6 PLAN_ID: 0 PLAN_HASH: 0 PLAN_TYPE: 0 QUERY_SQL: START TRANSACTION SQL_TYPE: 69 MODULE: NULL ACTION: NULL PARSING_DB_ID: 201001 PARSING_DB_NAME: oceanbase PARSING_USER_ID: 200001 EXECUTIONS_TOTAL: 202176 EXECUTIONS_DELTA: 156614 DISK_READS_TOTAL: 0 DISK_READS_DELTA: 0 BUFFER_GETS_TOTAL: 0 BUFFER_GETS_DELTA: 0 ELAPSED_TIME_TOTAL: 13049640 ELAPSED_TIME_DELTA: 10085095 CPU_TIME_TOTAL: 0 CPU_TIME_DELTA: 0 CCWAIT_TOTAL: 0 CCWAIT_DELTA: 0 USERIO_WAIT_TOTAL: 0 USERIO_WAIT_DELTA: 0 APWAIT_TOTAL: 0 APWAIT_DELTA: 0 PHYSICAL_READ_REQUESTS_TOTAL: 0 PHYSICAL_READ_REQUESTS_DELTA: 0 PHYSICAL_READ_BYTES_TOTAL: 0 PHYSICAL_READ_BYTES_DELTA: 0 WRITE_THROTTLE_TOTAL: 0 WRITE_THROTTLE_DELTA: 0 ROWS_PROCESSED_TOTAL: 0 ROWS_PROCESSED_DELTA: 0 MEMSTORE_READ_ROWS_TOTAL: 0 MEMSTORE_READ_ROWS_DELTA: 0 MINOR_SSSTORE_READ_ROWS_TOTAL: 0 MINOR_SSSTORE_READ_ROWS_DELTA: 0 MAJOR_SSSTORE_READ_ROWS_TOTAL: 0 MAJOR_SSSTORE_READ_ROWS_DELTA: 0 RPC_TOTAL: 0 RPC_DELTA: 0 FETCHES_TOTAL: 0 FETCHES_DELTA: 0 RETRY_TOTAL: 1 RETRY_DELTA: 0 PARTITION_TOTAL: 0 PARTITION_DELTA: 0 NESTED_SQL_TOTAL: 0 NESTED_SQL_DELTA: 0 SOURCE_IP: 172.xx.xxx.xxx SOURCE_PORT: 2882 1 row in setThe following table describes the fields.
Field Description SVR_IP The IP address of the server. SVR_PORT The port number of the server. TENANT_ID The ID of the tenant. SQL_ID The ID of the SQL statement. PLAN_ID The ID of the execution plan. PLAN_HASH The hash value of the execution plan. PLAN_TYPE For SQL plan caching, this field indicates the type of the plan. Valid values: 1: indicates a local plan.2: indicates a remote plan.3: indicates a distributed plan.
For PL object caching, this field indicates the type of the PL object. Valid values:1: indicates a procedure.2: indicates a function.3: indicates a package.4: indicates an anonymous block.
QUERY_SQL The full SQL text. SQL_TYPE The SQL statement type. MODULE The application module that was used when the statement was parsed for the first time. ACTION The application action that was taken when the statement was parsed for the first time. PARSING_DB_ID The ID of the database where the statement was parsed. PARSING_DB_NAME The name of the database where the statement was parsed. PARSING_USER_ID The user ID for parsing the statement. EXECUTIONS_TOTAL The total number of executions of the plan in the plan cache. EXECUTIONS_DELTA The incremental number of executions of the plan in the plan cache. DISK_READS_TOTAL The total number of disk reads. DISK_READS_DELTA The incremental number of disk reads. BUFFER_GETS_TOTAL The total number of logical reads from cache. BUFFER_GETS_DELTA The incremental number of logical reads from cache. ELAPSED_TIME_TOTAL The total time consumed by the statement. For parallel execution scenarios, the value is the cumulative time of all execution threads. ELAPSED_TIME_DELTA The incremental time consumed by the statement. CPU_TIME_TOTAL The total CPU time. CPU_TIME_DELTA The incremental CPU time. CCWAIT_TOTAL The total concurrency wait time. CCWAIT_DELTA The incremental concurrency wait time. USERIO_WAIT_TOTAL The total I/O wait time. USERIO_WAIT_DELTA The incremental I/O wait time. APWAIT_TOTAL The total application wait time. APWAIT_DELTA The incremental application wait time. PHYSICAL_READ_REQUESTS_TOTAL The total number of physical read requests. PHYSICAL_READ_REQUESTS_DELTA The incremental number of physical read requests. PHYSICAL_READ_BYTES_TOTAL The total number of physical read bytes. PHYSICAL_READ_BYTES_DELTA The incremental number of physical read bytes. WRITE_THROTTLE_TOTAL The total time of MemStore write throttling. WRITE_THROTTLE_DELTA The incremental time of MemStore write throttling. ROWS_PROCESSED_TOTAL The total number of rows processed by the statement. ROWS_PROCESSED_DELTA The incremental number of rows processed by the statement. MEMSTORE_READ_ROWS_TOTAL The total number of rows read from MemStore. MEMSTORE_READ_ROWS_DELTA The incremental number of rows read from MemStore. MINOR_SSSTORE_READ_ROWS_TOTAL The total number of rows read from MINOR SSStore. MINOR_SSSTORE_READ_ROWS_DELTA The incremental number of rows read from MINOR SSStore. MAJOR_SSSTORE_READ_ROWS_TOTAL The total number of rows read from MAJOR SSStore. MAJOR_SSSTORE_READ_ROWS_DELTA The incremental number of rows read from MAJOR SSStore. RPC_TOTAL The total number of remote procedure calls (RPCs). RPC_DELTA The incremental number of RPCs. FETCHES_TOTAL The total number of result set fetches. FETCHES_DELTA The incremental number of result set fetches. RETRY_TOTAL The total number of SQL retries. RETRY_DELTA The incremental number of SQL retries. PARTITION_TOTAL The total number of partitions scanned during SQL execution. PARTITION_DELTA The incremental number of partitions scanned during SQL execution. NESTED_SQL_TOTAL The total number of nested SQL executions, which increases by 1 for each additional subquery execution. NESTED_SQL_DELTA The incremental number of nested SQL executions. SOURCE_IP The IP address of the request source. SOURCE_PORT The port number of the request source. For more information about the
GV$OB_SQLSTATview, see GV$OB_SQLSTAT.Query the
V$OB_SQLSTATview for the basic performance statistics of SQL statements on the current node. Each SQL statement occupies one row, that is, each unique pair ofSQL_IDandPLAN_HASHoccupies one row. A column whose name contains_DELTAindicates the increment of the statistical value from the last WR snapshot collection time to the present time.obclient [oceanbase]> SELECT * FROM oceanbase.V$OB_SQLSTAT LIMIT 1\GThe query result is as follows:
*************************** 1. row *************************** SVR_IP: 172.xx.xxx.xxx SVR_PORT: 2882 TENANT_ID: 1002 SQL_ID: B7A6FA97FEC98C06F9586D23935AC4C6 PLAN_ID: 0 PLAN_HASH: 0 PLAN_TYPE: 0 QUERY_SQL: START TRANSACTION SQL_TYPE: 69 MODULE: NULL ACTION: NULL PARSING_DB_ID: 201001 PARSING_DB_NAME: oceanbase PARSING_USER_ID: 200001 EXECUTIONS_TOTAL: 202363 EXECUTIONS_DELTA: 156801 DISK_READS_TOTAL: 0 DISK_READS_DELTA: 0 BUFFER_GETS_TOTAL: 0 BUFFER_GETS_DELTA: 0 ELAPSED_TIME_TOTAL: 13062207 ELAPSED_TIME_DELTA: 10097662 CPU_TIME_TOTAL: 0 CPU_TIME_DELTA: 0 CCWAIT_TOTAL: 0 CCWAIT_DELTA: 0 USERIO_WAIT_TOTAL: 0 USERIO_WAIT_DELTA: 0 APWAIT_TOTAL: 0 APWAIT_DELTA: 0 PHYSICAL_READ_REQUESTS_TOTAL: 0 PHYSICAL_READ_REQUESTS_DELTA: 0 PHYSICAL_READ_BYTES_TOTAL: 0 PHYSICAL_READ_BYTES_DELTA: 0 WRITE_THROTTLE_TOTAL: 0 WRITE_THROTTLE_DELTA: 0 ROWS_PROCESSED_TOTAL: 0 ROWS_PROCESSED_DELTA: 0 MEMSTORE_READ_ROWS_TOTAL: 0 MEMSTORE_READ_ROWS_DELTA: 0 MINOR_SSSTORE_READ_ROWS_TOTAL: 0 MINOR_SSSTORE_READ_ROWS_DELTA: 0 MAJOR_SSSTORE_READ_ROWS_TOTAL: 0 MAJOR_SSSTORE_READ_ROWS_DELTA: 0 RPC_TOTAL: 0 RPC_DELTA: 0 FETCHES_TOTAL: 0 FETCHES_DELTA: 0 RETRY_TOTAL: 1 RETRY_DELTA: 0 PARTITION_TOTAL: 0 PARTITION_DELTA: 0 NESTED_SQL_TOTAL: 0 NESTED_SQL_DELTA: 0 SOURCE_IP: 172.xx.xxx.xxx SOURCE_PORT: 2882 1 row in setThe following table describes the fields.
Field Description SVR_IP The IP address of the server. SVR_PORT The port number of the server. TENANT_ID The ID of the tenant. SQL_ID The ID of the SQL statement. PLAN_ID The ID of the execution plan. PLAN_HASH The hash value of the execution plan. PLAN_TYPE For SQL plan caching, this field indicates the type of the plan. Valid values: 1: indicates a local plan.2: indicates a remote plan.3: indicates a distributed plan.
For PL object caching, this field indicates the type of the PL object. Valid values:1: indicates a procedure.2: indicates a function.3: indicates a package.4: indicates an anonymous block.
QUERY_SQL The full SQL text. SQL_TYPE The SQL statement type. MODULE The application module that was used when the statement was parsed for the first time. ACTION The application action that was taken when the statement was parsed for the first time. PARSING_DB_ID The ID of the database where the statement was parsed. PARSING_DB_NAME The name of the database where the statement was parsed. PARSING_USER_ID The user ID for parsing the statement. EXECUTIONS_TOTAL The total number of executions of the plan in the plan cache. EXECUTIONS_DELTA The incremental number of executions of the plan in the plan cache. DISK_READS_TOTAL The total number of disk reads. DISK_READS_DELTA The incremental number of disk reads. BUFFER_GETS_TOTAL The total number of logical reads from cache. BUFFER_GETS_DELTA The incremental number of logical reads from cache. ELAPSED_TIME_TOTAL The total time consumed by the statement. For parallel execution scenarios, the value is the cumulative time of all execution threads. ELAPSED_TIME_DELTA The incremental time consumed by the statement. CPU_TIME_TOTAL The total CPU time. CPU_TIME_DELTA The incremental CPU time. CCWAIT_TOTAL The total concurrency wait time. CCWAIT_DELTA The incremental concurrency wait time. USERIO_WAIT_TOTAL The total I/O wait time. USERIO_WAIT_DELTA The incremental I/O wait time. APWAIT_TOTAL The total application wait time. APWAIT_DELTA The incremental application wait time. PHYSICAL_READ_REQUESTS_TOTAL The total number of physical read requests. PHYSICAL_READ_REQUESTS_DELTA The incremental number of physical read requests. PHYSICAL_READ_BYTES_TOTAL The total number of physical read bytes. PHYSICAL_READ_BYTES_DELTA The incremental number of physical read bytes. WRITE_THROTTLE_TOTAL The total time of MemStore write throttling. WRITE_THROTTLE_DELTA The incremental time of MemStore write throttling. ROWS_PROCESSED_TOTAL The total number of rows processed by the statement. ROWS_PROCESSED_DELTA The incremental number of rows processed by the statement. MEMSTORE_READ_ROWS_TOTAL The total number of rows read from MemStore. MEMSTORE_READ_ROWS_DELTA The incremental number of rows read from MemStore. MINOR_SSSTORE_READ_ROWS_TOTAL The total number of rows read from MINOR SSStore. MINOR_SSSTORE_READ_ROWS_DELTA The incremental number of rows read from MINOR SSStore. MAJOR_SSSTORE_READ_ROWS_TOTAL The total number of rows read from MAJOR SSStore. MAJOR_SSSTORE_READ_ROWS_DELTA The incremental number of rows read from MAJOR SSStore. RPC_TOTAL The total number of RPCs. RPC_DELTA The incremental number of RPCs. FETCHES_TOTAL The total number of result set fetches. FETCHES_DELTA The incremental number of result set fetches. RETRY_TOTAL The total number of SQL retries. RETRY_DELTA The incremental number of SQL retries. PARTITION_TOTAL The total number of partitions scanned during SQL execution. PARTITION_DELTA The incremental number of partitions scanned during SQL execution. NESTED_SQL_TOTAL The total number of nested SQL executions, which increases by 1 for each additional subquery execution. NESTED_SQL_DELTA The incremental number of nested SQL executions. SOURCE_IP The IP address of the request source. SOURCE_PORT The port number of the request source. For more information about the
V$OB_SQLSTATview, see V$OB_SQLSTAT.Query the
oceanbase.CDB_WR_SQLSTATview for the basic performance statistics of SQL statements executed in all tenants. Each SQL statement occupies one row, that is, each unique pair ofSQL_IDandPLAN_HASHoccupies one row. A column whose name contains_DELTAindicates the increment of the statistical value from the last WR snapshot collection time to the present time.obclient [oceanbase]> SELECT * FROM oceanbase.CDB_WR_SQLSTAT LIMIT 1;The query result is as follows:
+-----------+---------+----------------+----------+----------------------------------+---------------------+---------+-----------+--------+--------+---------------+-----------------+-----------------+------------------+------------------+------------------+------------------+-------------------+-------------------+--------------------+--------------------+----------------+----------------+--------------+--------------+-------------------+-------------------+--------------+--------------+------------------------------+------------------------------+---------------------------+---------------------------+----------------------+----------------------+----------------------+----------------------+--------------------------+--------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------+-----------+---------------+---------------+-------------+-------------+-----------------+-----------------+------------------+------------------+----------------+-------------+ | TENANT_ID | SNAP_ID | SVR_IP | SVR_PORT | SQL_ID | PLAN_HASH | PLAN_ID | PLAN_TYPE | MODULE | ACTION | PARSING_DB_ID | PARSING_DB_NAME | PARSING_USER_ID | EXECUTIONS_TOTAL | EXECUTIONS_DELTA | DISK_READS_TOTAL | DISK_READS_DELTA | BUFFER_GETS_TOTAL | BUFFER_GETS_DELTA | ELAPSED_TIME_TOTAL | ELAPSED_TIME_DELTA | CPU_TIME_TOTAL | CPU_TIME_DELTA | CCWAIT_TOTAL | CCWAIT_DELTA | USERIO_WAIT_TOTAL | USERIO_WAIT_DELTA | APWAIT_TOTAL | APWAIT_DELTA | PHYSICAL_READ_REQUESTS_TOTAL | PHYSICAL_READ_REQUESTS_DELTA | PHYSICAL_READ_BYTES_TOTAL | PHYSICAL_READ_BYTES_DELTA | WRITE_THROTTLE_TOTAL | WRITE_THROTTLE_DELTA | ROWS_PROCESSED_TOTAL | ROWS_PROCESSED_DELTA | MEMSTORE_READ_ROWS_TOTAL | MEMSTORE_READ_ROWS_DELTA | MINOR_SSSTORE_READ_ROWS_TOTAL | MINOR_SSSTORE_READ_ROWS_DELTA | MAJOR_SSSTORE_READ_ROWS_TOTAL | MAJOR_SSSTORE_READ_ROWS_DELTA | RPC_TOTAL | RPC_DELTA | FETCHES_TOTAL | FETCHES_DELTA | RETRY_TOTAL | RETRY_DELTA | PARTITION_TOTAL | PARTITION_DELTA | NESTED_SQL_TOTAL | NESTED_SQL_DELTA | SOURCE_IP | SOURCE_PORT | +-----------+---------+----------------+----------+----------------------------------+---------------------+---------+-----------+--------+--------+---------------+-----------------+-----------------+------------------+------------------+------------------+------------------+-------------------+-------------------+--------------------+--------------------+----------------+----------------+--------------+--------------+-------------------+-------------------+--------------+--------------+------------------------------+------------------------------+---------------------------+---------------------------+----------------------+----------------------+----------------------+----------------------+--------------------------+--------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------+-----------+---------------+---------------+-------------+-------------+-----------------+-----------------+------------------+------------------+----------------+-------------+ | 1002 | 1 | 172.xx.xxx.xxx | 2882 | 01058E374A5EF26873BA4D99C4A74582 | 5547575357486659143 | 181 | 1 | NULL | NULL | 201001 | oceanbase | 200001 | 1 | 1 | 0 | 0 | 0 | 0 | 1424 | 1424 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 172.xx.xxx.xxx | 2882 | +-----------+---------+----------------+----------+----------------------------------+---------------------+---------+-----------+--------+--------+---------------+-----------------+-----------------+------------------+------------------+------------------+------------------+-------------------+-------------------+--------------------+--------------------+----------------+----------------+--------------+--------------+-------------------+-------------------+--------------+--------------+------------------------------+------------------------------+---------------------------+---------------------------+----------------------+----------------------+----------------------+----------------------+--------------------------+--------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------+-----------+---------------+---------------+-------------+-------------+-----------------+-----------------+------------------+------------------+----------------+-------------+ 1 row in setThe following table describes the fields.
Field Description 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 number of the server. SQL_ID The ID of the SQL statement. PLAN_HASH The hash value of the execution plan. PLAN_ID The ID of the execution plan. PLAN_TYPE For SQL plan caching, this field indicates the type of the plan. Valid values: 1: indicates a local plan.2: indicates a remote plan.3: indicates a distributed plan.
For PL object caching, this field indicates the type of the PL object. Valid values:1: indicates a procedure.2: indicates a function.3: indicates a package.4: indicates an anonymous block.
MODULE The application module that was used when the statement was parsed for the first time. ACTION The application action that was taken when the statement was parsed for the first time. PARSING_DB_ID The ID of the database where the statement was parsed. PARSING_DB_NAME The name of the database where the statement was parsed. PARSING_USER_ID The user ID for parsing the statement. EXECUTIONS_TOTAL The total number of executions of the plan in the plan cache. EXECUTIONS_DELTA The incremental number of executions of the plan in the plan cache. DISK_READS_TOTAL The total number of disk reads. DISK_READS_DELTA The incremental number of disk reads. BUFFER_GETS_TOTAL The total number of logical reads from cache. BUFFER_GETS_DELTA The incremental number of logical reads from cache. ELAPSED_TIME_TOTAL The total time consumed by the statement. For parallel execution scenarios, the value is the cumulative time of all execution threads. ELAPSED_TIME_DELTA The incremental time consumed by the statement. CPU_TIME_TOTAL The total CPU time. CPU_TIME_DELTA The incremental CPU time. CCWAIT_TOTAL The total concurrency wait time. CCWAIT_DELTA The incremental concurrency wait time. USERIO_WAIT_TOTAL The total I/O wait time. USERIO_WAIT_DELTA The incremental I/O wait time. APWAIT_TOTAL The total application wait time. APWAIT_DELTA The incremental application wait time. PHYSICAL_READ_REQUESTS_TOTAL The total number of physical read requests. PHYSICAL_READ_REQUESTS_DELTA The incremental number of physical read requests. PHYSICAL_READ_BYTES_TOTAL The total number of physical read bytes. PHYSICAL_READ_BYTES_DELTA The incremental number of physical read bytes. WRITE_THROTTLE_TOTAL The total time of MemStore write throttling. WRITE_THROTTLE_DELTA The incremental time of MemStore write throttling. ROWS_PROCESSED_TOTAL The total number of rows processed by the statement. ROWS_PROCESSED_DELTA The incremental number of rows processed by the statement. MEMSTORE_READ_ROWS_TOTAL The total number of rows read from MemStore. MEMSTORE_READ_ROWS_DELTA The incremental number of rows read from MemStore. MINOR_SSSTORE_READ_ROWS_TOTAL The total number of rows read from MINOR SSStore. MINOR_SSSTORE_READ_ROWS_DELTA The incremental number of rows read from MINOR SSStore. MAJOR_SSSTORE_READ_ROWS_TOTAL The total number of rows read from MAJOR SSStore. MAJOR_SSSTORE_READ_ROWS_DELTA The incremental number of rows read from MAJOR SSStore. RPC_TOTAL The total number of RPCs. RPC_DELTA The incremental number of RPCs. FETCHES_TOTAL The total number of result set fetches. FETCHES_DELTA The incremental number of result set fetches. RETRY_TOTAL The total number of SQL retries. RETRY_DELTA The incremental number of SQL retries. PARTITION_TOTAL The total number of partitions scanned during SQL execution. PARTITION_DELTA The incremental number of partitions scanned during SQL execution. NESTED_SQL_TOTAL The total number of nested SQL executions, which increases by 1 for each additional subquery execution. NESTED_SQL_DELTA The incremental number of nested SQL executions. SOURCE_IP The IP address of the request source. SOURCE_PORT The port number of the request source. For more information about the
oceanbase.CDB_WR_SQLSTATview, see oceanbase.CDB_WR_SQLSTAT.Query the
oceanbase.DBA_WR_SQLSTATview for the basic performance statistics of SQL statements executed in the current tenant. A column whose name contains_DELTAindicates the increment of the statistical value from the last WR snapshot collection time to the present time.obclient [SYS]> SELECT * FROM SYS.DBA_WR_SQLSTAT WHERE ROWNUM = 1;The query result is as follows:
+---------+----------------+----------+----------------------------------+---------------------+---------+-----------+--------+--------+---------------+-----------------+-----------------+------------------+------------------+------------------+------------------+-------------------+-------------------+--------------------+--------------------+----------------+----------------+--------------+--------------+-------------------+-------------------+--------------+--------------+------------------------------+------------------------------+---------------------------+---------------------------+----------------------+----------------------+----------------------+----------------------+--------------------------+--------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------+-----------+---------------+---------------+-------------+-------------+-----------------+-----------------+------------------+------------------+----------------+-------------+ | SNAP_ID | SVR_IP | SVR_PORT | SQL_ID | PLAN_HASH | PLAN_ID | PLAN_TYPE | MODULE | ACTION | PARSING_DB_ID | PARSING_DB_NAME | PARSING_USER_ID | EXECUTIONS_TOTAL | EXECUTIONS_DELTA | DISK_READS_TOTAL | DISK_READS_DELTA | BUFFER_GETS_TOTAL | BUFFER_GETS_DELTA | ELAPSED_TIME_TOTAL | ELAPSED_TIME_DELTA | CPU_TIME_TOTAL | CPU_TIME_DELTA | CCWAIT_TOTAL | CCWAIT_DELTA | USERIO_WAIT_TOTAL | USERIO_WAIT_DELTA | APWAIT_TOTAL | APWAIT_DELTA | PHYSICAL_READ_REQUESTS_TOTAL | PHYSICAL_READ_REQUESTS_DELTA | PHYSICAL_READ_BYTES_TOTAL | PHYSICAL_READ_BYTES_DELTA | WRITE_THROTTLE_TOTAL | WRITE_THROTTLE_DELTA | ROWS_PROCESSED_TOTAL | ROWS_PROCESSED_DELTA | MEMSTORE_READ_ROWS_TOTAL | MEMSTORE_READ_ROWS_DELTA | MINOR_SSSTORE_READ_ROWS_TOTAL | MINOR_SSSTORE_READ_ROWS_DELTA | MAJOR_SSSTORE_READ_ROWS_TOTAL | MAJOR_SSSTORE_READ_ROWS_DELTA | RPC_TOTAL | RPC_DELTA | FETCHES_TOTAL | FETCHES_DELTA | RETRY_TOTAL | RETRY_DELTA | PARTITION_TOTAL | PARTITION_DELTA | NESTED_SQL_TOTAL | NESTED_SQL_DELTA | SOURCE_IP | SOURCE_PORT | +---------+----------------+----------+----------------------------------+---------------------+---------+-----------+--------+--------+---------------+-----------------+-----------------+------------------+------------------+------------------+------------------+-------------------+-------------------+--------------------+--------------------+----------------+----------------+--------------+--------------+-------------------+-------------------+--------------+--------------+------------------------------+------------------------------+---------------------------+---------------------------+----------------------+----------------------+----------------------+----------------------+--------------------------+--------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------+-----------+---------------+---------------+-------------+-------------+-----------------+-----------------+------------------+------------------+----------------+-------------+ | 1 | 172.xx.xxx.xxx | 2882 | 01058E374A5EF26873BA4D99C4A74582 | 5547575357486659143 | 223 | 1 | NULL | NULL | 201001 | oceanbase | 200001 | 1 | 1 | 1 | 1 | 1 | 1 | 1699 | 1699 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 193 | 193 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 172.xx.xxx.xxx | 2882 | +---------+----------------+----------+----------------------------------+---------------------+---------+-----------+--------+--------+---------------+-----------------+-----------------+------------------+------------------+------------------+------------------+-------------------+-------------------+--------------------+--------------------+----------------+----------------+--------------+--------------+-------------------+-------------------+--------------+--------------+------------------------------+------------------------------+---------------------------+---------------------------+----------------------+----------------------+----------------------+----------------------+--------------------------+--------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------+-----------+---------------+---------------+-------------+-------------+-----------------+-----------------+------------------+------------------+----------------+-------------+ 1 row in setThe following table describes the fields.
Field Description SNAP_ID The ID of the snapshot. SVR_IP The IP address of the server. SVR_PORT The port number of the server. SQL_ID The ID of the SQL statement. PLAN_HASH The hash value of the execution plan. PLAN_ID The ID of the execution plan. PLAN_TYPE For SQL plan caching, this field indicates the type of the plan. Valid values: 1: indicates a local plan.2: indicates a remote plan.3: indicates a distributed plan.
For PL object caching, this field indicates the type of the PL object. Valid values:1: indicates a procedure.2: indicates a function.3: indicates a package.4: indicates an anonymous block.
MODULE The application module that was used when the statement was parsed for the first time. ACTION The application action that was taken when the statement was parsed for the first time. PARSING_DB_ID The ID of the database where the statement was parsed. PARSING_DB_NAME The name of the database where the statement was parsed. PARSING_USER_ID The user ID for parsing the statement. EXECUTIONS_TOTAL The total number of executions of the plan in the plan cache. EXECUTIONS_DELTA The incremental number of executions of the plan in the plan cache. DISK_READS_TOTAL The total number of disk reads. DISK_READS_DELTA The incremental number of disk reads. BUFFER_GETS_TOTAL The total number of logical reads from cache. BUFFER_GETS_DELTA The incremental number of logical reads from cache. ELAPSED_TIME_TOTAL The total time consumed by the statement. For parallel execution scenarios, the value is the cumulative time of all execution threads. ELAPSED_TIME_DELTA The incremental time consumed by the statement. CPU_TIME_TOTAL The total CPU time. CPU_TIME_DELTA The incremental CPU time. CCWAIT_TOTAL The total concurrency wait time. CCWAIT_DELTA The incremental concurrency wait time. USERIO_WAIT_TOTAL The total I/O wait time. USERIO_WAIT_DELTA The incremental I/O wait time. APWAIT_TOTAL The total application wait time. APWAIT_DELTA The incremental application wait time. PHYSICAL_READ_REQUESTS_TOTAL The total number of physical read requests. PHYSICAL_READ_REQUESTS_DELTA The incremental number of physical read requests. PHYSICAL_READ_BYTES_TOTAL The total number of physical read bytes. PHYSICAL_READ_BYTES_DELTA The incremental number of physical read bytes. WRITE_THROTTLE_TOTAL The total time of MemStore write throttling. WRITE_THROTTLE_DELTA The incremental time of MemStore write throttling. ROWS_PROCESSED_TOTAL The total number of rows processed by the statement. ROWS_PROCESSED_DELTA The incremental number of rows processed by the statement. MEMSTORE_READ_ROWS_TOTAL The total number of rows read from MemStore. MEMSTORE_READ_ROWS_DELTA The incremental number of rows read from MemStore. MINOR_SSSTORE_READ_ROWS_TOTAL The total number of rows read from MINOR SSStore. MINOR_SSSTORE_READ_ROWS_DELTA The incremental number of rows read from MINOR SSStore. MAJOR_SSSTORE_READ_ROWS_TOTAL The total number of rows read from MAJOR SSStore. MAJOR_SSSTORE_READ_ROWS_DELTA The incremental number of rows read from MAJOR SSStore. RPC_TOTAL The total number of RPCs. RPC_DELTA The incremental number of RPCs. FETCHES_TOTAL The total number of result set fetches. FETCHES_DELTA The incremental number of result set fetches. RETRY_TOTAL The total number of SQL retries. RETRY_DELTA The incremental number of SQL retries. PARTITION_TOTAL The total number of partitions scanned during SQL execution. PARTITION_DELTA The incremental number of partitions scanned during SQL execution. NESTED_SQL_TOTAL The total number of nested SQL executions, which increases by 1 for each additional subquery execution. NESTED_SQL_DELTA The incremental number of nested SQL executions. SOURCE_IP VARCHAR2(46) NO The IP address of the request source. SOURCE_PORT The port number of the request source. For more information about the
DBA_WR_SQLSTATview, see oceanbase.DBA_WR_SQLSTAT and DBA_WR_SQLSTAT.