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. Typically when a performance issue is detected you would collect an AWR report covering the period of the poor performance. It is best yo use a reporting period no longer than 1 hour as otherwise specifices 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
1.AWR Setup
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 1 21:13:04 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
SQL> col name format a30
SQL> col value format a30
SQL> col type format a10
SQL> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
client_statistics_level string TYPICAL
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>
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 ACTIVATION_LEVEL SYSTEM_STATUS
-------------------------------------------------- --------------------- ------------------------
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
Automatic DBOP Monitoring TYPICAL ENABLED
SQL Monitoring TYPICAL ENABLED
Adaptive Thresholds Enabled TYPICAL ENABLED
V$IOSTAT_* statistics TYPICAL ENABLED
OLAP row load time precision TYPICAL ENABLED
Column Tracking Level TYPICAL ENABLED
26 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>
SQL> set lines 180
SQL> col snap_interval for a20
SQL> col retention for a20
SQL> col src_dbname for a20
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID SRC_DBID SRC_DBNAME
---------- -------------------- -------------------- ------------------------------ ---------- ---------- --------------------
1093429351 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT 0 1093429351 CDB$ROOT
SQL>
--- or ---
SQL> select
2 extract( day from snap_interval) *24*60+
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 dba_hist_wr_control;
Snapshot Interval Minutes Retention Interval Minutes
------------------------- --------------------------
60 11520
SQL>
SQL> select 11520/60/24 "DAY" from dual;
DAY
----------
8
SQL>
3. Display snapshots
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)
------------ ------------------------------ ------------ ------------------------------
140 22-JAN-23 07.32.01.446 PM 173 01-FEB-23 08.05.40.527 PM
SQL> select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1;
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
140 22-JAN-23 07.32.01.446 PM 23-JAN-23 08.47.51.828 PM
141 23-JAN-23 08.47.51.828 PM 24-JAN-23 06.38.56.593 AM
142 24-JAN-23 06.38.56.593 AM 29-JAN-23 12.42.11.717 PM
143 29-JAN-23 12.42.11.717 PM 29-JAN-23 02.00.38.382 PM
144 29-JAN-23 02.00.38.382 PM 29-JAN-23 03.00.52.527 PM
145 29-JAN-23 03.00.52.527 PM 29-JAN-23 04.48.55.211 PM
146 29-JAN-23 05.00.01.000 PM 29-JAN-23 05.10.57.398 PM
147 29-JAN-23 05.10.57.398 PM 29-JAN-23 06.00.08.441 PM
148 29-JAN-23 06.00.08.441 PM 29-JAN-23 07.00.22.093 PM
149 29-JAN-23 07.00.22.093 PM 29-JAN-23 08.00.35.705 PM
150 29-JAN-23 08.00.35.705 PM 29-JAN-23 09.00.49.466 PM
151 29-JAN-23 09.00.49.466 PM 30-JAN-23 06.18.59.094 PM
152 30-JAN-23 06.18.59.094 PM 30-JAN-23 07.00.10.470 PM
153 30-JAN-23 07.00.10.470 PM 30-JAN-23 08.00.24.228 PM
154 30-JAN-23 08.00.24.228 PM 31-JAN-23 11.31.32.784 AM
155 31-JAN-23 11.31.32.784 AM 31-JAN-23 01.00.53.960 PM
156 31-JAN-23 01.00.53.960 PM 31-JAN-23 02.00.06.731 PM
157 31-JAN-23 02.00.06.731 PM 31-JAN-23 03.00.20.999 PM
158 31-JAN-23 03.00.20.999 PM 31-JAN-23 04.00.34.970 PM
159 31-JAN-23 04.00.34.970 PM 31-JAN-23 07.58.05.062 PM
160 31-JAN-23 07.58.05.062 PM 31-JAN-23 09.00.21.180 PM
161 31-JAN-23 09.00.21.180 PM 31-JAN-23 10.00.35.804 PM
162 31-JAN-23 10.00.35.804 PM 31-JAN-23 11.00.51.766 PM
163 31-JAN-23 11.00.51.766 PM 01-FEB-23 12.00.06.841 AM
164 01-FEB-23 12.00.06.841 AM 01-FEB-23 09.40.29.205 AM
165 01-FEB-23 09.40.29.205 AM 01-FEB-23 11.00.47.699 AM
166 01-FEB-23 11.00.47.699 AM 01-FEB-23 12.00.03.871 PM
167 01-FEB-23 12.59.12.000 PM 01-FEB-23 01.10.11.023 PM
168 01-FEB-23 01.10.11.023 PM 01-FEB-23 02.00.23.419 PM
169 01-FEB-23 02.13.21.000 PM 01-FEB-23 03.00.32.082 PM
170 01-FEB-23 03.27.55.000 PM 01-FEB-23 04.00.15.128 PM
171 01-FEB-23 04.00.15.128 PM 01-FEB-23 05.00.29.715 PM
172 01-FEB-23 05.00.29.715 PM 01-FEB-23 08.05.40.527 PM
173 01-FEB-23 08.05.40.527 PM 01-FEB-23 09.00.53.769 PM
34 rows selected.
SQL> select snap_id, BEGIN_INTERVAL_TIME from dba_hist_snapshot;
SNAP_ID BEGIN_INTERVAL_TIME
---------- ---------------------------------------------------------------------------
151 29-JAN-23 09.00.49.466 PM
152 30-JAN-23 06.18.59.094 PM
153 30-JAN-23 07.00.10.470 PM
154 30-JAN-23 08.00.24.228 PM
168 01-FEB-23 01.10.11.023 PM
172 01-FEB-23 05.00.29.715 PM
144 29-JAN-23 02.00.38.382 PM
142 24-JAN-23 06.38.56.593 AM
145 29-JAN-23 03.00.52.527 PM
146 29-JAN-23 05.00.01.000 PM
159 31-JAN-23 04.00.34.970 PM
163 31-JAN-23 11.00.51.766 PM
173 01-FEB-23 08.05.40.527 PM
147 29-JAN-23 05.10.57.398 PM
141 23-JAN-23 08.47.51.828 PM
143 29-JAN-23 12.42.11.717 PM
148 29-JAN-23 06.00.08.441 PM
150 29-JAN-23 08.00.35.705 PM
155 31-JAN-23 11.31.32.784 AM
158 31-JAN-23 03.00.20.999 PM
161 31-JAN-23 09.00.21.180 PM
164 01-FEB-23 12.00.06.841 AM
165 01-FEB-23 09.40.29.205 AM
166 01-FEB-23 11.00.47.699 AM
169 01-FEB-23 02.13.21.000 PM
170 01-FEB-23 03.27.55.000 PM
171 01-FEB-23 04.00.15.128 PM
162 31-JAN-23 10.00.35.804 PM
160 31-JAN-23 07.58.05.062 PM
140 22-JAN-23 07.32.01.446 PM
149 29-JAN-23 07.00.22.093 PM
156 31-JAN-23 01.00.53.960 PM
157 31-JAN-23 02.00.06.731 PM
167 01-FEB-23 12.59.12.000 PM
34 rows selected.
SQL> 4.AWR Report Generation
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 1 21:35:54 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats. Please enter the
name of the format at the prompt. Default value is 'html'.
'html' HTML format (default)
'text' Text format
'active-html' Includes Performance Hub active report
Enter value for report_type: html
old 1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual
new 1: select 'Type Specified: ',lower(nvl('html','html')) report_type from dual
Type Specified: html
old 1: select '&&report_type' report_type_def from dual
new 1: select 'html' report_type_def from dual
old 1: select '&&view_loc' view_loc_def from dual
new 1: select 'AWR_PDB' view_loc_def from dual
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance Container Name
-------------- -------------- -------------- -------------- --------------
1093429351 CDB1 1 cdb1 CDB$ROOT
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
* 1093429351 1 CDB1 cdb1 oracle-db-19
Using 1093429351 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 <return> 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 Snap Level
------------ ------------ ---------- ------------------ ----------
cdb1 CDB1 142 29 Jan 2023 12:42 1
143 29 Jan 2023 14:00 1
144 29 Jan 2023 15:00 1
145 29 Jan 2023 16:48 1
146 29 Jan 2023 17:10 1
147 29 Jan 2023 18:00 1
148 29 Jan 2023 19:00 1
149 29 Jan 2023 20:00 1
150 29 Jan 2023 21:00 1
151 30 Jan 2023 18:18 1
152 30 Jan 2023 19:00 1
153 30 Jan 2023 20:00 1
154 31 Jan 2023 11:31 1
155 31 Jan 2023 13:00 1
156 31 Jan 2023 14:00 1
157 31 Jan 2023 15:00 1
158 31 Jan 2023 16:00 1
159 31 Jan 2023 19:58 1
160 31 Jan 2023 21:00 1
161 31 Jan 2023 22:00 1
162 31 Jan 2023 23:00 1
163 01 Feb 2023 00:00 1
164 01 Feb 2023 09:40 1
165 01 Feb 2023 11:00 1
166 01 Feb 2023 12:00 1
167 01 Feb 2023 13:10 1
168 01 Feb 2023 14:00 1
169 01 Feb 2023 15:00 1
170 01 Feb 2023 16:00 1
171 01 Feb 2023 17:00 1
172 01 Feb 2023 20:05 1
173 01 Feb 2023 21:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 150
Begin Snapshot Id specified: 150
Enter value for end_snap: 151
End Snapshot Id specified: 151
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_150_151.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: test_maxwell_report.html
..
-- < Output of report is shown across the screen > ---
..
Report written to test_maxwell_report.html
SQL> !ls -ltr test_maxwell_report.html
-rw-r--r--. 1 oracle oinstall 781446 Feb 1 21:37 test_maxwell_report.html
SQL>
5. Change AWR snapshot interval
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 30); /*Minutes (1/2 Hour)*/
END;
/
SQL> BEGIN
2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 30);
3 END;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> set lines 180
SQL> col snap_interval for a20
SQL> col retention for a20
SQL> desc dba_hist_wr_control
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
DBID NOT NULL NUMBER
SNAP_INTERVAL NOT NULL INTERVAL DAY(5) TO SECOND(1)
RETENTION NOT NULL INTERVAL DAY(5) TO SECOND(1)
TOPNSQL VARCHAR2(10)
CON_ID NUMBER
SRC_DBID NUMBER
SRC_DBNAME VARCHAR2(128)
SQL> col src_dbname for a30
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID SRC_DBID SRC_DBNAME
---------- -------------------- -------------------- ------------------------------ ---------- ---------- ------------------------------
1093429351 +00000 00:30:00.0 +00008 00:00:00.0 DEFAULT 0 1093429351 CDB$ROOT
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>
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 CON_ID SRC_DBID SRC_DBNAME
---------- -------------------- -------------------- ------------------------------ ---------- ---------- ------------------------------
1093429351 +00000 00:30:00.0 +00008 00:00:00.0 DEFAULT 0 1093429351 CDB$ROOT
SQL>
SQL> BEGIN
2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 10080); /*Minutes (7 Days)*/
3 END;
4 /
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 213
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 265
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>
SQL> col BASELINE_NAME for a30
SQL>
SQL> select dbid,baseline_name,baseline_type,moving_window_size from dba_hist_baseline;
DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ------------------------------ --------------------------------------- ------------------
1093429351 SYSTEM_MOVING_WINDOW MOVING_WINDOW 8
SQL>
SQL> BEGIN
2 DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (window_size => 7);
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> select dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;
DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ------------------------------ --------------------------------------- ------------------
1093429351 SYSTEM_MOVING_WINDOW MOVING_WINDOW 7
SQL>
SQL> BEGIN
2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 10080); /*Minutes (7 Days)*/
3 END;
4 /
PL/SQL procedure successfully completed.
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 CON_ID SRC_DBID SRC_DBNAME
---------- -------------------- -------------------- ------------------------------ ---------- ---------- ------------------------------
1093429351 +00000 00:30:00.0 +00007 00:00:00.0 DEFAULT 0 1093429351 CDB$ROOT
SQL>
Now retention changed from 8 to 7 days.
7. Take AWR snapshot manually
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 1 23:09:06 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
SQL> select MAX(snap_id) from dba_hist_snapshot;
MAX(SNAP_ID)
------------
177
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
PL/SQL procedure successfully completed.
SQL> select MAX(snap_id) from dba_hist_snapshot;
MAX(SNAP_ID)
------------
178
SQL> 8. Purge AWR Snapshots
If you decide you do not need old snaps then you can purge using below example
SQL> set pagesize 200
SQL> set linesize 200
SQL>
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)
------------ --------------------------------------------------------------------------- ------------ ---------------------------------------------------------------------------
142 24-JAN-23 06.38.56.593 AM 178 01-FEB-23 11.08.08.026 PM
SQL> SELECT MIN(snap_id),MAX(snap_id) FROM dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
142 178
SQL> exec dbms_workload_repository.drop_snapshot_range(142,156);
PL/SQL procedure successfully completed.
-----
or
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id => 142,
high_snap_id => 156);
END;
/
-----
SQL> SELECT MIN(snap_id),MAX(snap_id) FROM dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
157 178
SQL>9. How to find the snap_id for sql_text
[oracle@oracle-db-19c ~]$
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 1 23:18:01 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show user;
USER is "SYS"
SQL> set pagesize 200
SQL> set linesize 200
SQL>
SQL> conn scott/tiger@PDB1;
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>
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 1 23:20:24 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
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)
------------
179
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
13 stat.sql_id=txt.sql_id and
14 stat.snap_id=snap.snap_id and
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 .077719 179 01.02 23:20:37 cgf95c3k5mszx
SQL>
10. Generating an AWR Report for a Single SQL Statement
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 1 23:22:48 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show user;
USER is "SYS"
SQL> set pagesize 200
SQL> set linesize 200
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
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
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1093429351 CDB1 1 cdb1
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
* 1093429351 1 CDB1 cdb1 oracle-db-19
Using 1093429351 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 <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
cdb1 CDB1 163 01 Feb 2023 00:00 1
164 01 Feb 2023 09:40 1
165 01 Feb 2023 11:00 1
166 01 Feb 2023 12:00 1
167 01 Feb 2023 13:10 1
168 01 Feb 2023 14:00 1
169 01 Feb 2023 15:00 1
170 01 Feb 2023 16:00 1
171 01 Feb 2023 17:00 1
172 01 Feb 2023 20:05 1
173 01 Feb 2023 21:00 1
174 01 Feb 2023 21:42 1
175 01 Feb 2023 22:00 1
176 01 Feb 2023 22:30 1
177 01 Feb 2023 23:08 1
178 01 Feb 2023 23:10 1
179 01 Feb 2023 23:20 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 178
Begin Snapshot Id specified: 178
Enter value for end_snap: 179
End Snapshot Id specified: 179
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: cgf95c3k5mszx
SQL ID specified: cgf95c3k5mszx
Listing all available Container DB Ids for SQL Id cgf95c3k5mszx
Container DB Id Container Name
----------------- --------------
* 2714456025 PDB1
Using Container DB Id 2714456025
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_178_179.txt. To use this name,
press <return> 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 Unique Name DB Role Edition Release RAC CDB
------------ ----------- ----------- ---------------- ------- ---------- --- ---
CDB1 1093429351 cdb1 PRIMARY EE 19.0.0.0.0 NO YES
Instance Inst Num Startup Time
------------ -------- ---------------
cdb1 1 01-Feb-23 15:27
Snap Id Snap Time Sessions Curs/Sess PDBs
--------- ------------------- -------- --------- -----
Begin Snap: 178 01-Feb-23 23:10:15 59 1.6 4
End Snap: 179 01-Feb-23 23:20:37 58 1.7 4
Elapsed: 10.37 (mins)
DB Time: 0.08 (mins)
SQL Summary DB/Inst: CDB1/cdb1 Snaps: 178-179
Elapsed
SQL Id Time (ms)
------------- ----------
Container DB
------------
cgf95c3k5mszx 78
Module: SQL*Plus
select * from emp where ename='SMITH'
2714456025
-------------------------------------------------------------
SQL ID: cgf95c3k5mszx (Container DB Id: 2714456025) DB/Inst: CDB1/cdb1 Snaps: 178-179
-> 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 78 2 179 179
-------------------------------------------------------------
Plan 1(PHV: 3956160932)
-----------------------
Plan Statistics DB/Inst: CDB1/cdb1 Snaps: 178-179
-> % Snap Total shows the % of the statistic for the SQL statement compared to the instance total
Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 78 38.9 1.6
CPU Time (ms) 75 37.7 2.7
Executions 2 1.0 0.0
Buffer Gets 2,125 1,062.5 0.3
Disk Reads 123 61.5 3.3
Parse Calls 2 1.0 0.0
Rows 2 1.0 N/A
User I/O Wait Time (ms) 4 2.1 2.1
Cluster Wait Time (ms) 0 0.0 0.0
Application Wait Time (ms) 0 0.0 0.0
Concurrency Wait Time (ms) 0 0.0 0.0
Invalidations 0 N/A N/A
Version Count 1 N/A N/A
Sharable Mem(KB) 27 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
------------- -----------------------------------------------------------------
cgf95c3k5mszx select * from emp where ename='SMITH'
Report written to cgf95c3k5mszx_txt1.txt
SQL> 11. Useful views
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 163 and 179;
扫一扫
442
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
