Re: Data Dictionary poor performance

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 Fri Jan 15 1999 - 15:24:29 CST