Re: ASH report - missing blocking session info

From: Sriram Kumar <k.sriramkumar_at_gmail.com>
Date: Thu, 24 Jan 2013 09:44:05 +0530
Message-ID: <CAEq4C0dS5nUQ=knyACL9Gz8bHdWeFcXPQhnNwWKfmQR48yZKiw_at_mail.gmail.com>

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
>
>
>

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

Received on Thu Jan 24 2013 - 05:14:05 CET