Re: BLOCKING_SESSION null in DBA_HIST_ACTIVE_SESS_HISTORY for enq: TX - row lock contention
- From: Marcin Przepiorowski <pioro1@xxxxxxxxx>
- To: Sreejith.Sreekantan@xxxxxxxxxx
- Date: Mon, 14 May 2012 08:13:42 +0100
On Mon, May 14, 2012 at 7:34 AM, Sreejith S Nair
<Sreejith.Sreekantan@xxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
