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

From: Satalabaha Oracle <"Satalabaha>
Date: Thu, 21 Nov 2024 20:08:13 +0530
Message-ID: <CAKi_91kKdhouTe18HyttMZ+4yR9Ly=USnuG2AWXSoA=ssUqZxA_at_mail.gmail.com>



Yes, this was done as well.

On Thu, Nov 21, 2024 at 9:54 AM Jon Crisler <joncrisler_at_gmail.com> wrote:

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

-- 
Thanks,
Satalabaha

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 21 2024 - 15:38:13 CET

Original text of this message