ADDM報告分析
以下為一份addm報告，檢視該報告，可以得到addm給我們的建議，addm和awr一樣，每份addm報告的格式基本一樣，只是內容不同而已。
$ cat addmrpt_1_20314_20317.txt
第一部分是指出收集ADDM的時間段
ADDM Report for Task 'TASK_62667'
---------------------------------
Analysis Period
---------------
AWR snapshot range from 20314 to 20317.
Time period starts at 24-DEC-13 09.00.01 AM
Time period ends at 24-DEC-13 11.06.35 AM
第二部分是庫的基本資訊，主要包括版本，dbid
Analysis Target
---------------
Database 'PMSESBDB' with DB ID 1256960331.
Database version 11.1.0.6.0.
ADDM performed an analysis of instance ESBP1, numbered 1 and hosted at
PMSEBPD1.
第三部分列出db_time
Activity During the Analysis Period
-----------------------------------
Total database time was 402 seconds.
The average number of active sessions was .05.
第四部分是ADDM給出的一個大概的統計建議
Summary of Findings
-------------------
Description Active Sessions Recommendations
Percent of Activity
------------------------------ ------------------- ---------------
1 Virtual Memory Paging .05 | 100 3
2 Top SQL by DB Time .02 | 37.8 5
3 Top SQL by "Cluster" Wait .01 | 16.51 5
4 Commits and Rollbacks 0 | 6.91 2
5 Buffer Busy 0 | 5.58 0
6 Interconnect Buffer Busy 0 | 4.49 1
7 Unusual "Other" Wait Event 0 | 4.01 1
8 PL/SQL Compilation 0 | 3.07 1
9 Hard Parse Due to Parse Errors 0 | 2.34 1
10 Session Connect and Disconnect 0 | 2.22 1
備註：這裡由於庫很閒，所以這裡的百分比都不大，虛擬記憶體換頁也才5%，topsql的dbtime為2%也不高。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
第五部分是ADDM的詳解建議
Findings and Recommendations
----------------------------
Finding 1: Virtual Memory Paging----------建議1：肯定要從第一項說起，這裡第一個就是虛擬記憶體換頁。
Impact is .05 active sessions, 100% of total activity.
------------------------------------------------------
Significant virtual memory paging was detected on the host operating system.
Recommendation 1: Host Configuration--這是給出的建議，讓調整主機資訊。
Estimated benefit is .05 active sessions, 100% of total activity.
-----------------------------------------------------------------
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.
Recommendation 2: Database Configuration
Estimated benefit is .05 active sessions, 100% of total activity.
-----------------------------------------------------------------
Action
Consider enabling Automatic Shared Memory Management by setting the
parameter "sga_target" to control the amount of SGA consumed by this
instance.--讓設定sga_target
Recommendation 3: Database Configuration
Estimated benefit is .05 active sessions, 100% of total activity.
-----------------------------------------------------------------
Action
Consider enabling Automatic PGA Memory Management by setting the
parameter "pga_aggregate_target" to control the amount of PGA consumed
by this instance.--讓設定pga_aggregate_target
Finding 2: Top SQL by DB Time-----建議2：給出topsql，這裡會將sql直接列出，比較直觀
Impact is .02 active sessions, 37.8% of total activity.
-------------------------------------------------------
SQL statements consuming significant database time were found.
Recommendation 1: SQL Tuning
Estimated benefit is .01 active sessions, 15.27% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "1rswbxwhbpmr7".
Related Object
SQL statement with SQL_ID 1rswbxwhbpmr7 and PLAN_HASH 876628497.
select decode(bitand(a.flags, 16384), 0, a.next_run_date,
a.last_enabled_tim..........................省略
Rationale
SQL statement with SQL_ID "1rswbxwhbpmr7" was executed 287 times and had
an average elapsed time of 0.21 seconds.
Recommendation 2: SQL Tuning
Estimated benefit is 0 active sessions, 7.6% of total activity.
---------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "6xfsagzmrr54f".
Related Object
SQL statement with SQL_ID 6xfsagzmrr54f and PLAN_HASH 587169518.
select * from..........................省略
Action
Investigate the SQL statement with SQL_ID "6xfsagzmrr54f" for possible
performance improvements.
Related Object
SQL statement with SQL_ID 6xfsagzmrr54f and PLAN_HASH 587169518.
select * from ..........................省略
Rationale
SQL statement with SQL_ID "6xfsagzmrr54f" was executed 1 times and had
an average elapsed time of 26 seconds.
Recommendation 3: SQL Tuning
Estimated benefit is 0 active sessions, 5.41% of total activity.
----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "6ssrk2dqj7jbx".
Related Object
SQL statement with SQL_ID 6ssrk2dqj7jbx and PLAN_HASH 3447323253.
select ..........................省略
Rationale
SQL statement with SQL_ID "6ssrk2dqj7jbx" was executed 704 times and had
an average elapsed time of 0.03 seconds.
Recommendation 4: SQL Tuning
Estimated benefit is 0 active sessions, 5.19% of total activity.
----------------------------------------------------------------
Action
Tune the PL/SQL block with SQL_ID "5471z2mmaf89k". Refer to the "Tuning
PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and--讓看書自己調優
Reference".
Related Object
SQL statement with SQL_ID 5471z2mmaf89k.
BEGIN ..........................省略
Rationale
SQL statement with SQL_ID "5471z2mmaf89k" was executed 83 times and had
an average elapsed time of 0.27 seconds.
Recommendation 5: SQL Tuning
Estimated benefit is 0 active sessions, 5.17% of total activity.
----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "18naypzfmabd6".
Related Object
SQL statement with SQL_ID 18naypzfmabd6.
INSERT INTO ..........................省略
Rationale
SQL statement with SQL_ID "18naypzfmabd6" was executed 513 times and had
an average elapsed time of 0.04 seconds.
Finding 3: Top SQL by "Cluster" Wait---給出一些發生記憶體間傳遞的sql
Impact is .01 active sessions, 16.51% of total activity.
--------------------------------------------------------
SQL statements responsible for significant inter-instance messaging were
found.
Recommendation 1: SQL Tuning
Estimated benefit is 0 active sessions, 7.6% of total activity.
---------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "6xfsagzmrr54f".
Related Object
SQL statement with SQL_ID 6xfsagzmrr54f and PLAN_HASH 587169518.
select * from ..........................省略
Action
Investigate the SQL statement with SQL_ID "6xfsagzmrr54f" for possible
performance improvements.
Related Object
SQL statement with SQL_ID 6xfsagzmrr54f and PLAN_HASH 587169518.
select * from..........................省略
Rationale
SQL statement with SQL_ID "6xfsagzmrr54f" was executed 1 times and had
an average elapsed time of 26 seconds.
Rationale
Average time spent in Cluster wait events per execution was 2.8 seconds.
Recommendation 2: SQL Tuning
Estimated benefit is 0 active sessions, 5.41% of total activity.
----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "6ssrk2dqj7jbx".
Related Object
SQL statement with SQL_ID 6ssrk2dqj7jbx and PLAN_HASH 3447323253.
select ..........................省略
Rationale
SQL statement with SQL_ID "6ssrk2dqj7jbx" was executed 704 times and had
an average elapsed time of 0.03 seconds.
Rationale
Average time spent in Cluster wait events per execution was 0.03
seconds.
Recommendation 3: SQL Tuning
Estimated benefit is 0 active sessions, 5.17% of total activity.
----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "18naypzfmabd6".
Related Object
SQL statement with SQL_ID 18naypzfmabd6.
INSERT INTO ..........................省略
Rationale
SQL statement with SQL_ID "18naypzfmabd6" was executed 513 times and had
an average elapsed time of 0.04 seconds.
Rationale
Average time spent in Cluster wait events per execution was 0.04
seconds.
Recommendation 4: SQL Tuning
Estimated benefit is 0 active sessions, 5.14% of total activity.
----------------------------------------------------------------
Action
Tune the PL/SQL block with SQL_ID "5471z2mmaf89k". Refer to the "Tuning
PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and
Reference".
Related Object
SQL statement with SQL_ID 5471z2mmaf89k.
BEGIN ..........................省略
Rationale
SQL statement with SQL_ID "5471z2mmaf89k" was executed 83 times and had
an average elapsed time of 0.27 seconds.
Rationale
Average time spent in Cluster wait events per execution was 0.24
seconds.
Recommendation 5: SQL Tuning
Estimated benefit is 0 active sessions, .16% of total activity.
---------------------------------------------------------------
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".
Related Object
SQL statement with SQL_ID 6gvch1xu9ca3g.
DECLARE ..........................省略
Rationale
SQL statement with SQL_ID "6gvch1xu9ca3g" was executed 90 times and had
an average elapsed time of 0.13 seconds.
Rationale
Average time spent in Cluster wait events per execution was 0.0071
seconds.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Cluster" was consuming significant database time.
Impact is 0 active sessions, 7.31% of total activity.
Finding 4: Commits and Rollbacks----發現4，提交和回退
Impact is 0 active sessions, 6.91% of total activity.有6.9%的dbtime消耗在此，會產生logfilesync等待事件。
-----------------------------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.
Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 6.91% of total activity.
----------------------------------------------------------------
Action
Investigate application logic for possible reduction in the number of
COMMIT operations by increasing the size of transactions.
Rationale
The application was performing 31 transactions per minute with an
average redo size of 10064 bytes per transaction.
Recommendation 2: Host Configuration
Estimated benefit is 0 active sessions, 6.91% of total activity.
----------------------------------------------------------------
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 4 milliseconds.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Commit" was consuming significant database time.
Impact is 0 active sessions, 6.91% of total activity.
Finding 5: Buffer Busy
Impact is 0 active sessions, 5.58% of total activity.
-----------------------------------------------------
Read and write contention on database blocks was consuming significant
database time. However, no single object was the predominant cause for this
contention.
No recommendations are available.
Symptoms That Led to the Finding:
---------------------------------
Inter-instance messaging was consuming significant database time on this
instance.
Impact is 0 active sessions, 7.29% of total activity.
Wait class "Cluster" was consuming significant database time.
Impact is 0 active sessions, 7.31% of total activity.
Finding 6: Interconnect Buffer Busy
Impact is 0 active sessions, 4.49% of total activity.
-----------------------------------------------------
Read and write contention on database blocks was consuming significant
database time in the cluster.
Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 4.49% of total activity.
----------------------------------------------------------------
Action
Verify that the set of services used by the application to connect to
the database are optimally distributed if response time is critical.
Symptoms That Led to the Finding:
---------------------------------
Inter-instance messaging was consuming significant database time on this
instance.
Impact is 0 active sessions, 7.29% of total activity.
Wait class "Cluster" was consuming significant database time.
Impact is 0 active sessions, 7.31% of total activity.
Finding 7: Unusual "Other" Wait Event
Impact is 0 active sessions, 4.01% of total activity.
-----------------------------------------------------
Wait event "reliable message" in wait class "Other" was consuming significant
database time.
Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 4.01% of total activity.
----------------------------------------------------------------
Action
Investigate the cause for high "reliable message" waits. Refer to
Oracle's "Database Reference" for the description of this wait event.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Other" was consuming significant database time.
Impact is 0 active sessions, 7.78% of total activity.
Finding 8: PL/SQL Compilation
Impact is 0 active sessions, 3.07% of total activity.
-----------------------------------------------------
PL/SQL compilation consumed significant database time.
Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 3.07% of total activity.
----------------------------------------------------------------
Action
Investigate the appropriateness of PL/SQL compilation. PL/SQL
compilation can be caused by DDL on dependent objects.
Finding 9: Hard Parse Due to Parse Errors
Impact is 0 active sessions, 2.34% of total activity.
-----------------------------------------------------
Hard parsing SQL statements that encountered parse errors was consuming
significant database time.
Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 2.34% of total activity.
----------------------------------------------------------------
Action
Investigate application logic to eliminate parse errors.
Symptoms That Led to the Finding:
---------------------------------
Hard parsing of SQL statements was consuming significant database time.
Impact is 0 active sessions, 3.99% of total activity.
Finding 10: Session Connect and Disconnect
Impact is 0 active sessions, 2.22% of total activity.
-----------------------------------------------------
Session connect and disconnect calls were consuming significant database time.
Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 2.22% of total activity.
----------------------------------------------------------------
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.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
CPU was not a bottleneck for the instance.
Wait class "Network" was not consuming significant database time.
Wait class "User I/O" was not consuming significant database time.
The network latency of the cluster interconnect was within acceptable limits
of 1 milliseconds.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/223653/viewspace-2123953/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ADDM
- 9. Oracle常用分析診斷工具——9.3.ADDMOracle
- statspack、awr、addm,ash影片分享
- 2022愛分析· RPA廠商全景報告 | 愛分析報告
- 2022愛分析 · DataOps廠商全景報告 | 愛分析報告
- 2023愛分析·大模型廠商全景報告｜愛分析報告大模型
- 2022愛分析· 流程挖掘廠商全景報告 | 愛分析報告
- 2023愛分析 · 元宇宙廠商全景報告 | 愛分析報告元宇宙
- 2022愛分析· 信創廠商全景報告 | 愛分析報告
- TruSSH Worm分析報告Worm
- python分析文字報告Python
- 2023愛分析 · 認知智慧廠商全景報告 | 愛分析報告
- 2022愛分析･智慧客服廠商全景報告 | 愛分析報告
- 2022愛分析･智慧決策廠商全景報告 | 愛分析報告
- 2022愛分析· 隱私計算廠商全景報告 | 愛分析報告
- 2022愛分析･資料庫廠商全景報告 | 愛分析報告資料庫
- 2022愛分析･智慧園區廠商全景報告 | 愛分析報告
- HBS：實時分析報告
- Sodinoki樣本分析報告
- Kong mesh深度分析報告
- 2022愛分析· 虛擬化活動廠商全景報告 | 愛分析報告
- 2022愛分析･採購數字化廠商全景報告 | 愛分析報告
- Redis後門植入分析報告Redis
- Joomla 物件注入漏洞分析報告OOM物件
- 黑狐”木馬分析報告
- Trojan.OceanLotus.UrsuA分析報告
- capital許可使用報告分析API
- 原神深度分析報告（上）
- 德勤報告：情報分析的未來
- 馬蜂窩：旅途中APP使用行為分析報告（附報告）APP
- Tower與DevCloud對比分析報告devCloud
- Win32/Angryel病毒分析報告Win32
- 全球Google Play市場分析報告Go
- 事後諸葛亮分析報告
- 2022愛分析･出海數字化系列報告之“出海實時互動與通訊”廠商全景報告 | 愛分析報告
- 如何看懂DOE分析報告？這篇文章告訴你
- 資料包告 | 非洲電商市場分析報告
- PHBS：2024年第三季度粵港澳大灣區經濟分析報告分析報告
- 2022愛分析･消費品零售數字化廠商全景報告 | 愛分析報告