Re: dropping analyze objects from data dictionary

"Andy Hassall" <andy_at_andyh.co.uk> wrote in message news:85hdiv452utnk7d0hd5ebl8v16behh93s3_at_4ax.com...
> On Tue, 29 Jul 2003 17:44:55 +0100, Norman Dunbar
> <Norman.Dunbar_at_lfs.co.uk> wrote:
>
> ><Dons Nomex suit and prepares for a flame fest>
> >
> >I believe that you *can* safely analyze SYS objects under 9i (could be
> >Release 2 only though !) without any ill effects.
>
> It's 9iR2 only, and whilst the database seems to run OK, it does have
> some effects on the explain plans for certain data dictionary queries
> resulting in dire performance for queries that run fine under RBO.
>
> >Not that I have done it.
> >
> >Nor will I be doing it - unltil I get advice from Oracle, but I have
> >read (on Metalink) something that says 'it makes no difference' under
> >9i. Of couse, I cannot find the damned note when I'm looking for it, but
> >when I do, I'll repost !
>
> Isn't it in the 'common misconceptions about cost based optimizer'
> note; subject was something like that.
>
> From what I remember of the wording, Oracle hedge their bets a bit,
> saying yes it's OK, but no it's not fully regression tested, but yes
> lots of databases run OK like this, but seem to stop short of saying
> it's an officially supported configuration. (so presumably it isn't).
>
> --
> Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
> Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

From what I've read on metalink: Oracle supports analyzing the SYS and SYSTEM with a word of caution that performance might be affected.

I've had the experience that when the SYSTEM tablespace was analyzed, the replication performance (in installation and performing admin tasks)
was affected quite significantly (read: became verrry sloow). I tracked the problem to one of the views. Never did try analyzing SYS .. and subsequently deleted stats on SYSTEM also.

So, I would say ... try analyzing if you are curious .. however: make sure it hasn't affected the performance adversly in your system.
I would still wait until 10i before trying again.

.. the database version 9.2.0.3
Anurag Received on Tue Jul 29 2003 - 18:03:05 CDT