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

Sorry, autocomplete. Of course, I meant current_dictionary

On Wed, Nov 20, 2024, 13:18 Satalabaha Oracle <satalabaha.oracle_at_gmail.com> 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')
>>>
>>> --
>>> Kind regards,
>>> Sayan
>>>
>>> On Wed, Nov 20, 2024, 12:50 Satalabaha Oracle <
>>> dmarc-noreply_at_freelists.org> wrote:
>>>
>>>> Hello Experts,
>>>>
>>>> I need your advice on troubleshooting a very slow Oracle dictionary
>>>> query after upgrading from 12.1.0.2 to 19.19. The query execution plan and
>>>> SQL monitoring details for both versions are provided in the links below.
>>>> It seems the query is being called by `OracleDatabaseMetaData.java` in the
>>>> application. This query uses a manual SQL profile and runs in a few
>>>> milliseconds in 12.1, but takes over 2 minutes in 19c. I'm unsure why the
>>>> 12.1 SQL profile isn't being utilized in 19c. In the gists below, I've
>>>> executed the SQL using the outline for the 12c query. Any guidance on this
>>>> issue would be greatly appreciated. Thanks!
>>>>
>>>>
>>>> 19c:
>>>>
>>>> https://gist.github.com/Satalabaha/eae70f3753dd1bf0401d2d49e3910170
>>>>
>>>> 12c:
>>>>
>>>> https://gist.github.com/Satalabaha/7a9ecabe423603844e249d1cdf6f2ff7
>>>>
>>>> --
>>>> Thanks,
>>>> Satalabaha
>>>>
>>>
>>
>> --
>> Thanks,
>> Satalabaha
>>
>
>
> --
> Thanks,
> Satalabaha
>

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