Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the idea of SQL tuning". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

In general, you need to pay attention to the following four types of Top SQL

Consume the most CPU (too much logical IO)

Resulting in too much physical Ihop O

Those whose execution times are relatively frequent

Those with longer execution time

We know that there is a famous formula for the response time of a statement:

Response time = service time + waiting time

Where the service time is the time CPU spent executing the statement.

Service time = analysis time + recursion time + execution time

The parsing time is the time that CPU used to analyze the statement, the recursive time is the time that CPU spent on the recursive SQL of the statement, and the rest is the real time that CPU used to execute the statement.

So, where did the above time information come from? there are some time statistics 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 above three statistics:

Execution time = CPU used by this session-parse time cpu-recursive cpu usage

If the execution time accounts for a large proportion of the overall 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 overall response time, the next step is to find out which SQL analyses are excessive, which are listed in the SQL ordered by Parse Calls in the statspack report.

If the wait time accounts for a large proportion of the overall response time, and the wait is mainly related to block reads, the next step is to find out which SQL is causing too many physical reads, you can check the SQL ordered by Reads section of the statspack report.

So, according to a simple principle listed above, we need to pay attention to three statistics about CPU time: CPU used by this session, parse time cpu, and recursive cpu usage, and the IO-related wait time in top5 wait events. If other wait events appear in Top5, you may need to analyze the cause according to different wait events. Then priority is given to tuning the relevant SQL that consumes the most time.

In addition to the above SQL ordered by Gets (most logical IO), SQL ordered by Parse Calls (too much soft parsing), SQL ordered by Reads (too much physical IO), statspack lists Top SQL in a number of other ways, and these Top SQL require special attention in some cases. For example:

If the number of times of SQL ordered by Executions execution exceeds 100,

SQL ordered by Sharable Memory occupies more than 1m of library cache

SQL ordered by Version Count with a cursor of more than 20

If there is no statspack, then according to the statistics in v$sysstat/v$sesstat, combined with v$sql/v$sqlarea, you can also get the relevant SQL.

V$sql has one row of statistical records for each child cursor, while v$sqlarea has only one row of statistical records for the same parent cursor, that is, v$sqlarea is a result of group by the v$sql according to the parent cursor. There are columns such as buffer_gets,parse_calls,disk_reads,executions,sharable_mem in both views, which correspond to the conditions under which the report lists Top SQL.

This is the end of the content of "what is the idea of SQL tuning". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.