Re: ASH report - missing blocking session info

From: Vasu <>
Date: Thu, 24 Jan 2013 07:31:11 -0600
Message-ID: <>

Its enq TM contention , I was embarrassed to say that as there are UnIndexed FKs and we are cleaning them up. So the issue may go away soon, but my intention/wish is to understand the lock behavior like which program/session and the DML that is causing it (so I can reproduce it, hence specifically interested in the session/program info).

The prev.question was : Since dba_hist_A.S.H is made of (in)frequent "sampling" that just represents the state of the system at a point in time(and won't provide all the details) , capturing all the lock specific data in real-time may be the best option. Wanted to hear from folks who tried DBA_HIST_A.S.H and their in that scenario (missing blocker session that specific to RAC?!)


On Wed, Jan 23, 2013 at 10:14 PM, Sriram Kumar <>wrote:

> Hi,
> What is the exact enqueue event?. you could possibly look at
> dba_hist_seg_stat for the sample interval and identify the segment with
> high enqueue and then query dab_hist_sqlstat for the SQL's containing the
> table for the DML for the same sample interval.
> best regards
> sriram kumar
> On Thu, Jan 24, 2013 at 8:43 AM, Vasu <> wrote:
>> We have had perf-issues in our, Rac DB a while ago. As a
>> post-mortem started looking into DBA_HIST_ACTIVE_SESS_HISTORY and ASH
>> report for that period.
>> The scenario is : A session on instance-1 was blocked by a session from
>> instance-2 (blocker) for nearly 40 minutes,
>> For the blocked session on instance-1, dba_hist_active_sess_history has
>> 300+ records, a record for every 30 sec approximately ,
>> with all records having the same info : Waiting on enq wait, with blocking
>> sesssion info :sid,serial# ,instance and status GLOBAL. (which is OK).
>> Wow..I thought I was making a good progress, only a record away from the
>> culprit.. but the blocker info is missing in DBA_HIST_ACTIVE_SESS_HISTORY,
>> and ash report too . :(
>> Quick glance of the 1st article thru google said 'only active sessions are
>> sampled'.. so is it possible the blocker was idle after some DMLs (without
>> issuing a commit)?
>> OR has any one come across 'missing session info ' for valid cases or
>> possibly a bug?
>> Thanks
>> Vasu
>> --


Received on Thu Jan 24 2013 - 14:31:11 CET

Original text of this message