ENABLE SNAPSHOT(AWR OR ADDM OR ASH) IN PDB | AWR SNAPSHOTS ARE NOT GENERATED IN PDB BUT CDB ORACLE
This article is not only for configuration of AWR in PDB. No offence with the user but just describing my experience with a narration.
Do you know ? In 19c multitenant container database having PDB’s, AWR is not enabled by default in PDB’s. Manual configuration is required. If you are biased towards troubleshooting an issue, you would not have expected this situation.
In my experience, most of the customer’s feel that AWR snapshots are not working properly and complaint on the MMON process. Performance diagnosis depends on AWR which depends on MMON. But MMON process do not always fail to do its critical job. This is a misunderstanding between PDB configuration and MMON issue
I was troubleshooting a customer’s database where AWR snapshots were not generated. I scrutinized all the diagnostics logs possible but none of them given any clue and everything was looking and working fine. I also checked AWRINFO report for any sysaux space crunch or old dbid registered in the database. But do you think that will help? No, after a month, I realized that the customer was complaining about AWR which was not generated in PDB but working in CDB. That’s where something strike into my mind. Customer did not configure AWR for PDB in the first place. User complained that MR. MMON is not doing his job. MMON process in spite of its busy schedule of autotask maintenance jobs and other AWR jobs was accused as a culprit and felt guilty even though it does its job. Always better to confirm if the user is on a multitenant or not. So, in this case, we need to instruct MMON to collect snapshots for PDB explicitly by configuration.
Check if AWR snapshots are already enabled in the PDB
kIsH@Xhydra<>select * from awr_pdb_snapshot;
no rows selected
kIsH@Xhydra<>col INTERVAL for 9999999
kIsH@Xhydra<>col RETENTION for 9999999
kIsH@Xhydra<>select CON_ID,extract(minute from SNAP_INTERVAL) as INTERVAL,extract(second from RETENTION) RETENTION from cdb_hist_wr_control;
CON_ID INTERVAL RETENTION
---------- -------- ---------
5 1 0
kIsH@Xhydra<>show con_id,con_name
CON_ID
------------------------------
5
kIsH@Xhydra<>show con_name
CON_NAME
------------------------------
PDBZX1
Check the parameters which are needed to enable AWR and set the parameter awr_pdb_autoflush_enabled at system level
kIsH@Xhydra<>show parameter awr_%
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
awr_pdb_autoflush_enabled boolean FALSE
awr_pdb_max_parallel_slaves integer 10
awr_snapshot_time_offset integer 0
kIsH@Xhydra<> alter system set awr_pdb_autoflush_enabled=true;
System altered.
Change the snapshot settings using workload package and check the interval and retention
kIsH@Xhydra<>EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 60, retention => 11520);
PL/SQL procedure successfully completed.
kIsH@Xhydra<>select CON_ID,
2 extract(hour from SNAP_INTERVAL) as INTERVAL_IN_HOUR,
3 extract(day from RETENTION) RETENTION_IN_DAYS
4 from cdb_hist_wr_control;
CON_ID INTERVAL_IN_HOUR RETENTION_IN_DAYS
---------- ---------------- -----------------
5 1 8
Check the description of the parameter
kIsH@Xhydra<>col NAME for a20
kIsH@Xhydra<>col VALUE for a20
kIsH@Xhydra<>col DESCRIPTION for a20
kIsH@Xhydra<>select NAME,VALUE,DESCRIPTION from v$parameter where name like '%offset%';
NAME VALUE DESCRIPTION
-------------------- -------------------- --------------------
awr_snapshot_time_of 0 Setting for AWR Snap
fset shot Time Offset
Below parameter is used for better performance during AWR snap generation due to multiple containers
kIsH@Xhydra<>alter system set awr_snapshot_time_offset=1000000;
alter system set awr_snapshot_time_offset=1000000
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
kIsH@Xhydra<>alter session set container=PDBZX1;
Session altered.
kIsH@Xhydra<>alter system set awr_snapshot_time_offset=1000000;
System altered.
At last, check the snapshot generation and create an AWR report for tuning
kIsH@Xhydra<>select count(*) from awr_pdb_snapshot;
COUNT(*)
----------
54
kIsH@Xhydra<>@?/rdbms/admin/awrrpt
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 num_days: 2
Listing the last 2 days of Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
db9zx DB9ZX 162 30 Jun 2022 01:50 1
163 30 Jun 2022 03:00 1
164 30 Jun 2022 04:00 1
165 30 Jun 2022 05:50 1
Lessons learnt: So it is always better to validate configuration of AWR in PDB in the first place during the course of troubleshoot, rather to bias towards any issues. At last, MMON is considered innocent.
Happy tuning !