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 handy 🙂