Re: dbms_utility.analyze_schema('SYS', 'COMPUTE')

Oracle does officially state that it is PERMISSIBLE to analyze the SYS schema using the dbms_utility.analyze schema package. However, if you talk to Oracle Support about it, they will RECOMMEND that you not do it.

This is not because of any locking or other conflicts. The package is coded to avoid those problems. The reason is that the objects in the SYS schema are coded to take advantage of specific rule based execution plans. Their performance can be severely impacted by analyzing them, which causes the cost-based optimizer to change the execution plans.

Hope this helps.

Vic Thomas wrote in message <72vo6v$em9$1_at_juliana.sprynet.com>...
> The documentation for the ANALYZE statement (in 7.3 and 8.0) clearly
>indicates that it should not be used on the data dictionary tables. I
>presume Oracle Corporation specifically means that ANALYZE should not be
>executed on any table, index, or cluster in the "SYS" schema.
> However, I have not been able to find any official reference material
>that specifically explains what bad things will happen and why they will
>happen when ANALYZE is executed on the "SYS" tables. Can anyone point me
to
>some Oracle documentation or a white paper that explains this? Or at least
>explain the specifics of what would go awry.
>
> In an earlier thread in this newsgroup, someone mentioned that it was
>okay to execute the "dbms_utility.analyze_schema('SYS', 'COMPUTE')"
packaged
>procedure, because it had intelligence built in such that it would not do
>those bad things to the "SYS" schema that ANALYZE would do. This sounds
>like a great approach. But, again, does Oracle have any official
>documentation or a white paper that explains this?
>
>----
>Vic Thomas
>Database Administrator
>Associated Software Consultants
>vic04_at_sprynet.com; vic_at_asconline.com
>
>
>
Received on Sat Nov 21 1998 - 11:00:55 CST