AWR Snapshot listing empty results Oracle Database 12c Release 2 (12.2.0.1)

I was checking some performance issues in some databases and one of the issues I found was that I couldn’t get the snapshot IDs to choose the interval between “Begin” and “End” snapshot to create my report. I started to find the reason for not showing the expected output as I can list as below:

  • Check tablespace SYSAUX usage: I had 25GB free of 75GB; So, OK.
  • Parameter “statistics_level” was configured to TYPICAL; Thus, OK.
  • Parameter “control_management_pack_access” was configured to “DIAGNOSTIC+TUNING”. So, OK.

I checked MOS and started to do more research and then I found many occurrences regarding the MMON process. This was the issue reported by the ACED Rodrigo Jorge . However, in my environment which was in PRODUCTION and then a very important database, gladly it wasn’t the same issue once the MMON process was running okay to my ASM and 2 other instances associated with this ASM. So what wasn’t configured at this database once the essentials parameters were set up to generate the AWR report? Well, what came to my mind is that either the interval or retention time weren’t configured properly and guess what? CHECKMATE! The database was set up with lower retention time and an unappropriated snap_interval and after my configuration, I could create the reports as below.

SQL>select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL
---------------------------------------------------------------------------
RETENTION
---------------------------------------------------------------------------
+40150 00:00:00.0
+00008 00:00:00.0


SQL>execute dbms_workload_repository.modify_snapshot_settings(interval => 30,retention => 43200);

PL/SQL procedure successfully completed.

SQL>select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL
---------------------------------------------------------------------------
RETENTION
---------------------------------------------------------------------------
+00000 00:30:00.0
+00030 00:00:00.0

Previous output:


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: 5

Listing the last 5 days of Completed Snapshots


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:
Begin Snapshot Id specified:

Enter value for end_snap:
End   Snapshot Id specified:

After modification:


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: 2

Listing the last 2 days of Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------

tech           tech       752  03 May 2021 13:56    1
                                753  03 May 2021 14:30    1
                                754  03 May 2021 15:00    1
                                755  03 May 2021 15:30    1


If this post was interesting to you, leave a comment 🙂

Related posts

Leave a Comment