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> @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@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:
-
https://blog.tanelpoder.com/posts/log-file-switch-checkpoint-incomplete-and-lgwr-waiting-for-checkpoint-progress/
--
Tanel Poder
https://blog.tanelpoder.com/seminar/
On Tue, Jun 11, 2019 at 5:37 AM Noveljic Nenad <nenad.noveljic@xxxxxxxxxxxx>
wrote:
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
