Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Oct 28, 2003

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

By Amar Kumar Padhi

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM