CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
HOME | BROWSE | GROUPS | REFERENCE | ADD CODE | LINKS | SPONSORS
It's time to sign up for your 2010 PSOUG membership!
Click here to join PSOUG now!
Search the Reference Library pages:  
Help us help you! Take our 1-minute PSOUG survey. Free Oracle Magazines & Oracle White Papers

Oracle DBMS_STATS
Version 11.1
 
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 DiffRepElem is record (
  report clob, -- stats difference report
  maxdiffpct number); -- max stats difference (percentage)

TYPE DiffRepTab IS TABLE OF DiffRepElem;

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
20007 Extension already exists
20008 Reached the upper limit on the number of extensions

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.disable('GATHER_STATS_JOB');
 
ALTER_DATABASE_TAB_MONITORING
Deprecated in 10g dbms_stats.alter_database_tab_monitoring(
monitoring IN BOOLEAN DEFAULT TRUE,
sysobjs    IN BOOLEAN DEFAULT FALSE);
Deprecated
 
ALTER_SCHEMA_TAB_MONITORING
Deprecated in 10g dbms_stats.alter_schema_tab_monitoring(
ownname    IN VARCHAR2 DEFAULT NULL,
monitoring IN BOOLEAN  DEFAULT TRUE);
Deprecated
 
ALTER_STATS_HISTORY_RETENTION
Enable or disable autopurging of statistic histories dbms_stats.alter_stats_history_retention(retention IN NUMBER);

NULL = change to default value
   0 = never save old stats, autopurge statistics history
   1 = statistics history never purged by autopurge 
exec dbms_stats.alter_stats_history_retention(0);
 
CLEANUP_STATS_JOB_PROC (new 11g)

Undocumented: For internal use only		 dbms_stats.cleanup_stats_job_proc(
ctx       IN NUMBER,
job_owner IN VARCHAR2,
job_name  IN VARCHAR2,
sesid     IN NUMBER,
sesser    IN NUMBER);
TBD
 
CONVERT_RAW_VALUE

Converts the internal representation of a minimum or maximum value into a datatype-specific value. The minval and maxval fields of the StatRec structure as filled in by GET_COLUMN_STATS or PREPARE_COLUMN_VALUES are appropriate values for input.

Convert RAW to VARCHAR2

Overload 1		 dbms_stats.convert_raw_value(rawval IN RAW, resval OUT VARCHAR2);
set linesize 121
col data_type format a20
col column_name format a20
col low_value format a25
col high_value format a25

SELECT column_name, data_type, low_value, high_value
FROM dba_tab_cols
WHERE table_name = 'SERV_INST'
AND column_name = 'SI_STATUS';


set serveroutput on

DECLARE
 rv RAW(32) := '416374697661746564';
 vc VARCHAR2(20);
BEGIN
  dbms_stats.convert_raw_value(rv, vc);
  dbms_output.put_line(vc);
END;
/

Convert RAW to DATE

Overload 2		 dbms_stats.convert_raw_value(rawval IN RAW, resval OUT DATE);
set linesize 121
col data_type format a20
col column_name format a20
col low_value format a25
col high_value format a25

SELECT column_name, data_type, low_value, high_value
FROM dba_tab_cols
WHERE table_name = 'AIRPLANES'
AND column_name = 'ORDER_DATE';

set serveroutput on

DECLARE
 rv RAW(32) := '786B060818023A';
 dt DATE;
BEGIN
  dbms_stats.convert_raw_value(rv, dt);
  dbms_output.put_line(TO_CHAR(dt));
END;
/

Convert RAW to NUMBER

Overload 3		 dbms_stats.convert_raw_value(rawval IN RAW, resval OUT NUMBER);
set linesize 121
col data_type format a20
col column_name format a20
col low_value format a25
col high_value format a25

SELECT column_name, data_type, low_value, high_value
FROM dba_tab_cols
WHERE table_name = 'SERVERS'
AND column_name = 'LATITUDE';

set serveroutput on

DECLARE
 rv RAW(32) := 'C11E2960';
 ft FLOAT(126);
BEGIN
  dbms_stats.convert_raw_value(rv, ft);
  dbms_output.put_line(TO_CHAR(ft));
END;
/
Convert RAW to BINARY_FLOAT

Overload 4		 dbms_stats.convert_raw_value(rawval IN RAW, resval OUT BINARY_FLOAT);
TBD
Convert RAW to BINARY_DOUBLE 

Overload 5		 dbms_stats.convert_raw_value(rawval IN RAW, resval OUT BINARY_DOUBLE);
TBD
 
CONVERT_RAW_VALUE_NVARCHAR

Same as convert_raw_value for NVARCHAR		 dbms_stats.convert_raw_value(rawval IN RAW, resval OUT NVARCHAR2);
set serveroutput on

DECLARE
 rv RAW(32) := '416374697661746564';
 nvarch NVARCHAR2(20);
BEGIN
  dbms_stats.convert_raw_value_nvarchar(rv, nvarch);
  dbms_output.put_line(nvarch);
END;
/
 
CONVERT_RAW_VALUE_ROWID

Same as convert_raw_value for ROWID		 dbms_stats.convert_raw_value(rawval IN RAW, resval OUT ROWID);
CREATE TABLE t AS
SELECT rowid RID, srvr_id
FROM servers;

exec dbms_stats.gather_table_stats(USER, 'T');

set linesize 121
col data_type format a20
col column_name format a20
col low_value format a25
col high_value format a25

SELECT column_name, data_type, low_value, high_value
FROM dba_tab_cols
WHERE table_name = 'T'
AND column_name = 'RID';

set serveroutput on

DECLARE
 rv RAW(32) := '0001132701000C1400B5';
 ri ROWID;
BEGIN
  dbms_stats.convert_raw_value(rv, ri);
  dbms_output.put_line(TO_CHAR(ri));
END;
/
 
COPY_TABLE_STATS (new 11g parameters)

Copy statistics from one table partition to another		 dbms_stats.copy_table_stats(
ownname      IN VARCHAR2,
tabname      IN VARCHAR2,
srcpartname  IN VARCHAR2,
dstpartname  IN VARCHAR2,
scale_factor IN NUMBER  DEFAULT 1,
flags        IN NUMBER  DEFAULT NULL,
force        IN BOOLEAN DEFAULT FALSE);
SELECT partition_name
FROM user_tab_partitions
WHERE table_name = 'SALES';

exec dbms_stats.copy_table_stats('SH', 'SALES', 'SALES_Q3_2002', 'SALES_Q3_2003');
 
CREATE_EXTENDED_STATS (new 11g)

Creates a virtual column for a user specified column group or an expression in a table.

This allows for the creation of stats that relate to a data distribution across multiple columns in a single table.		 dbms_stats.create_extended_stats(
ownname   IN VARCHAR2,
tabname   IN VARVCHAR2,
extension IN VARCHAR2) 
RETURN VARCHAR2;
set linesize 121
set long 1000000
col column_name format a30
col data_default format a45
col histogram format a9

SELECT column_name, data_default, global_stats, user_stats, virtual_column, histogram
FROM user_tab_cols
WHERE table_name = 'SERV_INST';

SELECT dbms_stats.create_extended_stats(USER, 'SERV_INST', '(srvr_id, si_status)')
FROM dual;

analyze table serv_inst compute statistics for all columns size 16;

SELECT column_name, COUNT(*) endpoints
FROM user_histograms
WHERE table_name='SERV_INST'
GROUP BY column_name
ORDER BY column_name;

SELECT column_name, data_default, global_stats, user_stats, virtual_column, histogram
FROM user_tab_cols
WHERE table_name = 'SERV_INST';

SELECT dbms_stats.show_extended_stats_name(USER, 'SERV_INST', '(srvr_id, si_status)')
FROM dual;

SELECT dbms_metadata.get_ddl('TABLE', 'SERV_INST')
FROM dual;

exec dbms_stats.drop_extended_stats(USER, 'SERV_INST', '(srvr_id, si_status)');
 
CREATE_STAT_TABLE (new 11g parameter)

Creates The Table Required To Capture System Statistics		 dbms_stats.create_stat_table(
ownname          IN VARCHAR2,               -- schema name
stattab          IN VARCHAR2,               -- stats table name
tblspace         IN VARCHAR2 DEFAULT NULL,  -- stats table tablespace
global_temporary IN BOOLEAN  DEFAULT FALSE);
exec dbms_stats.create_stat_table(USER, 'STAT_TAB', 'UWDATA');

desc stat_tab
 
DELETE_COLUMN_STATS (new 11g parameter)

Deletes column related statistics		 dbms_stats.delete_column_stats(
ownname       IN VARCHAR2,               -- schema name
tabname       IN VARCHAR2,               -- table name
colname       IN VARCHAR2,               -- column name
partname      IN VARCHAR2 DEFAULT NULL,  -- partition name
statab        IN VARCHAR2 DEFAULT NULL,  -- user stat table name
statid        IN VARCHAR2 DEFAULT NULL,  -- optional id.
cascade_parts IN BOOLEAN  DEFAULT TRUE,  -- cascade to partitions
statown       IN VARCHAR2 DEFAULT NULL,  -- stat table owner
no_validate   IN BOOLEAN  DEFAULT        -- invalidate shared cursor
  to_no_invalidate_type(get_param('NO_INVALIDATE')),
force         IN BOOLEAN  DEFAULT FALSE, --delete locked statistics
col_stat_type IN VARCHAR2 DEFAULT 'ALL');
exec dbms_stats.delete_column_stats(USER, 'PERSON', 'PERSON_ID');
 
DELETE_DATABASE_PREFS (new 11g)

Deletes the statistics preferences of all the tables, excluding the tables owned by Oracle		 dbms_stats.delete_database_prefs(
pname   IN VARCHAR2,
add_sys IN BOOLEAN DEFAULT FALSE); -- TRUE to include SYSTEM tables
Valid pname Values
cascade
degree
estimate_percent
granularity
incremental
method_opt
no_invalidate
publish
stale_percent
exec dbms_stats.delete_database_prefs('DEGREE', FALSE);

exec dbms_stats.delete_database_prefs('ESTIMATE_PERCENT', TRUE);
 
