如何使用DBMS_STATS将统计信息移动到另一个数据库
本文提供支持oracle用于调试的统计。我们看看如何使用DBMS_STATS将统计移动到不同的数据库，例如你想测试默写操作的缩小版的副本的时候，从您的生产库上，您需要生产统计得到统计。
步骤摘要：一共有四个步骤
1、创建数据库中的表来保存。
2、讲统计信息从数据字典移动到第一步创建的表中。
3、用oracle exp/imp工具将数据从这个表里的数据移动到另外一个库中。
4、将源数据表中的数据拷贝该库的数据字典中。
具体步骤：
1、 用DBMS_STATS创建一个表
SQL> exec dbms_stats.create_stat_table('SCOTT','STATS');
PL/SQL procedure successfully completed.
（SCOTT下的STATS表）
2、 将统计数据移至STAT表中
SQL> exec dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS',NULL,TRUE);
PL/SQL procedure successfully completed.
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
注意事项：以上所有定义为varchar2的参数，你必须用用字母（a-z）而且第一个字符的staid>=”A”。如果不是的话，必须用双引号括起来。否则将报ORA-20001 or ORA-6502错误，这个错误在老版本中可能不是规定很严格，但是在新的版本中控制的很严格，例如9.2.0.8.0或者更高。
在运行dbms_stats.export之前应该确定统计信息最近是更新过的。这个通常查询user_tables这个视图，如果avg_row_len,num_rows 应该是非控值,列last_analyzed将是最后一次在源数据库的数据。
3、 导出和导入STATS中的数据
首先在源数据库运行导出命令：
%exp scott/tiger tables=STATS file=expstat.dmp
然后再目标库运行导入命令：
%imp scott/tiger file=expstat.dmp full=y log=implog.txt
4、 在源数据库将数据移到数据字典中
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
这个数据字典填充的数据是从源库SCOTT.emp到目库SCOTT.emp中的数据。
如果你导出的统计是定义为一个特殊的STATID，那你必须用一下方法导入：
exec dbms_stats.export_table_stats('SCOTT','SJD_TEST',NULL,'STATS','"1"',TRUE);
然后进行导入：
exec dbms_stats.import_table_stats('SCOTT','SJD_TEST',NULL,'STATS','"1"',TRUE);
如果你不知道STATID那么你可以看stats表中的statid列：
SQL> select distinct statid,c1 from stats;
如果两个数据库中的init.ora初始化参数一样的话，你希望使用同样的计划在两个库中，不管实际数据是什么。这是因为基于成本的优化决定如何获得基于统计的数据。
对这个过程也可以对整个角色和数据库执行例如：
IMPORT_SCHEMA_STATS, IMPORT_DATABASE_STATS (and EXPORT_DATABASE_STATS,
EXPORT_SCHEMA_STATS)
导出角色全部的统计信息
SQL> exec dbms_stats.export_schema_stats('SCOTT','STATS');
如果想看更多关于这些过程的信息：
Desc dbms_stats
例如：
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:
=================
你不应该从一个角色名字导出再导入到一个不同的角色名中(Bug 1077535).
角色名字应该匹配
如果目标库与源库角色名不同，你应该更新你将导出数据的表然后在目标库设置C5列。
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;
现在将数据导入到目标库中:
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)
Note:
=====
Importing statistics will overwrite any previous statistics that existed for the table having
statistics imported. For example, if previously there were histogram statistics and
you import statistics that do not include histograms, there will no longer be any histogram information.
When importing into later versions, if it is found that the structure of the statistics table has
changed then the DBMS_STATS.UPGRADE_STAT_TABLE procedure may resolve these
Errors
ORA-6502; ORA-20001; ORA-24372
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2012-1-4 8:28:39 |
赞(
操作
