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
» 
Comment and Contribute