Re: ASH report - missing blocking session info

From: Vasu <vasudevanr_at_gmail.com>
Date: Thu, 24 Jan 2013 07:31:11 -0600
Message-ID: <CA+O6cL+cwCJhaNf9Be9fKESrXzQg5YVbXC5P-XW3N+uL8CG7xQ_at_mail.gmail.com>

Hi,
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 info..is that specific to RAC?!)

Thanks

On Wed, Jan 23, 2013 at 10:14 PM, Sriram Kumar <k.sriramkumar_at_gmail.com>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 <vasudevanr_at_gmail.com> wrote:
>
>> We have had perf-issues in our 11.2.0.3, 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
>>
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>

-- 
-Vasu


--
http://www.freelists.org/webpage/oracle-l

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