Consequences of Analyzing SYS and SYSTEM tables?

From: David Wagoner <dwagoner_at_arsenaldigital.com>
Date: Thu, 1 Apr 2004 14:51:52 -0500
Message-ID: <C6F17AC15C94D84EBEB96A7B7D59D80605B38B79@MONALISA.arsenaldigital.com>

I was just working in another DBA's database and found that all tables are analyzed each day, including SYS and SYSTEM. His script uses the following syntax:
analyze table SYS.table_name validate structure cascade;

Can anyone think of a good reason to do this? The "validate structure" clause tells Oracle not to use the statisitics for the optimizer, and just validates the integrity of data blocks and rows.

I've heard that you are NOT supposed to analyze SYS and SYSTEM objects, so I don't. But, what are the implications if you do? Anyone had any errors or performance problems as a result? Seems like I've read such accounts, I just don't recall where.

This DB is 9iR1, so I plan to direct the DBA to the DBMS_STATS package for gathering statistics.

Thanks.

Best regards,

David B. Wagoner
Database Administrator

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

---

Received on Thu Apr 01 2004 - 13:53:51 CST