Dears,
One of my friends asked me to give him a help on analyzing a performance problem they are experimenting from time to time. First of all I am sorry to tell you that he didn’t gave me a lot of information. I will share with you all what I have been sent and would like to have your precious advice according to the available information.
It is a third party software installed on oracle data base 10.2.0.4.0. He said that from time to time the application hangs. He sends me an AWR reports supplied by the local DBA. Unfortunately, the local DBA send only the AWR information he thinks are most important to be looked at (he did not include the load profile and the instance efficiency Percentages part in this AWR for example).
Here below is the available information I can share with you
Snap time sessions cursors/session
Begin Snap 09-mars-11 08:00:03 31 6.5
End Snap 09-mars-11 08:39:49 41 9.8
Elapsed 39.77 (mins)
DB Time 536.65(mins)
Top 5 Timed Events
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
enq: TX - row lock contention 8,468 25,344 2,993 78.7 Application
read by other session 714,628 4,604 6 14.3 User I/O
db file sequential read 323,264 1,977 6 6.1 User I/O
CPU time 171 .5
db file scattered read 1,885 49 26 .2 User I/O
The AWR reports presents also the following SQL statement as the top SQL of its ‘’SQL ordered by Elapsed Time’’ part
UPDATE xxxx.table1
SET col1 = :1,
col2 = :2,
col3 = :3,
col4 = :4,
….
WHERE ID = :13
AND colx = :14;
And the following sql at its “SQL ordered by Reads’ part
SELECT t1.*
FROM xxxx.table1 t1
LEFT OUTER JOIN xxxx.table2 t2
ON t1.id = t2.id
LEFT OUTER JOIN xxxx.table3 t3
ON t1.id = t3.id
LEFT OUTER JOIN xxxx.table4 t4
ON t1.id = t4.id
WHERE t1.col1 = :1
AND t1.col2 IN (:2,:3, :4);
And finally in the Segments by Row Lock Waits part of the AWR I have this:
Owner tablespace name object name object type row lock waits %of capture
xxxx xxxx table1 TABLE 54 100
There are no bitmap indexes on this OLTP data base. There are no unindexed foreign keys and it seems that there are no selects done over a dblink.
I am waiting to have the table table1 script and its indexes
Given those information I think that the locking problem is due to this update on table table1 which is not followed immediately by a commit or a rollback. But, instead, several selects that might be taking a long time are done before reaching the commit that ends the lock on the table table1
What do you think about the possible reason of this lock?
Thanks in advance
Mohamed Houri