自動資料庫診斷監控 ADDM（Automatic Database Diagnostic Monitor）！
自動資料庫診斷監控 ADDM（Automatic Database Diagnostic Monitor）！
addmrpti.sql這個指令碼是生成其他資料庫的ADDM報告的！
[oracle@zfcs_web oracle]$ sqlplus / as sysdba @?/rdbms/admin/addmrpt.sql
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 2 11:30:52 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2325079448 NTDATA 1 ntdata
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 2325079448 1 NTDATA ntdata localhost.lo
caldomain
Using 2325079448 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 <return> 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
------------ ------------ --------- ------------------ -----
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 1
Snap
Instance 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 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 3450 --這裡輸入一個開始快照的值。
Begin Snapshot Id specified: 3450
Enter value for end_snap: 3460 --這裡輸入一個結束快照的值。
End Snapshot Id specified: 3460
Specify 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.txt
Running 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 3460
Database Time: 364 seconds
Average 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 operations
were 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 part
of 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" was
consuming 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" was
consuming 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 consuming
significant 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 analysis
period.
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.
End of Report
Report written to addmrpt_1_3450_3460.txt
addmrpti.sql這個指令碼是生成其他資料庫的ADDM報告的！
[oracle@zfcs_web oracle]$ sqlplus / as sysdba @?/rdbms/admin/addmrpt.sql
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 2 11:30:52 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2325079448 NTDATA 1 ntdata
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 2325079448 1 NTDATA ntdata localhost.lo
caldomain
Using 2325079448 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 <return> 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
------------ ------------ --------- ------------------ -----
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 1
Snap
Instance 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 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 3450 --這裡輸入一個開始快照的值。
Begin Snapshot Id specified: 3450
Enter value for end_snap: 3460 --這裡輸入一個結束快照的值。
End Snapshot Id specified: 3460
Specify 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.txt
Running 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 3460
Database Time: 364 seconds
Average 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 operations
were 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 part
of 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" was
consuming 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" was
consuming 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 consuming
significant 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 analysis
period.
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.
End of Report
Report written to addmrpt_1_3450_3460.txt
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2141474/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ADDM: Automatic Database Diagnostic MonitorDatabase
- How to use the Automatic Database Diagnostic Monitor(一)Database
- 2 Day DBA-管理方案物件-監控和優化資料庫-效能自我診斷：自動資料庫診斷監控物件優化資料庫
- Oracle10g資料庫自動診斷監視工具(ADDM)使用指南Oracle資料庫
- Oracle ADDM 自動診斷監視工具 介紹Oracle
- Oracle ADDM 自動診斷監視工具介紹及使用Oracle
- 2 Day DBA-管理方案物件-監控和優化資料庫-使用ADDM診斷效能問題-手動執行ADDM物件優化資料庫
- Oracle ADDM --dbms_addm執行oracle資料庫診斷Oracle資料庫
- dbms_addm執行oracle資料庫診斷Oracle資料庫
- 從監控到診斷:資料的力量
- 自管理的資料庫:自動效能診斷資料庫
- mysql資料庫效能診斷MySql資料庫
- 【Oracle】資料庫hang 診斷Oracle資料庫
- Oracle配置資料庫診斷Oracle資料庫
- zabbix和mysql performance monitor模板實現mysql資料庫的監控MySqlORM資料庫
- 資料庫監控資料庫
- 資料庫診斷一例資料庫
- 使用zabbix自帶模板監控MySQL資料庫MySql資料庫
- 當資料庫遇上"自動駕駛"，阿里雲 DAS 在自治診斷的突破資料庫自動駕駛阿里
- 資料庫效能監控資料庫
- Verilog 監控 Monitor
- Automatic Diagnostic Repository (ADR)
- Automatic Diagnostic Repository (ADR) in Oracle Database 11g Release 1 (ADRCI)OracleDatabase
- oracle小知識點16-診斷事件diagnostic eventsOracle事件
- MySQL監控-Datadog資料庫監控調研MySql資料庫
- 資料庫繁忙程度監控資料庫
- 資料庫監控軟體資料庫
- SQL Server資料庫監控SQLServer資料庫
- 資料庫監控指令碼資料庫指令碼
- 監控Oracle資料庫方法Oracle資料庫
- 監控資料庫指令碼資料庫指令碼
- zabbix監控oracle資料庫Oracle資料庫
- 資料庫異常智慧分析與診斷資料庫
- ODX 診斷資料庫轉換工具 — DDC資料庫
- 診斷Oracle資料庫Hanging問題Oracle資料庫
- 9. Oracle常用分析診斷工具——9.3.ADDMOracle
- 11g_Automatic_Diagnostic_Repository
- O2O行業資料平臺實戰：從監控到診斷的資料產品搭建行業