Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-aa.freelists.org (smtp-aa.freelists.org [23.23.80.81]) by malta2546.startdedicated.com (Postfix) with ESMTPS id 11FB41002D9EF0 for ; Tue, 2 Feb 2021 10:57:21 +0100 (CET) Received: from turing.freelists.org (ip-10-0-0-164.ec2.internal [10.0.0.164]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by smtp-aa.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id BA76844842; Tue, 2 Feb 2021 09:57:19 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id B19B33F85E; Tue, 2 Feb 2021 09:57:19 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612259839; bh=9Fyj3d8eWL2pIKjGItXD6j9Ogy18fIV0uhQ5H2yjpc0=; h=From:Sender:Sender:From; b=lUDoj9WUz/rmvAbqJ6ZnzKSv/2yZC18Enc9XC7Y8MIAL6O/nCRf68ksm8w4kz/xjI wKjujljn2/mZeoj/b3QaxKXBjsSx09MdP2WoO0g5ud4Tp2j8aZRgcEFJGz7UTfxCKr 4B+cGxmSReA94H4c8mfavwblTkkeamERLXCxfoK4= X-Virus-Scanned: by FreeLists at turing2.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id AgKnBKZOhoJf; Tue, 2 Feb 2021 09:57:19 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 7F1693F970; Tue, 2 Feb 2021 09:57:17 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612259838; bh=9Fyj3d8eWL2pIKjGItXD6j9Ogy18fIV0uhQ5H2yjpc0=; h=From:Sender:Sender:From; b=wZENwsD5wU5hUq7iwfSiVJHovMxE5pZdMm9vFNDteyRSNxyIYPmOkorCbYNo+yX+M kZw8oAoPLue4Zn1cOFk1f9MmnPAo0ci4w7xcxx3F8jvhhYXUMa1QwBla8Wgb0i3hgN 23sQ2t04o6fGB4eic3wFLsbuaWTmCILfomPaFAAk= Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 02 Feb 2021 09:57:16 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 24D6F3F85E for ; Tue, 2 Feb 2021 09:57:16 +0000 (UTC) Authentication-Results: turing.freelists.org; dkim=pass (2048-bit key; unprotected) header.d=gmail.com header.i=@gmail.com header.a=rsa-sha256 header.s=20161025 header.b=mQXgQ4Mz; dkim-atps=neutral Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 0Xl4Cin1fpSs for ; Tue, 2 Feb 2021 09:57:16 +0000 (UTC) Received: from mail-ua1-f45.google.com (mail-ua1-f45.google.com [209.85.222.45]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id 027F93F844 for ; Tue, 2 Feb 2021 09:57:15 +0000 (UTC) Received: by mail-ua1-f45.google.com with SMTP id a16so6900640uad.9 for ; Tue, 02 Feb 2021 01:57:15 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=JQEgAI0CQ7aPwo0PC5E+D4ZMpXPe4UrALD0CJjT5TEQ=; b=tCiX4FF3RN71AYe41qRh4Wc2NzFwrULN0CblMjCHKKMBhDKECVFkfB0QQ5Tx2mR0ZS 21r8iOcJ51pa7UE01wkQRSJTxv5tJTZwxcAWrxHywLYaNmpNtTUPDgytYTpxJOLSGLyE HmLuR8RiPanz2VhpIl8mseS3BUOhopv/Yi4LmZZIxtFC7U5+6mZG+NmRPbr3w1COrGbP 4HfPmeJH/J+sOsHYTZQ/XfqbC9Pzcon9sLQ8wtWWXQCQ/SGSyXZmQnUf1L7hH/iF+PE0 thP43UTJo5QOXfy6aUKSqX2Es5r+P9SEzEz8TscrB/PlYbtqDNhArtNDe2D/pI9iV/zI elxg== X-Gm-Message-State: AOAM532inoloyrhFaVNsxXOzkTaEExoyFfmrGUW/tvk6jgftIDZH1Am8 rJlnHOL2BU7v+jDgF89mXAd5L897RWoLgzgIK4o= X-Google-Smtp-Source: ABdhPJzajVidEZB9j0/EHtAT8UvzxXXzT+lUarkloNzW+IQTN6MK18yksEKaYYkujXHgAmMX7OvZfjzr/KXyjI1CojM= X-Received: by 2002:ab0:6511:: with SMTP id w17mr376295uam.17.1612259835436; Tue, 02 Feb 2021 01:57:15 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Pap Date: Tue, 2 Feb 2021 15:27:03 +0530 Message-ID: Subject: Re: Library lock issue To: Mohamed Houri Cc: Chinar Aliyev , Laurentiu Oprea , Sayan Malakshinov , Oracle L Content-Type: multipart/alternative; boundary="000000000000c6e1e105ba577ee7" X-archive-position: 78819 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: oracle.developer35@gmail.com Precedence: normal Reply-To: oracle.developer35@gmail.com List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-Subscribe: List-Owner: List-post: List-Archive: X-list: oracle-l --000000000000c6e1e105ba577ee7 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thanks much. For that query , I see the below note section. And as i mentioned are using private session level stats collected for two of the global temporary tables but one global temp table is having null stats and so perhaps for that the dynamic sampling is triggered. In our case we are not using bind variables in this query but literals. But anyway , I am planning to set the paralle_force_local to TRUE in the session level and along with that we will collect the stats on all the three tables setting all of them to use private session level stats , hopefully that will also make optimizer get rid of the dynamic sampling part. We can't set the dynamic sampling level to zero for this query because we are many times getting a bad execution path and to help fix that we need accurate stats , so I think private session level stats for all three tables is the one to go for in our case. Correct me if wrong. Also can you please share the exact bug which you are talking of. Note ----- - dynamic statistics used: dynamic sampling (level=3D2) Regards Pap On Tue, Feb 2, 2021 at 2:32 PM Mohamed Houri wrote: > > I have faced a similar 12cR1 issue of parallel slave (PX) refusing to > share the execution plan of their query coordinator (QC) despite both QC > and its PX slaves were in the same instance. It turned to be a kind of bu= g > because of the usage of bind variable (yes using bind variables !!) and > dynamic sampling. > > But here you are using literals and GTT with private statistics. Bear in > mind that when you are using session private statistics Oracle will not > propagate GTT private statistics from one session to another one. It > achieves this by invalidating similar cursor between sessions. > > > https://hourim.wordpress.com/2019/07/30/global-temporary-table-private-st= atistic > > > > That=E2=80=99s said I haven=E2=80=99t tested how Oracle will consider the= QC coordinator > session per regards to its PX slave sessions in case of private GTT > sessions. > > You can see if your execution plan has the following Note signaling that > it has used a GTT private statistics or not > > Note > > ----- > > -- Global temporary table session private statistics used > > > > And use Tanel Poder nonshared sql script to get the reason for which PX > slaves are refusing the share the execution plan of their QC > > > Best Regards > > Mohamed Houri > > Le mar. 2 f=C3=A9vr. 2021 =C3=A0 09:42, Chinar Aliyev a > =C3=A9crit : > >> Hi, >> There are several bugs in Oracle Support and might one of them be >> appropriate for your case. >> >> It happens when QC and PX Slaves are allocated in different instances, >> and a remote slave has to parse the statement based on the info sent by >> QC (to achieve the same execution plan generated by QC). >> To reduce parsing you can force it as Sayan has mentioned. Also, check >> support notes/bugs. >> >> Best Regards >> >> On Tue, Feb 2, 2021 at 11:12 AM Pap wrote= : >> >>> Thanks a lot. >>> >>> We will definitely try rerunning by setting session level >>> "parallel_force_local" parameter to true as we set this value as 'FALSE= ' in >>> v$parameter. >>> >>> *"parallel slaves will not be able to join the execution because they >>> are not able to reproduce the same execution plan as the coordinator. "= * >>> >>> I am a bit confused with above statement on the dynamic sampling part, = I >>> see out of three global temporary tables used in this query , two of th= em >>> are using private session level stats as it's gathered inside the code.= But >>> one(RTNI) is having stats set as NULL and also its locked , which means= the >>> dynamic sampling must have been triggered for that table only. But in t= hat >>> case too, i am not able to understand how the dynamic sampling can be t= he >>> cause, can you please explain bit more. As because , my understanding i= s >>> parsing will happen at the first stage and till that time parallel slav= es >>> wont get involve in real execution. So as the table related info will b= e >>> available in the node-3 as that being the session of the query >>> coordinator/parent session , so that should only do the parsing work. >>> Please correct me if wrong. >>> >>> Regards >>> Pap >>> >>> On Tue, Feb 2, 2021 at 11:20 AM Laurentiu Oprea < >>> laurentiu.oprea06@gmail.com> wrote: >>> >>>> 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 wi= th 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 >>>> 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 d= ata; >>>>> 2. You are using global temporary tables, so their data is private fo= r >>>>> 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 yo= ur >>>>> 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 >>>>> 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 execu= ting 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 app= earing >>>>>> as its own slave sessions. We have "parallel_degree_policy" set as M= ANUAL >>>>>> in v$parametr. This query is running for different literals one afte= r >>>>>> another multiple times in a loop fashion. And all these samples logg= ed in >>>>>> dba_hist_active_sess_history showing IN_PARSE as 'Y'. Dueto these wa= its the >>>>>> overall execution time of the process is going beyond ~5hrs+. The CP= U and >>>>>> IO waits as noted in sql monitor is very small. Wondering how parall= el >>>>>> slave processes of the same query are blocking each other during par= sing >>>>>> itself. Or are we hitting any bug in this version? >>>>>> >>>>>> Attached is the sql and its run time sql monitor. And all the tables >>>>>> used in this query are global temporary tables "on commit preserve r= ow" >>>>>> types. >>>>>> >>>>>> >>>>>> >>>>>> Thanks And Regards >>>>>> >>>>>> Pap >>>>>> >>>>> >>>>> >>>>> -- >>>>> Best regards, >>>>> Sayan Malakshinov >>>>> Oracle performance tuning engineer >>>>> Oracle ACE Associate >>>>> http://orasql.org >>>>> >>>> >> >> -- >> *Chinar Aliyev* >> >> >> Visit My :Blog >> Let's Connect - *= Linkedin >> Profile * >> >> My Twitter - ChinarAliyev >> >> >> > > -- > > Houri Mohamed > > Oracle DBA-Developer-Performance & Tuning > > Visit My - Blog > > Let's Connect - *L= inkedin > Profile * > > My Twitter - MohamedHouri > > > --000000000000c6e1e105ba577ee7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks much.

For that query = , I see the below note section. And as i mentioned are using private sessio= n level stats collected for two of the global temporary tables but one glob= al temp table is having null stats and so perhaps for that the dynamic samp= ling is triggered.=20 In our case we are not using bind variables in this query but literals. But= anyway , I am planning to set the paralle_force_local to TRUE in the sessi= on level and along with that we will collect the stats on all the three tab= les setting all of them to use private session level stats , hopefully that= will also make optimizer=C2=A0get rid of the dynamic sampling part.=C2=A0<= /div>

We can't set the dynamic sampling level to zer= o for this query because we are many times getting a bad execution path and= to help fix that we need accurate stats , so I think private session level= stats for all three tables is the one to go for in our case. Correct me if= wrong.

Also can you please share the exact bug wh= ich you are talking of.=C2=A0


Note

-----

=C2= =A0=C2=A0- dynamic statistics used: dynamic sampling (level=3D2)


Regards

Pap


On= Tue, Feb 2, 2021 at 2:32 PM Mohamed Houri <mohamed.houri@gmail.com> wrote:


I have faced a similar= 12cR1 issue of parallel slave (PX) refusing to share the execution plan of their query coordinator (QC) despit= e both QC and its PX slaves were in the same instance. It turned to be a kind= of bug because of the usage of bind variable (yes using bind variables !!) and dyna= mic sampling.

=C2=A0But here you are using literals and GTT with private statistics. Bear in mind that when = you are using session private statistics Oracle will not propagate GTT private statistics from one session to another one. It achieves this by invalidatin= g similar cursor between sessions.

https://hourim.wordpress.com/2019/07/30/global-temporary-table= -private-statistic

<= br>

That=E2=80=99s said I haven=E2=80=99t tested h= ow Oracle will consider the QC coordinator session per regards to its PX slave sessions in case of private GTT sessions.

You can see if your execution plan has the fol= lowing Note signaling that it has used a GTT private statistics or not

Note

----- <= /span>

-- Glob= al temporary table session private statistics used

=C2=A0

And use Tanel Poder nonshared sql script to ge= t the reason for which PX slaves are refusing the share the execution plan of the= ir QC


Best Regards

Mohamed Houri


Le=C2=A0mar. 2 f=C3= =A9vr. 2021 =C3=A0=C2=A009:42, Chinar Aliyev <chinaraliyev@gmail.com> a =C3=A9cr= it=C2=A0:
H= i,
There are several bugs in Oracle Support and might one of them be approp= riate for your case.

It happens=C2=A0when QC and PX Slaves are allocat= ed in different instances, and a remote=C2=A0slave has to parse the stateme= nt based on the info=C2=A0sent=C2=A0by QC (to achieve the same=C2=A0execution plan generated by = QC).=C2=A0
To reduce parsing you can force it as Sayan=C2=A0has= mentioned. Also, check support=C2=A0notes/bugs.

Best Regards=C2=A0

On Tue, Feb 2, 2021 at 11:12 AM Pap <<= a href=3D"mailto:oracle.developer35@gmail.com" target=3D"_blank">oracle.dev= eloper35@gmail.com> wrote:
Thanks a lot.

<= /div>
We will definitely try rerunning by setting session level "p= arallel_force_local" parameter to true as we set this value as 'FA= LSE' in v$parameter.

"parallel slaves = will not be able to join the execution because they are not able to reprodu= ce the same execution plan as the coordinator. "

I am a bit confused with above statement on the dynamic=C2=A0sampli= ng part, I see out of three global temporary tables used in this query , tw= o of them are using private session level stats as it's gathered inside= =C2=A0the code. But one(RTNI) is having stats set as NULL and also its lock= ed , which means the dynamic sampling must have been triggered for that tab= le only. But in that case too, i am not able to understand how the dynamic = sampling can be the cause, can you please explain bit more. As because , my= understanding is parsing will happen at the first stage and till that time= parallel slaves wont get involve in real execution. So as the table relate= d info will be available in the node-3 as that being the session of the que= ry coordinator/parent session , so that should only do the parsing work. Pl= ease correct me if wrong.

Regards
Pap

On Tue, Feb 2, 2021 at 11:20 AM Laurentiu Oprea <laurentiu.oprea06@gmail.= com> wrote:
Hello,

Additional=C2=A0to what has b= een mentioned, usually this issue is caused=C2=A0by 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.=C2=A0
Dynam= ic Sampling is often one of the root causes for this issue, If DS is not he= lping in your case you can create a spl patch for that sql with a lower lev= el of DS or even disable it : dynamic_sampling(0).

Good luck

=C3=8En lun., 1 feb. 2021 la 23:53, Sayan Malakshinov <<= a href=3D"mailto:xt.and.r@gmail.com" target=3D"_blank">xt.and.r@gmail.com> a scris:
=
Hi Pap,

Have you tried to set=C2=A0para= llel_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 sa= me 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. Als= o that means that node 2 have to parse your query too for your slaves (some= times it even leads to more child cursors)

On Mon, Feb 1, 2021 at 10:2= 8 PM Pap <oracle.developer35@gmail.com> wrote:

Hell= o 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 paralle= l(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 appearing as its own slave sessions. We have "parallel_degree_poli= cy" set as MANUAL in v$parametr. This query is running for different l= iterals one after another multiple times in a loop fashion. And all these s= amples logged in dba_hist_active_sess_history showing IN_PARSE as 'Y= 9;. Dueto these waits the overall execution time of the process is going be= yond ~5hrs+. The CPU and IO waits as noted in sql monitor is very small. Wo= ndering how parallel slave processes of the same query are blocking each ot= her 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 used in this query are global tem= porary tables "on commit preserve row" types.



Thanks A= nd Regards

Pap



--
Best regards,
Sayan Malaksh= inov
Oracle performance tuning engine= er
Oracle ACE Associate
http://orasql.org
=


--
Chinar Aliyev


Visit My=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 :Blog
Let's Connect -=C2=A0Linkedin Profile

My=C2=A0Twitter=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 -=C2=A0ChinarAliyev

<= br>


--

Houri Mohamed=C2= =A0

Oracle DBA-Developer-Performance & Tuning=C2=A0

Visit My =C2=A0 =C2=A0 =C2=A0 =C2=A0 -=C2=A0Blog

Let's Connect -=C2=A0Linkedin Profile

My=C2=A0Twitter= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -=C2=A0MohamedHouri

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