BLOCKING_SESSION null in DBA_HIST_ACTIVE_SESS_HISTORY for enq: TX - row lock contention
- From: Sreejith S Nair <Sreejith.Sreekantan@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 14 May 2012 12:04:20 +0530
Listers,
I am trying to troubleshoot a performance issue in one of our database,
then I happened to see this situation.
SQL> SELECT * FROM (SELECT
2 A.INSTANCE_NUMBER,
3 A.SQL_ID,
4 A.EVENT,
5 SUM(A.TIME_WAITED),
A.PROGRAM
6 7 FROM DBA_HIST_ACTIVE_SESS_HISTORY A,DBA_OBJECTS D
8 WHERE A.SNAP_ID BETWEEN 3437 AND 3438
9 AND A.CURRENT_OBJ#=D.OBJECT_ID
10 GROUP BY A.INSTANCE_NUMBER,A.SQL_ID,A.EVENT,A.PROGRAM
11 ORDER BY 4 DESC)
12 WHERE ROWNUM < 26;
INSTANCE_NUMBER SQL_ID EVENT SUM(A.TIME_WAITED) PROGRAM
--------------- --------------- ------------------------------
------------------ ------------------------------
2 cur1h59565sjv enq: TX - row lock contention 476046055
JDBC Thin Client
1 cur1h59565sjv enq: TX - row lock contention 467932849
JDBC Thin Client
1 41mm9y6p1wzsf enq: TX - row lock contention 417594238
JDBC Thin Client
2 41mm9y6p1wzsf enq: TX - row lock contention 297323843
JDBC Thin Client
2 direct path write 4613158 JDBC Thin Client
Top wait event being enq: TX - row lock contention, I looked for blocking
sessions. But I could not find any value in BLOCKING_SESSION. It is coming
as NULL.
SQL> SELECT * FROM (SELECT
2 A.INSTANCE_NUMBER,
3 A.SQL_ID,
A.EVENT,
4 5 SUM(A.TIME_WAITED),
6 A.PROGRAM ,
7 A.BLOCKING_SESSION,
8 A.BLOCKING_SESSION_SERIAL#
9 FROM DBA_HIST_ACTIVE_SESS_HISTORY A,DBA_OBJECTS D
10 WHERE A.SNAP_ID BETWEEN 3437 AND 3438
AND A.CURRENT_OBJ#=D.OBJECT_ID
11 12 AND A.SQL_ID='&1'
13 GROUP BY
A.INSTANCE_NUMBER,A.SQL_ID,A.EVENT,A.PROGRAM,A.BLOCKING_SESSION,A.BLOCKING_SESSION_SERIAL#
ORDER BY 4 DESC)
14 15 WHERE ROWNUM < 26;
Enter value for 1: cur1h59565sjv
old 12: AND A.SQL_ID='&1'
new 12: AND A.SQL_ID='cur1h59565sjv'
INSTANCE_NUMBER SQL_ID EVENT SUM(A.TIME_WAITED) PROGRAM
BLOCKING_SESSION BLOCKING_SESSION_SERIAL#
--------------- --------------- ------------------------------
------------------ ------------------------------ ----------------
2 cur1h59565sjv enq: TX - row lock contention 476046055
JDBC Thin Client
1 cur1h59565sjv enq: TX - row lock contention 467932849
JDBC Thin Client
2 cur1h59565sjv gc cr block busy 3622 JDBC Thin Client
Can someone please tell me in which case BLOCKING_SESSION will come as
NULL for event enq: TX - row lock contention ?
On another context, If I am able to see SAMPLE_TIME field in
DBA_HIST_ACTIVE_SESS_HISTORY for a particular session,sql_id and event for
continuos samples eg:
Suppose If I see like this
14-MAY-12 12.33.27.293 AM
14-MAY-12 12.33.37.293 AM
14-MAY-12 12.33.47.293 AM
14-MAY-12 12.33.57.293 AM
14-MAY-12 12.34.07.293 AM
14-MAY-12 12.34.17.293 AM
14-MAY-12 12.34.27.293 AM
Here I know that session was WAITING for 1 minute, but If I sum up
TIME_WAITED column which is in microseconds, I am not arriving at value
derived from sample time ( 1 minute ).
Did any one noticed this or Am I expecting something wrong ?
With Regards,
Sreejith
--
Sreejith S Nair
Associate Systems Architect | AOS DBA Team
IBS Software Services Private Ltd.
2nd Floor, IBS Campus, Technopark Campus, Trivandrum - 695 581, Kerala,
India
((Direct) +91 471 661 4707 ) +91 808 648 5523
*sreejith.sreekantan@xxxxxxxxxx
8 www.ibsplc.com
DISCLAIMER:
"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."
--
//www.freelists.org/webpage/oracle-l
Other related posts:
