Re: 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 15:28:05 +0530
Message-ID: <OFF377F7CC.4BD5718C-ON652579FE.0027E7BA-652579FE.0036C1CC_at_ibsplc.com>



Thank you Marcin.
Sorry for not including my db version. Db runs in 11.2.0.2 on Solaris 10. I am sure that these sessions were waiting, but blocking details did not come.

SQL> SELECT

  2  A.SESSION_ID,
  3  A.SESSION_SERIAL#,
  4  A.SAMPLE_TIME,

A.SESSION_STATE,
  5 6 A.EVENT,
  7  A.TIME_WAITED/1000/1000 WAIT_SECS,
  8  A.PROGRAM  ,
  9  A.BLOCKING_SESSION,
 10  A.BLOCKING_SESSION_SERIAL#

 11 FROM DBA_HIST_ACTIVE_SESS_HISTORY A  12 WHERE A.SQL_ID='&1'
 13 ORDER BY A.SAMPLE_TIME ASC;
Enter value for 1: cur1h59565sjv
old 12: WHERE A.SQL_ID='&1'
new 12: WHERE A.SQL_ID='cur1h59565sjv'
SESSION_ID SESSION_SERIAL# SAMPLE_TIME                    SESSION_ST EVENT 
                          WAIT_SECS PROGRAM BLOCKING_SESSION 
BLOCKING_SESSION_SERIAL#
---------- --------------- ------------------------------ ---------- 
------------------------------ ---------- ------------------------------ 
---------------- ------------------------
       136            1195 14-MAY-12 12.33.27.293 AM      WAITING    enq: 
TX - row lock contention           0 JDBC Thin Client
       136            1195 14-MAY-12 12.33.37.298 AM      WAITING    enq: 
TX - row lock contention           0 JDBC Thin Client
         129              53 14-MAY-12 12.34.51.955 AM      WAITING enq: 
TX - row lock contention 8.753382 JDBC Thin Client

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



From:   Marcin Przepiorowski <pioro1_at_gmail.com>
To:     Sreejith.Sreekantan_at_ibsplc.com
Cc:     oracle-l_at_freelists.org
Date:   05/14/2012 12:42 PM
Subject:        Re: BLOCKING_SESSION null in DBA_HIST_ACTIVE_SESS_HISTORY 
for enq: TX - row lock contention



On Mon, May 14, 2012 at 7:34 AM, Sreejith S Nair
<Sreejith.Sreekantan_at_ibsplc.com> wrote:

> 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;
> ...
> 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 ?
> Hi Sreejith You can't use sum(time_waited) on SAMPLED data - both views v$active_session_history and dba_hist_active_sess_history have sampled rows (1 s for ASH and 1/10 of ASH data is transferred dba_hist_.....). Instead of sum(time_waited) you should use count(*), ex. select sql_id, event, count(*) from v$active_session_history where sample_time > sysdate - 1/24 and session_state = 'WAITING' group by sql_id, event; Next question is what version of Oracle are you using ? For most of 10.2 and a few of 11.1 event column was not cleared even if session was on cpu and session_state was equal to 'ON CPU'. It has been changed at some point but I can't recall exact version. So if you want to be sure that you are looking for waiting session only add this condition session_state = 'WAITING'. It is possible that it is your case - session was on cpu and this is why blocking session was set to null. There is very good presentation about using ASH by Graham Wood - http://www.oracle.com/technetwork/database/focus-areas/manageability/ppt-active-session-history-129612.pdf -- Marcin Przepiorowski http://oracleprof.blogspot.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 - 04:58:05 CDT

Original text of this message