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.
A.SESSION_STATE,
5 6 A.EVENT,
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'
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_SESSIONBLOCKING_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:Received on Mon May 14 2012 - 04:58:05 CDT
> 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