| General |
Note: The drawback to
collecting REDUNDANT column stats is that they have to be loaded
into the dictionary cache, and they have to be considered when the
query is optimised. This waste shared pool space and CPU.
On
the other hand, if the statistics add value about the data - which
does mean EVEN on unindexed columns - queries can run
faster.
Most systems should have only a few column histograms
in place, but there is no obvious requirement that they should only
be on indexed columns. |
| Source |
{ORACLE_HOME}/rdbms/admin/prvtstas.pbl |
| First Available |
8.1.5 |
| Character Sets |
For DBMS_STATS to run
properly may require the US numeric separators. If problems exist
try the following:
alter session
set NLS_NUMERIC_CHARACTERS='.,'; |
| Constants |
| Constant |
Data
Type |
Usage |
| AUTO_CASCADE |
BOOLEAN |
Whether to collect statistics for
indexes or not |
| AUTO_DEGREE |
NUMBER |
Select the degree of
parallelism |
| AUTO_INVALIDATE |
BOOLEAN |
Decide when to invalidate
dependent cursors |
| AUTO_SAMPLE_SIZE |
NUMBER |
Indicate that auto-sample size
algorithms should be used |
| DEFAULT_DEGREE |
NUMBER |
Used to determine the system
default degree of parallelism | |
| Method_opt
Constants |
Accepts:
* FOR ALL
[INDEXED | HIDDEN] COLUMNS [size_clause]
* FOR COLUMNS [size
clause] column|attribute [size_clause] [,column|attribute
[size_clause]...]
size_clause is defined as size_clause :=
SIZE {integer | REPEAT | AUTO | SKEWONLY}
- integer : Number
of histogram buckets: Range [1,254].
- REPEAT : Collects
histograms only on columns that already have
histograms.
- AUTO : Determines the columns to collect histograms
based on
data distribution and the workload of the
columns.
- SKEWONLY : Determines the columns to collect
histograms based on
the data distribution of the
columns.
The default is FOR ALL COLUMNS SIZE AUTO.The default
value can be changed using the SET_PARAM Procedure. |
| Degree Constants |
Degree of parallelism. The
default for degree is NULL. The default value can be changed using
the SET_PARAM Procedure. NULL means use the table default value
specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE
statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the
default value based on the initialization parameters. The
AUTO_DEGREE value determines the degree of parallelism
automatically. This is either 1 (serial execution) or DEFAULT_DEGREE
(the system default value based on number of CPUs and initialization
parameters) according to size of the object. |
| Graularity
Constants |
Granularity of statistics to
collect (only pertinent if the table is partitioned).
| Constant |
Description |
| ALL |
Gathers all (subpartition,
partition, and global) statistics |
| AUTO |
Determines the granularity based
on the partitioning type. This is the default
value |
| DEFAULT |
Gathers global and partition-level
statistics. This option is obsolete, and while currently
supported, it is included in the documentation for legacy
reasons only. You should use the 'GLOBAL AND PARTITION' for
this functionality |
| GLOBAL |
Gathers global
statistics |
| GLOBAL AND
PARTITION |
gathers the global and partition
level statistics. No subpartition level statistics are
gathered even if it is a composite partitioned
object |
| PARTITION |
gathers partition-level
statistics |
| SUBPARTITION |
gathers subpartition-level
statistics | |
| Data Types |
TYPE numarray IS VARRAY(256) OF
NUMBER;
TYPE datearray IS VARRAY(256) OF DATE;
TYPE chararray
IS VARRAY(256) OF VARCHAR2(4000);
TYPE rawarray IS VARRAY(256) OF
RAW(2000);
TYPE fltarray IS VARRAY(256) OF BINARY_FLOAT;
TYPE
dblarray IS VARRAY(256) OF BINARY_DOUBLE;
TYPE StatRec IS
RECORD (
epc NUMBER,
minval
RAW(2000),
maxval RAW(2000),
bkvals NUMARRAY,
novals
NUMARRAY);
Types for
listing stale tables include:
TYPE
ObjectElem IS RECORD (
ownname
VARCHAR2(30), -- owner
objtype
VARCHAR2(6), -- 'TABLE' or
'INDEX'
objname VARCHAR2(30), --
table/index
partname VARCHAR2(30), --
partition
subpartname VARCHAR2(30), --
subpartition
confidence
NUMBER); -- not used
TYPE
ObjectTab IS TABLE OF ObjectElem;
/ |
| Dependencies |
SELECT name
FROM
dba_dependencies
WHERE referenced_name =
'DBMS_STATS'
UNION
SELECT referenced_name
FROM
dba_dependencies
WHERE name = 'DBMS_STATS'; |
| Exceptions |
| Error
Code |
Reason |
| 20000 |
Table already exists
or insufficient privileges (or) Insufficient
privileges
(or) Index does not exist or insufficient
privileges (or) Object does not exist or insufficient
privileges |
| 20001 |
Tablespace does not
exist (or) Bad input value (or) Invalid or inconsistent values
in the user statistics table |
| 20002 |
Bad user statistics
table; may need to be upgraded |
| 20003 |
Unable to set system
statistics (or) Unable to gather system
statistics |
| 20004 |
Parameter does not
exist (or) Error in the INTERVAL mode: system parameter
job_queue_processes must be >0 |
| 20005 |
Object statistics are
locked |
| 20006 |
Unable to restore
statistics, statistics history not
available | |
| Granularity
Parameters |
| Parameter |
Description |
| ALL |
Gathers all
(subpartition, partition, and global) stats. |
| AUTO |
Determines the
granularity based on the partitioning type, and collects the
global, partition level and subpartition level statistics if
the subpartitioning method is LIST, and the global and
partition level only otherwise. This is the default
value. |
| DEFAULT |
Gathers global and
partition-level statistics. This option is obsolete, and while
currently supported, it is included in the documentation for
legacy reasons only. You should use the 'GLOBAL AND PARTITION'
for this functionality. Note that the default value is now
'AUTO'. |
| GLOBAL |
Gathers global
statistics. |
| GLOBAL
AND PARTITION |
Gathers the global and
partition level statistics. No subpartition level statistics
are gathered even if it is a composite partitioned
object. |
| PARTITION |
Gathers
partition-level statistics. |
| SUBPARTITION |
Gathers
subpartition-level
statistics. | |
| Options Parameters |
| Parameter |
Description |
| GATHER |
Gathers statistics on
all objects in the schema |
| GATHER
AUTO |
Gathers all necessary
statistics automatically. Oracle implicitly determines which
objects need new statistics, and determines how to gather
those statistics. When GATHER AUTO is specified, the only
additional valid parameters are stattab, statid, objlist and
statown; all other parameter settings are ignored. Returns a
list of processed objects |
| GATHER
STALE |
Gathers statistics on
stale objects as determined by looking at the
*_tab_modifications views. Also, return a list of objects
found to be stale |
| GATHER
EMPTY |
Gathers statistics on
objects which currently have no statistics. Return a list of
objects found to have no statistics |
| LIST
AUTO |
Returns a list of
objects to be processed with GATHER AUTO |
| LIST
STALE |
Returns a list of
stale objects determined by looking at the *_tab_modifications
views |
| LIST
EMPTY |
Returns a list of
objects which currently have no
statistics | |
| pname Parameter for GET and
SET PARAM |
| Parameter |
Description |
| CASCADE |
The default value for
CASCADE set by SET_PARAM is not used by export/import
procedures.It is used only by gather procedures |
| DEGREE |
Degree of
parallelism |
| ESTIMATE_PERCENT |
|
| METHOD_OPT |
'FOR COLUMNS
REPEAT'
'FOR ALL COLUMNS SIZE REPEAT'
'FOR ALL COLUMNS
SIZE 1' |
| NO_VALIDATE |
| |
| pname Parameter for
GET_SYSTEM_STATS |
| Parameter |
Description |
| CPUSPEED |
Average number of CPU
cycles for each second, in millions, captured for the workload
(statistics collected using 'INTERVAL' or 'START' and 'STOP'
options) |
| SPUSPEEDNW |
Average number of CPU
cycles for each second, in millions, captured for the
noworkload (statistics collected using 'NOWORKLOAD'
option |
| IOSEEKTIM |
Seek time + latency
time + operating system overhead time, in
milliseconds |
| IOTFRSPEED |
I/O transfer speed in
bytes for each millisecond |
| MAXTHR |
Maximum I/O system
throughput, in bytes/second |
| MBRC |
Average multiblock
read count for sequential read, in blocks |
| MREADTIM |
Average time to read
an mbrc block at once (sequential read), in
milliseconds |
| SLAVETHR |
Average slave I/O
throughput, in bytes/second |
| SREADTIM |
Average time to read
single block (random read), in
milliseconds | |
| System Privileges |
For some of the DBMS_STATS
procedures one or more of the following may be
required:
ANALYZE ANY DICTIONARY
ANALYZE ANY |
| Enable automatic statistics
collection |
exec
dbms_scheduler.enable('GATHER_STATS_JOB'); |
| Disable automatic statistics
collection |
exec
dbms_scheduler.disalbe('GATHER_STATS_JOB'); |
| |
| ALTER_DATABASE_TAB_MONITORING |
| Deprecated in
10g |
| |
| ALTER_SCHEMA_TAB_MONITORING |
| Deprecated in 10g |
| |
| ALTER_STATS_HISTORY_RETENTION |
| Enable or disable
autopurging of statistic histories |
dbms_stats.alter_stats_history_retention(<integer>);
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); |
| |
| 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 |
|
select
table_name, column_name, low_value, high_value, num_distinct
from
user_tab_columns
where table_name = 'AIRPLANES';
set
serveroutput on
DECLARE
x
VARCHAR2(100);
BEGIN
dbms_stats.convert_raw_value('373737',
x);
dbms_output.put_line(x);
END;
/ |
Convert RAW to
DATE
Overload 2 |
|
| TBD |
Convert RAW to
NUMBER
Overload 3 |
|
| TBD |
Convert RAW to
BINARY_FLOAT
Overload 4 |
|
| TBD |
Convert RAW to
BINARY_DOUBLE
Overload 5 |
|
| TBD |
| |
| CONVERT_RAW_VALUE_NVARCHAR |
| This procedure 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_VALUE_ROWID |
| This procedure 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. |
| |
| COPY_TABLE_STATS |
| Copy statistics
from one stats table to another |
dbms_stats.copy_table_stats(
ownname
VARCHAR2,
tabname
VARCHAR2,
srcpartname VARCHAR2,
dstpartname
VARCHAR2,
flags
NUMBER); |
| Undocumented |
| |
| CREATE_STAT_TABLE |
| Creates The Table
Required To Capture System Statistics |
dbms_stats.create_stat_table(
ownname
VARCHAR2,
-- schema name
stattab
VARCHAR2,
-- stats table name
tblspace VARCHAR2 DEFAULT NULL); -- stats
table tblspace |
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
VARCHAR2, --
schema name
tabname
VARCHAR2, --
table name
colname
VARCHAR2, --
column name
partname VARCHAR2
DEFAULT NULL, -- partition
name
statab VARCHAR2
DEFAULT NULL, -- user stat table
name
statid VARCHAR2
DEFAULT NULL, -- optional id.
cascade_parts BOOLEAN
DEFAULT TRUE, -- cascade to
partitions
statown VARCHAR2
DEFAULT NULL, -- stat table owner
no_validate
BOOLEAN DEFAULT -- invalidate shared
cursor
to_no_invalidate_type(get_param('NO_INVALIDATE')),
force
BOOLEAN DEFAULT FALSE); --delete locked
statistics |
| exec dbms_stats.delete_column_stats(USER, 'PERSON',
'PERSON_ID'); |
| |
| DELETE_DATABASE_STATS |
| Deletes statistics
for all tables in the database |
dbms_stats.delete_database_stats(
stattab
VARCHAR2 DEFAULT NULL, -- user stat table
name
statid VARCHAR2
DEFAULT NULL, -- optional
ident.
statown VARCHAR2
DEFAULT NULL, -- stat table schema
no_invalidate
BOOLEAN DEFAULT -- invalidate shared
cursor
to_no_invalidate_type(get_param('NO_INVALIDATE')),
force
BOOLEAN DEFAULT FALSE); -- delete locked
stats |
| exec dbms_stats.delete_database_stats; |
| |
| DELETE_DICTIONARY_STATS |
| Deletes statistics
for all dictionary schemas (SYS and SYSTEM) |
dbms_stats.delete_dictionary_stats(
stattab
VARCHAR2 DEFAULT NULL, -- stat table
name
statid VARCHAR2
DEFAULT NULL, -- optional
identifier
statown VARCHAR2
DEFAULT NULL, -- stat table schema
no_invalidate
BOOLEAN DEFAULT -- invalidate shared
cursor
to_no_invalidate_type(get_param('NO_INVALIDATE')),
force
BOOLEAN DEFAULT FALSE); -- delete locked
stats |
| exec dbms_stats.delete_dictionary_stats; |
| |
| DELETE_FIXED_OBJECT_STATS |
| Delete statistics
for all fixed objects |
dbms_stats.delete_fixed_object_stats(
stattab
VARCHAR2 DEFAULT NULL, -- stat table
name
statid VARCHAR2
DEFAULT NULL, -- optional
identifier
statown VARCHAR2
DEFAULT NULL, -- stat table schema
no_invalidate
BOOLEAN DEFAULT -- invalidate shared
cursor
to_no_invalidate_type(get_param('NO_INVALIDATE')),
force
BOOLEAN DEFAULT FALSE); -- delete locked
stats |
| exec dbms_stats.delete_fixed_object_stats; |
| |
| DELETE_INDEX_STATS |
| Delete index
related statistics |
dbms_stats.delete_index_stats(
ownname
VARCHAR2, --
schema name
indname
VARCHAR2, --
index name
partname VARCHAR2
DEFAULT NULL, -- partition
name
stattab VARCHAR2 DEFAULT
NULL, -- stat table
name
statid VARCHAR2
DEFAULT NULL, -- optional
identifier
cascade
BOOLEAN DEFAULT TRUE -- cascade to
partitions
statown VARCHAR2
DEFAULT NULL, -- stat table schema
no_invalidate
BOOLEAN DEFAULT -- invalidate shared
cursor
to_no_invalidate_type(get_param('NO_INVALIDATE')),
force
BOOLEAN DEFAULT FALSE); -- delete locked stats |
| exec dbms_stats.delete_index_stats(USER); |
| |
| DELETE_SCHEMA_STATS |
| Delete statistics
for an entire schema |
dbms_stats.delete_schema_stats(
ownname
VARCHAR2,
-- schema name
stattab
VARCHAR2 DEFAULT NULL, -- stat table
name
statid VARCHAR2
DEFAULT NULL, -- optional
identifier
statown VARCHAR2
DEFAULT NULL, -- stat table schema
no_invalidate
BOOLEAN DEFAULT -- invalidate shared
cursor
to_no_invalidate_type(get_param('NO_INVALIDATE')),
force
BOOLEAN DEFAULT FALSE); -- delete locked stats |
| exec dbms_stats.delete_schema_stats(OWNNAME=>'UWCLASS'); |
| |
| DELETE_SYSTEM_STATS |
| Delete workload
statistics gathered using the 'INTERVAL', 'START' and 'STOP'
options |
dbms_stats.delete_system_stats(
stattab
VARCHAR2 DEFAULT NULL, -- stat table
name
statid VARCHAR2 DEFAULT NULL, -- optional
identifier
statown VARCHAR2 DEFAULT NULL); -- stat
table schema |
| exec dbms_stats.delete_system_stats('STAT_TAB'); |
| |
| DELETE_TABLE_STATS |
| Delete table
related statistics |
dbms_stats.delete_table_stats(
ownname
VARCHAR2,
-- schema name
tabname
VARCHAR2,
-- table name
partname
VARCHAR2 DEFAULT NULL, -- partition
name
stattab
VARCHAR2 DEFAULT NULL, -- stat table
name
statid
VARCHAR2 DEFAULT NULL, -- optional
identifier
cascade_parts BOOLEAN DEFAULT
TRUE, -- cascade to partitions
cascade_columns
BOOLEAN DEFAULT TRUE, -- cascade to all
columns
cascade_indexes BOOLEAN DEFAULT TRUE,
-- cascade to all
indexes
statown
VARCHAR2 DEFAULT NULL, -- stat table
schema
no_invalidate BOOLEAN
DEFAULT --
invalidate shared
cursor
to_no_invalidate_type(get_param('NO_INVALIDATE')),
force
BOOLEAN DEFAULT FALSE); -- delete locked
stats |
| exec dbms_stats.delete_table_stats(USER,
'servers'); |
| |
| DROP_STAT_TABLE |
| Drop a user
statistics table |
dbms_stats.drop_stat_table(
ownname VARCHAR2,
-- schema name
stattab VARCHAR2); -- user stat
table name |
| exec dbms_stats.drop_stat_table(USER,
'PERSON'); |
| |
| EXPORT_COLUMN_STATS |
| Retrieves
statistics for a column and stores them in the user statistics
table |
dbms_stats.export_column_stats(
ownname
VARCHAR2,
-- schema name
tabname
VARCHAR2,
-- table
name
colname VARCHAR2,
-- column name
partname VARCHAR2 DEFAULT
NULL, -- partition
name
stattab VARCHAR2,
-- stat table
name
statid VARCHAR2 DEFAULT NULL,
-- optional
identifier
statown VARCHAR2 DEFAULT NULL);
-- stat table schema |
| exec dbms_stats.export_column_stats(USER, 'servers',
'srvr_id', NULL, 'STAT_TAB'); |
| |
| EXPORT_DATABASE_STATS |
| Retrieves
statistics for all objects in the database |
dbms_stats.export_database_stats(
stattab VARCHAR2
, -- stat table
name
statid VARCHAR2 DEFAULT NULL, --
optional identifier
statown VARCHAR2 DEFAULT NULL); --
stat table schema |
| exec dbms_stats.export_database_stats; |
| |
| EXPORT_DICTIONARY_STATS |
| Retrieves
statistics for all dictionary schemas (SYS and SYSTEM) |
dbms_stats.export_column_stats(
stattab VARCHAR2,
-- stat table
name
statid VARCHAR2 DEFAULT
NULL, -- optional
identifier
statown VARCHAR2 DEFAULT NULL);
-- stat table schema |
| exec dbms_stats.export_dictionary_stats; |
| |
| EXPORT_FIXED_OBJECTS_STATS |
| Retrieves
statistics for fixed tables |
dbms_stats.export_column_stats(
stattab VARCHAR2
DEFAULT NULL, -- stat table
name
statid VARCHAR2 DEFAULT
NULL, -- optional
identifier
statown VARCHAR2 DEFAULT NULL);
-- stat table schema |
| exec dbms_stats.export_fixed_object_stats; |
| |
| EXPORT_INDEX_STATS |
| Retrieves and
stores statistics for an index |
dbms_stats.export_index_stats(
ownname
VARCHAR2,
-- schema name
indname
VARCHAR2,
-- index name
partname VARCHAR2 DEFAULT
NULL, -- partition name
stattab
VARCHAR2,
-- stat table name
statid
VARCHAR2 DEFAULT NULL, -- optional
identifier
statown VARCHAR2 DEFAULT
NULL); -- stat table schema |
| exec dbms_stats.export_index_stats(USER,
'uc_state_city', NULL, 'STAT_TAB'); |
| |
| EXPORT_SCHEMA_STATS |
| Retrieves and
stores statistics for all objects in a schema |
dbms_stats.export_schema_stats(
ownname
VARCHAR2,
-- schema name
stattab
VARCHAR2,
-- stat table name
statid
VARCHAR2 DEFAULT NULL, -- optional
identifier
statown VARCHAR2 DEFAULT NULL);
-- stat table schema |
| exec dbms_stats.export_schema_stats(USER,
'STAT_TAB'); |
| |
| EXPORT_SYSTEM_STATS |
| Retrieves and
stores system statistics |
dbms_stats.export_system_stats(
stattab
VARCHAR2,
-- stat table name
statid
VARCHAR2 DEFAULT NULL, -- optional
identifier
statown VARCHAR2 DEFAULT
NULL); -- stat table schema |
| exec dbms_stats.export_system_stats('STAT_TAB'); |
| |
| EXPORT_TABLE_STATS |
| Retrieves and
stores table statistics |
dbms_stats.export_table_stats(
ownname
VARCHAR2,
-- schema name
tabname
VARCHAR2,
-- table name
partname VARCHAR2 DEFAULT
NULL, -- partition
name
stattab VARCHAR2,
-- stat table name
statid VARCHAR2
DEFAULT NULL, -- optional identifier
cascade
BOOLEAN DEFAULT TRUE, -- TRUE = indexes
too
statown VARCHAR2 DEFAULT
NULL); -- stat table schema |
| exec dbms_stats.export_table_stats(USER, 'servers',
NULL, 'STAT_TAB', NULL, TRUE); |
| |
| FLUSH_DATABASE_MONITORING_INFO |
| Flushes in-memory
monitoring information for all tables in the dictionary |
dbms_stats.flush_database_monitoring_info; |
| exec dbms_stats.flush_database_monitoring_info; |
| |
| GATHER_DATABASE_STATS |
| Gathers statistics
for all objects in the database. Overload 1 |
dbms_stats.gather_database_stats(
estimate_percent
NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample
BOOLEAN DEFAULT
FALSE,
method_opt VARCHAR2
DEFAULT
get_param('METHOD_OPT'),
degree
NUMBER DEFAULT
to_degree_type(get_param('DEGREE')),
granularity
VARCHAR2 DEFAULT
'AUTO',
cascade
BOOLEAN DEFAULT
to_cascade_type(get_param('CASCADE')),
stattab
VARCHAR2 DEFAULT
NULL,
statid
VARCHAR2 DEFAULT
NULL,
options
VARCHAR2 DEFAULT
'GATHER',
objlist
OUT
ObjectTab,
statown
VARCHAR2 DEFAULT
NULL,
gather_sys BOOLEAN
DEFAULT FALSE,
no_invalidate BOOLEAN DEFAULT
to_no_invalidate_type(
get_param('NO_INVALIDATE'))); |
| TBD |
| Gathers statistics
for all objects in the database. Overload 2 |
dbms_stats.gather_database_stats(
estimate_percent
NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample
BOOLEAN DEFAULT
FALSE,
method_opt VARCHAR2
DEFAULT
get_param('METHOD_OPT'),
degree
NUMBER DEFAULT
to_degree_type(get_param('DEGREE')),
granularity
VARCHAR2 DEFAULT
'AUTO',
cascade
BOOLEAN DEFAULT
to_cascade_type(get_param('CASCADE')),
stattab
VARCHAR2 DEFAULT
NULL,
statid
VARCHAR2 DEFAULT
NULL,
options
VARCHAR2 DEFAULT
'GATHER',
statown
VARCHAR2 DEFAULT
NULL,
gather_sys BOOLEAN
DEFAULT FALSE,
no_invalidate BOOLEAN DEFAULT
to_no_invalidate_type(
get_param('NO_INVALIDATE'))); |
| TBD |
| |
| GATHER_DATABASE_STATS_JOB_PROC |
| Undocumented |
| |
| GATHER_DICTIONARY_STATS |
Gathers statistics
for dictionary schemas 'SYS', 'SYSTEM' and schemas of RDBMS
components
Overload 1 |
dbms_stats.gather_dictionary_stats(
comp_id
VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT
to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample
BOOLEAN DEFAULT
FALSE,
method_opt VARCHAR2
DEFAULT
get_param('METHOD_OPT'),
degree
NUMBER DEFAULT
to_degree_type(get_param('DEGREE')),
granularity
VARCHAR2 DEFAULT
'AUTO',
cascade
BOOLEAN DEFAULT
to_cascade_type(get_param('CASCADE')),
stattab
VARCHAR2 DEFAULT
NULL,
statid
VARCHAR2 DEFAULT
NULL,
options
VARCHAR2 DEFAULT 'GATHER
AUTO',
objlist
OUT
ObjectTab,
statown
VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN
DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE'))); |
| TBD |
Gathers statistics
for dictionary schemas 'SYS', 'SYSTEM' and schemas of RDBMS
components
Overload 2 |
dbms_stats.gather_dictionary_stats(
comp_id
VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT
to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample
BOOLEAN DEFAULT
FALSE,
method_opt VARCHAR2
DEFAULT
get_param('METHOD_OPT'),
degree
NUMBER DEFAULT
to_degree_type(get_param('DEGREE')),
granularity
VARCHAR2 DEFAULT
'AUTO',
cascade
BOOLEAN DEFAULT
to_cascade_type(get_param('CASCADE')),
stattab
VARCHAR2 DEFAULT
NULL,
statid
VARCHAR2 DEFAULT
NULL,
options
VARCHAR2 DEFAULT 'GATHER
AUTO',
statown
VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN
DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE'))); |
| TBD |
| |
| GATHER_FIXED_OBJECTS_STATS |
| Gathers statistics
for all fixed objects (dynamic performance tables) |
dbms_stats.gather_fixed_objects_stats
(
stattab VARCHAR2 DEFAULT
NULL,
statid VARCHAR2
DEFAULT NULL,
statown
VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT
to_no_invalidate_type (
get_param('NO_INVALIDATE'))); |
| exec dbms_stats.gather_fixed_objects_stats('SYS',
gather_fixed => TRUE); |
| |
| GATHER_INDEX_STATS |
| Gather Index
Statistics |
dbms_stats.gather_index_stats(
ownname
VARCHAR2,
indname
VARCHAR2,
partname
VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER
DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
stattab
VARCHAR2 DEFAULT
NULL,
statid
VARCHAR2 DEFAULT
NULL,
statown
VARCHAR2 DEFAULT
NULL,
degree
NUMBER DEFAULT
to_degree_type(get_param('DEGREE')),
granularity
VARCHAR2 DEFAULT 'AUTO',
no_invalidate
BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE'))
force
BOOLEAN DEFAULT FALSE); |
| exec dbms_stats.gather_index_stats(USER,
'PK_SERVERS', stattab=>'STAT_TAB'); |
| |
| GATHER_SCHEMA_STATS |
Gather Schema Statistics
Overload 1 |
dbms_stats.gather_schema_stats(
ownname
VARCHAR2,
estimate_percent NUMBER DEFAULT
to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample
BOOLEAN DEFAULT
FALSE,
method_opt
VARCHAR2 DEFAULT
get_param('METHOD_OPT'),
degree
NUMBER DEFAULT to_degree_type(
get_param('DEGREE')),
granularity
VARCHAR2 DEFAULT
GET_PARAM('GRANULARITY'),
cascade
BOOLEAN DEFAULT to_cascade_type(
get_param('CASCADE')),
stattab
VARCHAR2 DEFAULT
NULL,
statid
VARCHAR2 DEFAULT
NULL,
options
VARCHAR2 DEFAULT
'GATHER',
objlist OUT
ObjectTab,
statown
VARCHAR2 DEFAULT NULL,
no_invalidate
BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force
BOOLEAN DEFAULT FALSE); |
exec dbms_stats.gather_schema_stats(USER,
cascade=>TRUE);
or
exec dbms_stats.gather_schema_stats(USER,
degree=>2, options=>'GATHER STALE',
cascade=>TRUE);
or
exec dbms_stats.gather_schema_stats(ownname=>'UWCLASS', degree=>8,
estimate_percent=>17, cascade=>TRUE);
or
exec
dbms_stats.gather_schema_stats(USER',
options=>'GATHER AUTO',
estimate_percent=>dbms_stats.auto_sample_size,
method_opt=>'for
all columns size repeat', degree=>8);
or
exec
dbms_stats.gather_schema_stats(USER,
estimate_percent=>2.5, method_opt=>'FOR ALL COLUMNS SIZE 1',
degree=>4, granularity=>'ALL', options=>'GATHER STALE',
cascade=>TRUE); |
| Overload
2 |
dbms_stats.gather_schema_stats(
ownname
VARCHAR2,
estimate_percent NUMBER DEFAULT
to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample
BOOLEAN DEFAULT
FALSE,
method_opt
VARCHAR2 DEFAULT
get_param('METHOD_OPT'),
degree
NUMBER DEFAULT to_degree_type(
get_param('DEGREE')),
granularity
VARCHAR2 DEFAULT
GET_PARAM('GRANULARITY'),
cascade
BOOLEAN DEFAULT to_cascade_type(
get_param('CASCADE')),
stattab
VARCHAR2 DEFAULT
NULL,
statid
VARCHAR2 DEFAULT
NULL,
options
VARCHAR2 DEFAULT
'GATHER',
statown
VARCHAR2 DEFAULT NULL,
no_invalidate
BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force
BOOLEAN DEFAULT FALSE); |
| See above
Overload 1 |
| |
| GATHER_SYSTEM_STATS |
Gather Statistics For The System |
dbms_stats.gather_system_stats(
gathering_mode
VARCHAR2 DEFAULT 'NOWORKLOAD', -- alt.
INTERVAL
interval
INTEGER DEFAULT
NULL,
stattab VARCHAR2
DEFAULT
NULL,
statid
VARCHAR2 DEFAULT
NULL,
statown VARCHAR2
DEFAULT NULL); |
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
VARCHAR2,
tabname
VARCHAR2,
partname
VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER
DEFAULT NULL to_estimate_percent_type(
get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT
FALSE,
method_opt VARCHAR2
DEFAULT get_param('METHOD_OPT'),
--
or 'FOR ALL COLUMNS SIZE
1',
degree
NUMBER DEFAULT to_degree_type(
get_param('DEGREE')),
granularity
VARCHAR2 DEFAULT get_param('GRANULARITY'),
cascade
BOOLEAN DEFAULT to_cascade_type(
get_param('CASCADE')),
stattab
VARCHAR2 DEFAULT NULL,
statid
VARCHAR2 DEFAULT
NULL,
statown
VARCHAR2 DEFAULT NULL,
no_invalidate
BOOLEAN DEFAULT to_no_invalidate_type(
get_param('NO_INVALIDATE')),
force
BOOLEAN DEFAULT FALSE); |
| exec dbms_stats.gather_table_stats(USER,'SERVERS',stattab=>'STAT_TAB'); |
| |
| GENERATE_STATS |
| Generates object
statistics from previously collected statistics of related
objects |
dbms_stats.generate_stats(
ownname
VARCHAR2,
objname VARCHAR2,
organized NUMBER
DEFAULT 7); -- values from 0 through 10 |
| exec dbms_stats.generate_stats(USER,
'NEW_TABLE'); |
| |
| GET_COLUMN_STATS |
Gets all
column-related information
Overload 1 |
dbms_stats.get_column_stats(
ownname
VARCHAR2,
tabname
VARCHAR2,
colname
VARCHAR2,
partname VARCHAR2 DEFAULT
NULL,
stattab VARCHAR2 DEFAULT
NULL,
statid VARCHAR2
DEFAULT NULL,
distcnt OUT NUMBER,
density
OUT NUMBER,
nullcnt OUT
NUMBER,
srec OUT
StatRec,
avgclen OUT
NUMBER,
statown VARCHAR2 DEFAULT
NULL); |
| TBD |
| Overload
2 |
dbms_stats.get_column_stats(
ownname
VARCHAR2,
tabname
VARCHAR2,
colname
VARCHAR2,
partname
VARCHAR2 DEFAULT
NULL,
stattab
VARCHAR2 DEFAULT
NULL,
statid
VARCHAR2 DEFAULT NULL,
ext_stats OUT
RAW
stattypown OUT VARCHAR2 DEFAULT
NULL,
stattypname OUT VARCHAR2 DEFAULT
NULL,
statown
VARCHAR2 DEFAULT NULL); |
| TBD |
| |
| GET_INDEX_STATS |
Get all
index-related information
Overload 1 |
dbms_stats.get_index_stats(
ownname
VARCHAR2,
indname
VARCHAR2,
partname VARCHAR2 DEFAULT
NULL,
stattab VARCHAR2
DEFAULT
NULL,
statid
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 VARCHAR2
DEFAULT NULL,
cachedblk OUT NUMBER,
cachehit OUT
NUMBER); |
| TBD |
| Overload
2 |
dbms_stats.get_index_stats(
ownname
VARCHAR2,
indname
VARCHAR2,
partname VARCHAR2 DEFAULT
NULL,
stattab VARCHAR2
DEFAULT
NULL,
statid
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 VARCHAR2
DEFAULT NULL,
guessq OUT NUMBER,
cachedblk
OUT NUMBER,
cachehit OUT NUMBER); |
| TBD |
| |
| GET_PARAM |
| Returns the
default value of DBMS_STATS parameters |
dbms_stats.get_param(pname IN
VARCHAR2) RETURN VARCHAR2; |
SELECT dbms_stats.get_param('ESTIMATE_PERCENT')
FROM
dual;
exec dbms_stats.set_param('ESTIMATE_PERCENT','5');
SELECT
dbms_stats.get_param('ESTIMATE_PERCENT')
FROM
dual; |
| |
| GET_STATS_HISTORY_AVAILABILITY |
| Returns oldest
timestamp where statistics history is available |
dbms_stats.get_stats_history_availability
RETURN
TIMESTAMP WITH TIMEZONE; |
SELECT dbms_stats.get_stats_history_availability
FROM
dual; |
| |
| GET_STATS_HISTORY_RETENTION |
| Returns the
current retention value |
dbms_stats.get_stats_history_retention RETURN
NUMBER; |
SELECT dbms_stats.get_stats_history_retention
FROM
dual; |
| |
| GET_SYSTEM_STATS |
| Gets system
statistics from stattab, or from the dictionary if stattab is
NULL |
dbms_stats.get_system_stats(
status OUT
VARCHAR2,
dstart OUT DATE,
dstop OUT
DATE,
pname
VARCHAR2,
pvalue OUT NUMBER,
stattab IN VARCHAR2
DEFAULT NULL,
statid IN VARCHAR2 DEFAULT
NULL,
statown IN VARCHAR2 DEFAULT NULL);
Status options COMPLETED, AUTOGATHERING,
MANUALGATHERING, BADSTATS |
| TBD |
| |
| GET_TABLE_STATS |
| Get all
table-related |
dbms_stats.get_table_stats(
ownname
VARCHAR2,
tabname
VARCHAR2,
partname VARCHAR2
DEFAULT NULL,
stattab
VARCHAR2 DEFAULT
NULL,
statid VARCHAR2
DEFAULT NULL,
numrows OUT
NUMBER,
numblks OUT
NUMBER,
avgrlen OUT
NUMBER,
statown VARCHAR2
DEFAULT NULL,
cachedblk OUT NUMBER,
cachehit OUT
NUMBER); |
| exec dbms_stats.gather_table_stats(USER,
'SERVERS'); |
| |
| 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_STATS |
| Retrieves
statistics for all objects in the database and stores them in the
dictionary |
dbms_stats.import_database_stats(
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_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
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_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
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_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
VARCHAR2,
indname
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_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
VARCHAR2,
stattab
VARCHAR2,
statid
VARCHAR2 DEFAULT
NULL,
statown VARCHAR2
DEFAULT NULL,
no_invalidate BOOLEAN
DEFAULTto_no_invalidate_type(
get_param('NO_INVALIDATE')),
force
BOOLEAN DEFAULT FALSE); |
| exec dbms_stats.import_schema_stats(USER,
'STAT_TAB'); |
| |
| IMPORT_SYSTEM_STATS |
| Retrieves system
statistics from the user statistics table, identified by stattab,
and stores the statistics in the dictionary |
dbms_stats.import_system_stats(
stattab
VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown
VARCHAR2 DEFAULT NULL); |
| exec dbms_stats.import_system_stats('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
VARCHAR2,
tabname
VARCHAR2,
partname VARCHAR2 DEFAULT
NULL,
stattab
VARCHAR2,
statid
VARCHAR2 DEFAULT
NULL,
cascade BOOLEAN
DEFAULT TRUE,
statown
VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT
to_no_invalidate_type(
get_param('NO_INVALIDATE')),
force
BOOLEAN DEFAULT FALSE); |
| exec
dbms_stats.import_table_stats(USER, 'servers',
stattab=>'STAT_TAB'); |
| |
| INIT_PACKAGE |
| Undocumented |
| |
| LOCK_PARTITION_STATS |
| Locks the
statistics of a partition |
dbms_stats.unlock_partition_stats
(
ownname VARCHAR2,
tabname VARCHAR2,
partname
VARCHAR2); |
| exec dbms_stats.lock_partition_stats(USER,
'INVOICES', 'TRANS2007'); |
| |
| LOCK_SCHEMA_STATS |
| Locks the
statistics of all tables of a schema |
dbms_stats.lock_schema_stats(ownname
VARCHAR2); |
| 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 VARCHAR2, tabname VARCHAR2); |
| exec dbms_stats.lock_table_stats(USER,
'SALES'); |
| |
| PREPARE_COLUMN_VALUES |
| Convert
user-specified minimum, maximum, and histogram endpoint
datatype-specific values into internal representation for future
storage using SET_COLUMN_STATS |
dbms_stats.prepare_column_values(
srec
IN OUT StatRec,
charvals
CHARARRAY);
dbms_stats.prepare_column_values(
srec
IN OUT StatRec,
datevals
DATEARRAY);
dbms_stats.prepare_column_values(
srec
IN OUT StatRec,
dblvals
DBLARRAY);
dbms_stats.prepare_column_values(
srec
IN OUT StatRec,
fltvals
FLTARRAY);
dbms_stats.prepare_column_values(
srec
IN OUT StatRec,
numvals
NUMARRAY);
dbms_stats.prepare_column_values(
srec
IN OUT StatRec,
rawvals RAWARRAY); |
| TBD |
| |
| PREPARE_COLUMN_VALUES_NVARCHAR2 |
| Convert
user-specified minimum, maximum, and histogram endpoint
datatype-specific values into internal representation for future
storage using SET_COLUMN_STATS |
dbms_stats.prepare_column_values_nvarchar2(
srec
IN OUT StatRec,
nvmin NVARCHAR2,
nvmax
NVARCHAR2); |
| TBD |
| |
| PREPARE_COLUMN_VALUES_ROWID |
| Convert
user-specified minimum, maximum, and histogram endpoint
datatype-specific values into internal representation for future
storage using SET_COLUMN_STATS |
dbms_stats.prepare_column_values_rowid(
srec
IN OUT StatRec,
nvmin ROWID,
nvmax
ROWID); |
| TBD |
| |
| PURGE_STATS |
| Purge old versions
of statistics saved in the dictionary |
dbms_stats.purge_stats(
before_timestamp TIMESTAMP
WITH TIME ZONE); |
DECLARE
ts TIMESTAMP WITH TIME ZONE;
BEGIN
SELECT SYSTIMESTAMP
INTO ts
FROM dual;
dbms_stats.purge_stats(ts);
END;
/ |
| |
| RESET_PARAM_DEFAULTS |
| Resets the default
values of all parameters to recommended values |
dbms_stats.reset_param_defaults; |
| exec dbms_stats.reset_param_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
TIMESTAMP WITH TIME
ZONE,
force
BOOLEAN DEFAULT FALSE,
no_invalidate BOOLEAN DEFAULT
to_no_invalidate_type(
get_param('NO_INVALIDATE'))); |
| TBD |
| |
| 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
TIMESTAMP WITH TIME
ZONE,
force
BOOLEAN DEFAULT FALSE,
no_invalidate BOOLEAN DEFAULT
to_no_invalidate_type(
get_param('NO_INVALIDATE'))); |
| TBD |
| |
| RESTORE_FIXED_OBJECTS_STATS |
| Restore statistics
of all fixed tables as of a specified timestamp |
dbms_stats.restore_fixed_objects_stats(
as_of_timestamp
TIMESTAMP WITH TIME
ZONE,
force
BOOLEAN DEFAULT FALSE,
no_invalidate BOOLEAN DEFAULT
to_no_invalidate_type(
get_param('NO_INVALIDATE'))); |
| TBD |
| |
| RESTORE_SCHEMA_STATS |
| Restore statistics
of all tables of a schema as of a specified timestamp |
dbms_stats.restore_schema_stats(
ownname
VARCHAR2,
as_of_timestamp TIMESTAMP WITH TIME
ZONE,
force
BOOLEAN DEFAULT FALSE,
no_invalidate BOOLEAN DEFAULT
to_no_invalidate_type(
get_param('NO_INVALIDATE'))); |
| TBD |
| |
| RESTORE_SYSTEM_STATS |
| Restores system
statistics as of a specified timestamp |
dbms_stats.restore_schema_stats(
as_of_timestamp
TIMESTAMP WITH TIME ZONE); |
| TBD |
| |
| RESTORE_TABLE_STATS |
| Restores
statistics of a table as of a specified timestamp |
dbms_stats.restore_table_stats(
ownname
VARCHAR2,
tabname
VARCHAR2,
as_of_timestamp
TIMESTAMP WITH TIME ZONE,
restore_cluster_index BOOLEAN DEFAULT
FALSE,
force
BOOLEAN DEFAULT
FALSE,
no_invalidate
BOOLEAN DEFAULT to_no_invalidate_type(
get_param('NO_INVALIDATE'))); |
| TBD |
| |
| SET_COLUMN_STATS |
Sets column
related information
Overload 1 |
dbms_stats.set_column_stats(
ownname
VARCHAR2,
tabname
VARCHAR2,
colname
VARCHAR2,
partname VARCHAR2
DEFAULT NULL,
stattab
VARCHAR2 DEFAULT
NULL,
statid
VARCHAR2 DEFAULT
NULL,
distcnt
NUMBER DEFAULT
NULL,
density
NUMBER DEFAULT
NULL,
nullcnt
NUMBER DEFAULT
NULL,
srec
StatRec DEFAULT
NULL,
avgclen
NUMBER DEFAULT
NULL,
flags
NUMBER DEFAULT
NULL,
statown VARCHAR2
DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT
to_no_invalidate_type(
get_param('NO_INVALIDATE')),
force
BOOLEAN DEFAULT FALSE); |
| TBD |
Overload
2
Use this version for user-defined statistics |
dbms_stats.set_column_stats(
ownname
VARCHAR2,
tabname
VARCHAR2,
colname
VARCHAR2,
partname VARCHAR2
DEFAULT NULL,
stattab
VARCHAR2 DEFAULT
NULL,
statid
VARCHAR2 DEFAULT NULL,
ext_stats
RAW,
stattypown VARCHAR2 DEFAULT
NULL,
stattypname VARCHAR2 DEFAULT
NULL,
statown VARCHAR2
DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT
to_no_invalidate_type(
get_param('NO_INVALIDATE')),
force
BOOLEAN DEFAULT FALSE); |
| TBD |
| |
| SET_INDEX_STATS |
Sets index-related
information
Overload 1
|
dbms_stats.set_index_stats(
ownname
VARCHAR2,
indname
VARCHAR2,
partname VARCHAR2 DEFAULT
NULL,
stattab VARCHAR2
DEFAULT
NULL,
statid
VARCHAR2 DEFAULT
NULL,
numrows
NUMBER DEFAULT
NULL,
numlblks
NUMBER DEFAULT
NULL,
numdist
NUMBER DEFAULT
NULL,
avglblk
NUMBER DEFAULT
NULL,
avgdblk
NUMBER DEFAULT
NULL,
clstfct
NUMBER DEFAULT
NULL,
indlevel NUMBER
DEFAULT
NULL,
flags
NUMBER DEFAULT
NULL,
statown VARCHAR2
DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT
to_no_invalidate_type(
get_param('NO_INVALIDATE')),
guessq
NUMBER DEFAULT
NULL,
cachedblk NUMBER
DEFAULT NULL,
cachehit
NUMBER DEFUALT
NULL,
force
BOOLEAN DEFAULT FALSE); |
| TBD |
Overload
2
Use this version for user-defined statistics |
dbms_stats.set_index_stats(
ownname
VARCHAR2,
indname
VARCHAR2,
partname VARCHAR2 DEFAULT
NULL,
stattab VARCHAR2
DEFAULT
NULL,
statid
VARCHAR2 DEFAULT NULL,
ext_stats
RAW,
stattypown VARCHAR2 DEFAULT
NULL,
stattypname VARCHAR2 DEFAULT
NULL,
statown VARCHAR2
DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT
to_no_invalidate_type(
get_param('NO_INVALIDATE')),
cachedblk
NUMBER DEFAULT
NULL,
cachehit NUMBER
DEFUALT
NULL,
force
BOOLEAN DEFAULT FALSE); |
| TBD |
| |
| SET_PARAM |
| Sets default
values for parameters of DBMS_STATS procedures |
dbms_stats.set_param(pname IN
VARCHAR2, pval IN VARCHAR2);
pname options:
- CASCADE - The default value for CASCADE set
by SET_PARAM is not used by export/import procedures.It is used
only by gather procedures
- DEGREE
- METHOD_OPT
- NO_INVALIDATE
pval options:
| Option |
Description |
| ALL |
Statistics are collected for all
objects in the system |
| AUTO |
Oracle decides for which objects
to collect statistics |
| ORACLE |
Statistics are collected for all
Oracle owned objects | |
dbms_stats.set_param('CASCADE','DBMS_STATS.AUTO_CASCADE');
dbms_stats.set_param('ESTIMATE_PERCENT','5');
dbms_stats.set_param('DEGREE','NULL'); |
| |
| SET_SYSTEM_STATS |
| Sets system
statistics |
dbms_stats.set_system_stats(
pname
VARCHAR2,
pvalue NUMBER,
stattab VARCHAR2
DEFAULT NULL,
statid VARCHAR2 DEFAULT
NULL,
statown 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_STATS |
| Sets table-related
information |
dbms_stats.set_table_stats(
ownname
VARCHAR2,
tabname
VARCHAR2,
partname VARCHAR2
DEFAULT NULL,
stattab
VARCHAR2 DEFAULT
NULL,
statid
VARCHAR2 DEFAULT
NULL,
numrows
NUMBER DEFAULT
NULL,
numblks
NUMBER DEFAULT
NULL,
avgrlen
NUMBER DEFAULT
NULL,
flags
NUMBER DEFAULT
NULL,
statown VARCHAR2
DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT
to_no_invalidate_type(
get_param('NO_INVALIDATE')),
cachedblk
NUMBER DEFAULT
NULL,
cachehit NUMBER
DEFAULT
NULL,
force
BOOLEAN DEFAULT FALSE); |
conn
hr/hr
set linesize 121
SELECT segment_name,
SUM(blocks)
FROM user_segments
GROUP BY
segment_name;
set autotrace on
-- no
statistics
SELECT department_id, department_name
FROM
departments d
WHERE EXISTS (
SELECT NULL
FROM employees
e
WHERE e.department_id = d.department_id);
-- bad
statistics
exec dbms_stats.set_table_stats(USER, 'EMPLOYEES', numrows=>
1000, numblks=>100);
exec dbms_stats.set_table_stats(USER, 'DEPARTMENTS',
numrows=> 10000, numblks=>1000);
SELECT department_id,
department_name
FROM departments d
WHERE EXISTS (
SELECT NULL
FROM employees e
WHERE
e.department_id = d.department_id);
-- accurate
statistics
exec dbms_stats.set_table_stats(USER, 'EMPLOYEES', numrows=>
107, numblks=>8);
exec dbms_stats.set_table_stats(USER, 'DEPARTMENTS',
numrows=> 27, numblks=>8);
SELECT department_id,
department_name
FROM departments d
WHERE EXISTS (
SELECT NULL
FROM employees e
WHERE
e.department_id = d.department_id); |
| |
| SET_TO_CASCADE_TYPE |
| Undocumented |
| |
| SET_TO_DEGREE_TYPE |
| Undocumented |
| |
| SET_TO_ESTIMATE_PERCENT_TYPE |
| Undocumented |
| |
| SET_TO_NO_INVALIDATE_TYPE |
| Undocumented |
| |
| UNLOCK_PARTITION_STATS |
| Unlocks the
statistics on a partition |
dbms_stats.unlock_partition_stats(
ownname
VARCHAR2,
tabname VARCHAR2,
partname
VARCHAR2); |
| exec dbms_stats.unlock_partition_stats(USER,
'INVOICES', 'TRANS2003'); |
| |
| UNLOCK_SCHEMA_STATS |
| Unlocks the
statistics on all the table in a schema |
dbms_stats.unlock_schema_stats(ownname
VARCHAR2); |
| exec dbms_stats.unlock_schema_stats('UWCLASS'); |
| |
| UNLOCK_TABLE_STATS |
| Unlocks the
statistics on a table |
dbms_stats.unlock_table_stats(
ownname
VARCHAR2,
tabname VARCHAR2); |
| exec dbms_stats.unlock_table_stats(USER,
't1'); |
| |
| UPGRADE_STAT_TABLE |
| Upgrades user
statistics on an older table |
dbms_stats.upgrade_stat_table(
ownname
VARCHAR2,
stattab VARCHAR2); |
| exec dbms_stats.upgrade_stat_table(USER,
'uwstat_tab'); |
| |
| Related
Queries |
| Default Job |
col schema_user
format a20
col interval format a70
SELECT job,
schema_user, interval
FROM
dba_jobs; |