自动数据库诊断监控 ADDM（Automatic Database Diagnostic Monitor）！

addmrpti.sql这个脚本是生成其他数据库的ADDM报告的！

[oracle@zfcs_web oracle]$ sqlplus / as sysdba @?/rdbms/admin/addmrpt.sqlSQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 2 11:30:52 2011Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsCurrent Instance~~~~~~~~~~~~~~~~   DB Id    DB Name Inst Num Instance----------- ------------ -------- ------------ 2325079448 NTDATA1 ntdataInstances in this Workload Repository schema~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   DB Id     Inst Num DB Name   InstanceHost------------ -------- ------------ ------------ ------------* 2325079448    1 NTDATA   ntdatalocalhost.localdomainUsing 2325079448 for database IdUsing       1 for instance numberSpecify 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> withoutspecifying a number lists all completed snapshots.Listing the last 3 days of Completed SnapshotsSnapInstance     DB Name    Snap Id    Snap Started    Level------------ ------------ --------- ------------------ -----ntdata     NTDATA       3431 30 Nov 2011 00:00   1       3432 30 Nov 2011 01:00   1       3433 30 Nov 2011 02:00   1       3434 30 Nov 2011 03:00   1       3435 30 Nov 2011 04:00   1       3436 30 Nov 2011 05:00   1       3437 30 Nov 2011 06:00   1       3438 30 Nov 2011 07:00   1       3439 30 Nov 2011 08:00   1       3440 30 Nov 2011 09:00   1       3441 30 Nov 2011 10:00   1       3442 30 Nov 2011 11:00   1       3443 30 Nov 2011 12:00   1       3444 30 Nov 2011 13:00   1       3445 30 Nov 2011 14:00   1       3446 30 Nov 2011 15:00   1       3447 30 Nov 2011 16:00   1       3448 30 Nov 2011 17:00   1       3449 30 Nov 2011 18:00   1       3450 30 Nov 2011 19:00   1       3451 30 Nov 2011 20:00   1       3452 30 Nov 2011 21:00   1       3453 30 Nov 2011 22:00   1       3454 30 Nov 2011 23:00   1       3455 01 Dec 2011 00:00   1       3456 01 Dec 2011 01:00   1       3457 01 Dec 2011 02:00   1       3458 01 Dec 2011 03:00   1       3459 01 Dec 2011 04:00   1       3460 01 Dec 2011 05:00   1       3461 01 Dec 2011 06:00   1       3462 01 Dec 2011 07:00   1       3463 01 Dec 2011 08:00   1       3464 01 Dec 2011 09:00   1       3465 01 Dec 2011 10:00   1       3466 01 Dec 2011 11:01   1       3467 01 Dec 2011 12:01   1       3468 01 Dec 2011 13:01   1       3469 01 Dec 2011 14:01   1       3470 01 Dec 2011 15:01   1       3471 01 Dec 2011 16:00   1       3472 01 Dec 2011 17:00   1       3473 01 Dec 2011 18:00   1       3474 01 Dec 2011 19:00   1       3475 01 Dec 2011 20:00   1       3476 01 Dec 2011 21:00   1       3477 01 Dec 2011 22:00   1       3478 01 Dec 2011 23:00   1       3479 02 Dec 2011 00:00   1       3480 02 Dec 2011 01:00   1       3481 02 Dec 2011 02:00   1       3482 02 Dec 2011 03:00   1       3483 02 Dec 2011 04:00   1       3484 02 Dec 2011 05:00   1       3485 02 Dec 2011 06:00   1       3486 02 Dec 2011 07:00   1SnapInstance     DB Name    Snap Id    Snap Started    Level------------ ------------ --------- ------------------ -----ntdata     NTDATA       3487 02 Dec 2011 08:00   1       3488 02 Dec 2011 09:00   1       3489 02 Dec 2011 10:00   1       3490 02 Dec 2011 11:00   1Specify the Begin and End Snapshot Ids~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Enter value for begin_snap: 3450  --这里输入一个开始快照的值。Begin Snapshot Id specified: 3450Enter value for end_snap: 3460   --这里输入一个结束快照的值。End   Snapshot Id specified: 3460Specify the Report Name~~~~~~~~~~~~~~~~~~~~~~~The default report file name is addmrpt_1_3450_3460.txt.  To use this name,press <return> to continue, otherwise enter an alternative.Enter value for report_name:    --这里使用默认报告名称。Using the report name addmrpt_1_3450_3460.txt    --使用报告名称addmrpt_1_3450_3460.txtRunning the ADDM analysis on the specified pair of snapshots ...  --对指定的快照运行ADDM分析...Generating the ADDM report for this analysis ...  --这个分析生成ADDM报告......  DETAILED ADDM REPORT FOR TASK 'TASK_3697' WITH ID 3697  --任务“TASK_3697编号3697的详细ADDM报告（从这里开始到后面结束就是ADDM报告的具体内容了！）  ------------------------------------------------------      Analysis Period: from 30-NOV-2011 19:00 to 01-DEC-2011 05:00 Database ID/Instance: 2325079448/1      Database/Instance Names: NTDATA/ntdata    Host Name: localhost.localdomain     Database Version: 10.2.0.1.0       Snapshot Range: from 3450 to 3460Database Time: 364 secondsAverage Database Load: 0 active sessions~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~FINDING 1: 32% impact (116 seconds)-----------------------------------Session connect and disconnect calls were consuming significant database time.   RECOMMENDATION 1: Application Analysis, 32% benefit (116 seconds)      ACTION: Investigate application logic for possible reduction of connect and disconnect calls. For example, you might use a connection pool scheme in the middle tier.FINDING 2: 31% impact (112 seconds)-----------------------------------SQL statements consuming significant database time were found.   RECOMMENDATION 1: SQL Tuning, 14% benefit (50 seconds)      ACTION: Investigate the SQL statement with SQL_ID "b6usrg82hwsa3" for possible performance improvements. RELEVANT OBJECT: SQL statement with SQL_ID b6usrg82hwsa3 call dbms_stats.gather_database_stats_job_proc (  )      RATIONALE: SQL statement with SQL_ID "b6usrg82hwsa3" was executed 1 times and had an average elapsed time of 49 seconds.   RECOMMENDATION 2: SQL Tuning, 5.9% benefit (21 seconds)      ACTION: Tune the PL/SQL block with SQL_ID "6gvch1xu9ca3g". 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 6gvch1xu9ca3g DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;      RATIONALE: SQL statement with SQL_ID "6gvch1xu9ca3g" was executed 554 times and had an average elapsed time of 0.04 seconds.   RECOMMENDATION 3: SQL Tuning, 4.9% benefit (18 seconds)      ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "0h6b2sajwb74n". RELEVANT OBJECT: SQL statement with SQL_ID 0h6b2sajwb74n and PLAN_HASH 3409717582 select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0      RATIONALE: SQL statement with SQL_ID "0h6b2sajwb74n" was executed 99226 times and had an average elapsed time of 0.00018 seconds.   RECOMMENDATION 4: SQL Tuning, 3.7% benefit (13 seconds)      ACTION: Tune the PL/SQL block with SQL_ID "b2hrmq9xsdw51". 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 b2hrmq9xsdw51 BEGIN EMD_LOADER.STRING_HISTORY_PURGE(:1); END;      RATIONALE: SQL statement with SQL_ID "b2hrmq9xsdw51" was executed 5 times and had an average elapsed time of 2.6 seconds.   RECOMMENDATION 5: SQL Tuning, 3.7% benefit (13 seconds)      ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "6g1p4s9ra6ag8". RELEVANT OBJECT: SQL statement with SQL_ID 6g1p4s9ra6ag8 and PLAN_HASH 1530554441 SELECT SMH.ROWID FROM (SELECT TARGET_GUID,METRIC_GUID,KEY_VALUE, LEAST(MAX(COLLECTION_TIMESTAMP),:B2 ) MAX_COLL FROM MGMT_STRING_METRIC_HISTORY WHERE TARGET_GUID = :B1 GROUP BY TARGET_GUID, METRIC_GUID, KEY_VALUE) MAX_STR_HIST, MGMT_STRING_METRIC_HISTORY SMH WHERE SMH.TARGET_GUID = MAX_STR_HIST.TARGET_GUID AND SMH.METRIC_GUID = MAX_STR_HIST.METRIC_GUID AND SMH.KEY_VALUE = MAX_STR_HIST.KEY_VALUE AND SMH.COLLECTION_TIMESTAMP < MAX_STR_HIST.MAX_COLL      RATIONALE: SQL statement with SQL_ID "6g1p4s9ra6ag8" was executed 5 times and had an average elapsed time of 2.6 seconds.FINDING 3: 15% impact (56 seconds)----------------------------------Waits on event "log file sync" while performing COMMIT and ROLLBACK operationswere consuming significant database time.   RECOMMENDATION 1: Application Analysis, 15% benefit (56 seconds)      ACTION: Investigate application logic for possible reduction in the number of COMMIT operations by increasing the size of transactions.      RATIONALE: The application was performing 14 transactions per minute with an average redo size of 9259 bytes per transaction.   RECOMMENDATION 2: Host Configuration, 15% benefit (56 seconds)      ACTION: Investigate the possibility of improving the performance of I/O to the online redo log files.      RATIONALE: The average size of writes to the online redo log files was 7 K and the average time per write was 7 milliseconds.   SYMPTOMS THAT LED TO THE FINDING:      SYMPTOM: Wait class "Commit" was consuming significant database time.       (15% impact [56 seconds])FINDING 4: 13% impact (47 seconds)----------------------------------Time spent on the CPU by the instance was responsible for a substantial partof database time.   RECOMMENDATION 1: Application Analysis, 10% benefit (37 seconds)      ACTION: Parsing SQL statements were consuming significant CPU. Please refer to other findings in this task about parsing for further details.   RECOMMENDATION 2: SQL Tuning, 5.2% benefit (19 seconds)      ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "c8khjbn635s3c". RELEVANT OBJECT: SQL statement with SQL_ID c8khjbn635s3c and PLAN_HASH 2645822727 select s.synonym_name object_name, o.object_type from all_synonyms s, sys.all_objects o where s.owner in ('PUBLIC', user) and o.owner = s.table_owner and o.object_name = s.table_name and o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')      ACTION: Investigate the SQL statement with SQL_ID "c8khjbn635s3c" for possible performance improvements. RELEVANT OBJECT: SQL statement with SQL_ID c8khjbn635s3c and PLAN_HASH 2645822727 select s.synonym_name object_name, o.object_type from all_synonyms s, sys.all_objects o where s.owner in ('PUBLIC', user) and o.owner = s.table_owner and o.object_name = s.table_name and o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')      RATIONALE: SQL statement with SQL_ID "c8khjbn635s3c" was executed 11 times and had an average elapsed time of 0.86 seconds.      RATIONALE: Average CPU used per execution was 0.86 seconds.FINDING 5: 8.7% impact (32 seconds)-----------------------------------Soft parsing of SQL statements was consuming significant database time.   RECOMMENDATION 1: Application Analysis, 8.7% benefit (32 seconds)      ACTION: Investigate application logic to keep open the frequently used cursors. Note that cursors are closed by both cursor close calls and session disconnects.   RECOMMENDATION 2: DB Configuration, 8.7% benefit (32 seconds)      ACTION: Consider increasing the maximum number of open cursors a session can have by increasing the value of parameter "open_cursors".      ACTION: Consider increasing the session cursor cache size by increasing the value of parameter "session_cached_cursors".      RATIONALE: The value of parameter "open_cursors" was "300" during the analysis period.      RATIONALE: The value of parameter "session_cached_cursors" was "20" during the analysis period.FINDING 6: 5.3% impact (19 seconds)-----------------------------------Wait class "User I/O" was consuming significant database time.   NO RECOMMENDATIONS AVAILABLE   ADDITIONAL INFORMATION:      Waits for I/O to temporary tablespaces were not consuming significant      database time.      The throughput of the I/O subsystem was not significantly lower than      expected.      The SGA was adequately sized.FINDING 7: 5.2% impact (19 seconds)-----------------------------------Wait event "SQL*Net more data from client" in wait class "Network" wasconsuming significant database time.   RECOMMENDATION 1: Application Analysis, 5.2% benefit (19 seconds)      ACTION: Investigate the cause for high "SQL*Net more data from client" waits. Refer to Oracle's "Database Reference" for the description of this wait event.   SYMPTOMS THAT LED TO THE FINDING:      SYMPTOM: Wait class "Network" was consuming significant database time.       (5.3% impact [19 seconds])FINDING 8: 4.8% impact (18 seconds)-----------------------------------Wait event "SQL*Net break/reset to client" in wait class "Application" wasconsuming significant database time.   RECOMMENDATION 1: Application Analysis, 4.8% benefit (18 seconds)      ACTION: Investigate the cause for high "SQL*Net break/reset to client" waits. Refer to Oracle's "Database Reference" for the description of this wait event.   SYMPTOMS THAT LED TO THE FINDING:      SYMPTOM: Wait class "Application" was consuming significant database       time. (4.9% impact [18 seconds])FINDING 9: 4.8% impact (17 seconds)-----------------------------------Hard parsing of SQL statements was consuming significant database time.   NO RECOMMENDATIONS AVAILABLE   ADDITIONAL INFORMATION:      Hard parses due to cursor environment mismatch were not consuming      significant database time.      Hard parsing SQL statements that encountered parse errors was not      consuming significant database time.      Hard parses due to literal usage and cursor invalidation were not      consuming significant database time.      The SGA was adequately sized.FINDING 10: 3.8% impact (14 seconds)------------------------------------Wait event "os thread startup" in wait class "Concurrency" was consumingsignificant database time.   RECOMMENDATION 1: Application Analysis, 3.8% benefit (14 seconds)      ACTION: Investigate the cause for high "os thread startup" waits. Refer to Oracle's "Database Reference" for the description of this wait event.   SYMPTOMS THAT LED TO THE FINDING:      SYMPTOM: Wait class "Concurrency" was consuming significant database       time. (3.9% impact [14 seconds])~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  ADDITIONAL INFORMATION  ----------------------Wait class "Configuration" was not consuming significant database time.The database's maintenance windows were active during 70% of the analysisperiod.The analysis of I/O performance is based on the default assumption that theaverage read time for one database block is 10000 micro-seconds.An explanation of the terminology used in this report is available when yourun the report with the 'ALL' level of detail.End of ReportReport written to addmrpt_1_3450_3460.txt