12.1 USER_CONSTRA* queries running in parallel
Date: Wed, 23 Feb 2022 10:16:01 +0530
Message-ID: <CAOzfMurDDqFDsqczq56O5hKqbsOuBF=-o63n_YB0tmnjr56F+A_at_mail.gmail.com>
Hi All,
Envt : Oracle 12.1 Jan 2019 PSU running on RHEL 7.
We have the below SQL which is embedded inside a PL/SQL procedure, which always runs in parallel. However, if I run the SQL from SQLPLUS command line, it takes a serial execution plan. I have provided the gist for both the plans below. 10053 trace (for parallel execution) states "kkopqSetDopReason: Reason why we chose this DOP is: table property." However, I couldn't notice any tables decorated with parallelism. Is there a way to identify what is causing this parallel execution plan being invoked when the SQL runs as part of a stored proc?
Query:
alter session set current_schema= APP_USER;
SELECT UC.CONSTRAINT_NAME
,UC.R_CONSTRAINT_NAME
,UC.TABLE_NAME
,UTC.NULLABLE
FROM USER_CONSTRAINTS UC JOIN USER_CONS_COLUMNS UCC ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME JOIN USER_TAB_COLUMNS UTC ON UCC.COLUMN_NAME = UTC.COLUMN_NAMEAND UC.TABLE_NAME = UTC.TABLE_NAME
WHERE (
UC.CONSTRAINT_TYPE = 'R'
AND UC.STATUS = 'ENABLED'
AND UC.R_CONSTRAINT_NAME IN (
SELECT CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE IN (
'P'
,'U'
)
AND TABLE_NAME = 'TEMPLATES'
)
AND NOT UC.TABLE_NAME = 'TEMPLATES'
);
10053 Trace Says:
Automatic degree of parallelism (AUTODOP)
Automatic degree of parallelism is disabled: Parameter. kkopqSetForceParallelProperties: Hint:no Query: compute:yes forced:no forceDop:0
kkopqSetDopReason: Reason why we chose this DOP is: table property. table property forces parallelism
Serial Plan:
https://gist.github.com/aryangoti/325f0c60e39fcceab38e980eea049508
Parallel Plan:
https://gist.github.com/aryangoti/f50de232c925100e158b8f834171d27e
Thanks,
Goti
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 23 2022 - 05:46:01 CET