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

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