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 !

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s