Databases Reference
In-Depth Information
Other procedures in DBMS_STATS
In this recipe, we have used some stored procedures of the
DBMS_STATS
package. The
following are some other useful procedures, to collect statistics at database, schema, table,
or index level:
EXEC DBMS_STATS.GATHER_DATABASE_STATS;
EXEC DBMS_STATS.GATHER_SCHEMA_STATS;
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'CUSTOMERS');
EXEC DBMS_STATS.GATHER_INDEX_STATS('SH', 'CUSTOMERS_YOB_BIX');
We can also use the corresponding procedures when we want to delete the statistics:
EXEC DBMS_STATS.DELETE_DATABASE_STATS;
EXEC DBMS_STATS.DELETE_SCHEMA_STATS;
EXEC DBMS_STATS.DELETE_TABLE_STATS('SH', 'CUSTOMERS');
EXEC DBMS_STATS.DELETE_INDEX_STATS('SH', 'CUSTOMERS_YOB_BIX');
From Oracle 10
g
onward, old versions of statistics are automatically saved, and we can
use the
DBA_OPTSTAT_OPERATIONS
dictionary view to explore the history of statistics
operations performed at the schema and database level. The
DBMS_STATS.RESTORE
procedure lets us restore the statistics as they were at the timestamp used as a parameter
when invoking the procedure.
Older versions of statistics are not automatically saved when
we use the (old)
ANALYZE
statement to collect statistics.
See also
F
See
Chapter
3
,
Optimizing Storage Structures
, the
Avoiding
row
chaining
and
Avoiding
row
migration
recipes
F
The
Exploring
the
optimizer
hints
,
Using
histogram
, and
Managing
stored
outlines
recipes in this chapter
Using histograms
In this recipe, we will see how to use histograms on tables to provide a detailed estimate of
value distribution inside a column.
