Oracle Optimizer: Moving to and working with CBO - Part 4 - Page 4

October 28, 2003

9.4.2) Deleting statistics with DBMS_STATS

DBMS_STATS.DELETE_TABLE_STATS

DBMS_STATS.DELETE_TABLE_STATS deletes table statistics.

Call Syntax

dbms_stats.delete_table_stats
	(ownname, tabname, partname, 
	stattab, statid, cascade_parts, 
	cascade_columns, cascade_indexes, 
	statown);

• cascade_parts - delete statistics for all partitions (partname should be null).
• cascade_columns - delete column statistics. Default is true.
• cascade_indexes - delete index statistics. Default is true.

e.g.: Delete statistics for a table and its columns and indexes.

orAP>exec dbms_stats.delete_table_stats(ownname => 'APPS', tabname => 'AM21');
PL/SQL procedure successfully completed.

e.g.: Delete statistics for table only. Column and index statistics will be preserved.

orAP>exec dbms_stats.delete_table_stats(ownname => 'APPS', tabname => 'AM21', -
> cascade_columns => false, cascade_indexes => false);
PL/SQL procedure successfully completed.

DBMS_STATS.DELETE_COLUMN_STATS

DBMS_STATS.DELETE_COLUMN_STATS deletes individual column statistics.

Call Syntax

dbms_stats.delete_column_stats(ownname, tabname, colname, partname, stattab,
	statid, cascade_parts, statown);

e.g.: Deleting statistics for one column.

orAP>exec dbms_stats.delete_column_stats(ownname => 'APPS', tabname => 'AM21', -
> colname => 'DESCRIPTION');
PL/SQL procedure successfully completed.

DBMS_STATS.DELETE_INDEX_STATS

DBMS_STATS.DELETE_INDEX_STATS deletes individual index statistics.

Call Syntax

dbms_stats.delete_index_stats(ownname, indname, partname, stattab, statid, 
cascade_parts, statown);

e.g.: Deleting index statistics.

orAP>exec dbms_stats.delete_index_stats(ownname => 'APPS', indname => 'AM21_N1');
PL/SQL procedure successfully completed.

DBMS_STATS.DELETE_SCHEMA_STATS

DBMS_STATS.DELETE_SCHEMA_STATS deletes the complete schema statistics.

Call Syntax

dbms_stats.delete_schema_stats(ownname, stattab, statid, statown);

e.g.: Deleting statistics for schema FA.

SQL> exec dbms_stats.delete_schema_stats('FA');
PL/SQL procedure successfully completed.

DBMS_STATS.DELETE_DATABASE_STATS

DBMS_STATS.DELETE_DATABASE_STATS deletes the complete database statistics.

Call Syntax

dbms_stats.delete_database_stats(stattab, statid, statown);

9.4.3) Providing user statistics with DBMS_STATS

DBMS_STATS.SET_TABLE_STATS

Use this routine to set your own statistics in the dictionary instead of the RDBMS statistics.

Call Syntax

dbms_stats.set_table_stats(ownname, tabname, partname, stattab, statid, numrows, 
numblks, avgrlen, flags, statown);

• numrows - number of rows.
• numblks - blocks in the table.
• avgrlen - average row length.
• flags - currently for internal use only.

e.g.:

SQL> exec dbms_stats.set_table_stats(ownname => 'JASHAN', tabname => 'TMP_CKFA', -
> numrows => 12422, numblks => 100, avgrlen => 124);
PL/SQL procedure successfully completed.


jaJA>select owner, num_rows, blocks, avg_row_len
  2  from dba_tables
  3  where table_name = 'TMP_CKFA';
OWNER               |  NUM_ROWS|   BLOCKS|AVG_ROW_LEN
____________________|__________|__________|___________
JASHAN              |     12422|       100|       124

DBMS_STATS.SET_COLUMN_STATS

DBMS_STATS.SET_COLUMN_STATS sets column statistics explicitly.

Call Syntax

dbms_stats.set_column_stats(ownname, tabname, colname, partname, stattab, statid, 
	distcnt, density, nullcnt, srec, avgclen, 
	flags, statown);

• distcnt - number of distinct values.
• density - column density. If null, it is derived from distcnt.
• nullcnt - null count.
• srec - record of type DBMS_STATS.STATREC, value populated by
• call to PREPARE_COLUMN_VALUES or GET_COLUMNS_STATS.
• avgclen - average column length.

e.g.: Setting statistics for one column of a table.

jaJA>exec dbms_stats.set_column_stats(ownname => 'JASHAN', tabname => 
'TMP_CKFA', -
> colname => 'CODE', distcnt => 1000, density => 5, nullcnt => 0, 
avgclen => 12);
PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, low_value, high_value, density,
  2         num_nulls, num_buckets, avg_col_len
  3  from   dba_tab_columns
  4* where  table_name = 'TMP_CKFA' and column_name = 'CODE'

COLUMN_NAME|NUM_DISTINCT|LOW_VA|HIGH_VA|DENSITY| NUM_NULLS|NUM_BUCKETS|AVG_COL_LEN
___________|____________|______|_______|_______|__________|___________|___________
CODE       |        1000|      |       |      5|         0|          1|         12

DBMS_STATS.SET_INDEX_STATS

Set index statistics.

Call Syntax

dbms_stats.set_index_stats(ownname, indname, partname, stattab, statid, numrows, numlblks, 
	numdist, avglblk, avgdblk, clstfct, indlevel, flags, statown);

• numlblks - number of leaf blocks.
• numdist - number of distinct keys.
• avglblk - average number of leaf blocks in which each distinct key appears.
• avgdblk - average number of data blocks in the table pointed to by the distinct keys.
• clstfct - clustering factor.
• indlevel - Height of the index.

e.g.:

jaJA>exec dbms_stats.set_index_stats(ownname => 'JASHAN', indname => 'TMP_CKFA_N1', -
> numrows => 1000, numlblks => 100, numdist => 100, avglblk => 1, avgdblk => 12, -
> clstfct => 1000, indlevel => 2);

PL/SQL procedure successfully completed.

jaJA>select num_rows, blevel, leaf_blocks, avg_leaf_blocks_per_key,
  2         avg_data_blocks_per_key, clustering_factor, user_stats
  3  from   dba_indexes
  4  where  index_name = 'TMP_CKFA_N1';
  
NUM_ROWS|BLEVEL|LEAF_BLOCKS|AVG_LEAF_B|AVG_DATA_B|CLUSTERING_F|USE
________|______|___________|__________|__________|____________|___
    1000|     2|        100|         1|        12|        1000|YES