조회 수 17064 추천 수 0 댓글 0

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 = '<target schemaname>'
where c5 = '<Source Schema name>'
  and statid = '<Stat Id used while exporting these stats>;"


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

  Note 242489.1 Transferring Optimizer Statistics to Support

Additional Search Words:
========================

stable plan

 


List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
공지 Q&A Oracle관련 게시물만 Sean 2014.04.09 84902
76 Tip 오라클 null값 정리 유주환 2010.04.18 29867
75 Tip sysaux resize 유주환 2010.04.18 25290
74 Tip Analyze 통계정보 dbkill 2010.04.11 13296
73 Tip Expdp/Impdp시 ORA-31633 에러 해결 방법 2 김준호 2010.04.20 22479
72 Tip DATAFILE SIZE 줄이는 계산 1 고구마 2010.04.19 13475
71 Tip 세마포어에 대하여 고구마 2010.04.19 13230
70 Tip DML_DDL_로그기록_체크 유주환 2010.04.19 13417
69 Tip 통계 백업 및 생성 유주환 2010.04.18 16183
68 Tip shared pool / library cache 고구마 2010.04.28 25066
67 Tip DML 문의 처리과정 고구마 2010.04.28 17306
66 Tip Buffer Cahe 관련 대기 이벤트들 고구마 2010.04.28 25635
65 Tip Buffer busy waits 고구마 2010.04.28 19597
64 Tip 버퍼캐시와 OWI 1 고구마 2010.04.28 19873
63 Tip IDLE EVENT 유주환 2010.04.23 21499
62 Tip 권한주기 고구마 2010.04.23 15733
61 Tip 핫백업 디비올리기 유주환 2010.04.09 34980
60 Tip partiton table truncate 및 rebuild 작업 유주환 2010.04.09 10545
» Tip How to Use DBMS_STATS to Move Statistics to a Different Database 고구마 2010.04.09 17064
58 Tip 10G FGA AUDIT 1 고구마 2010.04.09 23607
57 Tip EM 재구성 고구마 2010.04.09 26261
Board Pagination Prev 1 2 3 4 Next
/ 4