Automatic Workload Repository (AWR)
SQL AWR Reports
SQL AWR reports
SQL> select snap_id, dbid, instance_number, begin_interval_time, end_interval_time from dba_hist_snapshot order by snap_id desc; SNAP_ID DBID INSTANCE_NUMBER BEGIN_INTERVAL_TIME END_INTERVAL_TIME ---------- ---------- --------------- ----------------------------- ---------------------------- 3 3358061257 1 01-JUN-24 04.47.23.000 AM 01-JUN-24 04.58.22.669 AM 2 3358061257 1 01-JUN-24 03.58.37.000 AM 01-JUN-24 04.09.38.007 AM 1 3358061257 1 31-MAY-24 11.15.18.000 AM 31-MAY-24 11.58.51.326 AM SQL>
awrsqrpt_sql.sql
--DBID: 3358061257 --instance ID:1 --begin snapshot ID:2 --end snapshot ID:3 --SQLID: dbdxr98c0pqsq sqlplus system@dbdocs awrsqrpt_sql.sql 3358061257 1 2 3 dbdxr98c0pqsq
-- SQL*Plus script to generate an HTML and text-based AWR SQL report. -- Usage: sqlplus username@database @awrsqrpt_sql.sql dbid inst bid eid sqlid set feedback off set heading off set linesize 10000 set pagesize 0 set termout off set trimspool on set verify off define dbid=&1 define inst=&2 define bid=&3 define eid=&4 define sqlid=&5 column awr_html_file new_value awr_html_file noprint column awr_text_file new_value awr_text_file noprint select 'awr_sql_report_' || &&dbid || '_' || &&inst || '_' || &&bid || '_' || &&eid || '_' || '&&sqlid' || '.html' awr_html_file from dual; select 'awr_sql_report_' || &&dbid || '_' || &&inst || '_' || &&bid || '_' || &&eid || '_' || '&&sqlid' || '.txt' awr_text_file from dual; set termout on prompt Generating HTML AWR SQL report set termout off spool &awr_html_file select output from table( dbms_workload_repository.awr_sql_report_html( l_dbid => &&dbid, l_inst_num => &&inst, l_bid => &&bid, l_eid => &&eid, l_sqlid => '&&sqlid' ) ); spool off set termout on prompt Generating text AWR SQL report set termout off spool &awr_text_file select output from table( dbms_workload_repository.awr_sql_report_text( l_dbid => &&dbid, l_inst_num => &&inst, l_bid => &&bid, l_eid => &&eid, l_sqlid => '&&sqlid' ) ); spool off quit
awr_report_dbid_inst_bid_eid_sqlid.html
awr_report_dbid_inst_bid_eid_sqlid.txt
DBMS_WORKLOAD_REPOSITORY
SQL ID, SQL Text,
Execution Plan
CPU Time, Elapsed Time, Buffer Gets, Disk Reads
Rows Processed
Executions, Parses
Fetches