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

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

On Thu, Nov 21, 2024 at 6:53 AM Mladen Gogala <dmarc-noreply_at_freelists.org> wrote:

> On Wed, 2024-11-20 at 18:48 +0530, Satalabaha Oracle wrote:
>
> Hi Sayan,
>
> Additionally, I see the Oracle doc states only the below 3 values. I don't
> see CURRENT_DIRECTORY.
>
> When this type of object is queried from a PDB, the value of the
> CONTAINER_DATA initialization parameter controls the query result as
> follows:
>
> -
>
> ALL: The query returns data pertaining to both the CDB root and the PDB
> -
>
> CURRENT: The query returns only data pertaining to the PDB
> -
>
> CURRENT_DICTIONARY: The query returns only data pertaining to the PDB
>
>
> On Wed, Nov 20, 2024 at 6:43 PM Satalabaha Oracle <
> satalabaha.oracle_at_gmail.com> wrote:
>
> Hi Sayan,
>
> Thanks for the quick response.
>
> We changed this parameter to "container_data" at the database level based
> on some notes we found. I added the hint you suggested, and the query
> runtime decreased to 44 seconds. However, this is still far from the
> performance we had with 12c.
>
>
> SQL> select banner from v$version;
>
> BANNER
>
> --------------------------------------------------------------------------------
> Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
>
> 1 row selected.
>
> Elapsed: 00:00:00.01
> SQL> sho parameter container
>
> NAME
> ------------------------------------
> TYPE
>
> --------------------------------------------------------------------------------
> VALUE
> ------------------------------
> container_data
> string
> CURRENT_DICTIONARY
>
> [1]:
>
> https://gist.github.com/Satalabaha/28afc543cb43662c3934ebb836d9c627
>
> On Wed, Nov 20, 2024 at 6:23 PM Sayan Malakshinov <xt.and.r_at_gmail.com>
> wrote:
>
> Hi,
> Just add this hint:
> opt_param('container_data' 'current_directory')
>
> What Sayan meant was "*current_dictionary*". Just out of curiosity, did
> you gather system statistics and statistics for the fixed objects? Ever
> since Oracle has abolished rule based optimizer, optimizer needs statistics
> to properly deduce the execution plan. Also, you might want to run
> dbms_resource_manager.calibrate_io, to provide Oracle with more data about
> the disk drives. A fancy way of doing the same thing is running SLOB and
> then updating the values in the dictionary table manually. However, that is
> a fairly advanced procedure, not for the fainthearted.
>
>
>
> --
>
> Mladen Gogala
> Database SME
> https://dbwhisperer.wordpress.com
>
>

-- 
Thanks,
Satalabaha

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