Re: dropping analyze objects from data dictionary

On 28 Jul 2003 08:10:45 -0700, amanclark_at_yahoo.co.uk (amani) wrote:

>I have an Oracle 8.1.7 Database running on a Windows 2000 server
>which, although it is medium sized at best, is and has been running
>slowly. I have noted that there are scheduled tasks running including
>an Analyze script which analyzes every schema, including SYS and
>SYSTEM.
>
>I've read that doing this can cause performance to fall significantly
>and cause problems. While there have been no huge problems so far,
>Would stopping the scripts analyzing the SYS and SYSTEM schema's give
>a potential increase in performance? If so, how would I drop the
>existing analysis data from the data dictionary? (I'm not yet very
>competent/confident at sql)
>
>Thanks for any help.

Don't analyze SYS. Ever. Analyzing fet$ and uet$ can lead to deadlocks and downed production systems. Analyzing other tables can lead to very bad execution paths against the dictionary leading to nasty performance issues.

You will want to run either 'analyze table .... delete statistics;' (if the script uses the analyze command) or exec dbms_stats.delete_table_stats('SYS','TABLE_NAME'); if the script calls dbms_stats. Received on Mon Jul 28 2003 - 20:19:07 CDT