MIME-Version: 1.0
References: <CAEjw_fg5yfOWT2KOkYRn+pqkMhgRJC+cQnuSPO+Uz5s2m9DnBA@mail.gmail.com>
 <CAOVevU5ZH2_znOXdVLid445nC-c5vZRddoDSZ+tDMUPM1RKz7Q@mail.gmail.com>
In-Reply-To: <CAOVevU5ZH2_znOXdVLid445nC-c5vZRddoDSZ+tDMUPM1RKz7Q@mail.gmail.com>
From: Laurentiu Oprea <laurentiu.oprea06@gmail.com>
Date: Tue, 2 Feb 2021 07:49:20 +0200
Message-ID: <CA+riqSXW1K7X+B2wF-=tT0mxPncTRrtKt28KqBJrstnypb6TyQ@mail.gmail.com>
Subject: Re: Library lock issue
To: Sayan Malakshinov <xt.and.r@gmail.com>
Cc: Pap <oracle.developer35@gmail.com>, Oracle L <oracle-l@freelists.org>
Content-Type: multipart/alternative; boundary="000000000000c0f59b05ba540a65"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: laurentiu.oprea06@gmail.com
--000000000000c0f59b05ba540a65
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hello,

Additional to what has been mentioned, usually this issue is caused by the
fact that parallel slaves will not be able to join the execution because
they are not able to reproduce the same execution plan as the coordinator.
Dynamic Sampling is often one of the root causes for this issue, If DS is
not helping in your case you can create a spl patch for that sql with a
lower level of DS or even disable it : dynamic_sampling(0).

Good luck

=C3=8En lun., 1 feb. 2021 la 23:53, Sayan Malakshinov <xt.and.r@gmail.com> =
a
scris:

> Hi Pap,
>
> Have you tried to set parallel_force_local=3Dtrue? Your QC is on Node 3
> while your slaves are on Node 2:
> 1. Parallel queries usually work much better if they don't need to send
> data between nodes and fight for concurrent access to the same data;
> 2. You are using global temporary tables, so their data is private for
> your session and stored on the same node as your session. In case of
> parallel access to GTT, QC has to send also their segment info, so your
> slaves have to request data from it, ie from Node 3.
> 3. Also that means that node 2 have to parse your query too for your
> slaves (sometimes it even leads to more child cursors)
>
> On Mon, Feb 1, 2021 at 10:28 PM Pap <oracle.developer35@gmail.com> wrote:
>
>> Hello All, We are seeing some odd behaviour. Its version 12.1.0.2.0 of
>> oracle. And a small query(finishing in <1 minutes) which is executing in
>> parallel(2) is experiencing "library cache lock" and "cursor: pin S wait=
 on
>> X" between its own slaves. I mean to say the blocking session is appeari=
ng
>> as its own slave sessions. We have "parallel_degree_policy" set as MANUA=
L
>> in v$parametr. This query is running for different literals one after
>> another multiple times in a loop fashion. And all these samples logged i=
n
>> dba_hist_active_sess_history showing IN_PARSE as 'Y'. Dueto these waits =
the
>> overall execution time of the process is going beyond ~5hrs+. The CPU an=
d
>> IO waits as noted in sql monitor is very small. Wondering how parallel
>> slave processes of the same query are blocking each other during parsing
>> itself. Or are we hitting any bug in this version?
>>
>> Attached is the sql and its run time sql monitor. And all the tables use=
d
>> in this query are global temporary tables "on commit preserve row" types=
.
>>
>>
>>
>> Thanks And Regards
>>
>> Pap
>>
>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org
>

--000000000000c0f59b05ba540a65
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello,<div><br></div><div>Additional=C2=A0to what has been=
 mentioned, usually this issue is caused=C2=A0by the fact that parallel sla=
ves will not be able to join the execution because they are not able to rep=
roduce the same execution plan as the coordinator.=C2=A0</div><div>Dynamic =
Sampling is often one of the root causes for this issue, If DS is not helpi=
ng in your case you can create a spl patch for that sql with a lower level =
of DS or even disable it : dynamic_sampling(0).</div><div><br></div><div>Go=
od luck</div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D=
"gmail_attr">=C3=8En lun., 1 feb. 2021 la 23:53, Sayan Malakshinov &lt;<a h=
ref=3D"mailto:xt.and.r@gmail.com">xt.and.r@gmail.com</a>&gt; a scris:<br></=
div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bor=
der-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">Hi P=
ap,<div><br></div><div>Have you tried to set=C2=A0parallel_force_local=3Dtr=
ue? Your QC is on Node 3 while your slaves are on Node 2:</div><div>1. Para=
llel queries usually work much better if they don&#39;t need to send data b=
etween nodes and fight for concurrent access to the same data;</div><div>2.=
 You are using global temporary tables, so their data is private for your s=
