PLSQL_统计信息系列09_统计信息在不同数据库中迁移

2014-01-05 Created By BaoXinjian

一、摘要

---

由于系统在升级，或者克隆数据迁移等等问题中，会导致数据的统计信息的问题

解决方式无非两种，一种进行重新分析，另外一中就是统计信息在不同的数据库进行迁移

前一种方式，如果数据量过大的话，会导致重新分析统计信息耗时时间过长，优点是统计信息应该是最准确的

后一种方式，虽然耗时较短，但是必须确认源端统计信息是正确的，源端和目标端统计信息的比对分析，之后才可以做

(1) Create the holding table using DBMS_STATS:

(2) Move the statistics to the STATS holding table.

(3) Export and Import the data in the STATS table.

(4) Populate the data dictionary in the new database.

 

二、案例

---

1. 创建源端创建统计信息备份表

BEGIN
   DBMS_STATS.create_stat_table ('SCOTT', 'STATS');
END;

2. 查看备份表信息

3. 导出HR.EMPLOYEES的统计信息至备份表中

BEGIN
   DBMS_STATS.export_table_stats ('HR','EMPLOYEES',NULL,'STATS',NULL,TRUE);
END;

4. 查看备份表

5. 通过数据泵，将备份表中的数据从源端导入到目标端

First, run the export:
%exp hr/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 hr/tiger file=expstat.dmp full=y log=implog.txt 

6. 在目标端从备份表的信息导入到字典统计信息中

dbms_stats.import_table_stats('HR','EMPLOYEES',NULL,'STATS',NULL,TRUE);

 

三、其他信息

---

1. 如果要使用STATID信息 - 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);

 

2. 如果不知道STATID - 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.

 

3. 导出整个Schema统计信息 - here are also procedures for performing this activity on the whole schema and database.

For example, IMPORT_SCHEMA_STATS，IMPORT_DATABASE_STATS， 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');

 

4. dbms_stats更多信息 - 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');

 

5. 统计信息删除后，解析计划的变化

5.1 删除统计信息前的解析计划

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)

5.2 删除统计信息

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'

5.3 导入统计信息后的解析计划

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)

 

Thanks and Regards