I know that the bug fix was back ported to 12r1 in a specific patch, but those
notes were entangled with a confidential access to which I was not allowed to
make notes aside from what I keep in my head.
IF I recall correctly that might have been a one-off and not necessarily
bundled compatibly with 12r1’s upgrade and patches because 12r2 was coming out
with it already fixed.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Laurentiu Oprea
Sent: Tuesday, February 02, 2021 5:23 AM
To: Mohamed Houri
Cc: Pap; Chinar Aliyev; Sayan Malakshinov; Oracle L
Subject: 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@xxxxxxxxx> a scris:
@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
@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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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@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
<https://docs.google.com/uc?export=download&id=1HYf9FoBNXnnwwf-tYZFjAkMCrzg_Yddo&revid=0B-QXE3D0UMX1ZFN1d3RhcXZCODhjU3JEVkJsa1F2eEp0eDRVPQ>
Visit My :Blog <http://chinaraliyev.wordpress.com/>
Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>
<https://www.linkedin.com/in/chinaraliyev/> Linkedin Profile
My <https://twitter.com/MohamedHouri> Twitter -
<https://twitter.com/ChinarAliyev> 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 <https://twitter.com/MohamedHouri> Twitter - 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 <https://twitter.com/MohamedHouri> Twitter - MohamedHouri
<https://twitter.com/MohamedHouri>
