Oracle DBMS_STATS
Version 21c

`
General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Cost Based Optimizer (CBO) statistics collection to improve DML and query performance
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.
AUTHID CURRENT_USER
Automatic Statistics Collection exec dbms_scheduler.enable('GATHER_STATS_JOB');

exec dbms_scheduler.disable('GATHER_STATS_JOB');
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
Name Data Type Value
ADD_GLOBAL_PREFS NUMBER 1
AUTO_CASCADE BOOLEAN NULL
AUTO_DEGREE NUMBER 32768
AUTO_INVALIDATE BOOLEAN NULL
AUTO_SAMPLE_SIZE NUMBER 0
AUTO_TABLE_CACHED_BLOCKS INTEGER 0
CCTX_SIZE NUMBER 30
DEFAULT_AUTOSTATS_TARGET VARCHAR2(1) 'Z'
DEFAULT_AUTO_TASK_INTERVAL NUMBER 900
DEFAULT_AUTO_TASK_MAX_RUN_TIME NUMBER 3600
DEFAULT_AUTO_TASK_STATUS VARCHAR2(3) 'OFF'
DEFAULT_CASCADE BOOLEAN NULL
DEFAULT_COORD_TRIGGER_SHARD BOOLEAN FALSE
DEFAULT_DEGREE NUMBER 32767
DEFAULT_DEGREE_VALUE NUMBER 32766
DEFAULT_DEL_STAT_CATEGORY VARCHAR2(100) 'OBJECT_STATS, SYNOPSES'
DEFAULT_ESTIMATE_PERCENT NUMBER 101
DEFAULT_GRANULARITY VARCHAR2(1) 'Z'
DEFAULT_INCREMENTAL BOOLEAN FALSE
DEFAULT_METHOD_OPT VARCHAR2(1) 'Z'
DEFAULT_NO_INVALIDATE BOOLEAN NULL
DEFAULT_OPTIONS VARCHAR2(1) 'Z'
DEFAULT_PUBLISH BOOLEAN TRUE
DEFAULT_ROOT_TRIGGER_PDB BOOLEAN FALSE
DEFAULT_STALE_PERCENT NUMBER 10
DEFAULT_STAT_CATEGORY VARCHAR2(100) 'Z'
DP_OPTIONS_FULL NUMBER ADD_GLOBAL_PREFS
NULL_NUMTAB dbms_utility.number_array dbms_utility.init_number_array;
PURGE_ALL TIMESTAMP WITH TIMEZONE TO_TIMESTAMP_TZ('1001-01-0101:00:00-00:00', 'YYYY-MM-DDHH:MI:SSTZH:TZM')
RECLAIM_SYNOPSIS TIMESTAMP WITH TIMEZONE TO_TIMESTAMP_TZ('1002-01-0101:00:00-00:00', 'YYYY-MM-DDHH:MI:SSTZH:TZM')
UPDATE_GLOBAL_STATS BINARY_INTEGER 8
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 (only pertinent for partitioned tables)
Constant Description
ALL Gathers all (subpartition, partition, and global) statistics
APPROX_GLOBAL AND PARTITION Similar to 'GLOBAL AND PARTITION'. But the global statistics are aggregated from partition level 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
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.
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,     -- number of values specified in charvals, datevals, etc. (2 - 256)
minval RAW(2000),
maxval RAW(2000),
bkvals NUMARRAY,   -- If a frequency distribution is desired, this array contains the
         -- number of occurrences of distinct values specified in charvals or numvals
novals NUMARRAY,   -- numeric values
chvals CHARARRAY,  -- character values
eavs   NUMBER);    -- undocumented

-- type 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

TYPE ObjectTab IS TABLE OF ObjectElem;

-- type for displaying stats difference report
TYPE DiffRepElem is record (
report     CLOB,     -- stats difference report
maxdiffpct NUMBER);  -- max stats difference (percentage)

TYPE DiffRepTab IS TABLE OF DiffRepElem;

-- type for gather_table_stats context -- internal only
TYPE CContext IS VARRAY(30) OF VARCHAR2(4000);

sys.ds_varray_4_clob VARRAY(2000000000) OF VARCHAR2(4000 CHAR);
Dependencies
ALL_INDEXES DBMS_SQL OPTSTAT_USER_PREFS$
ALL_PART_HISTOGRAMS DBMS_SQLDIAG OPT_CALIBRATION_STATS$
ALL_SUBPART_HISTOGRAMS DBMS_SQLTCB_INTERNAL PARTOBJ$
ALL_TAB_HISTGRM_PENDING_STATS DBMS_SQLTUNE PLITBLM
ALL_TAB_HISTOGRAMS DBMS_SQLTUNE_INTERNAL PRVT_ADVISOR
ANYDATA DBMS_STANDARD PRVT_REPORT_TAGS
AQ$_AGENT DBMS_STATS_ADVISOR RAWCREC
AUX_STATS$ DBMS_STATS_INTERNAL RAWCTAB
COL$ DBMS_STATS_INTERNAL_AGG RDF_APIS
COLDICTREC DBMS_STATS_LIB SCHEDULER$_EVENT_INFO
COLDICTTAB DBMS_SWRF_INTERNAL SDO_RDF
COLHISTREC DBMS_SYS_SQL SDO_RDF_INTERNAL
COLHISTTAB DBMS_UTILITY SDO_ROUTER_PARTITION
COL_USAGE$ DBMS_WORKLOAD_REPLAY_I SDO_SEM_CLI
CONFIGURE_DV DMP_SEC SDO_SEM_PERF
CREC DRVDDL SDO_SEM_UTL
CTAB DS_VARRAY_4_CLOB SELREC
DBA_PART_HISTOGRAMS DUAL SELTAB
DBA_SUBPART_HISTOGRAMS ICOL$ SEM_INDEXTYPE_IM
DBA_TAB_HISTGRM_PENDING_STATS IND$ SQL_BINDS
DBA_TAB_HISTOGRAMS INDCOMPART$ STATS_TARGET$
DBMSSTATNUMTAB INDPART$ S_SDO_RDF_INTERNAL
DBMS_APPLICATION_INFO INDSUBPART$ TAB$
DBMS_AQ IREC TABCOMPART$
DBMS_AQADM ITAB TABPART$
DBMS_ASSERT KU$_JOBDESC TABSUBPART$
DBMS_AUTO_INDEX_INTERNAL KU$_STATUS TS$
DBMS_AW KUPCC USER$
DBMS_CSX_ADMIN KUPM$MCP USER_PART_HISTOGRAMS
DBMS_DATAPUMP KUPP$PROC USER_SUBPART_HISTOGRAMS
DBMS_DDL KUPW$WORKER USER_TAB_HISTGRM_PENDING_STATS
DBMS_FEATURE_CONCURRENT_STATS LOGMNR_KRVRDLUID3 USER_TAB_HISTOGRAMS
DBMS_FEATURE_STATS_INCREMENTAL MON_MODS_ALL$ UTL_RAW
DBMS_LOB NLS_SESSION_PARAMETERS UTL_RECOMP
DBMS_LOGMNR_FFVTOLOGMNRT OBJ$ V$MYSTAT
DBMS_LOGMNR_LOGREP_DICT ODCICOLINFO V$SESSION
DBMS_METADATA_UTIL ODCICOLINFOLIST WRI$_OPTSTAT_AUX_HISTORY
DBMS_PARALLEL_EXECUTE ODCICONST WRI$_OPTSTAT_OPR
DBMS_PRIV_CAPTURE ODCIENV WRI$_OPTSTAT_OPR_TASKS
DBMS_REDEFINITION ODCIINDEXINFO WRI$_REPT_OPTSTATS
DBMS_REGISTRY ODCIOBJECT X$KQFTA
DBMS_REGISTRY_SYS ODCIOBJECTLIST X$KSPPCV
DBMS_REPORT ODCISTATSOPTIONS X$KSPPI
DBMS_SCHEDULER OPG_APIS XMLTYPE
DBMS_SMB_INTERNAL OPG_GRAPHOP XQSEQUENCE
DBMS_SPD OPG_PATH _user_stat
DBMS_SPD_INTERNAL OPTSTAT_HIST_CONTROL$ _user_stat_varray
Documented Partial
Exceptions
Error Code Reason
01465 invalid hex number
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
First Available 8.1.5
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 The percentage of blocks or rows to be sampled
METHOD_OPT 'FOR COLUMNS REPEAT'
'FOR ALL COLUMNS SIZE REPEAT'
'FOR ALL COLUMNS SIZE 100'
NO_INVALIDATE Do not invalidate cursors dependent upon the previous statistics
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
Security Model Owned by SYS with EXECUTE granted to AUDSYS, DVSYS and PUBLIC

CREATE ROLE gather_system_statistics
GRANT update, insert, select, delete ON aux_stats$ TO gather_system_statistics
GRANT update, insert, select, delete ON sys.wri$_optstat_aux_history TO gather_system_statistics
GRANT gather_system_statistics TO dba

CREATE ROLE optimizer_processing_rate
GRANT update, insert, select, delete ON opt_calibration_stats$ TO optimizer_processing_rate
GRANT optimizer_processing_rate TO dba
Source {ORACLE_HOME}/rdbms/admin/dbmsstat.sql
Related System Privileges
ANALYZE ANY DICTIONARY ANALYZE ANY GATHER_SYSTEM_STATISTICS
Subprograms
ALTER_DATABASE_TAB_MONITORING EXPORT_SCHEMA_PREFS POSTPROCESS_STATS
ALTER_SCHEMA_TAB_MONITORING EXPORT_SCHEMA_STATS PREPARE_COLUMN_VALUES
ALTER_STATS_HISTORY_RETENTION EXPORT_STATS_FOR_DP PREPARE_COLUMN_VALUES_NVARCHAR
CANCEL_ADVISOR_TASK EXPORT_SYSTEM_STATS PREPARE_COLUMN_VALUES_ROWID
CLEANUP_STATS_JOB_PROC EXPORT_TABLE_PREFS PUBLISH_PENDING_STATS
CLOB_TO_VARRAY EXPORT_TABLE_STATS PUBLISH_PENDING_SYSTEM_STATS
COLUMN_NEED_HIST FLUSH_DATABASE_MONITORING_INFO PURGE_STATS
CONFIGURE_ADVISOR_FILTER GATHER_DATABASE_STATS REMAP_STAT_TABLE
CONFIGURE_ADVISOR_OBJ_FILTER GATHER_DATABASE_STATS_JOB_PROC REPORT_ADVISOR_TASK
CONFIGURE_ADVISOR_OPR_FILTER GATHER_DICTIONARY_STATS REPORT_COL_USAGE
CONFIGURE_ADVISOR_RULE_FILTER GATHER_FIXED_OBJECTS_STATS REPORT_GATHER_AUTO_STATS
CONVERT_RAW_TO_BIN_DOUBLE GATHER_INDEX_STATS REPORT_GATHER_DATABASE_STATS
CONVERT_RAW_TO_BIN_FLOAT GATHER_PROCESSING_RATE REPORT_GATHER_DICTIONARY_STATS
CONVERT_RAW_TO_DATE GATHER_SCHEMA_STATS REPORT_GATHER_FIXED_OBJ_STATS
CONVERT_RAW_TO_NUMBER GATHER_ST_JOB_CONTINUOUS_PROC REPORT_GATHER_SCHEMA_STATS
CONVERT_RAW_TO_NVARCHAR GATHER_SYSTEM_STATS REPORT_GATHER_TABLE_STATS
CONVERT_RAW_TO_ROWID GATHER_TABLE_STATS REPORT_SINGLE_STATS_OPERATION
CONVERT_RAW_TO_VARCHAR2 GATHER_TABLE_STATS_FUNC REPORT_STATS_OPERATIONS
CONVERT_RAW_VALUE GENERATE_INDEX_STATS RESET_ADVISOR_TASK
CONVERT_RAW_VALUE_NVARCHAR GENERATE_STATS RESET_COL_USAGE
CONVERT_RAW_VALUE_ROWID GEN_SELMAP RESET_GLOBAL_PREF_DEFAULTS
CONV_RAW GET_ADVISOR_OPR_FILTER RESET_PARAM_DEFAULTS
COPY_TABLE_STATS GET_ADVISOR_RECS RESTORE_DATABASE_STATS
CREATE_ADVISOR_TASK GET_COLUMN_STATS RESTORE_DICTIONARY_STATS
CREATE_EXTENDED_STATS GET_COMPATIBLE RESTORE_FIXED_OBJECTS_STATS
CREATE_STAT_TABLE GET_INDEX_STATS RESTORE_SCHEMA_STATS
DELETE_COLUMN_STATS GET_PARAM RESTORE_SYSTEM_STATS
DELETE_DATABASE_PREFS GET_PREFS RESTORE_TABLE_STATS
DELETE_DATABASE_STATS GET_ROW_COUNT_ESTIMATE RESUME_ADVISOR_TASK
DELETE_DICTIONARY_STATS GET_STATS_HISTORY_AVAILABILITY RESUME_GATHER_STATS
DELETE_FIXED_OBJECTS_STATS GET_STATS_HISTORY_RETENTION SAVE_INMEMORY_STATS
DELETE_INDEX_STATS GET_STAT_TAB_VERSION SCRIPT_ADVISOR_TASK
DELETE_PENDING_STATS GET_SYSTEM_STATS SEED_COL_USAGE
DELETE_PENDING_SYSTEM_STATS GET_TABLE_STATS SET_ADVISOR_TASK_PARAMETER
DELETE_PROCESSING_RATE IMPLEMENT_ADVISOR_TASK SET_COLUMN_STATS
DELETE_SCHEMA_PREFS IMPORT_COLUMN_STATS SET_DATABASE_PREFS
DELETE_SCHEMA_STATS IMPORT_DATABASE_PREFS SET_GLOBAL_PREFS
DELETE_SYSTEM_STATS IMPORT_DATABASE_STATS SET_INDEX_STATS
DELETE_TABLE_PREFS IMPORT_DICTIONARY_STATS SET_PARAM
DELETE_TABLE_STATS IMPORT_FIXED_OBJECTS_STATS SET_PROCESSING_RATE
DIFF_TABLE_STATS_IN_HISTORY IMPORT_INDEX_STATS SET_SCHEMA_PREFS
DIFF_TABLE_STATS_IN_PENDING IMPORT_SCHEMA_PREFS SET_SYSTEM_STATS
DIFF_TABLE_STATS_IN_STATTAB IMPORT_SCHEMA_STATS SET_TABLE_PREFS
DROP_ADVISOR_TASK IMPORT_STATS_FOR_DP SET_TABLE_STATS
DROP_EXTENDED_STATS IMPORT_SYSTEM_STATS SHOW_EXTENDED_STATS_NAME
DROP_STAT_TABLE IMPORT_TABLE_PREFS TO_NO_INVALIDATE_TYPE
EXECUTE_ADVISOR_TASK IMPORT_TABLE_STATS TRANSFER_STATS
EXPORT_COLUMN_STATS INIT_PACKAGE UNLOCK_PARTITION_STATS
EXPORT_DATABASE_PREFS INTERRUPT_ADVISOR_TASK UNLOCK_SCHEMA_STATS
EXPORT_DATABASE_STATS LOCK_PARTITION_STATS UNLOCK_TABLE_STATS
EXPORT_DICTIONARY_STATS LOCK_SCHEMA_STATS UPGRADE_STAT_TABLE
EXPORT_FIXED_OBJECTS_STATS LOCK_TABLE_STATS VARRAY_TO_CLOB
EXPORT_INDEX_STATS MERGE_COL_USAGE Related Queries
EXPORT_PENDING_STATS MERGE_STATS_PARTITION_EXCHANGE Related Demos
EXPORT_PENDING_SYSTEM_STATS POSTPROCESS_INDSTATS  
 
ALTER_DATABASE_TAB_MONITORING
Deprecated as of 10gR1 dbms_stats.alter_database_tab_monitoring(
monitoring IN BOOLEAN DEFAULT TRUE,
sysobjs    IN BOOLEAN DEFAULT FALSE);
TBD
 
ALTER_SCHEMA_TAB_MONITORING
Deprecated as of 10gR1 dbms_stats.alter_schema_tab_monitoring(
ownname    IN VARCHAR2 DEFAULT NULL,
monitoring IN BOOLEAN  DEFAULT TRUE);
TBD
 
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);
 
CANCEL_ADVISOR_TASK
Cancels a statistics advisor task execution. All intermediate results of the current execution will be removed from the task. dbms_stats.cancel_advisor_task(task_name IN VARCHAR2);
col task_name format a30

SELECT task_name
FROM dba_advisor_tasks
WHERE execution_start IS NOT NULL
AND execution_end IS NULL;

TASK_NAME
---------------------
ADDM:549318987_1_580

exec dbms_stats.cancel_advisor_task('ADDM:549318987_1_580');
 
CLEANUP_STATS_JOB_PROC
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
 
CLOB_TO_VARRAY
Converts a CLOB to multiple elements of a VARRAY dbms_stats.clob_to_varray(cl IN CLOB) RETURN ds_varray_4_clob;
DECLARE
 c CLOB;
 v ds_varray_4_clob;
BEGIN
  c := 'A' || RPAD('A', 3999) || RPAD('B', 4000) || RPAD('C', 2000);
  dbms_output.put_line(TO_CHAR(LENGTH(c)));

  v := dbms_stats.clob_to_varray(c);
  dbms_output.put_line(v.COUNT);
END;
/
 
COLUMN_NEED_HIST
Undocumented: For internal use. Appears to not like being executed as a stand-alone. dbms_stats.column_need_hist(
ownname    IN VARCHAR2,
tabname    IN VARCHAR2,
colname    IN VARCHAR2,
method_opt IN VARCHAR2)
RETURN NUMBER;
SELECT dbms_stats.column_need_hist('SYS', 'XSTREAM$_MAP', 'SEQUENCE', 'AUTO')
FROM dual;
SELECT dbms_stats.column_need_hist('SYS', 'XSTREAM$_MAP', 'SEQUENCE', 'AUTO')
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at "SYS.DBMS_STATS", line 21767
 
CONFIGURE_ADVISOR_FILTER
Configures the filter list for a statistics advisor task dbms_stats.configure_advisor_filter(
task_name          IN VARCHAR2,
stats_adv_opr_type IN VARCHAR2, -- values EXECUTE, IMPLEMENT, REPORT, SCRIPT
configuration_type IN VARCHAR2, -- values CLEAR, SET, SHOW
filter             IN StatsAdvFilterTab := NULL)
RETURN CLOB;
DECLARE
 c CLOB;
BEGIN
  c := dbms_stats.configure_advisor_filter('ADDM:549318987_1_580', 'EXECUTE', 'CLEAR');
  dbms_output.put_line(c);
END;
/
DECLARE
*
ERROR at line 1:
ORA-20001: ADDM:549318987_1_580 is an invalid identifier
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 19475
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 19460
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 432
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 3954
ORA-06512: at "SYS.DBMS_STATS", line 53393
ORA-06512: at line 4

-- it appears having a valid task name might help
 
CONFIGURE_ADVISOR_OBJ_FILTER
Configures an object filter for a statistics advisor task dbms_stats.configure_advisor_obj_filter(
task_name          IN VARCHAR2,
stats_adv_opr_type IN VARCHAR2,
rule_name          IN VARCHAR2,
ownname            IN VARCHAR2,
tabname            IN VARCHAR2,
action             IN VARCHAR2)
RETURN CLOB;
TBD
 
CONFIGURE_ADVISOR_OPR_FILTER
Configure operation filter for a statistics advisor task dbms_stats.configure_advisor_opr_filter(
task_name          IN VARCHAR2,
stats_adv_opr_type IN VARCHAR2,
rule_name          IN VARCHAR2,
operation_name     IN VARCHAR2,
action             IN VARCHAR2)
RETURN CLOB;
TBD
 
CONFIGURE_ADVISOR_RULE_FILTER
Configure a rule filter for a statistics advisor task dbms_stats.configure_advisor_rule_filter(
task_name          IN VARCHAR2,
stats_adv_opr_type IN VARCHAR2,
rule_name          IN VARCHAR2,
action             IN VARCHAR2)
RETURN CLOB;
TBD
 
CONVERT_RAW_TO_BIN_DOUBLE
Convert RAW to BINARY DOUBLE dbms_stats.convert_raw_to_bin_double(rawval IN RAW) RETURN BINARY_DOUBLE;
SELECT dbms_stats.convert_raw_to_bin_double(utl_raw.cast_from_binary_double(123.45)) AS OUTPUT
FROM dual;

    OUTPUT
----------
-3.35E-002

-- not impressed
 
CONVERT_RAW_TO_BIN_FLOAT
Convert RAW to BINARY FLOAT dbms_stats.convert_raw_to_bin_float(rawval IN RAW)
RETURN BINARY_FLOAT;
SELECT dbms_stats.convert_raw_to_bin_float(utl_raw.cast_from_binary_float(123.45)) AS OUTPUT
FROM dual;

 OUTPUT
-------
    -0

-- not impressed
 
CONVERT_RAW_TO_DATE
Convert RAW to DATE dbms_stats.convert_raw_to_date(rawval IN RAW)
RETURN DATE;
TBD
 
CONVERT_RAW_TO_NUMBER
Convert RAW to NUMBER dbms_stats.convert_raw_to_number(rawval IN RAW) RETURN NUMBER;
SELECT dbms_stats.convert_raw_to_number(utl_raw.cast_from_number(123)) AS OUTPUT
FROM dual;

 OUTPUT
-------
    123
 
CONVERT_RAW_TO_NVARCHAR
Convert RAW to NVARCHAR dbms_stats.convert_raw_to_nvarchar(rawval IN RAW) RETURN NVARCHAR;
TBD
 
CONVERT_RAW_TO_ROWID
Convert RAW to ROWID dbms_stats.convert_raw_to_rowid(rawval IN RAW) RETURN ROWID;
TBD
 
CONVERT_RAW_TO_VARCHAR2
Convert RAW to VARCHAR2 dbms_stats.convert_raw_to_varchar2(rawval IN RAW) RETURN VARCHAR2;
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);
See Overload 3 Demo Above
Convert RAW to BINARY_DOUBLE

Overload 5		 dbms_stats.convert_raw_value(
rawval IN  RAW,
resval OUT BINARY_DOUBLE);
See Overload 3 Demo Above
 
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;
/
 
CONV_RAW
Tests a value and returns "RAW DATA" if the value is RAW: Else ORA-01465 dbms_stats.conv_raw(
rawval IN RAW,
type   IN NUMBER)
RETURN VARCHAR2;
SELECT dbms_stats.conv_raw('0001132701000C1400B5', 0)
FROM dual;

SELECT dbms_stats.conv_raw('Oracle ACE Director', 0)
FROM dual;
 
COPY_TABLE_STATS
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_2019', 'SALES_Q3_2019', 1.01);
 
CREATE_ADVISOR_TASK
Creates an advisor task for the Statistics Advisor dbms_stats.create_advisor_task(task_name IN VARCHAR2 := NULL) RETURN VARCHAR2;
SELECT dbms_stats.create_advisor_task('UW_ADVISOR_TASK')
FROM dual;

DBMS_STATS.CREATE_ADVISOR_TASK('UW_ADVISOR_TASK')
--------------------------------------------------
UW_ADVISOR_TASK

SELECT task_id, task_name, status, how_created
FROM dba_advisor_tasks
WHERE task_name LIKE 'UW%';

 TASK_ID   TASK_NAME                      STATUS      HOW_CREATED
---------- ------------------------------ ----------- ------------
       643 UW_ADVISOR_TASK                INITIAL     CMD
 
CREATE_EXTENDED_STATS
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.

Overload 1		 dbms_stats.create_extended_stats(
ownname   IN VARCHAR2,
tabname   IN VARCHAR2,
extension IN VARCHAR2)
RETURN VARCHAR2;
set linesize 121
set long 1000000
col column_name format a30
col data_default format a45
col histogram format a20

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

SELECT extension_name, extension
FROM user_stat_extensions
WHERE table_name='SERV_INST';

SELECT e.extension col_group, t.num_distinct, t.histogram
FROM user_stat_extensions e, user_tab_col_statistics t
WHERE e.extension_name = t.column_name
AND e.table_name=t.table_name
AND t.table_name='SERV_INST';

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

SELECT extension_name, extension
FROM user_stat_extensions
WHERE table_name='SERV_INST';

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

BEGIN
  dbms_stats.gather_table_stats(USER, 'SERV_INST', METHOD_OPT=>'FOR COLUMNS SIZE AUTO');
END;
/

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)');
Overload 2 dbms_stats.create_extended_stats(
ownname IN VARCHAR2,
tabname IN VARCHAR2)
RETURN CLOB;
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')
FROM dual;

BEGIN
  dbms_stats.gather_table_stats(USER, 'SERV_INST', METHOD_OPT=>'FOR COLUMNS SIZE AUTO');
END;
/

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
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
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
Deletes the statistics preferences of all the tables, excluding the tables owned by SYS 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
Deletes statistics for all tables in the database dbms_stats.delete_database_stats(
stattab       IN VARCHAR2 DEFAULT NULL,    -- user stat table name
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
stat_category IN VARCHAR2 DEFAULT DEFAULT_DEL_STAT_CATEGORY);
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
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
stat_category IN VARCHAR2 DEFAULT DEFAULT_DEL_STAT_CATEGORY);
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_objects_stats;
 
DELETE_INDEX_STATS
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
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_PENDING_SYSTEM_STATS
Deletes pending system statistics that have been gathered and stored as pending dbms_stats.delete_pending_system_stats;
exec dbms_stats.delete_pending_system_stats;
 
DELETE_PROCESSING_RATE
Deletes the processing rate of a given statistics source. If the source is not specified, it deletes the statistics of all the sources dbms_stats.delete_processing_rate(stat_source IN VARCHAR2 DEFAULT NULL);
exec dbms_stats.delete_processing_rate('UWSTATSRC');
 
DELETE_SCHEMA_PREFS
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
Delete statistics for an entire schema dbms_stats.delete_schema_stats(
ownname       IN VARCHAR2,                 -- schema name
stattab       IN VARCHAR2 DEFAULT NULL,    -- stat table name
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
stat_category IN VARCHAR2 DEFAULT DEFAULT_DEL_STAT_CATEGORY);
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
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
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
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
stat_category   IN VARCHAR2  DEFAULT DEFAULT_DEL_STAT_CATEGORY);
exec dbms_stats.delete_table_stats(USER, 'servers');
 
DIFF_TABLE_STATS_IN_HISTORY
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;
SELECT * FROM TABLE(dbms_stats.diff_table_stats_in_history('SYS', 'TAB$', SYSTIMESTAMP-10, SYSTIMESTAMP-1));

REPORT                                                                           MAXDIFFPCT
-------------------------------------------------------------------------------- ----------
###############################################################################  11.5428129

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE : TAB$
OWNER : SYS
SOURCE A : Statistics as of 18-APR-21 02.02.32.000000 AM +00:00
SOURCE B : Statistics as of 27-APR-21 02.02.32.000000 AM +00:00
PCTTHRESHOLD : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NO DIFFERENCE IN TABLE / (SUB)PARTITION STATISTICS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:
.............................

COLUMN_NAME SRC NDV DENSITY HIST NULLS LEN MIN MAX SAMPSIZ
...............................................................................

ROWCNT A 322 .000957 YES 137 3 80 C4031 2302
B 316 .000971 YES 137 3 80 C4032 2302
SAMPLESIZE A 322 .000957 YES 137 3 80 C4031 2302
B 316 .000971 YES 137 3 80 C4032 2302
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NO DIFFERENCE IN INDEX / (SUB)PARTITION STATISTICS
###############################################################################
 
DIFF_TABLE_STATS_IN_PENDING
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 threshold in %
RETURN diffreptab PIPELINED;
SELECT * FROM TABLE(dbms_stats.diff_table_stats_in_pending('SYS', 'TAB$'));

REPORT                                                                           MAXDIFFPCT
-------------------------------------------------------------------------------- ----------
###############################################################################           0

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE : TAB$
OWNER : SYS
SOURCE A : Current Statistics in dictionary
SOURCE B : Pending Statistics
PCTTHRESHOLD : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NO DIFFERENCE IN TABLE / (SUB)PARTITION STATISTICS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NO DIFFERENCE IN COLUMN STATISTICS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NO DIFFERENCE IN INDEX / (SUB)PARTITION STATISTICS
###############################################################################
 
DIFF_TABLE_STATS_IN_STATTAB
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_ADVISOR_TASK
Drops the statistics advisor task dbms_stats.drop_advisor_task(task_name IN VARCHAR2);
exec dbms_stats.drop_advisor_task('UW_ADVISOR_TASK');
 
DROP_EXTENDED_STATS
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 Above
 
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');
 
EXECUTE_ADVISOR_TASK
Executes a previously created statistics advisor task dbms_stats.execute_advisor_task(
task_name      IN VARCHAR2,
execution_name IN VARCHAR2 := NULL)
RETURN VARCHAR2;
exec dbms_stats.execute_advisor_task('UW_ADVISOR_TASK');
 
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
Exports the statistics preferences of all the tables, excluding the tables owned by SYS 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
statown       IN VARCHAR2 DEFAULT NULL,  -- stat table schema
stat_category IN VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
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
statown       IN VARCHAR2 DEFAULT NULL,  -- stat table schema
stat_category IN VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
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_objects_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
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_PENDING_SYSTEM_STATS
Export the system statistics that has been gathered and stored as pending to the specified table dbms_stats.export_pending_system_stats(
stattab IN VARCHAR2,
statid  IN VARCHAR2 DEFAULT NULL,
statown IN VARCHAR2 DEFAULT NULL);
exec dbms_stats.export_pending_system_stats('STAT_TAB');
 
EXPORT_SCHEMA_PREFS
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
statown       IN VARCHAR2 DEFAULT NULL,  -- stat table schema
stat_category IN VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
exec dbms_stats.export_schema_stats(USER, 'STAT_TAB');
 
EXPORT_STATS_FOR_DP
Export Stats for DataPump dbms_stats.export_stats_for_dp(
objlist_tab_own    IN VARCHAR2,
objlist_tab        IN VARCHAR2,
dblink             IN VARCHAR2 DEFAULT NULL,
options            IN NUMBER DEFAULT NULL,
export_stats_since IN TIMESTAMP WITH TIME ZONE DEFAULT NULL);
TBD
 
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
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 VARCHAR2,
statid  IN VARCHAR2 DEFAULT NULL,
statown IN VARCHAR2 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
partname      IN VARCHAR2 DEFAULT NULL,   -- partition name
stattab       IN VARCHAR2,                -- stat table name
cascade       IN BOOLEAN  DEFAULT TRUE,   -- TRUE = indexes too
statown       IN VARCHAR2 DEFAULT NULL,   -- stat table schema
stat_category IN VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
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
Gathers statistics for all objects in the database

Overload 1		 dbms_stats.gather_database_stats(
estimate_percent IN  NUMBER    DEFAULT default_estimate_percent,
block_sample     IN  BOOLEAN   DEFAULT FALSE,
method_opt       IN  VARCHAR2  DEFAULT default_method_opt,
degree           IN  NUMBER    DEFAULT default_degree_value,
granularity      IN  VARCHAR2  DEFAULT default_granularity,
cascade          IN  BOOLEAN   DEFAULT default_cascade,
stattab          IN  VARCHAR2  DEFAULT NULL,
statid           IN  VARCHAR2  DEFAULT NULL,
options          IN  VARCHAR2  DEFAULT 'GATHER',
objlist          OUT NOCOPY 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',
obj_filter_list  IN  ObjectTab DEFAULT NULL);
DECLARE
 obl dbms_stats.objecttab := dbms_stats.objecttab();
BEGIN
  obl.extend;
  obl(1).ownname := 'UWCLASS';
  obl.extend;
  obl(2).ownname := 'SCOTT';

  dbms_stats.gather_database_stats(objlist=>obl);
END;
/
Gathers statistics for all objects in the database

Overload 2		 dbms_stats.gather_database_stats(
estimate_percent IN NUMBER    DEFAULT default_estimate_percent,
block_sample     IN BOOLEAN   DEFAULT FALSE,
method_opt       IN VARCHAR2  DEFAULT default_method_opt,
degree           IN NUMBER    DEFAULT default_degree_value,
granularity      IN VARCHAR2  DEFAULT default_granularity,
cascade          IN BOOLEAN   DEFAULT default_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    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',
obj_filter_list  IN ObjectTab DEFAULT NULL);
exec dbms_stats.gather_database_stats(statown=>'UWCLASS');
 
GATHER_DATABASE_STATS_JOB_PROC
Undocumented: Gathers stats for AUTO_TASK jobs dbms_stats.gather_database_stats_job_proc;
exec dbms_stats.gather_database_stats_job_proc;
 
GATHER_DICTIONARY_STATS
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 default_estimate_percent,
block_sample     IN  BOOLEAN   DEFAULT FALSE,
method_opt       IN  VARCHAR2  DEFAULT default_method_opt,
degree           IN  NUMBER    DEFAULT default_degree_value,
granularity      IN  VARCHAR2  DEFAULT default_granularity,
cascade          IN  BOOLEAN   DEFAULT default_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',
obj_filter_list  IN  ObjectTab DEFAULT NULL);
See above Overload 2 Below
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 default_estimate_percent,
block_sample     IN BOOLEAN  DEFAULT FALSE,
method_opt       IN VARCHAR2 DEFAULT default_method_opt,
degree           IN NUMBER   DEFAULT default_degree_value,
granularity      IN VARCHAR2 DEFAULT default_granularity,
cascade          IN BOOLEAN  DEFAULT default_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',
obj_filter_list  IN ObjectTab DEFAULT NULL);
exec dbms_stats.gather_dictionary_stats(cascade=>TRUE);
 
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
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           IN 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_PROCESSING_RATE
Starts the job of gathering the processing rates which end after 'timeLimit' seconds dbms_stats.gather_processing_rate(
gathering_mode IN VARCHAR2 DEFAULT 'START', -- valid values 'START' and 'END'
duration       IN NUMBER   DEFAULT NULL);   -- default is 60 minutes
exec dbms_stats.gather_processing_rate('START', 20);
 
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 default_estimate_percent,
block_sample     IN  BOOLEAN   DEFAULT FALSE,
method_opt       IN  VARCHAR2  DEFAULT default_method_opt,
degree           IN  NUMBER    DEFAULT default_degree_value,
granularity      IN  VARCHAR2  DEFAULT default_granularity,
cascade          IN  BOOLEAN   DEFAULT default_cascade,
stattab          IN  VARCHAR2  DEFAULT NULL,
statid           IN  VARCHAR2  DEFAULT NULL,
options          IN  VARCHAR2  DEFAULT 'GATHER',
objlist          OUT NOCOPY 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,
obj_filter_list  IN  ObjectTab DEFAULT NULL);
exec dbms_stats.gather_schema_stats(USER, cascade=>TRUE);

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

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

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

exec dbms_stats.gather_schema_stats(USER, estimate_percent=>2.5, method_opt=>'FOR ALL COLUMNS SIZE 10', degree=>4, granularity=>'ALL', options=>'GATHER STALE', cascade=>TRUE);
Overload 2 dbms_stats.gather_schema_stats(
ownname          IN VARCHAR2,
estimate_percent IN NUMBER    DEFAULT default_estimate_percent,
block_sample     IN BOOLEAN   DEFAULT FALSE,
method_opt       IN VARCHAR2  DEFAULT default_method_opt,
degree           IN NUMBER    DEFAULT default_degree_value,
granularity      IN VARCHAR2  DEFAULT default_granularity,
cascade          IN BOOLEAN   DEFAULT default_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,
obj_filter_list  IN ObjectTab DEFAULT NULL);
See above Overload 1 Above
 
GATHER_ST_JOB_CONTINUOUS_PROC
Undocumented, internal, invoked by the high frequency auto stats collection task dbms_stats.gather_st_job_continuous_proc;
exec dbms_stats.gather_st_job_continuous_proc;
 
GATHER_SYSTEM_STATS
Gather Statistics for the System Tables and Indexes dbms_stats.gather_system_stats(
gathering_mode IN VARCHAR2 DEFAULT 'NOWORKLOAD',
interval       IN INTEGER  DEFAULT 60,          -- in minutes
stattab        IN VARCHAR2 DEFAULT NULL,
statid         IN VARCHAR2 DEFAULT NULL,
statown        IN VARCHAR2 DEFAULT NULL);

Gathering Modes
EXADATA
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 DEFAULT_ESTIMATE_PERCENT,
block_sample     IN BOOLEAN  DEFAULT FALSE,
method_opt       IN VARCHAR2 DEFAULT DEFAULT_METHOD_OPT,
 -- or 'FOR ALL COLUMNS SIZE 1',
degree           IN NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
granularity      IN VARCHAR2 DEFAULT DEFAULT_GRANULARITY,
cascade          IN BOOLEAN  DEFAULT DEFAULT_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,
context          IN dbms_stats.ccontext DEFAULT NULL,
options          IN VARCHAR2 DEFAULT DEFAULT_OPTIONS);
exec dbms_stats.gather_table_stats(USER, 'SERVERS', 'P2010103106', estimate_percent=>dbms_stats.auto_sample_size, block_sample=>TRUE, method_opt=>'FOR ALL INDEXED COLUMNS', degree=>2, force=>TRUE);
 
GATHER_TABLE_STATS_FUNC
For internal use only dbms_stats.gather_table_stats_func(
ownname           IN VARCHAR2,
tabname           IN VARCHAR2,
partname          IN VARCHAR2 DEFAULT NULL,
estimate_percent  IN NUMBER   DEFAULT DEFAULT_ESTIMATE_PERCENT,
block_sample_str  IN VARCHAR2,
method_opt        IN VARCHAR2 DEFAULT DEFAULT_METHOD_OPT,
degree            IN NUMBER   DEFAULT DEFAULT_DEGREE_VALUE,
granularity       IN VARCHAR2 DEFAULT DEFAULT_GRANULARITY,
cascade_str       IN VARCHAR2,
stattab           IN VARCHAR2 DEFAULT NULL,
statid            IN VARCHAR2 DEFAULT NULL,
statown           IN VARCHAR2 DEFAULT NULL,
no_invalidate_str IN VARCHAR2,
stattype          IN VARCHAR2 DEFAULT 'DATA',
force_str         IN VARCHAR2,
options           IN VARCHAR2 DEFAULT DEFAULT_OPTIONS)
RETURN NUMBER;
TBD
 
GENERATE_INDEX_STATS
Generates index stats for all indexes created on the given owner and table, with given status and index name following some pattern dbms_stats.generate_index_stats(
ownname             IN VARCHAR2,
tabname             IN VARCHAR2,
index_pattern       IN VARCHAR2 DEFAULT NULL,
status              IN VARCHAR2 DEFAULT NULL,
no_stats_index_only IN NUMBER   DEFAULT NULL);
CREATE TABLE gistab AS
SELECT * FROM user_objects;

CREATE INDEX ix_gistab
ON gistab(object_name);

SELECT leaf_blocks, distinct_keys, clustering_factor
FROM user_indexes
WHERE index_name = 'IX_GISTAB';

LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
----------- ------------- -----------------
        350         50232             21994

exec dbms_stats.delete_index_stats(USER, 'IX_GISTAB');

SQL> SELECT leaf_blocks, distinct_keys, clustering_factor
  2  FROM user_indexes
  3  WHERE index_name = 'IX_GISTAB';

LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
----------- ------------- -----------------


exec dbms_stats.generate_index_stats(USER, 'GISTAB', 'IX_', 'VALID', 1);

SELECT leaf_blocks, distinct_keys, clustering_factor
FROM user_indexes
WHERE index_name = 'IX_GISTAB';

LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
----------- ------------- -----------------
        350         50232             21994
 
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');
 
GEN_SELMAP
Undocumented dbms_stats.gen_selmap(
owner   IN            VARCHAR2,
tabname IN            VARCHAR2,
pname   IN            VARCHAR2,
spname  IN            VARCHAR2,
flag    IN            BINARY_INTEGER,
colinfo IN            ColDictTab,
selmap  IN OUT NOCOPY SelTab,
clist   IN OUT NOCOPY CTab);
TBD
 
GET_ADVISOR_OPR_FILTER
Create an operation filter for a statistics operation dbms_stats.get_advisor_opr_filter(
opr_id     IN NUMBER,
opr_filter IN OUT NOCOPY StatsAdvOpr);
TBD
 
GET_ADVISOR_RECS
Generates a recommendation report on the given item dbms_stats.get_advisor_recs(
ownname IN VARCHAR2,
tabname IN VARCHAR2,
rec     IN VARCHAR2,           -- INCREMENTAL, CONCURRENT
type    IN VARCHAR2 := 'TEXT') -- TEXT, HTML, XML
RETURN CLOB;
set severoutput on

DECLARE
 c CLOB;
BEGIN
  c := dbms_stats.get_advisor_recs('SYS', 'OBJ$', 'INCREMENTAL', 'XML');
  dbms_output.put_line(c);
END;
/

<report db_version="21.0.0.0.0" cpu_cores="1" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="0" packs="2" service_type="0">
  <report_id><![CDATA[/orarep/statsadv/main%3flevel%3dALL]]></report_id>
</report>
 
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 NOCOPY NUMBER,
statown        IN         VARCHAR2 DEFAULT NULL,
realtime_stats IN         BOOLEAN  DEFAULT TRUE);
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 NOCOPY RAW
stattypown  OUT        VARCHAR2,
stattypname OUT        VARCHAR2,
statown     IN         VARCHAR2 DEFAULT NULL);
See Overload 1 Demo Above
 
GET_COMPATIBLE
Undocumented dbms_stats.get_compatible RETURN NUMBER;
SELECT dbms_stats.get_compatible
FROM dual;
 
GET_INDEX_STATS
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);
See Overload 2 Demo Below
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 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);
See Overload 2 Demo Above
Overload 4 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);
See Overload 2 Demo Above
 
GET_PARAM
Deprecated as of 11gR1 dbms_stats.get_param(pname IN VARCHAR2) RETURN VARCHAR2;
Deprecated: Do not use. Instead use GET_PREFS
 
GET_PREFS
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
col degree format a10
SELECT dbms_stats.get_prefs('DEGREE') DEGREE
FROM dual;

DEGREE
----------
NULL

col method_opt format a30
SELECT dbms_stats.get_prefs('METHOD_OPT', USER) METHOD_OPT
FROM dual;

METHOD_OPT
------------------------------
FOR ALL COLUMNS SIZE AUTO

col cascade format a30
SELECT dbms_stats.get_prefs('CASCADE', USER) CASCADE
FROM dual;

CASCADE
------------------------------
DBMS_STATS.AUTO_CASCADE

-- demo 2
col est_pct format a30
SELECT dbms_stats.get_prefs('ESTIMATE_PERCENT') EST_PCT
FROM dual;

EST_PCT
------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE

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

SELECT dbms_stats.get_prefs('ESTIMATE_PERCENT') EST_PCT
FROM dual;

EST_PCT
----------------------------
5
 
GET_ROW_COUNT_ESTIMATE
Quick estimate number of the rows in a table for auto sampling dbms_stats.get_row_count_estimate(
ownname  IN VARCHAR2,  -- table owner to select from
tabname  IN VARCHAR2,  -- table name to select from
partname IN VARCHAR2,  -- table partition name
nblks    IN INTEGER,
degree   IN INTEGER)
RETURN NUMBER;
SELECT blocks
FROM dba_tables
WHERE table_name = 'USER$';

BLOCKS
-------
     7

SELECT dbms_stats.get_row_count_estimate('SYS', 'USER$', '', 7, 1)
FROM dual;

DBMS_STATS.GET_ROW_COUNT_ESTIMATE('SYS','USER$','',7,1)
-------------------------------------------------------
                                                    127
 
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_AVAILABILITY
---------------------------------------
27-MAR-21 10.04.04.637365000 PM +00:00
 
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_STATS_HISTORY_RETENTION
---------------------------
                         31
 
GET_STAT_TAB_VERSION
Gets the DBMS_STATS version of a stats table built in a earlier database version. In 12.1 it is 7, in 19.3 and above it is 8 dbms_stats.get_stat_tab_version RETURN NUMBER;
SELECT dbms_stats.get_stat_tab_version
FROM dual;

GET_STAT_TAB_VERSION
--------------------
                   8
 
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 Status Values   Valid pname Values
AUTOGATHERING   cpuspeed
BADSTATS   cpuspeednw
COMPLETED   ioseektim
MANUALGATHERING   iotfrspeed
    maxthr
    mbrc
    mreadtim
    slavethr
    sreadtim
SELECT pname, pval1
FROM sys.aux_stats$;

set serveroutput on

DECLARE
 stat VARCHAR2(30);
 pbeg  DATE;
 pend  DATE;
 pval NUMBER;
BEGIN
  dbms_stats.get_system_stats(stat, pbeg, pend, 'cpuspeednw', 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;
/
COMPLETED
04/10/21 07:13:00
04/10/21 07:13:00

PL/SQL procedure successfully completed.

exec dbms_stats.gather_system_stats('INTERVAL', 15);

DECLARE
 stat VARCHAR2(30);
 pbeg  DATE;
 pend  DATE;
 pval NUMBER;
BEGIN
  dbms_stats.get_system_stats(stat, pbeg, pend, 'cpuspeednw', 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;
/
AUTOGATHERING
04/07/21 09:47:00
04/07/21 09:52:00
1264
 
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;
/
 
IMPLEMENT_ADVISOR_TASK
Implements the actions recommended by the statistics advisor dbms_stats.implement_advisor_task(
task_name      IN VARCHAR2,
execution_name IN VARCHAR2 := NULL,
level          IN VARCHAR2 := 'TYPICAL')
RETURN CLOB;
exec dbms_stats.implement_advisor_task('UW_ADVISOR_TASK');
 
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
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,
statown       IN VARCHAR2 DEFAULT NULL,
no_invalidate IN BOOLEAN  DEFAULT to_no_invalidate_type(get_param('NO_INVALIDATE')),
force         IN BOOLEAN  DEFAULT FALSE,
stat_category IN VARCHAR2 DEFAULT DEFAULT_STST_CATEGORY);
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,
statown       IN VARCHAR2 DEFAULT NULL,
no_invalidate IN BOOLEAN  DEFAULT to_no_invalidate_type(get_param('NO_INVALIDATE')),
force         IN BOOLEAN  DEFAULT FALSE,
stat_category IN VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
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
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,
statown       IN VARCHAR2 DEFAULT NULL,
no_invalidate IN BOOLEAN  DEFAULT to_no_invalidate_type(get_param('NO_INVALIDATE')),
force         IN BOOLEAN  DEFAULT FALSE,
stat_category IN VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
exec dbms_stats.import_schema_stats(USER, 'STAT_TAB');
 
IMPORT_STATS_FOR_DP
Undocumented dbms_stats.import_stats_for_dp(
objlist_tab_own IN VARCHAR2,
objlist_tab     IN VARCHAR2,
options         IN NUMBER DEFAULT NULL);
TBD
 
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
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,
partname      IN VARCHAR2 DEFAULT NULL,
stattab       IN VARCHAR2,
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,
stat_category IN VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
exec dbms_stats.import_table_stats(USER, 'servers', stattab => 'STAT_TAB');
 
INIT_PACKAGE
Stats initialization procedure
See: ?/rdbms/admin/execstat.sql		 dbms_stats.init_package;
exec dbms_stats.init_package;
 
INTERRUPT_ADVISOR_TASK
Interrupts a currently executing statistics advisor task. The task will end its operations as it would at a normal exit and the user will be able to access intermediate results. The task may also later be resumed. dbms_stats.interrupt_advisor_task(task_name IN VARCHAR2);
exec dbms_stats.interrupt_advisor_task('UW_ADVISOR_TASK');
 
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
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');
 
MERGE_STATS_PARTITION_EXCHANGE
Undocumented, internal dbms_stats.merge_stats_partition_exchange(
 g_objn   IN NUMBER,
g_ownname IN VARCHAR2,
g_tabname IN VARCHAR2,
t_objn    IN NUMBER,
t_ownname IN VARCHAR2,
t_tabname IN VARCHAR2,
p_objn    IN NUMBER,
flags     IN NUMBER);
TBD
 
POSTPROCESS_INDSTATS
Undocumented, internal dbms_postprocess_indstats(
istats IN ITab,
flags  IN NUMBER);
TBD
 
POSTPROCESS_STATS
Undocumented for internal use only dbms_stats.postprocess_stats(
owner    IN VARCHAR2,
tabname  IN VARCHAR2,
pname    IN VARCHAR2,
spname   IN VARCHAR2,
flag     IN NUMBER,
rawstats IN RawCTab,
selmap   IN SelTab,
clist    IN CTab);
TBD
 
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

Note: This is the workaround for 11.1.0.7 bug #9372653		 dbms_stats.prepare_column_values(
srec     IN OUT StatRec,
datevals IN     DATEARRAY);
CREATE TABLE preptest (
owner         VARCHAR2(30),
object_name   VARCHAR2(30),
object_type   VARCHAR2(19),
last_ddl_time DATE)
PARTITION BY RANGE (last_ddl_time) (
PARTITION yr01 VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY')),
PARTITION yr02 VALUES LESS THAN (TO_DATE('01-JAN-2003','DD-MON-YYYY')),
PARTITION yr03 VALUES LESS THAN (TO_DATE('01-JAN-2004','DD-MON-YYYY')),
PARTITION yr04 VALUES LESS THAN (TO_DATE('01-JAN-2005','DD-MON-YYYY')),
PARTITION yr05 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY')),
PARTITION yr06 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY')),
PARTITION yr07 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')),
PARTITION yr08 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY')),
PARTITION yr09 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY')),
PARTITION yr10 VALUES LESS THAN (TO_DATE('01-JAN-2011','DD-MON-YYYY')),
PARTITION yr11 VALUES LESS THAN (TO_DATE('01-JAN-2012','DD-MON-YYYY')),
PARTITION yr12 VALUES LESS THAN (TO_DATE('01-JAN-2013','DD-MON-YYYY')));

INSERT INTO preptest
SELECT owner, object_name, object_type, last_ddl_time
FROM dba_objects
WHERE last_ddl_time IS NOT NULL;

COMMIT;

-- view cbo stats
col low_value format a15
col high_value format a15

SELECT column_name, low_value, high_value
FROM user_tab_cols
WHERE table_name = 'PREPTEST'
AND data_type = 'DATE';

-- gather stats
exec dbms_stats.gather_table_stats(USER, 'PREPTEST');

-- view the stats
SELECT column_name, low_value, high_value
FROM user_tab_cols
WHERE table_name = 'PREPTEST'
AND data_type = 'DATE';

-- make high and low values human readable
CREATE OR REPLACE PROCEDURE show_hi_low AUTHID CURRENT_USER IS
 ldt DATE;
 hdt DATE;
 lrv RAW(32);
 hrv RAW(32);
BEGIN
  SELECT low_value, high_value
  INTO lrv, hrv
  FROM user_tab_cols
  WHERE table_name = 'PREPTEST'
  AND column_name = 'LAST_DDL_TIME';

  dbms_stats.convert_raw_value(lrv, ldt);
  dbms_stats.convert_raw_value(hrv, hdt);
  dbms_output.put_line('Low Value:  ' || TO_CHAR(ldt));
  dbms_output.put_line('High Value: ' || TO_CHAR(hdt));
END show_hi_low;
/

set serveroutput on

exec show_hi_low;

DECLARE
 sr_arr   dbms_stats.statrec;
 new_low  DATE := TO_DATE('01-JAN-2004');
 new_high DATE := TO_DATE('31-DEC-2014');
 vals_arr dbms_stats.datearray;
BEGIN
  sr_arr.eavs := 0;
  sr_arr.chvals := NULL;
  sr_arr.epc := 2;

  vals_arr := dbms_stats.datearray(new_low, new_high);

  sr_arr.bkvals := dbms_stats.numarray(10000,1000000);
  dbms_stats.prepare_column_values(sr_arr, vals_arr);
  dbms_stats.set_column_stats(USER, 'PREPTEST', 'LAST_DDL_TIME', NULL, srec=>sr_arr);
END;
/

exec show_hi_low;
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_NVARCHAR
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_nvarchar(
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
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);
 
PUBLISH_PENDING_SYSTEM_STATS
Publish system statistics that have been gathered and stored as pending dbms_stats.publish_pending_system_stats;
exec dbms_stats.publish_pending_system_stats;
 
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;
/
 
REMAP_STAT_TABLE
Remaps, changes, the owner or STATTAB name dbms_stats.remap_stat_table(
ownname IN VARCHAR2,
stattab IN VARCHAR2,
src_own IN VARCHAR2,
src_tab IN VARCHAR2,
tgt_own IN VARCHAR2,
tgt_tab IN VARCHAR2);
exec dbms_stats.remap_stat_table('UWCLASS', 'ustat', 'UWCLASS', NULL, 'C##UWCLASS', NULL);
 
REPORT_ADVISOR_TASK
Reports the results of a statistics advisor task dbms_stats.report_advisor_task(
task_name      IN VARCHAR2,
execution_name IN VARCHAR2 := NULL,
type           IN VARCHAR2 := 'TEXT',
section        IN VARCHAR2 := 'ALL',
level          IN VARCHAR2 := 'TYPICAL')
RETURN CLOB;
exec dbms_stats.report_advisor_task('UW_ADVISOR_TASK');
 
REPORT_COL_USAGE
Reports column usage dbms_stats.report_col_usage(ownname IN VARCHAR2, tabname IN VARCHAR2) RETURN CLOB;
conn uwclass/uwclass@pdbdev

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

SELECT COUNT(*)
FROM servers
WHERE srvr_id = 5;

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

SELECT COUNT(*)
FROM servers
WHERE srvr_id < 5;

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

SELECT s.srvr_id
FROM servers s, serv_inst si
WHERE s.srvr_id = si.srvr_id;

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

exec dbms_stats.reset_col_usage(USER, 'SERVERS');

SELECT dbms_stats.report_col_usage(USER, 'SERVERS')
FROM dual;
 
REPORT_GATHER_AUTO_STATS
Runs the auto statistics gathering job in reporting mode. The object names are collected but stats are not gathered dbms_stats.report_gather_auto_stats(
detail_level IN VARCHAR2 DEFAULT 'TYPICAL',
format       IN VARCHAR2 DEFAULT 'TEXT')
RETURN CLOB;
DECLARE
 c CLOB;
BEGIN
  c := dbms_stats.report_gather_auto_stats;
  dbms_output.put_line(SUBSTR(c,1,10000));
END;
/
 
REPORT_GATHER_DATABASE_STATS
Runs the GATHER_DATABASE_STATS procedures in reporting mode. Stats are not collected, but all the objects that will be affected when it is invoked are reported. dbms_stats.report_gather_database_stats(
estimate_percent IN NUMBER    DEFAULT DEFAULT_ESTIMATE_PERCENT,
block_sample     IN BOOLEAN   DEFAULT FALSE,
method_opt       IN VARCHAR2  DEFAULT DEFAULT_METHOD_OPT,
degree           IN NUMBER    DEFAULT to_degree_type(get_param('DEGREE')),
granularity      IN VARCHAR2  DEFAULT DEFAULT_GRANULARITY,
cascade          IN BOOLEAN   DEFAULT DEFAULT_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    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',
obj_filter_list  IN ObjectTab DEFAULT NULL,
detail_level     IN VARCHAR2  DEFAULT 'TYPICAL',
format           IN VARCHAR2  DEFAULT 'TEXT')
RETURN CLOB;
DECLARE
 c CLOB;
BEGIN
  c := dbms_stats.report_gather_database_stats;
  dbms_output.put_line(SUBSTR(c,1,10000));
END;
/
 
REPORT_GATHER_DICTIONARY_STATS
Runs the GATHER_DICTIONARY_STATS procedures in reporting mode. Stats are not collected, but all the objects that will be affected when it is invoked are reported. dbms_stats.report_gather_dictionary_stats(
comp_id          IN VARCHAR2  DEFAULT NULL,
estimate_percent IN NUMBER    DEFAULT DEFAULT_ESTIMATE_PERCENT,
block_sample     IN BOOLEAN   DEFAULT FALSE,
method_opt       IN VARCHAR2  DEFAULT DEFAULT_METHOD_OPT,
degree           IN NUMBER    DEFAULT to_degree_type(get_param('DEGREE')),
granularity      IN VARCHAR2  DEFAULT DEFAULT_GRANULARITY,
cascade          IN BOOLEAN   DEFAULT DEFAULT_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',
obj_filter_list  IN ObjectTab DEFAULT NULL,
detail_level     IN VARCHAR2  DEFAULT 'TYPICAL',
format           IN VARCHAR2  DEFAULT 'TEXT')
RETURN CLOB;
DECLARE
 c CLOB;
BEGIN
  c := dbms_stats.report_gather_dictionary_stats;
  dbms_output.put_line(SUBSTR(c,1,10000));
END;
/
 
REPORT_GATHER_FIXED_OBJ_STATS
Runs the GATHER_FIXED OBJ_STATS procedures in reporting mode. Stats are not collected, but all the objects that will be affected when it is invoked are reported. dbms_stats.report_gather_fixed_obj_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')),
detail_level  IN VARCHAR2 DEFAULT 'TYPICAL',
format        IN VARCHAR2 DEFAULT 'TEXT')
RETURN CLOB;
DECLARE
 c CLOB;
BEGIN
  c := dbms_stats.report_gather_fixed_obj_stats;
  dbms_output.put_line(SUBSTR(c,1,10000));
END;
/
 
REPORT_GATHER_SCHEMA_STATS
Runs the GATHER_SCHEMA_STATS procedures in reporting mode. Stats are not collected, but all the objects that will be affected when it is invoked are reported. dbms_stats.report_gather_schema_stats(
ownname          IN VARCHAR2,
estimate_percent IN NUMBER    DEFAULT DEFAULT_ESTIMATE_PERCENT,
block_sample     IN BOOLEAN   DEFAULT FALSE,
method_opt       IN VARCHAR2  DEAULT DEFAULT_METHOD_OPT,
degree           IN NUMBER    DEFAULT to_degree_type(get_param('DEGREE')),
granularity      IN VARCHAR2  DEFAULT DEFAULT_GRANULARITY,
cascade          IN BOOLEAN   DEFAULT DEFAULT_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,
obj_filter_list  IN ObjectTab DEFAULT NULL,
detail_level     IN VARCHAR2  DEFAULT 'TYPICAL',
format           IN VARCHAR2  DEFAULT 'TEXT')
RETURN CLOB;
DECLARE
 c CLOB;
BEGIN
  c := dbms_stats.report_gather_schema_stats(USER);
  dbms_output.put_line(SUBSTR(c,1,10000));
END;
/
 
REPORT_GATHER_TABLE_STATS
Runs the GATHER_TABLE_STATS procedures in reporting mode. Stats are not collected, but all the objects that will be affected when it is invoked are reported. dbms_stats.report_gather_table_stats(
ownname          IN VARCHAR2,
tabname          IN VARCHAR2,
partname         IN VARCHAR2 DEFAULT NULL,
estimate_percent IN NUMBER   DEFAULT DEFAULT_ESTIMATE_PERCENT,
block_sample     IN BOOLEAN  DEFAULT FALSE,
method_opt       IN VARCHAR2 DEFAULT DEFAULT_METHOD_OPT,
degree           IN NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
granularity      IN VARCHAR2 DEFAULT DEFAULT_GRANULARITY,
cascade          IN BOOLEAN  DEFAULT DEFAULT_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,
options          IN VARCHAR2 DEFAULT DEFAULT_OPTIONS,
detail_level     IN VARCHAR2 DEFAULT 'TYPICAL',
format           IN VARCHAR2 DEFAULT 'TEXT')
RETURN CLOB;
DECLARE
 c CLOB;
BEGIN
  c := dbms_stats.report_gather_table_stats(USER, 'TAB$');
  dbms_output.put_line(SUBSTR(c,1,10000));
END;
/
 
REPORT_SINGLE_STATS_OPERATION
Generates a report for the provided operation optionally in a particular pluggable database (PDB) in a container database (CDB) environment. dbms_stats.report_single_stats_operation(
opid         IN NUMBER,
detail_level IN VARCHAR2 DEFAULT 'TYPICAL',
format       IN VARCHAR2 DEFAULT 'TEXT',
container_id IN NUMBER   DEFAULT NULL)
RETURN CLOB;
DECLARE
 c CLOB;
BEGIN
  c := dbms_stats.report_single_stats_operation;
  dbms_output.put_line(SUBSTR(c,1,10000));
END;
/
 
REPORT_STATS_OPERATIONS
Reports statistics operations at different levels of detail with optional filters on the start time of operations dbms_stats.report_stats_operations(
detail_level  IN VARCHAR2                  DEFAULT 'TYPICAL',
format        IN VARCHAR2                  DEFAULT 'TEXT',
latestN       IN NUMBER                    DEFAULT NULL,
since         IN TIMESTAMP WITH TIME ZONE  DEFAULT NULL,
until         IN TIMESTAMP WITH TIME ZONE  DEFAULT NULL,
auto_only     IN BOOLEAN                   DEFAULT FALSE,
container_ids IN dbms_utility.number_array DEFAULT dbms_stats.null_numtab)
RETURN CLOB;
DECLARE
 c CLOB;
BEGIN
  c := dbms_stats.report_stats_operations(format=>'HTML', since=> SYSTIMESTAMP-1);
  dbms_output.put_line(SUBSTR(c,1,10000));
END;
/
 
RESET_ADVISOR_TASK
Reset a statistics advisor task execution to its initial state. The procedure should be called on an execution that is not currently executing. dbms_stats.reset_advisor_task(task_name IN VARCHAR2);
exec dbms_stats.reset_advisor_task('UW_ADVISOR_TASK');
 
RESET_COL_USAGE
Resets the column usage statistics to unused dbms_stats.reset_col_usage(ownname IN VARCHAR2, tabname IN VARCHAR2);
See REPORT_COL_USAGE Demo Above
 
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;

SYSTIMESTAMP
------------------------------------
28-APR-21 02.11.48.961504 AM +00:00

SELECT SYSTIMESTAMP "CUR_TIME", SYSTIMESTAMP - (SYSTIMESTAMP - TO_TIMESTAMP('28-APR-21 02.11.48.961504')) "CUR_TIME_MINUS_1HR"
FROM dual;

CUR_TIME                                 CUR_TIME_MINUS_1HR
---------------------------------------- ----------------------------------------
28-APR-21 02.13.52.983904 AM +00:00      28-APR-21 02.11.48.961504000 AM +00:00

DECLARE
 restore_to TIMESTAMP := SYSTIMESTAMP - ( SYSTIMESTAMP - TO_TIMESTAMP('28-APR-21 02.11.48.961504'));
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('28-APR-21 02.11.48.961504')) "CUR_TIME_MINUS_1HR"
FROM dual;

DECLARE
 restore_to TIMESTAMP := SYSTIMESTAMP - ( SYSTIMESTAMP - TO_TIMESTAMP('28-APR-21 02.11.48.961504'));
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('28-APR-21 02.11.48.961504')) "CUR_TIME_MINUS_1HR"
FROM dual;

DECLARE
 restore_to TIMESTAMP := SYSTIMESTAMP - ( SYSTIMESTAMP - TO_TIMESTAMP('28-APR-21 02.11.48.961504'));
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('28-APR-21 02.11.48.961504')) "CUR_TIME_MINUS_1HR"
FROM dual;

DECLARE
 restore_to TIMESTAMP := SYSTIMESTAMP - ( SYSTIMESTAMP - TO_TIMESTAMP('28-APR-21 02.11.48.961504'));
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('28-APR-21 02.11.48.961504')) "CUR_TIME_MINUS_1HR"
FROM dual;

DECLARE
 restore_to TIMESTAMP := SYSTIMESTAMP - (SYSTIMESTAMP - TO_TIMESTAMP('28-APR-21 02.11.48.961504'));
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_ADVISOR_TASK
Resume a previously interrupted task. It will only resume the execution that was most recently interrupted dbms_stats.resume_advisor_task(task_name IN VARCHAR2);
exec dbms_stats.resume_advisor_task('UW_ADVISOR_TASK');
 
RESUME_GATHER_STATS
Resumes statistics gathering at the point where it was interrupted dbms_stats.resume_gather_stats;
exec dbms_stats.resume_gather_stats;
 
SAVE_INMEMORY_STATS
Undocumented dbms_stats.save_inmemory_stats(objn IN NUMBER);
TBD
 
SCRIPT_ADVISOR_TASK
Returns the script that implements the recommended actions for the problems found by the statistics advisor. dbms_stats.script_advisor_task(
task_name      IN VARCHAR2,
execution_name IN VARCHAR2 := NULL,
dir_name       IN VARCHAR2 := NULL,
level          IN VARCHAR2 := 'TYPICAL')
RETURN CLOB;
exec dbms_stats.script_advisor_task('UW_ADVISOR_TASK');
 
SEED_COL_USAGE
Iterates over the SQL statements in a SQL tuning set and compile them in order to seed column usage information for the columns that appear in these statements dbms_stats.seed_col_usage(
sqlset_name IN VARCHAR2,
owner_name  IN VARCHAR2,
time_limit  IN POSITIVE DEFAULT NULL);
exec dbms_stats.seed_col_usage(NULL, NULL, 600);

exec dbms_stats.seed_col_usage('UWSet', 'UWCLASS', 600);
 
SET_ADVISOR_TASK_PARAMETER
Updates the value of of a statistics advisor task parameter. Note that while the "VALUE" parameter is a VARCHAR2 the only values accepted are numeric (you can put the value into single quotes if it makes you feel better). dbms_stats.set_advisor_task_parameter(
task_name IN VARCHAR2,
parameter IN VARCHAR2, -- TIME_LIMIT, START_TIME
value     IN VARCHAR2);
exec dbms_stats.set_advisor_task_parameter('UW_ADVISOR_TASK', 'TIME_LIMIT', 60);
 
SET_COLUMN_STATS
Sets column related information. Used for the creation of histograms

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);
col low_value format a10
col high_value format a10

SELECT num_distinct, low_value, high_value, density, num_nulls, avg_col_len
FROM user_tab_cols
WHERE table_name = 'AIRPLANES'
AND column_name = 'LINE_NUMBER';

DECLARE
 vSRec dbms_stats.statrec;
BEGIN
  vSRec.minval := utl_raw.cast_from_number(1);
  vSRec.maxval := utl_raw.cast_from_number(50000);

  dbms_stats.set_column_stats(
  ownname => USER,
  tabname => 'AIRPLANES',
  colname => 'LINE_NUMBER',
  distcnt => 50000,
  density => .00002,
  nullcnt => 4,
  avgclen => 5,
  srec    => vSRec);
END;
/

SELECT num_distinct, low_value, high_value, density, num_nulls, avg_col_len
FROM user_tab_cols
WHERE table_name = 'AIRPLANES'
AND column_name = 'LINE_NUMBER';

-- Also see Tuning (Setting Stats) and Histogram Manual Generation 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
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 IN 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
Sets the global statistics preferences dbms_stats.set_global_prefs(pname IN VARCHAR2, pvalue IN VARCHAR2);
PNAME PVALUE Description
AUTOSTATS_TARGET ALL Collect statistics on all objects
AUTOSTATS_TARGET AUTO Collect statistics on objects as determined by DBMS_STATS algorithm
AUTOSTATS_TARGET ORACLE Collect stats only on objects created by Oracle during installation and patching
CASCADE   Whether index stats should be gathered automatically
CONCURRENT ALL Concurrency always enabled
CONCURRENT AUTOMATIC concurrency enabled for automatic gathering only
CONCURRENT MANUAL Concurrenty enabled for manual gathering only
CONCURRENT OFF Concurrency is disabled for manual and automatic stats
DEGREE   The degree of parallelism during stats gathering
ESTIMATED_PERCENT   The percentage of rows to be evaluated: range 0.000001 to 100
GLOBAL_TEMP_TABLE_STATS SESSION Session-specific global temporary table stats
GLOBAL_TEMP_TABLE_STATS SHARED All sessions see the same global temporary table stats
GRANULARITY ALL Gather global, partition, and subpartition stats
GRANULARITY AUTO Chooses a granularity based on the type of partitioning
GRANULARITY DEFAULT Deprecated
GRANULARITY GLOBAL Gather global stats only
GRANULARITY GLOBAL AND PARTITION Gather global and partition-level stats only
GRANULARITY PARTITION Gather partition-level stats
GRANULARITY SUBPARTITION Gather subpartition-level stats
INCREMENTAL   Determines whether global stats of a partitioned talbe will be maintained without doing a full table scan
INCREMENTAL_LEVEL   Controls that synopses to collect when INCREMENTAL preference is TRUE
INCREMENTAL_STALENESS   Determines the definition of "stale" for partitions and subpartitions
METHOD_OPT   Controls histogram creation during stats collection
NO_INVALIDATE   Controls the invalidation of cursors base on stats collection
PUBLISH   Controls whether new stats are published immediately
STALE_PERCENT   Controls the percentage of rows changes required before stats are gathered
TABLE_CACHED_BLOCKS   The average number of blocks in the buffer cache for any to be assumed when determining the index clustering factor
OPTIONS GATHER Gather stats for all objects in the table
OPTIONS GATHER AUTO Gather all stats deemed necessary by Oracle's algorithm
exec dbms_stats.set_global_prefs('CONCURRENT', 'AUTOMATIC');
 
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   DEFAULT 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 as of 11gR1 dbms_stats.set_param(pname IN VARCHAR2, pval IN VARCHAR2);
Deprecated: Do not use. Instead use SET_GLOBAL_PREFS
 
SET_PROCESSING_RATE
Sets the value of rate of processing for a given operation

The user issuing should have the role optimizer_processing_rate or DBA		 dbms_stats.set_processing_rate(
opName   IN VARCHAR2,
procRate IN NUMBER);

Operation Names
ALL
CPU
CPU_ACCESS
CPU_AGGR
CPU_BYTES_PER_SEC
CPU_FILTER
CPU_GBY
CPU_HASH_JOIN
CPU_JOIN
CPU_NL_JOIN
CPU_RANDOM_ACCESS
CPU_SEQUENTIAL_ACCESS
CPU_SM_JOIN
CPU_SORT
HASH, AGGR
IO
IO_ACCESS
IO_BYTES_PER_SEC
IO_RANDOM_ACCESS
IO_SEQUENTIAL_ACCESS
MEMCMP
MEMCPY
SELECT operation_name, manual_value, calibration_value, default_value
FROM v$optimizer_processing_rate
ORDER BY 1;

exec dbms_stats.set_processing_rate('IO', 100);

SELECT operation_name, manual_value, calibration_value, default_value
FROM v$optimizer_processing_rate
ORDER BY 1;
 
SET_SCHEMA_PREFS
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
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 stats 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@pdbdev

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
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_NO_INVALIDATE_TYPE
Undocumented for internal use only

Valid values are TRUE, FALSE, and DBMS_STATS.AUTO_INVALIDATE		 dbms_stats.to_no_invalidate_type(no_invalidate IN VARCHAR2) RETURN BOOLEAN;
BEGIN
  IF dbms_stats.to_no_invalidate_type('TRUE') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
 
TRANSFER_STATS
Transfers statistics for specified table(s) from a remote database specified by dblink to the local database. It also transfers other statistics related structures like synopses and DML monitoring information. dbms_stats.transfer_stats(
ownname IN VARCHAR2,
tabname IN VARCHAR2,
dblink  IN VARCHAR2,
options IN NUMBER DEFAULT NULL);
-- note: Must be run as SYS
conn sys@pdbdev as sysdba

exec dbms_stats.transfer_stats('UWCLASS', 'SERV_INST', 'REMOTEDB');
 
UNLOCK_PARTITION_STATS
Unlocks the statistics on a partition dbms_stats.unlock_partition_stats(
ownname  IN VARCHAR2,
tabname  IN VARCHAR2,
partname IN VARCHAR2);
conn sys@pdbdev as sysdba

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

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

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_ae
  WHERE owner = 'SH'
  AND subobject_name LIKE 'SALES%Q2_2019');

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_ae
  WHERE owner = 'SH'
  AND subobject_name LIKE 'SALES%Q2_2019');
 
UNLOCK_SCHEMA_STATS
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');
 
VARRAY_TO_CLOB
Converts a  multiple VARRAY elements to a CLOB

Oracle states that this is for internal use only ... so be it ... but it is solid code and understanding the support risk I would use it anywhere		 dbms_stats.varray_to_clob(va DS_VARRAY_4_CLOB) RETURN CLOB;
DECLARE
 c CLOB;
 v ds_varray_4_clob;
BEGIN
  c := 'A' || RPAD('A', 3999) || RPAD('B', 4000) || RPAD('C', 2000);

  dbms_output.put_line(TO_CHAR(LENGTH(c)));

  v := dbms_stats.clob_to_varray(c);

  dbms_output.put_line(v.COUNT);

  c := dbms_stats.varray_to_clob(v);

  dbms_output.put_line(LENGTH(c));
END;
/
 
Related Queries
Default Collection Jobs col schema_user format a20
col interval format a70

SELECT task_name, status, last_good_date, mean_good_cpu_time
FROM dba_autotask_task;
 
Related Demos
The Danger In Not Collecting Stats /* This demo was written by Richard Foote and posted in his blog.
https://richardfoote.wordpress.com/2010/02/16/how-does-an-execution-plan-suddenly-change-when-the-statistics-remain-the-same-in-limbo/
I have duplicated it here because it is absolutely critical that DBA understand the that not collecting stats can cause severe problems. The only changes I have made to Richard's demo are in formatting for readability and replacing some reserved words.

Richard's explanation of this demo should be careful read as I will not repeat it here. All I am doing here is creating a copy of the demo code to make it easier for you to cut-and-paste into SQL*Plus so that you can run it yourself. */

CREATE TABLE muse (rid NUMBER, muse_date DATE, namecol VARCHAR2(10));

DECLARE
 v_count NUMBER;
BEGIN
  v_count:=0;
  FOR i IN 1..1830 LOOP
    FOR j IN 1..1000 LOOP
      v_count := v_count+1;
      INSERT INTO muse VALUES (v_count, sysdate-i, 'MUSE');
    END LOOP;
  END LOOP;
  COMMIT;
END;
/

CREATE INDEX muse_i ON muse(muse_date);

exec dbms_stats.gather_table_stats(USER, 'MUSE', cascade=>true, estimate_percent=>NULL, method_opt=>'FOR ALL COLUMNS SIZE 1');

set autotrace traceonly

SELECT * FROM muse WHERE muse_date > SYSDATE-365;

-------------------------------------------------------------------------
| Id | Operation         | Name |  Rows | Bytes | Cost (%CPU)|     Time |
-------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |   364K|  6401K|   1499 (29)| 00:00:07 |
|* 1 |  TABLE ACCESS FULL| MUSE |   364K|  6401K|   1499 (29)| 00:00:07 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("MUSE_DATE">SYSDATE@!-365)

Statistics
----------------------------------------------------------
      0 recursive calls
      0 db block gets
  30146 consistent gets
   5381 physical reads
      0 redo size
6716417 bytes sent via SQL*Net to client
 267342 bytes received via SQL*Net from client
  24268 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
364000 rows processed

DECLARE
 v_count NUMBER;
BEGIN
  v_count:=1830000;
  FOR i IN 1..365 LOOP
    FOR j IN 1..1000 LOOP
      v_count := v_count+1;
      INSERT INTO into muse VALUES (v_count, sysdate+i, 'MUSE');
    END LOOP;
  END LOOP;
  COMMIT;
END;
/

SELECT * FROM muse WHERE muse_date > (sysdate+365) - 365;

-------------------------------------------------------------------------------------
| Id | Operation                   |   Name | Rows | Bytes | Cost (%CPU) |     Time |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |        |  941 | 16938 |      10 (0) | 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID| MUSE   |  941 | 16938 |      10 (0) | 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | MUSE_I |  941 |       |       5 (0) | 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("MUSE_DATE">SYSDATE@!+365-365)

Statistics
----------------------------------------------------------
       1 recursive calls
       0 db block gets
   52241 consistent gets
     384 physical reads
   73352 redo size
11920158 bytes sent via SQL*Net to client
  268079 bytes received via SQL*Net from client
   24335 SQL*Net roundtrips to/from client
       0 sorts (memory)
       0 sorts (disk)
  365000 rows processed

exec dbms_stats.gather_table_stats(USER, 'MUSE', cascade=>true, estimate_percent=>NULL, method_opt=>'FOR ALL COLUMNS SIZE 1');

SELECT * FROM muse WHERE muse_date > (SYSDATE+365)-365;

-------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU) |     Time |
-------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |  364K|  6415K|   1948 (35) | 00:00:09 |
|* 1 |  TABLE ACCESS FULL| MUSE |  364K|  6415K|   1948 (35) | 00:00:09 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("MUSE_DATE">SYSDATE@!+365-365)

Statistics
----------------------------------------------------------
       0 recursive calls
       0 db block gets
   51285 consistent gets
       0 physical reads
       0 redo size
11920158 bytes sent via SQL*Net to client
  268079 bytes received via SQL*Net from client
   24335 SQL*Net roundtrips to/from client
       0 sorts (memory)
       0 sorts (disk)
  365000 rows processed

/* Remember this demo too the next time you consider using COPY_STATS rather than collecting or SETTING stats. With SET ... you can make the required adjustments. */

Related Topics
Built-in Functions
Built-in Packages
Database Security
Autotrace
DBMS_SCHEDULER
DBMS_STATS_ADVISOR
DBMS_STATS_INTERNAL
DBMS_STATS_INTERNAL_AGG
DBMS_UTILITY
DBMS_XPLAN
Explain Plan
Histograms
System Stats
Trace
Tuning
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx