Oracle Data Warehouse Procedures in DBMS_STATS Oracle Data Warehouse Tips by Burleson Consulting

Procedures in DBMS_STATS

The statistic gathering related procedures in DBMS_STATS are:

PREPARE_COLUMN_VALUES

The procedure prepare_column_vlaues is used to convert user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation  for future storage via set_column_stats.

Generic input arguments:

* srec.epc - The number of values specified in charvals, datevals, numvals, or rawvals.  This value must be between 2 and 256 inclusive. Should be set to 2 for procedures which don't allow histogram information (nvarchar and rowid). The first corresponding array entry should hold the minimum value for the column and the last entry should hold the maximum. If there are more than two entries, then all the others hold the remaining height-balanced or frequency histogram endpoint values (with in-between values ordered from next-smallest to next-largest).  This value may be adjusted to account for compression, so the returned value should be left as is for a call to set_column_stats.

* srec.bkvals - If a frequency distribution is desired, this array contains the number of occurrences of each distinct value specified in charvals, datevals, numvals, or rawvals.  Otherwise, it is merely an ouput argument and must be set to null when this procedure is called.

 Datatype specific input arguments (one of these):

* charvals - The array of values when the column type is character-based. Up to the first 32 bytes of each string should be provided. Arrays must have between 2 and 256 entries, inclusive.

* datevals - The array of values when the column type is date-based.

* numvals - The array of values when the column type is numeric-based.

* rawvals - The array of values when the column type is raw.  Up to the first 32 bytes of each strings should be provided.

* nvmin,nvmax - The minimum and maximum values when the column type is national character set based (NLS).  No histogram information can be provided for a column of this type.

* rwmin,rwmax - The minimum and maximum values when the column type is rowid.  No histogram information can be provided for a columns of this type.

Output arguments:

* srec.minval - Internal representation of the minimum which is suitable for use in a call to set_column_stats.

* srec.maxval - Internal representation of the maximum which is suitable for use in a call to set_column_stats.

* srec.bkvals - array suitable for use in a call to set_column_stats.

* srec.novals - array suitable for use in a call to set_column_stats.

Exceptions:

* ORA-20001: Invalid or inconsistent input values

SET_COLUMN_STATS

The set_column_stats procedure is used to set column-related information.

Input arguments:

* ownname - The name of the schema

* tabname - The name of the table to which this column belongs

* colname - The name of the column

* partname - The name of the table partition in which to store the statistics.  If the table is partitioned and partname is null, the statistics will be stored at the global table level.

* stattab - The user statistics table identifier describing where to store the statistics.  If stattab is null, the statistics will be stored directly in the dictionary.

* statid - The (optional) identifier to associate with these statistics within stattab (Only pertinent if stattab is not NULL).

* distcnt - The number of distinct values

* density - The column density.  If this value is null and distcnt is not null, density will be derived from distcnt.

* nullcnt - The number of nulls

* srec - StatRec structure filled in by a call to prepare_column_values or get_column_stats.

* avgclen - The average length for the column (in bytes)

* flags - For internal Oracle use (should be left as null)

* statown - The schema containing stattab (if different then ownname)

Exceptions:

* ORA-20000: Object does not exist or insufficient privileges

* ORA-20001: Invalid or inconsistent input values

SET_INDEX_STATS

The procedure set_index_stats is used to set index-related information.

Input arguments:

* ownname - The name of the schema

* indname - The name of the index

* partname - The name of the index partition in which to store the statistics.  If the index is partitioned and partname is null, the statistics will be stored at the global index level.

* stattab - The user statistics table identifier describing where to store the statistics.  If stattab is null, the statistics will be stored directly in the dictionary.

* statid - The (optional) identifier to associate with these statistics within stattab (Only pertinent if stattab is not NULL).

* numrows - The number of rows in the index (partition)

* numlblks - The number of leaf blocks in the index (partition)

* numdist - The number of distinct keys in the index (partition)

* avglblk - Average integral number of leaf blocks in which each distinct key appears for this index (partition).  If not provided, this value will be derived from numlblks and numdist.

* avgdblk - Average integral number of data blocks in the table pointed to by a distinct key for this index (partition). If not provided, this value will be derived from clstfct and numdist.

* clstfct - see clustering_factor column of the user_indexes view for a description.

* indlevel - The height of the index (partition)

* flags - For internal Oracle use (should be left as null)

* statown - The schema containing stattab (if different then ownname)

 Exceptions:

* ORA-20000: Object does not exist or insufficient privileges

* ORA-20001: Invalid input value

SET_TABLE_STATS

The procedure set_table_stats is used to set table-related information

Input arguments:

* ownname - The name of the schema

* tabname - The name of the table

* partname - The name of the table partition in which to store the statistics.  If the table is partitioned and partname is null, the statistics will be stored at the global table level.

* stattab - The user statistics table identifier describing where to store the statistics.  If stattab is null, the statistics will be stored directly in the dictionary.

* statid - The (optional) identifier to associate with these statistics within stattab (Only pertinent if stattab is not NULL).

* numrows - Number of rows in the table (partition)

* numblks - Number of blocks the table (partition) occupies

* avgrlen - Average row length for the table (partition)

* flags - For internal Oracle use (should be left as null)

* statown - The schema containing stattab (if different then ownname)

 Exceptions:

* ORA-20000: Object does not exist or insufficient privileges

* ORA-20001: Invalid input value

CONVERT_RAW_VALUE

The procedure convert_raw_value is used to convert 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.

Input argument

* rawval - The raw representation of a column minimum or maximum

Datatype specific output arguments:

* resval - The converted, type-specific value

Exceptions:

* None

GET_COLUMN_STATS

The purpose of the procedure get_column_stats is to get all column-related information for a specified table.

Input arguments:

* ownname - The name of the schema

* tabname - The name of the table to which this column belongs

* colname - The name of the column

* partname - The name of the table partition from which to get the statistics.  If the table is partitioned and partname is null, the statistics will be retrieved from the global table level.

* stattab - The user statistics table identifier describing from where to retrieve the statistics.  If stattab is null, the statistics will be retrieved directly from the dictionary.

* statid - The (optional) identifier to associate with these statistics within stattab (Only pertinent if stattab is not NULL).

* statown - The schema containing stattab (if different then ownname)

Output arguments:

* distcnt - The number of distinct values

* density - The column density

* nullcnt - The number of nulls

* srec - structure holding internal representation of column minimum, maximum, and histogram values

* avgclen - The average length of the column (in bytes)

Exceptions:

* ORA-20000: Object does not exist or insufficient privileges or no statistics have been stored for requested object.
 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.