Importing and Exporting Optimizer Statistics

17 Importing and Exporting Optimizer Statistics

You can export and import optimizer statistics from the data dictionary to user-defined statistics tables. You can also copy statistics from one database to another database.

This chapter contains the following topics:

17.1 About Transporting Optimizer Statistics

When you transport optimizer statistics between databases, you must use DBMS_STATS to copy the statistics to and from a staging table, and tools to make the table contents accessible to the destination database.

Importing and exporting are especially useful for testing an application using production statistics. You use DBMS_STATS.EXPORT_SCHEMA_STATS to export schema statistics from a production database to a test database so that developers can tune execution plans in a realistic environment before deploying applications.

The following figure illustrates the process using Oracle Data Pump and ftp.

Figure 17-1 Transporting Optimizer Statistics

Description of "Figure 17-1 Transporting Optimizer Statistics"

As shown in Figure 17-1, the basic steps are as follows:

In the production database, copy the statistics from the data dictionary to a staging table using DBMS_STATS.EXPORT_SCHEMA_STATS.
Export the statistics from the staging table to a .dmp file using Oracle Data Pump.
Transfer the .dmp file from the production host to the test host using a transfer tool such as ftp.
In the test database, import the statistics from the .dmp file to a staging table using Oracle Data Pump.
Copy the statistics from the staging table to the data dictionary using DBMS_STATS.IMPORT_SCHEMA_STATS.

17.2 Transporting Optimizer Statistics to a Test Database: Tutorial

You can transport schema statistics from a production database to a test database using Oracle Data Pump.

Prerequisites and Restrictions

When preparing to export optimizer statistics, note the following:

Before exporting statistics, you must create a table to hold the statistics. The procedure DBMS_STATS.CREATE_STAT_TABLE creates the statistics table.
The optimizer does not use statistics stored in a user-owned table. The only statistics used by the optimizer are the statistics stored in the data dictionary. To make the optimizer use statistics in user-defined tables, import these statistics into the data dictionary using the DBMS_STATS import procedure.
The Data Pump Export and Import utilities export and import optimizer statistics from the database along with the table. When a column has system-generated names, Original Export (exp) does not export statistics with the data, but this restriction does not apply to Data Pump Export.

Note:

Exporting and importing statistics using DBMS_STATS is a distinct operation from using Data Pump Export and Import.

Assumptions

This tutorial assumes the following:

You want to generate representative sh schema statistics on a production database and use DBMS_STATS to import them into a test database.
Administrative user dba1 exists on both production and test databases.
You intend to create table opt_stats to store the schema statistics.
You intend to use Oracle Data Pump to export and import table opt_stats.

To generate schema statistics and import them into a separate database:

On the production host, start SQL*Plus and connect to the production database as administrator dba1.
Create a table to hold the production statistics.

For example, execute the following PL/SQL program to create user statistics table opt_stats:
```
BEGIN
  DBMS_STATS.CREATE_STAT_TABLE ( 
    ownname => 'dba1'
,   stattab => 'opt_stats'
);
END;
/
```
Gather schema statistics.

For example, manually gather schema statistics as follows:
```
-- generate representative workload
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SH');
```
Use DBMS_STATS to export the statistics.

For example, retrieve schema statistics and store them in the opt_stats table created previously:
```
BEGIN
  DBMS_STATS.EXPORT_SCHEMA_STATS (
    ownname => 'dba1'
,   stattab => 'opt_stats'
);
END;
/
```
Use Oracle Data Pump to export the contents of the statistics table.

For example, run the expdp command at the operating schema prompt:
```
expdp dba1 DIRECTORY=dpump_dir1 DUMPFILE=stat.dmp TABLES=opt_stats
```
Transfer the dump file to the test database host.
Log in to the test host, and then use Oracle Data Pump to import the contents of the statistics table.

For example, run the impdp command at the operating schema prompt:
```
impdp dba1 DIRECTORY=dpump_dir1 DUMPFILE=stat.dmp TABLES=opt_stats 
```
On the test host, start SQL*Plus and connect to the test database as administrator dba1.
Use DBMS_STATS to import statistics from the user statistics table and store them in the data dictionary.

The following PL/SQL program imports schema statistics from table opt_stats into the data dictionary:
```
BEGIN
  DBMS_STATS.IMPORT_SCHEMA_STATS( 
    ownname => 'dba1'
,   stattab => 'opt_stats' 
);
END;
/
```