The SQL diagnostics feature provides a wide range of metric statistics. You can create custom columns to filter SQL statement execution statistics.
Metrics in the Column Management dialog box are classified into four types: time model metrics, common metrics, basic information metrics, and execution plan metrics.
Time model metrics
After an OBServer node receives an SQL request from a user, it creates timestamps to record the time consumed when the SQL statement is executed in each stage within OceanBase Database. The following figure shows the runtime time model for a local SQL statement:
As shown in the preceding figure, the time model includes the following time segments:
- Network transmission time: the interval between the time when the client sends the SQL statement and the time when the OBServer node receives the SQL statement, which is the time consumed on the network.
- Network wait time: the interval between the time when the OBServer node receives the SQL statement and the time when the SQL statement enters the processing queue of the OBServer node, which is the time consumed at the network architecture layer of the OBServer node.
- Queuing time: the interval between the time when the SQL statement enters the processing queue and the time when the SQL statement is dequeued. When processing resources of the OBServer node are insufficient, the SQL statement waits in the queue until sufficient processing resources are available.
- Parsing time: the interval between the time when the SQL statement is dequeued and the time when the physical plan is executed, which is the time spent by the SQL engine of OceanBase Database in generating or obtaining a plan.
- Execution time: the interval between the execution start time and end time of the plan. The execution time consists of CPU time and wait time. The wait time is classified into the application wait time, concurrency wait time, I/O wait time, and scheduling wait time.
The following table describes the metrics in the SQL execution time model provided by OCP.
| Metric | Description |
|---|---|
| Total Response Time | The total response time, in ms, of the SQL statement within the specified period of time. |
| Maximum Response Time | The maximum response time, in ms, of the SQL statement within the specified period of time. |
| Maximum CPU Time | The maximum CPU time, in ms, of the SQL statement within the specified period of time. |
| Network Transmission Time | The average network transmission time, in ms, of the SQL statement within the specified period of time. |
| Network Jointime | The average network wait time, in ms, of the SQL statement within the specified period of time. |
| Queuing Time | The average queuing time, in ms, of the SQL statement within the specified period of time. |
| Parsing Time | The average syntax parsing time, in ms, of the SQL statement within the specified period of time. |
| Latency | The average wait time, in ms, of the SQL statement within the specified period of time. |
| Total Latency | The total wait time, in ms, of the SQL statement within the specified period of time. |
| Application Latency | The average wait time, in ms, of application events within the specified period of time. |
| Concurrency Latency | The average wait time, in ms, of concurrency events within the specified period of time. |
| I/O Latency | The average wait time, in ms, of user I/O events within the specified period of time. |
| Scheduling Latency | The average wait time, in ms, of scheduling events within the specified period of time. |
Common metrics
| Metric | Description |
|---|---|
| Event with Maximum Latency | The event with the maximum wait time within the specified period of time. |
| Executions | The total number of executions of the SQL statement within the specified period of time. |
| Executions per Second | The average number of executions of the SQL statement per second within the specified period of time. |
| Average Response Time | The average response time, in ms, of the SQL statement within the specified period of time. The response time is the total time elapsed from when the client sends the SQL statement to when the client obtains the SQL execution result. |
| Error Count | The number of execution errors of the SQL statement within the specified period of time. |
| Average CPU Time | The average CPU time, in ms, of the SQL statement within the specified period of time. |
| Plan Generation Time | The average time, in ms, spent in generating an SQL plan within the specified period of time. |
| Execution Time | The average time, in ms, spent in executing an SQL plan within the specified period of time. |
| Plans Not Retrieved | The SQL plan miss rate within the specified period of time. |
Basic information metrics
| Metric | Description |
|---|---|
| SQL Text | The SQL text. |
| SQL ID | The ID of the SQL statement. |
| Server | The IP address of the OBServer node. |
| Database | The database accessed by the SQL statement. |
| User | The user that executes the SQL statement. |
| SQL Type | The SQL type. For example, the SQL type can be SELECT, INSERT, UPDATE, or DELETE. |
| Internal SQL | Indicates whether the SQL statement is initiated by OceanBase Database. |
Execution plan metrics
| Metric | Description |
|---|---|
| Updated Rows | The average number of rows updated by the SQL statement within the specified period of time. |
| Returned Rows | The average number of rows returned by the SQL statement within the specified period of time. |
| Accessed Partitions | The average number of partitions accessed by the SQL statement within the specified period of time. |
| Proportion of Errors | The percentage of errors of the SQL statement within the specified period of time. |
| Timeout Errors | The total number of timeout errors (error code: 4012) of the SQL statement within the specified period of time. |
| Insufficient Memory Errors | The total number of insufficient memory errors (error code: 4013) of the SQL statement within the specified period of time. |
| Syntax Errors | The total number of syntax parsing errors (error code: 5001) of the SQL statement within the specified period of time. |
| Conflicting Key Errors | The total number of conflicting key errors (error code: 5024) of the SQL statement within the specified period of time. |
| Data Overlength Errors | The total number of data overlength errors (error code: 5167) of the SQL statement within the specified period of time. |
| Unknown Column Errors | The total number of unknown column errors (error code: 5217) of the SQL statement within the specified period of time. |
| Transaction Roll-back Errors | The total number of transaction roll-back errors (error code: 6002) of the SQL statement within the specified period of time. |
| Waits | The average number of waits of the SQL statement within the specified period of time. |
| RPCs | The average number of RPCs sent by the SQL statement within the specified period of time. |
| Proportion of Local Plans | The percentage of local SQL plans within the specified period of time. |
| Proportion of Remote Plans | The percentage of remote SQL plans within the specified period of time. |
| Proportion of Distributed Plans | The percentage of distributed SQL plans within the specified period of time. |
| RPC Requests | The average number of RPC requests executed by the SQL statement within the specified period of time. |
| Row Cache Hit | The total number of row cache hits during the execution of the SQL statement within the specified period of time. |
| Bloom Filter Cache Hit | The total number of Bloom filter cache hits during the execution of the SQL statement within the specified period of time. |
| Block Cache Hit | The total number of block cache hits during the execution of the SQL statement within the specified period of time. |
| Block Index Cache Hit | The total number of block index cache hits during the execution of the SQL statement within the specified period of time. |
| Physical Reads | The average number of physical reads of the SQL statement within the specified period of time. |
| Retries | The total number of retries of the SQL statement within the specified period of time. |
| Table Scan | The average number of table scans performed by the SQL statement within the specified period of time. |
| Proportion of Strong Consistency Transactions | The percentage of strong-consistency transactions within the specified period of time. |
| Proportion of Weak Consistency Transactions | The percentage of weak-consistency transactions within the specified period of time. |
| Rows of Memory Reads | The average number of rows read by the SQL statement from the MemStore within the specified period of time. |
| Rows of Physical Reads | The average number of rows read by the SQL statement from the SSStore within the specified period of time. |