Re: Library lock issue

Hello Mohamed,

Can you post which bug number you`ve hit. I`m also strugaling with downgrading situations even for queries which are ridiculously simple and I already had applied a couple of patches.

Thanks.

În mar., 2 feb. 2021 la 11:02, Mohamed Houri <mohamed.houri_at_gmail.com> a scris:

>
> 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-statistic
> <https://hourim.wordpress.com/2019/07/30/global-temporary-table-private-statistics/>
>
>
> That’s said I haven’t 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évr. 2021 à 09:42, Chinar Aliyev <chinaraliyev_at_gmail.com> a
> écrit :
>
>> 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_at_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_at_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
>>>>
>>>> În lun., 1 feb. 2021 la 23:53, Sayan Malakshinov <xt.and.r_at_gmail.com>
>>>> a scris:
>>>>
>>>>> Hi Pap,
>>>>>
>>>>> Have you tried to set parallel_force_local=true? 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_at_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
>>>>>
>>>>
>>
>> --
>> *Chinar Aliyev*
>>
>>
>> Visit My :Blog <http://chinaraliyev.wordpress.com/>
>> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
>> Profile <https://www.linkedin.com/in/chinaraliyev/>*
>>
>> My Twitter <https://twitter.com/MohamedHouri> - ChinarAliyev
>> <https://twitter.com/ChinarAliyev>
>>
>>
>
> --
>
> Houri Mohamed
>
> Oracle DBA-Developer-Performance & Tuning
>
> Visit My - Blog <http://www.hourim.wordpress.com/>
>
> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
>
> My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
> <https://twitter.com/MohamedHouri>
>
>

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