DELETE_DATABASE_STATS (new 11g parameter)

Deletes statistics for all tables in the database		 dbms_stats.delete_database_stats(
stattab       IN VARCHAR2 DEFAULT NULL,    -- user stat table name
statid        IN VARCHAR2 DEFAULT NULL,    -- optional ident.
statown       IN VARCHAR2 DEFAULT NULL,    -- stat table schema
no_invalidate IN BOOLEAN  DEFAULT          -- invalidate shared cursor
  to_no_invalidate_type(get_param('NO_INVALIDATE')),
stattype      IN VARCHAR2 DEFAULT 'ALL',
force         IN BOOLEAN  DEFAULT FALSE);  -- delete locked stats
exec dbms_stats.delete_database_stats;
 
DELETE_DICTIONARY_STATS

Deletes statistics for all dictionary schemas (SYS and SYSTEM)		 dbms_stats.delete_dictionary_stats(
stattab       IN VARCHAR2 DEFAULT NULL,   -- stat table name
statid        IN VARCHAR2 DEFAULT NULL,   -- optional identifier
statown       IN VARCHAR2 DEFAULT NULL,   -- stat table schema
no_invalidate IN BOOLEAN  DEFAULT         -- invalidate shared cursor
  to_no_invalidate_type(get_param('NO_INVALIDATE')),
stattype      IN VARCHAR2 DEFAULT 'ALL',
force         IN BOOLEAN  DEFAULT FALSE);  -- delete locked stats
exec dbms_stats.delete_dictionary_stats;
 
DELETE_FIXED_OBJECTS_STATS

Delete statistics for all fixed objects		 dbms_stats.delete_fixed_objects_stats(
stattab       IN VARCHAR2 DEFAULT NULL,   -- stat table name
statid        IN VARCHAR2 DEFAULT NULL,   -- optional identifier
statown       IN VARCHAR2 DEFAULT NULL,   -- stat table schema
no_invalidate IN BOOLEAN  DEFAULT         -- invalidate shared cursor
  to_no_invalidate_type(get_param('NO_INVALIDATE')),
force         IN BOOLEAN  DEFAULT FALSE); -- delete locked stats
exec dbms_stats.delete_fixed_object_stats;
 
DELETE_INDEX_STATS (new 11g parameter)

Delete index related statistics		 dbms_stats.delete_index_stats(
ownname       IN VARCHAR2,                -- schema name
indname       IN VARCHAR2,                -- index name
partname      IN VARCHAR2 DEFAULT NULL,   -- partition name
stattab       IN VARCHAR2 DEFAULT NULL,   -- stat table name
statid        IN VARCHAR2 DEFAULT NULL,   -- optional identifier
cascade_parts IN BOOLEAN  DEFAULT TRUE    -- cascade to partitions
statown       IN VARCHAR2 DEFAULT NULL,   -- stat table schema
no_invalidate IN BOOLEAN  DEFAULT         -- invalidate shared cursor
  to_no_invalidate_type(get_param('NO_INVALIDATE')),
stattype      IN VARCHAR2 DEFAULT 'ALL',
force         IN BOOLEAN  DEFAULT FALSE); -- delete locked stats
exec dbms_stats.delete_index_stats(USER);
 
DELETE_PENDING_STATS (new 11g)
Deletes the private statistics that have been collected but have not been published dbms_stats.delete_pending_stats(
ownname IN VARCHAR2 DEFAULT USER, 
tabname IN VARCHAR2);
exec dbms_stats.delete_pending_stats(USER, 'SERVERS');
 
DELETE_SCHEMA_PREFS (new 11g)
Deletes the statistics preferences of all the tables owned by the specified owner name dbms_stats.delete_schema_prefs(
ownname IN VARCHAR2,
pname   IN VARCHAR2);
exec dbms_stats.delete_schema_prefs(USER, 'DEGREE');
exec dbms_stats.delete_schema_prefs(USER, 'CASCADE');
 
DELETE_SCHEMA_STATS (new 11g parameter)

Delete statistics for an entire schema		 dbms_stats.delete_schema_stats(
ownname       IN VARCHAR2,                -- schema name
stattab       IN VARCHAR2 DEFAULT NULL,   -- stat table name
statid        IN VARCHAR2 DEFAULT NULL,   -- optional identifier
statown       IN VARCHAR2 DEFAULT NULL,   -- stat table schema
no_invalidate IN BOOLEAN  DEFAULT         -- invalidate shared cursor
   to_no_invalidate_type(get_param('NO_INVALIDATE')),
stattype      IN VARCHAR2 DEFAULT 'ALL',
force         IN BOOLEAN  DEFAULT FALSE); -- delete locked stats
exec dbms_stats.delete_schema_stats(USER);
 
DELETE_SYSTEM_STATS
Delete workload statistics gathered using the 'INTERVAL', 'START' and 'STOP' options dbms_stats.delete_system_stats(
stattab IN VARCHAR2 DEFAULT NULL,  -- stat table name
statid  IN VARCHAR2 DEFAULT NULL,  -- optional identifier
statown IN VARCHAR2 DEFAULT NULL); -- stat table schema
exec dbms_stats.delete_system_stats('STAT_TAB');
 
DELETE_TABLE_PREFS (new 11g)
Deletes statistics preferences of the specified table in the specified schema dbms_stats.delete_table_prefs(
ownname IN VARCHAR2,   -- schema name
tabname IN VARCHAR2,   -- table name
pname   IN VARCHAR2);  -- statistic to delete
exec dbms_stats.delete_system_prefs(USER, 'SERVERS', 'DEGREE');
 
DELETE_TABLE_STATS (new 11g parameter)

Delete table related statistics		 dbms_stats.delete_table_stats(
ownname         IN VARCHAR2,               -- schema name
tabname         IN VARCHAR2,               -- table name
partname        IN VARCHAR2  DEFAULT NULL,  -- partition name
stattab         IN VARCHAR2  DEFAULT NULL,  -- stat table name
statid          IN VARCHAR2  DEFAULT NULL,  -- optional identifier
cascade_parts   IN BOOLEAN   DEFAULT TRUE,  -- cascade to  partitions
cascade_columns IN BOOLEAN   DEFAULT TRUE,  -- cascade to all columns
cascade_indexes IN BOOLEAN   DEFAULT TRUE, -- cascade to all indexes
statown         IN VARCHAR2  DEFAULT NULL, -- stat table schema
no_invalidate   IN BOOLEAN   DEFAULT       -- invalidate shared cursor
  to_no_invalidate_type(get_param('NO_INVALIDATE')),
stattype        IN VARCHAR2  DEFAULT 'ALL',
force           IN BOOLEAN   DEFAULT FALSE); -- delete locked stats
exec dbms_stats.delete_table_stats(USER, 'servers');
 
DIFF_TABLE_STATS_IN_HISTORY (new 11g)

Compares statistics for a table from two timestamps in past and compare the statistics as of that timestamps		 dbms_stats.diff_table_stats_in_history(
ownname      IN VARCHAR2,
tabname      IN VARCHAR2,
time1        IN TIMESTAMP WITH TIME ZONE,
time2        IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
pctthreshold IN NUMBER DEFAULT 10)
RETURN diffreptab PIPELINED;
TBD
 
DIFF_TABLE_STATS_IN_PENDING (new 11g)

Compares statistics		 dbms_stats.diff_table_stats_in_pending(
ownname      IN VARCHAR2,                 -- schema owner
tabname      IN VARCHAR2,                 -- table name
time_stamp   IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
pctthreshold IN NUMBER DEFAULT 10)        -- reporting threshhold in %
RETURN diffreptab PIPELINED;
TBD
 
DIFF_TABLE_STATS_IN_STATTAB (new 11g)

Compares statistics for a table from two different sources		 dbms_stats.diff_table_stats_in_stattab(
ownname      IN VARCHAR2,
tabname      IN VARCHAR2,
stattab1     IN VARCHAR2,
stattab2     IN VARCHAR2  DEFAULT NULL,
pctthreshold IN NUMBER   DEFAULT 10,
statid1      IN VARCHAR2  DEFAULT NULL,
statid2      IN VARCHAR2  DEFAULT NULL,
stattab1own  IN VARCHAR2  DEFAULT NULL,
stattab2own  IN VARCHAR2  DEFAULT NULL)
RETURN diffreptab PIPELINED;
TBD
 
DROP_EXTENDED_STATS (new 11g)

Drops the statistics entry that is created for the user specified extension		 dbms_stats.drop_extended_stats(
ownname   IN VARCHAR2,  -- schema name
tabname   IN VARCHAR2,  -- table name
extension IN VARCHAR2); -- column group or extension
See CREATE_EXTENDED_STATS Demo
 
DROP_STAT_TABLE
Drop a user statistics table dbms_stats.drop_stat_table(
ownname IN VARCHAR2,  -- schema name
stattab IN VARCHAR2); -- user stat table name
exec dbms_stats.drop_stat_table(USER, 'PERSON');
 
EXPORT_COLUMN_STATS

Retrieves statistics for a column and stores them in the user statistics table		 dbms_stats.export_column_stats(
ownname  IN VARCHAR2,               -- schema name
tabname  IN VARCHAR2,               -- table name
colname  IN VARCHAR2,               -- column name
partname IN VARCHAR2  DEFAULT NULL,  -- partition name
stattab  IN VARCHAR2,               -- stat table name
statid   IN VARCHAR2  DEFAULT NULL,  -- optional identifier
statown  IN VARCHAR2  DEFAULT NULL); -- stat table schema
exec dbms_stats.export_column_stats(USER, 'servers', 'srvr_id', NULL, 'STAT_TAB');
 
EXPORT_DATABASE_PREFS (new 11g)

Exports the statistics preferences of all the tables, excluding the tables owned by Oracle		 dbms_stats.export_database_prefs(
stattab IN VARCHAR2,                 -- stat table name
statid  IN VARCHAR2  DEFAULT NULL,    -- optional identifier
statown IN VARCHAR2  DEFAULT NULL,    -- stat table schema
add_sys IN BOOLEAN  DEFAULT FALSE);  -- if TRUE includes SYS tables
exec dbms_stats.export_database_prefs('STAT_TAB', statown=> USER);
 
