How to identify the most resource-consuming SQL statements

Generally, the first-hand information for tuning is probably a statspack report in a typical business period. How can we determine which sqls consume the most system resources based on the statspack report? Which SQL statements need to be adjusted most? A general optimization idea is provided here. Of course, the idea is dead, people are active, and optimization also needs to be taken on demand.

In general, you need to pay attention to the following four top SQL statements:

• Maximum CPU consumption (excessive logical Io)
• Resulting in excessive physical I/O
• Executed frequently
• Long execution time

We know that the response time of a statement has a well-known formula:

Response time = service time + wait time

The service time is the time that the CPU takes to execute the statement.

Service time = analysis time + recursive time + execution time

The analysis time is the time when the CPU is used to analyze the Statement, the recursion time is the time when the CPU is used to recursive SQL statements, and the rest is the real time when the CPU is used to execute the statement.

So where did the above time information come from? Some time statistics are provided in the system statistics provided by Oracle:

Service time = CPU used by this session
Analysis time = parse time CPU
Recursive time = recursive CPU usage

Then, the execution time can be calculated based on the preceding three statistical information:

Execution time = CPU used by this session-Parse time CPU-recursive CPU usage

• If the execution time accounts for a large proportion of the response time, the next step is to find the SQL statements that cause the most logical Io, which can be found in the SQL ordered by gets section of the statspack report.
• If the analysis time accounts for a large proportion of the response time, the next step is to find out which SQL analyses are too large, which is listed in SQL ordered by parse CILS IN THE statspack report.
• If the waiting time accounts for a large proportion of the total response time and is mainly about waiting for block reading, the next step is to find out which SQL statements cause excessive physical reads, you can view the SQL ordered by reads Section in the statspack report.

According to a simple principle listed above, we need to pay attention to three statistical information about CPU time: CPU used by this session, parse time CPU and recursive CPU usage, and the IO-related wait time in the top 5 wait events. If other wait events appear in the top 5, you may need to analyze the cause based on different wait events. Then the related SQL statements that consume the most time are prioritized.

Apart from the preceding SQL ordered by gets (the most logical Io), SQL ordered by parse CILS (excessive soft resolution), SQL ordered by reads (excessive physical Io ), statspack also lists top SQL statements in other ways. In some cases, these top SQL statements require special attention. For example

• The number of SQL ordered by executions exceeds 100
• SQL ordered by sharable memory occupies more than 1 MB of library Cache
• SQL ordered by version count sub-cursor over 20

If no statspack exists, you can obtain related SQL statements based on the statistics in V $ sysstat/V $ sesstat and V $ SQL/V $ sqlarea.

V $ SQL has a statistical record for each sub-cursor, while V $ sqlarea only has one row of statistical record for the same parent cursor, that is, V $ sqlarea is a result of group by for V $ SQL according to the parent cursor. Both views have columns such as buffer_gets, parse_cils, disk_reads, executions, and sharable_mem, which correspond to the top SQL conditions listed in statspack.