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

This article mainly introduces how to view the slowest sql sentences according to the awr report, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

When the system has been running for a period of time, the system basically tends to be stable, and SQL tuning has also become a major work of DBA. Most of the time, we can check out the SQL statements with performance bottlenecks by looking at the awr report, through which we can clearly see the specific running time, times, the proportion of CPU and IO.

But it is tedious to check the awr report every time, and if you can see which sql has problems separately, you don't have to generate an awr report every time. The dba_hist_sqlstat view records the sql information in each snap_id, and here we help you write the whole script.

Click (here) to collapse or open

SELECT v.SQLZTEXT, m.* FROM (select distinct snap_id

Sql_id

EXECUTIONS_DELTA

Trunc (max (ELAPSED_TIME_DELTA))

OVER (PARTITION BY snap_id, sql_id) / 1000000

0) max_elapsed

Trunc (max (ELAPSED_TIME_DELTA))

OVER (PARTITION BY snap_id, sql_id)) /

(SUM (ELAPSED_TIME_DELTA) OVER (PARTITION BY snap_id))

2) * 100 per_total

From dba_hist_sqlstat t WHERE T.snap_id IN (SELECT MAX (snap_id) FROM dba_hist_sqlstat)) M _ mam vault SQL v

Where m.sql_id=v.sql_id and m.max_elapsed > = 300

This script looks for m.max_elapsed > = 300 (the unit here is seconds), which is also a 5-minute time, which can be defined according to the actual situation of the system.

It can be seen that the sql_ id value is once run by d1ftvurv76hct, and the running time is 1199s, accounting for 36% of the total consumption.

[in addition] you can execute this sql statement by running timed job, and then send the problematic sql information to the user's mailbox

Thank you for reading this article carefully. I hope the article "how to View the slowest sql sentences according to the awr report" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.