Very Computer

by **Vic Thoma** » Thu, 19 Nov 1998 04:00:00

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

by **Rich Wood** » Thu, 19 Nov 1998 04:00:00

Vic,

As the Analyze command updates the data dictionary with information extracted
from the specified table/index/schema, a deadlock will most likely occur when
the Analyze command attempts to gather this information from the data dictionary
table where statistics are stored.

FYI - if the "don't do it" appears in Oracle documentation, consider it an
official statement.

> 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

by **Biju Thoma** » Thu, 19 Nov 1998 04:00:00

I guess Oracle recommends you should not analyze SYS owned objects, because
the optimizer is "tuned" for rule based optimization...

I have seen performance degradation after analyzing SYS tables. The
performance improves once I delete the statistics.

--
Thanks,
Biju Thomas

For Oracle Scripts, Tips and Links
http://www.geocities.com/SiliconValley/Peaks/4535/oracle_main.htm

> 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

by **Jonathan Lewi** » Fri, 20 Nov 1998 04:00:00

Quote:> Vic,

> As the Analyze command updates the data dictionary with information
extracted
> from the specified table/index/schema, a deadlock will most likely
occur when
> the Analyze command attempts to gather this information from the
data dictionary
> table where statistics are stored.

Specifically - when you analyze a table you may need to perform some
space
management operations for the sorts etc; i.e. inserts/deletes on
fet$ and uet$,
so if analyze these two tables and they are large, you could hit a
deadlock.

by **Vic Thoma** » Fri, 20 Nov 1998 04:00:00

Interesting. So, to paraphrase, this means that as long as ANALYZE is
not run against fet$ or uet$, then no deadlock or corruption should occur?
In practice, I am actually only interested in updating the stats for the
following tables and their indexes:
CCOL$
CDEF$
COL$
CON$
OBJ$
USER$

----
Vic Thomas
Database Administrator
Associated Software Consultants

>> Vic,

>> As the Analyze command updates the data dictionary with information
>extracted
>> from the specified table/index/schema, a deadlock will most likely
>occur when
>> the Analyze command attempts to gather this information from the
>data dictionary
>> table where statistics are stored.

>Specifically - when you analyze a table you may need to perform some
>space
>management operations for the sorts etc; i.e. inserts/deletes on
>fet$ and uet$,
>so if analyze these two tables and they are large, you could hit a
>deadlock.

by **Bob Withe** » Fri, 20 Nov 1998 04:00:00

> Interesting. So, to paraphrase, this means that as long as ANALYZE is
> not run against fet$ or uet$, then no deadlock or corruption should occur?
> In practice, I am actually only interested in updating the stats for the
> following tables and their indexes:
> CCOL$
> CDEF$
> COL$
> CON$
> OBJ$
> USER$

> ----
> Vic Thomas
> Database Administrator
> Associated Software Consultants

> >> Vic,

> >> As the Analyze command updates the data dictionary with information
> >extracted
> >> from the specified table/index/schema, a deadlock will most likely
> >occur when
> >> the Analyze command attempts to gather this information from the
> >data dictionary
> >> table where statistics are stored.

> >Specifically - when you analyze a table you may need to perform some
> >space
> >management operations for the sorts etc; i.e. inserts/deletes on
> >fet$ and uet$,
> >so if analyze these two tables and they are large, you could hit a
> >deadlock.

I seem to remember from somewhere Oracle saying that the tables in the
data dictionary were built to provide the best performance using the rule
based optimizer and that generating statistics for the tables could cause
a switch to cost based optimization and a performance bottleneck.
Perhaps this isn't true any longer. Does anyone know for sure?

Bob

--
-------------------------------------------------------------------------
Bob Withers Do or do not, there is no try

-------------------------------------------------------------------------

by **Tim Cuthbertso** » Sun, 22 Nov 1998 04:00:00

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.

> 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