DBMS STATS

DBMS_STATS

The DBMS_STATS package provides an interface which can view and modifies the stats information. By using stored procedures and functions, the stats information can be established and updated, also it can configure or delete the stats information for each column, index, and table or per each system

The procedures and functions comprised of the DBMS_STATS package are in the following table below.

Procedures/Functions	Description
COPY_TABLE_STATS	Copies stats information of a partition to a new partition.
DELETE_COLUMN_STATS	Deletes stats information in column(s) of specific tables.
DELETE_DATABASE_STATS	Deletes stats information of all tables.
DELETE_INDEX_STATS	Used to dlete stats information of specific indexes.
DELETE_TABLE_STATS	Delete stats information of specific tables.
DELETE_SYSTEM_STATS	Deletes stats information of the database system.
GATHER_DATABASE_STATS	Gathers stats information of all tables.
GATHER_INDEX_STATS	Gathers stats information of specific indexes.
GATHER_SYSTEM_STATS	Gathers stats information of database system.
GATHER_TABLE_STATS	Gathers stats information of specific tables.
GET_COLUMN_STATS	Views stats information of column(s) in specific tables.
GET_INDEX_STATS	Views stats information of specific indexes.
GET_SYSTEM_STATS	View stats information of database system.
GET_TABLE_STATS	Views stats information of specific tables.
LOCK_TABLE_STATS	Locks the stats information of a specific table.
SET_COLUMN_STATS	Views stats information of column(s) in specific tables.
SET_INDEX_STATS	Alters stats information of specific indexes.
SET_PRIMARY_KEY_STATS	Alters stats information of PRIMARY KEY INDEX of a specific table.
SET_SYSTEM_STATS	Alters stats information of the database system.
SET_TABLE_STATS	Alters stats information of (a) specific tables.
SET_UNIQUE_KEY_STATS	Alter stats information of UNIQUE KEY INDEX of (a) specific tables.
UNLOCK_TABLE_STATS	Unlocks the stats information of a specific table.

SET_PRIMARY_KEY_STATS

This procedure alters stats information of PRIMARY KEY INDEX of a specific table.

Syntax

SET_PRIMARY_KEY_STATS (
  ownname       VARCHAR(128),
  tabname       VARCHAR(128),
  keycount      BIGINT DEFAULT NULL,
  numpage       BIGINT DEFAULT NULL,
  numdist       BIGINT DEFAULT NULL,
  clusteringfactor BIGINT DEFAULT NULL,
  indexheight   BIGINT DEFAULT NULL,
  avgslotcnt    BIGINT DEFAULT NULL,
  no_invalidate BOOLEAN DEFAULT FALSE );

Parameters

Name	In/Output	Data Type	Description
ownname	IN	VARCHAR(128)	Name of the index owner
tabname	IN	VARCHAR(128)	Name of the table for which statistics to be changed
keycount	IN	BIGINT	Number of records in the index
numpage	IN	BIGINT	Number of pages in the index
numdist	IN	BIGINT	Number of unique keys in the index
clusteringfactor	IN	BIGINT	Degree to which the data is aligned with the index
indexheight	IN	BIGINT	Depth from the root of the index to the leaf node
avgslotcnt	IN	BIGINT	Average number of records stored in the index leaf node.
no_invalidate	IN	BOOLEAN	Whether to rebuild execution plans for all queries related to the indexes for which statistics were collected. The default is FALSE, which rebuilds the execution plan. If do not want to rebuild, enter TRUE.

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

iSQL> EXEC DBMS_STATS.SET_PRIMARY_KEY_STATS( 'SYS', 'T1', 1, 2, 3, 4, 5, 6, TRUE );
__SYS_IDX_ID_148 c integer;
Execute success.

SET_UNIQUE_KEY_STATS

This procedure alters stats information of UNIQUE KEY INDEX

Syntax

SET_UNIQUE_KEY_STATS (
  ownname       VARCHAR(128),
  tabname       VARCHAR(128),
  colnamelist   VARCHAR(32000),
  keycount      BIGINT DEFAULT NULL,
  numpage       BIGINT DEFAULT NULL,
  numdist       BIGINT DEFAULT NULL,
  clusteringfactor BIGINT DEFAULT NULL,
  indexheight   BIGINT DEFAULT NULL,
  avgslotcnt    BIGINT DEFAULT NULL,
  no_invalidate     BOOLEAN DEFAULT FALSE );

Parameters

Name	In/Out	Data Type	Description
ownname	IN	VARCHAR(128)	Name of the index owner
tabname	IN	VARCHAR(128)	Name of the table for which statistics to be changed
colnamelist	IN	VARCHAR(32000)	List of column names to change statistics for. If DESC is specified in a column when creating a UNIQUE KEY INDEX, it must also be specified in uppercase in the colnamelist.
keycount	IN	BIGINT	Number of records in the index
numpage	IN	BIGINT	Number of pages in the index
numdist	IN	BIGINT	Number of unique keys in the index
clusteringfactor	IN	BIGINT	Degree to which the data is aligned with the index
indexheight	IN	BIGINT	Depth from the root of the index to the leaf node
avgslotcnt	IN	BIGINT	Average number of records stored in the index leaf node.
no_invalidate	IN	BIGINT	Whether to rebuild execution plans for all queries related to the indexes for which statistics were collected. The default is FALSE, which rebuilds the execution plan. If do not want to rebuild, enter TRUE.

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

iSQL> EXEC DBMS_STATS.SET_UNIQUE_KEY_STATS( 'SYS', 'T1', 'C1,C2', 1, 2, 3, 4, 5, 6, TRUE );
__SYS_IDX_ID_149
Execute success.

LOCK_TABLE_STATS

This procedure locks the stats information of a specific table.

Syntax

LOCK_TABLE_STATS (
  ownname VARCHAR(128),
  tabname VARCHAR(128) );

Parameters

Name	In/Out	Data Type	Description
ownname	IN	VARCHAR(128)	Name of the table owner
tabname	IN	VARCHAR(128)	Name of the table for which statistics to be changed

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

iSQL> EXEC DBMS_STATS.LOCK_TABLE_STATS( 'SYS', 'T1' );
Execute success.

UNLOCK_TABLE_STATS

This procedure unlocks the stats information of a specific table.

Syntax

UNLOCK_TABLE_STATS (
  ownname VARCHAR(128),
  tabname VARCHAR(128) );

Parameters

Name	In/Out	Data Type	Description
ownname	IN	VARCHAR(128)	Name of the table owner
tabname	IN	VARCHAR(128)	Name of the table for which statistics to be changed

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

iSQL> EXEC DBMS_STATS.UNLOCK_TABLE_STATS( 'SYS', 'T1' );
Execute success.