PLSQL_統計信息系列3_統計信息在不同數據庫中遷移（案例），plsql統計信息

PLSQL_統計信息系列3_統計信息在不同數據庫中遷移（案例），plsql統計信息

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