Oracle DB. Simple AWR report generation from the database itself.



From the version 10g, Oracle database let you to use AWR for system performance analisis.
I will show you how to generate AWR report fast and simple.

All commands are running under SYS user, but you can use another one with appropriate privileges.

First, let’s compile simple report generation function.

create or replace function AWR_HTML(in_begin_point in number, in_end_point in number) return clob is
  ret_html clob;
  db_id number;
begin
  select dbid into db_id from v$database;
  for i in (
            select output 
            from table(dbms_workload_repository.awr_report_html(db_id,  1, in_begin_point, in_end_point))
           )
  loop
    ret_html:=ret_html||i.output;
  end loop;

  return(ret_html);
end AWR_HTML;

After that, we should decide which snapshots to use for report generation.

select snap_id, end_interval_time from dba_hist_snapshot
order by snap_id desc
1458	20.01.16 11:00:07,855
1457	20.01.16 10:00:05,418
1456	20.01.16 09:00:02,709
...

Oracle creates snapshots periodically, as a rule. Run this, if you want to force snapshot creation.

begin
  dbms_workload_repository.create_snapshot;
end;

Everything is ready. Now you can generate report using appropriate snapshot IDs.

select awr_html(1457, 1458) from dual

In case you are using PL/SQL Developer IDE, generated report will be opened using built-in html-viewer. Very smart 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *