- Introduction: AWR (Automatic Workload Repository) is an Oracle performance monitoring feature that collects, processes, and stores database performance statistics. These statistics are used for problem diagnosis and self-tuning of the database. The collected data can be viewed through database views or generated as HTML or text reports, helping DBAs analyze the current performance state of the database in detail.
- Background Processes for AWR:
- MMON: Collects AWR statistics.
- MMNL: Writes ASH and AWR data to disk.
- Data Collected by AWR: AWR captures a wide range of performance related metrics, including:
- Object Statistics: Access and usage statistics of database segments.
- Time Model Statistics: Data from V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views.
- System and Session Statistics: Selected statistics from V$SYSSTAT and V$SESSTAT views.
- Active Session History (ASH) statistics.
- High-load SQL statements contributing significantly to database workload.
- Prerequisites:
- Database Edition should be Enterprise.
- Must have Oracle Diagnostics Pack license.
- CONTROL_MANAGEMENT_PACK_ACCESS parameter should be DIAGNOSTIC+TUNING.
- Reports provided by AWR:
SQL Script Description awrrpt.sql Basic AWR Report awrsqrpt.sql Standard SQL Statement Report awrddrpt.sql Period Difference Report on Current Instance awrrpti.sql Workload Repository Report by Instance (RAC) awrgrpt.sql AWR Global Report (RAC) awrgdrpt.sql AWR Global Difference Report (RAC) awrinfo.sql Script to Output General AWR Information - Summary of activities: We will do below activities related to AWR.
- Report Generation.
- Modify AWR Retention & Interval.
- Generate Manul AWR snap.
- Modify tablespace for AWR.
- Purge old snaps.
- Now let's proceed to do the activities with AWR step by step:
Report Generation:
1. Below types of reports can be generated.
Location of AWR scripts.
[oracle@orcl ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@orcl admin]$
[oracle@orcl admin]$ ls -lrth awr*
-rw-r--r--. 1 oracle oinstall 744 Jan 25 2017 awrrptinoop.sql
-rw-r--r--. 1 oracle oinstall 1.7K Jan 25 2017 awrrptidc.sql
-rw-r--r--. 1 oracle oinstall 7.5K May 29 2017 awrupd12.sql
-rw-r--r--. 1 oracle oinstall 3.1K May 29 2017 awrsqrpt.sql
-rw-r--r--. 1 oracle oinstall 10K May 29 2017 awrrpti.sql
-rw-r--r--. 1 oracle oinstall 15K May 29 2017 awrinput.sql
-rw-r--r--. 1 oracle oinstall 1.5K May 29 2017 awrgrpt.sql
-rw-r--r--. 1 oracle oinstall 8.5K May 29 2017 awrgrpti.sql
-rw-r--r--. 1 oracle oinstall 13K May 29 2017 awrginp.sql
-rw-r--r--. 1 oracle oinstall 3.6K May 29 2017 awrgdrpt.sql
-rw-r--r--. 1 oracle oinstall 9.2K May 29 2017 awrgdrpi.sql
-rw-r--r--. 1 oracle oinstall 3.8K May 29 2017 awrddrpt.sql
-rw-r--r--. 1 oracle oinstall 1.5K May 29 2017 awrblmig.sql
-rw-r--r--. 1 oracle oinstall 2.9K Jul 29 2017 awrinpnm.sql
-rw-r--r--. 1 oracle oinstall 26K Jul 29 2017 awrgdinp.sql
-rw-r--r--. 1 oracle oinstall 2.5K Jan 4 2018 awrmacro.sql
-rw-r--r--. 1 oracle oinstall 8.2K Aug 9 2018 awrrpt.sql
-rw-r--r--. 1 oracle oinstall 12K Aug 16 2018 awrsqrpi.sql
-rw-r--r--. 1 oracle oinstall 10K Aug 21 2018 awrddrpi.sql
-rw-r--r--. 1 oracle oinstall 14K Jul 14 2024 awrextr.sql
-rw-r--r--. 1 oracle oinstall 6.6K Jul 14 2024 awrload.sql
-rw-r--r--. 1 oracle oinstall 11K Jul 14 2024 awrload_18.sql
-rw-r--r--. 1 oracle oinstall 12K Jul 14 2024 awrextr_pre20.sql
-rw-r--r--. 1 oracle oinstall 31K Jul 14 2024 awrddinp.sql
-rw-r--r--. 1 oracle oinstall 56K Apr 15 2025 awrinfo.sql
[oracle@orcl admin]$
Basic AWR Report.
SQL> @?/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:
old 1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual
new 1: select 'Type Specified: ',lower(nvl('','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
-------------- -------------- -------------- -------------- --------------
1718803653 ORCL 1 orcldc CDB$ROOT
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
* 1718803653 1 ORCL orcldc orcl.oraeasy
Using 1718803653 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: 9
Listing the last 9 days of Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
orcldc ORCL 179 07 Dec 2025 11:44 1
180 07 Dec 2025 12:30 1
181 07 Dec 2025 13:30 1
182 07 Dec 2025 14:30 1
183 07 Dec 2025 15:30 1
184 07 Dec 2025 16:30 1
185 07 Dec 2025 17:30 1
186 09 Dec 2025 20:09 1
187 09 Dec 2025 21:30 1
188 13 Dec 2025 18:23 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 181
Begin Snapshot Id specified: 181
Enter value for end_snap: 182
End Snapshot Id specified: 182
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_181_182.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awrrpt_test
Using the report name awrrpt_test
......
......
End of Report
</body></html>
Report written to awrrpt_test.html
SQL>
AWR Comparasion report.
SQL> @?/rdbms/admin/awrddrpt.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:
Type Specified: html
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Id DB Name Inst Num Inst Num Instance
----------- ----------- ------------ -------- -------- ------------
1718803653 1718803653 ORCL 1 1 orcldc
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
* 1718803653 1 ORCL orcldc orcl.oraeasy
Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 1718803653 for Database Id for the first pair of snapshots
Using 1 for Instance Number for the first pair of snapshots
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
------------ ------------ ---------- ------------------ ----------
orcldc ORCL 179 07 Dec 2025 11:44 1
180 07 Dec 2025 12:30 1
181 07 Dec 2025 13:30 1
182 07 Dec 2025 14:30 1
183 07 Dec 2025 15:30 1
184 07 Dec 2025 16:30 1
185 07 Dec 2025 17:30 1
186 09 Dec 2025 20:09 1
187 09 Dec 2025 21:30 1
188 13 Dec 2025 18:23 1
Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 180
First Begin Snapshot Id specified: 180
Enter value for end_snap: 181
First End Snapshot Id specified: 181
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
* 1718803653 1 ORCL orcldc orcl.oraeasy
Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 1718803653 for Database Id for the second pair of snapshots
Using 1 for Instance Number for the second pair of snapshots
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_days2: 6
Listing the last 6 days of Completed Snapshots
186 09 Dec 2025 20:09 1
187 09 Dec 2025 21:30 1
188 13 Dec 2025 18:23 1
Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 186
Second Begin Snapshot Id specified: 186
Enter value for end_snap2: 187
Second End Snapshot Id specified: 187
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_180_1_186.html To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrdiff_1_180_1_186.html
......
......
</body></html>
Report written to awrdiff_1_180_1_186.html
SQL>
AWR for specfic SQL ID.
SQL> @?/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:
Type Specified: html
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1718803653 ORCL 1 orcldc
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
* 1718803653 1 ORCL orcldc orcl.oraeasy
Using 1718803653 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
------------ ------------ ---------- ------------------ ----------
orcldc ORCL 179 07 Dec 2025 11:44 1
180 07 Dec 2025 12:30 1
181 07 Dec 2025 13:30 1
182 07 Dec 2025 14:30 1
183 07 Dec 2025 15:30 1
184 07 Dec 2025 16:30 1
185 07 Dec 2025 17:30 1
186 09 Dec 2025 20:09 1
187 09 Dec 2025 21:30 1
188 13 Dec 2025 18:23 1
189 13 Dec 2025 19:30 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 182
Begin Snapshot Id specified: 182
Enter value for end_snap: 183
End Snapshot Id specified: 183
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: 22356bkgsdcnh
SQL ID specified: 22356bkgsdcnh
......
......
</body></html>
Report written to sqlawrreport.html
SQL>
Use below script for AWR info.
SQL> @?/rdbms/admin/awrinfo.sql
This script will report general AWR information
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrinfo.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrinfo.txt
No errors.
No errors.
~~~~~~~~~~~~~~~
AWR INFO Report
~~~~~~~~~~~~~~~
......
......
Report generation time: +00 00:00:18.8
End of Report
Report written to awrinfo.txt
SQL>
Report excerpt.
*************************************
(2) Size estimates for AWR snapshots
*************************************
|
| Estimates based on 15 mins snapshot INTERVAL:
| AWR size/day 736.2 MB (7,852 K/snap * 96 snaps/day)
| AWR size/wk 5,153.1 MB (size_per_day * 7) per instance
|
| Estimates based on 13 snaps in past 24 hours:
| AWR size/day 697.8 MB (7,852 K/snap and 13 snaps in past 3.4 hours)
| AWR size/wk 4,884.7 MB (size_per_day * 7) per instance
|
**********************************
(3) Space usage by AWR components (per database)
**********************************
COMPONENT MB % AWR KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX%
--------- --------- ------ ------------ ---------- ----------- ----------------
FIXED 52.3 52.5 4,121 366.2 2,563.3 44% : 56%
EVENTS 9.5 9.5 748 66.5 465.5 45% : 55%
SQLPLAN 7.0 7.0 551 49.0 343.0 71% : 29%
SPACE 4.8 4.8 374 33.3 232.8 47% : 53%
SQL 3.2 3.2 251 22.3 156.2 51% : 49%
ASH 0.9 0.9 74 6.6 45.9 60% : 40%
SQLTEXT 0.9 0.9 69 6.1 42.9 86% : 14%
SQLBIND 0.9 0.9 69 6.1 42.9 50% : 50%
RAC 0.6 0.6 44 3.9 27.6 44% : 56%
**********************************
Modify AWR Retention & Interval:
2. We can modify AWR retention & interval like below.
Check the current interval & retention.
SQL> select con_id, dbid, snap_interval, retention
from cdb_hist_wr_control
order by con_id;
CON_ID DBID SNAP_INTERVAL RETENTION
---------- ---------- -------------------- --------------------
0 1718803653 +00000 01:00:00.0 +00008 00:00:00.0
3 1593504508 +00000 01:00:00.0 +00008 00:00:00.0
Now change the interval to 15 mins and retention for 15 days (21600 mins).
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL>
SQL> begin
dbms_workload_repository.modify_snapshot_settings(
interval => 15,
retention => 21600
);
end;
/
PL/SQL procedure successfully completed.
SQL> select con_id, dbid, snap_interval, retention
from cdb_hist_wr_control
order by con_id;
CON_ID DBID SNAP_INTERVAL RETENTION
---------- ---------- -------------------- --------------------
0 1718803653 +00000 00:15:00.0 +00015 00:00:00.0
3 1593504508 +00000 01:00:00.0 +00008 00:00:00.0
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> begin
dbms_workload_repository.modify_snapshot_settings(
interval => 15,
retention => 21600
);
end;
/
PL/SQL procedure successfully completed.
SQL> select con_id, dbid, snap_interval, retention
from cdb_hist_wr_control
order by con_id;
CON_ID DBID SNAP_INTERVAL RETENTION
---------- ---------- -------------------- --------------------
3 1593504508 +00000 00:15:00.0 +00015 00:00:00.0
Now verify the interval & retention.
SQL> conn /as sysdba
Connected.
SQL> select con_id, dbid, snap_interval, retention
from cdb_hist_wr_control
order by con_id;
CON_ID DBID SNAP_INTERVAL RETENTION
---------- ---------- -------------------- --------------------
0 1718803653 +00000 00:15:00.0 +00015 00:00:00.0
3 1593504508 +00000 00:15:00.0 +00015 00:00:00.0
For AWR interval minimum value is 10 mins. Also for retention, it is related to the basline called MOVING_WINDOW value so retention will be always greater than the MOVING_WINDOW value. So let's do practice for that also.
3. AWR Basline setup:
Error while setting interval less than 10 mins.
SQL> begin
dbms_workload_repository.modify_snapshot_settings(
interval => 5,
retention => 21600
);
end;
/
begin
*
ERROR at line 1:
ORA-13511: invalid INTERVAL 5, must be in the range (10, 52560000)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 213
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 265
ORA-06512: at line 2
Check the current MOVING_WINDOW value & try to set retention below that.
SQL> col BASELINE_NAME for a30
SQL> select baseline_name,
baseline_type,
moving_window_size
from dba_hist_baseline
where baseline_type = 'MOVING_WINDOW';
BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
------------------------------ ------------- ------------------
SYSTEM_MOVING_WINDOW MOVING_WINDOW 8
SQL> begin
dbms_workload_repository.modify_snapshot_settings(
interval => 10,
retention => 5760 -- 4 days
);
end;
/
begin
*
ERROR at line 1:
ORA-13541: system moving window baseline size (691200) greater than retention (345600)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 213
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 265
ORA-06512: at line 2
Change the MOVING_WINDOW value to 3 & then try to set retention.
SQL> begin
dbms_workload_repository.modify_baseline_window_size(
window_size => 3
);
end;
/
PL/SQL procedure successfully completed.
SQL> select baseline_name,
baseline_type,
moving_window_size
from dba_hist_baseline
where baseline_type = 'MOVING_WINDOW';
BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
------------------------------ ------------- ------------------
SYSTEM_MOVING_WINDOW MOVING_WINDOW 3
SQL>
SQL> begin
dbms_workload_repository.modify_snapshot_settings(
interval => 10,
retention => 5760 -- 4 days
);
end;
/
PL/SQL procedure successfully completed.
SQL>
SQL> select con_id, dbid, snap_interval, retention
from cdb_hist_wr_control
order by con_id;
CON_ID DBID SNAP_INTERVAL RETENTION
---------- ---------- -------------------- --------------------
0 1718803653 +00000 00:10:00.0 +00004 00:00:00.0
3 1593504508 +00000 00:15:00.0 +00015 00:00:00.0
SQL>
Now to increase MOVING_WINDOW value, we first need to increase retention.
SQL> begin
dbms_workload_repository.modify_baseline_window_size(
window_size => 8
);
end;
/
begin
*
ERROR at line 1:
ORA-13541: system moving window baseline size (691200) greater than retention (345600)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 1036
ORA-06512: at line 2
SQL> begin
dbms_workload_repository.modify_snapshot_settings(
interval => 15,
retention => 21600
);
end;
/
PL/SQL procedure successfully completed.
SQL> begin
dbms_workload_repository.modify_baseline_window_size(
window_size => 8
);
end;
/
PL/SQL procedure successfully completed.
SQL> select baseline_name,
baseline_type,
moving_window_size
from dba_hist_baseline
where baseline_type = 'MOVING_WINDOW';
BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
------------------------------ ------------- ------------------
SYSTEM_MOVING_WINDOW MOVING_WINDOW 8
SQL>
SQL> select con_id, dbid, snap_interval, retention
from cdb_hist_wr_control order by con_id;
CON_ID DBID SNAP_INTERVAL RETENTION
---------- ---------- -------------------- --------------------
0 1718803653 +00000 00:15:00.0 +00015 00:00:00.0
3 1593504508 +00000 00:15:00.0 +00015 00:00:00.0
SQL>
Generate Manul AWR snap:
4. We can generate manul AWR snap like below.
Check the current time and genearted AWR snaps.
SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
---------------------------------------
13-DEC-25 09.06.05.566286 PM +05:30
SQL> col BEGIN_INTERVAL_TIME for a30
SQL> col END_INTERVAL_TIME for a30
SQL> select snap_id, begin_interval_time, end_interval_time
from dba_hist_snapshot
order by snap_id desc;
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- ------------------------------ ------------------------------
193 13-DEC-25 08.50.41.053 PM 13-DEC-25 09.00.42.594 PM
192 13-DEC-25 08.42.37.081 PM 13-DEC-25 08.50.41.053 PM
191 13-DEC-25 08.28.35.786 PM 13-DEC-25 08.42.37.081 PM
190 13-DEC-25 07.30.15.521 PM 13-DEC-25 08.28.35.786 PM
189 13-DEC-25 06.23.04.246 PM 13-DEC-25 07.30.15.521 PM
188 13-DEC-25 06.12.35.000 PM 13-DEC-25 06.23.04.246 PM
187 09-DEC-25 08.09.44.062 PM 09-DEC-25 09.30.41.431 PM
186 09-DEC-25 07.59.30.000 PM 09-DEC-25 08.09.44.062 PM
185 07-DEC-25 04.30.39.843 PM 07-DEC-25 05.30.49.750 PM
184 07-DEC-25 03.30.30.767 PM 07-DEC-25 04.30.39.843 PM
183 07-DEC-25 02.30.24.354 PM 07-DEC-25 03.30.30.767 PM
182 07-DEC-25 01.30.19.229 PM 07-DEC-25 02.30.24.354 PM
181 07-DEC-25 12.30.08.525 PM 07-DEC-25 01.30.19.229 PM
180 07-DEC-25 11.44.46.012 AM 07-DEC-25 12.30.08.525 PM
179 07-DEC-25 11.33.55.000 AM 07-DEC-25 11.44.46.012 AM
15 rows selected.
SQL>
Create a manual snap.
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
Check the created snap.
SQL> select snap_id, begin_interval_time, end_interval_time
from dba_hist_snapshot
order by snap_id desc;
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- ------------------------------ ------------------------------
194 13-DEC-25 09.00.42.594 PM 13-DEC-25 09.07.55.166 PM
193 13-DEC-25 08.50.41.053 PM 13-DEC-25 09.00.42.594 PM
192 13-DEC-25 08.42.37.081 PM 13-DEC-25 08.50.41.053 PM
191 13-DEC-25 08.28.35.786 PM 13-DEC-25 08.42.37.081 PM
190 13-DEC-25 07.30.15.521 PM 13-DEC-25 08.28.35.786 PM
189 13-DEC-25 06.23.04.246 PM 13-DEC-25 07.30.15.521 PM
188 13-DEC-25 06.12.35.000 PM 13-DEC-25 06.23.04.246 PM
187 09-DEC-25 08.09.44.062 PM 09-DEC-25 09.30.41.431 PM
186 09-DEC-25 07.59.30.000 PM 09-DEC-25 08.09.44.062 PM
185 07-DEC-25 04.30.39.843 PM 07-DEC-25 05.30.49.750 PM
184 07-DEC-25 03.30.30.767 PM 07-DEC-25 04.30.39.843 PM
183 07-DEC-25 02.30.24.354 PM 07-DEC-25 03.30.30.767 PM
182 07-DEC-25 01.30.19.229 PM 07-DEC-25 02.30.24.354 PM
181 07-DEC-25 12.30.08.525 PM 07-DEC-25 01.30.19.229 PM
180 07-DEC-25 11.44.46.012 AM 07-DEC-25 12.30.08.525 PM
179 07-DEC-25 11.33.55.000 AM 07-DEC-25 11.44.46.012 AM
16 rows selected.
SQL>
Modify tablespace for AWR:
5. We can modify the default tablespace for AWR like below.
Checck current tablespace for AWR.
SQL> select dbid,CON_ID,TABLESPACE_NAME from dba_hist_wr_control;
DBID CON_ID TABLESPACE_NAME
---------- ---------- ----------------------------------------------
1718803653 0 SYSAUX
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> select dbid,CON_ID,TABLESPACE_NAME from dba_hist_wr_control;
DBID CON_ID TABLESPACE_NAME
---------- ---------- ----------------------------------------------
1718803653 0 SYSAUX
1593504508 3 SYSAUX
Create a new tablespace for AWR in CDB.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL>
SQL> create tablespace AWR datafile '/u01/app/oracle/oradata/ORCL/awr01.dbf' size 500m autoextend on;
Tablespace created.
SQL>
Set new tablespace for AWR in CDB.
SQL> begin
dbms_workload_repository.modify_snapshot_settings( tablespace_name=> 'AWR');
end;
/
PL/SQL procedure successfully completed.
SQL> select dbid,CON_ID,TABLESPACE_NAME from dba_hist_wr_control;
DBID CON_ID TABLESPACE_NAME
---------- ---------- -----------------------
1718803653 0 AWR
Create tablespace for PDB
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ORCLPDB READ WRITE NO
SQL>
SQL> create tablespace AWRPDB datafile '/u01/app/oracle/oradata/ORCL/orclpdb/awrpdb01.dbf' size 500m autoextend on;
Tablespace created.
Set new tablespace for AWR in PDB.
SQL> begin
dbms_workload_repository.modify_snapshot_settings( tablespace_name=> 'AWRPDB');
end;
/
PL/SQL procedure successfully completed.
SQL> select dbid,CON_ID,TABLESPACE_NAME from dba_hist_wr_control;
DBID CON_ID TABLESPACE_NAME
---------- ---------- -----------------
1718803653 0 AWR
1593504508 3 AWRPDB
SQL>
Purge old snaps
6. AWR snaps will be get purged based on the set retention. Although you can manually purge the range of AWR snaps like below.
Check current utilization of AWR snaps.
SQL> select tablespace_name,round(sum(bytes)/1024/1024,2) awr_used_mb from dba_segments where owner = 'SYS' and segment_name like 'WR%' group by tablespace_name;
TABLESPACE_NAME AWR_USED_MB
------------------------------ -----------
SYSTEM 3.31
SYSAUX 128.56
AWR 2.69
SQL> select count(*) from dba_hist_snapshot;
COUNT(*)
----------
34
SQL>
SQL> select min(snap_id), max(snap_id) from dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
179 213
SQL>
Drop set of AWR snaps.
SQL> begin
dbms_workload_repository.drop_snapshot_range(
low_snap_id => 179,
high_snap_id => 200
);
end;
/
PL/SQL procedure successfully completed.
SQL> select tablespace_name,round(sum(bytes)/1024/1024,2) awr_used_mb from dba_segments where owner = 'SYS' and segment_name like 'WR%' group by tablespace_name;
TABLESPACE_NAME AWR_USED_MB
------------------------------ -----------
SYSTEM 3.31
SYSAUX 116.63
AWR 2.69
SQL>
SQL> select min(snap_id), max(snap_id) from dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
201 213
Thank you for reading!
I hope this content has been helpful to you. Your feedback and suggestions are always welcome — feel free to leave a comment or reach out with any queries.
Abhishek Shrivastava
Comments
Post a Comment