WR periodically collects data from performance views, and persists and displays the collected ASH, statistics, wait events, SQL execution status, and other data.
View historical session data
View persisted ASH data of all tenants through the
oceanbase.CDB_WR_ACTIVE_SESSION_HISTORYview.obclient [oceanbase]> SELECT * FROM oceanbase.CDB_WR_ACTIVE_SESSION_HISTORY limit 1\GThe 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 columns are described as follows:
ColumnDescriptionCLUSTER_ID Cluster ID TENANT_ID Tenant ID SNAP_ID Snapshot ID SVR_IP Node IP SVR_PORT Node PORT SAMPLE_ID Sampling ID SESSION_ID ID of the sampled session SAMPLE_TIME Sampling time USER_ID User ID of the sampled SESSION SESSION_TYPE SESSION type - N: FOREGROUND indicates a foreground session, that is, a user session
- Y: BACKGROUND indicates a background session
SESSION_STATE State of the SESSION at the sampling time EVENT_NO == 0: "ON CPU" indicates that there is currently no wait and SQL logic is being executedEVENT_NO ≠ 0: "WAITTING" indicates that the session is currently waiting. For details about the wait, see the EVENT field
SQL_ID SQL ID TRACE_ID TRACE_ID EVENT_NO Internal number of the wait event, used for associated queries with other tables EVENT_ID ID of the current wait event TIME_WAITED Total wait time of the wait event, in microseconds (us) P1 Value of wait event parameter 1 P2 Value of wait event parameter 2 P3 Value of wait event parameter 3 SQL_PLAN_LINE_ID SQL operator ID corresponding to the sampling time. The value is NULL if no corresponding operator exists GROUP_ID Group information of the sampling point PLAN_HASH Plan Hash corresponding to the currently executed SQL command THREAD_ID Thread ID of the current active session STMT_TYPE SQL type of the current active session TIME_MODEL Information related to TIME MODEL IN_PARSE Whether the current SESSION is performing SQL Parse at the sampling time IN_PL_PARSE Whether the current SESSION is performing SQL PL Parse at the sampling time IN_PLAN_CACHE Whether the current SESSION is using plan cache at the sampling time IN_SQL_OPTIMIZE Whether the current SESSION is performing SQL optimization at the sampling time IN_SQL_EXECUTION Whether the current SESSION is performing SQL execution at the sampling time IN_PX_EXECUTION Whether the current SESSION is performing SQL parallel execution at the sampling time. When the SESSION is in this state, it must also be in the IN_SQL_EXECUTION state. IN_SEQUENCE_LOAD Whether the current SESSION is retrieving a value from an auto-increment column or SEQUENCE at the sampling time IN_COMMITTING Whether the current sampling point is in the transaction commit phase IN_STORAGE_READ Whether the current sampling point is in the storage read phase IN_STORAGE_WRITE Whether the current sampling point is in the storage write phase IN_REMOTE_DAS_EXECUTION Whether the current sampling point is in the DAS remote execution phase IN_FILTER_ROWS Whether the current sampling point is in the storage pushdown execution phase IN_RPC_ENCODE The serialization operation currently being performed by the current SQL IN_RPC_DECODE The deserialization operation currently being performed by the current SQL IN_CONNECTION_MGR The connection establishment operation currently being performed by the current SQL PROGRAM The name of the program being executed at the current sampling point: - Background thread: the thread name, such as
observerorWrTimer - Foreground thread:
- A normal request is
user@client_ip (thread_name) inner_sqlremote execution isINNER SQL REMOTE EXEC (thread_name)- DAS remote execution is
DAS REMOTE EXEC (thread_name)
- A normal request is
MODULE The MODULE value recorded on the current SESSION at the sampling time, set by using the DBMS_APPLICATION_INFO.SET_MODULEpackageACTION The ACTION value recorded on the current SESSION at the sampling time, set by using the DBMS_APPLICATION_INFO.SET_ACTIONpackageCLIENT_ID The CLIENT_ID value recorded on the current SESSION at the sampling time, set by using the DBMS_APPLICATION_INFO.set_identifierpackageBACKTRACE An auxiliary debugging field used to record the code call stack when an event occurs PLAN_ID The plan ID of the sampled SQL in the PLAN CACHE, 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 past TM_DELTA_TIMEperiodTM_DELTA_DB_TIME The amount of time spent in database calls during the past TM_DELTA_TIMEperiodTOP_LEVEL_SQL_ID Top-level SQL ID IN_PLSQL_COMPILATION Current PL compilation status: Y/N IN_PLSQL_EXECUTION Current PL execution status: Y/N PLSQL_ENTRY_OBJECT_ID OBJECT ID of the top-level PL PLSQL_ENTRY_SUBPROGRAM_ID Subprogram ID of the top-level PL PLSQL_ENTRY_SUBPROGRAM_NAME Subprogram name of the top-level PL 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, displays the ID of the session that blocks the current session. Currently, this field takes effect only in lock conflict scenarios and displays the ID of the session that holds the lock TABLET_ID The ID of the tablet being processed by the current SQL PROXY_SID Proxy session ID TX_ID Current transaction ID DELTA_READ_IO_REQUESTS Number of reads between two samples DELTA_READ_IO_BYTES Cumulative size of files read between two samples DELTA_WRITE_IO_REQUESTS Number of writes between two samples DELTA_WRITE_IO_BYTES Cumulative size of files written between two samples For more information about the oceanbase.CDB_WR_ACTIVE_SESSION_HISTORY view, see oceanbase.CDB_WR_ACTIVE_SESSION_HISTORY.
View the persisted ASH data of the current tenant by using the
oceanbase.DBA_WR_ACTIVE_SESSION_HISTORYview.obclient [oceanbase]> SELECT * FROM oceanbase.DBA_WR_ACTIVE_SESSION_HISTORY limit 1\GThe response will be 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 columns are described as follows:
ColumnDescriptionCLUSTER_ID Cluster ID TENANT_ID Tenant ID SNAP_ID Snapshot ID SVR_IP Node IP SVR_PORT Node PORT SAMPLE_ID Sampling ID SESSION_ID ID of the sampled session SAMPLE_TIME Sampling time USER_ID User ID of the sampled SESSION SESSION_TYPE SESSION type - N: FOREGROUND, indicating a foreground session, that is, a user session
- Y: BACKGROUND, indicating a background session
SESSION_STATE State of the SESSION at the sampling time EVENT_NO == 0: "ON CPU" indicates that there is currently no wait and SQL logic is being executedEVENT_NO ≠ 0: "WAITTING" indicates that a wait is currently occurring. For details about the wait, see the EVENT field
SQL_ID SQL ID TRACE_ID TRACE_ID EVENT_NO Internal number of the wait event, used for associated queries with other tables EVENT_ID ID of the current wait event TIME_WAITED Total wait time of the wait event, in microseconds (us) P1 Value of wait event parameter 1 P2 Value of wait event parameter 2 P3 Value of wait event parameter 3 SQL_PLAN_LINE_ID SQL operator ID corresponding to the sampling time. The value is NULL if there is no corresponding operator GROUP_ID Group information of the sampling point PLAN_HASH Plan Hash corresponding to the currently executed SQL command THREAD_ID Thread ID of the current active session STMT_TYPE SQL type of the current active session TIME_MODEL Information about TIME MODEL IN_PARSE Whether the current SESSION is performing SQL Parse at the sampling time IN_PL_PARSE Whether the current SESSION is performing SQL PL Parse at the sampling time IN_PLAN_CACHE Whether the current SESSION is performing plan cache at the sampling time IN_SQL_OPTIMIZE Whether the current SESSION is performing SQL optimization at the sampling time IN_SQL_EXECUTION Whether the current SESSION is performing SQL execution at the sampling time IN_PX_EXECUTION Whether the current SESSION is performing SQL parallel execution at the sampling time. When the SESSION is in this state, it must also be in the IN_SQL_EXECUTION state. IN_SEQUENCE_LOAD Whether the current SESSION is fetching values from an auto-increment column or SEQUENCE at the sampling time IN_COMMITTING Whether the current sampling point is in the transaction commit stage IN_STORAGE_READ Whether the current sampling point is in the storage read stage IN_STORAGE_WRITE Whether the current sampling point is in the storage write stage IN_REMOTE_DAS_EXECUTION Whether the current sampling point is in the DAS remote execution stage IN_FILTER_ROWS Whether the current sampling point is in the storage pushdown execution stage IN_RPC_ENCODE Serialization operation being performed by the current SQL IN_RPC_DECODE Deserialization operation being performed by the current SQL IN_CONNECTION_MGR Connection establishment operation being performed by the current SQL PROGRAM Program name being executed at the current sampling point: - Background thread: indicates the thread name, such as
observerandWrTimer - Foreground thread:
- A regular request is
user@client_ip (thread_name) inner_sqlremote execution isINNER SQL REMOTE EXEC (thread_name)- DAS remote execution is
DAS REMOTE EXEC (thread_name)
- A regular request is
MODULE MODULE value recorded on the current SESSION at the sampling time, set by using the DBMS_APPLICATION_INFO.SET_MODULEpackageACTION ACTION value recorded on the current SESSION at the sampling time, set by using the DBMS_APPLICATION_INFO.SET_ACTIONpackageCLIENT_ID CLIENT_ID value recorded on the current SESSION at the sampling time, set by using the DBMS_APPLICATION_INFO.set_identifierpackageBACKTRACE Auxiliary debugging field used to record the code call stack when an event occurs PLAN_ID Plan ID of the sampled SQL in PLAN CACHE, used to associate the sampling point with the plan 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 past TM_DELTA_TIMEperiodTM_DELTA_DB_TIME Amount of time spent in database calls during the past TM_DELTA_TIMEperiodTOP_LEVEL_SQL_ID Top-level SQL ID IN_PLSQL_COMPILATION Current PL compilation status: Y/N IN_PLSQL_EXECUTION Current PL execution status: Y/N PLSQL_ENTRY_OBJECT_ID OBJECT ID of the top-level PL PLSQL_ENTRY_SUBPROGRAM_ID Subprogram ID of the top-level PL PLSQL_ENTRY_SUBPROGRAM_NAME Subprogram name of the top-level PL 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, displays the ID of the session that blocks the current session. Currently, this field takes effect only in lock conflict scenarios and displays the ID of the session that holds the lock TABLET_ID ID of the tablet currently being processed by the SQL statement PROXY_SID Proxy session ID TX_ID Current transaction ID DELTA_READ_IO_REQUESTS Number of reads between two samples DELTA_READ_IO_BYTES Cumulative size of files read between two samples DELTA_WRITE_IO_REQUESTS Number of writes between two samples DELTA_WRITE_IO_BYTES Cumulative size of files written between two samples For more information about the
DBA_WR_ACTIVE_SESSION_HISTORYview, see oceanbase.DBA_WR_ACTIVE_SESSION_HISTORY and DBA_WR_ACTIVE_SESSION_HISTORY.View active session history records through 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 columns are described as follows:
ColumnDescriptionSVR_IP Server IP address of the sample SVR_PORT Server port number of the sample SAMPLE_ID Locally unique ID of the sample SAMPLE_TIME Sampling time CON_ID Tenant ID USER_ID User ID of the sampled session SESSION_ID ID of the sampled session SESSION_TYPE Session type: - FOREGROUND: foreground session, namely, user session
- (Not supported yet) BACKGROUND: background session
SESSION_STATE Session state: - ON CPU: not waiting and is executing SQL logic
- WAITING: currently waiting. For detailed wait information, see the EVENT field
SQL_ID SQL ID PLAN_ID Plan ID of the sampled SQL in PLAN CACHE, used to associate the sample point with the plan TRACE_ID Trace ID of the operator EVENT Description of the wait event EVENT_NO Internal number of the wait event, used for associated queries with other tables EVENT_ID ID of the current wait event P1TEXT Name of parameter 1 of the wait event. The name varies with EVENT P1 Value of parameter 1 of the wait event P2TEXT Name of parameter 2 of the wait event. The name varies with EVENT P2 Value of parameter 2 of the wait event P3TEXT Name of parameter 3 of the wait event. The name varies with EVENT P3 Value of parameter 3 of the wait event WAIT_CLASS Type to which the wait event belongs WAIT_CLASS_ID ID of the type to which the wait event belongs, used for associated queries with other tables TIME_WAITED Total wait time of the wait event, in microseconds (us) SQL_PLAN_LINE_ID Number of the SQL operator in the SQL plan when the session was sampled GROUP_ID Group information of the sample point PLAN_HASH Plan Hash corresponding to the SQL command currently being executed THREAD_ID Thread ID of the current active session STMT_TYPE SQL type of the current active session IN_PARSE Whether the session was performing SQL Parse when sampled IN_PL_PARSE Whether the session was performing SQL PL Parse when sampled IN_PLAN_CACHE Whether the session was performing Plan Cache matching when sampled IN_SQL_OPTIMIZE Whether the session was performing SQL parsing optimization when sampled IN_SQL_EXECUTION Whether the session was performing SQL execution when sampled IN_PX_EXECUTION Whether the session was performing SQL parallel execution when sampled. When the session is in this state, it must also be in the IN_SQL_EXECUTION state IN_SEQUENCE_LOAD Whether the session was obtaining a value for an auto-increment column or SEQUENCE when sampled IN_COMMITTING Whether the current sample point is in the transaction commit stage IN_STORAGE_READ Whether the current sample point is in the storage read stage IN_STORAGE_WRITE Whether the current sample point is in the storage write stage IN_REMOTE_DAS_EXECUTION Whether the current sample point is in the DAS remote execution stage IN_FILTER_ROWS Indicates whether the current sample point is in the storage pushdown execution stage IN_RPC_ENCODE Serialization operation currently being performed by the SQL IN_RPC_DECODE Deserialization operation currently being performed by the SQL IN_CONNECTION_MGR Connection establishment operation currently being performed by the SQL PROGRAM Name of the program being executed at the current sample point: - Background thread: the thread name, such as
observerandWrTimer - Foreground thread:
- A regular request is
user@client_ip (thread_name) inner_sqlremote execution isINNER SQL REMOTE EXEC (thread_name)- DAS remote execution is
DAS REMOTE EXEC (thread_name)
- A regular request is
MODULE MODULE value recorded by the session when sampled, set by using the DBMS_APPLICATION_INFO.SET_MODULEpackage.ACTION ACTION value recorded by the session when sampled, set by using the DBMS_APPLICATION_INFO.SET_ACTIONpackage.CLIENT_ID CLIENT_ID value recorded by the session when sampled, set by using the DBMS_APPLICATION_INFO.set_identifierpackage.BACKTRACE Auxiliary debugging field used to record the code call stack when the 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 during the past TM_DELTA_TIMEperiodTM_DELTA_DB_TIME Amount of time spent in database calls during the past TM_DELTA_TIMEperiodTOP_LEVEL_SQL_ID Top-level SQL ID IN_PLSQL_COMPILATION Current PL compilation status: Y/N IN_PLSQL_EXECUTION Current PL execution status: Y/N PLSQL_ENTRY_OBJECT_ID OBJECT ID of the top-level PL PLSQL_ENTRY_SUBPROGRAM_ID Sub project ID of the top-level PL PLSQL_ENTRY_SUBPROGRAM_NAME Sub project name of the top-level PL PLSQL_OBJECT_ID PL object ID currently being executed PLSQL_SUBPROGRAM_ID PL subprogram ID currently being executed PLSQL_SUBPROGRAM_NAME PL subprogram name currently being executed BLOCKING_SESSION_ID If the current session is blocked, displays the session ID of the session that blocks it. Currently, this field takes effect only in lock conflict scenarios and displays the session ID of the lock holder TABLET_ID ID of the tablet currently being processed by the SQL statement PROXY_SID Proxy session ID TX_ID Current transaction ID DELTA_READ_IO_REQUESTS Number of reads between two samples DELTA_READ_IO_BYTES Cumulative size of files read between two samples DELTA_WRITE_IO_REQUESTS Number of writes between two samples DELTA_WRITE_IO_BYTES Cumulative size of files written between two samples For more information about the
GV$OB_ACTIVE_SESSION_HISTORYview, see GV$OB_ACTIVE_SESSION_HISTORY.View the current active session history through the
V$OB_ACTIVE_SESSION_HISTORYview.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 columns are described as follows:
ColumnDescriptionSVR_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 Locally unique ID of the sample SAMPLE_TIME Sampling time CON_ID Tenant ID USER_ID User ID of the sampled session SESSION_ID ID of the sampled session SESSION_TYPE Session type: - FOREGROUND: foreground session, that is, user session
- (Not supported) BACKGROUND: background session
SESSION_STATE Session state: - ON CPU: currently not waiting and is executing SQL logic
- WAITING: currently waiting. For detailed wait information, see the EVENT column
SQL_ID SQL ID PLAN_ID Plan ID of the sampled SQL in PLAN CACHE, used to associate sampling points with plans TRACE_ID Trace ID of the operator EVENT Description of the wait event EVENT_NO Internal number of the wait event, used for associated queries with other tables EVENT_ID Indicates the ID of the current wait event P1TEXT Name of parameter 1 of the wait event. The name here changes accordingly for different EVENT values P1 Value of wait event parameter 1 P2TEXT Name of parameter 2 of the wait event. The name here changes accordingly for different EVENT values P2 Value of wait event parameter 2 P3TEXT Name of parameter 3 of the wait event. The name here changes accordingly for different EVENT values P3 Value of wait event parameter 3 WAIT_CLASS Type to which the wait event belongs WAIT_CLASS_ID ID of the type to which the wait event belongs, used for associated queries with other tables TIME_WAITED Total wait time of the wait event, in microseconds (us) SQL_PLAN_LINE_ID Number of the SQL operator in the SQL plan when the session is sampled GROUP_ID Group information of the sampling point PLAN_HASH Plan Hash corresponding to the currently executed SQL command THREAD_ID Thread ID of the current active session STMT_TYPE SQL type of the current active session IN_PARSE Whether the session is performing SQL Parse when sampled IN_PL_PARSE Whether the session is performing SQL PL Parse when sampled IN_PLAN_CACHE Whether the session is performing Plan Cache matching when sampled IN_SQL_OPTIMIZE Whether the session is performing SQL parsing optimization when sampled IN_SQL_EXECUTION Whether the session is performing SQL execution when sampled IN_PX_EXECUTION Whether the session is performing SQL parallel execution when sampled. When the session is in this state, it must also be in the IN_SQL_EXECUTION state IN_SEQUENCE_LOAD Whether the session is fetching values for an auto-increment column or SEQUENCE when sampled IN_COMMITTING Whether the current sampling point is in the transaction commit phase IN_STORAGE_READ Whether the current sampling point is in the storage read phase IN_STORAGE_WRITE Whether the current sampling point is in the storage write phase IN_REMOTE_DAS_EXECUTION 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 pushdown execution phase IN_RPC_ENCODE Serialization operation currently being performed by the current SQL IN_RPC_DECODE Deserialization operation currently being performed by the current SQL IN_CONNECTION_MGR Connection establishment operation currently being performed by the current SQL PROGRAM Program name being executed at the current sampling point: - Background thread: refers to the thread name, such as
observerandWrTimer - Foreground thread:
- A normal request is
user@client_ip (thread_name) inner_sqlremote execution isINNER SQL REMOTE EXEC (thread_name)- DAS remote execution is
DAS REMOTE EXEC (thread_name)
- A normal request is
MODULE MODULE value recorded by the session when sampled, set through the DBMS_APPLICATION_INFO.SET_MODULEpackage.ACTION ACTION value recorded by the session when sampled, set through the DBMS_APPLICATION_INFO.SET_ACTIONpackage.CLIENT_ID CLIENT_ID value recorded by the session when sampled, set through the DBMS_APPLICATION_INFO.set_identifierpackage.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 CPU during the past TM_DELTA_TIMEperiodTM_DELTA_DB_TIME Amount of time spent in database calls during the past TM_DELTA_TIMEperiodTOP_LEVEL_SQL_ID Top-level SQL ID IN_PLSQL_COMPILATION Current PL compilation status: Y/N IN_PLSQL_EXECUTION Current PL execution status: Y/N PLSQL_ENTRY_OBJECT_ID OBJECT ID of the top-level PL PLSQL_ENTRY_SUBPROGRAM_ID Sub project ID of the top-level PL PLSQL_ENTRY_SUBPROGRAM_NAME Sub project name of the top-level PL PLSQL_OBJECT_ID PL object ID that is currently being executed PLSQL_SUBPROGRAM_ID PL subprogram ID that is currently being executed PLSQL_SUBPROGRAM_NAME PL subprogram name that is currently being executed BLOCKING_SESSION_ID If the current session is blocked, displays the session ID of the session that blocks it. Currently, this field takes effect only in lock conflict scenarios and displays the session ID of the lock holder TABLET_ID ID of the tablet that the current SQL statement is processing PROXY_SID Proxy session ID TX_ID Current transaction ID DELTA_READ_IO_REQUESTS Number of reads between two samples DELTA_READ_IO_BYTES Cumulative size of files read between two samples DELTA_WRITE_IO_REQUESTS Number of writes between two samples DELTA_WRITE_IO_BYTES Cumulative size of files written between two samples For more information about the
V$OB_ACTIVE_SESSION_HISTORYview, see V$OB_ACTIVE_SESSION_HISTORY.
View historical session statistics
View the names of system statistics for all tenants through the
oceanbase.CDB_WR_STATNAMEview.obclient [oceanbase]> SELECT * FROM oceanbase.CDB_WR_STATNAME limit 1;The returned 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 columns are described as follows:
ColumnDescriptionCLUSTER_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.
View the names of system statistics for the current tenant through the
oceanbase.DBA_WR_STATNAMEview.obclient [oceanbase]> SELECT * FROM oceanbase.DBA_WR_STATNAME limit 1;The returned 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 columns are described as follows:
ColumnDescriptionCLUSTER_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.
View the details of system statistics for all tenants through the
oceanbase.CDB_WR_SYSSTATview.obclient [oceanbase]> SELECT * FROM oceanbase.CDB_WR_SYSSTAT limit 1;The returned 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 columns are described as follows:
ColumnDescriptionCLUSTER_ID Cluster ID TENANT_ID Tenant ID SNAP_ID Snapshot ID SVR_IP Node IP SVR_PORT Node PORT STAT_ID Statistic ID VALUE Value of the statistic For more information about the oceanbase.CDB_WR_SYSSTAT view, see oceanbase.CDB_WR_SYSSTAT.
View detailed information about the system statistics of the current tenant through the
oceanbase.DBA_WR_SYSSTATview.obclient [oceanbase]> SELECT * FROM oceanbase.DBA_WR_SYSSTAT limit 1;The returned 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 columns are described as follows:
ColumnDescriptionCLUSTER_ID Cluster ID TENANT_ID Tenant ID SNAP_ID Snapshot ID SVR_IP Node IP SVR_PORT Node PORT STAT_ID Statistic ID VALUE Value of the statistic For more information about the DBA_WR_SYSSTAT view, see oceanbase.DBA_WR_SYSSTAT and DBA_WR_SYSSTAT.
View time model statistics
View session-level Time Model statistics in the cluster by using
GV$OB_SESS_TIME_MODEL.obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_SESS_TIME_MODEL LIMIT 10;The returned 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 columns are described as follows:
ColumnDescriptionSID Session ID TENANT_ID Tenant ID SVR_IP Node IP address SVR_PORT Node port number STAT_ID Statistic ID STAT_NAME Statistic name VALUE Statistic value For more information about the
GV$OB_SESS_TIME_MODELview, see GV$OB_SESS_TIME_MODEL.View session-level Time Model statistics on the current node by using 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 setThe columns are described as follows:
ColumnDescriptionSID Session ID TENANT_ID Tenant ID SVR_IP Node IP address SVR_PORT Node port number STAT_ID Statistic ID STAT_NAME Statistic name VALUE Statistic value For more information about the
V$OB_SESS_TIME_MODELview, see V$OB_SESS_TIME_MODEL.View tenant-level Time Model statistics in the cluster by using 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 setThe columns are described as follows:
ColumnDescriptionTENANT_ID Tenant ID SVR_IP Node IP SVR_PORT Node port number STAT_ID Statistic ID STAT_NAME Statistic name VALUE Statistic value For more information about the
GV$OB_SYS_TIME_MODELview, see GV$OB_SYS_TIME_MODEL.View tenant-level Time Model statistics on all nodes through 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 setThe columns are described as follows:
ColumnDescriptionTENANT_ID Tenant ID SVR_IP Node IP SVR_PORT Node port number STAT_ID Statistic ID STAT_NAME Statistic name VALUE Statistic value For more information about the
V$OB_SYS_TIME_MODELview, see V$OB_SYS_TIME_MODEL.View WR data of Time Model statistics of all tenants through 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 setThe columns are described as follows:
ColumnDescriptionCLUSTER_ID Cluster ID TENANT_ID Tenant ID SNAP_ID Snapshot ID SVR_IP Node IP address SVR_PORT Node port number STAT_ID Statistic ID STAT_NAME Statistic name VALUE Statistic value For more information about the oceanbase.CDB_WR_SYS_TIME_MODEL view, see oceanbase.CDB_WR_SYS_TIME_MODEL.
View the WR data of Time Model statistics for the current tenant through 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 setThe columns are described as follows:
ColumnDescriptionSNAP_ID Snapshot ID SVR_IP Node IP address SVR_PORT Node port number STAT_ID Statistic ID STAT_NAME Statistic name VALUE Statistic value For more information about the DBA_WR_SYS_TIME_MODEL view, see oceanbase.DBA_WR_SYS_TIME_MODEL and DBA_WR_SYS_TIME_MODEL.
View SQLSTAT data
View the basic performance statistics of SQL in the cluster through the
GV$OB_SQLSTATview.Each SQL statement occupies one row (that is, each unique value of
SQL_ID+Plan_Hashoccupies one row). Columns that contain_DELTAindicate the increments of the statistics from the last WR snapshot collection to the current time.Each SQL statement corresponds to a line (that is, a unique value of
SQL_ID+Plan_Hash). Those with_DELTAin the list indicate the incremental statistical values 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: xx.xx.xx.xx SVR_PORT: 2882 TENANT_ID: 1002 SQL_ID: FD4390F00BA50EBCC4BBAE0C3A06BD8E PLAN_ID: 133 PLAN_HASH: 10950220524135603512 PLAN_TYPE: 1 QUERY_SQL: SELECT column_value FROM __all_core_table WHERE TABLE_NAME = ? AND COLUMN_NAME = ? FOR UPDATE SQL_TYPE: 1 MODULE: NULL ACTION: NULL PARSING_DB_ID: 201001 PARSING_DB_NAME: oceanbase PARSING_USER_ID: 200001 EXECUTIONS_TOTAL: 227017 EXECUTIONS_DELTA: 312 DISK_READS_TOTAL: 54 DISK_READS_DELTA: 0 BUFFER_GETS_TOTAL: 1896 BUFFER_GETS_DELTA: 0 ELAPSED_TIME_TOTAL: 143219394 ELAPSED_TIME_DELTA: 247601 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: 227017 ROWS_PROCESSED_DELTA: 312 MEMSTORE_READ_ROWS_TOTAL: 264444 MEMSTORE_READ_ROWS_DELTA: 312 MINOR_SSSTORE_READ_ROWS_TOTAL: 334659 MINOR_SSSTORE_READ_ROWS_DELTA: 312 MAJOR_SSSTORE_READ_ROWS_TOTAL: 1545194 MAJOR_SSSTORE_READ_ROWS_DELTA: 2184 RPC_TOTAL: 0 RPC_DELTA: 0 FETCHES_TOTAL: 0 FETCHES_DELTA: 0 RETRY_TOTAL: 0 RETRY_DELTA: 0 PARTITION_TOTAL: 227017 PARTITION_DELTA: 312 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-09-25 11:11:09.123018 PLAN_CACHE_HIT_TOTAL: 227016 PLAN_CACHE_HIT_DELTA: 312 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 setThe columns are described as follows:
ColumnDescriptionSVR_IP Node IP SVR_PORT Node port number TENANT_ID Tenant ID SQL_ID SQL statement ID PLAN_ID Execution plan ID PLAN_HASH Hash value of the execution plan PLAN_TYPE For the SQL plan cache, this field indicates the type of the plan: 1: indicates Local Plan2: indicates Remote Plan3: indicates Distributed Plan
For the PL object cache, this field indicates the type of the PL object:1: indicates Procedure2: indicates Function3: indicates Package4: indicates Anonymous Block
QUERY_SQL Full SQL text SQL_TYPE Statement type MODULE The application Module when the statement was parsed for the first time, set by the DBMS_APPLICATION_INFO.SET_MODULEpackageACTION The application Action when the statement was parsed for the first time, set by the DBMS_APPLICATION_INFO.SET_ACTIONpackagePARSING_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 this plan in the plan cache EXECUTIONS_DELTA Increment in the total number of executions of this plan in the plan cache DISK_READS_TOTAL Total number of disk reads DISK_READS_DELTA Increment in the number of disk reads BUFFER_GETS_TOTAL Total number of logical reads from the cache BUFFER_GETS_DELTA Increment in the number of logical reads from the cache ELAPSED_TIME_TOTAL Total time consumed by this statement. In parallel execution scenarios, this value is the accumulated value for each execution thread. ELAPSED_TIME_DELTA Increment in the time consumed by this statement CPU_TIME_TOTAL Total CPU time CPU_TIME_DELTA Increment in the total CPU time CCWAIT_TOTAL Total time consumed by concurrency wait events CCWAIT_DELTA Increment in the total time consumed by concurrency wait events USERIO_WAIT_TOTAL Total time consumed by I/O waits USERIO_WAIT_DELTA Increment in the total time consumed by I/O waits APWAIT_TOTAL Total time consumed by application wait events APWAIT_DELTA Increment in the total time consumed by application wait events PHYSICAL_READ_REQUESTS_TOTAL Total number of physical read requests PHYSICAL_READ_REQUESTS_DELTA Increment in the number of physical read requests PHYSICAL_READ_BYTES_TOTAL Total number of physical read bytes PHYSICAL_READ_BYTES_DELTA Increment in the number of physical read bytes WRITE_THROTTLE_TOTAL Total time throttled when writing to the MemStore WRITE_THROTTLE_DELTA Increment of the total time throttled when writing 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 times result sets are fetched FETCHES_DELTA Increment of the number of times result sets are fetched RETRY_TOTAL Total number of SQL retries RETRY_DELTA Increment of the number of SQL retries PARTITION_TOTAL Total number of partitions scanned during SQL execution PARTITION_DELTA Increment of the number of partitions scanned during SQL execution NESTED_SQL_TOTAL Total number of nested SQL executions. The value increases by 1 each time a sub-SQL is executed NESTED_SQL_DELTA Increment of nested SQL executions SOURCE_IP IP address of the request source SOURCE_PORT Port number of the request source ROUTE_MISS_TOTAL Total number of times the requested partition has not been correctly hit since the execution plan was generated ROUTE_MISS_DELTA Total number of times the requested partition has not been correctly hit since the last WR snapshot collection FIRST_LOAD_TIME Time when the execution plan was generated PLAN_CACHE_HIT_TOTAL Total number of Plan Cache hits since the execution plan was generated PLAN_CACHE_HIT_DELTA Number of Plan Cache hits since the last WR snapshot collection MUTI_QUERY_TOTAL Number of times this SQL has been in Muti Query since the execution plan was generated MUTI_QUERY_DELTA Number of times this SQL has been in Muti Query since the last WR snapshot collection MUTI_QUERY_BATCH_TOTAL Number of times this SQL Batch has been executed since the execution plan was generated MUTI_QUERY_BATCH_DELTA Number of times this SQL Batch has been executed since the last WR snapshot collection FULL_TABLE_SCAN_TOTAL Number of full table scans performed by this SQL since the execution plan was generated FULL_TABLE_SCAN_DELTA Number of full table scans performed by this SQL since the last WR snapshot collection ERROR_COUNT_TOTAL Number of errors reported by this SQL since the execution plan was generated ERROR_COUNT_DELTA Number of errors reported by this SQL since the last WR snapshot collection For more information about the
GV$OB_SQLSTATview, see GV$OB_SQLSTAT.View basic SQL performance statistics on the current node through the
V$OB_SQLSTATview.Each SQL statement is displayed as one row. In other words, each unique
SQL_ID+Plan_Hashvalue is displayed as one row. Columns containing_DELTAindicate the increment of the statistical value from the last WR snapshot collection to the current time.Each SQL statement is displayed as a line (that is, each unique
SQL_IDandPlan_Hashvalue corresponds to a row)._DELTAindicates the change in the values between the previous WR snapshot and the current time.obclient [oceanbase]> SELECT * FROM oceanbase.V$OB_SQLSTAT LIMIT 1\GThe query result is as follows:
*************************** 1. row *************************** SVR_IP: xx.xx.xx.xx SVR_PORT: 2882 TENANT_ID: 1002 SQL_ID: FD4390F00BA50EBCC4BBAE0C3A06BD8E PLAN_ID: 133 PLAN_HASH: 10950220524135603512 PLAN_TYPE: 1 QUERY_SQL: SELECT column_value FROM __all_core_table WHERE TABLE_NAME = ? AND COLUMN_NAME = ? FOR UPDATE SQL_TYPE: 1 MODULE: NULL ACTION: NULL PARSING_DB_ID: 201001 PARSING_DB_NAME: oceanbase PARSING_USER_ID: 200001 EXECUTIONS_TOTAL: 226974 EXECUTIONS_DELTA: 269 DISK_READS_TOTAL: 54 DISK_READS_DELTA: 0 BUFFER_GETS_TOTAL: 1896 BUFFER_GETS_DELTA: 0 ELAPSED_TIME_TOTAL: 143148603 ELAPSED_TIME_DELTA: 176810 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: 226974 ROWS_PROCESSED_DELTA: 269 MEMSTORE_READ_ROWS_TOTAL: 264401 MEMSTORE_READ_ROWS_DELTA: 269 MINOR_SSSTORE_READ_ROWS_TOTAL: 334616 MINOR_SSSTORE_READ_ROWS_DELTA: 269 MAJOR_SSSTORE_READ_ROWS_TOTAL: 1544893 MAJOR_SSSTORE_READ_ROWS_DELTA: 1883 RPC_TOTAL: 0 RPC_DELTA: 0 FETCHES_TOTAL: 0 FETCHES_DELTA: 0 RETRY_TOTAL: 0 RETRY_DELTA: 0 PARTITION_TOTAL: 226974 PARTITION_DELTA: 269 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-09-25 11:11:09.123018 PLAN_CACHE_HIT_TOTAL: 226973 PLAN_CACHE_HIT_DELTA: 269 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 setThe columns are described as follows:
ColumnDescriptionSVR_IP Node IP address SVR_PORT Node port number TENANT_ID Tenant ID SQL_ID SQL statement ID PLAN_ID Execution plan ID PLAN_HASH Hash value of the execution plan PLAN_TYPE For the SQL plan cache, this field indicates the type of the plan: 1: indicates Local Plan2: indicates Remote Plan3: indicates Distributed Plan
For the PL object cache, this field indicates the type of the PL object:1: indicates Procedure2: indicates Function3: indicates Package4: indicates Anonymous Block
QUERY_SQL Full SQL text SQL_TYPE Statement type MODULE The application Module when the statement was first parsed, set by the DBMS_APPLICATION_INFO.SET_MODULEpackageACTION The application Action when the statement was first parsed, set by the DBMS_APPLICATION_INFO.SET_ACTIONpackagePARSING_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 in the total number of executions of the plan in the plan cache DISK_READS_TOTAL Total number of disk reads DISK_READS_DELTA Increment in the number of disk reads BUFFER_GETS_TOTAL Total number of logical reads from the cache BUFFER_GETS_DELTA Increment in the number of logical reads from the cache ELAPSED_TIME_TOTAL Total time consumed by the statement. In parallel execution scenarios, this value is the accumulated value of all execution threads. ELAPSED_TIME_DELTA Increment in the time consumed by the statement CPU_TIME_TOTAL Total CPU time CPU_TIME_DELTA Increment in the total CPU time CCWAIT_TOTAL Total time consumed by concurrency wait events CCWAIT_DELTA Increment in the total time consumed by concurrency wait events USERIO_WAIT_TOTAL Total time consumed by I/O waits USERIO_WAIT_DELTA Increment in the total time consumed by I/O waits APWAIT_TOTAL Total time consumed by application wait events APWAIT_DELTA Increment in the total time consumed by application wait events PHYSICAL_READ_REQUESTS_TOTAL Total number of physical read requests PHYSICAL_READ_REQUESTS_DELTA Increment in the number of physical read requests PHYSICAL_READ_BYTES_TOTAL Total number of bytes physically read PHYSICAL_READ_BYTES_DELTA Increment in the number of bytes physically read WRITE_THROTTLE_TOTAL Total time throttled when writing to MemStore WRITE_THROTTLE_DELTA Increment in the total time throttled when writing to MemStore ROWS_PROCESSED_TOTAL Total number of rows processed by the statement ROWS_PROCESSED_DELTA Increment in 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 in 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 in 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 in the total number of rows read from MAJOR SSStore RPC_TOTAL Total number of RPCs RPC_DELTA Increment in the number of RPCs FETCHES_TOTAL Total number of result set fetches FETCHES_DELTA Increment in the number of result set fetches RETRY_TOTAL Total number of SQL retries RETRY_DELTA Increment in the number of SQL retries PARTITION_TOTAL Total number of partitions scanned during SQL execution PARTITION_DELTA Increment in the number of partitions scanned during SQL execution NESTED_SQL_TOTAL Total number of nested SQL executions, which increases by 1 each time a sub-SQL is executed NESTED_SQL_DELTA Increment in the number of nested SQL executions SOURCE_IP IP address of the request source SOURCE_PORT Port number of the request source ROUTE_MISS_TOTAL Total number of times the requested partition was not correctly hit since the execution plan was generated ROUTE_MISS_DELTA Total number of times the requested partition was not correctly hit since the last WR snapshot collection FIRST_LOAD_TIME Time when the execution plan was generated PLAN_CACHE_HIT_TOTAL Total number of Plan Cache hits since the execution plan was generated PLAN_CACHE_HIT_DELTA Number of Plan Cache hits since the last WR snapshot collection MUTI_QUERY_TOTAL Number of times this SQL was in Muti Query since the execution plan was generated MUTI_QUERY_DELTA Number of times this SQL was in Muti Query since the last WR snapshot collection MUTI_QUERY_BATCH_TOTAL Number of times this SQL Batch was executed since the execution plan was generated MUTI_QUERY_BATCH_DELTA Number of times this SQL Batch was executed since the last WR snapshot collection FULL_TABLE_SCAN_TOTAL Number of full table scans by this SQL since the execution plan was generated FULL_TABLE_SCAN_DELTA Number of full table scans by this SQL since the last WR snapshot collection ERROR_COUNT_TOTAL Number of errors reported by this SQL since the execution plan was generated ERROR_COUNT_DELTA Number of errors reported by this SQL since the last WR snapshot collection For more information about the
V$OB_SQLSTATview, see V$OB_SQLSTAT.View the basic performance statistics of SQL executed by all tenants through the
oceanbase.CDB_WR_SQLSTATview.Each SQL statement occupies one row (that is, each unique value of
SQL_ID+Plan_Hashoccupies one row). Columns containing_DELTAindicate the increment of the statistical values from the last WR snapshot collection to the current time.Each line is an SQL statement (i.e., each unique combination of
SQL_IDandPlan_Hashcorresponds to one line). The values in the lists that contain_DELTAindicate the incremental changes in the values captured from the last WR snapshot to the current time.obclient [oceanbase]> SELECT * FROM oceanbase.CDB_WR_SQLSTAT LIMIT 1;The query results are as follows:
*************************** 1. row *************************** TENANT_ID: 1 SNAP_ID: 1 SVR_IP: xx.xx.xx.xx SVR_PORT: 2882 SQL_ID: 01058E374A5EF26873BA4D99C4A74582 PLAN_HASH: 5547575357486659143 PLAN_TYPE: 1 MODULE: NULL ACTION: NULL PARSING_DB_ID: 201001 PARSING_DB_NAME: oceanbase PARSING_USER_ID: 200001 EXECUTIONS_TOTAL: 3 EXECUTIONS_DELTA: 3 DISK_READS_TOTAL: 1 DISK_READS_DELTA: 1 BUFFER_GETS_TOTAL: 1 BUFFER_GETS_DELTA: 1 ELAPSED_TIME_TOTAL: 4864 ELAPSED_TIME_DELTA: 4864 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: 1 PHYSICAL_READ_REQUESTS_DELTA: 1 PHYSICAL_READ_BYTES_TOTAL: 194 PHYSICAL_READ_BYTES_DELTA: 194 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: 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: 2025-10-15 11:11:19.638416 PLAN_CACHE_HIT_TOTAL: 1 PLAN_CACHE_HIT_DELTA: 1 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 setThe columns are described as follows:
ColumnDescriptionTENANT_ID Tenant ID SNAP_ID Snapshot ID SVR_IP Node IP address 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 the cache of SQL plans, this field indicates the plan type: 1: indicates Local Plan2: indicates Remote Plan3: indicates Distributed Plan
For the cache of PL objects, this field indicates the PL object type:1: indicates Procedure2: indicates Function3: indicates Package4: indicates Anonymous Block
MODULE The application Module when the statement was parsed for the first time, which is set by using the DBMS_APPLICATION_INFO.SET_MODULEpackageACTION The application Action when the statement was parsed for the first time, which is set by using the DBMS_APPLICATION_INFO.SET_ACTIONpackagePARSING_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 in the total number of executions of the plan in the plan cache DISK_READS_TOTAL Total number of disk reads DISK_READS_DELTA Increment in the number of disk reads BUFFER_GETS_TOTAL Total number of logical reads from the cache BUFFER_GETS_DELTA Increment in the number of logical reads from the cache ELAPSED_TIME_TOTAL Total time consumed by the statement. In parallel execution scenarios, this value is the cumulative value of each execution thread. ELAPSED_TIME_DELTA Increment in the time consumed by the statement CPU_TIME_TOTAL Total CPU time CPU_TIME_DELTA Increment in total CPU time CCWAIT_TOTAL Total time consumed by concurrency wait events CCWAIT_DELTA Increment in the total time consumed by concurrency wait events USERIO_WAIT_TOTAL Total time consumed by I/O waits USERIO_WAIT_DELTA Increment in the total time consumed by I/O waits APWAIT_TOTAL Total time consumed by application wait events APWAIT_DELTA Increment in the total time consumed by application wait events PHYSICAL_READ_REQUESTS_TOTAL Total number of physical read requests PHYSICAL_READ_REQUESTS_DELTA Increment in the number of physical read requests PHYSICAL_READ_BYTES_TOTAL Total number of physical read bytes PHYSICAL_READ_BYTES_DELTA Increment in the number of physical read bytes WRITE_THROTTLE_TOTAL Total time throttled when writing to MemStore WRITE_THROTTLE_DELTA Increment in the total time throttled when writing to MemStore ROWS_PROCESSED_TOTAL Total number of rows processed by the statement ROWS_PROCESSED_DELTA Increment in 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 in 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 in 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 in the total number of rows read from MAJOR SSStore RPC_TOTAL Total number of RPCs RPC_DELTA Increment in the number of RPCs FETCHES_TOTAL Total number of result set fetches FETCHES_DELTA Increment in the number of result set fetches RETRY_TOTAL Total number of SQL retries RETRY_DELTA Increment in the number of SQL retries PARTITION_TOTAL Total number of partitions scanned during SQL execution PARTITION_DELTA Increment in the number of partitions scanned during SQL execution NESTED_SQL_TOTAL Total number of nested SQL executions, which increases by 1 each time a sub-SQL is executed NESTED_SQL_DELTA Increment in the number of nested SQL executions SOURCE_IP IP address of the request source SOURCE_PORT Port number of the request source ROUTE_MISS_TOTAL Total number of times the requested partition was not correctly hit since the execution plan was generated ROUTE_MISS_DELTA Total number of times the requested partition was 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 Plan Cache hits since the execution plan was generated PLAN_CACHE_HIT_DELTA Number of Plan Cache hits since the last WR snapshot was collected MUTI_QUERY_TOTAL Number of times this SQL was in Muti Query since the execution plan was generated MUTI_QUERY_DELTA Number of times this SQL was in Muti Query since the last WR snapshot was collected MUTI_QUERY_BATCH_TOTAL Number of times this SQL Batch was executed since the execution plan was generated MUTI_QUERY_BATCH_DELTA Number of times this SQL Batch was executed since the last WR snapshot was collected FULL_TABLE_SCAN_TOTAL Number of full table scans performed by this SQL since the execution plan was generated FULL_TABLE_SCAN_DELTA Number of full table scans performed by this SQL since the last WR snapshot was collected ERROR_COUNT_TOTAL Number of errors reported by this SQL since the execution plan was generated ERROR_COUNT_DELTA Number of errors reported by this SQL since the last WR snapshot was collected For more information about the
oceanbase.CDB_WR_SQLSTATview, see oceanbase.CDB_WR_SQLSTAT.View basic performance statistics of SQL statements executed by the current tenant through the
oceanbase.DBA_WR_SQLSTATview.Columns containing
_DELTAindicate the increment in statistics from the last WR snapshot collection to the current time.Those with
_DELTAin their names indicate the incremental values of statistics 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:
*************************** 1. row *************************** SNAP_ID: 1 SVR_IP: xx.xx.xx.xx SVR_PORT: 2882 SQL_ID: 056E37B2111B414A8B917B1C29F3C9F6 PLAN_HASH: 9282102774431564901 PLAN_TYPE: 1 MODULE: NULL ACTION: NULL PARSING_DB_ID: 201001 PARSING_DB_NAME: oceanbase PARSING_USER_ID: 200001 EXECUTIONS_TOTAL: 5 EXECUTIONS_DELTA: 5 DISK_READS_TOTAL: 1 DISK_READS_DELTA: 1 BUFFER_GETS_TOTAL: 1 BUFFER_GETS_DELTA: 1 ELAPSED_TIME_TOTAL: 3364 ELAPSED_TIME_DELTA: 3364 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: 1 PHYSICAL_READ_REQUESTS_DELTA: 1 PHYSICAL_READ_BYTES_TOTAL: 193 PHYSICAL_READ_BYTES_DELTA: 193 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: 0 RETRY_DELTA: 0 PARTITION_TOTAL: 5 PARTITION_DELTA: 5 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: 15-OCT-25 11.15.20.963356 AM PLAN_CACHE_HIT_TOTAL: 4 PLAN_CACHE_HIT_DELTA: 4 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 setThe columns are described as follows:
ParameterDescriptionSNAP_ID Snapshot ID SVR_IP Node IP address 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 the SQL plan cache, this field indicates the plan type: 1: indicates a Local Plan2: indicates a Remote Plan3: indicates a Distributed Plan
For the PL object cache, this field indicates the PL object type:1: indicates a Procedure2: indicates a Function3: indicates a Package4: indicates an Anonymous Block
MODULE Application Module when the statement was first parsed, set by using the DBMS_APPLICATION_INFO.SET_MODULEpackageACTION Application Action when the statement was first parsed, set by using the DBMS_APPLICATION_INFO.SET_ACTIONpackagePARSING_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 Delta in the total number of executions of the plan in the plan cache DISK_READS_TOTAL Total number of disk reads DISK_READS_DELTA Delta in the number of disk reads BUFFER_GETS_TOTAL Total number of logical reads from the cache BUFFER_GETS_DELTA Delta in 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 accumulated value of each execution thread. ELAPSED_TIME_DELTA Delta in the time consumed by the statement CPU_TIME_TOTAL Total CPU time CPU_TIME_DELTA Delta in the total CPU time CCWAIT_TOTAL Total time consumed by concurrency-class wait events CCWAIT_DELTA Delta in the total time consumed by concurrency-class wait events USERIO_WAIT_TOTAL Total time consumed by I/O waits USERIO_WAIT_DELTA Delta in the total time consumed by I/O waits APWAIT_TOTAL Total time consumed by application-class wait events APWAIT_DELTA Delta in the total time consumed by application-class wait events PHYSICAL_READ_REQUESTS_TOTAL Total number of physical read requests PHYSICAL_READ_REQUESTS_DELTA Delta in the number of physical read requests PHYSICAL_READ_BYTES_TOTAL Total number of bytes physically read PHYSICAL_READ_BYTES_DELTA Delta in the number of bytes physically read WRITE_THROTTLE_TOTAL Total time throttled when writing to MemStore WRITE_THROTTLE_DELTA Delta in the total time throttled when writing to MemStore ROWS_PROCESSED_TOTAL Total number of rows processed by the statement ROWS_PROCESSED_DELTA Delta in the total number of rows processed by the statement MEMSTORE_READ_ROWS_TOTAL Total number of rows read from MemStore MEMSTORE_READ_ROWS_DELTA Delta in 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 Delta in 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 Delta in the total number of rows read from MAJOR SSStore RPC_TOTAL Total number of RPCs RPC_DELTA Delta in the number of RPCs FETCHES_TOTAL Total number of result set fetches FETCHES_DELTA Delta in the number of result set fetches RETRY_TOTAL Total number of SQL retries RETRY_DELTA Delta in the number of SQL retries PARTITION_TOTAL Total number of partitions scanned during SQL execution PARTITION_DELTA Increment in the number of partitions scanned during SQL execution NESTED_SQL_TOTAL Total number of nested SQL executions; increments by 1 each time an additional child SQL is executed NESTED_SQL_DELTA Increment in 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 has not been correctly hit since the execution plan was generated ROUTE_MISS_DELTA Total number of times the request partition has not been 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 Plan Cache hits since the execution plan was generated PLAN_CACHE_HIT_DELTA Number of Plan Cache hits since the last WR snapshot was collected MUTI_QUERY_TOTAL Number of times this SQL has been in Muti Query since the execution plan was generated MUTI_QUERY_DELTA Number of times this SQL has been in Muti Query since the last WR snapshot was collected MUTI_QUERY_BATCH_TOTAL Number of times this SQL Batch has been executed since the execution plan was generated MUTI_QUERY_BATCH_DELTA Number of times this SQL Batch has been executed since the last WR snapshot was collected FULL_TABLE_SCAN_TOTAL Number of full table scans performed by this SQL since the execution plan was generated FULL_TABLE_SCAN_DELTA Number of full table scans performed by this SQL since the last WR snapshot was collected ERROR_COUNT_TOTAL Number of errors reported by this SQL since the execution plan was generated ERROR_COUNT_DELTA Number of errors reported by this SQL since the last WR snapshot was collected For more information about the
DBA_WR_SQLSTATview, see oceanbase.DBA_WR_SQLSTAT and DBA_WR_SQLSTAT.
View OBServer resource group information
View the statistics information of resource groups on all OBServers through 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 columns are described as follows:
ColumnDescriptionCON_ID Tenant ID GROUP_ID Consumer group ID SVR_IP Node IP SVR_PORT Node PORT STATISTIC# Statistic number NAME Statistic name CLASS Statistic class VALUE Statistic value VALUE_TYPE Value type: cumulative type or assignment type STAT_ID Statistic ID For more information about the
GV$OB_RES_MGR_SYSSTATview, see GV$OB_RES_MGR_SYSSTAT.View the statistics information of resource groups on the current OBServer through 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 columns are described as follows:
ColumnDescriptionCON_ID Tenant ID GROUP_ID Consumer group ID SVR_IP Node IP SVR_PORT Node PORT STATISTIC# Statistic number NAME Statistic name CLASS Statistic class VALUE Statistic value VALUE_TYPE Value type: cumulative type or assignment type STAT_ID Statistic ID For more information about the
V$OB_RES_MGR_SYSSTATview, see V$OB_RES_MGR_SYSSTAT.