Three ways to run ADDM analysis:
Option 1 , Running ADDM Using addmrpt.sql
(@$ORACLE_HOME/rdbms/admin/addmrpt.sql)
SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql
... ...
Listing the last 3 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ctgbu2 CTGBU2 6611 14 Jun 2009 00:00 1
6612 14 Jun 2009 01:00 1
6613 14 Jun 2009 02:00 1
6614 14 Jun 2009 03:00 1
6615 14 Jun 2009 04:00 1
6616 14 Jun 2009 05:00 1
6617 14 Jun 2009 06:00 1
6618 14 Jun 2009 07:00 1
6619 14 Jun 2009 08:00 1
6620 14 Jun 2009 09:00 1
6621 14 Jun 2009 10:00 1
6622 14 Jun 2009 11:00 1
6623 14 Jun 2009 12:00 1
6624 14 Jun 2009 13:00 1
6625 14 Jun 2009 14:00 1
6626 14 Jun 2009 15:00 1
6627 14 Jun 2009 16:00 1
6628 14 Jun 2009 17:00 1
6629 14 Jun 2009 18:00 1
6630 14 Jun 2009 19:00 1
6631 14 Jun 2009 20:01 1
6632 14 Jun 2009 21:00 1
6633 14 Jun 2009 22:00 1
6634 14 Jun 2009 23:00 1
6635 15 Jun 2009 00:00 1
6636 15 Jun 2009 01:00 1
6637 15 Jun 2009 02:00 1
6638 15 Jun 2009 03:00 1
6639 15 Jun 2009 04:00 1
6640 15 Jun 2009 05:00 1
6641 15 Jun 2009 06:00 1
6642 15 Jun 2009 07:00 1
6643 15 Jun 2009 08:00 1
6644 15 Jun 2009 09:00 1
6645 15 Jun 2009 10:00 1
6646 15 Jun 2009 11:00 1
6647 15 Jun 2009 12:00 1
6648 15 Jun 2009 13:00 1
6649 15 Jun 2009 14:00 1
6650 15 Jun 2009 15:00 1
6651 15 Jun 2009 16:00 1
6652 15 Jun 2009 17:00 1
6653 15 Jun 2009 18:00 1
6654 15 Jun 2009 19:00 1
6655 15 Jun 2009 20:00 1
6656 15 Jun 2009 21:00 1
6657 15 Jun 2009 22:00 1
6658 15 Jun 2009 23:00 1
6659 16 Jun 2009 00:00 1
6660 16 Jun 2009 01:00 1
6661 16 Jun 2009 02:00 1
6662 16 Jun 2009 03:00 1
6663 16 Jun 2009 04:01 1
6664 16 Jun 2009 05:01 1
6665 16 Jun 2009 06:01 1
6666 16 Jun 2009 07:01 1
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ctgbu2 CTGBU2 6667 16 Jun 2009 08:00 1
6668 16 Jun 2009 09:00 1
6669 16 Jun 2009 10:01 1
6670 16 Jun 2009 11:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 6664
Begin Snapshot Id specified: 6664
Enter value for end_snap: 6666
End Snapshot Id specified: 6666
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is addmrpt_1_6664_6666.txt. To use this name,
press
Enter value for report_name: /tmp/addmtest.log
Using the report name /tmp/addmtest.log
Running the ADDM analysis on the specified pair of snapshots ...
Generating the ADDM report for this analysis ...
DETAILED ADDM REPORT FOR TASK 'TASK_31477' WITH ID 31477
--------------------------------------------------------
Analysis Period: 16-JUN-2009 from 05:01:03 to 07:01:09
Database ID/Instance: 2568139559/1
Database/Instance Names: CTGBU2/ctgbu2
Host Name: JPSIT01
Database Version: 10.1.0.4.0
Snapshot Range: from 6664 to 6666
Database Time: 8710 seconds
Average Database Load: 1.2 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 14% impact (1261 seconds)
------------------------------------
Individual SQL statements responsible for significant user I/O wait were
found.
RECOMMENDATION 1: SQL Tuning, 4.1% benefit (356 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "89j4h9uu70grb". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID 89j4h9uu70grb
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN /* Database Analysis report */
quest_ix_scheduler.run_task(147,JOB,NEXT_DATE); :mydate := next_date;
IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
RECOMMENDATION 2: SQL Tuning, 3.5% benefit (302 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "dxh8b2fpqfnu5". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID dxh8b2fpqfnu5
BEGIN :rule_value:=quest_ix_cbo_rule_pak.stale_analyzer_stats(:run_id
); END;
RECOMMENDATION 3: SQL Tuning, 3.5% benefit (302 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"07k0t67czx7g6".
RELEVANT OBJECT: SQL statement with SQL_ID 07k0t67czx7g6 and
PLAN_HASH 2736083658
SELECT OWNER , TABLE_NAME , BLOCKS , ANALYZED_BLOCKS , BLOCK_VARIANCE
FROM QUEST_IX_STALE_STATS ORDER BY 5 DESC
RECOMMENDATION 4: SQL Tuning, 3.4% benefit (300 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "bqx3f42d29g34". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID bqx3f42d29g34
BEGIN DBMS_SPACE.SPACE_USAGE(segment_owner=>:segment_owner,segment_na
me=>:segment_name,segment_type=>:segment_type,unformatted_blocks=>:un
f,unformatted_bytes=>:unfb,fs1_blocks=>:fs1,fs1_bytes=>:fs1b,fs2_bloc
ks=>:fs2,fs2_bytes=>:fs2b,fs3_blocks=>:fs3,fs3_bytes=>:fs3b,fs4_block
s=>:fs4,fs4_bytes=>:fs4b,full_blocks=>:full,full_bytes=>:fullb); END;
SYMPTOMS THAT LED TO THE FINDING:
Wait class "User I/O" was consuming significant database time. (4.3%
impact [378 seconds])
FINDING 2: 14% impact (1236 seconds)
------------------------------------
PL/SQL execution consumed significant database time.
RECOMMENDATION 1: SQL Tuning, 3.6% benefit (316 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "89j4h9uu70grb". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID 89j4h9uu70grb
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN /* Database Analysis report */
quest_ix_scheduler.run_task(147,JOB,NEXT_DATE); :mydate := next_date;
IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
RECOMMENDATION 2: SQL Tuning, 3.5% benefit (307 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "dxh8b2fpqfnu5". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID dxh8b2fpqfnu5
BEGIN :rule_value:=quest_ix_cbo_rule_pak.stale_analyzer_stats(:run_id
); END;
RECOMMENDATION 3: SQL Tuning, 3.5% benefit (307 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"07k0t67czx7g6".
RELEVANT OBJECT: SQL statement with SQL_ID 07k0t67czx7g6 and
PLAN_HASH 2736083658
SELECT OWNER , TABLE_NAME , BLOCKS , ANALYZED_BLOCKS , BLOCK_VARIANCE
FROM QUEST_IX_STALE_STATS ORDER BY 5 DESC
RECOMMENDATION 4: SQL Tuning, 3.5% benefit (305 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "bqx3f42d29g34". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID bqx3f42d29g34
BEGIN DBMS_SPACE.SPACE_USAGE(segment_owner=>:segment_owner,segment_na
me=>:segment_name,segment_type=>:segment_type,unformatted_blocks=>:un
f,unformatted_bytes=>:unfb,fs1_blocks=>:fs1,fs1_bytes=>:fs1b,fs2_bloc
ks=>:fs2,fs2_bytes=>:fs2b,fs3_blocks=>:fs3,fs3_bytes=>:fs3b,fs4_block
s=>:fs4,fs4_bytes=>:fs4b,full_blocks=>:full,full_bytes=>:fullb); END;
FINDING 3: 4.5% impact (391 seconds)
------------------------------------
Host CPU was a bottleneck and the instance was consuming 89% of the host CPU.
All wait times will be inflated by wait for CPU.
RECOMMENDATION 1: Host Configuration, 4.5% benefit (391 seconds)
ACTION: Consider adding more CPUs to the host or increasing the number
of instances serving the database.
ADDITIONAL INFORMATION: Host CPU consumption was 76%. The instance spent
significant time on CPU. However, there were no predominant SQL
statements responsible for the CPU load.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ADDITIONAL INFORMATION
----------------------
Wait class "Administrative" was not consuming significant database time.
Wait class "Application" was not consuming significant database time.
Wait class "Cluster" 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.
Wait class "Network" was not consuming significant database time.
Wait class "Scheduler" was not consuming significant database time.
Wait class "Other" was not consuming significant database time.
The analysis of I/O performance is based on the default assumption that the
average read time for one database block is 10000 micro-seconds.
An explanation of the terminology used in this report is available when you
run the report with the 'ALL' level of detail.
Option 2, Running ADDM Using DBMS_ADVISOR APIs
Step 1: create function for ADDM Analysis on a Pair of Snapshots.
CREATE OR REPLACE FUNCTION run_addm(start_time IN DATE, end_time IN DATE )
RETURN VARCHAR2
IS
begin_snap NUMBER;
end_snap NUMBER;
tid NUMBER;
-- Task ID tname VARCHAR2(30);
-- Task Name tdesc VARCHAR2(256);
-- Task DescriptionBEGIN
-- Find the snapshot IDs corresponding to the given input parameters.
SELECT max(snap_id)INTO begin_snap FROM DBA_HIST_SNAPSHOT
WHERE trunc(end_interval_time, 'MI') <= start_time;
SELECT min(snap_id) INTO end_snap FROM DBA_HIST_SNAPSHOT
WHERE end_interval_time >= end_time;
-- -- set Task Name (tname) to NULL and let create_task return a unique name for the task.
tname := '';
tdesc := 'run_addm( ' begin_snap ', ' end_snap ' )';
-- -- Create a task, set task parameters and execute it
DBMS_ADVISOR.CREATE_TASK( 'ADDM', tid, tname, tdesc );
DBMS_ADVISOR.SET_TASK_PARAMETER( tname, 'START_SNAPSHOT', begin_snap );
DBMS_ADVISOR.SET_TASK_PARAMETER( tname, 'END_SNAPSHOT' , end_snap );
DBMS_ADVISOR.EXECUTE_TASK( tname );
Step 2: Reporting ADDM Analysis on a Pair of Specific Snapshots
-- set SQL*Plus variables and column formats for the report
SET PAGESIZE 0 LONG 1000000 LONGCHUNKSIZE 1000;
COLUMN get_clob FORMAT a80;
-- execute run_addm() with 5am and 7am as input
VARIABLE task_name VARCHAR2(30);
BEGIN
:task_name := run_addm( TO_DATE('05:00:01 (06/16)', 'HH24:MI:SS (MM/DD)'),
TO_DATE('07:00:01 (06/16)', 'HH24:MI:SS (MM/DD)') );
END;
/
-- execute GET_TASK_REPORT to get the textual ADDM report.
SELECT DBMS_ADVISOR.GET_TASK_REPORT(:task_name)
FROM DBA_ADVISOR_TASKS t
WHERE t.task_name = :task_name
AND t.owner = SYS_CONTEXT( 'userenv', 'session_user');
Option 3, Running ADDM Report Using DBMS_ADVISOR
SQL>
BEGIN
DBMS_ADVISOR.create_task (
advisor_name => 'ADDM',
task_name => '5838_6027_AWR_SNAPSHOT',
task_desc => 'Advisor for snapshots 5838 to 6027');
DBMS_ADVISOR.set_task_parameter (
task_name => '5838_6027_AWR_SNAPSHOT',
parameter => 'START_SNAPSHOT',
value => 5838);
DBMS_ADVISOR.set_task_parameter (
task_name => '5838_6027_AWR_SNAPSHOT',
parameter => 'END_SNAPSHOT',
value => 6027);
DBMS_ADVISOR.execute_task(task_name => '5838_6027_AWR_SNAPSHOT');
END;
/
PL/SQL procedure successfully completed.
SQL> SET LONG 100000 PAGESIZE 50000
SQL> SELECT DBMS_ADVISOR.get_task_report('5838_6027_AWR_SNAPSHOT') AS report FROM dual;
REPORT
--------------------------------------------------------------------------------
DETAILED ADDM REPORT FOR TASK '5838_6027_AWR_SNAPSHOT' WITH ID 30828
--------------------------------------------------------------------
Analysis Period: from 13-MAY-2009 00:00 to 20-MAY-2009 16:00
Database ID/Instance: 2568139559/1
... ...
Views with ADDM Information
1. DBA_ADVISOR_TASKS This view provides basic information about existing tasks, such as the task Id, task name, and when created.
2. DBA_ADVISOR_LOG This view contains the current task information, such as status, progress, error messages, and execution times.
3. DBA_ADVISOR_RECOMMENDATIONS This view displays the results of completed diagnostic tasks with recommendations for the problems identified in each run. The recommendations should be looked at in the order of the RANK column, as this relays the magnitude of the problem for the recommendation. The BENEFIT column gives the benefit to the system you can expect after the recommendation is carried out.
4. DBA_ADVISOR_FINDINGS This view displays all the findings and symptoms that the diagnostic monitor encountered along with the specific recommendation.
No comments:
Post a Comment