PLSQL_总结信息比比皆是09_总计信息在不同数据库中迁移

2014-01-05 Created By
BaoXinjian

ACCESS 1一、摘要


鉴于系统在晋级,或者克隆数据迁移等等问题中,会造成数据的总结音讯的题目

化解措施只有二种,一种举办双重分析,其它一中就是总括音信在不同的数据库举办搬迁

前一种方法,倘使数据量过大的话,会导致重新分析总计音讯耗时时间过长,优点是总计音信应该是最纯粹的

后一种艺术,尽管耗时较短,不过必须认可源端总结信息是没错的,源端和对象端总括信息的比对分析,之后才得以做

(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.

 

ACCESS 2二、案例


  1. 创建源端创立总结音信备份表
BEGIN
   DBMS_STATS.create_stat_table ('SCOTT', 'STATS');
END;
  1. 查阅备份表消息

ACCESS 3

3.
导出HR.EMPLOYEES的总计消息至备份表中

BEGIN
   DBMS_STATS.export_table_stats ('HR','EMPLOYEES',NULL,'STATS',NULL,TRUE);
END;
  1. 查阅备份表

ACCESS 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);

 

ACCESS 5三、其他音讯


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);

 

  1. 一旦不晓得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’);

 

  1. 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 删除总括音信前的分析计划

ACCESS,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

ACCESS 6

相关文章