question about the ash_wait_chains script

[slavadba]

Hi Tanel,

I have a question about the ash_wait_chains script.

https://drive.google.com/file/d/1hkvCbWN-5ofCv04rFubc_uISiCF4dWma/view?usp=sharing - the full output that it returned, and the picture there is as follows: session 433 has blocked many other sessions, and it is shown as idle. But just below there is this line

-> 433***3bk7zs1qb22p6 - > [idle blocker 1,2020,18565]

 i.e. it turns out that session 433  is blocked by session 2020.  But why is this not shown in the chains for session 433?  i.e. in my opinion, it should have looked something like this  

 -> 2641***7aspktw7mn6w8 -> 3077***68t8xu5nfmw41 - >433***some_sql_id_or_null - > [idle blocker 1,2020,18565]
 -> 136***7aspktw7mn6w8 -> 3077***68t8xu5nfmw41 -> 433***some_sql_id_or_null - > [idle blocker 1,2020,18565]

   - and so on in the chains where 433 is set as the final blocker. Or its not possible for some reason?

With regards, Vyasheslav

[tanelpoder]

Hi Vyasheslav,

It would be useful to also see the program2||event2 columns to understand which process types and which events were involved in the blocking.

The ash_wait_chains script walks thought the waiting session chains one ASH sample at a time. So, depending on the nature of your application or the process types involved, session A may be blocking session B 50% of the time, but session B blocking session A the remaining 50% of time (or whatever the percentages).

You can zoom in to a single ASH sample (by selecting a very narrow time range) and you shouldn't see this pattern anymore.

There's one special case that I don't report yet: cyclic waits (that end up as deadlocks). That would also show a related pattern where during a single sample, A waits for B and B waits for A (until the deadlock detection forces a rollback for one session). I already use Oracle's CONNECT_BY_ISCYCLE in the script, but I currently don't print it.