EXPORT_DATABASE_STATS
Retrieves statistics for all objects in the database dbms_stats.export_database_stats(
stattab IN VARCHAR2             ,   -- stat table name
statid  IN VARCHAR2 DEFAULT NULL,   -- optional identifier
statown IN VARCHAR2 DEFAULT NULL);  -- stat table schema
exec dbms_stats.export_database_stats;
 
EXPORT_DICTIONARY_STATS
Retrieves statistics for all dictionary schemas (SYS and SYSTEM) dbms_stats.export_column_stats(
stattab IN VARCHAR2,                 -- stat table name
statid  IN VARCHAR2  DEFAULT NULL,   -- optional identifier
statown IN VARCHAR2  DEFAULT NULL);  -- stat table schema
exec dbms_stats.export_dictionary_stats;
 
EXPORT_FIXED_OBJECTS_STATS
Retrieves statistics for fixed tables dbms_stats.export_column_stats(
stattab IN VARCHAR2 DEFAULT NULL,   -- stat table name
statid  IN VARCHAR2 DEFAULT NULL,   -- optional identifier
statown IN VARCHAR2 DEFAULT NULL);  -- stat table schema
exec dbms_stats.export_fixed_object_stats;
 
EXPORT_INDEX_STATS

Retrieves and stores statistics for an index		 dbms_stats.export_index_stats(
ownname  IN VARCHAR2,               -- schema name
indname  IN VARCHAR2,               -- index name
partname IN VARCHAR2  DEFAULT NULL,  -- partition name
stattab  IN VARCHAR2,               -- stat table name
statid   IN VARCHAR2  DEFAULT NULL,  -- optional identifier
statown  IN VARCHAR2  DEFAULT NULL);  -- stat table schema
exec dbms_stats.export_index_stats(USER, 'uc_state_city', NULL, 'STAT_TAB');
 
EXPORT_PENDING_STATS (new 11g)

Exports the statistics gathered and stored as pending		 dbms_stats.export_pending_stats(
ownname IN VARCHAR2 DEFAULT USER, 
tabname IN VARCHAR2,
stattab IN VARCHAR2,
statid  IN VARCHAR2 DEFAULT NULL,
statown IN VARCHAR2 DEFAULT USER);
exec dbms_stats.export_pending_stats(USER, NULL, 'STAT_TAB');
 
EXPORT_SCHEMA_PREFS (new 11g)

Exports the statistics preferences of all the tables owned by the specified owner name		 dbms_stats.export_schema_prefs(
ownname IN VARCHAR2,
stattab IN VARCHAR2,
statid  IN VARCHAR2 DEFAULT NULL,
statown IN VARCHAR2 DEFAULT NULL);
exec dbms_stats.export_schema_prefs(USER, 'ESTIMATE_PERCENT');
 
EXPORT_SCHEMA_STATS

Retrieves and stores statistics for all objects in a schema		 dbms_stats.export_schema_stats(
ownname IN VARCHAR2,               -- schema name
stattab IN VARCHAR2,               -- stat table name
statid  IN VARCHAR2 DEFAULT NULL,  -- optional identifier
statown IN VARCHAR2 DEFAULT NULL); -- stat table schema
exec dbms_stats.export_schema_stats(USER, 'STAT_TAB');
 
EXPORT_SYSTEM_STATS

Retrieves and stores system statistics		 dbms_stats.export_system_stats(
stattab IN VARCHAR2,                 -- stat table name
statid  IN VARCHAR2  DEFAULT NULL,   -- optional identifier
statown In VARCHAR2  DEFAULT NULL);  -- stat table schema
exec dbms_stats.export_system_stats('STAT_TAB');
 
EXPORT_TABLE_PREFS (new 11g)

Exports statistics preferences of the specified table in the specified schema into the specified statistics table		 dbms_stats.export_table_prefs(
ownname IN VARCHAR2,
tabname IN VARCHAR2,
stattab IN VARCHAR,
statid  IN VARCHAR DEFAULT NULL,
statown IN VARCHAR DEFAULT NULL);
exec dbms_stats.export_table_prefs(USER, 'SERVERS', 'STAT_TAB');
 
EXPORT_TABLE_STATS

Retrieves and stores table statistics		 dbms_stats.export_table_stats(
ownname  IN VARCHAR2,                -- schema name
tabname  IN VARCHAR2,                -- table name
partname IN VARCHAR2 DEFAULT NULL,   -- partition name
stattab  IN VARCHAR2,                -- stat table name
statid   IN VARCHAR2 DEFAULT NULL,   -- optional identifier
cascade  IN BOOLEAN  DEFAULT TRUE,   -- TRUE = indexes too 
statown  IN VARCHAR2 DEFAULT NULL);  -- stat table schema
exec dbms_stats.export_table_stats(USER, 'servers', NULL, 'STAT_TAB', NULL, TRUE);
 
FLUSH_DATABASE_MONITORING_INFO
Flushes in-memory monitoring information for all tables in the dictionary dbms_stats.flush_database_monitoring_info;
exec dbms_stats.flush_database_monitoring_info;
 
GATHER_DATABASE_STATS (new 11g params)

Gathers statistics for all objects in the database.

Overload 1

 dbms_stats.gather_database_stats(
estimate_percent IN  NUMBER   DEFAULT
  to_estimate_percent_type(get_param('ESTIMATE_PERCENT')),
block_sample     IN  BOOLEAN  DEFAULT FALSE,
method_opt       IN  VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree           IN  NUMBER   DEFAULT
  to_degree_type(get_param('DEGREE')),
granularity      IN  VARCHAR2 DEFAULT get_param('GRANULARITY'), 
cascade          IN  BOOLEAN  DEFAULT
  to_cascade_type(get_param('CASCADE')),
stattab          IN  VARCHAR2 DEFAULT NULL, 
statid           IN  VARCHAR2 DEFAULT NULL,
options          IN  VARCHAR2 DEFAULT 'GATHER',
objlist          OUT ObjectTab,
statown          IN  VARCHAR2 DEFAULT NULL,
gather_sys       IN  BOOLEAN  DEFAULT TRUE,
no_invalidate    IN  BOOLEAN  DEFAULT
  to_no_invalidate_type(get_param('NO_INVALIDATE'))
gather_temp      IN BOOLEAN   DEFAULT FALSE,
gather_fixed     IN BOOLEAN   DEFAULT FALSE,
stattype         IN VARCHAR2  DEFAULT 'DATA');
DECLARE
 obl dbms_stats.objecttab ;
BEGIN
  obl.
  obl(1).ownname := 'UWCLASS';
  obl(2).ownname := 'SCOTT';

--  dbms_stats.gather_database_stats(objlist=>obl);
END;
/

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

Gathers statistics for all objects in the database.

Overload 2

 dbms_stats.gather_database_stats(
estimate_percent IN NUMBER   DEFAULT
  to_estimate_percent_type(get_param('ESTIMATE_PERCENT')),
block_sample     IN BOOLEAN  DEFAULT FALSE,
method_opt       IN VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree           IN NUMBER   DEFAULT
   to_degree_type(get_param('DEGREE')),
granularity      IN VARCHAR2 DEFAULT get_param('GRANULARITY'), 
cascade          IN BOOLEAN  DEFAULT
    to_cascade_type(get_param('CASCADE')),
stattab          IN VARCHAR2 DEFAULT NULL, 
statid           IN VARCHAR2 DEFAULT NULL,
options          IN VARCHAR2 DEFAULT 'GATHER',
statown          IN VARCHAR2 DEFAULT NULL,
gather_sys       IN BOOLEAN  DEFAULT TRUE,
no_invalidate    BOOLEAN DEFAULT
  to_no_invalidate_type(get_param('NO_INVALIDATE'))
gather_temp      IN BOOLEAN  DEFAULT FALSE,
gather_fixed     IN BOOLEAN  DEFAULT FALSE,
stattype         IN VARCHAR2 DEFAULT 'DATA');
exec dbms_stats.gather_database_stats(statown=>'UWCLASS');
 
GATHER_DATABASE_STATS_JOB_PROC
Undocumented: For internal use only dbms_stats.gather_database_stats_job_proc;
exec dbms_stats.gather_database_stats_job_proc;
 
GATHER_DICTIONARY_STATS (new 11g params)

Gathers statistics for dictionary schemas 'SYS', 'SYSTEM' and schemas of RDBMS components

Overload 1		 dbms_stats.gather_dictionary_stats(
comp_id          IN  VARCHAR2 DEFAULT NULL, 
estimate_percent IN  NUMBER   DEFAULT
  to_estimate_percent_type(get_param('ESTIMATE_PERCENT')),
block_sample     IN  BOOLEAN  DEFAULT FALSE,
method_opt       IN  VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree           IN  NUMBER DEFAULT
  to_degree_type(get_param('DEGREE')),
granularity      IN  VARCHAR2 DEFAULT get_param('GRANULARITY'),
cascade          IN  BOOLEAN DEFAULT
  to_cascade_type(get_param('CASCADE')),
stattab          IN  VARCHAR2 DEFAULT NULL, 
statid           IN  VARCHAR2 DEFAULT NULL,
options          IN  VARCHAR2 DEFAULT 'GATHER AUTO', 
objlist          OUT ObjectTab,
statown          IN  VARCHAR2 DEFAULT NULL,
no_invalidate    IN  BOOLEAN  DEFAULT
  to_no_invalidate_type(get_param('NO_INVALIDATE'))
stattype         IN  VARCHAR2  DEFAULT 'DATA');
TBD

Gathers statistics for dictionary schemas 'SYS', 'SYSTEM' and schemas of RDBMS components

