The docs note "Analyze fixed
objects only once, unless the workload footprint changes. You
must be connected a SYS (or a user with SYSDBA) to invoke
dbms_stats.gather_fixed_objects_stats.
Just like the workload statistics, Oracle recommends that you
analyze the x$ tables only once, and during a typical database
workload.
exec
dbms_stats.gather_schema_stats('sYS?,gather_fixed=>TRUE)
Oracle recommends a single analyze of data dictionary and x$
fixed structures for the cost-based optimizer, but it is not clear
when it is necessary to re-analyze the v$ views and x$ structures.
If it ain't broke, don't fix it. However, Oracle recommends that
major parameter changes (db_cache_size, shared_pool_size. sga_target, &c
) may be followed-up with a re-analyze using
dbms_stats.gather_fixed_stats.
gather_fixed_objects_stats usage tips
'the payback from good
statistics management and execution plans will exceed any benefit of
init.ora tuning by orders of magnitude?
Fixed Table Statistics
The data dictionary has
many fixed tables, such as X$ tables. Oracle suggests that you also collect
statistics for these objects, however, less frequently than the other normal
objects.
There is a new parameter
gather_fixed available in the procedure gather_database_stats which when set
to TRUE, collects the statistics for data dictionary fixed tables.
gather_fixed is set to FALSE by default, and causes statistics not to be
gathered for fixed tables. It may not be necessary to collect statistics
very often for data dictionary fixed tables.
Another procedure,
gather_fixed_objects_stats, is primarily aimed at collecting statistics of
fixed objects. This procedure takes the following arguments:
-
STATTAB: The user statistics table identifier
describing where to save the current statistics. Default value is NULL
for dictionary collection.
-
STATID: The optional identifier to associate
with these statistics within STATTAB. Default value is also NULL
-
STATOWN: The schema containing STATAB. Default
value is NULL.
-
NO_INVALIDATE: Do not invalidate the dependent
cursors if it is set to TRUE. Default value is FALSE.
It is also possible to
delete statistics on all fixed tables by using the new procedure
delete_fixed_objects_stats. You can also perform export or import statistics
on fixed tables by using the export_fixed_objects_stats and
import_fixed_objects_stats procedures respectively.
The following example shows
different formats:
SQL> EXEC
DBMS_STATS.GATHER_SCHEMA_STATS ('SYS', -gather_fixed=>TRUE) ;
PL/SQL procedure
successfully completed.
You can also use the
gather_fixed_objects_stats procedure to collect statistics.
SQL> EXEC
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (?ALL?);
In addition to what has
been shown above, it is also possible to collect statistics for individual
fixed tables. The procedures in the dbms_stats package that accept a table
name as an argument are enhanced to accept a fixed table name as an
argument. Since the fixed tables do not have
I/O cost, as the rows reside in memory, CBO takes into account the CPU cost
of reading rows.
Oracle suggests the
following best practices for collecting statistics.
-
Collect statistics for normal data dictionary
objects using the same interval that you would analyze objects in your
schemas. In addition, you need to analyze the dictionary objects after a
sufficient amount of DDL operations have occurred.
-
Use the procedures gather_database_stats or
gather_schema_stats with options set to GATHER AUTO. With this feature,
only the objects that need to be re-analyzed are processed every time.
-
For fixed objects, the initial collection of
statistics is usually sufficient. A subsequent collection is not usually
needed, unless workload characteristics have changed dramatically.
In the next section, we
will examine the changes introduced for the dbms_stats package.
With Oracle Database 10g,
there are some new arguments available for the dbms_stats package
subprograms. Those parameters are as follows:
granularity
This parameter is used in
subprograms such as gather_table_stats and gather_schema_stats. This
parameter indicates the granularity of the statistics that you want to
collect, particularly for partitioned tables. As an example, you can gather
the global statistics on a partitioned table, or you can gather global and
partition-level statistics. It has two options. They are: AUTO and GLOBAL
AND PARTITION.
When the AUTO option is
specified, the procedure determines the granularity based on the
partitioning type. Oracle collects global, partition-level, and
sub-partition level statistics if sub-partition method is LIST. For other
partitioned tables, only the global and partition level statistics are
generated.
When the GLOBAL AND
PARTITION option is specified, Oracle gathers the global and partition level
statistics. No sub-partition level statistics are gathered even it is
composite partitioned object.
degree
With this parameter, you
are able to specify the degree of parallelism. In general, the ?degree?
parameter allows you to parallelize the statistics gathering process. The
degree parameter can take the value of auto_degree .
When you specify the
auto_degree, Oracle will determine the degree of parallelism automatically.
It will be either 1 (serial execution) or default_degree (the system default
value based on number of CPUs and initialization parameters), according to
the size of the object. Take care if Hyper Threading is used, as you will
have less computational power than Oracle assumes.
and include the URL for the page.
