Automated AWR Performance Reporting

** This process requires an Oracle Diagnostic license

Oracle’s AWR repository is extremely useful, providing excellent reports on the performance of your database instances. Separating the performance report data from the source database means you can keep your performance data for as long as you like without impacting space usage on the source database.

That’s a positive, because I’ve often found that I didn’t have the space to store long term (>6 mo) performance data. The following procedure addresses that by automatically creating daily AWR reports to either store on disk for long term analysis, or perhaps send as a daily email report. As you accumulate these reports over time, you can compare them to determine long term changes in performance and usage. You can also use unix commands such as “grep” or “awk” to compare data across your reports. For instance you can pull out the db_time metric to determine your system load over time.

I usually set up the process with a non-system account.

mkdir /home/oracle/awr

— The following direct grants are required.

grant select on dba_hist_snapshot to dvdba;

grant select on gv_$database to dvdba;

grant execute on DBMS_WORKLOAD_REPOSITORY to dvdba;

create directory awr_dir as ‘/home/oracle/awr’;

grant all on directory awr_dir to dvdba;

Here is the procedure:

create or replace procedure dvdba.awrtofile

(days_ago number default 1, starttime number default 7, endtime number default 18 ) is

dbid NUMBER;

inst_id NUMBER;

bid NUMBER;

eid NUMBER;

db_unique_name VARCHAR2(30);

file1 utl_file.file_type;

prior_day number;

BEGIN

if endtime <= starttime then

prior_day:=1;

else

prior_day:=0;

end if;

SELECT MIN (snap_id), MAX (snap_id)

INTO bid, eid

FROM dba_hist_snapshot

WHERE

end_interval_time >= (starttime/24 + trunc(sysdate-days_ago-prior_day))

and end_interval_time <= (endtime/24 + 0.0068 + trunc(sysdate-days_ago))

;

SELECT dbid, inst_id, db_unique_name INTO dbid, inst_id, db_unique_name

FROM gv$database;

file1:= utl_file.fopen(‘AWR_DIR’,db_unique_name||’_awrrpt_’||inst_id||’_’||bid||’_’||eid||’.txt’,’w’);

FOR c1_rec IN

(SELECT output

FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(dbid,inst_id, bid, eid, 8 )))

LOOP

utl_file.put_line(file1,c1_rec.output);

END LOOP;

utl_file.fclose(file1);

EXCEPTION

WHEN UTL_FILE.INVALID_OPERATION THEN

UTL_FILE.FCLOSE(file1);

dbms_output.put_line(‘File could not be opened or operated on as requested.’);

WHEN OTHERS THEN

dbms_output.put_line(‘other trouble’||SQLCODE||SQLERRM);

END;

/

One of the virtues of a script like this is that it gives you fine control over your reporting period. Here is how you would execute the procedure for yesterday, from 7 am until 6 pm:

exec dvdba.awrtofile(1,7,18);

To create reports for the last 30 days (which requires a 30-day retention policy for AWR data), run the following:

declare

x number;

begin

for x in 1..30 loop

dbms_output.put_line(x);

dvdba.awrtofile(x,7,18);

dvdba.awrtofile(x,23,5);

end loop;

end;

/

Here is a schedule entry to create reports daily for both a day window (7am to 6pm) and a night window (11pm to 6am):

BEGIN

— Job defined entirely by the CREATE JOB procedure.

DBMS_SCHEDULER.create_job (

job_name => ‘DV_WRITE_AWR_TO_DISK’,

job_type => ‘PLSQL_BLOCK’,

job_action => ‘dvdba.awrtofile(1,7,18); dvdba.awrtofile(1,23,6);’,

start_date => SYSTIMESTAMP,

repeat_interval => ‘freq=daily; byhour=7;byminute=0’,

end_date => NULL,

enabled => TRUE,

comments => ‘Task to create daily awr report and write them to the AWR_DIR.’);

END;

/

EXEC DBMS_SCHEDULER.DROP_JOB(‘DV_WRITE_AWR_TO_DISK’);

EXEC dbms_scheduler.run_job(‘DV_WRITE_AWR_TO_DISK’);

SELECT * FROM dba_scheduler_job_log WHERE job_name = ‘DV_WRITE_AWR_TO_DISK’;

Useful scripts to address minor “paper cut” irritations are only one of the ways Datavail’s experienced database professionals can make your life easier. Contact us to learn what we can do for you.