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 4E1AC10036E783 for ; Tue, 2 Feb 2021 11:19:29 +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 B7ADD447C3; Tue, 2 Feb 2021 10:19:27 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id A542C3F85E; Tue, 2 Feb 2021 10:19:27 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612261167; bh=ce7FTJ8YbsTmlz+ek1feRGkWqsqwjmrTIINzBiEYnoc=; h=From:Sender:Sender:From; b=OpAGORKG2JFHJaHrz669KfVmmBgISSkqgCDWJhWqkA56deufWi2+XIXTF+ee5S12k sEdb72U8/rBdZBhEdhH8QBQz+2Qk+yEEh+tEbX7BbXVvi5+pM87X4NlZ1JdPcNFLWJ /Po7FJ3sp5i/vibpHfVE/YYyOSE1+gWX8P1SMskU= 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 gUT2p2AuXfTr; Tue, 2 Feb 2021 10:19:27 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 4C4EC3F970; Tue, 2 Feb 2021 10:19:25 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612261166; bh=ce7FTJ8YbsTmlz+ek1feRGkWqsqwjmrTIINzBiEYnoc=; h=From:Sender:Sender:From; b=vlPRedplgrYGhp77gA3F9986fO/e8lGKzE7DZyrAGXXvzA6Xto0Bcbwqo60WKjzBs l4pqfAMs37SS0PcGO1soo6k2Zqh/i4gMP+03cbiTtvMMvCTGn1q5YI2mTWooH9Vq4u KTDNJ8M5Xv7UL/8MQudtv3Zw5MpPfh8GR7GicYhA= Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 02 Feb 2021 10:19:24 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 00CBF3F85E for ; Tue, 2 Feb 2021 10:19:24 +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=qxSg3IIC; 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 LhVEWGXE0fce for ; Tue, 2 Feb 2021 10:19:23 +0000 (UTC) Received: from mail-ed1-f42.google.com (mail-ed1-f42.google.com [209.85.208.42]) (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 CE3A33F844 for ; Tue, 2 Feb 2021 10:19:23 +0000 (UTC) Received: by mail-ed1-f42.google.com with SMTP id y8so7103274ede.6 for ; Tue, 02 Feb 2021 02:19:23 -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=3F1rawwg3PWDCh9LMRZYyok5srLwYKFuuuIxZyRLqdQ=; b=pOTGcjVTgIzGPeCHUeWnnMf3i/4et1YAMLzkQJMZs8lF0lrdWCcgJ/OYYajpgzH0B1 Afz8pFY1eoDJSQs0TgyFFh7usJfclxt79vE55c0JZ1r/dspEeIDhRVFMzAxn5SfoStdo E6zZGVTXJ/0uuR3VNggqnFOWHLt1HS39V7diPs8OOrCX1savZ3sqW0GSKzAEpBpiLcmX lJzG5DjLK7ZQUgUKFJLWYW3UdOlcG1j4X7jDcV17GKXSnS733ylvzFIYpGa/5y+owFqz ANlDpNw3eqdQFJ/7hYn9iaUoy8K3xI5JksgdS/4UTYsTslSrqSta0DK58WFGDXL6Kcd8 IC+g== X-Gm-Message-State: AOAM530g4DBqU82IqPXyO99Uxfh8F7TqzIWmxoz+yffIxc9eujiuka6D qd8NGkiVmNDVSSC5a/LHQMnKR0P1sZVjxLRDLFIPKVB8PRs= X-Google-Smtp-Source: ABdhPJx9pwPAzxhh890CXD+hAXhgAZ8EPT/9apltY6hX4AbGGFJ0YdawiqqLr904m7iewSzP9T4cTSbdz1+GS3HqQ6s= X-Received: by 2002:aa7:d486:: with SMTP id b6mr14209981edr.96.1612261162713; Tue, 02 Feb 2021 02:19:22 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Mohamed Houri Date: Tue, 2 Feb 2021 11:19:11 +0100 Message-ID: Subject: Re: Library lock issue To: Laurentiu Oprea Cc: Pap , Chinar Aliyev , Sayan Malakshinov , Oracle L Content-Type: multipart/alternative; boundary="000000000000e384ca05ba57cdcb" X-archive-position: 78821 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 --000000000000e384ca05ba57cdcb Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable @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 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 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 tempor= ary >> 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 bin= d >> 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 us= e >> private session level stats , hopefully that will also make optimizer ge= t >> rid of the dynamic sampling part. >> >> We can't set the dynamic sampling level to zero for this query because w= e >> 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 >> 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 Q= C >>> 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-= statistic >>> >>> >>> >>> That=E2=80=99s said I haven=E2=80=99t tested how Oracle will consider t= he 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 tha= t >>> 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 b= y >>>> 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 'FAL= SE' 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 o= f them >>>>> are using private session level stats as it's gathered inside the cod= e. But >>>>> one(RTNI) is having stats set as NULL and also its locked , which mea= ns 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 sl= aves >>>>> 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 s= ql 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 No= de >>>>>>> 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 ca= se 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 you= r >>>>>>> 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 exe= cuting 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 a= ppearing >>>>>>>> as its own slave sessions. We have "parallel_degree_policy" set as= MANUAL >>>>>>>> in v$parametr. This query is running for different literals one af= ter >>>>>>>> another multiple times in a loop fashion. And all these samples lo= gged 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 para= llel >>>>>>>> slave processes of the same query are blocking each other during p= arsing >>>>>>>> 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 p= reserve >>>>>>>> row" 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 - = *Linkedin >>> Profile * >>> >>> My Twitter - MohamedHouri >>> >>> >>> --=20 Houri Mohamed Oracle DBA-Developer-Performance & Tuning Visit My - Blog Let's Connect - *Linkedin Profile * My Twitter - MohamedHouri --000000000000e384ca05ba57cdcb Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

@Laurentiu

As I mentioned above it was a kind of bug or k= ind 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 discus= sion 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 sh= are with you for this issue. Moreover, it happened in 12cR1 and, as I said above, I still ha= ve not encountered a similar issue in 12cR2, 18c, and 19c where I am using parallelism extensively

@Pap

My answer was that, probably, your para= llel parsing issue is due to the usage of GTT private statistics. So if your planned fix tentative will reveal to be usel= ess, I would then suggest checking if using GTT shared statistics will get rid o= f the library cache lock and cursor pin S wait on x wait events. If so then y= ou have to balance between sharing inadequate GTT statistics and parsing effec= ts.

=C2=A0

=E2=80=9CAll things being equal you must balance between the performance improvement brought by this new GT= T 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

=C2=A0

Best regards

Mohamed Houri


Le=C2=A0mar. 2 f=C3= =A9vr. 2021 =C3=A0=C2=A011:10, Laurentiu Oprea <laurentiu.oprea06@gmail.com> a =C3=A9crit=C2= =A0:
Hello Pap,

I was mentioning DS in the context of p= ossible bug as well. If L2 is used most probably=C2=A0you 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:
<= div>Thanks much.

For that query , I see the below = note section. And as i mentioned are using private session level stats coll= ected for two of the global temporary tables but one global temp table is h= aving null stats and so perhaps for that the dynamic sampling is triggered.= =20 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 sessi= on level and along with that we will collect the stats on all the three tab= les setting all of them to use private session level stats , hopefully that= will also make optimizer=C2=A0get rid of the dynamic sampling part.=C2=A0<= /div>

We can't set the dynamic sampling level to zer= o 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 wh= ich you are talking of.=C2=A0


Note

-----

=C2= =A0=C2=A0- 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 ha= ve 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



--

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

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