Select Page

Automated AWR Performance Reporting

Zane Warton | | March 15, 2019

** 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.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Tips for Upgrading From SQL 2008 to 2012 or 2014

It’s 2015 and you can now establish totally respectable MS SQL DBA credibility just by mentioning you have been in the game since SQL Server version 9. You may even get the same gasps of shock from some colleagues that used to be reserved for the version 6 veterans.

Andy McDermid | April 8, 2015

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?

JP Chen | October 1, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS