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

October 28, 2003

9.4.5) Exporting and importing statistics with DBMS_STATS

DBMS_STATS also includes routines for gathering statistics and storing them outside the dictionary. This does not influence the optimizer. Most of the procedures in this package as mentioned above have three common parameters - STATID, STATTAB and STATOWN that are related to user processing of statistics.

Advantages of this feature:

1. Estimated statistics at different percentages could be stored and used for testing.

2. Statistics generated on one database could be transferred to another database.

DBMS_STATS.CREATE_STAT_TABLE

DBMS_STATS.CREATE_STAT_TABLE creates a user statistics table for storing dictionary statistics.

Call syntax

dbms_stats.create_stat_table(ownname, stattab, tblspace);

• stattab - statistics table name.
• tblspace - tablespace to be used.

e.g.: creating a user statistics table.

SQL> exec dbms_stats.create_stat_table(ownname => 'SYS', stattab => 'STAT_AT_5PC', -
>  tblspace => 'SYSTEM');

PL/SQL procedure successfully completed.

SQL> desc stat_at_5pc
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ----------------
 STATID                                                         VARCHAR2(30)
 TYPE                                                           CHAR(1)
 VERSION                                                        NUMBER
 FLAGS                                                          NUMBER
 C1                                                             VARCHAR2(30)
 C2                                                             VARCHAR2(30)
 C3                                                             VARCHAR2(30)
 C4                                                             VARCHAR2(30)
 C5                                                             VARCHAR2(30)
 N1                                                             NUMBER
 N2                                                             NUMBER
 N3                                                             NUMBER
 N4                                                             NUMBER
 N5                                                             NUMBER
 N6                                                             NUMBER
 N7                                                             NUMBER
 N8                                                             NUMBER
 N9                                                             NUMBER
 N10                                                            NUMBER
 N11                                                            NUMBER
 N12                                                            NUMBER
 D1                                                             DATE
 R1                                                             RAW(32)
 R2                                                             RAW(32)
 CH1                                                            VARCHAR2(1000)

DBMS_STATS.EXPORT_TABLE_STATS

DBMS_STATS.EXPORT_TABLE_STATS retrieves table statistics for a particular table and puts it in the user statistics table.

Call syntax

dbms_stats.export_table_stats(ownname, tabname, partname, stattab, statid, 
			                      cascade, statown);

• cascade - column and index statistics are also exported.

e.g.: exporting AM01 stat for testing purpose, including table and indexes.

SQL> exec dbms_stats.export_table_stats(ownname => 'SYS', tabname => 'AM01', -
> stattab => 'STAT_AT_5PC', cascade => true, statown => 'SYS');

PL/SQL procedure successfully completed.

DBMS_STATS.EXPORT_COLUMN_STATS

DBMS_STATS.EXPORT_COLUMN_STATS retrieves column statistics for a particular table and puts it in the user statistics table.

Call syntax

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

DBMS_STATS.EXPORT_INDEX_STATS

DBMS_STATS.EXPORT_INDEX_STATS retrieves index statistics for a particular index and puts it in the user statistics table.

Call syntax

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

DBMS_STATS.EXPORT_SCHEMA_STATS

DBMS_STATS.EXPORT_SCHEMA_STATS retrieves statistics for a schema and puts it in the user statistics table.

Call syntax

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

DBMS_STATS.EXPORT_DATABASE_STATS

DBMS_STATS.EXPORT_DATABASE_STATS retrieves statistics for the complete database and puts it in the user statistics table.

Call syntax

dbms_stats.export_database_stats(stattab, statid, statown);

DBMS_STATS.IMPORT_TABLE_STATS

DBMS_STATS.IMPORT_TABLE_STATS retrieves statistics for a table from a user statistics table and stores it in dictionary.

Call syntax

dbms_stats.import_table_stats(ownname, tabname, partname, stattab, statid, 
	cascade, statown);

e.g.: importing statistics for table am01, including column and indexes.

SQL> exec dbms_stats.import_table_stats(ownname => 'SYS', tabname => 'AM01', -
> stattab => 'STAT_AT_5PC', cascade => true, statown => 'SYS');

PL/SQL procedure successfully completed.

DBMS_STATS.IMPORT_COLUMN_STATS

DBMS_STATS.IMPORT_COLUMN_STATS retrieves statistics for a column from a user statistics table and stores it in dictionary.

Call syntax

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

DBMS_STATS.IMPORT_INDEX_STATS

Retrieve statistics for an index from a user statistics table and store it in dictionary.

Call syntax

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

DBMS_STATS.IMPORT_SCHEMA_STATS

DBMS_STATS.IMPORT_SCHEMA_STATS Rretrieves statistics for a schema from a user statistics table and stores it in dictionary.

Call syntax

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

DBMS_STATS.IMPORT_DATABASE_STATS

DBMS_STATS.IMPORT_DATABASE_STATS retrieves statistics for the database from a user statistics table and stores it in dictionary.

Call syntax

dbms_stats.import_schema_stats(stattab, statid, statown);

DBMS_STATS.DROP_STAT_TABLE

DBMS_STATS.DROP_STAT_TABLE drops a user statistics table.

Call syntax

dbms_stats.drop_stat_table(ownname, stattab);

e.g.: dropping my stat table.

SQL> exec dbms_stats.drop_stat_table(ownname => 'SYS', stattab => 'STAT_AT_5PC');

PL/SQL procedure successfully completed.

We will continue with Histograms and DML Monitoring in the next installment of this series.

Due to the extent of coverage for each section, the topics to be covered have been segregated over more than the originally planned installments mentioned in part 1 of this series.

» See All Articles by Columnist Amar Kumar Padhi