Re: Library lock issue

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

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

> _at_Laurentiu
>
> As I mentioned above it was a kind of bug or kind of “we don’t know why
> when using bind variable PX slaves were refusing to share the execution
> plan of their QC”. 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’t 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
>
> _at_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.
>
>
>
> *“All 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.”*
>
>
>
> Best regards
>
> Mohamed Houri
>
> Le mar. 2 févr. 2021 à 11:10, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com>
> a écrit :
>
>> 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.
>>
>> În mar., 2 feb. 2021 la 11:57, Pap <oracle.developer35_at_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=2)
>>>
>>>
>>> Regards
>>>
>>> Pap
>>>
>>> On Tue, Feb 2, 2021 at 2:32 PM Mohamed Houri <mohamed.houri_at_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-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>
>>>>
>>>>
>
> --
>
> 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