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
