Re: 12.1 USER_CONSTRA* queries running in parallel

Thanks Timur. Yes I will try to execute this as app user and update for both the cases . So far I was connecting as sys user to pdb and then using alter session set current_schema ..

On Wed, 23 Feb 2022 at 12:49 PM, Timur Akhmadeev <timur.akhmadeev_at_gmail.com> wrote:

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

-- 
Thanks,

Goti

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