Re: 12.1 USER_CONSTRA* queries running in parallel

It's not a "fair" comparison.

The Predicate Information shows us that the parallel plan supplies the value as a PL/SQL bind variable while the serial plan shows that you're supply a particular literal value that is being converted through cursor_sharing=FORCE (or similar) to a SYS-internal bind variable. This might be sufficient to cause a change in plan and a variation in parallelism. If you want to try and make the comparison fairer, then define a couple of variables in SQL*Plus, exec :variable := 'TEMPLATE' (etc.), and then try executing the query. Bear in mind that the plan from the production pl/sql may be dependent on the first set of values that are supplied, and there may be some procedure that guarantees a "bad" choice of values that affects the plan.

Can you edit and recompile the procedure or is it a 3rd party thing that you have to work around? It looks like an unusual requirement to be in a PL/SQL procedure - is it saying "find me all the tables in the current schema which have a foreign key connection to a specific table in the current schema" ?

There should be ways to rewrite the query to make it much more efficient (or even to hint the current query to behave better), but there's no point in fussing about that until we know what's allowed. For example: your where clause and IN subquery are all dependent on the User_constraints view - so it might be worth forcing them to be applied to that view before joining to the other two views, e.g. (and this is just a quick and dirty edit of your code after a few minutes examination - there may be "nicer" options:

SELECT

        UC.CONSTRAINT_NAME


        , UC.R_CONSTRAINT_NAME

        , UC.TABLE_NAME


        , UTC.NULLABLE


FROM
        (select * from
                USER_CONSTRAINTS UC
        where
                (
                        UC.CONSTRAINT_TYPE = 'R'
                AND     UC.STATUS = 'ENABLED'
                AND     UC.R_CONSTRAINT_NAME IN (
                                SELECT  /*+ push_subq no_unnest */
CONSTRAINT_NAME
                                FROM    USER_CONSTRAINTS
                                WHERE   CONSTRAINT_TYPE IN ( 'P' ,'U')
                                AND     TABLE_NAME = 'TEMPLATES'
                        )
                AND     NOT UC.TABLE_NAME = 'TEMPLATES'
                )
        ) 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

The original took a couple of seconds to run on my system, the rewrite was almost instantaneous.
(You may be able to add an SQL patch to the original - when it's using the PL/SQL fixed form) to force the hints into the query without editing the procedure.

Regards
Jonathan Lewis

On Wed, 23 Feb 2022 at 04:46, 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
>

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