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

Have you tried dropping the profile and recreating it ?

On Wed, Nov 20, 2024 at 9:20 PM 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).
>
>
>
> 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