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 🙂