The execution process of an SQL query is divided into a compilation phase and an execution phase. An execution plan is generated in the compilation phase, which consists of several sub-phases such as lexical parsing, syntax parsing, syntax optimization, and code generation. Then, the execution plan is submitted to the executor for execution, and the result is returned. The process of parsing an SQL query to generate the execution plan is known as hard parsing. Hard parsing consumes considerable resources and affects the duration of SQL execution. Therefore, the execution plan is stored in the plan cache. When the same SQL query is received next time, the system first checks whether it hits the plan cache. If yes, the cached plan is extracted and submitted to the executor for execution. This process is defined as soft parsing. This way, it is unnecessary to perform hard parsing for each SQL query, thereby reducing resource consumption. Therefore, the key to performance tuning is to make sure that SQL queries hit the plan cache and the plan is optimal.
The optimizer may generate suboptimal execution plans due to various reasons, such as missing indexes, outdated statistics, and cardinality. The execution of a suboptimal execution plan requires massive logical reads and scanning of a large amount of data. As a result, the time consumption increases and the throughput decreases.
You can use the V$OB_SQL_AUDIT view to determine whether the performance bottleneck is caused by SQL execution and, if yes, identify the suspicious SQL statements. Then, you can use the related views to check the execution plans. If a suboptimal execution plan is used, you can execute the CREATE OUTLINE statement to bind a specific execution plan, scale out the system, or perform throttling as needed.
Note
For more information about the V$OB_SQL_AUDIT view, see V$OB_SQL_AUDIT (Oracle mode) or V$OB_SQL_AUDIT (MySQL mode).
The following example shows how to retrieve top SQL statements and determine whether SQL execution exceptions exist. The SQL statement whose SQL_ID is 2705182A6EAB699CEC8E59DA80710B64 tops the list. It consumes the most tenant resources and involves a large number of logical reads. Therefore, you need to optimize this SQL statement.
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)