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 2974B1002BB728 for ; Tue, 2 Feb 2021 10:02:54 +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 1100344938; Tue, 2 Feb 2021 09:02:52 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 05E403F85E; Tue, 2 Feb 2021 09:02:52 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612256572; bh=bHLHgKPW/rP1YDr8dCYOzmxxxdtDQEXnJiYwEtnBiRo=; h=From:Sender:Sender:From; b=fBxpk0LgN1jaHgE4XRVx8C3Zni/NkWOnM6OLviTpxBqWpDhtLxCY+qRcAQc9GqOzB jFRkJ8zRU8JUnJtW88mQUvKazcLDf7RBijfBFw8NwK87ZF18gQi76mw+0CIvIi2wot K4pq0bpknxq/k/PF1UXcptAj99E2wRZDn28APlXI= 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 IiXinbh8Y9MO; Tue, 2 Feb 2021 09:02:51 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 77EA43F970; Tue, 2 Feb 2021 09:02:49 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612256570; bh=bHLHgKPW/rP1YDr8dCYOzmxxxdtDQEXnJiYwEtnBiRo=; h=From:Sender:Sender:From; b=fr2SR0Xuf/EchMrasIdHwptkKnvZe1syA8NMXpH6iqs6T0jxVWyYVuOj4WtZw17A2 XETKAig+bjJMGcb9djvQ5dUqb6WdiiluO/Q/cPT6tPHTjgTh6VhgjelSBZYfaoembL je8cQNtmKRw/C7tckkyTsrC6BfrahUUNwVAkuxd4= Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 02 Feb 2021 09:02:48 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 3B4EC3F85E for ; Tue, 2 Feb 2021 09:02:48 +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=qBY/xZyB; 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 UK227udPB-LY for ; Tue, 2 Feb 2021 09:02:48 +0000 (UTC) Received: from mail-ej1-f53.google.com (mail-ej1-f53.google.com [209.85.218.53]) (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 2C0633F844 for ; Tue, 2 Feb 2021 09:02:48 +0000 (UTC) Received: by mail-ej1-f53.google.com with SMTP id jj19so1033116ejc.4 for ; Tue, 02 Feb 2021 01:02:48 -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=DkDngE2TeW6N7HlkLSQkkw1Syf4U+FBbNjOkz4ic1S4=; b=XGwdC1KQq1J0HVD7ITiIdFDNxl+vSVnh5eZINUcHHcA2vJ/PTSHGQQcx8UpzWFwrBy igE2eVxJ7mdPCSyR5UZN0GSttrb4mhouzPsBpnxqOc3mIz/+jeuIBoZigjIZ66HXYONV r0eZXsMQBq84oqGmsnCS0nb/Y4ADnz1Amfuw9kN+c+N0QuNZVV50Prfqws7DU18NyDx9 1JhkuFMwDRFTqc7RD6DWyjyh3XU9OWCerKejd654+M3h6nInvmxKof4faSUSEheZVC15 sOHkbk2DsBh8uqZZacLoYPe7QDQEtoW8a5B93sFD2hXrbwGVDjbRL5qq0dz1845aMPYw ZOyQ== X-Gm-Message-State: AOAM531dbtkdr83Dgl8VdSEy9GaFAL7cC7LnMwZmFZNCwBNrsA4e3Yiu Bnx9/Ia1+nyfLDGdKJp9RH+K3dVR5A5el4gMHPLioCfOvds= X-Google-Smtp-Source: ABdhPJzx3Sm4kMyHkXJ4ybh9ESyFHVbBdVX6VjJVjZcjt5JKEikt0NR6gW5nFxNwIaBJ3db/29DoqyqOrTfgIvDL2WQ= X-Received: by 2002:a17:907:3da0:: with SMTP id he32mr1379765ejc.265.1612256567217; Tue, 02 Feb 2021 01:02:47 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Mohamed Houri Date: Tue, 2 Feb 2021 10:02:36 +0100 Message-ID: Subject: Re: Library lock issue To: Chinar Aliyev Cc: oracle.developer35@gmail.com, Laurentiu Oprea , Sayan Malakshinov , Oracle L Content-Type: multipart/alternative; boundary="000000000000f9d25805ba56bb8b" X-archive-position: 78817 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: mohamed.houri@gmail.com Precedence: normal Reply-To: mohamed.houri@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 --000000000000f9d25805ba56bb8b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 bug 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-stat= istic That=E2=80=99s said I haven=E2=80=99t tested how Oracle will consider the Q= C 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, an= d > 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 ar= e >> 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 the= m >> 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 th= at >> case too, i am not able to understand how the dynamic sampling can be th= e >> cause, can you please explain bit more. As because , my understanding is >> parsing will happen at the first stage and till that time parallel slave= s >> wont get involve in real execution. So as the table related info will be >> 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 wit= h 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 sen= d >>>> 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 you= r >>>> 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 o= f >>>>> oracle. And a small query(finishing in <1 minutes) which is executing= in >>>>> parallel(2) is experiencing "library cache lock" and "cursor: pin S w= ait on >>>>> X" between its own slaves. I mean to say the blocking session is appe= aring >>>>> as its own slave sessions. We have "parallel_degree_policy" set as MA= NUAL >>>>> in v$parametr. This query is running for different literals one after >>>>> another multiple times in a loop fashion. And all these samples logge= d in >>>>> dba_hist_active_sess_history showing IN_PARSE as 'Y'. Dueto these wai= ts 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 paralle= l >>>>> slave processes of the same query are blocking each other during pars= ing >>>>> 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 ro= w" >>>>> 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 - *L= inkedin > Profile * > > My Twitter - ChinarAliyev > > > --=20 Houri Mohamed Oracle DBA-Developer-Performance & Tuning Visit My - Blog Let's Connect - *Linkedin Profile * My Twitter - MohamedHouri --000000000000f9d25805ba56bb8b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


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=


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=A9crit=C2=A0:
Hi,
There are sever= al bugs in Oracle Support and might one of them be appropriate for your cas= e.

It happens=C2=A0when QC and PX Slaves are allocated in different in= stances, and a remote=C2=A0slave has to parse the statement based on the i= nfo=C2=A0sent=C2=A0by = QC (to achieve the same=C2=A0execution plan generated by QC).=C2=A0<= /div>
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 <oracle.developer35@gmail.com= > wrote:
=
Thanks a lot.

We will = definitely try rerunning by setting session level "parallel_force_loca= l" parameter to true as we set this value as 'FALSE' in v$para= meter.

"parallel slaves will not be able t= o join the execution because they are not able to reproduce the same execut= ion plan as the coordinator. "

I am a bit= confused with above statement on the dynamic=C2=A0sampling part, I see out= of three global temporary tables used in this query , two of them are usin= g private session level stats as it's gathered inside=C2=A0the code. Bu= t one(RTNI) is having stats set as NULL and also its locked , which means t= he dynamic sampling must have been triggered for that table only. But in th= at case too, i am not able to understand how the dynamic sampling can be th= e 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 w= ont get involve in real execution. So as the table related info will be ava= ilable in the node-3 as that being the session of the query coordinator/par= ent session , so that should only do the parsing work. Please correct me if= wrong.

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

Additional=C2=A0to what has been mentioned, usu= ally 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
Dynamic Sampling is oft= en 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 d= isable it : dynamic_sampling(0).

Good luck

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

Have you tried to set=C2=A0parallel_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 d= ata between nodes and fight for concurrent access to the same data;
2. You are using global temporary tables, so their data is private for y= our session and stored on the same node as your session. In case of paralle= l access to GTT, QC has to send also their segment info, so your slaves hav= e to request data from it, ie from Node 3.
3. Also that means tha= t node 2 have to parse your query too for your slaves (sometimes it even le= ads to more child cursors)

On Mon, Feb 1, 2021 at 10:28 PM Pap <oracle.devel= oper35@gmail.com> wrote:

Hello All, We are see= ing 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 experienc= ing "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_policy" set as M= ANUAL in v$parametr. This query is running for different literals one after= another multiple times in a loop fashion. And all these samples logged in = dba_hist_active_sess_history showing IN_PARSE as 'Y'. Dueto these w= aits 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 paral= lel slave processes of the same query are blocking each other during parsin= g itself. Or are we hitting any bug in this version?

Attached is the sql and its run time sql mon= itor. And all the tables used in this query are global temporary tables &qu= ot;on commit preserve row" types.



=

Thanks And 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

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