Overload 2		 dbms_stats.gather_dictionary_stats(
comp_id          IN VARCHAR2 DEFAULT NULL, 
estimate_percent IN NUMBER   DEFAULT
  to_estimate_percent_type(get_param('ESTIMATE_PERCENT')),
block_sample     IN BOOLEAN  DEFAULT FALSE,
method_opt       IN VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree           IN NUMBER   DEFAULT
  to_degree_type(get_param('DEGREE')),
granularity      IN VARCHAR2 DEFAULT get_param('GRANULARITY'),
cascade          IN BOOLEAN DEFAULT
  to_cascade_type(get_param('CASCADE')),
stattab          IN VARCHAR2 DEFAULT NULL, 
statid           IN VARCHAR2 DEFAULT NULL,
options          IN VARCHAR2 DEFAULT 'GATHER AUTO', 
statown          IN VARCHAR2 DEFAULT NULL,
no_invalidate    IN BOOLEAN  DEFAULT
  to_no_invalidate_type (get_param('NO_INVALIDATE'))
stattype         IN  VARCHAR2  DEFAULT 'DATA');
exec dbms_stats.gather_dictionary_stats;
 
GATHER_FIXED_OBJECTS_STATS

Gathers statistics for all fixed objects (dynamic performance tables)		 dbms_stats.gather_fixed_objects_stats (
stattab       IN VARCHAR2 DEFAULT NULL,
statid        IN VARCHAR2 DEFAULT NULL,
statown       IN VARCHAR2 DEFAULT NULL, 
no_invalidate IN BOOLEAN DEFAULT
  to_no_invalidate_type(get_param('NO_INVALIDATE')));
exec dbms_stats.gather_fixed_objects_stats;
 
GATHER_INDEX_STATS (new 11g param)

Gather Index Statistics		 dbms_stats.gather_index_stats(
ownname          IN VARCHAR2, 
indname          IN VARCHAR2, 
partname         IN VARCHAR2 DEFAULT NULL,
estimate_percent IN NUMBER DEFAULT
  to_estimate_percent_type(get_param('ESTIMATE_PERCENT')),
stattab          IN VARCHAR2 DEFAULT NULL, 
statid           IN VARCHAR2 DEFAULT NULL,
statown          IN VARCHAR2 DEFAULT NULL,
degree           NUMBER DEFAULT
  to_degree_type(get_param('DEGREE')),
granularity      IN VARCHAR2 DEFAULT get_param('GRANULARITY'),
no_invalidate    IN BOOLEAN  DEFAULT
  to_no_invalidate_type(get_param('NO_INVALIDATE')),
stattype         IN VARCHAR2 DEFAULT 'DATA',
force            IN BOOLEAN  DEFAULT FALSE);
exec dbms_stats.gather_index_stats(USER, 'PK_SERVERS', stattab=>'STAT_TAB');
 
GATHER_SCHEMA_STATS

Gather Schema Statistics

Overload 1

Note: Thank you Dr. Friedrich Pfeiffer for your clarification

 dbms_stats.gather_schema_stats(
ownname          IN  VARCHAR2, 
estimate_percent IN  NUMBER   DEFAULT
  to_estimate_percent_type(get_param('ESTIMATE_PERCENT')), 
block_sample     IN  BOOLEAN  DEFAULT FALSE, 
method_opt       IN  VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree           IN  NUMBER   DEFAULT to_degree_type( get_param('DEGREE')), 
granularity      IN  VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
cascade          IN  BOOLEAN  DEFAULT
  to_cascade_type( get_param('CASCADE')), 
stattab          IN  VARCHAR2 DEFAULT NULL, 
statid           IN  VARCHAR2 DEFAULT NULL, 
options          IN  VARCHAR2 DEFAULT 'GATHER', 
objlist          OUT ObjectTab,
statown          IN  VARCHAR2 DEFAULT NULL, 
no_invalidate    IN  BOOLEAN  DEFAULT
  to_no_invalidate_type(get_param('NO_INVALIDATE')),
gather_temp      IN  BOOLEAN   DEFAULT FALSE,
gather_fixed     IN  BOOLEAN   DEFAULT FALSE,
stattype         IN  VARCHAR2  DEFAULT 'DATA',
force            IN  BOOLEAN   DEFAULT FALSE);
exec dbms_stats.gather_schema_stats(USER, cascade=>TRUE);

or

exec dbms_stats.gather_schema_stats(USER, degree=>2, options=>'GATHER STALE', cascade=>TRUE);

or

exec dbms_stats.gather_schema_stats(ownname=>'UWCLASS', degree=>8, estimate_percent=>17, cascade=>TRUE);

or

