SOS- system table were analyzed

[elintrend]

Hi All,

I accidentally ran analyze all system tables, is there a way to undo it? I ran the script and didn't pay attention of what I was running against. Now the performance is dying, what should I do to "recover it"

Please help

[Halo]

Oops. Kill session doing the analysis or bounce the db.

[elintrend]

the analyze has been done.

what to do... please help

[chrisrlong]

Whatever you ran last time, run again, but with DELETE STATISTICS.

ANALYZE TABLE x DELETE STATISTICS;

- Chris

[Halo]

Check the performance. Bounce the db. This will clean up everything and things should be fine.

[elintrend]

Thank you all, my performance is back.

Could anyone edcuate me "Why can't we ananlyze sys tables?"

[chrisrlong]

You seem pretty persistent on this one Halo . So are you saying that when the database starts up, it will remove the offending statistics?

- Chris

[Halo]

No.

[chrisrlong]

Okay, so then I'm missing something. :( Why would the performance change if he bounced the server?

elintrend, how *did* you get your performance back?

- Chris

[jdorlon]

> "Why can't we ananlyze sys tables?"

because Oracle's internal queries (sometimes called recursive SQL) don't perform well when there are statistics on the SYS tables. Having statistics on your tables when under the CHOOSE optimiser mode will cause Oracle to use the cost based optimizer. The recursive SQL was written for the rule based optimizer, becuase that's the only thing that was around in the past. Why the CBO isn't smart enough to determine good enough execution paths for Oracle's own queries is beyond me.

-John