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