Subject: How to Use DBMS_STATS to Move Statistics to a Different Database
Doc ID: Note:117203.1 Type: FAQ
Last Revision Date: 01-MAR-2007 Status: PUBLISHED
To provide Oracle Support Services with statistics for debugging, please refer to the following article:
Note 242489.1 Transferring Optimizer Statistics to Support
Purpose:
========
The purpose of this article is to explain how to use the DBMS_STATS package to copy statistics from one database to another database.
How to Use DBMS_STATS to Move Statistics to a Different Database:
=================================================================
You want to copy database statistics from one database to another database.
For example, you want to test certain operations on a scaled-down copy
of your production database and you need the statistics from the production
database.
This article shows you how to use the DBMS_STATS package to copy statistics
from one database to another database.
Summary of Steps:
—————–
There are four basic steps to copy the statistics from one database
to another database using DBMS_STATS:
1) Create a table in your database to hold the statistics.
2) Move the statistics from the data dictionary to the table you created in step 1.
3) Use the Oracle export/import tools to move the data (statistics) from the holding table in your database to a second database.
4) Populate the data dictionary of the second database with the statistics from the holding table that were copied from the original database.
Step Details:
————-
The following shows you the basic syntax to use for each of the above steps:
1) Create the holding table using DBMS_STATS:
SQL> exec dbms_stats.create_stat_table(‘SCOTT’,’STATS’);
PL/SQL procedure successfully completed.
— This command creates the holding table for statistics. The table that is created is owned by SCOTT and called STATS (SCOTT.STATS).
PROCEDURE CREATE_STAT_TABLE
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
OWNNAME VARCHAR2 IN
STATTAB VARCHAR2 IN
TBLSPACE VARCHAR2 IN DEFAULT
2) Move the statistics to the STATS holding table.
SQL> exec dbms_stats.export_table_stats(‘SCOTT’,’EMP’,NULL,’STATS’,NULL,TRUE);
PL/SQL procedure successfully completed.
— This populates the holding table SCOTT.STATS with statistics gathered
on the SCOTT.EMP table, and includes any indexes and places them in
the SCOTT.STATS table.
PROCEDURE EXPORT_TABLE_STATS
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
OWNNAME VARCHAR2 IN
TABNAME VARCHAR2 IN
PARTNAME VARCHAR2 IN DEFAULT
STATTAB VARCHAR2 IN
STATID VARCHAR2 IN DEFAULT
CASCADE BOOLEAN IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT
IMPORTANT:
For all of the above parameters defined as varchar2, you must be sure to use a
alpha character (a-z)as the first character of value (statid=>’A’).
If you begin the value with a non-alpha character, then you must
surround it with double quotes (statid=>'”1″‘).
Otherwise, you will receive an ORA-20001 or ORA-6502. This restriction may be
relaxed in earlier versions, but is enforced in versions 9.2.0.8.0, 10.2.0.x.x.x and higher.
Be aware that if you specify a statid on the export then you MUST specify it on
the import. If you do not specify one on the export then you don’t need on on import
either.
In advance of running dbms_stats.export you should make sure that the statistics are
updated recently. This is normally done by querying the view user_tables. The columns
avg_row_len, num_rows should have non-zero values and the column last_analyzed will
have the date when the stats where last gathered in the source db.
3) Export and Import the data in the STATS table.
First, run the export:
%exp scott/tiger tables=STATS file=expstat.dmp
About to export specified tables via Conventional Path …
. . exporting table STATS …
Then on the new database, run import:
%imp scott/tiger file=expstat.dmp full=y log=implog.txt
4) Populate the data dictionary in the new database.
SQL> exec dbms_stats.import_table_stats(‘SCOTT’,’EMP’,NULL,’STATS’,NULL,TRUE);
PL/SQL procedure successfully completed.
PROCEDURE IMPORT_TABLE_STATS
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
OWNNAME VARCHAR2 IN
TABNAME VARCHAR2 IN
PARTNAME VARCHAR2 IN DEFAULT
STATTAB VARCHAR2 IN
STATID VARCHAR2 IN DEFAULT
CASCADE BOOLEAN IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT
This populates the data dictionary with the statistics for the SCOTT.EMP
table in the new database with the statistics of the SCOTT.EMP table
from the original database.
If you export statistics declaring a specific STATID, then you must use it
when importing the statistics:
exec dbms_stats.export_table_stats(‘SCOTT’,’SJD_TEST’,NULL,’STATS’,'”1″‘,TRUE);
Then you must import with:
exec dbms_stats.import_table_stats(‘SCOTT’,’SJD_TEST’,NULL,’STATS’,'”1″‘,TRUE);
If you do not know the statid then you can see it in the statid column of the stats table:
SQL> select distinct statid,c1 from stats;
If your init.ora parameters are the same in both databases, you expect
the same explain plans on the old and new databases, regardless of the
actual data. This is because the Cost-Based Optimizer makes its decisions
on how to obtain the data based on statistics.
There are also procedures for performing this activity on the whole schema
and database. For example,
IMPORT_SCHEMA_STATS, IMPORT_DATABASE_STATS (and EXPORT_DATABASE_STATS,
EXPORT_SCHEMA_STATS)
To export statististics for an entire schema:
SQL> exec dbms_stats.export_schema_stats(SCOTT,’STATS’);
You may also export system statistcs (cpu/io information):
SQL> exec dbms_stats.export_system_stats(‘STAT’);
For more information on these prodedures, issue the command:
desc dbms_stats
This command describes the package and lists the procedures and arguments.
Examples
Same schema:
============
SD_STAT = table to store statistics in
SD – is my table
SCOTT & JBARLOW – user accounts
‘a’ – optional statid (otherwise NULL)
exec dbms_stats.gather_table_stats(‘SCOTT’,’SD’);
exec dbms_stats.drop_stat_table(‘SCOTT’,’SD_STAT’);
exec dbms_stats.create_stat_table(‘SCOTT’,’SD_STAT’);
exec dbms_stats.export_table_stats(‘SCOTT’,’SD’,NULL,’SD_STAT’,’a’,TRUE,’SCOTT’)
;
set autot trace explain
select * from sd;
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=133 Card=100000 Bytes=5500000)
1 0 TABLE ACCESS (FULL) OF ‘SD’ (Cost=133 Card=100000 Bytes=5500000)
exec dbms_stats.delete_stat_table(‘SCOTT’,’SD’);
select * from sd;
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF ‘SD’
exec dbms_stats.import_table_stats(‘SCOTT’,’SD’,NULL,’SD_STAT’,’a’,TRUE,’SCOTT’)
;
select * from sd;
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=133 Card=100000 Bytes=5500000)
1 0 TABLE ACCESS (FULL) OF ‘SD’ (Cost=133 Card=100000 Bytes=5500000)
Different schema:
=================
You may not export stats from one schema name and import into a different schema name (Bug 1077535).
The schema names much match exactly.
If the target database schema name (import database) is different from the source
database schema name (export database), then you may update the table you exported the statistics
into and set the C5 column to the target schema name.
i.e.
“update table sd_stat set c5 = ‘
where c5 = ‘
and statid = ‘
See example below:
————————————–
SD_STAT = table to store statistics in
SD – is my table
SCOTT & JBARLOW – user accounts
‘a’ – optional statid (otherwise NULL)
—————————————
Checking current explain plan of table sd on target db:
select * from jbarlow.sd;
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=78)
1 0 TABLE ACCESS (FULL) OF ‘SD’ (Cost=1 Card=1 Bytes=78)
Update the SD_STAT table which contains the statistics from source db, schmea SCOTT,
setting the C5 column to the new schema name on the target db:
update sd_stat set c5 = ‘JBARLOW’;
where c5 = ‘SCOTT’
and statid = ‘a’;
commit;
Now import the statistics into the data dictionary on the target db:
exec dbms_stats.import_table_stats(‘JBARLOW’,’SD’,NULL,’SD_STAT’,’a’,TRUE,’SCOTT’);
Check the explain plan. Should reflect new statistics imported:
select * from jbarlow.sd;
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=133 Card=100000 Bytes=5500000)
1 0 TABLE ACCESS (FULL) OF ‘SD’ (Cost=133 Card=100000 Bytes=5500000)
References:
===========
8i New Features for Administrators.
desc dbms_stats