exec dbms_stats.gather_schema_stats(USER', options=>'GATHER AUTO');

or

exec dbms_stats.gather_schema_stats(USER, estimate_percent=>2.5, method_opt=>'FOR ALL COLUMNS SIZE 1', degree=>4, granularity=>'ALL', options=>'GATHER STALE', cascade=>TRUE);

Overload 2		 dbms_stats.gather_schema_stats(
ownname          IN VARCHAR2, 
estimate_percent IN NUMBER DEFAULT
  to_estimate_percent_type(get_param('ESTIMATE_PERCENT')), 
block_sample     IN BOOLEAN DEFAULT FALSE, 
method_opt       IN VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree           IN NUMBER DEFAULT
  to_degree_type(get_param('DEGREE')), 
granularity      IN VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
cascade          IN BOOLEAN DEFAULT
  to_cascade_type(get_param('CASCADE')), 
stattab          IN VARCHAR2 DEFAULT NULL, 
statid           IN VARCHAR2 DEFAULT NULL, 
options          IN VARCHAR2 DEFAULT 'GATHER', 
statown          IN VARCHAR2 DEFAULT NULL, 
no_invalidate    IN BOOLEAN DEFAULT
  to_no_invalidate_type(get_param('NO_INVALIDATE')),
gather_temp      IN BOOLEAN   DEFAULT FALSE,
gather_fixed     IN BOOLEAN   DEFAULT FALSE,
stattype         IN VARCHAR2  DEFAULT 'DATA',
force            IN BOOLEAN   DEFAULT FALSE);
See above Overload 1
 
GATHER_SYSTEM_STATS

Gather Statistics For The System		 dbms_stats.gather_system_stats(
gathering_mode IN VARCHAR2 DEFAULT 'NOWORKLOAD',
interval       IN INTEGER  DEFAULT 60,
stattab        IN VARCHAR2 DEFAULT NULL,
statid         IN VARCHAR2 DEFAULT NULL,
statown        IN VARCHAR2 DEFAULT NULL);
Gathering Modes
INTERVAL
NOWORKLOAD
START
STOP
exec dbms_stats.create_stat_table(USER, 'STAT_TAB', 'UWDATA');

exec dbms_stats.gather_system_stats('START', NULL, 'STAT_TAB');

SELECT COUNT(*)
FROM person p, person_role r, person_role_ie i
WHERE p.person_id = i.person_id
AND i.role_id = r.role_id;

exec dbms_stats.gather_system_stats('STOP', NULL, 'STAT_TAB');

exec dbms_stats.gather_system_stats('INTERVAL', 20, 'STAT_TAB');

SELECT pname, pval1
FROM aux_stats$
WHERE sname = 'SYSSTATS_MAIN';
-- collect system statistics for 720 minutes

exec dbms_stats.gather_system_stats(interval => 720, 
stattab => 'STAT_TAB', statid => 'OLTP');

-- update the dictionary with the gathered statistics
DECLARE
 jobno  NUMBER;
BEGIN
  dbms_job.submit(jobno, 'dbms_stats.import_system_stats
  (''STAT_TAB'',''OLTP'');' sysdate, 'sysdate + 1');
  COMMIT;
END;
/
 
GATHER_TABLE_STATS

Gathers table and column (and index) statistics		 dbms_stats.gather_table_stats(
ownname          IN VARCHAR2,
tabname          IN VARCHAR2,
partname         IN VARCHAR2 DEFAULT NULL,
estimate_percent IN NUMBER   DEFAULT NULL
  to_estimate_percent_type(get_param('ESTIMATE_PERCENT')),
block_sample     IN BOOLEAN  DEFAULT FALSE,
method_opt       IN VARCHAR2 DEFAULT get_param('METHOD_OPT'),
 -- or 'FOR ALL COLUMNS SIZE 1',
degree           IN NUMBER   DEFAULT
  to_degree_type(get_param('DEGREE')),
granularity      IN VARCHAR2 DEFAULT get_param('GRANULARITY'),
cascade          IN BOOLEAN  DEFAULT
  to_cascade_type(get_param('CASCADE')),
stattab          IN VARCHAR2 DEFAULT NULL,
statid           IN VARCHAR2 DEFAULT NULL,
statown          IN VARCHAR2 DEFAULT NULL,
no_invalidate    IN BOOLEAN  DEFAULT
  to_no_invalidate_type(get_param('NO_INVALIDATE')),
stattype         IN VARCHAR2 DEFAULT 'DATA',
force            IN BOOLEAN  DEFAULT FALSE);
exec dbms_stats.gather_table_stats(USER, 'SERVERS', stattab => 'STAT_TAB');
 
GENERATE_STATS

Generates object statistics from previously collected statistics of related objects		 dbms_stats.generate_stats(
ownname   IN VARCHAR2, 
objname   IN VARCHAR2,
organized IN NUMBER DEFAULT 7,
force     IN BOOLEAN DEFAULT FALSE);

Note: organized is a value between 0 and 10 with 0 indicating the highest clustering factor and 10 indicating the lowest.
exec dbms_stats.generate_stats(USER, 'NEW_TABLE');
 
GET_COLUMN_STATS

Gets all column-related information

Overload 1		 dbms_stats.get_column_stats(
ownname  IN  VARCHAR2, 
tabname  IN  VARCHAR2, 
colname  IN  VARCHAR2, 
partname IN  VARCHAR2 DEFAULT NULL,
stattab  IN  VARCHAR2 DEFAULT NULL, 
statid   IN  VARCHAR2 DEFAULT NULL,
distcnt  OUT NUMBER, 
density  OUT NUMBER,
nullcnt  OUT NUMBER, 
srec     OUT StatRec,
avgclen  OUT NUMBER,
statown  IN  VARCHAR2 DEFAULT NULL);
set serveroutput on

DECLARE
 dist NUMBER;
 dens NUMBER;
 ncnt NUMBER;
 orec dbms_stats.StatRec;
 avgc NUMBER;
BEGIN
  dbms_stats.get_column_stats(USER, 'SERVERS', 'LATITUDE', distcnt => dist, density => dens, nullcnt => ncnt, srec => orec, avgclen => avgc);

  dbms_output.put_line(TO_CHAR(dist));
  dbms_output.put_line(TO_CHAR(dens));
  dbms_output.put_line(TO_CHAR(ncnt));
  dbms_output.put_line(TO_CHAR(avgc));
END;
/

Overload 2		 dbms_stats.get_column_stats(
ownname     IN  VARCHAR2, 
tabname     IN  VARCHAR2, 
colname     IN  VARCHAR2, 
partname    IN  VARCHAR2 DEFAULT NULL,
stattab     IN  VARCHAR2 DEFAULT NULL, 
statid      IN  VARCHAR2 DEFAULT NULL,
ext_stats   OUT RAW
stattypown  OUT VARCHAR2 DEFAULT NULL, 
stattypname OUT VARCHAR2 DEFAULT NULL, 
statown     IN  VARCHAR2 DEFAULT NULL);
TBD
 
GET_INDEX_STATS (2 new 11g overloads and new params)

Get all index-related information

Overload 1		 dbms_stats.get_index_stats(
ownname   IN  VARCHAR2, 
indname   IN  VARCHAR2,
partname  IN  VARCHAR2 DEFAULT NULL,
stattab   IN  VARCHAR2 DEFAULT NULL, 
statid    IN  VARCHAR2 DEFAULT NULL,
numrows   OUT NUMBER, 
numlblks  OUT NUMBER,
numdist   OUT NUMBER, 
avglblk   OUT NUMBER,
avgdblk   OUT NUMBER, 
clstfct   OUT NUMBER,
indlevel  OUT NUMBER,
statown   IN  VARCHAR2 DEFAULT NULL,
guessq    OUT NUMBER,
cachedblk OUT NUMBER,
cachehit  OUT NUMBER);
TBD

Overload 2		 dbms_stats.get_index_stats(
ownname  IN  VARCHAR2, 
indname  IN  VARCHAR2,
partname IN  VARCHAR2 DEFAULT NULL,
stattab  IN  VARCHAR2 DEFAULT NULL, 
statid   IN  VARCHAR2 DEFAULT NULL,
numrows  OUT NUMBER,
numlblks  OUT NUMBER,
numdist  OUT NUMBER,
avglblk  OUT NUMBER,
avgdblk  OUT NUMBER,
clstfct  OUT NUMBER,
indlevel  OUT NUMBER,
statown  IN  VARCHAR2 DEFAULT NULL,
guessq   OUT NUMBER);
set serveroutput on

DECLARE
 nrow NUMBER;
 nblk NUMBER;
 numd NUMBER;
 avgl NUMBER;
 avgd NUMBER;
 cfac NUMBER;
 ilvl NUMBER;
 gues NUMBER;
BEGIN
  dbms_stats.get_index_stats(USER, 'IX_PROGRAM_ID', NULL, NULL, NULL, nrow, nblk, numd, avgl, avgd, cfac, ilvl, NULL, gues);

  dbms_output.put_line('Number of rows: ' || TO_CHAR(nrow));
  dbms_output.put_line('Number of blocks: ' || TO_CHAR(nblk));
  dbms_output.put_line('Distinct keys: ' || TO_CHAR(numd));
  dbms_output.put_line('Avg leaf blocks/key: ' || TO_CHAR(avgl));
  dbms_output.put_line('Avg data blocks/key: ' || TO_CHAR(avgd));
  dbms_output.put_line('Clustering factor: ' || TO_CHAR(cfac));
  dbms_output.put_line('Index level: ' || TO_CHAR(ilvl));
  dbms_output.put_line('IOT guess quality: ' || TO_CHAR(gues));
END;
/

Overload 3 (new 11g)		 dbms_stats.get_index_stats(
ownname   IN  VARCHAR2, 
indname   IN  VARCHAR2,
partname  IN  VARCHAR2 DEFAULT NULL,
stattab   IN  VARCHAR2 DEFAULT NULL, 
statid    IN  VARCHAR2 DEFAULT NULL,
numrows   OUT NUMBER, 
numlblks  OUT NUMBER,
numdist   OUT NUMBER, 
avglblk   OUT NUMBER,
avgdblk   OUT NUMBER, 
clstfct   OUT NUMBER,
indlevel  OUT NUMBER,
statown   IN  VARCHAR2 DEFAULT NULL);
TBD

Overload 4 (new 11g)		 dbms_stats.get_index_stats(
ownname     IN  VARCHAR2, 
indname     IN  VARCHAR2,
partname    IN  VARCHAR2 DEFAULT NULL,
stattab     IN  VARCHAR2 DEFAULT NULL, 
statid      IN  VARCHAR2 DEFAULT NULL,
ext_stats   OUT RAW,
stattypown  OUT VARCHAR2,
stattypname OUT VARCHAR2,
statown     IN  VARCHAR2 DEFAULT NULL);
TBD
 
GET_PARAM
Deprecated in 11g dbms_stats.get_param(pname IN VARCHAR2) RETURN VARCHAR2;
Deprecated: Do not use. Instead use GET_PREFS
 
GET_PREFS (new 11g)

Returns the default value of the specified preference		 dbms_stats.get_prefs(
pname   IN VARCHAR2,
ownname IN VARCHAR2 DEFAULT NULL,
tabname IN VARCHAR2 DEFAULT NULL) 
RETURN VARCHAR2;
-- demo 1
SELECT dbms_stats.get_prefs('DEGREE')
FROM dual;

SELECT dbms_stats.get_prefs('METHOD_OPT', USER)
FROM dual;

SELECT dbms_stats.get_prefs('CASCADE', USER, 'SERVERS')
FROM dual;

-- demo 2
SELECT dbms_stats.get_prefs('ESTIMATE_PERCENT')
FROM dual;

exec dbms_stats.set_global_prefs('ESTIMATE_PERCENT','5');

SELECT dbms_stats.get_prefs('ESTIMATE_PERCENT')
FROM dual;
 
GET_STATS_HISTORY_AVAILABILITY
Returns oldest timestamp where statistics history is available dbms_stats.get_stats_history_availability
RETURN TIMESTAMP WITH TIMEZONE;
SELECT dbms_stats.get_stats_history_availability
FROM dual;
 
GET_STATS_HISTORY_RETENTION
Returns the current retention value dbms_stats.get_stats_history_retention RETURN NUMBER;
SELECT dbms_stats.get_stats_history_retention
FROM dual;
 
GET_SYSTEM_STATS

Gets system statistics from stattab, or from the dictionary if stattab is NULL		 dbms_stats.get_system_stats(
status  OUT VARCHAR2,
dstart  OUT DATE,
dstop   OUT DATE,
pname   IN  VARCHAR2,
pvalue  OUT NUMBER,
stattab  IN  VARCHAR2 DEFAULT NULL, 
statid  IN  VARCHAR2 DEFAULT NULL,
statown  IN  VARCHAR2 DEFAULT NULL);

Valid Values for status
AUTOGATHERING
BADSTATS
COMPLETED
MANUALGATHERING

Valid Values for pname
cpuspeed
cpuspeednw
ioseektim
iotfrspeed
maxthr
mbrc
mreadtim
slavethr
sreadtim
TBD

set serveroutput on

DECLARE
 stat VARCHAR2(30);
 pbeg  DATE;
 pend  DATE;
 pval NUMBER;
BEGIN
  dbms_stats.get_system_stats(stat, pbeg, pend, 'cpuspeed', pval);

  dbms_output.put_line(stat);
  dbms_output.put_line(TO_CHAR(pbeg, 'MM/DD/YY HH:MI:SS'));
  dbms_output.put_line(TO_CHAR(pend, 'MM/DD/YY HH:MI:SS'));
  dbms_output.put_line(pval);
END;
/
 
GET_TABLE_STATS

Get all table-related information

Overload 1		 dbms_stats.get_table_stats(
ownname   IN  VARCHAR2, 
tabname   IN  VARCHAR2, 
partname  IN  VARCHAR2 DEFAULT NULL,
stattab   IN  VARCHAR2 DEFAULT NULL,
statid    IN  VARCHAR2 DEFAULT NULL,
numrows   OUT NUMBER, 
numblks   OUT NUMBER,
avgrlen   OUT NUMBER,
statown   IN  VARCHAR2 DEFAULT NULL);
set serveroutput on

DECLARE
 numr NUMBER;
 numb NUMBER;
 avgr NUMBER;
BEGIN
  dbms_stats.get_table_stats(USER, 'SERVERS', numrows=>numr, numblks =>numb, avgrlen=>avgr);

  dbms_output.put_line('# of rows: ' || TO_CHAR(numr));
  dbms_output.put_line('# of blocks: ' || TO_CHAR(numb));
  dbms_output.put_line('Avg row len: ' || TO_CHAR(avgr) || ' bytes');
END;
/

Overload 2		 dbms_stats.get_table_stats(
ownname   IN  VARCHAR2, 
tabname   IN  VARCHAR2, 
partname  IN  VARCHAR2 DEFAULT NULL,
stattab   IN  VARCHAR2 DEFAULT NULL,
statid    IN  VARCHAR2 DEFAULT NULL,
numrows   OUT NUMBER, 
numblks   OUT NUMBER,
avgrlen   OUT NUMBER,
statown   IN  VARCHAR2 DEFAULT NULL,
cachedblk OUT NUMBER,
cachehit  OUT NUMBER);
set serveroutput on

DECLARE
 numr NUMBER;
 numb NUMBER;
 avgr NUMBER;
 cblk NUMBER;
 chit NUMBER;
BEGIN
  dbms_stats.get_table_stats(USER, 'SERVERS', numrows=>numr, numblks =>numb, avgrlen=>avgr, cachedblk=>cblk, cachehit=>chit);

  dbms_output.put_line('# of rows: ' || TO_CHAR(numr));
  dbms_output.put_line('# of blocks: ' || TO_CHAR(numb));
  dbms_output.put_line('Avg row len: ' || TO_CHAR(avgr) || ' bytes');
  dbms_output.put_line('Cached blocks: ' || TO_CHAR(cblk));
  dbms_output.put_line('Cache hits: ' || TO_CHAR(chit));
END;
/
 
IMPORT_COLUMN_STATS

Retrieves statistics for a particular column and stores them in the dictionary		 dbms_stats.import_column_stats(
ownname       VARCHAR2, 
tabname       VARCHAR2, 
colname       VARCHAR2,
partname      VARCHAR2 DEFAULT NULL,
stattab       VARCHAR2, 
statid        VARCHAR2 DEFAULT NULL,
statown       VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN  DEFAULT
 to_no_invalidate_type (get_param('NO_INVALIDATE')),
force         BOOLEAN  DEFAULT FALSE);
TBD
 
IMPORT_DATABASE_PREFS (new 11g)

Imports the statistics preferences of all the tables, excluding the tables owned by Oracle		 dbms_stats.import_database_prefs(
stattab IN VARCHAR2,
statid  IN VARCHAR2 DEFAULT NULL,
statown IN VARCHAR2 DEFAULT NULL,
add_sys IN BOOLEAN DEFAULT FALSE);
exec dbms_stats.import_database_prefs('STAT_TAB', statown=>'UWCLASS');
 
IMPORT_DATABASE_STATS

Retrieves statistics for all objects in the database and stores them in the dictionary		 dbms_stats.import_database_stats(
stattab       IN VARCHAR2, 
statid        IN VARCHAR2 DEFAULT NULL,
statown       IN VARCHAR2 DEFAULT NULL,
no_invalidate IN BOOLEAN  DEFAULT
  to_no_invalidate_type(get_param('NO_INVALIDATE')),
force         IN BOOLEAN  DEFAULT FALSE);
TBD
 
IMPORT_DICTIONARY_STATS

Retrieves statistics for all dictionary schemas ('SYS', 'SYSTEM' and RDBMS component schemas) and stores them in the dictionary		 dbms_stats.import_dictionary_stats(
stattab       IN VARCHAR2, 
statid        IN VARCHAR2 DEFAULT NULL,
statown       IN VARCHAR2 DEFAULT NULL,
no_invalidate IN BOOLEAN  DEFAULT
  to_no_invalidate_type(get_param('NO_INVALIDATE')),
force         IN BOOLEAN  DEFAULT FALSE);
TBD
 
IMPORT_FIXED_OBJECTS_STATS

Retrieves statistics for fixed tables from the user statistics table(s) and stores them in the dictionary		 dbms_stats.import_fixed_objects_stats(
stattab       IN VARCHAR2, 
statid        IN VARCHAR2 DEFAULT NULL,
statown       IN VARCHAR2 DEFAULT NULL,
no_invalidate IN BOOLEAN  DEFAULT
  to_no_invalidate_type(get_param('NO_INVALIDATE')),
force         IN BOOLEAN  DEFAULT FALSE);
TBD
 
IMPORT_INDEX_STATS

Retrieves statistics for a particular index from the user statistics table identified by stattab and stores them in the dictionary		 dbms_stats.import_index_stats(
ownname       IN VARCHAR2, 
indname       IN VARCHAR2,
partname      IN VARCHAR2 DEFAULT NULL,
stattab       IN VARCHAR2, 
statid        IN VARCHAR2 DEFAULT NULL,
statown       IN VARCHAR2 DEFAULT NULL,
no_invalidate IN BOOLEAN  DEFAULT
  to_no_invalidate_type(get_param('NO_INVALIDATE')),
force         IN BOOLEAN  DEFAULT FALSE);
TBD
 
IMPORT_SCHEMA_PREFS (new 11g)

Imports the statistics preferences of all the tables owned by the specified owner name		 dbms_stats.import_schema_prefs(
ownname IN VARCHAR2,
stattab IN VARCHAR2,
statid  IN VARCHAR2 DEFAULT NULL,
statown IN VARCHAR2 DEFAULT NULL);
exec dbms_stats.import_schema_prefs(USER, 'STAT_TAB');
 
IMPORT_SCHEMA_STATS

Retrieves statistics for all objects in the schema identified by ownname from the user statistics table and stores them in the dictionary		 dbms_stats.import_schema_stats(
ownname       IN VARCHAR2,
stattab       IN VARCHAR2, 
statid        IN VARCHAR2 DEFAULT NULL,
statown       IN VARCHAR2 DEFAULT NULL,
no_invalidate IN BOOLEAN  DEFAULT 
  to_no_invalidate_type(get_param('NO_INVALIDATE')),
force         IN BOOLEAN  DEFAULT FALSE);
exec dbms_stats.import_schema_stats(USER, 'STAT_TAB');
 
IMPORT_SYSTEM_STATS
Retrieves system statistics from the user statistics table, identified by stattab, and stores the statistics in the dictionary dbms_stats.import_system_stats(
stattab IN VARCHAR2, 
statid  IN VARCHAR2 DEFAULT NULL,
statown IN VARCHAR2 DEFAULT NULL);
exec dbms_stats.import_system_stats('STAT_TAB');
 
IMPORT_TABLE_PREFS (new 11g)

Sets the statistics preferences of the specified table in the specified schema		 dbms_stats.import_table_prefs(
ownname IN VARCHAR2,
tabname IN VARCHAR2,
stattab IN VARCHAR2,
statid  IN VARCHAR2 DEFAULT NULL,
statown IN VARCHAR2 DEFAULT NULL);
exec dbms_stats.import_table_prefs(USER, 'SERVERS', 'STAT_TAB');
 
IMPORT_TABLE_STATS

Retrieves statistics for a particular table from the user statistics table identified by stattab and stores them in the dictionary		 dbms_stats.import_table_stats(
ownname       IN VARCHAR2, 
tabname       IN VARCHAR2,
partname      IN VARCHAR2 DEFAULT NULL,
stattab       IN VARCHAR2, 
statid        IN VARCHAR2 DEFAULT NULL,
cascade       IN BOOLEAN  DEFAULT TRUE,
statown       IN VARCHAR2 DEFAULT NULL,
no_invalidate IN BOOLEAN  DEFAULT
  to_no_invalidate_type(get_param('NO_INVALIDATE')),
force         IN BOOLEAN  DEFAULT FALSE);
exec dbms_stats.import_table_stats(USER, 'servers', stattab => 'STAT_TAB');
 
INIT_PACKAGE
Undocumented: For internal use only dbms_stats.init_package;
exec dbms_stats.init_package;
 
LOCK_PARTITION_STATS

Locks partition statistics		 dbms_stats.unlock_partition_stats(
ownname  IN VARCHAR2,
tabname  IN VARCHAR2,
partname IN VARCHAR2);
CREATE TABLE syst_part (
tx_id   NUMBER(5),
begdate DATE)
PARTITION BY SYSTEM (
PARTITION p1,
PARTITION p2,
PARTITION p3);

SELECT partition_name
FROM user_tab_partitions
WHERE table_name = 'SYST_PART';

exec dbms_stats.lock_partition_stats(USER, 'SYST_PART', 'P1');
 
LOCK_SCHEMA_STATS (new 11g param)
Locks the statistics of all tables of a schema dbms_stats.lock_schema_stats(
ownname  IN VARCHAR2,
stattype IN VARCHAR DEFAULT 'ALL'););
exec dbms_stats.lock_schema_stats(USER);
 
LOCK_TABLE_STATS
Locks the statistics of a table so that they are not updated dbms_stats.lock_table_stats(
ownname  IN VARCHAR2, 
tabname  IN VARCHAR2, 
stattype IN VARCHAR2 DEFAULT 'ALL');
exec dbms_stats.lock_table_stats(USER, 'SERVERS');
 
MERGE_COL_USAGE
Merges column usage information from a source database, specified via a dblink, into the local database dbms_stats.merge_col_usage(dblink IN VARCHAR2);
exec dbms_stats.merge_col_usage('REMOTEDB');
 
PREPARE_COLUMN_VALUES
Convert minimum and maximum values into a datatype-specific value. The minval and maxval fields
of the StatRec structure as filled in by get_column_stats or prepare_column_values.

Overload 1		 dbms_stats.prepare_column_values(
srec     IN OUT StatRec, 
charvals IN     CHARARRAY);
TBD

Overload 2		 dbms_stats.prepare_column_values(
srec     IN OUT StatRec, 
datevals IN     DATEARRAY);
TBD

Overload 3		 dbms_stats.prepare_column_values(
srec    IN OUT StatRec, 
numvals IN     NUMARRAY);
TBD

Overload 4		 dbms_stats.prepare_column_values(
srec    IN OUT StatRec, 
fltvals IN     FLTARRAY);
TBD

Overload 5		 dbms_stats.prepare_column_values(
srec    IN OUT StatRec, 
dblvals IN     DBLARRAY);
TBD

Overload 6		 dbms_stats.prepare_column_values(
srec    IN OUT StatRec, 
rawvals IN     RAWARRAY);
TBD
 
PREPARE_COLUMN_VALUES_NVARCHAR2
Convert minimum and maximum values into a datatype-specific value. The minval and maxval fields
of the StatRec structure as filled in by get_column_stats or prepare_column_values.		 dbms_stats.prepare_column_values_nvarchar2(
srec  IN OUT StatRec, 
nvmin IN     NVARCHAR2, 
nvmax IN     NVARCHAR2);
TBD
 
PREPARE_COLUMN_VALUES_ROWID
Convert minimum and maximum values into a datatype-specific value. The minval and maxval fields
of the StatRec structure as filled in by get_column_stats or prepare_column_values.		 dbms_stats.prepare_column_values_rowid(
srec  IN OUT StatRec, 
rwmin IN     ROWID, 
rwmax IN     ROWID);
TBD
 
PUBLISH_PENDING_STATS (New 11g)

Publishes the statistics gathered and stored as pending		 dbms_stats.publish_pending_stats(
ownname IN VARCHAR2 DEAULT USER, 
tabname IN VARCHAR2,
force   IN BOOLEAN DEFAULT FALSE);
exec dbms_stats.publish_pending_stats(USER, NULL, TRUE);
 
PURGE_STATS

Purge old versions of statistics saved in the dictionary		 dbms_stats.purge_stats(
before_timestamp IN TIMESTAMP WITH TIME ZONE);
DECLARE
 ts  TIMESTAMP WITH TIME ZONE;
BEGIN
  SELECT SYSTIMESTAMP
  INTO ts
  FROM dual;

  dbms_stats.purge_stats(ts);
END;
/
 
RESET_GLOBAL_PREF_DEFAULTS
Resets the default values of all parameters to  Oracle recommended values dbms_stats.reset_global_pref_defaults;
exec dbms_stats.reset_global_pref_defaults;
 
RESET_PARAM_DEFAULTS
Deprecated: Do not use dbms_stats.reset_param_defaults;
Deprecated: Do not use. Instead use RESET_GLOBAL_PREF_DEFAULTS
 
RESTORE_DATABASE_STATS

Restore statistics of all tables of the database as of a specified timestamp		 dbms_stats.restore_database_stats( 
as_of_timestamp IN TIMESTAMP WITH TIME ZONE, 
force           IN BOOLEAN DEFAULT FALSE,
no_invalidate   IN BOOLEAN DEFAULT to_no_invalidate_type(
  get_param('NO_INVALIDATE')));
set linesize 121
col cur_time format a40
col cur_time_minus_1hr format a40

SELECT SYSTIMESTAMP
FROM dual;

SELECT SYSTIMESTAMP "CUR_TIME", SYSTIMESTAMP - (SYSTIMESTAMP - TO_TIMESTAMP('17-JUL-07 07.36.08.828000')) "CUR_TIME_MINUS_1HR"
FROM dual;

DECLARE
 restore_to TIMESTAMP := SYSTIMESTAMP - ( SYSTIMESTAMP -
 TO_TIMESTAMP('17-JUL-07 07.36.08.828000'));
BEGIN
  dbms_stats.restore_database_stats(restore_to, FALSE);
END;
/
 
RESTORE_DICTIONARY_STATS

Restore statistics of all dictionary tables (tables of 'SYS', 'SYSTEM' and RDBMS component schemas) as of a specified timestamp		 dbms_stats.restore_dictionary_stats( 
as_of_timestamp IN TIMESTAMP WITH TIME ZONE, 
force           IN BOOLEAN DEFAULT FALSE,
no_invalidate   IN BOOLEAN DEFAULT to_no_invalidate_type(
  get_param('NO_INVALIDATE')));
set linesize 121
col cur_time format a40
col cur_time_minus_1hr format a40

SELECT SYSTIMESTAMP
FROM dual;

SELECT SYSTIMESTAMP "CUR_TIME", SYSTIMESTAMP - ( SYSTIMESTAMP - TO_TIMESTAMP('17-JUL-07 07.36.08.828000')) "CUR_TIME_MINUS_1HR"
FROM dual;

DECLARE
 restore_to TIMESTAMP := SYSTIMESTAMP - ( SYSTIMESTAMP -
 TO_TIMESTAMP('17-JUL-07 07.36.08.828000'));
BEGIN
  dbms_stats.restore_dictionary_stats(restore_to, FALSE);
END;
/
 
RESTORE_FIXED_OBJECTS_STATS

Restore statistics of all fixed tables as of a specified timestamp		 dbms_stats.restore_fixed_objects_stats( 
as_of_timestamp IN TIMESTAMP WITH TIME ZONE, 
force           IN BOOLEAN DEFAULT FALSE,
no_invalidate   IN BOOLEAN DEFAULT to_no_invalidate_type(
  get_param('NO_INVALIDATE')));
set linesize 121
col cur_time format a40
col cur_time_minus_1hr format a40

SELECT SYSTIMESTAMP
FROM dual;

SELECT SYSTIMESTAMP "CUR_TIME", SYSTIMESTAMP - ( SYSTIMESTAMP - TO_TIMESTAMP('17-JUL-07 07.36.08.828000')) "CUR_TIME_MINUS_1HR"
FROM dual;

DECLARE
 restore_to TIMESTAMP := SYSTIMESTAMP - ( SYSTIMESTAMP -
 TO_TIMESTAMP('17-JUL-07 07.36.08.828000'));
BEGIN
  dbms_stats.restore_fixed_objects_stats(restore_to, FALSE);
END;
/
 
RESTORE_SCHEMA_STATS

Restore statistics of all tables of a schema as of a specified timestamp		 dbms_stats.restore_schema_stats( 
ownname         IN VARCHAR2, 
as_of_timestamp IN TIMESTAMP WITH TIME ZONE, 
force           IN BOOLEAN DEFAULT FALSE,
no_invalidate   IN BOOLEAN DEFAULT to_no_invalidate_type(
  get_param('NO_INVALIDATE')));
set linesize 121
col cur_time format a40
col cur_time_minus_1hr format a40

SELECT SYSTIMESTAMP
FROM dual;

SELECT SYSTIMESTAMP "CUR_TIME", SYSTIMESTAMP - ( SYSTIMESTAMP - TO_TIMESTAMP('17-JUL-07 07.36.08.828000')) "CUR_TIME_MINUS_1HR"
FROM dual;

DECLARE
 restore_to TIMESTAMP := SYSTIMESTAMP - ( SYSTIMESTAMP -
 TO_TIMESTAMP('17-JUL-07 07.36.08.828000'));
BEGIN
  dbms_stats.restore_schema_stats(USER, restore_to, FALSE);
END;
/
 
RESTORE_SYSTEM_STATS

Restores system statistics as of a specified timestamp		 dbms_stats.restore_schema_stats( 
as_of_timestamp IN TIMESTAMP WITH TIME ZONE);
set linesize 121
col cur_time format a40
col cur_time_minus_1hr format a40

SELECT SYSTIMESTAMP
FROM dual;

SELECT SYSTIMESTAMP "CUR_TIME", SYSTIMESTAMP - ( SYSTIMESTAMP - TO_TIMESTAMP('17-JUL-07 07.36.08.828000')) "CUR_TIME_MINUS_1HR"
FROM dual;

DECLARE
 restore_to TIMESTAMP := SYSTIMESTAMP - ( SYSTIMESTAMP -
 TO_TIMESTAMP('17-JUL-07 07.36.08.828000'));
BEGIN
  dbms_stats.restore_system_stats(restore_to);
END;
/
 
RESTORE_TABLE_STATS

Restores statistics of a table as of a specified timestamp		 dbms_stats.restore_table_stats(
ownname               IN VARCHAR2, 
tabname               IN VARCHAR2, 
as_of_timestamp       IN TIMESTAMP WITH TIME ZONE,
restore_cluster_index IN BOOLEAN DEFAULT FALSE,
force                 IN BOOLEAN DEFAULT FALSE,
no_invalidate         IN BOOLEAN DEFAULT to_no_invalidate_type(
  get_param('NO_INVALIDATE')));
TBD
 
RESUME_GATHER_STATS (new 11g)
Resumes statistics gathering at the point where it was interrupted dbms_stats.resume_gather_stats;
exec dbms_stats.resume_gather_stats;
 
SET_COLUMN_STATS

Sets column related information

Overload 1		 dbms_stats.set_column_stats(
ownname       IN VARCHAR2, 
tabname       IN VARCHAR2, 
colname       IN VARCHAR2, 
partname      IN VARCHAR2 DEFAULT NULL,
stattab       IN VARCHAR2 DEFAULT NULL, 
statid        IN VARCHAR2 DEFAULT NULL,
distcnt       IN NUMBER   DEFAULT NULL,
density       IN NUMBER   DEFAULT NULL,
nullcnt       IN NUMBER   DEFAULT NULL,
srec          IN StatRec  DEFAULT NULL,
avgclen       IN NUMBER   DEFAULT NULL,
flags         IN NUMBER   DEFAULT NULL,
statown       IN VARCHAR2 DEFAULT NULL,
no_invalidate IN BOOLEAN  DEFAULT
  to_no_invalidate_type(get_param('NO_INVALIDATE')),
force         IN BOOLEAN  DEFAULT FALSE);
See Tuning (Optimizing Joins) Demos

Overload 2

Use this version for user-defined statistics		 dbms_stats.set_column_stats(
ownname       IN VARCHAR2, 
tabname       IN VARCHAR2, 
colname       IN VARCHAR2, 
partname      IN VARCHAR2 DEFAULT NULL,
stattab       IN VARCHAR2 DEFAULT NULL, 
statid        IN VARCHAR2 DEFAULT NULL,
ext_stats     IN RAW,
stattypown    IN VARCHAR2 DEFAULT NULL, 
stattypname   IN VARCHAR2 DEFAULT NULL, 
statown       IN VARCHAR2 DEFAULT NULL,
no_invalidate IN BOOLEAN DEFAULT
  to_no_invalidate_type(get_param('NO_INVALIDATE')),
force         IN BOOLEAN DEFAULT FALSE);
TBD
 
SET_DATABASE_PREFS (new 11g)

Sets the statistics preferences of all the tables, excluding the tables owned by Oracle		 dbms_stats.set_database_prefs(
pname   IN VARCHAR2,
pvalue  IN VARCHAR2,
add_sys BOOLEAN DEFAULT FALSE);
exec dbms_stats.set_database_prefs('CASCADE',
  'DBMS_STATS.AUTO_CASCADE');

exec dbms_stats.set_database_prefs('ESTIMATE_PERCENT','90');

exec dbms_stats.set_database_prefs('DEGREE','8');
 
SET_GLOBAL_PREFS (new 11g)

Sets the global statistics preferences		 dbms_stats.set_global_prefs(
pname  IN VARCHAR2,
pvalue IN VARCHAR2);

pname options:
  • 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
  • METHOD_OPT
  • NO_INVALIDATE

pval options:

Option

Description
ALL Statistics are collected for all objects in the system
AUTO Oracle decides for which objects to collect statistics
ORACLE Statistics are collected for all Oracle owned objects
exec dbms_stats.set_global_prefs('ESTIMATE_PERCENT','3');
exec dbms_stats.set_global_prefs('DEGREE','8');
 
SET_INDEX_STATS

Sets index-related information

Overload 1 		dbms_stats.set_index_stats(
ownname       IN VARCHAR2, 
indname       IN VARCHAR2,
partname      IN VARCHAR2 DEFAULT NULL,
stattab       IN VARCHAR2 DEFAULT NULL,
statid        IN VARCHAR2 DEFAULT NULL,
numrows       IN NUMBER   DEFAULT NULL,
numlblks      IN NUMBER   DEFAULT NULL,
numdist       IN NUMBER   DEFAULT NULL,
avglblk       IN NUMBER   DEFAULT NULL,
avgdblk       IN NUMBER   DEFAULT NULL,
clstfct       IN NUMBER   DEFAULT NULL,
indlevel      IN NUMBER   DEFAULT NULL,
flags         IN NUMBER   DEFAULT NULL,
statown       IN VARCHAR2 DEFAULT NULL,
no_invalidate IN BOOLEAN  DEFAULT
  to_no_invalidate_type(get_param('NO_INVALIDATE')),
guessq        IN NUMBER   DEFAULT NULL,
cachedblk     IN NUMBER   DEFAULT NULL,
cachehit      IN NUMBER   DEFUALT NULL,
force         IN BOOLEAN  DEFAULT FALSE);
See Tuning (Optimizing Joins) Demos

Overload 2

Use this version for user-defined statistics		 dbms_stats.set_index_stats(
ownname       IN VARCHAR2,
indname       IN VARCHAR2,
partname      IN VARCHAR2 DEFAULT NULL,
stattab       IN VARCHAR2 DEFAULT NULL,
statid        IN VARCHAR2 DEFAULT NULL,
ext_stats     IN RAW,
stattypown    IN VARCHAR2 DEFAULT NULL,
stattypname   IN VARCHAR2 DEFAULT NULL,
statown       IN VARCHAR2 DEFAULT NULL,
no_invalidate IN BOOLEAN  DEFAULT
  to_no_invalidate_type(get_param('NO_INVALIDATE')),
cachedblk     IN NUMBER   DEFAULT NULL,
cachehit      IN NUMBER   DEFUALT NULL,
force         IN BOOLEAN  DEFAULT FALSE);
TBD
 
SET_PARAM
Deprecated in 11g dbms_stats.set_param(pname IN VARCHAR2, pval IN VARCHAR2);
Deprecated: Do not use. Instead use SET_GLOBAL_PREFS
 
SET_SCHEMA_PREFS (new 11g)

Sets the statistics preferences of all the tables owned by the specified owner name		 dbms_stats.set_prefs(
owname IN VARCHAR2,
pname  IN VARCHAR2,
pval   IN VARCHAR2);
exec dbms_stats.set_schema_prefs(USER, 'CASCADE',
  'DBMS_STATS.AUTO_CASCADE');

exec dbms_stats.set_schema_prefs(USER 'ESTIMATE_PERCENT','90');

exec dbms_stats.set_schema_prefs(USER, 'DEGREE','8');
 
SET_SYSTEM_STATS

Sets system statistics		 dbms_stats.set_system_stats(
pname   IN VARCHAR2,
pvalue  IN NUMBER,
stattab IN VARCHAR2 DEFAULT NULL, 
statid  IN VARCHAR2 DEFAULT NULL,
statown IN VARCHAR2 DEFAULT NULL);
pname options:
Option

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)
cpuspeednw average number of CPU cycles for each second, in millions, captured for the noworkload (statistics collected using 'NOWORKLOAD' option
iotfrspeed I/O transfer speed in bytes for each millisecond
ioseektim seek time + latency time + operating system overhead time, in milliseconds
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
select * from sys.aux_stats$;

exec dbms_stats.set_system_stats(pname =>'mreadtim', pvalue =>100);

select * from sys.aux_stats$;
 
SET_TABLE_PREFS (new 11g)

Sets the statistics preferences of the specified table in the specified schema		 dbms_stats.set_table_prefs(
ownname IN VARCHAR2,
tabname IN VARCHAR2,
pname   IN VARCHAR2, 
pvalue  IN VARCHAR2);
exec dbms_stats.set_table_prefs(USER, 'SERVERS', 'CASCADE',
  'DBMS_STATS.AUTO_CASCADE');

exec dbms_stats.set_table_prefs(USER, 'SERVERS', 'ESTIMATE_PERCENT','90');

exec dbms_stats.set_table_prefs(USER, 'SERVERS', 'DEGREE','8');
 
SET_TABLE_STATS

Sets table-related information		 dbms_stats.set_table_stats(
ownname       VARCHAR2, 
tabname       VARCHAR2, 
partname      VARCHAR2 DEFAULT NULL,
stattab       VARCHAR2 DEFAULT NULL, 
statid        VARCHAR2 DEFAULT NULL,
numrows       NUMBER   DEFAULT NULL, 
numblks       NUMBER   DEFAULT NULL,
avgrlen       NUMBER   DEFAULT NULL, 
flags         NUMBER   DEFAULT NULL,
statown       VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN  DEFAULT
  to_no_invalidate_type(get_param('NO_INVALIDATE')),
cachedblk     NUMBER   DEFAULT NULL,
cachehit      NUMBER   DEFAULT NULL,
force         BOOLEAN  DEFAULT FALSE);
conn hr/hr

set linesize 121

SELECT segment_name, SUM(blocks)
FROM user_segments
GROUP BY segment_name;

set autotrace on

-- no statistics
SELECT department_id, department_name
FROM departments d
WHERE EXISTS (
SELECT NULL
FROM employees e
WHERE e.department_id = d.department_id);

-- bad statistics
exec dbms_stats.set_table_stats(USER, 'EMPLOYEES', numrows=> 1000, numblks=>100);

exec dbms_stats.set_table_stats(USER, 'DEPARTMENTS', numrows=> 10000, numblks=>1000);

SELECT department_id, department_name
FROM departments d
WHERE EXISTS (
  SELECT NULL
  FROM employees e
  WHERE e.department_id = d.department_id);

-- accurate statistics
exec dbms_stats.set_table_stats(USER, 'EMPLOYEES', numrows=> 107, numblks=>8);

exec dbms_stats.set_table_stats(USER, 'DEPARTMENTS', numrows=> 27, numblks=>8);

SELECT department_id, department_name
FROM departments d
WHERE EXISTS (
  SELECT NULL
  FROM employees e
  WHERE e.department_id = d.department_id);
 
SHOW_EXTENDED_STATS_NAME (new 11g)

Returns the name of the virtual column that is created for the user-specified extension.		 dbms_stats.show_extended_stats_name(
ownname   IN VARCHAR2,
tabname   IN VARCHAR2,
extension IN VARCHAR2) 
RETURN VARCHAR2;
See CREATE_EXTENDED_STATS Demo
 
TO_CASCADE_TYPE (new 11g)
Undocumented: For internal use only dbms_stats.to_cascade_type(cascade IN VARCHAR2) RETURN BOOLEAN;
TBD
 
TO_DEGREE_TYPE (new 11g)
Undocumented: For internal use only dbms_stats.to_degree_type(degree IN VARCHAR2) RETURN NUMBER;
TBD
 
TO_ESTIMATE_PERCENT_TYPE (new 11g)
Undocumented: For internal use only dbms_stats.to_estimate_percent_type(estimate_percent IN VARCHAR2)
RETURN NUMBER;
TBD
 
TO_NO_INVALIDATE_TYPE (new 11g)
Undocumented: For internal use only dbms_stats.to_no_invalidate_type(no_invalidate IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
TO_PUBLISH_TYPE (new 11g)
Undocumented: For internal use only dbms_stats.to_publish_type(publish IN VARCHAR2) RETURN BOOLEAN;
TBD
 
UNLOCK_PARTITION_STATS

Unlocks the statistics on a partition		 dbms_stats.unlock_partition_stats(
ownname  IN VARCHAR2,
tabname  IN VARCHAR2,
partname IN VARCHAR2);
conn / as sysdba

SELECT object_id, object_name, subobject_name
FROM dba_objects
WHERE owner = 'SH'
AND subobject_name LIKE 'SALES%Q2_2003';

SELECT obj#, part#, flags
FROM tabpart$
WHERE obj# IN (
  SELECT object_id
  FROM dba_objects
  WHERE owner = 'SH'
  AND subobject_name LIKE 'SALES%Q2_2003');

exec dbms_stats.lock_partition_stats('SH', 'SALES', 'SALES_Q2_2003');

SELECT obj#, part#, flags
FROM tabpart$
WHERE obj# IN (
  SELECT object_id
  FROM dba_objects
  WHERE owner = 'SH'
  AND subobject_name LIKE 'SALES%Q2_2003');

