Need help with this ADDM Report.
I have an ADDM report, Most of the findings in the report are clear, but the First Finding in this report is the most important one for me. I have to work on the User I/O, pls see Finding 1 (its the first one below in the report).
Need your help on this.
This is a 3 Node RAC Database on 10.2.0.4 on RHEL 5
--------------------------------------------------------------------------------------------------
Analysis Period: 19-AUG-2009 from 10:00:15 to 12:00:06
Database ID/Instance: xxxxx
Database/Instance Names: xxxx/xxxx1
Host Name: xxxx1.xxxxx.com
Database Version: 10.2.0.4.0
Snapshot Range: from 178 to 180
Database Time: 1917 seconds
Average Database Load: .3 active sessions
---------------------------------------------------------------------------------------------------
FINDING 1: 31% impact (591 seconds)
-----------------------------------
Wait class "User I/O" was consuming significant database time.
NO RECOMMENDATIONS AVAILABLE
ADDITIONAL INFORMATION:
The throughput of the I/O subsystem was not significantly lower than
expected.
The SGA was adequately sized.
FINDING 2: 27% impact (516 seconds)
-----------------------------------
Time spent on the CPU by the instance was responsible for a substantial part
of database time.
RECOMMENDATION 1: SQL Tuning, 12% benefit (234 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"0pjvqxzywsmmv".
RELEVANT OBJECT: SQL statement with SQL_ID 0pjvqxzywsmmv and
PLAN_HASH 2383920675
SELECT c.cma_cont_id, c.cont_shrt_dn, c.cont_dn, c.ttl_list_id,
CMAgetListInfo(c.cma_cont_id) listType, c.isbn_id, c.ttl_id,
c.cust_spec_cont_ind, CMAContChilds(c.cma_cont_id, :1, 'none')
moreLink FROM cma_cont c, cma_cg_cont a, cma_cont_srvc_lvl cv,
cma_cont_mkt_sgmt cm WHERE c.cma_cont_id = a.cma_cont_id AND
c.cma_cont_id = cm.cma_cont_id AND c.cma_cont_id = cv.cma_cont_id AND
a.cma_cg_tp_id = 2 AND a.cma_cont_tp_id IN (8, 9, 10, 11) AND
cm.cma_cont_ownr_id = 0 AND cv.aplc_svc_lvl_nbr = 1 AND
((cm.mkt_sgmt_cd = :2 AND c.cma_cont_actv_ind = 'Y' AND c.strt_dt = SYSDATE) or c.cma_cont_id = :3) ORDER BY
cm.mkt_sgmt_sort_seq
RATIONALE: SQL statement with SQL_ID "0pjvqxzywsmmv" was executed 873
times and had an average elapsed time of 0.26 seconds.
RATIONALE: Average CPU used per execution was 0.26 seconds.
RECOMMENDATION 2: Application Analysis, 12% benefit (228 seconds)
ACTION: Parsing SQL statements were consuming significant CPU. Please
refer to other findings in this task about parsing for further
details.
RECOMMENDATION 3: SQL Tuning, 2.8% benefit (54 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"ggqrugu0xn4kq".
RELEVANT OBJECT: SQL statement with SQL_ID ggqrugu0xn4kq and
PLAN_HASH 2215635973
SELECT c.cont_shrt_dn heading, c.cont_dn, c.cma_cont_id, c.prod_id
prod_Id, c.ttl_list_id list_id, CMAgetImage(c.cma_cont_id)
imge_id,c.wbpg_loc_path_dn url, c.wbpg_dn tag,
CMAgetListInfo(c.cma_cont_id) listType,c.cma_cont_tp_id type,
c.ttl_id, c.imge_loc_path_dn alt_text,CMAContChilds(c.cma_cont_id, 0,
'none') moreLink FROM cma_cont c, cma_cont_srvc_lvl t,
cma_cont_mkt_sgmt s, cma_cg_cont cg, cont_rltn r, cma_cont_ad a WHERE
c.cma_cont_id = t.cma_cont_id AND c.cma_cont_id = s.cma_cont_id AND
c.cma_cont_id = cg.cma_cont_id AND c.cma_cont_id = r.cma_cont_id AND
c.cma_cont_id = a.cma_cont_id AND r.cma_cont_ownr_id = 0 AND
(s.mkt_sgmt_cd = :1 AND a.cma_cg_tp_id = :2 AND c.cma_cont_actv_ind =
'Y' AND ((c.strt_dt SYSDATE)) OR c.cma_cont_id = :3) AND
t.aplc_svc_lvl_nbr = :4 AND cg.cma_cont_tp_id = :5 ORDER BY
s.mkt_sgmt_sort_seq, cg.cma_cont_tp_id desc, c.que_ind, c.cma_cont_id
RATIONALE: SQL statement with SQL_ID "ggqrugu0xn4kq" was executed 157
times and had an average elapsed time of 0.34 seconds.
RATIONALE: Average CPU used per execution was 0.34 seconds.
FINDING 3: 15% impact (288 seconds)
-----------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 12% benefit (234 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"0pjvqxzywsmmv".
RELEVANT OBJECT: SQL statement with SQL_ID 0pjvqxzywsmmv and
PLAN_HASH 2383920675
SELECT c.cma_cont_id, c.cont_shrt_dn, c.cont_dn, c.ttl_list_id,
CMAgetListInfo(c.cma_cont_id) listType, c.isbn_id, c.ttl_id,
c.cust_spec_cont_ind, CMAContChilds(c.cma_cont_id, :1, 'none')
moreLink FROM cma_cont c, cma_cg_cont a, cma_cont_srvc_lvl cv,
cma_cont_mkt_sgmt cm WHERE c.cma_cont_id = a.cma_cont_id AND
c.cma_cont_id = cm.cma_cont_id AND c.cma_cont_id = cv.cma_cont_id AND
a.cma_cg_tp_id = 2 AND a.cma_cont_tp_id IN (8, 9, 10, 11) AND
cm.cma_cont_ownr_id = 0 AND cv.aplc_svc_lvl_nbr = 1 AND
((cm.mkt_sgmt_cd = :2 AND c.cma_cont_actv_ind = 'Y' AND c.strt_dt = SYSDATE) or c.cma_cont_id = :3) ORDER BY
cm.mkt_sgmt_sort_seq
RATIONALE: SQL statement with SQL_ID "0pjvqxzywsmmv" was executed 873
times and had an average elapsed time of 0.26 seconds.
RECOMMENDATION 2: SQL Tuning, 9.8% benefit (187 seconds)
ACTION: Use bigger fetch arrays while fetching results from the SELECT
statement with SQL_ID "0pjvqxzywsmmv".
RELEVANT OBJECT: SQL statement with SQL_ID 0pjvqxzywsmmv and
PLAN_HASH 2383920675
SELECT c.cma_cont_id, c.cont_shrt_dn, c.cont_dn, c.ttl_list_id,
CMAgetListInfo(c.cma_cont_id) listType, c.isbn_id, c.ttl_id,
c.cust_spec_cont_ind, CMAContChilds(c.cma_cont_id, :1, 'none')
moreLink FROM cma_cont c, cma_cg_cont a, cma_cont_srvc_lvl cv,
cma_cont_mkt_sgmt cm WHERE c.cma_cont_id = a.cma_cont_id AND
c.cma_cont_id = cm.cma_cont_id AND c.cma_cont_id = cv.cma_cont_id AND
a.cma_cg_tp_id = 2 AND a.cma_cont_tp_id IN (8, 9, 10, 11) AND
cm.cma_cont_ownr_id = 0 AND cv.aplc_svc_lvl_nbr = 1 AND
((cm.mkt_sgmt_cd = :2 AND c.cma_cont_actv_ind = 'Y' AND c.strt_dt = SYSDATE) or c.cma_cont_id = :3) ORDER BY
cm.mkt_sgmt_sort_seq
RECOMMENDATION 3: SQL Tuning, 2.8% benefit (54 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"ggqrugu0xn4kq".
RELEVANT OBJECT: SQL statement with SQL_ID ggqrugu0xn4kq and
PLAN_HASH 2215635973
SELECT c.cont_shrt_dn heading, c.cont_dn, c.cma_cont_id, c.prod_id
prod_Id, c.ttl_list_id list_id, CMAgetImage(c.cma_cont_id)
imge_id,c.wbpg_loc_path_dn url, c.wbpg_dn tag,
CMAgetListInfo(c.cma_cont_id) listType,c.cma_cont_tp_id type,
c.ttl_id, c.imge_loc_path_dn alt_text,CMAContChilds(c.cma_cont_id, 0,
'none') moreLink FROM cma_cont c, cma_cont_srvc_lvl t,
cma_cont_mkt_sgmt s, cma_cg_cont cg, cont_rltn r, cma_cont_ad a WHERE
c.cma_cont_id = t.cma_cont_id AND c.cma_cont_id = s.cma_cont_id AND
c.cma_cont_id = cg.cma_cont_id AND c.cma_cont_id = r.cma_cont_id AND
c.cma_cont_id = a.cma_cont_id AND r.cma_cont_ownr_id = 0 AND
(s.mkt_sgmt_cd = :1 AND a.cma_cg_tp_id = :2 AND c.cma_cont_actv_ind =
'Y' AND ((c.strt_dt SYSDATE)) OR c.cma_cont_id = :3) AND
t.aplc_svc_lvl_nbr = :4 AND cg.cma_cont_tp_id = :5 ORDER BY
s.mkt_sgmt_sort_seq, cg.cma_cont_tp_id desc, c.que_ind, c.cma_cont_id
RATIONALE: SQL statement with SQL_ID "ggqrugu0xn4kq" was executed 157
times and had an average elapsed time of 0.34 seconds.
RECOMMENDATION 4: SQL Tuning, 2.3% benefit (43 seconds)
ACTION: Use bigger fetch arrays while fetching results from the SELECT
statement with SQL_ID "ggqrugu0xn4kq".
RELEVANT OBJECT: SQL statement with SQL_ID ggqrugu0xn4kq and
PLAN_HASH 2215635973
SELECT c.cont_shrt_dn heading, c.cont_dn, c.cma_cont_id, c.prod_id
prod_Id, c.ttl_list_id list_id, CMAgetImage(c.cma_cont_id)
imge_id,c.wbpg_loc_path_dn url, c.wbpg_dn tag,
CMAgetListInfo(c.cma_cont_id) listType,c.cma_cont_tp_id type,
c.ttl_id, c.imge_loc_path_dn alt_text,CMAContChilds(c.cma_cont_id, 0,
'none') moreLink FROM cma_cont c, cma_cont_srvc_lvl t,
cma_cont_mkt_sgmt s, cma_cg_cont cg, cont_rltn r, cma_cont_ad a WHERE
c.cma_cont_id = t.cma_cont_id AND c.cma_cont_id = s.cma_cont_id AND
c.cma_cont_id = cg.cma_cont_id AND c.cma_cont_id = r.cma_cont_id AND
c.cma_cont_id = a.cma_cont_id AND r.cma_cont_ownr_id = 0 AND
(s.mkt_sgmt_cd = :1 AND a.cma_cg_tp_id = :2 AND c.cma_cont_actv_ind =
'Y' AND ((c.strt_dt SYSDATE)) OR c.cma_cont_id = :3) AND
t.aplc_svc_lvl_nbr = :4 AND cg.cma_cont_tp_id = :5 ORDER BY
s.mkt_sgmt_sort_seq, cg.cma_cont_tp_id desc, c.que_ind, c.cma_cont_id
FINDING 4: 8.3% impact (158 seconds)
------------------------------------
Wait event "gc cr disk read" in wait class "Cluster" was consuming significant
database time.
RECOMMENDATION 1: Application Analysis, 8.3% benefit (158 seconds)
ACTION: Investigate the cause for high "gc cr disk read" waits. Refer to
Oracle's "Database Reference" for the description of this wait event.
Use given SQL for further investigation.
RATIONALE: The SQL statement with SQL_ID "25qg9z8cvkcmc" was found
waiting for "gc cr disk read" wait event.
RELEVANT OBJECT: SQL statement with SQL_ID 25qg9z8cvkcmc
Analyze Table EB.PROD
Estimate Statistics
Sample 25 Percent
RATIONALE: The SQL statement with SQL_ID "ayad7870304mk" was found
waiting for "gc cr disk read" wait event.
RELEVANT OBJECT: SQL statement with SQL_ID ayad7870304mk
RECOMMENDATION 2: Application Analysis, 8.3% benefit (158 seconds)
ACTION: Investigate the cause for high "gc cr disk read" waits in Module
"TOAD background query session".
RECOMMENDATION 3: Application Analysis, 8.3% benefit (158 seconds)
ACTION: Investigate the cause for high "gc cr disk read" waits in
Service "xxx.xxxxx.com".
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Cluster" was consuming significant database time.
(9.5% impact [182 seconds])
FINDING 5: 6.6% impact (127 seconds)
------------------------------------
Cursors were getting invalidated due to DDL operations. This resulted in
additional hard parses which were consuming significant database time.
RECOMMENDATION 1: Application Analysis, 6.6% benefit (127 seconds)
ACTION: Investigate appropriateness of DDL operations.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Hard parsing of SQL statements was consuming significant
database time. (12% impact [235 seconds])
FINDING 6: 5.4% impact (103 seconds)
------------------------------------
SQL statements were not shared due to the usage of literals. This resulted in
additional hard parses which were consuming significant database time.
RECOMMENDATION 1: Application Analysis, 5.4% benefit (103 seconds)
ACTION: Investigate application logic for possible use of bind variables
instead of literals.
ACTION: Alternatively, you may set the parameter "cursor_sharing" to
"force".
RATIONALE: At least 13 SQL statements with PLAN_HASH_VALUE 1177726922
were found to be using literals. Look in V$SQL for examples of such
SQL statements.
RATIONALE: At least 6 SQL statements with PLAN_HASH_VALUE 785131581 were
found to be using literals. Look in V$SQL for examples of such SQL
statements.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Hard parsing of SQL statements was consuming significant
database time. (12% impact [235 seconds])
FINDING 7: 3.9% impact (74 seconds)
-----------------------------------
Wait class "Other" was consuming significant database time.
NO RECOMMENDATIONS AVAILABLE
ADDITIONAL INFORMATION:
Database latches in the "Other" wait class were not consuming
significant database time.
FINDING 8: 3.2% impact (61 seconds)
-----------------------------------
Wait event "Data file init write" in wait class "User I/O" was consuming
significant database time.
RECOMMENDATION 1: Application Analysis, 3.2% benefit (61 seconds)
ACTION: Investigate the cause for high "Data file init write" waits.
Refer to Oracle's "Database Reference" for the description of this
wait event.
RECOMMENDATION 2: Application Analysis, 3.2% benefit (61 seconds)
ACTION: Investigate the cause for high "Data file init write" waits in
Module "Admin Connection".
RECOMMENDATION 3: Application Analysis, 3.2% benefit (61 seconds)
ACTION: Investigate the cause for high "Data file init write" waits in
Service "SYS$USERS".
RECOMMENDATION 4: Application Analysis, 3.2% benefit (61 seconds)
ACTION: Investigate the cause for high "Data file init write" waits with
P1,P2,P3 ("count, intr, timeout") values "1", "32" and "2147483647"
respectively.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(31% impact [591 seconds])
INFO: The throughput of the I/O subsystem was not significantly lower
than expected.
The SGA was adequately sized.
FINDING 9: 2% impact (39 seconds)
---------------------------------
The PGA was inadequately sized, causing additional I/O to temporary
tablespaces to consume significant database time.
NO RECOMMENDATIONS AVAILABLE
ADDITIONAL INFORMATION:
The value of parameter "pga_aggregate_target" was "10240 M" during the
analysis period.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(31% impact [591 seconds])
INFO: The throughput of the I/O subsystem was not significantly lower
than expected.
The SGA was adequately sized.
FINDING 10: 1.2% impact (24 seconds)
------------------------------------
Inter-instance messaging was consuming significant database time on this
instance.
NO RECOMMENDATIONS AVAILABLE
ADDITIONAL INFORMATION:
The network latency of the cluster interconnect was within acceptable
limits of 1 milliseconds.
Read and write contention on database blocks was not consuming
significant database time in the cluster.
Global Cache Service Processes (LMSn) in other instances were performing
within acceptable limits of 1 milliseconds.
Waits on "buffer busy" events were not consuming significant database
time.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Cluster" was consuming significant database time.
(9.5% impact [182 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.
Session connect and disconnect calls were 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.
An explanation of the terminology used in this report is available when you
run the report with the 'ALL' level of detail.