Library lock issue
From: Pap <oracle.developer35_at_gmail.com>
Date: Tue, 2 Feb 2021 00:58:09 +0530
Message-ID: <CAEjw_fg5yfOWT2KOkYRn+pqkMhgRJC+cQnuSPO+Uz5s2m9DnBA_at_mail.gmail.com>
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?
Date: Tue, 2 Feb 2021 00:58:09 +0530
Message-ID: <CAEjw_fg5yfOWT2KOkYRn+pqkMhgRJC+cQnuSPO+Uz5s2m9DnBA_at_mail.gmail.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Feb 01 2021 - 20:28:09 CET
- text/plain attachment: sql_query_plan.txt