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 … Continue reading AWR →
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118
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>123456789101112131415161718192021222324252627282930313233343536373839
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>123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104
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>12345678910111213141516171819202122232425262728293031
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.123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293
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.1234567891011121314151617181920212223242526272829303132333435363738394041424344454647
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>12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788
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>
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175
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;Please to add comments
No comments yet. Be the first to comment!