ADDM Supports From Oracle 19c PDB Database
Description:-
- This feature is available from Oracle 19c database.
- You can now use Automatic Database Diagnostic Monitor (ADDM) analysis for pluggable databases (PDBs) in a multitenant environment.ADDM analysis at a PDB level enables you to tune a PDB effectively for better performance.
- Starting with Oracle Database 12c, ADDM is enabled by default in the root container of a multitenant container database (CDB). Starting with Oracle Database 19c, you can also use ADDM in a pluggable database (PDB).
- ADDM is enabled by default in a CDB root.
- ADDM does not work in a PDB by default, because automatic AWR snapshots are disabled by default in a PDB. To use ADDM in a PDB, you must enable automatic AWR snapshots in the PDB.
- A user whose current container is the CDB root can view ADDM results for the entire CDB. The ADDM results can include information about multiple PDBs. ADDM results related to a PDB are not included if the PDB is unplugged.
How it works?
- In a CDB, ADDM works in the same way as it works in a non-CDB, that is, the ADDM analysis is performed each time an AWR snapshot is taken on a CDB root or a PDB, and the ADDM results are stored on the same database system where the snapshot is taken. The time period analyzed by ADDM is defined by the last two snapshots (the last hour by default).
- The ADDM results stored on the CDB root cannot be viewed when the current container is a PDB.
ADDM results on a PDB provide only PDB-specific findings and recommendations. A user whose current container is a PDB can view ADDM results for the current PDB only. - The ADDM results exclude findings that apply to the CDB as a whole, for example, I/O problems relating to the buffer cache size.Enabling AWR snapshots on a PDB does not change the ADDM report on the CDB root.AWR data on a PDB cannot be accessed from the CDB root.
PDB-Level ADDM Restrictions
Unlike in a non-CDB, ADDM does not report the following issues in a PDB, because these issues apply to a CDB as a whole and do not apply to an individual PDB
I/O problems due to:
- undersized buffer cache
- undersized streams pool
- excessive temporary writes
- excessive checkpoint writes
- excessive undo writes
- excessive PQ checkpoint writes
- excessive truncate writes
- excessive tablespace DDL checkpoint
- I/O capacity limit
SQL hard parsing issues due to:
- cursor aging
- out-of-memory failed parse
SGA sizing issues:
ADDM also does not report the following issues in a PDB, because these issues cannot be resolved at a PDB level:
- Cluster messaging related issues, such as network latency, congestion, contention, and lost blocks
- Log file switch waits on archiving and on checkpoint incomplete
- Too many free-buffer waits
- Contention on log buffer waits
- Waits due to CPU bottleneck
- Operating system VM paging
- Session slot wait event
- CPU quantum wait event
- RMAN related wait events, such as PQ queued wait event, PGA limit wait event, and I/O queue wait event
Demo:-
Enabling ADDM in a Oracle 19c Pluggable Database
ADDM does not work in a pluggable database (PDB) by default, because automatic AWR snapshots are disabled by default in a PDB. To use ADDM in a PDB, you must enable automatic AWR snapshots in the PDB by setting the AWR_PDB_AUTOFLUSH_ENABLED initialization parameter to TRUE and AWR snapshot interval greater than 0.
[oracle@dev19c ~]$ export ORACLE_SID=dbwr
[oracle@dev19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Oct 21 22:22:37 2019
Version 19.4.1.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1157627856 bytes
Fixed Size 9134032 bytes
Variable Size 520093696 bytes
Database Buffers 620756992 bytes
Redo Buffers 7643136 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 DBWRPDB READ WRITE NO
SQL> COLUMN name FORMAT A15
SQL> SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
————— ———-
PDB$SEED READ ONLY
DBWRPDB READ WRITE
To enable ADDM in a PDB:
Set the AWR_PDB_AUTOFLUSH_ENABLED initialization parameter to TRUE in the PDB using the following command:
SQL> ALTER SESSION SET CONTAINER =DBWRPDB;
Session altered.
SQL> show parameter AWR_PDB_AUTOFLUSH_ENABLED
NAME TYPE VALUE
———————————— ———– ——————————
awr_pdb_autoflush_enabled boolean FALSE
SQL> ALTER SYSTEM SET AWR_PDB_AUTOFLUSH_ENABLED=TRUE;
System altered.
SQL> EXEC dbms_workload_repository.modify_snapshot_settings(interval=>60);
PL/SQL procedure successfully completed.
SQL> select SRC_DBNAME ,SNAP_INTERVAL from DBA_HIST_WR_CONTROL;
SRC_DBNAME SNAP_INTERVAL
——————– ——————–
DBWRPDB +00000 01:00:00.0
CDB$ROOT +00000 01:00:00.0
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
PL/SQL procedure successfully completed.
wait some time….
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
PL/SQL procedure successfully completed.
Check Snapshot DBA_HIST_SNAPSHOT
Testing Started to take addm report oracle 19c PDB database.
SQL> CREATE TABLE scale_data (
section NUMBER NOT NULL,
id1 NUMBER NOT NULL,
id2 NUMBER NOT NULL
); 2 3 4 5
Table created.
SQL> INSERT INTO scale_data
SELECT sections.n, gen.x, CEIL(DBMS_RANDOM.VALUE(0, 100))
FROM (
SELECT level – 1 n
FROM DUAL
CONNECT BY level < 300) sections
, (
SELECT level x
FROM DUAL
CONNECT BY level < 900000) gen
WHERE gen.x <= sections.n * 3000;
133653000 rows created.
SQL> SQL> commit;
Commit complete.
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
PL/SQL procedure successfully completed.
SQL> @?/rdbms/admin/addmrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
———– ———— ——– ————
3303028116 DBWR 1 dbwr
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
———— ———- ——— ———- ——
3303028116 1 DBWR dbwr dev19c
Using 3303028116 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.
Listing the last 3 days of Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
———— ———— ———- —————— ———-
dbwr DBWR 1 22 Oct 2019 08:10 1
2 22 Oct 2019 08:16 1
3 22 Oct 2019 08:22 1
4 22 Oct 2019 08:42 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 2
Begin Snapshot Id specified: 2
Enter value for end_snap: 4
End Snapshot Id specified: 4
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is addmrpt_1_2_4.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name addmrpt_1_2_4.txt
Running the ADDM analysis on the specified pair of snapshots …
Generating the ADDM report for this analysis …
ADDM Report for Task ‘TASK_15’
——————————
Analysis Period
—————
AWR snapshot range from 2 to 4.
Time period starts at 22-OCT-19 08.16.32 AM
Time period ends at 22-OCT-19 08.42.19 AM
Analysis Target
—————
Database ‘DBWR’ with DB ID 3303028116.
Database version 19.0.0.0.0.
ADDM performed an analysis of instance dbwr, numbered 1 and hosted at dev19c.
ADDM detected that the system is a PDB.
Activity During the Analysis Period
———————————–
Total database time was 573 seconds.
The average number of active sessions was .37.
Summary of Findings
——————-
Description Active Sessions Recommendations
Percent of Activity
——————— ——————- —————
1 Top SQL Statements .37 | 100 1
2 PL/SQL Execution .1 | 28.07 1
3 “User I/O” wait Class .01 | 2.45
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and Recommendations
—————————-
Finding 1: Top SQL Statements
Impact is .37 active sessions, 100% of total activity.
——————————————————
SQL statements consuming significant database time were found. These
statements offer a good opportunity for performance improvement.
Recommendation 1: SQL Tuning
Estimated benefit is .37 active sessions, 100% of total activity.
—————————————————————–
Action
Run SQL Tuning Advisor on the INSERT statement with SQL_ID
“711q7nyk6g2y2”.
Related Object
SQL statement with SQL_ID 711q7nyk6g2y2.
INSERT INTO scale_data
SELECT sections.n, gen.x, CEIL(DBMS_RANDOM.VALUE(0, 100))
FROM (
SELECT level – 1 n
FROM DUAL
CONNECT BY level < 300) sections
, (
SELECT level x
FROM DUAL
CONNECT BY level < 900000) gen
WHERE gen.x <= sections.n * 3000
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 62% for SQL
execution, 0% for parsing, 38% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID “711q7nyk6g2y2” was executed 1 times and had
an average elapsed time of 567 seconds.
Finding 2: PL/SQL Execution
Impact is .1 active sessions, 28.07% of total activity.
——————————————————-
PL/SQL execution consumed significant database time.
Recommendation 1: SQL Tuning
Estimated benefit is .1 active sessions, 28.07% of total activity.
——————————————————————
Action
Tune the entry point PL/SQL ID 15115. Refer to the PL/SQL documentation
for addition information.
Rationale
Subprogam ID 4 of PL/SQL ID 15115 spent 120 seconds in execution.
Finding 3: “User I/O” wait Class
Impact is .01 active sessions, 2.45% of total activity.
——————————————————-
Wait class “User I/O” was consuming significant database time.
No recommendations are available.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
———————-
Miscellaneous Information
————————-
Wait class “Application” was not consuming significant database time.
Wait class “Commit” was not consuming significant database time.
Wait class “Concurrency” was not consuming significant database time.
Wait class “Configuration” was not consuming significant database time.
CPU was not a bottleneck for the DB on this instance.
Wait class “Network” was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.
Hard parsing of SQL statements was not consuming significant database time.
End of Report
Report written to addmrpt_1_2_4.txt
More information check for the below views:-
DBA_ADDM_* and DBA_ADVISOR_*
Telegram App: https://t.me/oracledbwr
LinkedIn: https://www.linkedin.com/in/hariprasathdba
LinkedIN Page: https://www.linkedin.com/company/orcldbwr/
Facebook: https://www.facebook.com/HariPrasathdba
FB Group: https://www.facebook.com/groups/oracledbwr/
FB Page : https://www.facebook.com/dbahariprasath/
Twitter : https://twitter.com/hariprasathdba
