The DBMS_STATS package is a godsend
for the Oracle DBA in managing database statistics
only for the cost based optimizer.
The package itself allows us to
create, modify, view and delete statistics from
a standard, well-defined set of package procedures.
The statistics can be gathered on tables, indexes,
columns, partitions and schemas, but note that it
does not generate statistics for clusters.
Some of its many features are:
Statistics can be computed or estimated from
a random sample
Can gather statistics for sub-partitions or
partitions
Whenever possible, dbms_stats routines will
run via parallel query or operate serially
Statistics can be generated to a statistics
table and can be imported or exported between
databases and re-loaded into the data dictionary
at any time.
This allows the DBA to experiment
with various statistics. It is recommended over
the analyse command for CBO statistics generation
because:
analyze always runs serially
analyze cant overwrite or delete some statistics
generated by dbms_stats
statistic generation via analyse for partitions
is not optimal, namely for global partition statistics.
Even so, dbms_stats does not generate
information about chained rows and the structural
integrity of segments. It should also be mentioned
that in 8i, certain DDL commands (ie. create index),
statistics are automatically generated, therefore
eliminating the need to generate statistics explicitly
after DDL command. Oracle will determine if statistics
are automatically generated based on the expected
overhead.
DBMS_OLAP (v8.1.5)
The dbms_olap package provides
a series of functions that provide analysis information
on your materalised views (summary tables). It provides
numerous statistics such as:
Cardinalities of fact tables and dimensions
Estimate the size of a summary table in bytes
and rows
Materialized view utilization
Recommendations on what views to drop, create
or retain
Verifies that the relationships in dimensions
are correct
Utilization of materialized views
is based on statistics from hypothetical workloads
generate by Oracle Trace coupled with column cardinality
statistics gathered by the analyze command
Please refer to oracle supplied
packages documentation for more details about this
package.
