The execution of an SQL request goes through two phases: compilation and execution. In the compilation phase, the optimizer performs operations such as lexical analysis, syntax analysis, syntax optimization, and code generation to generate an execution plan. The plan is submitted to the executor for execution, which returns the results. The process of parsing an SQL request and generating an execution plan is called hard parsing. Hard parsing consumes resources and increases SQL latency. To avoid the overheads of hard parsing for each request, execution plans are stored in the plan cache. When the optimizer receives a subsequent SQL request, it first checks the plan cache to see whether an execution plan for the request exists in the cache. If so, the optimizer retrieves the plan from the cache and submits it to the executor, which returns the results. This process is called soft parsing. Therefore, whether an execution plan is stored in the plan cache and whether the plan is optimal are key to performance optimization.
The optimizer may generate a non-optimal execution plan for various reasons, such as missing indexes, outdated statistics, and account privileges. A non-optimal execution plan results in a large number of logical reads and scans of a large amount of data, which increases the latency and decreases the throughput.
You can query the V$OB_SQL_AUDIT view to determine whether performance issues are caused by SQL statements and suspicious SQL statements. You can then query plan-related views to determine whether execution plans are optimal. If an execution plan is non-optimal, you can bind the plan to a statement by using the CREATE OUTLINE statement or recover from the suboptimal performance by scaling up resources or implementing traffic control.
Note
For more information about the V$OB_SQL_AUDIT view, see V$OB_SQL_AUDIT(Oracle mode) and V$OB_SQL_AUDIT(MySQL mode).
For example, the following example shows how to identify TopSQL statements and determine whether SQL statements cause performance issues. The SQL statement with the SQL_ID of 2705182A6EAB699CEC8E59DA80710B64 is the top SQL statement that consumes the most tenant resources and has the highest logical read count.
obclient>
select SQL_ID,
avg(ELAPSED_TIME),
avg(QUEUE_TIME),
avg(ROW_CACHE_HIT + BLOOM_FILTER_CACHE_HIT + BLOCK_CACHE_HIT + DISK_READS) avg_logical_read,
avg(execute_time) avg_exec_time,
count(*) cnt,
avg(execute_time - TOTAL_WAIT_TIME_MICRO ) avg_cpu_time,
avg( TOTAL_WAIT_TIME_MICRO ) avg_wait_time,
WAIT_CLASS,
avg(retry_cnt)
from v$OB_SQL_AUDIT
group by 1
order by avg_exec_time * cnt desc limit 10;
+----------------------------------+-------------------+-----------------+------------------+---------------+--------+--------------+---------------+------------+----------------+
| SQL_ID | avg(ELAPSED_TIME) | avg(QUEUE_TIME) | avg_logical_read | avg_exec_time | cnt | avg_cpu_time | avg_wait_time | WAIT_CLASS | avg(retry_cnt) |
+----------------------------------+-------------------+-----------------+------------------+---------------+--------+--------------+---------------+------------+----------------+
| 2705182A6EAB699CEC8E59DA80710B64 | 54976.9269 | 43.8605 | 17664.2727 | 54821.5828 | 11759 | 54821.5828 | 0.0000 | OTHER | 0.0000 |
| 32AB97A0126F566064F84DDDF4936F82 | 1520.9832 | 380.7903 | 63.7847 | 789.6781 | 63632 | 789.6781 | 0.0000 | OTHER | 0.0000 |
| A5F514E873BE9D1F9A339D0DA7481D69 | 44032.5553 | 44.5149 | 8943.7834 | 43878.1405 | 1039 | 43878.1405 | 0.0000 | OTHER | 0.0000 |
| 31FD78420DB07C11C8E3154F1658D237 | 7769857.0000 | 35.7500 | 399020.7500 | 7769682.7500 | 4 | 7769682.7500 | 0.0000 | NETWORK | 1.0000 |
| C48AEE941D985D8DEB66892228D5E845 | 8528.6227 | 0.0000 | 0.0000 | 8450.4047 | 1601 | 8450.4047 | 0.0000 | OTHER | 0.0000 |
| 101B7B79DFA9AE801BEE4F1A234AD294 | 158.2296 | 41.7211 | 0.0000 | 46.0345 | 286758 | 46.0345 | 0.0000 | OTHER | 0.0000 |
| 1D0BA376E273B9D622641124D8C59264 | 1774.5924 | 0.0049 | 0.0000 | 1737.4885 | 5081 | 1737.4885 | 0.0000 | OTHER | 0.0000 |
| 64CF75576816DB5614F3D5B1F35B1472 | 1801.8767 | 747.0343 | 0.0000 | 827.1674 | 10340 | 827.1674 | 0.0000 | OTHER | 0.0000 |
| 23D1C653347BA469396896AD9B20DCA1 | 5564.9419 | 0.0000 | 0.0000 | 5478.2228 | 1257 | 5478.2228 | 0.0000 | OTHER | 0.0000 |
| FA4F493FA5CE2DCC64F51CF3754F96C6 | 2478.3956 | 378.7557 | 3.1040 | 1731.1802 | 3357 | 1731.1802 | 0.0000 | OTHER | 0.0000 |
+----------------------------------+-------------------+-----------------+------------------+---------------+--------+--------------+---------------+------------+----------------+
10 rows in set (1.34 sec)