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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx>
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>
