Performance

Grabbing an AWR without access to the database server

Getting an AWR can sometimes be difficult. If you are on an autonomous database you cannot log in to the database server and run your trusty old script for it. Other times you may not have the access and cannot get the DBA to find time to help you with it every time you want to look at one.

Fortunately you can leverage your PL/SQL skills and just get it through any tool you want to use.

The first thing you need is the snap_ids you want to use. Each time data is saved for an AWR it is given a new sequential higher snap_id. So you’ll want to list the ones you have available to generate an AWR from.

select snap_id
     , begin_interval_time
     , end_interval_time
  from dba_hist_snapshot
 where dbid            = (select dbid            from v$database)
   and instance_number = (select instance_number from v$instance)
 order by snap_id desc;

Of course if it is not a recent snap you are looking for you may want to filter it by begin_interval_time.

Now that you have the two snaps that covers the time you are interested in, you’ll want to generate it so you get the output locally on the environment you work on. Note that you need to enable dbms_output in order to get the output from the script.

undefine enter_start_id
undefine enter_end_id
begin
  for r in (select output
              from table(dbms_workload_repository.awr_report_html
                   ( (select dbid            from v$database)
                   , (select instance_number from v$instance)
                   , &enter_start_id.
                   , &enter_end_id.
                        ))) loop
 
  dbms_output.put_line(r.output);
  end loop;
end;
/

By entering the two snap_ids form the output of the first SQL you will get an AWR in the form of HTML. Just save it to a file and open it in your favourite web-browser to see the AWR.

Disclaimer: The basis for the code used here is taken from Maria Colgan who showed this technique and some other variants. I have modified the code from there to in my mind be easier to use and demonstrate. See her blog to read about other ways to do it, including how to grab the AWR for most recent snaps with SQL Developer.