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 🙂
Hi! I am Bruno, a Brazilian born and bred. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)