Oracle 11g New Features DBMS_ADDM for RAC

Oracle 11g New Features DBMS_ADDM for RAC

Virag Sharma virag123@gmail.com

In “OCP Oracle Database 10g Exam Guide“ chapter 3 “Automatic Database Management” there is question

5. To retrieve the ADDM reports using SQL, what do you need to do?

A. Run the addmrpt.sql SQL script
B. Use the DBA_ADDM view
C. Use the DBA_ADVISOR view
D. Use the DBMS_ADDM package

Answer “A” is correct and D is wrong because there is no PL/SQL package named DBMS_ADDM.
But in 11g , it is not true , i.e. Package DBMS_ADDM is there in 11g. In case of RAC script addmrpt.sql
run give report for single instance, not report of all instance in RAC. But using DBMS_ADDM , we can generate report for all instance of RAC.

Different mode of DBMS_ADDM

1. Database Mode Instance Mode
   
2. Partial Mode

Database Mode of DBMS_ADDM

In database mode DBMS_ADDM , analyze all instance in RAC

VAR tname VARCHAR2(30); VAR start_snap_id number(10); VAR end_snap_id number(10); BEGIN :tname := ‘DB_MODE_TEST_RAG’; :start_snap_id = 100 ; :end_snap_id = 200 ; DBMS_ADDM.ANALYZE_DB(:tname, :start_snap_id, :end_snap_id); END;

Instance Mode of DBMS_ADDM
In Instance mode DBMS_ADDM , analyze one particular instance

VAR tname VARCHAR2(30); VAR start_snap_id number(10); VAR end_snap_id number(10); VAR INST_NUM number(10); BEGIN :tname := ‘INST_MODE_TEST_RAG’; :start_snap_id := 100 ; :end_snap_id := 200 ; :INST_NUM := 2; DBMS_ADDM.ANALYZE_INST(:tname,:start_snap_id,:end_snap_id, :INST_NUM ); END; /

Partial mode of DBMS_ADDM

In partial mode DBMS_ADDM analyze subset of instances. for example we want to analyze instance 2 and 4 out of four node RAC

VAR tname VARCHAR2(30); VAR start_snap_id number(10); VAR end_snap_id number(10); BEGIN :tname := ‘PART_MODE_TEST_RAG’; :start_snap_id :=100; :end_snap_id := 200; DBMS_ADDM.ANALYZE_PARTIAL(:tname,’2,4′, :start_snap_id, :end_snap_id); END; /

Displaying an ADDM Report

SET LONG 1000000 PAGESIZE 0; SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL;

The views expressed are my own and not necessarily those of my current or past employer. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine