Thursday, September 20, 2007

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;
VAR end_snap_id number;

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;
VAR end_snap_id number;
VAR INST_NUM number;
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;
VAR end_snap_id number;

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;

  or

 SELECT DBMS_ADDM.GET_REPORT('DB_MODE_TEST_RAG') FROM DUAL;


Posted by at
Labels:

2 comments:

  1. NataliaNovember 1, 2011 at 12:35 AM

    I have read that book and was assuming that option D is the correct answer. After reading all the reasons and details from the above article I got to know the exact point. Thanks for sharing in such a detailed way.
    sap upgrade

    ReplyDelete
  2. BillcarsonDecember 11, 2020 at 10:53 PM

    I feel really happy to have seen your webpage and look forward to so many more entertaining times reading here.Same as your blog i found another one Oracle Project Portfolio Management Cloud Training.Actually I was looking for the same information on internet for Oracle PPM and came across your blog. I am impressed by the information that you have on this blog. Thanks once more for all the details.

    ReplyDelete

Subscribe to: Post Comments (Atom)
Google