The previous chapter describes system monitoring, which provides specific monitoring information for you to determine the health conditions of clusters, tenants, and sessions. This chapter describes SQL monitoring, another crucial monitoring method in actual O&M. SQL monitoring allows you to diagnose system performance issues based on the monitoring information collected during multiple or specific executions of specific SQL statements.
SQL monitoring information includes the resource consumption during SQL execution, which is obtained by collecting the statistics of metrics and wait events before and after an SQL statement is executed, and calculating the difference. In addition, SQL monitoring provides information such as the SQL text, SQL plan, and execution feedback, which is quite helpful in diagnosing an SQL statement.
The following figure shows the workflow for diagnosing a possible SQL issue:
OceanBase Database provides many views for SQL diagnostics. The following table describes important ones.
View |
Description |
|---|---|
GV$OB_SQL_AUDIT |
Displays the SQL statistics. |
GV$OB_PLAN_CACHE_PLAN_STAT |
Displays the statistics of execution plans. |
GV$OB_PLAN_CACHE_PLAN_EXPLAIN |
Displays the details of execution plans. |
DBA_OB_OUTLINES |
Displays the general outlines. |
DBA_OB_CONCURRENT_LIMIT_SQL |
Displays the throttling outlines. |
GV$OB_PLAN_CACHE_STAT |
Displays the plan cache statistics. |
GV$SQL_PLAN_MONITOR |
Displays the statistics of SQL operators. |
GV$SESSION_EVENT |
Displays the statistics of session-level wait events. |
GV$OB_TRANSACTION_PARTICIPANTS |
Displays the information about participants of the active transaction. |