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 50504100307714 for ; Tue, 2 Feb 2021 15:30:26 +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 4593A45588; Tue, 2 Feb 2021 14:30:25 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 316D13F971; Tue, 2 Feb 2021 14:30:25 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612276225; bh=SqgPstr3PNh0VPIMDNqxdOHIjjLvhWAtm2KpHxoT3xM=; h=From:Sender:Sender:From; b=i1VRZggUlAUvBU4Eg6szYl6VXIXj45L98PCv+eUPNqdrawoh+BAYPM9r882I6P1Vu e5zbBs389S4SAapyTlmlWqxREf7jyCAYHV5vrIUsP+pObaCmzA31vH4KPz589DEw6Q zSY39XCsRnAzgplLS/xG9fYh02On+x/UMvhhMHuA= 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 PWEw8pCIm4zS; Tue, 2 Feb 2021 14:30:25 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id A89E33F85E; Tue, 2 Feb 2021 14:30:22 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612276223; bh=SqgPstr3PNh0VPIMDNqxdOHIjjLvhWAtm2KpHxoT3xM=; h=From:Sender:Sender:From; b=JIfotSTPq80Jio+WzXOlMndQFqjN+qn825vuoyC7y8a/JgRlDLaTbyRow3ZuMscqB /SK/BCFkqLNbAzClS78kPBwGYip8uOqI9rtpTWziqJWap/Yxekipsh2lx8OOi5a4Rv PSKTl8VEqv4tUM60ikMrHxKTIg5QOcVEacDMdvWM= Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 02 Feb 2021 14:30:21 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 6DF3A3F972 for ; Tue, 2 Feb 2021 14:30:21 +0000 (UTC) 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 jL-F9I2Gv0Gj for ; Tue, 2 Feb 2021 14:30:21 +0000 (UTC) Received: from gw2.tidalhosting.net (gateway.tidalhosting.net [155.130.128.124]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id 34C883F970 for ; Tue, 2 Feb 2021 14:30:20 +0000 (UTC) Received: from mrtr.tidalhosting.net (UnknownHost [10.0.150.3]) by gw2.tidalhosting.net with SMTP (version=TLS\Tls12 cipher=Aes256 bits=256); Tue, 2 Feb 2021 09:30:11 -0500 Received: from mx1.tidalhosting.net (unknown [10.0.100.9]) by mrtr.tidalhosting.net (Postfix) with ESMTPS id 94B293049D7C; Tue, 2 Feb 2021 09:30:06 -0500 (EST) Received: from mwf4500 (c-73-238-99-41.hsd1.nh.comcast.net [73.238.99.41]) by mx1.tidalhosting.net with SMTP (version=Tls cipher=Aes256 bits=256); Tue, 2 Feb 2021 09:29:48 -0500 From: "Mark W. Farnham" To: , "'Mohamed Houri'" Cc: "'Pap'" , "'Chinar Aliyev'" , "'Sayan Malakshinov'" , "'Oracle L'" References: In-Reply-To: Subject: RE: Library lock issue Date: Tue, 2 Feb 2021 09:29:43 -0500 Message-ID: <2df701d6f96f$da147c30$8e3d7490$@rsiz.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_2DF8_01D6F945.F1435630" Content-Language: en-us X-Exim-Id: 2df701d6f96f$da147c30$8e3d7490$ X-archive-position: 78825 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: mwf@rsiz.com Precedence: normal Reply-To: mwf@rsiz.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 ------=_NextPart_000_2DF8_01D6F945.F1435630 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable I know that the bug fix was back ported to 12r1 in a specific patch, but = those notes were entangled with a confidential access to which I was not = allowed to make notes aside from what I keep in my head. =20 IF I recall correctly that might have been a one-off and not necessarily = bundled compatibly with 12r1=E2=80=99s upgrade and patches because 12r2 = was coming out with it already fixed. =20 mwf =20 From: oracle-l-bounce@freelists.org = [mailto:oracle-l-bounce@freelists.org] On Behalf Of Laurentiu Oprea Sent: Tuesday, February 02, 2021 5:23 AM To: Mohamed Houri Cc: Pap; Chinar Aliyev; Sayan Malakshinov; Oracle L Subject: Re: Library lock issue =20 Nice one, thanks Mohamed. I`ll update on this thread once I reach the = bottom of my issues as well.=20 =20 =C3=8En mar., 2 feb. 2021 la 12:19, Mohamed Houri = a scris: @Laurentiu As I mentioned above it was a kind of bug or kind of =E2=80=9Cwe = don=E2=80=99t know why when using bind variable PX slaves were refusing = to share the execution plan of their QC=E2=80=9D. I have had an = excellent private discussion with Oracle support engineer about this = issue and he clearly explained that this was reported to the CBO team. So, I am sorry I haven=E2=80=99t any bug to share with you for this = issue. Moreover, it happened in 12cR1 and, as I said above, I still have = not encountered a similar issue in 12cR2, 18c, and 19c where I am using = parallelism extensively @Pap My answer was that, probably, your parallel parsing issue is due to the = usage of GTT private statistics. So if your planned fix tentative will = reveal to be useless, I would then suggest checking if using GTT shared = statistics will get rid of the library cache lock and cursor pin S wait = on x wait events. If so then you have to balance between sharing = inadequate GTT statistics and parsing effects. =20 =E2=80=9CAll things being equal you must balance between the performance = improvement brought by this new GTT feature and the parsing side effect = it introduces because of the underlying cursor invalidation. In my = client case, the Library cache and Cursor Pin S wait on X wait events = introduced by the SESSION PRIVATE statistics largely outweigh the = performance penalty that comes when the 49 streams share the same GTT = statistics.=E2=80=9D =20 Best regards Mohamed Houri =20 Le mar. 2 f=C3=A9vr. 2021 =C3=A0 11:10, Laurentiu Oprea = a =C3=A9crit : Hello Pap, =20 I was mentioning DS in the context of possible bug as well. If L2 is = used most probably you don't have an issue around this and your issue = might be around GTTs stats. =20 =C3=8En mar., 2 feb. 2021 la 11:57, Pap a = scris: Thanks much. =20 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.=20 =20 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. =20 Also can you please share the exact bug which you are talking of.=20 =20 =20 Note ----- - dynamic statistics used: dynamic sampling (level=3D2) =20 Regards Pap =20 On Tue, Feb 2, 2021 at 2:32 PM Mohamed Houri = wrote: =20 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.=20 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 = =20 =20 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.=20 You can see if your execution plan has the following Note signaling that = it has used a GTT private statistics or not Note=20 -----=20 -- Global temporary table session private statistics used =20 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 =20 Best Regards Mohamed Houri =20 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. =20 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).=20 To reduce parsing you can force it as Sayan has mentioned. Also, check = support notes/bugs. =20 Best Regards=20 =20 On Tue, Feb 2, 2021 at 11:12 AM Pap = wrote: Thanks a lot. =20 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. =20 "parallel slaves will not be able to join the execution because they are = not able to reproduce the same execution plan as the coordinator. " =20 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. 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 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 = 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. =20 Regards Pap =20 On Tue, Feb 2, 2021 at 11:20 AM Laurentiu Oprea = wrote: Hello, =20 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.=20 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). =20 Good luck =20 =C3=8En lun., 1 feb. 2021 la 23:53, Sayan Malakshinov = a scris: Hi Pap, =20 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) =20 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 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 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'. 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 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. =20 =20 Thanks And Regards Pap =20 --=20 Best regards, Sayan Malakshinov Oracle performance tuning engineer Oracle ACE Associate http://orasql.org =20 --=20 Chinar Aliyev =20 = = Visit My :Blog =20 Let's Connect - = Linkedin Profile My Twitter - = ChinarAliyev =20 =20 --=20 Houri Mohamed=20 Oracle DBA-Developer-Performance & Tuning=20 Visit My - Blog =20 Let's Connect - = Linkedin Profile =20 My Twitter - MohamedHouri = =20 =20 --=20 Houri Mohamed=20 Oracle DBA-Developer-Performance & Tuning=20 Visit My - Blog =20 Let's Connect - = Linkedin Profile =20 My Twitter - MohamedHouri = =20 ------=_NextPart_000_2DF8_01D6F945.F1435630 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

I know that the bug fix was back ported to 12r1 in a specific patch, = but those notes were entangled with a confidential access to which I was = not allowed to make notes aside from what I keep in my = head.

 

IF I recall correctly that might have been a one-off and not = necessarily bundled compatibly with 12r1=E2=80=99s upgrade and patches = because 12r2 was coming out with it already = fixed.

 

mwf

 

From:= = oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] = On Behalf Of Laurentiu Oprea
Sent: Tuesday, February = 02, 2021 5:23 AM
To: Mohamed Houri
Cc: Pap; Chinar = Aliyev; Sayan Malakshinov; Oracle L
Subject: Re: Library lock = issue

 

