General
Note: The drawback to collecting REDUNDANT column stats is that they have to be loaded into the dictionary cache, and they have to be considered when the query is optimised. This waste shared pool space and CPU.
On the other hand, if the statistics add value about the data - which does mean EVEN on unindexed columns - queries can run faster.
Most systems should have only a few column histograms in place, but there is no obvious requirement that they should only be on indexed columns.
Source {ORACLE_HOME}/rdbms/admin/dbmsstat.sql
First Available 8.1.5
Character Sets For DBMS_STATS to run properly may require the US numeric separators. If problems exist try the following:
alter session set NLS_NUMERIC_CHARACTERS='.,';
Constants Constant Data Type Usage
AUTO_CASCADE BOOLEAN Whether to collect statistics for indexes or not
AUTO_DEGREE NUMBER Select the degree of parallelism
AUTO_INVALIDATE BOOLEAN Decide when to invalidate dependent cursors
AUTO_SAMPLE_SIZE NUMBER Indicate that auto-sample size algorithms should be used
DEFAULT_DEGREE NUMBER Used to determine the system default degree of parallelism
Default Constants Constant Data Type Value
DEFAULT_CASCADE BOOLEAN NULL
DEFAULT_DEGREE_VALUE NUMBER 32766
DEFAULT_ESTIMATE_PERCENT NUMBER 101
DEFAULT_METHOD_OPT VARCHAR2(1) 'Z'
DEFAULT_NO_INVALIDATE BOOLEAN NULL
DEFAULT_GRANULARITY VARCHAR2(1) 'Z'
DEFAULT_PUBLISH BOOLEAN TRUE
DEFAULT_INCREMENTAL BOOLEAN FALSE
DEFAULT_STALE_PERCENT NUMBER 10
DEFAULT_AUTOSTATS_TARGET VARCHAR2(1) 'Z'
Method_opt Constants Accepts:
* FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
* FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...]
size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
- integer : Number of histogram buckets: Range [1,254].
- REPEAT : Collects histograms only on columns that already have
histograms.
- AUTO : Determines the columns to collect histograms based on
data distribution and the workload of the columns.
- SKEWONLY : Determines the columns to collect histograms based on
the data distribution of the columns.
The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using the SET_PARAM Procedure.
Degree Constants Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_PARAM Procedure. NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters. The AUTO_DEGREE value determines the degree of parallelism automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object.
Granularity Constants Granularity of statistics to collect (only pertinent if the table is partitioned).
Constant Description
ALL Gathers all (subpartition, partition, and global) statistics
AUTO Determines the granularity based on the partitioning type. This is the default value
DEFAULT Gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality
GLOBAL Gathers global statistics
GLOBAL AND PARTITION gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object
PARTITION gathers partition-level statistics
SUBPARTITION gathers subpartition-level statistics
Data Types 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 fltarray IS VARRAY(256) OF BINARY_FLOAT;
TYPE dblarray IS VARRAY(256) OF BINARY_DOUBLE;
TYPE StatRec IS RECORD (
epc NUMBER,
minval RAW(2000),
maxval RAW(2000),
bkvals NUMARRAY,
novals NUMARRAY,
chvals CHARARRAY,
eavs NUMBER);
Types for listing stale tables include:
TYPE ObjectElem IS RECORD (
ownname VARCHAR2(30), -- owner
objtype VARCHAR2(6), -- 'TABLE' or 'INDEX'
objname VARCHAR2(30), -- table/index
partname VARCHAR2(30), -- partition
subpartname VARCHAR2(30), -- subpartition
confidence NUMBER); -- not used
TYPE ObjectTab IS TABLE OF ObjectElem;
/
Dependencies SELECT name
FROM dba_dependencies
WHERE referenced_name = 'DBMS_STATS'
UNION
SELECT referenced_name
FROM dba_dependencies
WHERE name = 'DBMS_STATS';
Exceptions Error Code Reason
20000 Table already exists or insufficient privileges (or) Insufficient privileges
(or) Index does not exist or insufficient privileges (or) Object does not exist or insufficient privileges
20001 Tablespace does not exist (or) Bad input value (or) Invalid or inconsistent values in the user statistics table
20002 Bad user statistics table; may need to be upgraded
20003 Unable to set system statistics (or) Unable to gather system statistics
20004 Parameter does not exist (or) Error in the INTERVAL mode: system parameter job_queue_processes must be >0
20005 Object statistics are locked
20006 Unable to restore statistics, statistics history not available
Granularity Parameters Parameter Description
ALL Gathers all (subpartition, partition, and global) stats.
AUTO Determines the granularity based on the partitioning type, and collects the global, partition level and subpartition level statistics if the subpartitioning method is LIST, and the global and partition level only otherwise. This is the default value.
DEFAULT Gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.
GLOBAL Gathers global statistics.
GLOBAL AND PARTITION Gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.
PARTITION Gathers partition-level statistics.
SUBPARTITION Gathers subpartition-level statistics.
Options Parameters Parameter Description
GATHER Gathers statistics on all objects in the schema
GATHER AUTO Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a list of processed objects
GATHER STALE Gathers statistics on stale objects as determined by looking at the *_tab_modifications views. Also, return a list of objects found to be stale
GATHER EMPTY Gathers statistics on objects which currently have no statistics. Return a list of objects found to have no statistics
LIST AUTO Returns a list of objects to be processed with GATHER AUTO
LIST STALE Returns a list of stale objects determined by looking at the *_tab_modifications views
LIST EMPTY Returns a list of objects which currently have no statistics
pname Parameter for GET and SET PARAM Parameter Description
CASCADE The default value for CASCADE set by SET_PARAM is not used by export/import procedures.It is used only by gather procedures
DEGREE Degree of parallelism
ESTIMATE_PERCENT
METHOD_OPT 'FOR COLUMNS REPEAT'
'FOR ALL COLUMNS SIZE REPEAT'
'FOR ALL COLUMNS SIZE 1'
NO_VALIDATE
pname Parameter for GET_SYSTEM_STATS Parameter Description
CPUSPEED Average number of CPU cycles for each second, in millions, captured for the workload (statistics collected using 'INTERVAL' or 'START' and 'STOP' options)
SPUSPEEDNW Average number of CPU cycles for each second, in millions, captured for the noworkload (statistics collected using 'NOWORKLOAD' option
IOSEEKTIM Seek time + latency time + operating system overhead time, in milliseconds
IOTFRSPEED I/O transfer speed in bytes for each millisecond
MAXTHR Maximum I/O system throughput, in bytes/second
MBRC Average multiblock read count for sequential read, in blocks
MREADTIM Average time to read an mbrc block at once (sequential read), in milliseconds
SLAVETHR Average slave I/O throughput, in bytes/second
SREADTIM Average time to read single block (random read), in milliseconds
System Privileges For some of the DBMS_STATS procedures one or more of the following may be required:
ANALYZE ANY DICTIONARY
ANALYZE ANY
Enable automatic statistics collection exec dbms_scheduler.enable('GATHER_STATS_JOB');
Disable automatic statistics collection exec dbms_scheduler.disalbe('GATHER_STATS_JOB');