Re: a strange behavior in Oracle 12.2.0.1 by nested PLSQL cursor

Jonathan,

Thank you for this explanation and analysis. I have used the noparallel hint and is working fine now.

Regards
Ahmed Fikri

Am Mi., 30. Jan. 2019 um 10:04 Uhr schrieb Jonathan Lewis < jonathan_at_jlcomp.demon.co.uk>:

> Ahmed,
>
>
> It looks like my email is getting through to Oracle-L again, so:
>
> The MoS article was just to point out that the view definition had changed
> in 12.1.0.2 and therefore the plan COULD have changed from 11.2. to 12.1,
> and you may have been lucky that it didn't.
> Query to Dictionary ALL_CONSTRAINTS Slow after Upgrade to 12.1.0.2 (Doc ID
> 2266016.1)
>
> The further point of my note was that in 12.2 I see a completely different
> view definition from 12.1 that pushed part of the query into parallel
> execution when run from PL/SQL and the parallel execution returned the
> wrong results. (You might raise an SR with Oracle about that - if MoS works
> for you at present - because the bug is about parallel execution). That's
> why blocking parallelism is the correct fix - no_merge, by comparison, is
> an accidental fix.
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Ahmed <gherrami_at_gmail.com>
> Sent: 29 January 2019 18:15
> To: Andy Sayer
> Cc: ORACLE-L; kathy duret
> Subject: Re: a strange behavior in Oracle 12.2.0.1 by nested PLSQL cursor
>
> Ok, the noparallel hint helped, but the hidden parameters did not.
> That was very instructive for me.
>
> Query to Dictionary ALL_CONSTRAINTS Slow after Upgrade to 12.1.0.2 (Doc ID
> 2266016.1)
>
> Workaround is to set one of three hidden parameters:
> ALTER SESSION SET "_optimizer_push_pred_cost_based" = FALSE;
> ALTER SESSION SET "_optimizer_squ_bottomup" = FALSE;
> ALTER SESSION SET "_optimizer_cost_based_transformation" = 'OFF';
>
> This may help to explain why no_merge happens to work; and it's possibly
> only a matter of luck that 12.1.0.2 was okay for this person.
>
> -----------------------------------
>
> That (ed. the no_merge) may be luck rather than anything else. I just
> tried the same thing and still saw the problem (and parallel execution).
> In your case it's possible that the presence of the no_merge hint resulted
> in Oracle materializing the subquery and maybe that made it run serially -
> i.e. it was about object_statistics rather than functionality.
>
>
> P.S. Looking at the execution plans, my 12.2 (corrected from 12.1) is
> translating all_constraints to a query involving int$int$DBA_CONSTRAINTS,
> while the 12.1 query is a "more traditional" massive join of lots of tables
> - so the base problem seems to start with the appearance of a CDB-mechanism
> of all_constraints. (I'm running 12.2. from a PDB, while the 12.1 is
> non-PDB database)
>
> ----------------------
>
>

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