Re: 12.1 USER_CONSTRA* queries running in parallel

Hi Goti,

if you carefully check the serial plan, there're PX steps starting plan id 83 - user_constraints view. So it looks like the serial plan for some reason chooses to go serial at runtime. The parallel version of the plan uses parallelism.
Searching for X$COMVW$ I found MOS note 2033658.1 which mentions issues with PDBs.
How do you connect to the DB when you get a "serial" plan? Make sure you connect in the same way as the app user and not as SYS. I think you should see the same plan as in the PL/SQL in this case. In case you won't see the same plans I'd check outline sections of both plans and possibly parallel trace (alter session set "_px_trace"=high,all;) for both

HTH On Wed, Feb 23, 2022 at 7:46 AM Goti <aryan.goti_at_gmail.com> wrote:

> 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_NAME
> AND 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
>

-- 
Regards
Timur Akhmadeev

--
http://www.freelists.org/webpage/oracle-l