Re: BLOCKING_SESSION null in DBA_HIST_ACTIVE_SESS_HISTORY for enq: TX - row lock contention
Date: Mon, 14 May 2012 08:13:42 +0100
Message-ID: <CAGdek=xbcYyEQ+MGGqECeY31=3ZOuN7py5kPW26XKQeXpAnBWg_at_mail.gmail.com>
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 -- http://www.freelists.org/webpage/oracle-lReceived on Mon May 14 2012 - 02:13:42 CDT