BLOCKING_SESSION null in DBA_HIST_ACTIVE_SESS_HISTORY for enq: TX - row lock contention

From: Sreejith S Nair <Sreejith.Sreekantan_at_ibsplc.com>
Date: Mon, 14 May 2012 12:04:20 +0530
Message-ID: <OFB2C2246A.DF9DC646-ON652579FE.00235378-652579FE.00241A65_at_ibsplc.com>



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_at_ibsplc.com
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."

--

http://www.freelists.org/webpage/oracle-l Received on Mon May 14 2012 - 01:34:20 CDT

Original text of this message