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

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


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