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:
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
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
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
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;
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;
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;
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';
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';
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;
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);
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');
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);
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);
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
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
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);
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
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);
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));
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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$'));
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;
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
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
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
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);
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);
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
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
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);
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);
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);
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
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);
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);
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);
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);
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);
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);
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
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);
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);
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);
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);
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;
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';
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);
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;
/
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_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);
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);
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);
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;
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_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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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';
-- 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';
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);
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);
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);
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;
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;
/
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;
/
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;
/
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;
/
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;
/
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;
/
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;
/
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
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
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
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
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')));
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);
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);
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);
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);
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);
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
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);
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
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');
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');
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');
/* 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. */
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');
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;
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. */