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 C69FF100373106 for ; Tue, 2 Feb 2021 10:17: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 994A34488F; Tue, 2 Feb 2021 09:17:20 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 8544A3F970; Tue, 2 Feb 2021 09:17:20 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612257440; bh=GxrqVFvG0CqRxXjscu0xlkvWuG4wSr5pFyHN8o02IN8=; h=From:Sender:Sender:From; b=F0cMidxxEB8ZVe6XuDG3o7KbmBX7jV6BbmHFGOgN1im6p5+PJaMy780FsDfr2Aavb eo2a6lsB3X+gvwbr90o5pBTjknz2G5VpKIC6kfLytH2izio9lNZlweIq/8qjOxrcvJ EbfkiuYniOik5KkzwkJPf1pFFuWxt8kczk3KZO9o= 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 kOrQcAMTlxIo; Tue, 2 Feb 2021 09:17:20 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 544133F96E; Tue, 2 Feb 2021 09:17:18 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612257439; bh=GxrqVFvG0CqRxXjscu0xlkvWuG4wSr5pFyHN8o02IN8=; h=From:Sender:Sender:From; b=uKlDiQvPmuw7yGdGXYZ8MfMElb8Pd615eayNHv0JkElYFj1hi6SIETobBjBkp8HD6 JNUH/idwzHMd959840OHuDvEIpKlrLELyQWsHePT1O/qqwx1rbjpPt/JG8460exY/g jv5ZAP+WDNhFHWUAcIf93GMuNcQf/l73fOx43E7M= Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 02 Feb 2021 09:17:17 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 26AB73F85E for ; Tue, 2 Feb 2021 09:17:17 +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=Oe+meGa2; 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 Q1n5LUjnkA-o for ; Tue, 2 Feb 2021 09:17:17 +0000 (UTC) Received: from mail-io1-f43.google.com (mail-io1-f43.google.com [209.85.166.43]) (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 12B463F844 for ; Tue, 2 Feb 2021 09:17:16 +0000 (UTC) Received: by mail-io1-f43.google.com with SMTP id y19so20539577iov.2 for ; Tue, 02 Feb 2021 01:17:16 -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=7SBYp+emBjlFO+uI69wP1P1RS+Dx0wBIEeg+8uSIwZQ=; b=n9qBdgPwmNbXyQmupssWJMuMzfL9A78s66kK0zWIpSfTosisVIRy81OQBcAEZT7wSu 6rut3SsbE2NLkWe9a1Jj6//eusiPXvbwjcf4WTbx52RXg01rKbvaC/TeiQKTo+bRajPt jdyKRK3c9R3UfPiKz6RhO9XkdTvMMUSMRcW6qtxGH0gIbtwndUYBcVWA2EmRH8+Ye362 bLdGOJDbP0OJETb8x8WTiH5p37DzuYTv9fe3wRu1y5NGHCtBhP6Jdi/Gg4K7Q/DQVc8x efceRG2HsdCdhmjIn9A3/LWMBDzViMkWPr15RxOkQPt1RlDlZN9kr8e+eJmBzgWFVtHf R02Q== X-Gm-Message-State: AOAM531ZAhvvyo4nsS794vnDJREKVP/DpFz1gBKrDJFriFUv31T+s+TK H9XYrVgFJCFzFJ0fFkrIBjhWCXzpGnmvBc6I9fc= X-Google-Smtp-Source: ABdhPJyqJrCogpXcAUWw8uq8KpWU3RVXmDVchlzLPS+TO6PduzQwV29vCtRQEcWuJQkg0m9tF9XVDhL+QT9WjNmUB5s= X-Received: by 2002:a05:6602:2d0d:: with SMTP id c13mr3662003iow.51.1612257436418; Tue, 02 Feb 2021 01:17:16 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Laurentiu Oprea Date: Tue, 2 Feb 2021 11:16:32 +0200 Message-ID: Subject: Re: Library lock issue To: Mohamed Houri Cc: Chinar Aliyev , Pap , Sayan Malakshinov , Oracle L Content-Type: multipart/alternative; boundary="000000000000c8cb5005ba56ef08" X-archive-position: 78818 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: laurentiu.oprea06@gmail.com Precedence: normal Reply-To: laurentiu.oprea06@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 --000000000000c8cb5005ba56ef08 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hello Mohamed, Can you post which bug number you`ve hit. I`m also strugaling with downgrading situations even for queries which are ridiculously simple and I already had applied a couple of patches. Thanks. =C3=8En mar., 2 feb. 2021 la 11:02, Mohamed Houri = a scris: > > 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 > > > --000000000000c8cb5005ba56ef08 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello Mohamed,

Can you post which bug n= umber you`ve hit. I`m also strugaling=C2=A0with downgrading situations even= for queries which are ridiculously simple and I already had applied a coup= le of patches.=C2=A0

Thanks.=C2=A0

=
=C3=8En ma= r., 2 feb. 2021 la 11:02, Mohamed Houri <mohamed.houri@gmail.com> a scris:


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

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