admin管理员组文章数量:1566659
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本文标签： 数据库ADDMAutomaticMonitorDiagnostic
版权声明：本文标题：自动数据库诊断监控 ADDM（Automatic Database Diagnostic Monitor）！ 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：https://www.elefans.com/xitong/1725722670a1038567.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论