Re: Very slow Oracle dictionary query after upgrade to 19.19 from 12.1.0.2

Three pieces of information that may give you / us some clues.

1. Use dbms_xplan.display_cursor() to pull the execution plan from memory with the option "format => 'hint_report'") in the 19c run to see which of the embedded hints were used, unresolved etc. (You could also include the +outline option in the format string - to see if there are any "new" unexpected hints there that might suggest reasons why the 19c optimizer diverged from your expected path.)
2. Query v$sql_optimizer_env in both 12c and 19c to see what differences there are between the two. (See https://jonathanlewis.wordpress.com/2007/05/01/optimizer-environment/)
3. Use the expand_sql() procedure to expand the views (and recursive views) in your query to see if the underlying queries are actually the same by the time you've got to the bottom level. (I'm not sure how well this works with PDBs, I don't think I've tested in one). (See: https://jonathanlewis.wordpress.com/2012/07/10/expanding-sql/ )

Regards
Jonathan Lewis

Regards
Jonathan Lewis

On Thu, 21 Nov 2024 at 02:20, Satalabaha Oracle <dmarc-noreply_at_freelists.org> wrote:

> Thanks All for your response.
>
> Hi Mladen,
>
> To answer your question, yes, I have collected the dictionary stats and
> the fixed object stats.
>
> Hello Jonathan,
>
> Yes, you are right. In 19c , we have cursor_sharing set to FORCE in 19c
> and EXACT in 12.1. I did try to execute the query by setting the
> cursor_sharing to EXACT at session level , but the query still runs long.
> The data volume in both 12c and 19c is exactly the same for all the tables
> except ARGUMENT$ table. The 12c has 150 million records and 19c has 80
> million records (12c has got more records).
>
>
>

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