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.
View historical session data
Use the
oceanbase.CDB_WR_ACTIVE_SESSION_HISTORYview to view ASH data of all tenants after it is persisted.obclient [oceanbase]> SELECT * FROM oceanbase.CDB_WR_ACTIVE_SESSION_HISTORY limit 1\GThe return result is as follows:
*************************** 1. row *************************** CLUSTER_ID: 10001 TENANT_ID: 1002 SNAP_ID: 1 SVR_IP: xx.xx.xx.xx SVR_PORT: 2882 SAMPLE_ID: 740 SESSION_ID: -9223372036854729597 SAMPLE_TIME: 2025-02-28 09:43:43.809287 USER_ID: 200001 SESSION_TYPE: 1 SESSION_STATE: WAITING SQL_ID: TRACE_ID: YB42AC1E87F8-00062F29EA381666-0-0 EVENT_NO: 29 EVENT_ID: 15102 TIME_WAITED: 199579 P1: 200000 P2: 0 P3: 0 SQL_PLAN_LINE_ID: NULL GROUP_ID: 0 PLAN_HASH: NULL THREAD_ID: 82046 STMT_TYPE: NULL TIME_MODEL: 0 IN_PARSE: N IN_PL_PARSE: N IN_PLAN_CACHE: N IN_SQL_OPTIMIZE: N IN_SQL_EXECUTION: N IN_PX_EXECUTION: N IN_SEQUENCE_LOAD: N IN_COMMITTING: N IN_STORAGE_READ: N IN_STORAGE_WRITE: N IN_REMOTE_DAS_EXECUTION: N IN_FILTER_ROWS: N IN_RPC_ENCODE: N IN_RPC_DECODE: N IN_CONNECTION_MGR: N PROGRAM: T1002_LogService MODULE: LOCAL INNER SQL EXEC ACTION: NULL_INNER_SQL CLIENT_ID: NULL BACKTRACE: NULL PLAN_ID: 0 TM_DELTA_TIME: 739151 TM_DELTA_CPU_TIME: 165 TM_DELTA_DB_TIME: 165 TOP_LEVEL_SQL_ID: NULL IN_PLSQL_COMPILATION: N IN_PLSQL_EXECUTION: N PLSQL_ENTRY_OBJECT_ID: NULL PLSQL_ENTRY_SUBPROGRAM_ID: NULL PLSQL_ENTRY_SUBPROGRAM_NAME: NULL PLSQL_OBJECT_ID: NULL PLSQL_SUBPROGRAM_ID: NULL PLSQL_SUBPROGRAM_NAME: NULL BLOCKING_SESSION_ID: NULL TABLET_ID: NULL PROXY_SID: -9223372036854729597 TX_ID: NULL DELTA_READ_IO_REQUESTS: 0 DELTA_READ_IO_BYTES: 0 DELTA_WRITE_IO_REQUESTS: 0 DELTA_WRITE_IO_BYTES: 0 1 row in set (0.200 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 node. SVR_PORT The port of the node. SAMPLE_ID The ID of the sample. 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. - N: FOREGROUND indicates a foreground session, that is, a user session.
- Y: BACKGROUND indicates a background session.
SESSION_STATE The status of the session at the sampling time. EVENT_NO == 0: ON CPU indicates that the session is not waiting and is executing SQL logic.EVENT_NO ≠ 0: WAITTING indicates that the session is waiting. For more information about the wait events, see the EVENT field.
SQL_ID The ID of the SQL statement. TRACE_ID The ID of the operator. EVENT_NO The internal ID of the wait event, which is used to join with other tables. EVENT_ID The ID of the current wait event. TIME_WAITED The total waiting time of the wait event, in microseconds (us). P1 The value of wait event parameter 1. P2 The value of wait event parameter 2. P3 The value of wait event parameter 3. SQL_PLAN_LINE_ID The ID of the SQL operator in the SQL plan at the sampling time. The value is NULL if there is no corresponding operator. GROUP_ID The group information of the sampling point. PLAN_HASH The plan hash value of the current SQL statement. THREAD_ID The ID of the thread on which the active session resides. STMT_TYPE The type of the SQL statement of the active session. TIME_MODEL The time model information. IN_PARSE Indicates whether the session is parsing an SQL statement at the sampling time. IN_PL_PARSE Indicates whether the session is parsing an SQL PL statement at the sampling time. IN_PLAN_CACHE Indicates whether the session is matching a plan in the plan cache at the sampling time. IN_SQL_OPTIMIZE Indicates whether the session is optimizing an SQL statement at the sampling time. IN_SQL_EXECUTION Indicates whether the session is executing an SQL statement at the sampling time. IN_PX_EXECUTION Indicates whether the session is executing an SQL statement in parallel at the sampling time. If the session is in this state, it is also in the IN_SQL_EXECUTION state. IN_SEQUENCE_LOAD Indicates whether the session is fetching values from an auto-increment column or a sequence at the sampling time. IN_COMMITTING Indicates whether the sampling point is in the transaction commit phase. IN_STORAGE_READ Indicates whether the sampling point is in the storage read phase. IN_STORAGE_WRITE Indicates whether the sampling point is in the storage write phase. IN_REMOTE_DAS_EXECUTION Indicates whether the sampling point is in the DAS remote execution phase. IN_FILTER_ROWS Indicates whether the sampling point is in the storage downpress execution phase. IN_RPC_ENCODE The serialization operation currently being performed by the SQL. IN_RPC_DECODE The deserialization operation currently being performed by the SQL. IN_CONNECTION_MGR The connection establishment operation currently being performed by the SQL. PROGRAM The name of the program being executed at the sampling point: - For background threads: the name of the thread, such as
observerandWrTimer. - For foreground threads:
- For ordinary requests:
user@client_ip (thread_name) - For
inner_sqlremote executions:INNER SQL REMOTE EXEC (thread_name) - For DAS remote executions:
DAS REMOTE EXEC (thread_name)
- For ordinary requests:
MODULE The value of the MODULE field recorded for the session at the sampling time, which is set by the DBMS_APPLICATION_INFO.SET_MODULEpackage.ACTION The value of the ACTION field recorded for the session at the sampling time, which is set by the DBMS_APPLICATION_INFO.SET_ACTIONpackage.CLIENT_ID The value of the CLIENT_ID field recorded for the session at the sampling time, which is set by the DBMS_SESSION.set_identifierpackage.BACKTRACE The code call stack at the time of the event, which is used for auxiliary debugging. PLAN_ID The ID of the plan in the plan cache to which the sampled SQL statement belongs. This field is used to associate the sampling point with the plan. TM_DELTA_TIME The time interval for calculating the time model, in microseconds. TM_DELTA_CPU_TIME The time spent on CPU in the past TM_DELTA_TIMEtime period, in microseconds.TM_DELTA_DB_TIME The time spent in database calls in the past TM_DELTA_TIMEtime period, in microseconds.TOP_LEVEL_SQL_ID The ID of the top-level SQL statement. IN_PLSQL_COMPILATION The PL compilation status: Y/N IN_PLSQL_EXECUTION The PL execution status: Y/N PLSQL_ENTRY_OBJECT_ID The ID of the top-level PL object. PLSQL_ENTRY_SUBPROGRAM_ID The ID of the top-level PL subprogram. PLSQL_ENTRY_SUBPROGRAM_NAME The name of the top-level PL subprogram. PLSQL_OBJECT_ID The ID of the PL object being executed. PLSQL_SUBPROGRAM_ID The ID of the PL subprogram being executed. PLSQL_SUBPROGRAM_NAME The name of the PL subprogram being executed. BLOCKING_SESSION_ID The ID of the session that is blocking the current session. This field is displayed only in scenarios of lock conflict and shows the ID of the session that holds the lock. TABLET_ID The ID of the tablet being processed by the current SQL statement. PROXY_SID The ID of the proxy session. TX_ID The ID of the current transaction. DELTA_READ_IO_REQUESTS The number of read operations between two sampling points. DELTA_READ_IO_BYTES The cumulative size of files read between two sampling points. DELTA_WRITE_IO_REQUESTS The number of write operations between two sampling points. DELTA_WRITE_IO_BYTES The cumulative size of files written between two sampling points. For more information about the
oceanbase.CDB_WR_ACTIVE_SESSION_HISTORYview, see oceanbase.CDB_WR_ACTIVE_SESSION_HISTORY.Use the
oceanbase.DBA_WR_ACTIVE_SESSION_HISTORYview to view ASH data of the current tenant after it is persisted.obclient [oceanbase]> SELECT * FROM oceanbase.DBA_WR_ACTIVE_SESSION_HISTORY limit 1\GThe return result is as follows:
*************************** 1. row *************************** CLUSTER_ID: 10001 TENANT_ID: 1002 SNAP_ID: 1 SVR_IP: xx.xx.xx.xx SVR_PORT: 2882 SAMPLE_ID: 740 SESSION_ID: -9223372036854729597 SAMPLE_TIME: 2025-02-28 09:43:43.809287 USER_ID: 200001 SESSION_TYPE: 1 SESSION_STATE: WAITING SQL_ID: TRACE_ID: YBXXXXXXXX-000XXXXXXXXXXXX-0-0 EVENT_NO: 29 EVENT_ID: 15102 TIME_WAITED: 199579 P1: 200000 P2: 0 P3: 0 SQL_PLAN_LINE_ID: NULL GROUP_ID: 0 PLAN_HASH: NULL THREAD_ID: 82046 STMT_TYPE: NULL TIME_MODEL: 0 IN_PARSE: N IN_PL_PARSE: N IN_PLAN_CACHE: N IN_SQL_OPTIMIZE: N IN_SQL_EXECUTION: N IN_PX_EXECUTION: N IN_SEQUENCE_LOAD: N IN_COMMITTING: N IN_STORAGE_READ: N IN_STORAGE_WRITE: N IN_REMOTE_DAS_EXECUTION: N IN_FILTER_ROWS: N IN_RPC_ENCODE: N IN_RPC_DECODE: N IN_CONNECTION_MGR: N PROGRAM: T1002_LogService MODULE: LOCAL INNER SQL EXEC ACTION: NULL_INNER_SQL CLIENT_ID: NULL BACKTRACE: NULL PLAN_ID: 0 TM_DELTA_TIME: 739151 TM_DELTA_CPU_TIME: 165 TM_DELTA_DB_TIME: 165 TOP_LEVEL_SQL_ID: NULL IN_PLSQL_COMPILATION: N IN_PLSQL_EXECUTION: N PLSQL_ENTRY_OBJECT_ID: NULL PLSQL_ENTRY_SUBPROGRAM_ID: NULL PLSQL_ENTRY_SUBPROGRAM_NAME: NULL PLSQL_OBJECT_ID: NULL PLSQL_SUBPROGRAM_ID: NULL PLSQL_SUBPROGRAM_NAME: NULL BLOCKING_SESSION_ID: NULL TABLET_ID: NULL PROXY_SID: -9223372036854729597 TX_ID: NULL DELTA_READ_IO_REQUESTS: 0 DELTA_READ_IO_BYTES: 0 DELTA_WRITE_IO_REQUESTS: 0 DELTA_WRITE_IO_BYTES: 0 1 row in set (0.093 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 node. SVR_PORT The port of the node. SAMPLE_ID The ID of the sample. 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. - N: FOREGROUND indicates a foreground session, that is, a user session
- Y: BACKGROUND indicates a background session.
SESSION_STATE The status of the session at the sampling time. EVENT_NO == 0: ON CPU indicates that the session is not waiting and is executing SQL logic.EVENT_NO ≠ 0: WAITTING indicates that the session is waiting. For more information about the wait events, see the EVENT field.
SQL_ID The ID of the SQL statement. TRACE_ID The ID of the operator. EVENT_NO The internal ID of the wait event, which is used to join with other tables. EVENT_ID The ID of the current wait event. TIME_WAITED The total waiting time of the wait event, in microseconds (us). P1 The value of wait event parameter 1. P2 The value of wait event parameter 2. P3 The value of wait event parameter 3. SQL_PLAN_LINE_ID The ID of the SQL operator in the SQL plan at the sampling time. The value is NULL if there is no corresponding operator. GROUP_ID The group information of the sampling point. PLAN_HASH The plan hash value of the current SQL statement. THREAD_ID The ID of the thread on which the active session resides. STMT_TYPE The type of the SQL statement of the active session. TIME_MODEL The time model information. IN_PARSE Indicates whether the session is parsing an SQL statement at the sampling time. IN_PL_PARSE Indicates whether the session is parsing an SQL PL statement at the sampling time. IN_PLAN_CACHE Indicates whether the session is matching a plan in the plan cache at the sampling time. IN_SQL_OPTIMIZE Indicates whether the session is optimizing an SQL statement at the sampling time. IN_SQL_EXECUTION Indicates whether the session is executing an SQL statement at the sampling time. IN_PX_EXECUTION Indicates whether the session is executing an SQL statement in parallel at the sampling time. If the session is in this state, it is also in the IN_SQL_EXECUTION state. IN_SEQUENCE_LOAD Indicates whether the session is fetching values from an auto-increment column or a sequence at the sampling time. IN_COMMITTING Indicates whether the sampling point is in the transaction commit phase. IN_STORAGE_READ Indicates whether the sampling point is in the storage read phase. IN_STORAGE_WRITE Indicates whether the sampling point is in the storage write phase. IN_REMOTE_DAS_EXECUTION Indicates whether the sampling point is in the DAS remote execution phase. IN_FILTER_ROWS Indicates whether the sampling point is in the storage downpress execution phase. IN_RPC_ENCODE The serialization operation currently being performed by the SQL. IN_RPC_DECODE The deserialization operation currently being performed by the SQL. IN_CONNECTION_MGR The connection establishment operation currently being performed by the SQL. PROGRAM The name of the program being executed at the sampling point: - For background threads: the name of the thread, such as
observerandWrTimer. - For foreground threads:
- For ordinary requests:
user@client_ip (thread_name) - For
inner_sqlremote executions:INNER SQL REMOTE EXEC (thread_name) - For DAS remote executions:
DAS REMOTE EXEC (thread_name)
- For ordinary requests:
MODULE The value of the MODULE field recorded for the session at the sampling time, which is set by the DBMS_APPLICATION_INFO.SET_MODULEpackage.ACTION The value of the ACTION field recorded for the session at the sampling time, which is set by the DBMS_APPLICATION_INFO.SET_ACTIONpackage.CLIENT_ID The value of the CLIENT_ID field recorded for the session at the sampling time, which is set by the DBMS_SESSION.set_identifierpackage.BACKTRACE The code call stack at the time of the event, which is used for auxiliary debugging. PLAN_ID The ID of the plan in the plan cache to which the sampled SQL statement belongs. This field is used to associate the sampling point with the plan. TM_DELTA_TIME The time interval for calculating the time model, in microseconds. TM_DELTA_CPU_TIME The time spent on CPU in the past TM_DELTA_TIMEtime period, in microseconds.TM_DELTA_DB_TIME The time spent in database calls in the past TM_DELTA_TIMEtime period, in microseconds.TOP_LEVEL_SQL_ID The ID of the top-level SQL statement. IN_PLSQL_COMPILATION The PL compilation status: Y/N IN_PLSQL_EXECUTION The PL execution status: Y/N PLSQL_ENTRY_OBJECT_ID The ID of the top-level PL object. PLSQL_ENTRY_SUBPROGRAM_ID The ID of the top-level PL subprogram. PLSQL_ENTRY_SUBPROGRAM_NAME The name of the top-level PL subprogram. PLSQL_OBJECT_ID The ID of the PL object being executed. PLSQL_SUBPROGRAM_ID The ID of the PL subprogram being executed. PLSQL_SUBPROGRAM_NAME The name of the PL subprogram being executed. BLOCKING_SESSION_ID The ID of the session that is blocking the current session. This field is displayed only in scenarios of lock conflict and shows the ID of the session that holds the lock. TABLET_ID The ID of the tablet being processed by the current SQL statement. PROXY_SID The ID of the proxy session. TX_ID The ID of the current transaction. DELTA_READ_IO_REQUESTS The number of read operations between two sampling points. DELTA_READ_IO_BYTES The cumulative size of files read between two sampling points. DELTA_WRITE_IO_REQUESTS The number of write operations between two sampling points. DELTA_WRITE_IO_BYTES The cumulative size of files written between two sampling points. For more information about the
DBA_WR_ACTIVE_SESSION_HISTORYview, see oceanbase.DBA_WR_ACTIVE_SESSION_HISTORY and DBA_WR_ACTIVE_SESSION_HISTORY.Use the
GV$OB_ACTIVE_SESSION_HISTORYview to view the history of active sessions.obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_ACTIVE_SESSION_HISTORY limit 1\GThe query result is as follows:
*************************** 1. row *************************** SVR_IP: xx.xx.xx.xx SVR_PORT: 2882 SAMPLE_ID: 1912993 SAMPLE_TIME: 2025-03-04 10:25:06.155640 CON_ID: 1001 USER_ID: 0 SESSION_ID: -9223372036854729409 SESSION_TYPE: BACKGROUND SESSION_STATE: ON CPU SQL_ID: NULL PLAN_ID: 0 TRACE_ID: NULL EVENT: EVENT_NO: 0 EVENT_ID: NULL P1TEXT: P1: 0 P2TEXT: P2: 0 P3TEXT: P3: 0 WAIT_CLASS: OTHER WAIT_CLASS_ID: 100 TIME_WAITED: 0 SQL_PLAN_LINE_ID: NULL GROUP_ID: 0 PLAN_HASH: NULL THREAD_ID: 82216 STMT_TYPE: NULL TIME_MODEL: 0 IN_PARSE: N IN_PL_PARSE: N IN_PLAN_CACHE: N IN_SQL_OPTIMIZE: N IN_SQL_EXECUTION: N IN_PX_EXECUTION: N IN_SEQUENCE_LOAD: N IN_COMMITTING: N IN_STORAGE_READ: N IN_STORAGE_WRITE: N IN_REMOTE_DAS_EXECUTION: N IN_FILTER_ROWS: N IN_RPC_ENCODE: N IN_RPC_DECODE: N IN_CONNECTION_MGR: N PROGRAM: T1001_LogService MODULE: LogRestoreService ACTION: RemoteLogWriter CLIENT_ID: NULL BACKTRACE: NULL TM_DELTA_TIME: 1017574 TM_DELTA_CPU_TIME: 1017574 TM_DELTA_DB_TIME: 1017574 TOP_LEVEL_SQL_ID: NULL IN_PLSQL_COMPILATION: N IN_PLSQL_EXECUTION: N PLSQL_ENTRY_OBJECT_ID: NULL PLSQL_ENTRY_SUBPROGRAM_ID: NULL PLSQL_ENTRY_SUBPROGRAM_NAME: NULL PLSQL_OBJECT_ID: NULL PLSQL_SUBPROGRAM_ID: NULL PLSQL_SUBPROGRAM_NAME: NULL BLOCKING_SESSION_ID: NULL TABLET_ID: NULL PROXY_SID: -9223372036854729409 TX_ID: NULL DELTA_READ_IO_REQUESTS: 0 DELTA_READ_IO_BYTES: 0 DELTA_WRITE_IO_REQUESTS: 0 DELTA_WRITE_IO_BYTES: 0 1 row in set (0.052 sec)The following table describes the fields:
Field Description SVR_IP The IP address of the server where the sample is located. SVR_PORT The port of the server where the sample is located. SAMPLE_ID The unique ID of the sample on the local server. SAMPLE_TIME The sampling time. CON_ID The ID of the tenant. USER_ID The user ID of the sampled session. SESSION_ID The ID of the sampled session. SESSION_TYPE The type of the session. - FOREGROUND: a foreground session, that is, a user session
- (Not supported) BACKGROUND: a background session.
SESSION_STATE The status of the session at the sampling time. - ON CPU: the session is not waiting and is executing SQL logic.
- WAITING: the session is waiting. For more information about the wait events, see the EVENT field.
SQL_ID The ID of the SQL statement. PLAN_ID The ID of the plan in the plan cache to which the sampled SQL statement belongs. This field 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 ID of the wait event, which is used to join with other tables. EVENT_ID The ID of the current wait event. P1TEXT The name of wait event parameter 1. The name varies depending on the wait event. P1 The value of wait event parameter 1. P2TEXT The name of wait event parameter 2. The name varies depending on the wait event. P2 The value of wait event parameter 2. P3TEXT The name of wait event parameter 3. The name varies depending on the wait event. P3 The value of wait event parameter 3. WAIT_CLASS The type of the wait event. WAIT_CLASS_ID The ID of the wait event type, which is used to join with other tables. TIME_WAITED The total waiting time of the wait event, in microseconds (us). SQL_PLAN_LINE_ID The ID of the SQL operator in the SQL plan at the sampling time. The value is NULL if there is no corresponding operator. GROUP_ID The group information of the sampling point. PLAN_HASH The plan hash value of the current SQL statement. THREAD_ID The ID of the thread on which the active session resides. STMT_TYPE The type of the SQL statement of the active session. IN_PARSE Indicates whether the session is parsing an SQL statement at the sampling time. IN_PL_PARSE Indicates whether the session is parsing an SQL PL statement at the sampling time. IN_PLAN_CACHE Indicates whether the session is matching a plan in the plan cache at the sampling time. IN_SQL_OPTIMIZE Indicates whether the session is optimizing an SQL statement at the sampling time. IN_SQL_EXECUTION Indicates whether the session is executing an SQL statement at the sampling time. IN_PX_EXECUTION Indicates whether the session is executing an SQL statement in parallel at the sampling time. If the session is in this state, it is also in the IN_SQL_EXECUTION state. IN_SEQUENCE_LOAD Indicates whether the session is fetching values from an auto-increment column or a sequence at the sampling time. IN_COMMITTING Indicates whether the sampling point is in the transaction commit phase. IN_STORAGE_READ Indicates whether the sampling point is in the storage read phase. IN_STORAGE_WRITE Indicates whether the sampling point is in the storage write phase. IN_REMOTE_DAS_EXECUTION Indicates whether the sampling point is in the DAS remote execution phase. IN_FILTER_ROWS Indicates whether the sampling point is in the storage downpress execution phase. IN_RPC_ENCODE The serialization operation currently being performed by the SQL. IN_RPC_DECODE The deserialization operation currently being performed by the SQL. IN_CONNECTION_MGR The connection establishment operation currently being performed by the SQL. PROGRAM The name of the program being executed at the sampling point: - For background threads: the name of the thread, such as
observerandWrTimer. - For foreground threads:
- For ordinary requests:
user@client_ip (thread_name) - For
inner_sqlremote executions:INNER SQL REMOTE EXEC (thread_name) - For DAS remote executions:
DAS REMOTE EXEC (thread_name)
- For ordinary requests:
MODULE The value of the MODULE field recorded for the session at the sampling time, which is set by the DBMS_APPLICATION_INFO.SET_MODULEpackage.ACTION The value of the ACTION field recorded for the session at the sampling time, which is set by the DBMS_APPLICATION_INFO.SET_ACTIONpackage.CLIENT_ID The value of the CLIENT_ID field recorded for the session at the sampling time, which is set by the DBMS_SESSION.set_identifierpackage.BACKTRACE The code call stack at the time of the event, which is used for auxiliary debugging. The value of this field is always NULL. TM_DELTA_TIME The time interval for calculating the time model, in microseconds. TM_DELTA_CPU_TIME The time spent on CPU in the past TM_DELTA_TIMEtime period, in microseconds.TM_DELTA_DB_TIME The time spent in database calls in the past TM_DELTA_TIMEtime period, in microseconds.TOP_LEVEL_SQL_ID The ID of the top-level SQL statement. IN_PLSQL_COMPILATION The PL compilation status: Y/N IN_PLSQL_EXECUTION The PL execution status: Y/N PLSQL_ENTRY_OBJECT_ID The ID of the top-level PL object. PLSQL_ENTRY_SUBPROGRAM_ID The ID of the top-level PL subprogram. PLSQL_ENTRY_SUBPROGRAM_NAME The name of the top-level PL subprogram. PLSQL_OBJECT_ID The ID of the PL object being executed. PLSQL_SUBPROGRAM_ID The ID of the PL subprogram being executed. PLSQL_SUBPROGRAM_NAME The name of the PL subprogram being executed. BLOCKING_SESSION_ID The ID of the session that is blocking the current session. This field is displayed only in scenarios of lock conflict and shows the ID of the session that holds the lock. TABLET_ID The ID of the tablet being processed by the current SQL statement. PROXY_SID The ID of the proxy session. TX_ID The ID of the current transaction. DELTA_READ_IO_REQUESTS The number of read operations between two sampling points. DELTA_READ_IO_BYTES The total size of files read between two sampling points. DELTA_WRITE_IO_REQUESTS The number of write operations between two sampling points. DELTA_WRITE_IO_BYTES The total size of files written between two sampling points. For more information about the
GV$OB_ACTIVE_SESSION_HISTORYview, see GV$OB_ACTIVE_SESSION_HISTORY.Use the
V$OB_ACTIVE_SESSION_HISTORYview to view the history of active sessions.obclient [oceanbase]> SELECT * FROM oceanbase.V$OB_ACTIVE_SESSION_HISTORY limit 1\GThe query result is as follows:
*************************** 1. row *************************** SVR_IP: xx.xx.xx.xx SVR_PORT: 2882 SAMPLE_ID: 1919434 SAMPLE_TIME: 2025-03-04 10:44:17.593988 CON_ID: 1001 USER_ID: 0 SESSION_ID: -9223372036854729409 SESSION_TYPE: BACKGROUND SESSION_STATE: ON CPU SQL_ID: NULL PLAN_ID: 0 TRACE_ID: NULL EVENT: EVENT_NO: 0 EVENT_ID: NULL P1TEXT: P1: 0 P2TEXT: P2: 0 P3TEXT: P3: 0 WAIT_CLASS: OTHER WAIT_CLASS_ID: 100 TIME_WAITED: 0 SQL_PLAN_LINE_ID: NULL GROUP_ID: 0 PLAN_HASH: NULL THREAD_ID: 82216 STMT_TYPE: NULL TIME_MODEL: 0 IN_PARSE: N IN_PL_PARSE: N IN_PLAN_CACHE: N IN_SQL_OPTIMIZE: N IN_SQL_EXECUTION: N IN_PX_EXECUTION: N IN_SEQUENCE_LOAD: N IN_COMMITTING: N IN_STORAGE_READ: N IN_STORAGE_WRITE: N IN_REMOTE_DAS_EXECUTION: N IN_FILTER_ROWS: N IN_RPC_ENCODE: N IN_RPC_DECODE: N IN_CONNECTION_MGR: N PROGRAM: T1001_LogService MODULE: LogRestoreService ACTION: RemoteLogWriter CLIENT_ID: NULL BACKTRACE: NULL TM_DELTA_TIME: 1017436 TM_DELTA_CPU_TIME: 1017436 TM_DELTA_DB_TIME: 1017436 TOP_LEVEL_SQL_ID: NULL IN_PLSQL_COMPILATION: N IN_PLSQL_EXECUTION: N PLSQL_ENTRY_OBJECT_ID: NULL PLSQL_ENTRY_SUBPROGRAM_ID: NULL PLSQL_ENTRY_SUBPROGRAM_NAME: NULL PLSQL_OBJECT_ID: NULL PLSQL_SUBPROGRAM_ID: NULL PLSQL_SUBPROGRAM_NAME: NULL BLOCKING_SESSION_ID: NULL TABLET_ID: NULL PROXY_SID: -9223372036854729409 TX_ID: NULL DELTA_READ_IO_REQUESTS: 0 DELTA_READ_IO_BYTES: 0 DELTA_WRITE_IO_REQUESTS: 0 DELTA_WRITE_IO_BYTES: 0 1 row in set (0.026 sec)The following table describes the fields:
Field Description SVR_IP The IP address of the server where the sample is located. SVR_PORT The port of the server where the sample is located. SAMPLE_ID The unique ID of the sample on the local server. SAMPLE_TIME The sampling time. CON_ID The ID of the tenant. USER_ID The user ID of the sampled session. SESSION_ID The ID of the sampled session. SESSION_TYPE The type of the session. - FOREGROUND: a foreground session, that is, a user session
- (Not supported) BACKGROUND: a background session.
SESSION_STATE The status of the session at the sampling time. - ON CPU: the session is not waiting and is executing SQL logic.
- WAITING: the session is waiting. For more information about the wait events, see the EVENT field.
SQL_ID The ID of the SQL statement. PLAN_ID The ID of the plan in the plan cache to which the sampled SQL statement belongs. This field 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 ID of the wait event, which is used to join with other tables. EVENT_ID The ID of the current wait event. P1TEXT The name of wait event parameter 1. The name varies depending on the wait event. P1 The value of wait event parameter 1. P2TEXT The name of wait event parameter 2. The name varies depending on the wait event. P2 The value of wait event parameter 2. P3TEXT The name of wait event parameter 3. The name varies depending on the wait event. P3 The value of wait event parameter 3. WAIT_CLASS The type of the wait event. WAIT_CLASS_ID The ID of the wait event type, which is used to join with other tables. TIME_WAITED The total waiting time of the wait event, in microseconds (us). SQL_PLAN_LINE_ID The ID of the SQL operator in the SQL plan at the sampling time. The value is NULL if there is no corresponding operator. GROUP_ID The group information of the sampling point. PLAN_HASH bigint(20) unsigned NO The plan hash value of the current SQL statement. THREAD_ID The ID of the thread on which the active session resides. STMT_TYPE The type of the SQL statement of the active session. IN_PARSE Indicates whether the session is parsing an SQL statement at the sampling time. IN_PL_PARSE Indicates whether the session is parsing an SQL PL statement at the sampling time. IN_PLAN_CACHE Indicates whether the session is matching a plan in the plan cache at the sampling time. IN_SQL_OPTIMIZE Indicates whether the session is optimizing an SQL statement at the sampling time. IN_SQL_EXECUTION Indicates whether the session is executing an SQL statement at the sampling time. IN_PX_EXECUTION Indicates whether the session is executing an SQL statement in parallel at the sampling time. If the session is in this state, it is also in the IN_SQL_EXECUTION state. IN_SEQUENCE_LOAD Indicates whether the session is fetching values from an auto-increment column or a sequence at the sampling time. IN_COMMITTING Indicates whether the sampling point is in the transaction commit phase. IN_STORAGE_READ Indicates whether the sampling point is in the storage read phase. IN_STORAGE_WRITE Indicates whether the sampling point is in the storage write phase. IN_REMOTE_DAS_EXECUTION Indicates whether the sampling point is in the DAS remote execution phase. IN_FILTER_ROWS Indicates whether the sampling point is in the storage downpress execution phase. IN_RPC_ENCODE The serialization operation currently being performed by the SQL. IN_RPC_DECODE The deserialization operation currently being performed by the SQL. IN_CONNECTION_MGR The connection establishment operation currently being performed by the SQL. PROGRAM The name of the program being executed at the sampling point: - For background threads: the name of the thread, such as
observerandWrTimer. - For foreground threads:
- For ordinary requests:
user@client_ip (thread_name) - For
inner_sqlremote executions:INNER SQL REMOTE EXEC (thread_name) - For DAS remote executions:
DAS REMOTE EXEC (thread_name)
- For ordinary requests:
MODULE The value of the MODULE field recorded for the session at the sampling time, which is set by the DBMS_APPLICATION_INFO.SET_MODULEpackage.ACTION The value of the ACTION field recorded for the session at the sampling time, which is set by the DBMS_APPLICATION_INFO.SET_ACTIONpackage.CLIENT_ID The value of the CLIENT_ID field recorded for the session at the sampling time, which is set by the DBMS_SESSION.set_identifierpackage.BACKTRACE varchar(512) NO The code call stack at the time of the event, which is used for auxiliary debugging. The value of this field is always NULL. TM_DELTA_TIME The time interval for calculating the time model, in microseconds. TM_DELTA_CPU_TIME The time spent on CPU in the past TM_DELTA_TIMEtime period, in microseconds.TM_DELTA_DB_TIME The time spent in database calls in the past TM_DELTA_TIMEtime period, in microseconds.TOP_LEVEL_SQL_ID The ID of the top-level SQL statement. IN_PLSQL_COMPILATION The PL compilation status: Y/N IN_PLSQL_EXECUTION The PL execution status: Y/N PLSQL_ENTRY_OBJECT_ID The ID of the top-level PL object. PLSQL_ENTRY_SUBPROGRAM_ID The ID of the top-level PL subprogram. PLSQL_ENTRY_SUBPROGRAM_NAME The name of the top-level PL subprogram. PLSQL_OBJECT_ID The ID of the PL object being executed. PLSQL_SUBPROGRAM_ID The ID of the PL subprogram being executed. PLSQL_SUBPROGRAM_NAME The name of the PL subprogram being executed. BLOCKING_SESSION_ID The ID of the session that is blocking the current session. This field is displayed only in scenarios of lock conflict and shows the ID of the session that holds the lock. TABLET_ID The ID of the tablet being processed by the current SQL statement. PROXY_SID The ID of the proxy session. TX_ID The ID of the current transaction. DELTA_READ_IO_REQUESTS The number of read operations between two sampling points. DELTA_READ_IO_BYTES The total size of files read between two sampling points. DELTA_WRITE_IO_REQUESTS The number of write operations between two sampling points. DELTA_WRITE_IO_BYTES The total size of files written between two sampling points. For more information about the
V$OB_ACTIVE_SESSION_HISTORYview, see V$OB_ACTIVE_SESSION_HISTORY.
View historical session statistics
Use the
oceanbase.CDB_WR_STATNAMEview to view the names of system statistics for 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 Cluster ID TENANT_ID Tenant ID STAT_ID Statistic ID STAT_NAME Statistic name For more information about the oceanbase.CDB_WR_STATNAME view, see oceanbase.CDB_WR_STATNAME.
Use the
oceanbase.DBA_WR_STATNAMEview to view the names of system statistics for 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 Cluster ID TENANT_ID Tenant ID STAT_ID Statistic ID STAT_NAME Statistic name For more information about the DBA_WR_STATNAME view, see oceanbase.DBA_WR_STATNAME and DBA_WR_STATNAME.
Use the
oceanbase.CDB_WR_SYSSTATview to view the detailed information of system statistics for 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 Cluster ID TENANT_ID Tenant ID SNAP_ID Snapshot ID SVR_IP Node IP SVR_PORT Node PORT STAT_ID Statistic ID VALUE Statistic value For more information about the oceanbase.CDB_WR_SYSSTAT view, see oceanbase.CDB_WR_SYSSTAT.
Use the
oceanbase.DBA_WR_SYSSTATview to view the detailed information of system statistics for 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 Cluster ID TENANT_ID Tenant ID SNAP_ID Snapshot ID SVR_IP Node IP SVR_PORT Node PORT STAT_ID Statistic ID VALUE Statistic value For more information about the DBA_WR_SYSSTAT view, see oceanbase.DBA_WR_SYSSTAT and DBA_WR_SYSSTAT.
View time model statistics
Use the
GV$OB_SESS_TIME_MODELview to view session-level time model statistics 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 ID of the session. TENANT_ID The ID of the tenant. SVR_IP The IP address of the OBServer node. SVR_PORT The port number of the OBServer node. 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.Use the
V$OB_SESS_TIME_MODELview to view session-level time model statistics 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 ID of the session. TENANT_ID The ID of the tenant. SVR_IP The IP address of the OBServer node. SVR_PORT The port number of the OBServer node. 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.Use the
GV$OB_SYS_TIME_MODELview to view tenant-level time model statistics 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 OBServer node. SVR_PORT The port number of the OBServer node. 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.Use the
V$OB_SYS_TIME_MODELview to view tenant-level time model statistics 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 OBServer node. SVR_PORT The port number of the OBServer node. 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.Use the
oceanbase.CDB_WR_SYS_TIME_MODELview to view WR data of all tenant time model statistics.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 OBServer node. SVR_PORT The port number of the OBServer node. 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_SYSSTATview, see oceanbase.CDB_WR_SYS_TIME_MODEL.Use the
oceanbase.DBA_WR_SYS_TIME_MODELview to view WR data of tenant-level time model statistics in 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 OBServer node. SVR_PORT The port number of the OBServer node. 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_STATNAMEview, see oceanbase.DBA_WR_SYS_TIME_MODEL and DBA_WR_SYS_TIME_MODEL.
View SQLSTAT data
Use the
GV$OB_SQLSTATview to view the basic performance statistics of SQL statements in the cluster.Each SQL statement is a row (that is, each unique combination of
SQL_IDandPlan_Hashis a row). The statistics with_DELTAin their names indicate the incremental statistics collected from the last WR snapshot to the current 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 node. SVR_PORT The port of the node. 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 The type of the plan. For SQL plans in the plan cache, this field indicates the type of the plan: 1: local plan2: remote plan3: distributed plan
For PL objects in the PL object cache, this field indicates the type of the PL object:1: procedure2: function3: package4: anonymous block
QUERY_SQL The full SQL statement. SQL_TYPE The statement type. MODULE The application module when the statement was parsed for the first time. This field is set by the DBMS_APPLICATION_INFO.SET_MODULEpackage.ACTION The application action when the statement was parsed for the first time. This field is set by the DBMS_APPLICATION_INFO.SET_ACTIONpackage.PARSING_DB_ID The ID of the database when the statement was parsed. PARSING_DB_NAME The name of the database when the statement was parsed. PARSING_USER_ID The ID of the user when the statement was parsed. EXECUTIONS_TOTAL The total number of times the plan in the plan cache was executed. EXECUTIONS_DELTA The incremental number of times the plan in the plan cache was executed. 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 the cache. BUFFER_GETS_DELTA The incremental number of logical reads from the cache. ELAPSED_TIME_TOTAL The total time consumed by the statement. For parallel execution scenarios, this value is the cumulative value for each execution thread. 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 duration of concurrent wait events. CCWAIT_DELTA The incremental duration of concurrent wait events. USERIO_WAIT_TOTAL The total duration of I/O waits. USERIO_WAIT_DELTA The incremental duration of I/O waits. APWAIT_TOTAL The total duration of application wait events. APWAIT_DELTA The incremental duration of application wait events. 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 bytes read physically. PHYSICAL_READ_BYTES_DELTA The incremental number of bytes read physically. WRITE_THROTTLE_TOTAL The total duration for which data was throttled when written to the MemStore. WRITE_THROTTLE_DELTA The incremental duration for which data was throttled when written to the MemStore. 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 the MemStore. MEMSTORE_READ_ROWS_DELTA The incremental number of rows read from the MemStore. MINOR_SSSTORE_READ_ROWS_TOTAL The total number of rows read from the MINOR SSStore. MINOR_SSSTORE_READ_ROWS_DELTA The incremental number of rows read from the MINOR SSStore. MAJOR_SSSTORE_READ_ROWS_TOTAL The total number of rows read from the MAJOR SSStore. MAJOR_SSSTORE_READ_ROWS_DELTA The incremental number of rows read from the 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 by the SQL statement. PARTITION_DELTA The incremental number of partitions scanned by the SQL statement. NESTED_SQL_TOTAL The total number of nested SQL statements executed. Each time a sub-SQL statement is executed, the value is increased by 1. NESTED_SQL_DELTA The incremental number of nested SQL statements executed. SOURCE_IP The IP address of the request source. SOURCE_PORT The port of the request source. For more information about the
GV$OB_SQLSTATview, see GV$OB_SQLSTAT.Use the
V$OB_SQLSTATview to view the basic performance statistics of SQL statements on the current node.Each SQL statement is a row (that is, each unique combination of
SQL_IDandPlan_Hashis a row). The statistics with_DELTAin their names indicate the incremental statistics collected from the last WR snapshot to the current 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 node. SVR_PORT The port of the node. 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 The type of the plan. For SQL plans in the plan cache, this field indicates the type of the plan: 1: local plan2: remote plan3: distributed plan
For PL objects in the PL object cache, this field indicates the type of the PL object:1: procedure2: function3: package4: anonymous block
QUERY_SQL The full SQL statement. SQL_TYPE The statement type. MODULE The application module when the statement was parsed for the first time. This field is set by the DBMS_APPLICATION_INFO.SET_MODULEpackage.ACTION The application action when the statement was parsed for the first time. This field is set by the DBMS_APPLICATION_INFO.SET_ACTIONpackage.PARSING_DB_ID The ID of the database when the statement was parsed. PARSING_DB_NAME The name of the database when the statement was parsed. PARSING_USER_ID The ID of the user when the statement was parsed. EXECUTIONS_TOTAL The total number of times the plan in the plan cache was executed. EXECUTIONS_DELTA The incremental number of times the plan in the plan cache was executed. 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 the cache. BUFFER_GETS_DELTA The incremental number of logical reads from the cache. ELAPSED_TIME_TOTAL The total time consumed by the statement. For parallel execution scenarios, this value is the cumulative value for each execution thread. 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 duration of concurrent wait events. CCWAIT_DELTA The incremental duration of concurrent wait events. USERIO_WAIT_TOTAL The total duration of I/O waits. USERIO_WAIT_DELTA The incremental duration of I/O waits. APWAIT_TOTAL The total duration of application wait events. APWAIT_DELTA The incremental duration of application wait events. 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 bytes read physically. PHYSICAL_READ_BYTES_DELTA The incremental number of bytes read physically. WRITE_THROTTLE_TOTAL The total duration for which data was throttled when written to the MemStore. WRITE_THROTTLE_DELTA The incremental duration for which data was throttled when written to the MemStore. 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 the MemStore. MEMSTORE_READ_ROWS_DELTA The incremental number of rows read from the MemStore. MINOR_SSSTORE_READ_ROWS_TOTAL The total number of rows read from the MINOR SSStore. MINOR_SSSTORE_READ_ROWS_DELTA The incremental number of rows read from the MINOR SSStore. MAJOR_SSSTORE_READ_ROWS_TOTAL The total number of rows read from the MAJOR SSStore. MAJOR_SSSTORE_READ_ROWS_DELTA The incremental number of rows read from the 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 by the SQL statement. PARTITION_DELTA The incremental number of partitions scanned by the SQL statement. NESTED_SQL_TOTAL The total number of nested SQL statements executed. Each time a sub-SQL statement is executed, the value is increased by 1. NESTED_SQL_DELTA The incremental number of nested SQL statements executed. SOURCE_IP The IP address of the request source. SOURCE_PORT The port of the request source. For more information about the
V$OB_SQLSTATview, see V$OB_SQLSTAT.Use the
oceanbase.CDB_WR_SQLSTATview to view the basic performance statistics of all SQL statements executed in all tenants.Each SQL statement is a row (that is, each unique combination of
SQL_IDandPlan_Hashis a row). The statistics with_DELTAin their names indicate the incremental statistics collected from the last WR snapshot to the current 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 node. SVR_PORT The port of the node. 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 The type of the plan. For SQL plans in the plan cache, this field indicates the type of the plan: 1: local plan2: remote plan3: distributed plan
For PL objects in the PL object cache, this field indicates the type of the PL object:1: procedure2: function3: package4: anonymous block
MODULE The application module when the statement was parsed for the first time. This field is set by the DBMS_APPLICATION_INFO.SET_MODULEpackage.ACTION The application action when the statement was parsed for the first time. This field is set by the DBMS_APPLICATION_INFO.SET_ACTIONpackage.PARSING_DB_ID The ID of the database when the statement was parsed. PARSING_DB_NAME The name of the database when the statement was parsed. PARSING_USER_ID The ID of the user when the statement was parsed. EXECUTIONS_TOTAL The total number of times the plan in the plan cache was executed. EXECUTIONS_DELTA The incremental number of times the plan in the plan cache was executed. 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 the cache. BUFFER_GETS_DELTA The incremental number of logical reads from the cache. ELAPSED_TIME_TOTAL The total time consumed by the statement. For parallel execution scenarios, this value is the cumulative value for each execution thread. 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 duration of concurrent wait events. CCWAIT_DELTA The incremental duration of concurrent wait events. USERIO_WAIT_TOTAL The total duration of I/O waits. USERIO_WAIT_DELTA The incremental duration of I/O waits. APWAIT_TOTAL The total duration of application wait events. APWAIT_DELTA The incremental duration of application wait events. 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 bytes read physically. PHYSICAL_READ_BYTES_DELTA The incremental number of bytes read physically. WRITE_THROTTLE_TOTAL The total duration for which data was throttled when written to the MemStore. WRITE_THROTTLE_DELTA The incremental duration for which data was throttled when written to the MemStore. 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 the MemStore. MEMSTORE_READ_ROWS_DELTA The incremental number of rows read from the MemStore. MINOR_SSSTORE_READ_ROWS_TOTAL The total number of rows read from the MINOR SSStore. MINOR_SSSTORE_READ_ROWS_DELTA The incremental number of rows read from the MINOR SSStore. MAJOR_SSSTORE_READ_ROWS_TOTAL The total number of rows read from the MAJOR SSStore. MAJOR_SSSTORE_READ_ROWS_DELTA The incremental number of rows read from the 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 by the SQL statement. PARTITION_DELTA The incremental number of partitions scanned by the SQL statement. NESTED_SQL_TOTAL The total number of nested SQL statements executed. Each time a sub-SQL statement is executed, the value is increased by 1. NESTED_SQL_DELTA The incremental number of nested SQL statements executed. SOURCE_IP The IP address of the request source. SOURCE_PORT The port of the request source. For more information about the
oceanbase.CDB_WR_SQLSTATview, see oceanbase.CDB_WR_SQLSTAT.Use the
oceanbase.DBA_WR_SQLSTATview to view the basic performance statistics of all SQL statements executed in the tenant.The statistics with
_DELTAin their names indicate the incremental statistics collected from the last WR snapshot to the current 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 node. SVR_PORT The port of the node. 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 The type of the plan. For SQL plans in the plan cache, this field indicates the type of the plan: 1: local plan2: remote plan3: distributed plan
For PL objects in the PL object cache, this field indicates the type of the PL object:1: procedure2: function3: package4: anonymous block
MODULE The application module when the statement was parsed for the first time. This field is set by the DBMS_APPLICATION_INFO.SET_MODULEpackage.ACTION The application action when the statement was parsed for the first time. This field is set by the DBMS_APPLICATION_INFO.SET_ACTIONpackage.PARSING_DB_ID The ID of the database when the statement was parsed. PARSING_DB_NAME The name of the database when the statement was parsed. PARSING_USER_ID The ID of the user when the statement was parsed. EXECUTIONS_TOTAL The total number of times the plan in the plan cache was executed. EXECUTIONS_DELTA The incremental number of times the plan in the plan cache was executed. 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 the cache. BUFFER_GETS_DELTA The incremental number of logical reads from the cache. ELAPSED_TIME_TOTAL The total time consumed by the statement. For parallel execution scenarios, this value is the cumulative value for each execution thread. 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 duration of concurrent wait events. CCWAIT_DELTA The incremental duration of concurrent wait events. USERIO_WAIT_TOTAL The total duration of I/O waits. USERIO_WAIT_DELTA The incremental duration of I/O waits. APWAIT_TOTAL The total duration of application wait events. APWAIT_DELTA The incremental duration of application wait events. 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 bytes read physically. PHYSICAL_READ_BYTES_DELTA The incremental number of bytes read physically. WRITE_THROTTLE_TOTAL The total duration for which data was throttled when written to the MemStore. WRITE_THROTTLE_DELTA The incremental duration for which data was throttled when written to the MemStore. 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 the MemStore. MEMSTORE_READ_ROWS_DELTA The incremental number of rows read from the MemStore. MINOR_SSSTORE_READ_ROWS_TOTAL The total number of rows read from the MINOR SSStore. MINOR_SSSTORE_READ_ROWS_DELTA The incremental number of rows read from the MINOR SSStore. MAJOR_SSSTORE_READ_ROWS_TOTAL The total number of rows read from the MAJOR SSStore. MAJOR_SSSTORE_READ_ROWS_DELTA The incremental number of rows read from the 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 by the SQL statement. PARTITION_DELTA The incremental number of partitions scanned by the SQL statement. NESTED_SQL_TOTAL The total number of nested SQL statements executed. Each time a sub-SQL statement is executed, the value is increased by 1. NESTED_SQL_DELTA The incremental number of nested SQL statements executed. SOURCE_IP VARCHAR2(46) SOURCE_PORT The port of the request source. For more information about the
DBA_WR_SQLSTATview, see oceanbase.DBA_WR_SQLSTAT and DBA_WR_SQLSTAT.
View information about resource groups
You can query the statistics of resource groups in all OBServers by using the
GV$OB_RES_MGR_SYSSTATview.obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_RES_MGR_SYSSTAT LIMIT 10;The query result is as follows:
+--------+----------+----------------+----------+------------+-----------------------+-------+------------+------------+---------+ | CON_ID | GROUP_ID | SVR_IP | SVR_PORT | STATISTIC# | NAME | CLASS | VALUE | VALUE_TYPE | STAT_ID | +--------+----------+----------------+----------+------------+-----------------------+-------+------------+------------+---------+ | 1 | 0 | xx.xx.xx.xx | 2882 | 0 | rpc packet in | 1 | 12995014 | ADD_VALUE | 10000 | | 1 | 0 | xx.xx.xx.xx | 2882 | 1 | rpc packet in bytes | 1 | 3080600714 | ADD_VALUE | 10001 | | 1 | 0 | xx.xx.xx.xx | 2882 | 2 | rpc packet out | 1 | 7975192 | ADD_VALUE | 10002 | | 1 | 0 | xx.xx.xx.xx | 2882 | 3 | rpc packet out bytes | 1 | 2401633337 | ADD_VALUE | 10003 | | 1 | 0 | xx.xx.xx.xx | 2882 | 4 | rpc deliver fail | 1 | 0 | ADD_VALUE | 10004 | | 1 | 0 | xx.xx.xx.xx | 2882 | 5 | rpc net delay | 1 | 23735015 | ADD_VALUE | 10005 | | 1 | 0 | xx.xx.xx.xx | 2882 | 6 | rpc net frame delay | 1 | 240711 | ADD_VALUE | 10006 | | 1 | 0 | xx.xx.xx.xx | 2882 | 7 | mysql packet in | 1 | 66 | ADD_VALUE | 10007 | | 1 | 0 | xx.xx.xx.xx | 2882 | 8 | mysql packet in bytes | 1 | 13725 | ADD_VALUE | 10008 | | 1 | 0 | xx.xx.xx.xx | 2882 | 9 | mysql packet out | 1 | 86 | ADD_VALUE | 10009 | +--------+----------+----------------+----------+------------+-----------------------+-------+------------+------------+---------+ 10 rows in set (0.026 sec)The following table describes the fields:
Field Description CON_ID Tenant ID GROUP_ID The consumer group ID SVR_IP IP address of the node SVR_PORT Port of the node STATISTIC# Statistics ID NAME Statistics name CLASS Statistics category VALUE Statistics value VALUE_TYPE Value type: cumulative or assigned STAT_ID Statistics ID For more information about the
GV$OB_RES_MGR_SYSSTATview, see GV$OB_RES_MGR_SYSSTAT.You can query the statistics of resource groups in the current OBServer by using the
V$OB_RES_MGR_SYSSTATview.obclient [oceanbase]> SELECT * FROM oceanbase.V$OB_RES_MGR_SYSSTAT LIMIT 10;The query result is as follows:
+--------+----------+----------------+----------+------------+-----------------------+-------+------------+------------+---------+ | CON_ID | GROUP_ID | SVR_IP | SVR_PORT | STATISTIC# | NAME | CLASS | VALUE | VALUE_TYPE | STAT_ID | +--------+----------+----------------+----------+------------+-----------------------+-------+------------+------------+---------+ | 1 | 0 | xx.xx.xx.xx | 2882 | 0 | rpc packet in | 1 | 13010269 | ADD_VALUE | 10000 | | 1 | 0 | xx.xx.xx.xx | 2882 | 1 | rpc packet in bytes | 1 | 3084188226 | ADD_VALUE | 10001 | | 1 | 0 | xx.xx.xx.xx | 2882 | 2 | rpc packet out | 1 | 7984551 | ADD_VALUE | 10002 | | 1 | 0 | xx.xx.xx.xx | 2882 | 3 | rpc packet out bytes | 1 | 2404425894 | ADD_VALUE | 10003 | | 1 | 0 | xx.xx.xx.xx | 2882 | 4 | rpc deliver fail | 1 | 0 | ADD_VALUE | 10004 | | 1 | 0 | xx.xx.xx.xx | 2882 | 5 | rpc net delay | 1 | 23766006 | ADD_VALUE | 10005 | | 1 | 0 | xx.xx.xx.xx | 2882 | 6 | rpc net frame delay | 1 | 241049 | ADD_VALUE | 10006 | | 1 | 0 | xx.xx.xx.xx | 2882 | 7 | mysql packet in | 1 | 69 | ADD_VALUE | 10007 | | 1 | 0 | xx.xx.xx.xx | 2882 | 8 | mysql packet in bytes | 1 | 13871 | ADD_VALUE | 10008 | | 1 | 0 | xx.xx.xx.xx | 2882 | 9 | mysql packet out | 1 | 142 | ADD_VALUE | 10009 | +--------+----------+----------------+----------+------------+-----------------------+-------+------------+------------+---------+ 10 rows in set (0.007 sec)The following table describes the fields:
Field Description CON_ID Tenant ID GROUP_ID The consumer group ID SVR_IP IP address of the node SVR_PORT Port of the node STATISTIC# Statistics ID NAME Statistics name CLASS Statistics category VALUE Statistics value VALUE_TYPE Value type: cumulative or assigned STAT_ID Statistics ID For more information about the
V$OB_RES_MGR_SYSSTATview, see V$OB_RES_MGR_SYSSTAT.