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 3303C10035D536 for ; Tue, 2 Feb 2021 09:42:48 +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 922724473C; Tue, 2 Feb 2021 08:42:45 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 891563F85E; Tue, 2 Feb 2021 08:42:45 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612255365; bh=ebCmN8WW0TRXyaDa6+DmupJsJN0jczVs9NMYU+iDVts=; h=From:Sender:Sender:From; b=DwrJ80My7FdbhMBCIVmICieP25+9E5qndyL2owDA3pirY46Jn4CaRKknVcVioE6oB aNcRQR2C+84GDrJ8kH7sQcfyKvdoTzU9JvIHzkAuB8VvN3NvLDr8SjG6YZsOK7pYxm Ef4DYMn7mSMy8IGbgIuQZX9eG29OPE/uY/ofUgLU= 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 5uMSAe95PprI; Tue, 2 Feb 2021 08:42:45 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 43D3B3F970; Tue, 2 Feb 2021 08:42:43 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612255364; bh=ebCmN8WW0TRXyaDa6+DmupJsJN0jczVs9NMYU+iDVts=; h=From:Sender:Sender:From; b=SZaYRilxEKqkhadj0zeYOSFc6PaJi5gVRczHXVABJUX78/E1G9+7C1SjubdNjKtzV 4u5OicfyefjvUoda6hSppAjZF2yq+9q9S7PRWVT69YnHF4RL3lmg+C/lrZ7mWQQyXv ZD5T9n7yRWL8FM8R8RVeeClhHYBk074H7zi52wtw= Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 02 Feb 2021 08:42:42 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 026D53F85E for ; Tue, 2 Feb 2021 08:42:42 +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=ARz5Le+3; 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 HTUSExccSwZw for ; Tue, 2 Feb 2021 08:42:41 +0000 (UTC) Received: from mail-qk1-f170.google.com (mail-qk1-f170.google.com [209.85.222.170]) (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 D761B3F844 for ; Tue, 2 Feb 2021 08:42:41 +0000 (UTC) Received: by mail-qk1-f170.google.com with SMTP id v126so19052872qkd.11 for ; Tue, 02 Feb 2021 00:42:41 -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=Hyhy2HNAclUBmRR/a3R8B/f3Kt4H+auiF9gDm5MRN7E=; b=LrG3mk3TqGnlbhpbE64PD+mZXNVf7ZQ9T3aMYTre7A8nKFgVByZ0hTL4J7g3libTdI 8q3M1vqhcaGGiz002ZM/UFs5QPUcLcEFpCLf7Y3ObwOLyi0ZgbeN3r7j+f6t10OwG0EJ LA1Zuiofu7OiGU2vEG2QQ+/9Sic+fubqSRa/62I6dbv8PqGuinwdzX0GFHlnVBuWTcYR +PvEjZ9N7zop0fin7zWyTXXhAtTpMcSoyJ7HqlDYVhffQyn13Ar5HsxNnq0N9V33jR/U bodo0ExWl7dYP6wi/mCwqsVD7h6nHem2tE2dD8GZNsKbf5CsR0DwmufLIsiytHYc+rZX dt2g== X-Gm-Message-State: AOAM530D/JHI/LJuktZJcEcT1KAaXdXADSOrC3DAtn+n9dA+nNpavjon WPe3i5kQ4qSCwt1l8IqflzGJVYaD5r32GFIgEHs= X-Google-Smtp-Source: ABdhPJxJFvrDlBTkVruxI80VSdmDxF2S17eoahyN4+XzRIgw3YqH2xjLHJtDnp49XGBv1E3gH3ySoCaZ5OJzV2WINWw= X-Received: by 2002:a05:620a:2289:: with SMTP id o9mr19254763qkh.171.1612255361510; Tue, 02 Feb 2021 00:42:41 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Chinar Aliyev Date: Tue, 2 Feb 2021 12:42:31 +0400 Message-ID: Subject: Re: Library lock issue To: oracle.developer35@gmail.com Cc: Laurentiu Oprea , Sayan Malakshinov , Oracle L Content-Type: multipart/alternative; boundary="0000000000001c305505ba5674bc" X-archive-position: 78816 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: chinaraliyev@gmail.com Precedence: normal Reply-To: chinaraliyev@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 --0000000000001c305505ba5674bc Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 them > are using private session level stats as it's gathered inside the code. B= ut > 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 tha= t > 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 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 i= s >> 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 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 >>> 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 wa= it on >>>> X" between its own slaves. I mean to say the blocking session is appea= ring >>>> as its own slave sessions. We have "parallel_degree_policy" set as MAN= UAL >>>> 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 wait= s 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 parsi= ng >>>> 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 row= " >>>> types. >>>> >>>> >>>> >>>> Thanks And Regards >>>> >>>> Pap >>>> >>> >>> >>> -- >>> Best regards, >>> Sayan Malakshinov >>> Oracle performance tuning engineer >>> Oracle ACE Associate >>> http://orasql.org >>> >> --=20 *Chinar Aliyev* Visit My :Blog Let's Connect - *Linkedin Profile * My Twitter - ChinarAliyev --0000000000001c305505ba5674bc Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi,
There are several bugs in Oracle Support and= might one of them be appropriate for your case.

It happens=C2=A0when QC and PX Slaves are allocated in different instances= , and a remote=C2=A0slave has to parse the statement based on the info=C2= =A0sent=C2=A0by QC (to= achieve the same=C2=A0execution plan generated by QC).=C2=A0
<= div class=3D"gmail_default" style=3D"">To reduce parsing you can force it as Sayan=C2=A0has mentioned. A= lso, 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_local" parameter to true as we set this value as 'FALSE'= ; in v$parameter.

"parallel slaves will no= t 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=C2=A0sampling 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=C2=A0t= he code. But one(RTNI) is having stats set as NULL and also its locked , wh= ich means the dynamic sampling must have been triggered for that table only= . But in that case too, i am not able to understand how the dynamic samplin= g can be the cause, can you please explain bit more. As because , my unders= tanding is parsing will happen at the first stage and till that time parall= el slaves 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 coor= dinator/parent session , so that should only do the parsing work. Please co= rrect 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 been men= tioned, 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 reprodu= ce the same execution plan as the coordinator.=C2=A0
Dynamic Samp= ling is often one of the root causes for this issue, If DS is not helping i= n your case you can create a spl patch for that sql with a lower level of D= S or even disable it : dynamic_sampling(0).

Good l= uck

=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=C2=A0parallel_f= orce_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 dat= a;
2. You are using global temporary tables, so their data is pri= vate 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 P= ap <or= acle.developer35@gmail.com> wrote:

Hello All, = We are seeing some odd behaviour. Its version 12.1.0.2.0 of oracle. And a s= mall query(finishing in <1 minutes) which is executing in parallel(2) is= experiencing "library cache lock" and "cursor: pin S wait o= n 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 MANUAL 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'. Due= to these waits the overall execution time of the process is going beyond ~5= hrs+. 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 dur= ing parsing itself. Or are we hitting any bug in this version?

Attached is the sql and its run ti= me sql monitor. And all the tables used in this query are global temporary = tables "on commit preserve row" types.



Thanks And Rega= rds

Pap



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


--
Chinar Ali= yev


Visit My=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 :Blog<= /div>
Let&#= 39;s Connect -=C2=A0Linkedin Profile

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


--0000000000001c305505ba5674bc-- -- http://www.freelists.org/webpage/oracle-l