Nice = one, thanks Mohamed. I`ll update on this thread once I reach the bottom = of my issues as well. 

 

=C3=8En mar., 2 feb. 2021 la 12:19, Mohamed Houri = <mohamed.houri@gmail.com> = a scris:

@Laurentiu

As I mentioned above it was = a kind of bug or kind of =E2=80=9Cwe don=E2=80=99t know why when using = bind variable PX slaves were refusing to share the execution plan of = their QC=E2=80=9D. I have had an excellent private discussion with = Oracle support engineer about this issue and he clearly explained that = this was reported to the CBO team.

So, I am sorry I = haven=E2=80=99t any bug to share with you for this issue. Moreover, it = happened in 12cR1 and, as I said above, I still have not encountered a = similar issue in 12cR2, 18c, and 19c where I am using parallelism = extensively

@Pap

My answer was that, = probably, your parallel parsing issue is due to the usage of GTT = private statistics. So if your planned fix tentative will reveal to be = useless, I would then suggest checking if using GTT shared statistics = will get rid of the library cache lock and cursor pin S wait on x wait = events. If so then you have to balance between sharing inadequate GTT = statistics and parsing effects.

 

=E2=80=9CAll things being equal you must balance = between the performance improvement brought by this new GTT feature and = the parsing side effect it introduces because of the underlying cursor = invalidation. In my client case, the Library cache and Cursor Pin S wait = on X wait events introduced by the SESSION PRIVATE statistics largely = outweigh the performance penalty that comes when the 49 streams share = the same GTT statistics.=E2=80=9D

 

Best regards

Mohamed Houri

 

Le mar. 2 f=C3=A9vr. 2021 =C3=A0 11:10, = Laurentiu Oprea <laurentiu.oprea06@gmail.com> a = =C3=A9crit :

Hello Pap,

 

I = was mentioning DS in the context of possible bug as well. If L2 is used = most probably you don't have an issue around this and your issue = might be around GTTs stats.

 

=C3=8En mar., 2 feb. 2021 la 11:57, Pap <oracle.developer35@gmail.com> a = scris:

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 <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) 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-temporar= y-table-private-statistic

 

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 <chinaraliyev@gmail.com> 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 <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 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. 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 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 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 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 appearing = 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'. 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 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

<= /div>


 

-- =

Chinar = Aliyev

 

Visit = My         :Blog

Let's = Connect - Linkedin = Profile

My Twitter    &nbs= p; - ChinarAliyev

 


 

-- =

Houri = Mohamed 

Oracle = DBA-Developer-Performance & Tuning 

Visit My =         - Blog

Let's = Connect - Linkedin Profile

My Twitter    &nbs= p; - MohamedHouri


 

-- =

Houri = Mohamed 

Oracle = DBA-Developer-Performance & Tuning 

Visit My =         - Blog

Let's = Connect - Linkedin Profile

My Twitter    &nbs= p; - MohamedHouri

------=_NextPart_000_2DF8_01D6F945.F1435630-- -- http://www.freelists.org/webpage/oracle-l