exec dbms_stats.unlock_partition_stats('SH', 'SALES', 'SALES_Q2_2003');

SELECT obj#, part#, flags
FROM tabpart$
WHERE obj# IN (
  SELECT object_id
  FROM dba_objects
  WHERE owner = 'SH'
  AND subobject_name LIKE 'SALES%Q2_2003');
 
UNLOCK_SCHEMA_STATS (new 11g param)
Unlocks the statistics on all the table in a schema dbms_stats.unlock_schema_stats(
ownname  IN VARCHAR2,
stattype IN VARCHAR2 DEFAULT 'ALL'););
exec dbms_stats.unlock_schema_stats('UWCLASS');
 
UNLOCK_TABLE_STATS

Unlocks the statistics on a table		 dbms_stats.unlock_table_stats(
ownname  IN VARCHAR2,
tabname  IN VARCHAR2,
stattype IN VARCHAR2 DEFAULT 'ALL'););
exec dbms_stats.unlock_table_stats(USER, 'SERVERS');
 
UPGRADE_STAT_TABLE

Upgrades user statistics on an older table		 dbms_stats.upgrade_stat_table(
ownname IN VARCHAR2,
stattab IN VARCHAR2);
exec dbms_stats.upgrade_stat_table(USER, 'STAT_TAB');
 
Related Queries
Default Jobs col schema_user format a20
col interval format a70

SELECT job, schema_user, interval
FROM dba_jobs;
 
Related Topics
DBMS_UTILITY Built-in Package
Packages
Trace
Tuning
 
Contact Us • Legal Notices and Terms of Use • Privacy
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [59 visitors online]    © 2009 psoug.org