Oracle10g数据库自动诊断监视工具(ADDM)使用指南(2)

第六步：查看优化建议结果

通知函数dbms_advisor.get_task_report可以得到优化建议结果。

SQL> SET LONG 1000000 PAGESIZE 0 LONGCHUNKSIZE 1000

SQL> COLUMN get_clob FORMAT a80

SQL> SELECT dbms_advisor.get_task_report('DEMO_ADDM01', 'TEXT', 'ALL') FROM DUAL;

DBMS_ADVISOR.GET_TASK_REPORT('

--------------------------------------------------------------------------------

          DETAILED ADDM REPORT FOR TASK 'DEMO_ADDM01' WITH ID 243

          -------------------------------------------------------

              Analysis Period: 23-NOV-2005 from 15:02:27 to 16:06:42

         Database ID/Instance: 1712582900/1

      Database/Instance Names: EDGAR/edgar

                    Host Name: HUANGED

             Database Version: 10.2.0.1.0

               Snapshot Range: from 65 to 66

                Database Time: 1463 seconds

        Average Database Load: .4 active sessions

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

FINDING 1: 100% impact (1463 seconds)

-------------------------------------

Significant virtual memory paging was detected on the host operating system.

   RECOMMENDATION 1: Host Configuration, 100% benefit (1463 seconds)

      ACTION: Host operating system was experiencing significant paging but no

         particular root cause could be detected. Investigate processes that

         do not belong to this instance running on the host that are consuming

         significant amount of virtual memory. Also consider adding more

         physical memory to the host.

FINDING 2: 100% impact (1463 seconds)

-------------------------------------

SQL statements consuming significant database time were found.

   RECOMMENDATION 1: SQL Tuning, 68% benefit (998 seconds)

      ACTION: Tune the PL/SQL block with SQL_ID "064wqx7c5b81z". 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 064wqx7c5b81z

         DECLARE

         v_var number;

         BEGIN

         FOR n IN 1..10000

         LOOP

         select count(*) into v_var from bigtab b, smalltab a;

         END LOOP;

         END;

   RECOMMENDATION 2: SQL Tuning, 67% benefit (986 seconds)

      ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID

         "fvqfghq71cqns".

         RELEVANT OBJECT: SQL statement with SQL_ID fvqfghq71cqns and

         PLAN_HASH 3281046854

         SELECT COUNT(*) FROM BIGTAB B, SMALLTAB A

      RATIONALE: SQL statement with SQL_ID "fvqfghq71cqns" was executed 6

         times and had an average elapsed time of 166 seconds.

FINDING 3: 69% impact (1002 seconds)

------------------------------------

Time spent on the CPU by the instance was responsible for a substantial part

of database time.

   RECOMMENDATION 1: SQL Tuning, 67% benefit (986 seconds)

      ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID

         "fvqfghq71cqns".

         RELEVANT OBJECT: SQL statement with SQL_ID fvqfghq71cqns and

         PLAN_HASH 3281046854

         SELECT COUNT(*) FROM BIGTAB B, SMALLTAB A

      RATIONALE: SQL statement with SQL_ID "fvqfghq71cqns" was executed 6

         times and had an average elapsed time of 166 seconds.

      RATIONALE: Average CPU used per execution was 162 seconds.

   RECOMMENDATION 2: SQL Tuning, 2.1% benefit (30 seconds)

      ACTION: Tune the PL/SQL block with SQL_ID "2b064ybzkwf1y". 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 2b064ybzkwf1y

         BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;

      RATIONALE: SQL statement with SQL_ID "2b064ybzkwf1y" was executed 125

         times and had an average elapsed time of 0.26 seconds.

      RATIONALE: Average CPU used per execution was 0.24 seconds.

FINDING 4: 2.2% impact (33 seconds)

-----------------------------------

PL/SQL execution consumed significant database time.

   RECOMMENDATION 1: SQL Tuning, 2.2% benefit (33 seconds)

      ACTION: Tune the PL/SQL block with SQL_ID "2b064ybzkwf1y". 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 2b064ybzkwf1y

         BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;

      RATIONALE: SQL statement with SQL_ID "2b064ybzkwf1y" was executed 125

         times and had an average elapsed time of 0.26 seconds.

      RATIONALE: Average time spent in PL/SQL execution was 0.26 seconds.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

          ADDITIONAL 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.

Wait class "Network" was not consuming significant database time.

Wait class "User I/O" 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.

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.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

          TERMINOLOGY

          -----------

DATABASE TIME: This is the ADDM's measurement of throughput. From the user's

   point of view: this is the total amount of time spent by users waiting for

   a response from the database after issuing a call (not including

   networking). From the database instance point of view: this is the total

   time spent by forground processes waiting for a database resource (e.g.,

   read I/O), running on the CPU and waiting for a free CPU (run-queue). The

   target of ADDM analysis is to reduce this metric as much as possible,

   thereby reducing the instance's response time.

AVERAGE DATABASE LOAD: At any given time we can count how many users (also

   called 'Active Sessions') are waiting for an answer from the instance. This

   is the ADDM's measurement for instance load. The 'Average Database Load' is

   the average of the the load measurement taken over the entire analysis

   period. We get this number by dividing the 'Database Time' by the analysis

   period. For example, if the analysis period is 30 minutes and the 'Database

   Time' is 90 minutes, we have an average of 3 users waiting for a response.

IMPACT: Each finding has an 'Impact' associated with it. The impact is the

   portion of the 'Database Time' the finding deals with. If we assume that

   the problem described by the finding is completely solved, then the

   'Database Time' will be reduced by the amount of the 'Impact'.

BENEFIT: Each recommendation has a 'benefit' associated with it. The ADDM

   analysis estimates that the 'Database Time' can be reduced by the 'benefit'

   amount if all the actions of the recommendation are performed.

说明：

其中第五步到第六步可以直接执行$ORACLE_HOME/rdbms/admin/addmrpt.sql来得到，这个脚本的执行过程和statspack脚本执行过程类似：

SQL> @addmrpt

Current Instance

~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance

----------- ------------ -------- ------------

 1712582900 EDGAR               1 edgar

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host

------------ -------- ------------ ------------ ------------

* 1712582900        1 EDGAR        edgar        HUANGED

Using 1712582900 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  without

specifying a number lists all completed snapshots.

Listing the last 3 days of Completed Snapshots

                                                        Snap

Instance     DB Name        Snap Id    Snap Started    Level

------------ ------------ --------- ------------------ -----

edgar        EDGAR                7 22 Nov 2005 00:00      1

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9650775/viewspace-920374/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9650775/viewspace-920374/