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 510BF100382EDE for ; Tue, 2 Feb 2021 08:12:08 +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 1149D4461D; Tue, 2 Feb 2021 07:12:06 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 0454C3F85E; Tue, 2 Feb 2021 07:12:06 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612249926; bh=tLJArNueoXrl/Qp9wfimTo5JC24SppKBEP7XweULN+8=; h=From:Sender:Sender:From; b=hW2ygFvN6Yzn+Dd/mX0vxBHewNzIy1+LTVOp4BNJhORuvTcbVel7PQtfLy27H8YUM 3zH6GhqHKGqLQPGYojOXWsp8hF1LC35pwV5Rn1SuzLL8y1KQ8TE+926PECDOIdT6SH Mk2NduTuRPmrDhwIuTgqXWp/vQaBcWR2ikuWf/dI= 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 CyLufDh1F2HG; Tue, 2 Feb 2021 07:12:05 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id C33B73F96E; Tue, 2 Feb 2021 07:12:03 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612249924; bh=tLJArNueoXrl/Qp9wfimTo5JC24SppKBEP7XweULN+8=; h=From:Sender:Sender:From; b=M2QS4JjuxOVzCReV/i4aHaJx52ZXqEOYOEA+7YlLBMAdsAZ9iJRw0GH0pudHpZSUs AlvyTsgg6xoga+Iaceyv1dXx4ybNOsTn9n3kKJFoBnIMRsjHwI4GQVswBoJl1l87mW IPkVtSseb7XMT4UpGmmKzNexXH9P4HqQApZRQzaY= Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 02 Feb 2021 07:12:02 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 8F6853F85E for ; Tue, 2 Feb 2021 07:12:02 +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=bqRiT8WC; 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 E24MRDFGpGzd for ; Tue, 2 Feb 2021 07:12:02 +0000 (UTC) Received: from mail-vs1-f54.google.com (mail-vs1-f54.google.com [209.85.217.54]) (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 7F56F3F844 for ; Tue, 2 Feb 2021 07:12:02 +0000 (UTC) Received: by mail-vs1-f54.google.com with SMTP id o186so10602308vso.1 for ; Mon, 01 Feb 2021 23:12:02 -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=SBgMfE7YFw9C7dj4GGJNJpcwPpfHzJpE7haabD/aR40=; b=ds1VqH/z9N9tHzryF5WzJAOPhhsN2madDllWrYGMSc8NPQBrWM+xCQeSBFEU8mqjko g7VGlZobXpW4GQ+vMvbYeEVpZCkFDQliBU9iwOmP8Hl7ffbkYD0ESaLvwHf/lDwgT/go 2SlVzvSk0KatL1fK+puVbV6H2WUAofbRDbwc+gax/oV7KLz/hRzovf8paleyDt+BSE3u CjivIiaxV95HnaiO9EZtr1+Q8c1ktHhp3WRo9Y9Cmd+I69pzFFVdaNYMKKxPkicG5S1f kqMtC3BW6hBjUHuVAhSV69+N7OtAh0n3+g4bJ0RVypQXJz0IvkNXJ5viBxQsMweA1RkT V3Bw== X-Gm-Message-State: AOAM533AGNbPp1s+qHgZB9Ny0cFIw1pTMLUswhRFElCR5b1chYkdmqsi OBEn3P8EHFWZI7HQPNGOIGGfI5Cc6nmqmCuDcds= X-Google-Smtp-Source: ABdhPJxIx3u4N9OnYs85I5KBt/tfiXb581jcmiMnTvXpv7eaQdVV5v7+VcEcRYJz3BCDfamCxUpYHymajKOlCiS1Vvo= X-Received: by 2002:a05:6102:109:: with SMTP id z9mr11292776vsq.34.1612249922095; Mon, 01 Feb 2021 23:12:02 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Pap Date: Tue, 2 Feb 2021 12:41:49 +0530 Message-ID: Subject: Re: Library lock issue To: Laurentiu Oprea Cc: Sayan Malakshinov , Oracle L Content-Type: multipart/alternative; boundary="000000000000e54be605ba552f4b" X-archive-position: 78815 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 --000000000000e54be605ba552f4b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 wrote: > Hello, > > Additional to what has been mentioned, usually this issue is caused by th= e > 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 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 i= n >>> parallel(2) is experiencing "library cache lock" and "cursor: pin S wai= t on >>> X" between its own slaves. I mean to say the blocking session is appear= ing >>> as its own slave sessions. We have "parallel_degree_policy" set as MANU= AL >>> 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 a= nd >>> IO waits as noted in sql monitor is very small. Wondering how parallel >>> 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 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 >> > --000000000000e54be605ba552f4b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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,

<= /div>
Additional=C2=A0to what has been 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
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 : dynami= c_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=C2=A0parallel_force_local=3Dtrue? Your QC is = on Node 3 while your slaves are on Node 2:
1. Parallel queries us= ually work much better if they don't need to send data between nodes an= d fight for concurrent access to the same data;
2. You are using = global temporary tables, so their data is private for your session and stor= ed 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 fr= om it, ie from Node 3.
3. Also that means that node 2 have to par= se your query too for your slaves (sometimes it even leads to more child cu= rsors)

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

Hello All, We are seeing some odd behavio= ur. 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 ca= che lock" and "cursor: pin S wait on X" between its own slav= es. I mean to say the blocking session is appearing as its own slave sessio= ns. We have "parallel_degree_policy" set as MANUAL in v$parametr.= This query is running for different literals one after another multiple ti= mes 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 exe= cution 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 ta= bles used in this query are global temporary tables "on commit preserv= e row" types.


Thanks And Regards

Pap



--
Best regards,
Sayan Malaksh= inov
Oracle performance tuning engine= er
Oracle ACE Associate
http://orasql.org
=
--000000000000e54be605ba552f4b-- -- http://www.freelists.org/webpage/oracle-l