ession and stored on the same node as your session. In case of parallel acc=
ess to GTT, QC has to send also their segment info, so your slaves have to =
request data from it, ie from Node 3.</div><div>3. Also that means that nod=
e 2 have to parse your query too for your slaves (sometimes it even leads t=
o more child cursors)</div></div><br><div class=3D"gmail_quote"><div dir=3D=
"ltr" class=3D"gmail_attr">On Mon, Feb 1, 2021 at 10:28 PM Pap &lt;<a href=
=3D"mailto:oracle.developer35@gmail.com" target=3D"_blank">oracle.developer=
35@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" styl=
e=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddin=
g-left:1ex"><div dir=3D"ltr"><p style=3D"font-family:&quot;Oracle Sans&quot=
;;color:rgb(85,90,98);font-size:14px;word-break:break-word;line-height:inhe=
rit;box-sizing:border-box;padding:0px;margin:3px 0px 14px;border:0px;vertic=
al-align:baseline;outline:0px;text-overflow:ellipsis;white-space:pre-wrap">=
</p><p style=3D"font-family:&quot;Oracle Sans&quot;;color:rgb(85,90,98);fon=
t-size:14px;word-break:break-word;line-height:inherit;box-sizing:border-box=
;padding:0px;margin:3px 0px 14px;border:0px;vertical-align:baseline;outline=
:0px;text-overflow:ellipsis;white-space:pre-wrap">Hello All, We are seeing =
some odd behaviour. Its version 12.1.0.2.0 of oracle. And a small query(fin=
ishing in &lt;1 minutes) which is executing in parallel(2) is experiencing =
&quot;library cache lock&quot; and &quot;cursor: pin S wait on X&quot; betw=
een its own slaves. I mean to say the blocking session is appearing as its =
own slave sessions. We have &quot;parallel_degree_policy&quot; set as MANUA=
L in v$parametr. This query is running for different literals one after ano=
ther multiple times in a loop fashion. And all these samples logged in dba_=
hist_active_sess_history showing IN_PARSE as &#39;Y&#39;. Dueto these waits=
 the overall execution time of the process is going beyond ~5hrs+. The CPU =
and IO waits as noted in sql monitor is very small. Wondering how parallel =
slave processes of the same query are blocking each other during parsing it=
self. Or are we hitting any bug in this version?<br></p><p style=3D"font-fa=
mily:&quot;Oracle Sans&quot;;color:rgb(85,90,98);font-size:14px;word-break:=
break-word;line-height:inherit;box-sizing:border-box;padding:0px;margin:3px=
 0px 14px;border:0px;vertical-align:baseline;outline:0px;text-overflow:elli=
psis;white-space:pre-wrap">Attached is the sql and its run time sql monitor=
. And all the tables used in this query are global temporary tables &quot;o=
n commit preserve row&quot; types.<br></p><p style=3D"font-family:&quot;Ora=
cle Sans&quot;;color:rgb(85,90,98);font-size:14px;word-break:break-word;lin=
e-height:inherit;box-sizing:border-box;padding:0px;margin:3px 0px 14px;bord=
er:0px;vertical-align:baseline;outline:0px;text-overflow:ellipsis;white-spa=
ce:pre-wrap"><br></p><p style=3D"font-family:&quot;Oracle Sans&quot;;color:=
rgb(85,90,98);font-size:14px;word-break:break-word;line-height:inherit;box-=
sizing:border-box;padding:0px;margin:3px 0px 14px;border:0px;vertical-align=
:baseline;outline:0px;text-overflow:ellipsis;white-space:pre-wrap"><br></p>=
<p style=3D"font-family:&quot;Oracle Sans&quot;;color:rgb(85,90,98);font-si=
ze:14px;word-break:break-word;line-height:inherit;box-sizing:border-box;pad=
ding:0px;margin:3px 0px 14px;border:0px;vertical-align:baseline;outline:0px=
;text-overflow:ellipsis;white-space:pre-wrap">Thanks And Regards</p><p styl=
e=3D"font-family:&quot;Oracle Sans&quot;;color:rgb(85,90,98);font-size:14px=
;word-break:break-word;line-height:inherit;box-sizing:border-box;padding:0p=
x;margin:3px 0px 14px;border:0px;vertical-align:baseline;outline:0px;text-o=
verflow:ellipsis;white-space:pre-wrap">Pap</p></div>
</blockquote></div><br clear=3D"all"><div><br></div>-- <br><div dir=3D"ltr"=
><div dir=3D"ltr"><div><div dir=3D"ltr"><div>Best regards,<br>Sayan Malaksh=
inov</div><span style=3D"font-size:13.6px">Oracle performance tuning engine=
er</span><br style=3D"font-size:13.6px"><div>Oracle ACE Associate<br><a hre=
f=3D"http://orasql.org" target=3D"_blank">http://orasql.org</a></div></div>=
</div></div></div>
</blockquote></div>

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