Re: gv$active_session_history.blocking_session
Date: Tue, 11 Jun 2019 11:45:11 -0400
Message-ID: <CAMHX9J+0w=5Ln3sXR1Dj9bKM7OQo7i0Ly7sk86etU-rfS=w5eQ_at_mail.gmail.com>
As ASH captures only active samples, you can have a scenario when your session is blocked by someone that holds some resource/lock/etc, while being idle itself. So, you'll be blocked by a session that happens to be idle at the moment of the ASH sample capture.
Download the latest ash_wait_chains (v0.4), it says "idle blocker" in such scenario. Your SID 736 seems to be LGWR.
Last year I documented one scenario where everyone were waiting for LGWR, but LGWR itself was not in a synchronous blocking wait, but rather in a "poll-sleep-retry" style wait (waiting for DBWR to catch up before reusing a previous redolog). And that caused the following wait chains:
SQL> _at_ash/ash_wait_chains program2||event2 1=1 sysdate-1/24/12 sysdate
- Display ASH Wait Chain Signatures script v0.4 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This SECONDS AAS WAIT_CHAIN
- ---------- -------
39% 1665 5.6 -> (JDBC Thin Client) log file switch (checkpoint incomplete) -> *[idle blocker 1,250,30651 (oracle_at_oel7.localdomain (LGWR))] * 21% 910 3.0 -> (JDBC Thin Client) log file switch (checkpoint incomplete) -> (LGWR) enq: CF - contention -> (CKPT) direct path write
7% 283 .9 -> (DBWn) db file parallel write
5% 203 .7 -> (PSPn) ON CPU
5% 201 .7 -> (JDBC Thin Client) db file sequential read
...
The full blog entry is here:
-- Tanel Poder https://blog.tanelpoder.com/seminar/ On Tue, Jun 11, 2019 at 5:37 AM Noveljic Nenad <nenad.noveljic_at_vontobel.com> wrote:Received on Tue Jun 11 2019 - 17:45:11 CEST
> The session 2231 had 49 samples in the observed time period:
>
>
>
> select event,blocking_session,count(*)
>
> from gv$active_session_history where session_id = 2231
>
> and sample_time between
>
> to_date('11.06.2019 07:08:30','dd.mm.yyyy hh24:mi:ss') and
>
> to_date('11.06.2019 07:09:20','dd.mm.yyyy hh24:mi:ss')
>
> group by event,blocking_session order by count(*) desc
>
> ;
>
>
>
> EVENT
>
> ----------------------------------------------------------------
>
> BLOCKING_SESSION COUNT(*)
>
> ---------------- ----------
>
> log file switch (private strand flush incomplete)
>
> 736 49
>
>
>
> As we can see, the session was solely waiting on "log file switch (private
> strand flush incomplete)" and was blocked by the session 736.
>
>
>
> But, only 12 samples were captured for the blocker during the same time
> period:
>
>
>
> select count(*)
>
> from gv$active_session_history where session_id = 736
>
> and sample_time between
>
> to_date('11.06.2019 07:08:30','dd.mm.yyyy hh24:mi:ss') and
>
> to_date('11.06.2019 07:09:20','dd.mm.yyyy hh24:mi:ss')
>
> ;
>
>
>
> COUNT(*)
>
> ----------
>
> 12
>
>
>
-- http://www.freelists.org/webpage/oracle-l