Re: Analysing Tables and Indexes

Can I play 'devils advocate'/novice a bit and suggest that perhaps this is not the best idea?

Given that he's truncating tables/rebuilding indexes, it would depend on the time frame he does this in as to when/how often he analyzes. Analyzing an empty table - or a full one just before it gets truncated - is going to give the CBO a skewed perspective on things?

Perhaps he should look at tuning the SQL that accesses this particular table and use hinting (perhaps forced in RULE mode) to get the optimal performance given that he will know more about what is going to be accessing/updating etc it and how?

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:tb257jrjl04q37_at_beta-news.demon.nl...
>
> ""Anurag Minocha"" <aminocha_at_herold.com> wrote in message
> news:EBBA6D3A81229C42981E3AEA79BF5A4E11123D_at_jshemail.herold.com...
> > Hi,
> > How frequentlly should one analyse the tables and indexes of a database
 in
> > which where all tables are cleared (all rows deleted ) and populated
 again.
> >
> > The following steps are taken
> >
> > - Indexes Dropped
> > -All records from all tables deleted.
> > -Indexes recreated.
> >
> > Since the indexes are being recreated do they need to be analysed so
 that
> > CBO takes the best path.
> >
> > Please Help.
> >
> > Thanks
> > Anurag
> >
> >
> > --
> > Posted from sjc3-1.relay.mail.uu.net [199.171.54.122]
> > via Mailgate.ORG Server - http://www.Mailgate.ORG
>
>
> First of all, if you are deleting *all* records, you should TRUNCATE that
> table, instead of delete. Otherwise the HighWaterMark will not be reset
 and
> performance will suffer severely.
> And yes, you should analyze your *schema* (as opposed to your indexes
 only)
> by issuing
> begin
> dbms_utility.analyze_schema('<SCHEMANAME>','COMPUTE');
> end;
>
> and as your database seems to be volatile, you should repeat this action
> frequently, say once a week.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
>
>
Received on Fri Mar 16 2001 - 10:58:43 CST