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');