Re: Wait: Reliable message P1: Channel context
From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Mon, 15 May 2023 20:45:54 -0400
Message-ID: <CAMHX9J+FQ=1N3PkEAm0Rcm91fCOVS=gJkeKbBRMtaVTsdqr-LA_at_mail.gmail.com>
Date: Mon, 15 May 2023 20:45:54 -0400
Message-ID: <CAMHX9J+FQ=1N3PkEAm0Rcm91fCOVS=gJkeKbBRMtaVTsdqr-LA_at_mail.gmail.com>
Note that the easiest may be to just list what was your session doing at the time when it hit this wait. The blocking_session is usually populated both for "reliable message" wait events (and also "local write wait" events).
Something like this:
_at_ashtop
program2,top_level_call_name,sql_opname,event2,blocking_session_status,blocking_session
"event like 'reliable%'" sysdate-1/24 sysdate
And then see who the blocking SID was/is. Both the SQL_OPNAME and the blocking session would give you the next clues.
-- Tanel Poder https://learn.tanelpoder.com On Mon, May 15, 2023 at 8:14 PM Tanel Poder <tanel_at_tanelpoder.com> wrote:Received on Tue May 16 2023 - 02:45:54 CEST
> I have another video (in addition to what Stefan posted) about channels
> and background process communication and even how to trace reliable message
> activity between processes (using X$TRACE):
>
> - https://www.youtube.com/watch?v=mkmvZv58W6w&t=2701s
>
> As Jonathan said, these reliable message waits are usually a result of
> some higher level activity (too frequent posting of CKPT process due to
> direct path reads or truncate/drop operations, where CKPT falls behind,
> etc).
>
> v$channel_waits may give you an idea of which communication channel types
> have the most waits (but doesn't expose which sessions/communication
> endpoints or which exact channel cause the most waits).
>
> I also have a channels.sql script (that uses x$) that lists sessions
> involved in the communication and shows the channel memory address
> (context_ptr). The output is wider than what I pasted here:
>
> SQL> *_at_channels context_ptr=HEXTORAW('00000000BCE49368')*
>
> CHANNEL_NAME DESCR
> FLAGS MESG_COUNT SID
> ----------------------------------------------------------------
> ---------- ----------- ---------- ----------
> CKPT ksbxic channel XC01
> SUB 0 236
>
> SQL> _at_channels 1=1
>
> CHANNEL_NAME DESCR
> FLAGS MESG_COUNT SID
> ----------------------------------------------------------------
> ---------- ----------- ---------- ----------
> ACL propagation channel sacl
> SUB 0 7
> AQ post/wait channel 1
> PUB 0 -1
> AQ post/wait channel 1
> PUB 0 -1
> AQ post/wait channel 1
> PUB 0 707
> AQ post/wait channel 1
> PUB 0 -1
> CKPT ksbxic channel XC01
> SUB 0 236
> File Extent Array Channel
> SUB 10 700
> GEN0 ksbxic channel XC07
> SUB 0 464
> Get Segment Information Channel
> SUB 0 700
> ...
>
>
>
> On Mon, May 15, 2023 at 4:55 AM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> Is the time spent significant or is this just curiosity.
>>
>> You've probably got all the information you need from the video that
>> Stefan referenced, but if not I can add that I've seen this event creeping
>> up the "Top 10" a couple of times, but I think it's always been a side
>> effect of something much higher up the list.
>>
>> One case was an application that kept dropping and creating tables (when
>> it could have used GTTs or even WITH subqueries in most cases); the others
>> were RAC systems where there was far too much chatter across the
>> interconnect all the time, with hundreds or thousands of gc cr and gc cu
>> requests and blocks hammering back and forth all the time. One of them was
>> doing a huge number of direct path tablescans with a huge demand for undo
>> blocks to get read-consistent results.
>>
>> A query like the following will give you the channel name from the p1
>> value:
>>
>> select
>> ctx.waitcount_ksrcctx,
>> ctx.waittime_ksrcctx,
>> nam.name_ksrcdes
>> from
>> x$ksrcctx ctx,
>> x$ksrcdes nam
>> where
>> ctx.addr = '000000008C4BA208' -- your p1 left padded to 16
>> characters with 0
>> and nam.indx = ctx.name_ksrcctx
>> /
>>
>>
>> Regards
>> Jonathan Lewis
>>
>>
>> On Mon, 15 May 2023 at 02:13, Jack van Zanen <jack_at_vanzanen.com> wrote:
>>
>>> Hi All
>>>
>>>
>>> I am trying to find out what the wait
>>> Reliable message with P1 channel context is. I have tried google but no
>>> dice
>>> One of my sessions seem to encounter it a fair bit
>>>
>>> Oracle 12.2.01
>>>
>>>
>>> Thank you
>>>
>>> Jack van Zanen
>>>
>>>
>>> -------------------------
>>> This e-mail and any attachments may contain confidential material for
>>> the sole use of the intended recipient. If you are not the intended
>>> recipient, please be aware that any disclosure, copying, distribution or
>>> use of this e-mail or any attachment is prohibited. If you have received
>>> this e-mail in error, please contact the sender and delete all copies.
>>> Thank you for your cooperation
>>>
>>
-- http://www.freelists.org/webpage/oracle-l