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
You can query the
oceanbase.CDB_WR_ACTIVE_SESSION_HISTORYview to obtain the ASH data of all tenants after it is persisted.obclient [oceanbase]> SELECT * FROM oceanbase.CDB_WR_ACTIVE_SESSION_HISTORY limit 1\GThe query 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 fields are described as follows:
Field |
Description |
|---|---|
| CLUSTER_ID | The cluster ID. |
| TENANT_ID | The tenant ID. |
| SNAP_ID | The snapshot ID. |
| SVR_IP | The IP address of the node. |
| SVR_PORT | The port number of the node. |
| SAMPLE_ID | The sampling ID. |
| SESSION_ID | The ID of the session that is being sampled. |
| SAMPLE_TIME | The sampling time. |
| USER_ID | The user ID of the session that is being sampled. |
| SESSION_TYPE | The session type.
|
| SESSION_STATE | The state of the session at the time of sampling.
|
| SQL_ID | The SQL ID. |
| TRACE_ID | The trace ID. |
| EVENT_NO | The internal number of the waiting event. This field is used for cross-table queries. |
| EVENT_ID | The ID of the waiting event. |
| TIME_WAITED | The total waiting time of the waiting event, in microseconds (us). |
| P1 | The value of the first parameter of the waiting event. |
| P2 | The value of the second parameter of the waiting event. |
| P3 | The value of the third parameter of the waiting event. |
| SQL_PLAN_LINE_ID | The ID of the SQL operator corresponding to the sampling point. If no corresponding operator exists, the value is NULL. |
| GROUP_ID | The group information of the sampling point. |
| PLAN_HASH | The plan hash corresponding to the executed SQL statement. |
| THREAD_ID | The thread ID of the active session. |
| STMT_TYPE | The SQL type of the active session. |
| TIME_MODEL | The information about the time model. |
| IN_PARSE | Indicates whether the current session is performing SQL parsing at the time of sampling. |
| IN_PL_PARSE | Indicates whether the current session is performing SQL PL parsing at the time of sampling. |
| IN_PLAN_CACHE | Indicates whether the current session is performing plan cache operations at the time of sampling. |
| IN_SQL_OPTIMIZE | Indicates whether the current session is performing SQL optimization at the time of sampling. |
| IN_SQL_EXECUTION | Indicates whether the current session is performing SQL execution at the time of sampling. |
| IN_PX_EXECUTION | Indicates whether the current session is performing parallel SQL execution at the time of sampling. If the session is in this state, it must also be in the IN_SQL_EXECUTION state. |
| IN_SEQUENCE_LOAD | Indicates whether the current session is fetching values from an auto-increment column or a sequence at the time of sampling. |
| IN_COMMITTING | Indicates whether the current sampling point is in the transaction commit stage. |
| IN_STORAGE_READ | Indicates whether the current sampling point is in the storage read stage. |
| IN_STORAGE_WRITE | Indicates whether the current sampling point is in the storage write stage. |
| IN_REMOTE_DAS_EXECUTION | Indicates whether the current sampling point is in the DAS remote execution stage. |
| IN_FILTER_ROWS | Indicates whether the current sampling point is in the storage push-down execution stage. |
| IN_RPC_ENCODE | The serialization operation being performed by the current SQL statement. |
| IN_RPC_DECODE | The deserialization operation being performed by the current SQL statement. |
| IN_CONNECTION_MGR | The link creation operation being performed by the current SQL statement. |
| PROGRAM | The name of the program being executed at the current sampling point:
|
| MODULE | The MODULE value recorded by the current session at the time of sampling. This value is set by using the DBMS_APPLICATION_INFO.SET_MODULE package. |
| ACTION | The ACTION value recorded by the current session at the time of sampling. This value is set by using the DBMS_APPLICATION_INFO.SET_ACTION package. |
| CLIENT_ID | The CLIENT_ID value recorded by the current session at the time of sampling. This value is set by using the DBMS_APPLICATION_INFO.set_identifier package. |
| BACKTRACE | A debugging field used to record the code call stack at the time of the event. |
| PLAN_ID | The plan ID of the sampled SQL statement in the plan cache. 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 amount of time spent on the CPU during the TM_DELTA_TIME time period. |
| TM_DELTA_DB_TIME | The amount of time spent on database calls during the TM_DELTA_TIME time period. |
| 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 OBJECT ID of the top-level PL statement. |
| PLSQL_ENTRY_SUBPROGRAM_ID | The subprogram ID of the top-level PL statement. |
| PLSQL_ENTRY_SUBPROGRAM_NAME | The subprogram name of the top-level PL statement. |
| 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 | If the current session is blocked, the ID of the session that blocks the current session is displayed. At present, this field is effective only in the case of lock conflicts. It displays 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 data 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 data written between two sampling points. |
For more information about the oceanbase.CDB_WR_ACTIVE_SESSION_HISTORY view, see oceanbase.CDB_WR_ACTIVE_SESSION_HISTORY.
Use the
oceanbase.DBA_WR_ACTIVE_SESSION_HISTORYview to query the ASH data persisted for the current tenant.obclient [oceanbase]> SELECT * FROM oceanbase.DBA_WR_ACTIVE_SESSION_HISTORY limit 1\GThe query 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 columns in the preceding query result.
Field |
Description |
|---|---|
| CLUSTER_ID | The ID of the cluster. |
| TENANT_ID | The ID of the tenant. |
| SNAP_ID | The snapshot ID. |
| SVR_IP | The IP address of the node. |
| SVR_PORT | The port number of the node. |
| SAMPLE_ID | The sampling ID. |
| SESSION_ID | The ID of the sampled session. |
| SAMPLE_TIME | The sampling time. |
| USER_ID | The ID of the user of the sampled session. |
| SESSION_TYPE | The type of the session.
|
| SESSION_STATE | The state of the session at the time of sampling.
|
| SQL_ID | The SQL ID. |
| TRACE_ID | TRACE_ID |
| EVENT_NO | The internal number of the waiting event, which is used to associate with other tables. |
| EVENT_ID | The ID of the current waiting event. |
| TIME_WAITED | The total waiting time of the waiting event, in microseconds (us). |
| P1 | The value of the first parameter of the waiting event. |
| P2 | The value of the second parameter of the waiting event. |
| P3 | The value of the third parameter of the waiting event. |
| SQL_PLAN_LINE_ID | The ID of the SQL operator corresponding to the sampled SQL. If no corresponding operator exists, the value is NULL. |
| GROUP_ID | The group information of the sampling point. |
| PLAN_HASH | The plan hash corresponding to the executed SQL command. |
| THREAD_ID | The ID of the thread where the active session is located. |
| STMT_TYPE | The SQL type of the active session. |
| TIME_MODEL | The information about the TIME MODEL. |
| IN_PARSE | Indicates whether the current session is parsing an SQL statement at the time of sampling. |
| IN_PL_PARSE | Indicates whether the current session is parsing an SQL PL statement at the time of sampling. |
| IN_PLAN_CACHE | Indicates whether the current session is using the plan cache at the time of sampling. |
| IN_SQL_OPTIMIZE | Indicates whether the current session is optimizing an SQL statement at the time of sampling. |
| IN_SQL_EXECUTION | Indicates whether the current session is executing an SQL statement at the time of sampling. |
| IN_PX_EXECUTION | Indicates whether the current session is executing an SQL statement in parallel at the time of sampling. If the session is in this state, it is also in the IN_SQL_EXECUTION state. |
| IN_SEQUENCE_LOAD | Indicates whether the current session is fetching values from an auto-increment column or a sequence at the time of 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 DAS remote execution phase. |
| IN_FILTER_ROWS | Indicates whether the current sampling point is in the storage push-down execution phase. |
| IN_RPC_ENCODE | The serialization operation being performed by the current SQL. |
| IN_RPC_DECODE | The deserialization operation being performed by the current SQL. |
| IN_CONNECTION_MGR | The connection establishment operation being performed by the current SQL. |
| PROGRAM | The name of the program being executed at the current sampling point:
|
| MODULE | The MODULE value recorded in the current session at the time of sampling. This value is set by using the DBMS_APPLICATION_INFO.SET_MODULE package. |
| ACTION | The ACTION value recorded in the current session at the time of sampling. This value is set by using the DBMS_APPLICATION_INFO.SET_ACTION package. |
| CLIENT_ID | The CLIENT_ID value recorded in the current session at the time of sampling. This value is set by using the DBMS_APPLICATION_INFO.set_identifier package. |
| BACKTRACE | A debugging field used to record the code call stack at the time of the event. |
| PLAN_ID | The plan ID of the sampled SQL in the plan cache. This ID 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 amount of time spent on the CPU during the TM_DELTA_TIME period. |
| TM_DELTA_DB_TIME | The amount of time spent on database calls during the TM_DELTA_TIME period. |
| 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 object ID of the top-level PL statement. |
| PLSQL_ENTRY_SUBPROGRAM_ID | The subprogram ID of the top-level PL statement. |
| PLSQL_ENTRY_SUBPROGRAM_NAME | The subprogram name of the top-level PL statement. |
| 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 | If the current session is blocked, this field displays the ID of the session that blocks the current session. Currently, this field is valid only in lock conflict scenarios, and it displays 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 read data 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 written data between two sampling points. |
For more information about the DBA_WR_ACTIVE_SESSION_HISTORY view, see oceanbase.DBA_WR_ACTIVE_SESSION_HISTORY and DBA_WR_ACTIVE_SESSION_HISTORY.
Obtain the active session history by querying the
GV$OB_ACTIVE_SESSION_HISTORYview.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 in the preceding query result.
Field |
Description |
|---|---|
| SVR_IP | IP address of the server to which the sample belongs. |
| SVR_PORT | Port number of the server to which the sample belongs. |
| SAMPLE_ID | Unique ID of the sample. |
| SAMPLE_TIME | Time when the sample was taken. |
| CON_ID | Tenant ID. |
| USER_ID | ID of the user of the session to be sampled. |
| SESSION_ID | ID of the session to be sampled. |
| SESSION_TYPE | Session type:
|
| SESSION_STATE | Session state:
|
| SQL_ID | SQL ID. |
| PLAN_ID | Plan ID of the SQL statement in the PLAN CACHE. This field is used to associate the sampling point with the plan. |
| TRACE_ID | Trace ID of the operator. |
| EVENT | Description of the waiting event. |
| EVENT_NO | Internal number of the waiting event. This field is used to query other tables. |
| EVENT_ID | ID of the current waiting event. |
| P1TEXT | Name of parameter 1 of the waiting event. The name varies with the value of the EVENT field. |
| P1 | Value of parameter 1 of the waiting event. |
| P2TEXT | Name of parameter 2 of the waiting event. The name varies with the value of the EVENT field. |
| P2 | Value of parameter 2 of the waiting event. |
| P3TEXT | Name of parameter 3 of the waiting event. The name varies with the value of the EVENT field. |
| P3 | Value of parameter 3 of the waiting event. |
| WAIT_CLASS | Type of the waiting event. |
| WAIT_CLASS_ID | ID of the type of the waiting event. This field is used to query other tables. |
| TIME_WAITED | Total waiting time of the waiting event, in microseconds (us). |
| SQL_PLAN_LINE_ID | Line ID of the SQL operator in the SQL plan of the session when the sample was taken. |
| GROUP_ID | Group information of the sampling point. |
| PLAN_HASH | Plan hash of the SQL statement being executed. |
| THREAD_ID | Thread ID of the active session. |
| STMT_TYPE | SQL type of the active session. |
| IN_PARSE | Indicates whether the session is parsing an SQL statement when the sample was taken. |
| IN_PL_PARSE | Indicates whether the session is parsing an SQL PL statement when the sample was taken. |
| IN_PLAN_CACHE | Indicates whether the session is matching a plan in the plan cache when the sample was taken. |
| IN_SQL_OPTIMIZE | Indicates whether the session is optimizing the SQL statement when the sample was taken. |
| IN_SQL_EXECUTION | Indicates whether the session is executing the SQL statement when the sample was taken. |
| IN_PX_EXECUTION | Indicates whether the session is executing the SQL statement in parallel when the sample was taken. If the session is in this state, it must also be in the IN_SQL_EXECUTION state. |
| IN_SEQUENCE_LOAD | Indicates whether the session is fetching values from an auto-increment column or a sequence when the sample was taken. |
| IN_COMMITTING | Indicates whether the sampling point is in the transaction commit stage. |
| IN_STORAGE_READ | Indicates whether the sampling point is in the storage read stage. |
| IN_STORAGE_WRITE | Indicates whether the sampling point is in the storage write stage. |
| IN_REMOTE_DAS_EXECUTION | Indicates whether the sampling point is in the DAS remote execution stage. |
| IN_FILTER_ROWS | Indicates whether the sampling point is in the storage push-down execution stage. |
| IN_RPC_ENCODE | Indicates whether the current SQL statement is being serialized. |
| IN_RPC_DECODE | Indicates whether the current SQL statement is being deserialized. |
| IN_CONNECTION_MGR | Indicates whether the current SQL statement is being established. |
| PROGRAM | Name of the program being executed at the sampling point:
|
| MODULE | MODULE value recorded at the sampling point. This value is set by using the DBMS_APPLICATION_INFO.SET_MODULE package. |
| ACTION | ACTION value recorded at the sampling point. This value is set by using the DBMS_APPLICATION_INFO.SET_ACTION package. |
| CLIENT_ID | CLIENT_ID value recorded at the sampling point. This value is set by using the DBMS_APPLICATION_INFO.set_identifier package. |
| BACKTRACE | Auxiliary debugging field used to record the code call stack when an event occurs. The value of this field is always NULL. |
| TM_DELTA_TIME | Time interval for calculating the time model, in microseconds. |
| TM_DELTA_CPU_TIME | Amount of time spent on the CPU in the TM_DELTA_TIME time interval. |
| TM_DELTA_DB_TIME | Amount of time spent on database calls in the TM_DELTA_TIME time interval. |
| TOP_LEVEL_SQL_ID | ID of the top-level SQL statement. |
| IN_PLSQL_COMPILATION | PL compilation status: Y/N. |
| IN_PLSQL_EXECUTION | PL execution status: Y/N. |
| PLSQL_ENTRY_OBJECT_ID | Object ID of the top-level PL statement. |
| PLSQL_ENTRY_SUBPROGRAM_ID | Subprogram ID of the top-level PL statement. |
| PLSQL_ENTRY_SUBPROGRAM_NAME | Subprogram name of the top-level PL statement. |
| PLSQL_OBJECT_ID | Object ID of the PL statement being executed. |
| PLSQL_SUBPROGRAM_ID | Subprogram ID of the PL statement being executed. |
| PLSQL_SUBPROGRAM_NAME | Subprogram name of the PL statement being executed. |
| BLOCKING_SESSION_ID | If the current session is blocked, the ID of the session that blocks the current session. This field is valid only in the lock conflict scenario, and displays the ID of the session that holds the lock. |
| TABLET_ID | ID of the tablet being processed by the current SQL statement. |
| PROXY_SID | ID of the proxy session. |
| TX_ID | ID of the current transaction. |
| DELTA_READ_IO_REQUESTS | Number of read requests between two sampling points. |
| DELTA_READ_IO_BYTES | Total size of read files between two sampling points. |
| DELTA_WRITE_IO_REQUESTS | Number of write requests between two sampling points. |
| DELTA_WRITE_IO_BYTES | Total size of written files between two sampling points. |
For more information about the `GV$OB_ACTIVE_SESSION_HISTORY` view, see [GV$OB_ACTIVE_SESSION_HISTORY](../../../700.system-views/400.system-view-of-mysql-mode/300.performance-view-of-mysql-mode/400.gv-ob_active_session_history-of-mysql-mode.md).
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 in the preceding query result.
Field |
Description |
|---|---|
| SVR_IP | IP address of the server to which the sample belongs |
| SVR_PORT | Port number of the server to which the sample belongs |
| SAMPLE_ID | Unique ID of the sample |
| SAMPLE_TIME | Time when the sample was collected |
| CON_ID | Tenant ID |
| USER_ID | ID of the user in the session from which the sample was collected |
| SESSION_ID | ID of the session from which the sample was collected |
| SESSION_TYPE | Session type:
|
| SESSION_STATE | Session state:
|
| SQL_ID | SQL ID |
| PLAN_ID | ID of the plan in the plan cache to which the sample belongs. This ID is used to associate the sample with the plan. |
| TRACE_ID | Trace ID of the operator |
| EVENT | Description of the wait event |
| EVENT_NO | Internal ID of the wait event. This ID is used to associate the wait event with other tables. |
| EVENT_ID | ID of the wait event |
| P1TEXT | Name of parameter 1 of the wait event. The name varies depending on the value of the EVENT field. |
| P1 | Value of parameter 1 of the wait event |
| P2TEXT | Name of parameter 2 of the wait event. The name varies depending on the value of the EVENT field. |
| P2 | Value of parameter 2 of the wait event |
| P3TEXT | Name of parameter 3 of the wait event. The name varies depending on the value of the EVENT field. |
| P3 | Value of parameter 3 of the wait event |
| WAIT_CLASS | Type of the wait event |
| WAIT_CLASS_ID | ID of the type of the wait event. This ID is used to associate the wait event with other tables. |
| TIME_WAITED | Total wait time of the wait event, in microseconds (us) |
| SQL_PLAN_LINE_ID | ID of the SQL operator in the SQL plan of the session when the sample was collected |
| GROUP_ID | Group information of the sample point |
| PLAN_HASH | Plan hash of the SQL command currently being executed |
| THREAD_ID | ID of the thread in which the active session resides |
| STMT_TYPE | SQL type of the active session |
| IN_PARSE | Indicates whether the session was parsing SQL when the sample was collected. |
| IN_PL_PARSE | Indicates whether the session was parsing SQL PL when the sample was collected. |
| IN_PLAN_CACHE | Indicates whether the session was matching a plan in the plan cache when the sample was collected. |
| IN_SQL_OPTIMIZE | Indicates whether the session was optimizing SQL when the sample was collected. |
| IN_SQL_EXECUTION | Indicates whether the session was executing SQL when the sample was collected. |
| IN_PX_EXECUTION | Indicates whether the session was executing SQL in parallel when the sample was collected. If the session is in this state, it must also be in the IN_SQL_EXECUTION state. |
| IN_SEQUENCE_LOAD | Indicates whether the session was retrieving values from an auto-increment column or a sequence when the sample was collected. |
| IN_COMMITTING | Indicates whether the sample was collected during transaction commit. |
| IN_STORAGE_READ | Indicates whether the sample was collected during storage read. |
| IN_STORAGE_WRITE | Indicates whether the sample was collected during storage write. |
| IN_REMOTE_DAS_EXECUTION | Indicates whether the sample was collected during DAS remote execution. |
| IN_FILTER_ROWS | Indicates whether the sample was collected during storage pushdown execution. |
| IN_RPC_ENCODE | Serialization operation currently being performed on the SQL statement |
| IN_RPC_DECODE | Deserialization operation currently being performed on the SQL statement |
| IN_CONNECTION_MGR | Connection operation currently being performed on the SQL statement |
| PROGRAM | Name of the program being executed at the sample point:
|
| MODULE | Value of the MODULE field recorded when the sample was collected. This value is set by using the DBMS_APPLICATION_INFO.SET_MODULE package. |
| ACTION | Value of the ACTION field recorded when the sample was collected. This value is set by using the DBMS_APPLICATION_INFO.SET_ACTION package. |
| CLIENT_ID | Value of the CLIENT_ID field recorded when the sample was collected. This value is set by using the DBMS_APPLICATION_INFO.set_identifier package. |
| BACKTRACE | Auxiliary debugging field used to record the code call stack when the event occurred. The value of this field is always NULL. |
| TM_DELTA_TIME | Time interval for calculating the time model, in microseconds |
| TM_DELTA_CPU_TIME | Amount of time spent on the CPU during the TM_DELTA_TIME period |
| TM_DELTA_DB_TIME | Amount of time spent on database calls during the TM_DELTA_TIME period |
| TOP_LEVEL_SQL_ID | ID of the top-level SQL statement |
| IN_PLSQL_COMPILATION | PL compilation status: Y/N |
| IN_PLSQL_EXECUTION | PL execution status: Y/N |
| PLSQL_ENTRY_OBJECT_ID | ID of the top-level PL object |
| PLSQL_ENTRY_SUBPROGRAM_ID | ID of the top-level PL subprogram |
| PLSQL_ENTRY_SUBPROGRAM_NAME | Name of the top-level PL subprogram |
| PLSQL_OBJECT_ID | ID of the PL object currently being executed |
| PLSQL_SUBPROGRAM_ID | ID of the PL subprogram currently being executed |
| PLSQL_SUBPROGRAM_NAME | Name of the PL subprogram currently being executed |
| BLOCKING_SESSION_ID | If the current session is blocked, the ID of the session that blocks the current session. This field is valid only in the case of lock conflicts. It displays the ID of the session that holds the lock. |
| TABLET_ID | ID of the tablet being processed by the current SQL statement |
| PROXY_SID | ID of the proxy session |
| TX_ID | ID of the current transaction |
| DELTA_READ_IO_REQUESTS | Number of read requests between two samples |
| DELTA_READ_IO_BYTES | Total size of read files between two samples |
| DELTA_WRITE_IO_REQUESTS | Number of write requests between two samples |
| DELTA_WRITE_IO_BYTES | Total size of written files between two samples |
For more information about the V$OB_ACTIVE_SESSION_HISTORY view, see V$OB_ACTIVE_SESSION_HISTORY.
View statistics of historical sessions
To view the names of all system statistics of all tenants, query the
oceanbase.CDB_WR_STATNAMEview.obclient [oceanbase]> SELECT * FROM oceanbase.CDB_WR_STATNAME limit 1;The query 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 columns in the preceding result set.
ColumnDescriptionCLUSTER_ID The ID of the cluster. TENANT_ID The ID of the tenant. STAT_ID The ID of the statistic. STAT_NAME The name of the statistic. For more information about the
oceanbase.CDB_WR_STATNAMEview, see oceanbase.CDB_WR_STATNAME.To view the names of system statistics of the current tenant, query the
oceanbase.DBA_WR_STATNAMEview.obclient [oceanbase]> SELECT * FROM oceanbase.DBA_WR_STATNAME limit 1;The query 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 columns in the preceding result set.
ColumnDescriptionCLUSTER_ID The ID of the cluster. TENANT_ID The ID of the tenant. STAT_ID The ID of the statistic. STAT_NAME The name of the statistic. For more information about the
DBA_WR_STATNAMEview, see oceanbase.DBA_WR_STATNAME and DBA_WR_STATNAME.To view the details of system statistics of all tenants, query the
oceanbase.CDB_WR_SYSSTATview.obclient [oceanbase]> SELECT * FROM oceanbase.CDB_WR_SYSSTAT limit 1;The query 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 columns in the preceding result set.
ColumnDescriptionCLUSTER_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 number of the node. STAT_ID The ID of the statistic. VALUE The value of the statistic. For more information about the
oceanbase.CDB_WR_SYSSTATview, see oceanbase.CDB_WR_SYSSTAT.To view the details of system statistics of the current tenant, query the
oceanbase.DBA_WR_SYSSTATview.obclient [oceanbase]> SELECT * FROM oceanbase.DBA_WR_SYSSTAT limit 1;The query 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 columns in the preceding result set.
ColumnDescriptionCLUSTER_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 number of the node. STAT_ID The ID of the statistic. VALUE The value of the statistic. For more information about the
DBA_WR_SYSSTATview, see oceanbase.DBA_WR_SYSSTAT and DBA_WR_SYSSTAT.
View the time model statistics
- To view the session-level time model statistics in the cluster, query the
GV$OB_SESS_TIME_MODELview.
obclient [oceanbase]> SELECT * FROM oceanbase.GV$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 |
| 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 set
The following table describes the columns in the preceding query result.
Column |
Description |
|---|---|
| SID | Session ID. |
| TENANT_ID | Tenant ID. |
| SVR_IP | Node IP address. |
| SVR_PORT | Node port number. |
| STAT_ID | Statistics ID. |
| STAT_NAME | Statistics name. |
| VALUE | Statistics value. |
For more information about the GV$OB_SESS_TIME_MODEL view, see GV$OB_SESS_TIME_MODEL.
- To view the session-level time model statistics on the current node, query the
V$OB_SESS_TIME_MODELview.
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 set
The following table describes the columns in the preceding query result.
Column |
Description |
|---|---|
| SID | Session ID. |
| TENANT_ID | Tenant ID. |
| SVR_IP | Node IP address. |
| SVR_PORT | Node port number. |
| STAT_ID | Statistics ID. |
| STAT_NAME | Statistics name. |
| VALUE | Statistics value. |
For more information about the V$OB_SESS_TIME_MODEL view, see V$OB_SESS_TIME_MODEL.
- To view the tenant-level time model statistics in the cluster, query the
GV$OB_SYS_TIME_MODELview.
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 set
The following table describes the columns in the preceding query result.
Column |
Description |
|---|---|
| TENANT_ID | Tenant ID. |
| SVR_IP | Node IP address. |
| SVR_PORT | Node port number. |
| STAT_ID | Statistics ID. |
| STAT_NAME | Statistics name. |
| VALUE | Statistics value. |
For more information about the GV$OB_SYS_TIME_MODEL view, see GV$OB_SYS_TIME_MODEL.
- To view the tenant-level time model statistics on the current node, query the
V$OB_SYS_TIME_MODELview.
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 set
The following table describes the columns in the preceding query result.
Column |
Description |
|---|---|
| TENANT_ID | Tenant ID. |
| SVR_IP | Node IP address. |
| SVR_PORT | Node port number. |
| STAT_ID | Statistics ID. |
| STAT_NAME | Statistics name. |
| VALUE | Statistics value. |
For more information about the V$OB_SYS_TIME_MODEL view, see V$OB_SYS_TIME_MODEL.
- To view the WR data of the time model statistics for all tenants, query the
oceanbase.CDB_WR_SYS_TIME_MODELview.
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 set
The following table describes the columns in the preceding query result.
Column |
Description |
|---|---|
| CLUSTER_ID | Cluster ID. |
| TENANT_ID | Tenant ID. |
| SNAP_ID | Snapshot ID. |
| SVR_IP | Node IP address. |
| SVR_PORT | Node port number. |
| STAT_ID | Statistics ID. |
| STAT_NAME | Statistics name. |
| VALUE | Statistics value. |
For more information about the oceanbase.CDB_WR_SYS_TIME_MODEL view, see oceanbase.CDB_WR_SYS_TIME_MODEL.
- To view the WR data of the time model statistics for the current tenant, query the
oceanbase.DBA_WR_SYS_TIME_MODELview.
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 set
The following table describes the columns in the preceding query result.
Field |
Description |
|---|---|
| SNAP_ID | The snapshot ID. |
| SVR_IP | The IP address of the node. |
| SVR_PORT | The port number of the node. |
| STAT_ID | The ID of the statistic. |
| STAT_NAME | The name of the statistic. |
| VALUE | The value of the statistic. |
For more information about the DBA_WR_STATNAME view, see oceanbase.DBA_WR_SYS_TIME_MODEL and DBA_WR_SYS_TIME_MODEL.
View SQLSTAT data
- Use the
GV$OB_SQLSTATview to query the basic performance statistics of SQL statements in a cluster.
Each SQL statement corresponds to a row (i.e., each unique combination of SQL_ID and Plan_Hash). The fields with _DELTA in their names indicate the incremental values from the last WR snapshot to the current time.
obclient(root@mysql001)[(none)]> SELECT * FROM oceanbase.GV$OB_SQLSTAT LIMIT 1\G
The query result is as follows:
*************************** 1. row ***************************
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
TENANT_ID: 1002
SQL_ID: D9EB4937E65F94AA38F6B0FE72B06D13
PLAN_ID: 7
PLAN_HASH: 12254811659671915614
PLAN_TYPE: 1
QUERY_SQL: SELECT column_value FROM __all_core_table WHERE TABLE_NAME = ? AND COLUMN_NAME = ?
SQL_TYPE: 1
MODULE: NULL
ACTION: NULL
PARSING_DB_ID: 201001
PARSING_DB_NAME: oceanbase
PARSING_USER_ID: 200001
EXECUTIONS_TOTAL: 4076
EXECUTIONS_DELTA: 509
DISK_READS_TOTAL: 0
DISK_READS_DELTA: 0
BUFFER_GETS_TOTAL: 1377
BUFFER_GETS_DELTA: 0
ELAPSED_TIME_TOTAL: 899938
ELAPSED_TIME_DELTA: 112107
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: 332
PHYSICAL_READ_REQUESTS_DELTA: 0
PHYSICAL_READ_BYTES_TOTAL: 5255494
PHYSICAL_READ_BYTES_DELTA: 0
WRITE_THROTTLE_TOTAL: 0
WRITE_THROTTLE_DELTA: 0
ROWS_PROCESSED_TOTAL: 4075
ROWS_PROCESSED_DELTA: 509
MEMSTORE_READ_ROWS_TOTAL: 28525
MEMSTORE_READ_ROWS_DELTA: 3563
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: 0
RETRY_DELTA: 0
PARTITION_TOTAL: 4076
PARTITION_DELTA: 509
NESTED_SQL_TOTAL: 0
NESTED_SQL_DELTA: 0
SOURCE_IP: xx.xx.xx.xx
SOURCE_PORT: 2882
ROUTE_MISS_TOTAL: 0
ROUTE_MISS_DELTA: 0
FIRST_LOAD_TIME: 2025-10-30 10:41:10.408270
PLAN_CACHE_HIT_TOTAL: 4075
PLAN_CACHE_HIT_DELTA: 509
MUTI_QUERY_TOTAL: 0
MUTI_QUERY_DELTA: 0
MUTI_QUERY_BATCH_TOTAL: 0
MUTI_QUERY_BATCH_DELTA: 0
FULL_TABLE_SCAN_TOTAL: 0
FULL_TABLE_SCAN_DELTA: 0
ERROR_COUNT_TOTAL: 1
ERROR_COUNT_DELTA: 0
1 row in set (0.008 sec)
The following table describes the fields in the query result:
Field |
Description |
|---|---|
| SVR_IP | The IP address of the node. |
| SVR_PORT | The port number of the node. |
| TENANT_ID | The tenant ID. |
| SQL_ID | The SQL statement ID. |
| PLAN_ID | The execution plan ID. |
| PLAN_HASH | The hash value of the execution plan. |
| PLAN_TYPE | For SQL plan caching, this field indicates the type of the plan:
For PL object caching, this field indicates the type of the PL object:
|
| QUERY_SQL | The full SQL text. |
| SQL_TYPE | The statement type. |
| MODULE | The application module of the statement when it was first parsed. This module is set by using the DBMS_APPLICATION_INFO.SET_MODULE package. |
| ACTION | The application action of the statement when it was first parsed. This action is set by using the DBMS_APPLICATION_INFO.SET_ACTION package. |
| PARSING_DB_ID | The database ID when the statement was parsed. |
| PARSING_DB_NAME | The database name when the statement was parsed. |
| PARSING_USER_ID | The user ID when the statement was parsed. |
| EXECUTIONS_TOTAL | The total number of times the plan was executed in the plan cache. |
| EXECUTIONS_DELTA | The incremental number of times the plan was executed 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 the cache. |
| BUFFER_GETS_DELTA | The incremental number of logical reads from the cache. |
| ELAPSED_TIME_TOTAL | The total time consumed by the statement. In parallel execution scenarios, this value is the sum of the values for each execution thread. |
| ELAPSED_TIME_DELTA | The incremental time consumed by the statement. |
| CPU_TIME_TOTAL | The total CPU time consumed. |
| CPU_TIME_DELTA | The incremental CPU time consumed. |
| CCWAIT_TOTAL | The total time consumed by concurrency-related wait events. |
| CCWAIT_DELTA | The incremental time consumed by concurrency-related wait events. |
| USERIO_WAIT_TOTAL | The total time consumed by I/O waits. |
| USERIO_WAIT_DELTA | The incremental time consumed by I/O waits. |
| APWAIT_TOTAL | The total time consumed by application-related wait events. |
| APWAIT_DELTA | The incremental time consumed by application-related 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 from physical storage. |
| PHYSICAL_READ_BYTES_DELTA | The incremental number of bytes read from physical storage. |
| WRITE_THROTTLE_TOTAL | The total time consumed by throttling when writing data to the MemStore. |
| WRITE_THROTTLE_DELTA | The incremental time consumed by throttling when writing data 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. This value increases by 1 for each nested SQL statement executed. |
| NESTED_SQL_DELTA | The incremental number of nested SQL statements executed. |
| SOURCE_IP | The IP address of the request source. |
| SOURCE_PORT | The port number of the request source. |
| ROUTE_MISS_TOTAL | The total number of times the request partition was not correctly hit since the execution plan was generated. |
| ROUTE_MISS_DELTA | The total number of times the request partition was not correctly hit since the last WR snapshot was collected. |
| FIRST_LOAD_TIME | The time when the execution plan was generated. |
| PLAN_CACHE_HIT_TOTAL | The total number of times the plan cache was hit since the execution plan was generated. |
| PLAN_CACHE_HIT_DELTA | The number of times the plan cache was hit since the last WR snapshot was collected. |
| MUTI_QUERY_TOTAL | The number of times the SQL statement was in a multi-query since the execution plan was generated. |
| MUTI_QUERY_DELTA | The number of times the SQL statement was in a multi-query since the last WR snapshot was collected. |
| MUTI_QUERY_BATCH_TOTAL | The number of times the SQL batch was executed in a multi-query since the execution plan was generated. |
| MUTI_QUERY_BATCH_DELTA | The number of times the SQL batch was executed in a multi-query since the last WR snapshot was collected. |
| FULL_TABLE_SCAN_TOTAL | The number of full table scans performed by the SQL statement since the execution plan was generated. |
| FULL_TABLE_SCAN_DELTA | The number of full table scans performed by the SQL statement since the last WR snapshot was collected. |
| ERROR_COUNT_TOTAL | The number of errors reported by the SQL statement since the execution plan was generated. |
| ERROR_COUNT_DELTA | The number of errors reported by the SQL statement since the last WR snapshot was collected. |
For more information about the GV$OB_SQLSTAT view, see GV$OB_SQLSTAT.
- Use the
V$OB_SQLSTATview to view the basic performance statistics of SQL statements on the current node.
Each row corresponds to an SQL statement (i.e., each unique combination of SQL_ID and Plan_Hash). The fields with _DELTA in their names indicate the incremental values from the last WR snapshot collection to the current time.
obclient(root@mysql001)[(none)]> SELECT * FROM oceanbase.V$OB_SQLSTAT LIMIT 1\G
The query result is as follows:
*************************** 1. row ***************************
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
TENANT_ID: 1002
SQL_ID: D9EB4937E65F94AA38F6B0FE72B06D13
PLAN_ID: 7
PLAN_HASH: 12254811659671915614
PLAN_TYPE: 1
QUERY_SQL: SELECT column_value FROM __all_core_table WHERE TABLE_NAME = ? AND COLUMN_NAME = ?
SQL_TYPE: 1
MODULE: NULL
ACTION: NULL
PARSING_DB_ID: 201001
PARSING_DB_NAME: oceanbase
PARSING_USER_ID: 200001
EXECUTIONS_TOTAL: 4100
EXECUTIONS_DELTA: 533
DISK_READS_TOTAL: 0
DISK_READS_DELTA: 0
BUFFER_GETS_TOTAL: 1896
BUFFER_GETS_DELTA: 0
ELAPSED_TIME_TOTAL: 905176
ELAPSED_TIME_DELTA: 117345
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: 54
PHYSICAL_READ_REQUESTS_DELTA: 0
PHYSICAL_READ_BYTES_TOTAL: 131546
PHYSICAL_READ_BYTES_DELTA: 0
WRITE_THROTTLE_TOTAL: 0
WRITE_THROTTLE_DELTA: 0
ROWS_PROCESSED_TOTAL: 4099
ROWS_PROCESSED_DELTA: 533
MEMSTORE_READ_ROWS_TOTAL: 28693
MEMSTORE_READ_ROWS_DELTA: 3731
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: 0
RETRY_DELTA: 0
PARTITION_TOTAL: 4100
PARTITION_DELTA: 533
NESTED_SQL_TOTAL: 0
NESTED_SQL_DELTA: 0
SOURCE_IP: xx.xx.xx.xx
SOURCE_PORT: 2882
ROUTE_MISS_TOTAL: 0
ROUTE_MISS_DELTA: 0
FIRST_LOAD_TIME: 2025-10-30 10:41:10.408270
PLAN_CACHE_HIT_TOTAL: 4099
PLAN_CACHE_HIT_DELTA: 533
MUTI_QUERY_TOTAL: 0
MUTI_QUERY_DELTA: 0
MUTI_QUERY_BATCH_TOTAL: 0
MUTI_QUERY_BATCH_DELTA: 0
FULL_TABLE_SCAN_TOTAL: 0
FULL_TABLE_SCAN_DELTA: 0
ERROR_COUNT_TOTAL: 1
ERROR_COUNT_DELTA: 0
1 row in set
The following table describes the fields in the query result.
Field |
Description |
|---|---|
| SVR_IP | The IP address of the node. |
| SVR_PORT | The port number of the node. |
| TENANT_ID | The tenant ID. |
| 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:
For PL object caching, this field indicates the type of the PL object:
|
| QUERY_SQL | The full text of the SQL statement. |
| SQL_TYPE | The type of the statement. |
| MODULE | The application module of the statement when it was first parsed. This module is set by using the DBMS_APPLICATION_INFO.SET_MODULE package. |
| ACTION | The application action of the statement when it was first parsed. This action is set by using the DBMS_APPLICATION_INFO.SET_ACTION package. |
| PARSING_DB_ID | The database ID when the statement was parsed. |
| PARSING_DB_NAME | The name of the database when the statement was parsed. |
| PARSING_USER_ID | The user ID when the statement was parsed. |
| EXECUTIONS_TOTAL | The total number of times the plan was executed in the plan cache. |
| EXECUTIONS_DELTA | The incremental number of times the plan was executed 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 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, this value is the sum of the values of each execution thread. |
| ELAPSED_TIME_DELTA | The incremental time consumed by the statement. |
| CPU_TIME_TOTAL | The total CPU time consumed. |
| CPU_TIME_DELTA | The incremental CPU time consumed. |
| CCWAIT_TOTAL | The total time consumed by concurrency-related wait events. |
| CCWAIT_DELTA | The incremental time consumed by concurrency-related wait events. |
| USERIO_WAIT_TOTAL | The total time consumed by I/O wait events. |
| USERIO_WAIT_DELTA | The incremental time consumed by I/O wait events. |
| APWAIT_TOTAL | The total time consumed by application-related wait events. |
| APWAIT_DELTA | The incremental time consumed by application-related 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 from physical storage. |
| PHYSICAL_READ_BYTES_DELTA | The incremental number of bytes read from physical storage. |
| WRITE_THROTTLE_TOTAL | The total time during which the write to the MemStore is throttled. |
| WRITE_THROTTLE_DELTA | The incremental time during which the write to the MemStore is throttled. |
| 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 RPC requests. |
| RPC_DELTA | The incremental number of RPC requests. |
| FETCHES_TOTAL | The total number of result sets fetched. |
| FETCHES_DELTA | The incremental number of result sets fetched. |
| 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. This value increases by 1 for each executed substatement. |
| NESTED_SQL_DELTA | The incremental number of nested SQL statements executed. |
| SOURCE_IP | The IP address of the request source. |
| SOURCE_PORT | The port number of the request source. |
| ROUTE_MISS_TOTAL | The total number of times the request partition was not correctly hit since the execution plan was generated. |
| ROUTE_MISS_DELTA | The total number of times the request partition was not correctly hit since the last WR snapshot was collected. |
| FIRST_LOAD_TIME | The time when the execution plan was generated. |
| PLAN_CACHE_HIT_TOTAL | The total number of times the plan cache was hit since the execution plan was generated. |
| PLAN_CACHE_HIT_DELTA | The number of times the plan cache was hit since the last WR snapshot was collected. |
| MUTI_QUERY_TOTAL | The number of times the SQL statement was located in a multi-query since the execution plan was generated. |
| MUTI_QUERY_DELTA | The number of times the SQL statement was located in a multi-query since the last WR snapshot was collected. |
| MUTI_QUERY_BATCH_TOTAL | The number of times the SQL statement was executed in a batch since the execution plan was generated. |
| MUTI_QUERY_BATCH_DELTA | The number of times the SQL statement was executed in a batch since the last WR snapshot was collected. |
| FULL_TABLE_SCAN_TOTAL | The number of full table scans performed by the SQL statement since the execution plan was generated. |
| FULL_TABLE_SCAN_DELTA | The number of full table scans performed by the SQL statement since the last WR snapshot was collected. |
| ERROR_COUNT_TOTAL | The number of errors reported by the SQL statement since the execution plan was generated. |
| ERROR_COUNT_DELTA | The number of errors reported by the SQL statement since the last WR snapshot was collected. |
For more information about the V$OB_SQLSTAT view, see V$OB_SQLSTAT.
- You can query the
oceanbase.CDB_WR_SQLSTATview to obtain the basic performance statistics of SQL statements executed by all tenants.
Each SQL statement is represented by a row (that is, each unique combination of SQL_ID and Plan_Hash corresponds to a row). The fields with _DELTA in their names indicate the incremental values from the last WR snapshot collection to the current time.
obclient(root@sys)[(none)]> SELECT * FROM oceanbase.CDB_WR_SQLSTAT LIMIT 1\G
The query result is as follows:
*************************** 1. row ***************************
TENANT_ID: 1
SNAP_ID: 1
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
SQL_ID: 02AAEF3D6A4A939843FE958984B6EA51
PLAN_HASH: 8470076267874576774
PLAN_TYPE: 1
MODULE: NULL
ACTION: NULL
PARSING_DB_ID: 201001
PARSING_DB_NAME: oceanbase
PARSING_USER_ID: 200001
EXECUTIONS_TOTAL: 1878
EXECUTIONS_DELTA: 1878
DISK_READS_TOTAL: 0
DISK_READS_DELTA: 0
BUFFER_GETS_TOTAL: 0
BUFFER_GETS_DELTA: 0
ELAPSED_TIME_TOTAL: 214868
ELAPSED_TIME_DELTA: 214868
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: 1877
ROWS_PROCESSED_DELTA: 1877
MEMSTORE_READ_ROWS_TOTAL: 1877
MEMSTORE_READ_ROWS_DELTA: 1877
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: 0
RETRY_DELTA: 0
PARTITION_TOTAL: 1878
PARTITION_DELTA: 1878
NESTED_SQL_TOTAL: 0
NESTED_SQL_DELTA: 0
SOURCE_IP: xx.xx.xx.xx
SOURCE_PORT: 2882
ROUTE_MISS_TOTAL: 0
ROUTE_MISS_DELTA: 0
FIRST_LOAD_TIME: 2025-10-30 10:39:50.573624
PLAN_CACHE_HIT_TOTAL: 1876
PLAN_CACHE_HIT_DELTA: 1876
MUTI_QUERY_TOTAL: 0
MUTI_QUERY_DELTA: 0
MUTI_QUERY_BATCH_TOTAL: 0
MUTI_QUERY_BATCH_DELTA: 0
FULL_TABLE_SCAN_TOTAL: 0
FULL_TABLE_SCAN_DELTA: 0
ERROR_COUNT_TOTAL: 0
ERROR_COUNT_DELTA: 0
1 row in set
The following table describes the fields in the preceding query result:
Field |
Description |
|---|---|
| TENANT_ID | Tenant ID |
| SNAP_ID | Snapshot ID |
| SVR_IP | Node IP |
| SVR_PORT | Node port number |
| SQL_ID | SQL statement ID |
| PLAN_HASH | Hash value of the execution plan |
| PLAN_ID | Execution plan ID |
| PLAN_TYPE | For SQL plan caching, this field indicates the type of the plan:
For PL object caching, this field indicates the type of the PL object:
|
| MODULE | The Module of the application when the statement was first parsed. This field is set by using the DBMS_APPLICATION_INFO.SET_MODULE package. |
| ACTION | The Action of the application when the statement was first parsed. This field is set by using the DBMS_APPLICATION_INFO.SET_ACTION package. |
| PARSING_DB_ID | Database ID when the statement was parsed |
| PARSING_DB_NAME | Database name when the statement was parsed |
| PARSING_USER_ID | User ID when the statement was parsed |
| EXECUTIONS_TOTAL | Total number of executions of the plan in the plan cache |
| EXECUTIONS_DELTA | Increment of the total number of executions of the plan in the plan cache |
| DISK_READS_TOTAL | Total number of disk reads |
| DISK_READS_DELTA | Increment of the number of disk reads |
| BUFFER_GETS_TOTAL | Total number of logical reads from the cache |
| BUFFER_GETS_DELTA | Increment of the number of logical reads from the cache |
| ELAPSED_TIME_TOTAL | Total time consumed by the statement. For parallel execution scenarios, this value is the sum of the values of each execution thread. |
| ELAPSED_TIME_DELTA | Increment of the time consumed by the statement |
| CPU_TIME_TOTAL | Total CPU time |
| CPU_TIME_DELTA | Increment of the total CPU time |
| CCWAIT_TOTAL | Total time consumed by concurrency-related wait events |
| CCWAIT_DELTA | Increment of the total time consumed by concurrency-related wait events |
| USERIO_WAIT_TOTAL | Total time consumed by I/O waits |
| USERIO_WAIT_DELTA | Increment of the total time consumed by I/O waits |
| APWAIT_TOTAL | Total time consumed by application-related wait events |
| APWAIT_DELTA | Increment of the total time consumed by application-related wait events |
| PHYSICAL_READ_REQUESTS_TOTAL | Total number of physical read requests |
| PHYSICAL_READ_REQUESTS_DELTA | Increment of the number of physical read requests |
| PHYSICAL_READ_BYTES_TOTAL | Total number of bytes read by physical reads |
| PHYSICAL_READ_BYTES_DELTA | Increment of the number of bytes read by physical reads |
| WRITE_THROTTLE_TOTAL | Total time consumed by throttling during writes to the MemStore |
| WRITE_THROTTLE_DELTA | Increment of the total time consumed by throttling during writes to the MemStore |
| ROWS_PROCESSED_TOTAL | Total number of rows processed by the statement |
| ROWS_PROCESSED_DELTA | Increment of the total number of rows processed by the statement |
| MEMSTORE_READ_ROWS_TOTAL | Total number of rows read from the MemStore |
| MEMSTORE_READ_ROWS_DELTA | Increment of the total number of rows read from the MemStore |
| MINOR_SSSTORE_READ_ROWS_TOTAL | Total number of rows read from the MINOR SSStore |
| MINOR_SSSTORE_READ_ROWS_DELTA | Increment of the total number of rows read from the MINOR SSStore |
| MAJOR_SSSTORE_READ_ROWS_TOTAL | Total number of rows read from the MAJOR SSStore |
| MAJOR_SSSTORE_READ_ROWS_DELTA | Increment of the total number of rows read from the MAJOR SSStore |
| RPC_TOTAL | Total number of RPCs |
| RPC_DELTA | Increment of the number of RPCs |
| FETCHES_TOTAL | Total number of result set fetches |
| FETCHES_DELTA | Increment of the number of result set fetches |
| RETRY_TOTAL | Total number of SQL retries |
| RETRY_DELTA | Increment of the number of SQL retries |
| PARTITION_TOTAL | Total number of partitions scanned by the SQL statement |
| PARTITION_DELTA | Increment of the number of partitions scanned by the SQL statement |
| NESTED_SQL_TOTAL | Total number of nested SQL statements executed. This value increases by 1 for each nested SQL statement executed. |
| NESTED_SQL_DELTA | Increment of the number of nested SQL statements executed |
| SOURCE_IP | IP address of the request source |
| SOURCE_PORT | Port number of the request source |
| ROUTE_MISS_TOTAL | Total number of times the request partition was not hit since the execution plan was generated. |
| ROUTE_MISS_DELTA | Total number of times the request partition was not hit since the last WR snapshot was captured. |
| FIRST_LOAD_TIME | Time when the execution plan was generated |
| PLAN_CACHE_HIT_TOTAL | Total number of times the plan cache was hit since the execution plan was generated. |
| PLAN_CACHE_HIT_DELTA | Number of times the plan cache was hit since the last WR snapshot was captured. |
| MUTI_QUERY_TOTAL | Total number of times the SQL statement was in a multi-query since the execution plan was generated. |
| MUTI_QUERY_DELTA | Number of times the SQL statement was in a multi-query since the last WR snapshot was captured. |
| MUTI_QUERY_BATCH_TOTAL | Total number of times the SQL statement batch was executed since the execution plan was generated. |
| MUTI_QUERY_BATCH_DELTA | Number of times the SQL statement batch was executed since the last WR snapshot was captured. |
| FULL_TABLE_SCAN_TOTAL | Total number of times the SQL statement performed a full table scan since the execution plan was generated. |
| FULL_TABLE_SCAN_DELTA | Number of times the SQL statement performed a full table scan since the last WR snapshot was captured. |
| ERROR_COUNT_TOTAL | Total number of errors reported by the SQL statement since the execution plan was generated. |
| ERROR_COUNT_DELTA | Number of errors reported by the SQL statement since the last WR snapshot was captured. |
For more information about the oceanbase.CDB_WR_SQLSTAT view, see oceanbase.CDB_WR_SQLSTAT.
- Use the
oceanbase.DBA_WR_SQLSTATview to query the basic performance statistics of SQL statements executed in the current tenant.
The names of the columns with _DELTA in the preceding table indicate the incremental values of the corresponding metrics from the last WR snapshot to the current time.
obclient(SYS@oracle001)[SYS]> SELECT * FROM SYS.DBA_WR_SQLSTAT WHERE ROWNUM = 1\G
The query result is as follows:
*************************** 1. row ***************************
SNAP_ID: 1
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
SQL_ID: 0C0D6679C8AA7E3BAFBDC6C4FF37A89B
PLAN_HASH: 3220196041753657978
PLAN_TYPE: 1
MODULE: NULL
ACTION: NULL
PARSING_DB_ID: 201001
PARSING_DB_NAME: oceanbase
PARSING_USER_ID: 200001
EXECUTIONS_TOTAL: 1
EXECUTIONS_DELTA: 1
DISK_READS_TOTAL: 0
DISK_READS_DELTA: 0
BUFFER_GETS_TOTAL: 2
BUFFER_GETS_DELTA: 2
ELAPSED_TIME_TOTAL: 2822
ELAPSED_TIME_DELTA: 2822
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: 1
ROWS_PROCESSED_DELTA: 1
MEMSTORE_READ_ROWS_TOTAL: 2
MEMSTORE_READ_ROWS_DELTA: 2
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: 0
RETRY_DELTA: 0
PARTITION_TOTAL: 1
PARTITION_DELTA: 1
NESTED_SQL_TOTAL: 0
NESTED_SQL_DELTA: 0
SOURCE_IP: xx.xx.xx.xx
SOURCE_PORT: 2882
ROUTE_MISS_TOTAL: 0
ROUTE_MISS_DELTA: 0
FIRST_LOAD_TIME: 30-OCT-25 10.43.30.083941 AM
PLAN_CACHE_HIT_TOTAL: 0
PLAN_CACHE_HIT_DELTA: 0
MUTI_QUERY_TOTAL: 0
MUTI_QUERY_DELTA: 0
MUTI_QUERY_BATCH_TOTAL: 0
MUTI_QUERY_BATCH_DELTA: 0
FULL_TABLE_SCAN_TOTAL: 0
FULL_TABLE_SCAN_DELTA: 0
ERROR_COUNT_TOTAL: 0
ERROR_COUNT_DELTA: 0
1 row in set
The following table describes the columns in the preceding result set.
Field |
Description |
|---|---|
| SNAP_ID | Snapshot ID |
| SVR_IP | Node IP |
| SVR_PORT | Node port number |
| SQL_ID | SQL statement ID |
| PLAN_HASH | Hash value of the execution plan |
| PLAN_ID | Execution plan ID |
| PLAN_TYPE | For SQL plan caching, this field indicates the plan type:
For PL object caching, this field indicates the PL object type:
|
| MODULE | The application Module when the statement was first parsed. Set by using the DBMS_APPLICATION_INFO.SET_MODULE package. |
| ACTION | The application Action when the statement was first parsed. Set by using the DBMS_APPLICATION_INFO.SET_ACTION package. |
| PARSING_DB_ID | Database ID when the statement was parsed |
| PARSING_DB_NAME | Database name when the statement was parsed |
| PARSING_USER_ID | User ID when the statement was parsed |
| EXECUTIONS_TOTAL | Total number of executions of the plan in the plan cache |
| EXECUTIONS_DELTA | Increment of the total number of executions of the plan in the plan cache |
| DISK_READS_TOTAL | Total number of disk reads |
| DISK_READS_DELTA | Increment of the total number of disk reads |
| BUFFER_GETS_TOTAL | Total number of logical reads from the cache |
| BUFFER_GETS_DELTA | Increment of the total number of logical reads from the cache |
| ELAPSED_TIME_TOTAL | Total time consumed by the statement. For parallel execution, this value is the sum of the time consumed by each execution thread. |
| ELAPSED_TIME_DELTA | Increment of the time consumed by the statement |
| CPU_TIME_TOTAL | Total CPU time consumed |
| CPU_TIME_DELTA | Increment of the total CPU time consumed |
| CCWAIT_TOTAL | Total time consumed by concurrency-related wait events |
| CCWAIT_DELTA | Increment of the total time consumed by concurrency-related wait events |
| USERIO_WAIT_TOTAL | Total I/O wait time |
| USERIO_WAIT_DELTA | Increment of the total I/O wait time |
| APWAIT_TOTAL | Total time consumed by application-related wait events |
| APWAIT_DELTA | Increment of the total time consumed by application-related wait events |
| PHYSICAL_READ_REQUESTS_TOTAL | Total number of physical read requests |
| PHYSICAL_READ_REQUESTS_DELTA | Increment of the total number of physical read requests |
| PHYSICAL_READ_BYTES_TOTAL | Total number of bytes read by physical reads |
| PHYSICAL_READ_BYTES_DELTA | Increment of the total number of bytes read by physical reads |
| WRITE_THROTTLE_TOTAL | Total time when the write to MemStore is throttled |
| WRITE_THROTTLE_DELTA | Increment of the total time when the write to MemStore is throttled |
| ROWS_PROCESSED_TOTAL | Total number of rows processed by the statement |
| ROWS_PROCESSED_DELTA | Increment of the total number of rows processed by the statement |
| MEMSTORE_READ_ROWS_TOTAL | Total number of rows read from MemStore |
| MEMSTORE_READ_ROWS_DELTA | Increment of the total number of rows read from MemStore |
| MINOR_SSSTORE_READ_ROWS_TOTAL | Total number of rows read from MINOR SSStore |
| MINOR_SSSTORE_READ_ROWS_DELTA | Increment of the total number of rows read from MINOR SSStore |
| MAJOR_SSSTORE_READ_ROWS_TOTAL | Total number of rows read from MAJOR SSStore |
| MAJOR_SSSTORE_READ_ROWS_DELTA | Increment of the total number of rows read from MAJOR SSStore |
| RPC_TOTAL | Total number of RPCs |
| RPC_DELTA | Increment of the total number of RPCs |
| FETCHES_TOTAL | Total number of times the result set is fetched |
| FETCHES_DELTA | Increment of the total number of times the result set is fetched |
| RETRY_TOTAL | Total number of SQL retries |
| RETRY_DELTA | Increment of the total number of SQL retries |
| PARTITION_TOTAL | Total number of partitions scanned by SQL execution |
| PARTITION_DELTA | Increment of the total number of partitions scanned by SQL execution |
| NESTED_SQL_TOTAL | Total number of nested SQL executions. Each time a sub-SQL statement is executed, the value increases by 1. |
| NESTED_SQL_DELTA | Increment of the total number of nested SQL executions |
| SOURCE_IP | VARCHAR2(46) |
| SOURCE_PORT | Port number of the request source |
| ROUTE_MISS_TOTAL | Total number of times the request partition is not correctly hit since the execution plan was generated |
| ROUTE_MISS_DELTA | Total number of times the request partition is not correctly hit since the last WR snapshot was collected |
| FIRST_LOAD_TIME | Time when the execution plan was generated |
| PLAN_CACHE_HIT_TOTAL | Total number of times the plan cache is hit since the execution plan was generated |
| PLAN_CACHE_HIT_DELTA | Number of times the plan cache is hit since the last WR snapshot was collected |
| MUTI_QUERY_TOTAL | Total number of times the SQL statement is in a Muti Query since the execution plan was generated |
| MUTI_QUERY_DELTA | Total number of times the SQL statement is in a Muti Query since the last WR snapshot was collected |
| MUTI_QUERY_BATCH_TOTAL | Total number of times the SQL batch is executed in a Muti Query since the execution plan was generated |
| MUTI_QUERY_BATCH_DELTA | Total number of times the SQL batch is executed in a Muti Query since the last WR snapshot was collected |
| FULL_TABLE_SCAN_TOTAL | Total number of full table scans of the SQL statement since the execution plan was generated |
| FULL_TABLE_SCAN_DELTA | Total number of full table scans of the SQL statement since the last WR snapshot was collected |
| ERROR_COUNT_TOTAL | Total number of errors reported by the SQL statement since the execution plan was generated |
| ERROR_COUNT_DELTA | Total number of errors reported by the SQL statement since the last WR snapshot was collected |
For more information about the DBA_WR_SQLSTAT view, see oceanbase.DBA_WR_SQLSTAT and DBA_WR_SQLSTAT.
Query the information about resource groups
You can query the
GV$OB_RES_MGR_SYSSTATview to obtain the statistics about resource groups in all OBServer nodes.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 columns in the preceding query result.
ColumnDescriptionCON_ID The tenant ID. GROUP_ID The consumer group ID. SVR_IP The IP address of the node. SVR_PORT The port number of the node. STATISTIC# The sequence number of the statistics. NAME The name of the statistics. CLASS The category of the statistics. VALUE The value of the statistics. VALUE_TYPE The value type. Valid values: incremental and assigned. STAT_ID The ID of the statistics. For more information about the
GV$OB_RES_MGR_SYSSTATview, see GV$OB_RES_MGR_SYSSTAT.You can query the
V$OB_RES_MGR_SYSSTATview to obtain the statistics about resource groups in the current OBServer node.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 columns in the preceding query result.
ColumnDescriptionCON_ID The tenant ID. GROUP_ID The consumer group ID. SVR_IP The IP address of the node. SVR_PORT The port number of the node. STATISTIC# The sequence number of the statistics. NAME The name of the statistics. CLASS The category of the statistics. VALUE The value of the statistics. VALUE_TYPE The value type. Valid values: incremental and assigned. STAT_ID The ID of the statistics. For more information about the
V$OB_RES_MGR_SYSSTATview, see V$OB_RES_MGR_SYSSTAT.