|Oracle8i Supplied PL/SQL Packages Reference
Release 2 (8.1.6)
Part Number A76936-01
DBMS_STATS provides a mechanism for you to view and modify optimizer statistics gathered for database objects.The statistics can reside in two different locations:
Only statistics stored in the dictionary itself have an impact on the cost-based optimizer.
This package also facilitates the gathering of some statistics in parallel. The package is divided into three main sections:
Most of the
DBMS_STATS procedures include the three parameters
statid. These parameters allow you to store statistics in your own tables (outside of the dictionary), which does not affect the optimizer. Therefore, you can maintain and experiment with sets of statistics.
stattab parameter specifies the name of a table in which to hold statistics, and it is assumed that it resides in the same schema as the object for which statistics are collected (unless the
statown parameter is specified). Users may create multiple tables with different
stattab identifiers to hold separate sets of statistics.
Additionally, users can maintain different sets of statistics within a single
stattab by using the
statid parameter, which can help avoid cluttering the user's schema.
For all of the
GET procedures, if
stattab is not provided (i.e.,
NULL), then the operation works directly on the dictionary statistics; therefore, users do not need to create these statistics tables if they only plan to modify the dictionary directly. However, if
stattab is not
NULL, then the
GET operation works on the specified user statistics table, and not the dictionary.
Types for minimum/maximum values and histogram endpoints:
TYPE numarray IS VARRAY(256) OF NUMBER; TYPE datearray IS VARRAY(256) OF DATE; TYPE chararray IS VARRAY(256) OF VARCHAR2(4000); TYPE rawarray IS VARRAY(256) OF RAW(2000); type StatRec is record ( epc NUMBER, minval RAW(2000), maxval RAW(2000), bkvals NUMARRAY, novals NUMARRAY);
Types for listing stale tables: