DETAILED ADDM REPORT FOR TASK 'TASK_24611' WITH ID 24611
--------------------------------------------------------
Analysis Period: 03-FEB-2012 from 17:00:01 to 18:00:03
Database ID/Instance: 2785804221/1
Database/Instance Names: CPMIS/cpmis
Host Name: cpmisdb
Database Version: 10.2.0.4.0
Snapshot Range: from 14046 to 14047
Database Time: 57370 seconds
Average Database Load: 15.9 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 100% impact (72309 seconds)
--------------------------------------
Wait event "enq: DX - contention" in wait class "Other" was consuming
significant database time.
RECOMMENDATION 1: Application Analysis, 100% benefit (72309 seconds)
ACTION: Investigate the cause for high "enq: DX - contention" waits.
Refer to Oracle's "Database Reference" for the description of this
wait event.
RECOMMENDATION 2: Application Analysis, 100% benefit (72309 seconds)
ACTION: Investigate the cause for high "enq: DX - contention" waits in
Module "oracle@cpmisdb (TNS V1-V3)".
RECOMMENDATION 3: Application Analysis, 100% benefit (72309 seconds)
ACTION: Investigate the cause for high "enq: DX - contention" waits in
Service "SYS$USERS".
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Other" was consuming significant database time.
(100% impact [84371 seconds])
FINDING 2: 21% impact (12061 seconds)
-------------------------------------
Wait event "inactive transaction branch" in wait class "Other" was consuming
significant database time.
RECOMMENDATION 1: Application Analysis, 21% benefit (12061 seconds)
ACTION: Investigate the cause for high "inactive transaction branch"
waits. Refer to Oracle's "Database Reference" for the description of
this wait event.
RECOMMENDATION 2: Application Analysis, 21% benefit (12061 seconds)
ACTION: Investigate the cause for high "inactive transaction branch"
waits in Module "oracle@cpmisdb (TNS V1-V3)".
RECOMMENDATION 3: Application Analysis, 21% benefit (12061 seconds)
ACTION: Investigate the cause for high "inactive transaction branch"
waits in Service "SYS$USERS".
RECOMMENDATION 4: Application Analysis, 21% benefit (12061 seconds)
ACTION: Investigate the cause for high "inactive transaction branch"
waits with P1 ("branch#") value "0" and P2 ("waited") value "0".
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Other" was consuming significant database time.
(100% impact [84371 seconds])
FINDING 3: 17% impact (9993 seconds)
------------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 6.1% benefit (3517 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "gqj1krkj99bjq". 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 gqj1krkj99bjq
begin PROC_JOB_EVERYDAY_AM1; end;
RECOMMENDATION 2: SQL Tuning, 6.1% benefit (3472 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"a501hatw1t0tw".
RELEVANT OBJECT: SQL statement with SQL_ID a501hatw1t0tw and
PLAN_HASH 1507504904
SELECT NVL(SUM(GETPROJTHISINVESTFIN_EX(ID,NULL,:B2 ,NULL,1,1)),0)
FROM (SELECT * FROM VPROJECT_ITEM_INVESTTYPE A WHERE (EXISTS (SELECT
1 FROM VINVESTFIN_NEW_EXTRA_THIS C WHERE C.PROJID=A.ID) OR
A.JIUQFLAG=1) AND A.FINAAREACODE=:B1 )
RATIONALE: SQL statement with SQL_ID "a501hatw1t0tw" was executed 2
times and had an average elapsed time of 1736 seconds.
RECOMMENDATION 3: SQL Tuning, 5.2% benefit (3004 seconds)
ACTION: Investigate the SQL statement with SQL_ID "93kn0vymv9tyf" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 93kn0vymv9tyf and
PLAN_HASH 618914065
delete from bmanu_product_reviews_total
RATIONALE: SQL statement with SQL_ID "93kn0vymv9tyf" was executed 115
times and had an average elapsed time of 26 seconds.
RATIONALE: Waiting for event "enq: TX - row lock contention" in wait
class "Application" accounted for 50% of the database time spent in
processing the SQL statement with SQL_ID "93kn0vymv9tyf".
FINDING 4: 5.2% impact (3003 seconds)
-------------------------------------
SQL statements were found waiting for row lock waits.
RECOMMENDATION 1: Application Analysis, 5.2% benefit (3003 seconds)
ACTION: Significant row contention was detected in the TABLE
"PROJECT_HB.BMANU_PRODUCT_REVIEWS_TOTAL" with object id 96925. Trace
the cause of row contention in the application logic using the given
blocked SQL.
RELEVANT OBJECT: database object with id 96925
RATIONALE: The SQL statement with SQL_ID "93kn0vymv9tyf" was blocked on
row locks.
RELEVANT OBJECT: SQL statement with SQL_ID 93kn0vymv9tyf
delete from bmanu_product_reviews_total
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Application" was consuming significant database
time. (5.2% impact [3004 seconds])
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ADDITIONAL INFORMATION
----------------------
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.
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.
Session connect and disconnect calls were not consuming significant database
time.
Hard parsing of SQL statements was 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.