AWR Report – AWR (Automatic Workload Repository) reports
0. Overview
1. AWR Setup
2. Find AWR snapshot interval and retention settings
3. Display snapshots
4. AWR Report Generation
5. Change AWR snapshot interval
6. Change AWR snapshot retention
7. Take AWR snapshot manually
8. Purge AWR Snapshots
9. How to find the snap_id for sql_text
10. Generating an AWR Report for a Single SQL Statement
11. Useful views
0. Overview
AWR Report – AWR (Automatic Workload Repository) reports are a useful diagnostic tool for the determination of the potential cause of database wide performance issues. Typically when a performance issue is detected you would collect an AWR report covering the period of the poor performance. It is best to use a reporting period no longer than 1 hour as otherwise specifics can be lost. Capture performed by MMON process Captures data from V$ performance views – Cumulative data e.g. V$SYSSTAT – Data based on deltas e.g. V$SQL – Active Session History – Metrics Stores data in AWR tables in SYSAUX tablespace – WRH$_xxx for data – WRM$_xxx for metadata Access data directly using DBA_HIST_xxx views AWR snapshots resides in SYSAUX tablespace. By default snapshot are generated once every 60 min and maintained for 7 days. Each snapshot has a unique ID know as "snap_id". Snapshot detail can be found in "dba_hist_snapshot" view. $ORACLE_HOME/rdbms/admin/awrrpt.sql AWR reports can be generated by running various SQL scripts to satisfy various requirements. Each report is available in HTML or TXT format: awrrpt.sql - Displays various statistics for a range of snapshots Ids awrrpti.sql - Displays statistics for a range of snapshot Ids on a specified database and instance awrsqrpt.sql - Displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a particular SQL statement. awrsqrpi.sql - Displays statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL. awrddrpt.sql - Compares detailed performance attributes and configuration settings between two selected time periods. awrddrpi.sql - Compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance. awrgrpt.sql - AWR GLOBAL Report(RAC) awrgrpti.sql - Workload Repository RAC (Global) Report awrgdrpt.sql - AWR GLOBAL DIFF Report (RAC) awrinfo.sql - Script to Output General AWR Information awrblmig.sql - AWR Baseline Migrate awrload.sql - AWR LOAD: load awr from dump file awrextr.sql - AWR Extract awrddinp.sql - Get inputs for diff report awrgdinp.sql - Get inputs for global diff reports awrgdrpi.sql - Workload Repository Global Compare Periods Report awrginp.sql - AWR Global Input awrinpnm.sql - AWR Input Name awrinput.sql - Get inputs for AWR report
SQL> show parameter statistics_level NAME TYPE VALUE --------------------- ----------- ---------- statistics_level string TYPICAL SQL> To active the AWR change the system parameter statistics_level to one of three values. BASIC - this option disables the AWR TYPICAL (default) - activates standard level of collection ALL - same as typical but includes execution plans and timing info from the O/S If the parameter is set to BASIC, you simply need to modify the parameter in order to start gathering AWR statistics for your database alter system set statistics_level=TYPICAL scope=both; SQL> set pages 999 SQL> set lines 180 SQL> col STATISTICS_NAME for a50 SQL> SELECT statistics_name, activation_level, system_status FROM v$statistics_level; STATISTICS_NAME ACTIVAT SYSTEM_S -------------------------------------------------- ------- -------- Buffer Cache Advice TYPICAL ENABLED MTTR Advice TYPICAL ENABLED Timed Statistics TYPICAL ENABLED <-- Timed OS Statistics ALL DISABLED Segment Level Statistics TYPICAL ENABLED PGA Advice TYPICAL ENABLED Plan Execution Statistics ALL DISABLED Shared Pool Advice TYPICAL ENABLED Modification Monitoring TYPICAL ENABLED Longops Statistics TYPICAL ENABLED Bind Data Capture TYPICAL ENABLED Ultrafast Latch Statistics TYPICAL ENABLED Threshold-based Alerts TYPICAL ENABLED Global Cache Statistics TYPICAL ENABLED Active Session History TYPICAL ENABLED Undo Advisor, Alerts and Fast Ramp up TYPICAL ENABLED Streams Pool Advice TYPICAL ENABLED Time Model Events TYPICAL ENABLED Plan Execution Sampling TYPICAL ENABLED Automated Maintenance Tasks TYPICAL ENABLED SQL Monitoring TYPICAL ENABLED Adaptive Thresholds Enabled TYPICAL ENABLED V$IOSTAT_* statistics TYPICAL ENABLED 23 rows selected. SQL>
2. Find AWR snapshot interval and retention settings
In below example the snapshot interval is one hour and retention is 8 days. The data type for the columns SNAP_INTERVAL, RETENTION is having two fields – days and time with hour, min, sec. SQL> set lines 180 SQL> col SNAP_INTERVAL for a20 SQL> col RETENTION for a20 SQL> select * from dba_hist_wr_control; DBID SNAP_INTERVAL RETENTION TOPNSQL ---------- -------------------- -------------------- ---------- 730180430 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT SQL> -- OR -- SQL> select extract( day from snap_interval) *24*60+ 2 3 extract( hour from snap_interval) *60+ 4 extract( minute from snap_interval ) "Snapshot Interval Minutes", 5 extract( day from retention) *24*60+ 6 extract( hour from retention) *60+ 7 extract( minute from retention ) "Retention Interval Minutes" 8 from 9 dba_hist_wr_control; Snapshot Interval Minutes Retention Interval Minutes ------------------------- -------------------------- 60 11520 <--- 11520/60/24=8 Days SQL>
SQL> set lines 180 SQL> col MIN(BEGIN_INTERVAL_TIME) for a30 SQL> col max(BEGIN_INTERVAL_TIME) for a30 SQL> SELECT MIN(snap_id), min(BEGIN_INTERVAL_TIME),MAX(snap_id), max(BEGIN_INTERVAL_TIME) FROM dba_hist_snapshot; MIN(SNAP_ID) MIN(BEGIN_INTERVAL_TIME) MAX(SNAP_ID) MAX(BEGIN_INTERVAL_TIME) ------------ ------------------------------ ------------ ------------------------------ 94 26-OCT-16 02.42.53.000 PM 98 29-OCT-16 02.51.39.000 PM SQL> SQL> select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1; SQL> select snap_id, BEGIN_INTERVAL_TIME from dba_hist_snapshot; SNAP_ID BEGIN_INTERVAL_TIME ---------- ---------------------------------------- 96 26-OCT-16 03.30.53.159 PM 98 29-OCT-16 02.51.39.000 PM 94 26-OCT-16 02.42.53.000 PM 97 26-OCT-16 04.30.55.068 PM 95 26-OCT-16 02.53.52.122 PM SQL>
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 730180430 CAT 1 cat Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' Enter value for report_type: Type Specified: html Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ -------- ------------ ------------ ------------ * 730180430 1 CAT cat rac2.rajasek har.com Using 730180430 for database Id Using 1 for instance number Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing without specifying a number lists all completed snapshots. Enter value for num_days: 7 <--- Listing the last 7 days of Completed Snapshots Instance DB Name Snap Id Snap Started Level ------------ ------------ --------- ------------------ ----- cat CAT 94 26 Oct 2016 14:53 1 95 26 Oct 2016 15:30 1 96 26 Oct 2016 16:30 1 97 26 Oct 2016 19:16 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 95 <--- Begin Snapshot Id specified: 95 <---- Enter value for end_snap: 96 End Snapshot Id specified: 96 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrrpt_1_95_96.html. To use this name, press to continue, otherwise enter an alternative. Enter value for report_name: test_report.html <--- Specify Report Name .. -- < Output of report is shown across the screen > --- .. Report written to test_report.html SQL> SQL> !ls -ltr test_report.html -rw-r--r-- 1 oracle oinstall 488688 Oct 29 14:55 test_report.html SQL>
5. Change AWR snapshot interval
SQL> BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( interval => 30); <--- Minutes (1/2 Hour) END; / 2 3 4 5 PL/SQL procedure successfully completed. SQL> SQL> set lines 180 SQL> col SNAP_INTERVAL for a20 SQL> col RETENTION for a20 SQL> select * from dba_hist_wr_control; DBID SNAP_INTERVAL RETENTION TOPNSQL ---------- -------------------- -------------------- ---------- 730180430 +00000 00:30:00.0 +00008 00:00:00.0 DEFAULT SQL> The above example the snapshot interval is 1/2 hour and retention is 8 days. The data type for the columns SNAP_INTERVAL, RETENTION is having two fields – days and time with hour, min, sec.
6. Change AWR snapshot retention
SQL> set lines 180 SQL> col SNAP_INTERVAL for a20 SQL> col RETENTION for a20 SQL> select * from dba_hist_wr_control; DBID SNAP_INTERVAL RETENTION TOPNSQL ---------- -------------------- -------------------- ---------- 730180430 +00000 00:30:00.0 +00008 00:00:00.0 DEFAULT SQL> SQL> BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 10080); <-- Minutes (7 Days) END; / 2 3 4 5 BEGIN * ERROR at line 1: ORA-13541: system moving window baseline size (691200) greater than retention (604800) ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 174 ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 222 ORA-06512: at line 2 SQL> --------- 10080 Minutes = This value i given, convert to seconds 10080*60=604800 (604800/60/60/24 = 7 Days) 691200 Seconds = 691200/60/60/24 = 8 days --------- SQL> col BASELINE_NAME for a30 SQL> select dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline; DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE ---------- ------------------------------ ------------- ------------------ 730180430 SYSTEM_MOVING_WINDOW MOVING_WINDOW 8 <--- SQL> SQL> BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE ( window_size => 7); END; / 2 3 4 5 PL/SQL procedure successfully completed. SQL> SQL> select dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline; DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE ---------- ------------------------------ ------------- ------------------ 730180430 SYSTEM_MOVING_WINDOW MOVING_WINDOW 7 <-- SQL> SQL> BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 10080); <---- Minutes (7 Days) END; / 2 3 4 5 PL/SQL procedure successfully completed. SQL> SQL> set lines 180 SQL> col SNAP_INTERVAL for a20 SQL> col RETENTION for a20 SQL> select * from dba_hist_wr_control; DBID SNAP_INTERVAL RETENTION TOPNSQL ---------- -------------------- -------------------- ---------- 730180430 +00000 00:30:00.0 +00007 00:00:00.0 DEFAULT SQL> Now retention changed from 8 to 7 days.
SQL> select MAX(snap_id) FROM dba_hist_snapshot; MAX(SNAP_ID) ------------ 102 <--- SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot; PL/SQL procedure successfully completed. SQL> select MAX(snap_id) FROM dba_hist_snapshot; MAX(SNAP_ID) ------------ 103 <--- SQL>
If you decide you do not need old snaps then you can purge using below example SQL> SELECT MIN(snap_id), min(BEGIN_INTERVAL_TIME),MAX(snap_id), max(BEGIN_INTERVAL_TIME) FROM dba_hist_snapshot; SQL> SELECT MIN(snap_id),MAX(snap_id) FROM dba_hist_snapshot; MIN(SNAP_ID) MAX(SNAP_ID) ------------ ------------ 94 103 SQL> exec dbms_workload_repository.drop_snapshot_range(94,99); -- OR --- SQL> BEGIN DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range ( low_snap_id => 94, high_snap_id => 99); END; / 2 3 4 5 6 PL/SQL procedure successfully completed. SQL> In the below output we don't see snaps b/w 94 to 99 <--- SQL> SELECT MIN(snap_id),MAX(snap_id) FROM dba_hist_snapshot; MIN(SNAP_ID) MAX(SNAP_ID) ------------ ------------ 100 103 SQL>
9. How to find the snap_id for sql_text
SQL> conn scott/tiger; Connected. SQL> set lines 180 SQL> select * from emp where ename='SMITH'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 SQL> SQL> show user USER is "SYS" SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot; PL/SQL procedure successfully completed. SQL> SQL> select MAX(snap_id) FROM dba_hist_snapshot; MAX(SNAP_ID) ------------ 107 <--- SQL> ------------------------------------ col parsed format a6 col sql_text format a40 set lines 200 set pages 300 select sql_text, parsing_schema_name as parsed, elapsed_time_delta/1000/1000 as elapsed_sec, stat.snap_id, to_char(snap.end_interval_time,'dd.mm hh24:mi:ss') as snaptime, txt.sql_id from dba_hist_sqlstat stat, dba_hist_sqltext txt, dba_hist_snapshot snap where stat.sql_id=txt.sql_id and stat.snap_id=snap.snap_id and snap.begin_interval_time>=sysdate-1 and lower(sql_text) like '%&t%' and parsing_schema_name not in ('SYS','SYSMAN','MDSYS','WKSYS') order by elapsed_time_delta asc; ------------------------------------ SQL> col parsed format a6 SQL> col sql_text format a40 SQL> set lines 200 SQL> set pages 300 SQL> SQL> select 2 sql_text, 3 parsing_schema_name as parsed, 4 elapsed_time_delta/1000/1000 as elapsed_sec, 5 stat.snap_id, 6 to_char(snap.end_interval_time,'dd.mm hh24:mi:ss') as snaptime, 7 txt.sql_id 8 from 9 dba_hist_sqlstat stat, 10 dba_hist_sqltext txt, 11 dba_hist_snapshot snap 12 where stat.sql_id=txt.sql_id and stat.snap_id=snap.snap_id and 13 14 15 snap.begin_interval_time>=sysdate-1 and 16 lower(sql_text) like '%&t%' and 17 parsing_schema_name not in ('SYS','SYSMAN','MDSYS','WKSYS') 18 order by elapsed_time_delta asc; Enter value for t: select * from emp where ename old 16: lower(sql_text) like '%&t%' and new 16: lower(sql_text) like '%select * from emp where ename%' and SQL_TEXT PARSED ELAPSED_SEC SNAP_ID SNAPTIME SQL_ID ---------------------------------------- ------ ----------- ---------- -------------- ------------- select * from emp where ename='SMITH' SCOTT .020678 107 29.10 18:17:32 cgf95c3k5mszx SQL>
10. Generating an AWR Report for a Single SQL Statement
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
Output
SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 730180430 CAT 1 cat Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' Enter value for report_type: text Type Specified: text Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ -------- ------------ ------------ ------------ * 730180430 1 CAT cat rac2.rajasek har.com Using 730180430 for database Id Using 1 for instance number Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing without specifying a number lists all completed snapshots. Enter value for num_days: 1 <--- Listing the last day's Completed Snapshots Snap Instance DB Name Snap Id Snap Started Level ------------ ------------ --------- ------------------ ----- cat CAT 100 29 Oct 2016 16:00 1 101 29 Oct 2016 16:30 1 102 29 Oct 2016 16:41 1 103 29 Oct 2016 16:42 1 104 29 Oct 2016 17:00 1 105 29 Oct 2016 17:30 1 106 29 Oct 2016 18:00 1 107 29 Oct 2016 18:17 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 106 <-- Begin Snapshot Id specified: 106 Enter value for end_snap: 107 End Snapshot Id specified: 107 <--- Specify the SQL Id ~~~~~~~~~~~~~~~~~~ Enter value for sql_id: cgf95c3k5mszx <---- SQL ID specified: cgf95c3k5mszx Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrsqlrpt_1_106_107.txt. To use this name, press to continue, otherwise enter an alternative. Enter value for report_name: cgf95c3k5mszx_txt1 <---- Using the report name cgf95c3k5mszx_txt1 WORKLOAD REPOSITORY SQL Report Snapshot Period Summary DB Name DB Id Instance Inst Num Startup Time Release RAC ------------ ----------- ------------ -------- --------------- ----------- --- CAT 730180430 cat 1 29-Oct-16 14:51 11.2.0.3.0 NO Snap Id Snap Time Sessions Curs/Sess --------- ------------------- -------- --------- Begin Snap: 106 29-Oct-16 18:00:44 28 1.1 End Snap: 107 29-Oct-16 18:17:32 30 1.1 Elapsed: 16.80 (mins) DB Time: 0.01 (mins) SQL Summary DB/Inst: CAT/cat Snaps: 106-107 Elapsed SQL Id Time (ms) ------------- ---------- cgf95c3k5mszx 21 Module: SQL*Plus select * from emp where ename='SMITH' ------------------------------------------------------------- SQL ID: cgf95c3k5mszx DB/Inst: CAT/cat Snaps: 106-107 -> 1st Capture and Last Capture Snap IDs refer to Snapshot IDs witin the snapshot range -> select * from emp where ename='SMITH' Plan Hash Total Elapsed 1st Capture Last Capture # Value Time(ms) Executions Snap ID Snap ID --- ---------------- ---------------- ------------- ------------- -------------- 1 3956160932 21 1 107 107 ------------------------------------------------------------- Plan 1(PHV: 3956160932) ----------------------- Plan Statistics DB/Inst: CAT/cat Snaps: 106-107 -> % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 Stat Name Statement Per Execution % Snap --------------------------- ---------- -------------- ------- Elapsed Time (ms) 21 20.7 2.5 CPU Time (ms) 2 2.0 0.2 Executions 1 N/A N/A Buffer Gets 85 85.0 0.5 Disk Reads 8 8.0 14.5 Parse Calls 1 1.0 0.1 Rows 1 1.0 N/A User I/O Wait Time (ms) 18 N/A N/A Cluster Wait Time (ms) 0 N/A N/A Application Wait Time (ms) 0 N/A N/A Concurrency Wait Time (ms) 0 N/A N/A Invalidations 0 N/A N/A Version Count 1 N/A N/A Sharable Mem(KB) 19 N/A N/A ------------------------------------------------------------- Execution Plan -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Full SQL Text SQL ID SQL Text ------------ ----------------------------------------------------------------- cgf95c3k5msz select * from emp where ename='SMITH' Report written to cgf95c3k5mszx_txt1 SQL>
dba_hist_active_sess_history -- ASH info (see below) dba_hist_baseline -- baseline info dba_hist_database_instance -- environment data dba_hist_sql_plan -- sql execution path data dba_hist_wr_control -- AWR settings dba_hist_snapshot -- snapshot info in the AWR -- how to find the sqls between two snaps manually -- select t.sql_id, t.sql_text, s.executions_total, s.elapsed_time_total from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t where s.snap_id between 100 and 107;
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Reference:
http://www.datadisk.co.uk/html_docs/oracle/awr.htm
http://www.dbas-oracle.com/2013/05/10-steps-to-analyze-awr-report-in-oracle.html
Thank you,
Rajasekhar Amudala