Analyzing Oracle AWR reports from my clients is one of my daily tasks. A common challenge is that these reports often cover too broad a timeframe, diluting the severity of performance issues.
To address this, I developed the following script that automates AWR reports generation with a narrow focus. When clients do not have a specific time period in mind to diagnose, they can use this script to narrows down the diagnosed time range from two dimensions:
- Peak time focus. This script identifies the snapshot ID corresponding to the highest workload and generates AWR reports for the peak period. By focusing on the time of highest activity, it provides a clearer view of potential performance issues.
- Instance specific. In multi-instance environments, this script generates AWR reports for each instance individually rather than a single database-wide report covering all instances. This method helps in pinpointing issues specific to each instance which can be masked when looking at an aggregated report.
By using this script, clients can provide more targeted AWR reports, facilitating the identification and resolution of Oracle databas performance problems.
-- This script generates AWR reports for the peak time over the past back_days (defaulted to 7 days).
-- Dated July 2024
-- Author: Yuan Yao
CREATE OR REPLACE DIRECTORY tmp AS '/tmp/';
DECLARE
back_days NUMBER := 7; -- Customize the number of back days here
peak_id NUMBER;
my_dbid NUMBER;
today VARCHAR2(30);
awr_dir VARCHAR2(40) := 'TMP';
awr_file UTL_FILE.FILE_TYPE;
awr_file_name VARCHAR2(60);
BEGIN
-- Get the peak snap_id
SELECT snap_id
INTO peak_id
FROM (
SELECT snap_id, average, end_time
FROM dba_hist_sysmetric_summary
WHERE average = (SELECT MAX(average)
FROM dba_hist_sysmetric_summary
WHERE metric_name = 'Average Active Sessions'
AND end_time > SYSDATE - back_days)
)
WHERE ROWNUM = 1;
-- Get the DBID
SELECT dbid
INTO my_dbid
FROM v$database;
-- Get the current date and time
SELECT TO_CHAR(SYSDATE, 'YYYY_MON_DD_HH24_MI')
INTO today
FROM dual;
-- Loop through each instance in the RAC environment
FOR instance_rec IN (SELECT instance_number, instance_name FROM gv$instance) LOOP
awr_file_name := 'awr_' || today || '_inst' || instance_rec.instance_number || '.html';
awr_file := UTL_FILE.FOPEN(awr_dir, awr_file_name, 'w', 32767);
-- Generate the AWR report in HTML format for each instance
FOR curr_awr IN (
SELECT output
FROM TABLE(dbms_workload_repository.awr_report_html(
my_dbid,
instance_rec.instance_number,
peak_id - 1, peak_id,
0))
)
LOOP
UTL_FILE.PUT_LINE(awr_file, curr_awr.output);
END LOOP;
UTL_FILE.FCLOSE(awr_file);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(awr_file) THEN
UTL_FILE.FCLOSE(awr_file);
END IF;
RAISE;
END;
/
The following example is the AWR reports from a two-node database:
[oracle@rac19c1 tmp]$ ls -lrt
...
-rw-r--r-- 1 oracle asmadmin 1084024 Jul 31 10:43 awr_2024_JUL_31_10_42_orcl2.html
-rw-r--r-- 1 oracle asmadmin 1117641 Jul 31 10:43 awr_2024_JUL_31_10_42_orcl1.html
Yuan Yao, An Oracle ACE's Blog 
Leave a comment