How to collect AWR performance reports during the busiest periods of the system

As we all know, AWR plays an important role in troubleshooting and performance optimization, A common idea is that we can combine the two AWR performance reports during busy hours and idle hours to clearly see the changes in database performance. Q: What should I do if you want to collect AWR performance reports during the busiest periods of the database? How can we determine the busiest time of a database? Thinking

As we all know, AWR plays an important role in troubleshooting and performance optimization, A common idea is that we can combine the two AWR performance reports during busy hours and idle hours to clearly see the changes in database performance. Q: What should I do if you want to collect AWR performance reports during the busiest periods of the database? How can we determine the busiest time of a database? Thinking

As we all know, AWR plays an important role in troubleshooting and performance optimization, A common idea is that we can combine the two AWR performance reports during busy hours and idle hours to clearly see the changes in database performance.

Q: What should I do if you want to collect AWR performance reports during the busiest periods of the database? How can we determine the busiest time of a database?

Train of Thought: We can roughly judge whether the database is idle or busy in some TIME periods through the database TIME, and generate an AWR performance report by reading the corresponding SNAP_ID. The following is a demo process, you can make slight adjustments as required.

Collect the two interval SNAP_ID of the busiest time of the Database System

Select * from (SELECT snap_id, (nvl (LEAD (snap_id, 1) OVER (order by snap_id), 0) AS "UP_snapid", value, (nvl (LEAD (value, 1) OVER (order by value), 0) AS "UP_value", (nvl (LEAD (value, 1) OVER (order by value), 0 )) -value as "Result" FROM dba_hist_sysstat where stat_name = 'db time' order by 5 desc) where "Result"> 0 running Result is as follows:

Read AWR information through DBMS package

SELECT output  FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(4160719624,                                                      1,                                                      2335,                                                      2336));

The running result is as follows:

Combined with the above two simple steps, you can quickly collect AWR performance reports during busy hours of the database. However, this is only an AWR performance report of two adjacent snapids. Each SNAP_ID is 1 hour by default, friends can also use this idea to collect AWR performance reports for the busiest period of M in N days. I will not go into details here.

Bytes -------------------------------------------------------------------------------------------------

This article comes from my technical blog http://blog.csdn.net/robo23

For reprinting, please mark the source text link; otherwise, you will be held legally responsible!