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

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






--
http://www.freelists.org/webpage/oracle-l

Other related posts: