AWR Reports: HowTo track performance changes based on awr text reports

Using oracle function dbms_workload_repository.AWR_REPORT_TEXT with spool and linux comand grep, you can analyze load statistics or even track performance peeks without continuously access to your production database. I know that now we have more and more applications which can deliver such information(for example AWR Datawarehouse). But if you are command line geek… Check this outJ

All we need is dbid, instance id, begin_snap and end_snap_id to generate following string:

SELECT output FROM TABLE(dbms_workload_repository.AWR_REPORT_TEXT(:dbid,:instid,:begin_snap,:end_snap))

　

I created PLSQL coursor to fetch all snap_id’s from given date and time period. For example: create awr reports for day by day between 19:20 and 22:20 (batch load processing)

Here ist my script:

SET serveroutput ON
DECLARE
start_date VARCHAR2(20) := '2015-05-02';
start_hour VARCHAR2(20) := '19:20';
end_hour VARCHAR2(20) := '22:20';
begin_snap NUMBER(10):=0;
dbid NUMBER(10):=0;
instid NUMBER(10):=0;
CURSOR c_snapid
IS
SELECT MIN(snapa) begin_snap,MIN(snapb) end_snap FROM
(SELECT t1.snap_id snapa, t1.begin_interval_time A FROM dba_hist_snapshot t1
WHERE to_char(to_date(start_date||' '||start_hour,'YYYY-MM-DD HH24:MI'),'HH24') >= to_char(begin_interval_time,'HH24')
AND to_char(to_date(start_date||' '||start_hour,'YYYY-MM-DD HH24:MI'),'HH24') < to_char(end_interval_time,'HH24')),
(SELECT t2.snap_id snapb, t2.begin_interval_time b FROM dba_hist_snapshot t2
WHERE to_char(to_date(start_date||' '||end_hour,'YYYY-MM-DD HH24:MI'),'HH24') >= to_char(begin_interval_time,'HH24')
AND to_char(to_date(start_date||' '||end_hour,'YYYY-MM-DD HH24:MI'),'HH24') < to_char(end_interval_time,'HH24'))
WHERE to_char(A,'YYYY-MM-DD')=to_char(b,'YYYY-MM-DD')
AND A>=to_date(start_date||' '||start_hour,'YYYY-MM-DD HH24:MI')
GROUP BY A,b;
i_snapid c_snapid%rowtype;
BEGIN
SELECT dbid INTO dbid FROM v$database;
SELECT instance_number INTO instid FROM v$instance;
OPEN c_snapid;
loop
fetch c_snapid INTO i_snapid;
exit WHEN c_snapid%notfound;
dbms_output.put_line('spool awr_'||i_snapid.begin_snap||'_'||i_snapid.end_snap||'.txt');
dbms_output.put_line('SELECT output FROM TABLE(dbms_workload_repository.AWR_REPORT_TEXT('||dbid||','||instid||','||i_snapid.begin_snap||','||i_snapid.end_snap||'));');
dbms_output.put_line('spool off');
END loop;
CLOSE c_snapid;
exception
WHEN others THEN
dbms_output.put_line (sqlerrm);
END;
/

Output:

spool awr_62663_62666.txt

SELECT output FROM

TABLE(dbms_workload_repository.AWR_REPORT_TEXT(1416487949,1,62663,62666));

spool off

spool awr_62687_62690.txt

SELECT output FROM

TABLE(dbms_workload_repository.AWR_REPORT_TEXT(1416487949,1,62687,62690));

spool off

spool awr_62831_62834.txt

SELECT output FROM

TABLE(dbms_workload_repository.AWR_REPORT_TEXT(1416487949,1,62831,62834));

spool off

spool awr_62951_62954.txt

SELECT output FROM

TABLE(dbms_workload_repository.AWR_REPORT_TEXT(1416487949,1,62951,62954));

spool off

...

spool awr_63095_63098.txt

SELECT output FROM

TABLE(dbms_workload_repository.AWR_REPORT_TEXT(1416487949,1,63095,63098));

spool off

PL/SQL procedure successfully completed.

　

Now you can use grep to find out for example DB Time

/oracle.sw/oracle: grep -i 'DB Time:' awr_69*
awr_62519_62522.txt: DB Time: 97.82 (mins)

awr_62543_62546.txt: DB Time: 80.17 (mins)

awr_62567_62570.txt: DB Time: 1,810.81 (mins)

awr_62591_62594.txt: DB Time: 2,556.04 (mins)

awr_62615_62618.txt: DB Time: 90.30 (mins)

awr_62639_62642.txt: DB Time: 120.58 (mins)

awr_62663_62666.txt: DB Time: 78.02 (mins)

awr_62687_62690.txt: DB Time: 79.95 (mins)

awr_62711_62714.txt: DB Time: 74.97 (mins)

awr_62735_62738.txt: DB Time: 80.06 (mins)

awr_62759_62762.txt: DB Time: 78.12 (mins)

awr_62783_62786.txt: DB Time: 82.12 (mins)

awr_62807_62810.txt: DB Time: 78.77 (mins)

awr_62831_62834.txt: DB Time: 78.10 (mins)

awr_62855_62858.txt: DB Time: 76.02 (mins)

awr_62879_62882.txt: DB Time: 68.94 (mins)

awr_62903_62906.txt: DB Time: 74.38 (mins)

awr_62927_62930.txt: DB Time: 102.24 (mins)

awr_62951_62954.txt: DB Time: 300.03 (mins)

awr_62975_62978.txt: DB Time: 77.88 (mins)

awr_62999_63002.txt: DB Time: 78.58 (mins)

awr_63023_63026.txt: DB Time: 103.45 (mins)

/oracle.sw/oracle/awrrep: grep -i 'Physical read (blocks):' awr_6*

awr_62519_62522.txt: Physical read (blocks): 775.9 803.8

awr_62543_62546.txt: Physical read (blocks): 969.0 455.3

awr_62567_62570.txt: Physical read (blocks): 21,866.0 3,931.0

awr_62591_62594.txt: Physical read (blocks): 73,822.6 11,196.0

awr_62615_62618.txt: Physical read (blocks): 1,029.1 188.3

awr_62639_62642.txt: Physical read (blocks): 1,113.5 628.1

awr_62663_62666.txt: Physical read (blocks): 899.1 547.0

awr_62687_62690.txt: Physical read (blocks): 970.9 1,000.9

awr_62711_62714.txt: Physical read (blocks): 942.2 478.8

awr_62735_62738.txt: Physical read (blocks): 964.3 520.2

awr_62759_62762.txt: Physical read (blocks): 1,002.4 500.6

awr_62783_62786.txt: Physical read (blocks): 981.3 503.0

awr_62807_62810.txt: Physical read (blocks): 972.2 4,575.2

awr_62831_62834.txt: Physical read (blocks): 904.0 6,316.5

awr_62855_62858.txt: Physical read (blocks): 960.3 4,808.0

awr_62879_62882.txt: Physical read (blocks): 952.6 4,764.9

awr_62903_62906.txt: Physical read (blocks): 953.5 4,307.3

awr_62927_62930.txt: Physical read (blocks): 9,890.9 15,813.9

awr_62951_62954.txt: Physical read (blocks): 3,464.7 1,937.7

awr_62975_62978.txt: Physical read (blocks): 994.9 541.3

awr_62999_63002.txt: Physical read (blocks): 899.7 497.0

awr_63023_63026.txt: Physical read (blocks): 1,205.3 1,175.1