Re: Data Dictionary poor performance

I've tryed to with bigger block size, buffer cache, shared_pool, etc. = but performances of query on Data Dictionary tables are still poor. A query that need 0.05 sec. to execute now take 0.5 sec. . The only difference is the number of tables in the Db. Now I've a schema have 4 hundred tables and the whole database have 3000 = tables.

Any Idea?

Darryl E Balaski <darryl_e_balaski_at_groton.pfizer.com> scritto = nell'articolo <369FB20D.7C72_at_groton.pfizer.com>...
> Diane wrote:
> > In article <369E1252.2309_at_groton.pfizer.com>, Darryl E Balaski
> > <darryl_e_balaski_at_groton.pfizer.com> writes
> > >I agree with Terry -- Never analyze the oracle Internal Objects.
> > >As I said before, the problem can reside in many areas -- first and
> > >foremost being a possible undersized shared_pool -- good chance you
> > >would wish to increase it.
> > >Although, there are a plethera of other reasons that would effect
> > >performance
> > >(block size, too small buffer cache, etc etc)
> > >darryl dB Balaski
> > >www.rdbms.org
> > >Terry Ball wrote:
> > >> NO, NO, NO. Never analyze the sys (dictionary included) tables.
> > >> If the dictionary tables (or any sys tables) have statics on =
them, you
> > >> should consider deleting those stats.
> > >> Terry Ball
> > >> Sr. DBA, CSG Systems
> > >>
> > >> Diane wrote:
> > >> > In article =

<916178135.19434.1.nnrp-08.9e984b29_at_news.demon.co.uk>,
> > >> > Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> writes
> > >> > >You might try putting the /*+ rule */ hint into the query.
> > >> > >Jonathan Lewis
> > >> > >Luca Minudel wrote
> > >> > >Data Dictionary poor performance
> > >> > >I'm having poor performance when querying Oracle Data =
Dictionary table,
> > >> > >especially about crowd schemas.
> > >> > Or analyzing the tables (see under administration utilities)
> > >> > --
> > >> > Diane
> > Sorry but I just had Oracle support telling me to run analyze on the
> > tables today - I am running 2.1.1 and it uses the cost based =
optimizer.
> > Oracle support has told me to reanalyze every time I do a reverse
> > engineer or a lot of updates to the tables. I assume they know what
> > they're talking about.
> > --
> > Diane
>
> Did they tell you to
> 1) Analyze your application schema tables
> or
> 2) Analyze all the tables in the SYS schema (internal oracle user who
> owns all the tables behind the v$* views).
>
> There is a difference to these two statements.
>
> I'll assume by your statement
> "Oracle support has told me to reanalyze every time I do a reverse
> engineer or a lot of updates to the tables."
>
> That you are not inserting or modifying data in the tables owned by =
user
> SYS, but your application schema.
> If this is true, then Yes, you would want to analyze your application
> schema especially when using the cost based optimizer and this would
> effect performance of the application.
>
> Your not modifying data directly in the base oracle internal tables in
> the SYS schema???
>
> darryl
>
Received on Mon Jan 18 1999 - 16:11:31 CST