|
在使用CBO优化器模式的Oracle数据库中，统计信息是CBO生成最佳执行计划的重要依据。这些统计信息通常包括列级、表级、索引、系统级别的统计信
息等。所有的这些统计信息都可以被备份，导入导出也可以被锁定与解锁。因此相应地，我们可以导出列级、表级、索引、系统级别的统计信息。通过导出导入统计
信息，可以在测试环境来模拟产生环境进行数据库性能优化，SQL调优等。本文主要描述了基于schema级别导出导入统计信息到不同的数据库。
关于统计信息的具体描述与用法，本文不作详细介绍，大家可以参考 Oracle references 。
1、导入导出统计信息的情形或作用
2、schema级别统计信息导出导入的主要步骤
3、创建统计信息的示例 BEGIN
DBMS_STATS.gather_schema_stats (ownname => 'SCOTT',
options => 'GATHER AUTO',
estimate_percent => DBMS_STATS.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 8);
END;
/
4、演示导出导入统计信息导不同的DB --下面将数据库mmbo5上scott的统计信息导入到另外一个数据库mmbo4下的scott中
a、收集统计信息
scott@MMBO5> delete from emp where deptno=20;
scott@MMBO5> commit;
scott@MMBO5> exec dbms_stats.gather_schema_stats('SCOTT',cascade=>true);
scott@MMBO5> select table_name,num_rows,last_analyzed from user_tables;
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------
DEPT 4 20130513 22:19:10
EMP 9 20130513 22:19:10 --->emp表被删除了5条记录还剩9条
BONUS 0 20130513 22:19:10
SALGRADE 5 20130513 22:19:10
TT 4 20130513 22:19:10
b、创建用于存储统计信息的表
--可以指定表存放于哪个表空间，如果缺省则存放到用户的缺省表空间
scott@MMBO5> exec dbms_stats.create_stat_table('SCOTT', 'STATS_TABLE');
PL/SQL procedure successfully completed.
c、导出scott的统计信息到stats_table
scott@MMBO5> exec dbms_stats.export_schema_stats('SCOTT','STATS_TABLE','SCOTT');
PL/SQL procedure successfully completed.
scott@MMBO5> exit
d、导出统计信息到dump文件
oracle@Dev-DB-04:~> expdp scott/tiger directory=db_dump_dir dumpfile=st.dmp logfile=st.log tables=STATS_TABLE
Export: Release 10.2.0.4.0 - 64bit Production on Monday, 13 May, 2013 22:20:53
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "SCOTT"."STATS_TABLE" 17.82 KB 74 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u02/database/MMBO5/BNR/dump/st.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 22:21:11
e、ftp统计信息到目的服务器并导入dump文件
--由于下面是在同一台服务器，所以直接用cp命令复制dump文件到特定目录
oracle@Dev-DB-04:~> cp /u02/database/MMBO5/BNR/dump/st.dmp /u02/database/MMBO4/BNR/dump
f、使用datapump impdp导入dmp文件到目的schema
oracle@Dev-DB-04:~> export ORACLE_SID=MMBO4
oracle@Dev-DB-04:~> impdp scott/tiger directory=db_dump_dir dumpfile=st.dmp logfile=imp_st.log tables=STATS_TABLE
Import: Release 10.2.0.4.0 - 64bit Production on Monday, 13 May, 2013 22:22:57
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."STATS_TABLE" 17.82 KB 74 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 22:23:03
g、导入统计信息到目标数据库的schema
--导入前先查看一下本数据库scott的统计信息
scott@MMBO4> select table_name,num_rows,last_analyzed from user_tables;
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------
STATS_TABLE
DEPT 4 20130513 22:16:05
EMP 14 20130513 22:16:05
BONUS 0 20130513 22:16:05
SALGRADE 5 20130513 22:16:05
T 49991 20130513 22:16:20
6 rows selected.
--导入统计信息
scott@MMBO4> exec dbms_stats.import_schema_stats('SCOTT','STATS_TABLE','SCOTT');
PL/SQL procedure successfully completed.
--可以通过查询视图dba_optstat_operations获得目标数据库上统计信息日志
ALTER SESSION SET nls_timestamp_tz_format='yyyy-mm-dd hh24:mi:ss';
COL start_time FORMAT a14
COL end_time FORMAT a14
COL operation FORMAT a30
COL target FORMAT a30
SET LINESIZE 120
SELECT operation,
target,
start_time,
end_time
FROM dba_optstat_operations
WHERE operation LIKE 'import%';
OPERATION TARGET START_TIME END_TIME
------------------------------ ------------------------------ -------------- --------------
import_schema_stats SCOTT 2013-05-13 22: 2013-05-13 22:
24:23 24:23
--Author: Robinson
--Blog : http://blog.csdn.net/robinson_0612
--下面的查询也可以看到此时scott下LAST_ANALYZED已经被更新为与源服务器上的统计信息相同
scott@MMBO4> select table_name,num_rows,last_analyzed from user_tables;
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------
STATS_TABLE
DEPT 4 20130513 22:19:10
EMP 9 20130513 22:19:10
BONUS 0 20130513 22:19:10
SALGRADE 5 20130513 22:19:10
T 49991 20130513 22:16:20
6 rows selected.
scott@MMBO4> select count(*) from emp;
COUNT(*)
----------
14
--实际上在mmbo4上表emp的数据依旧为14条，但统计信息为9条，是来自源数据库的统计信息
--对于源数据库schema上存在，但目标数据库schema不存在的对象，如mmbo5上的表tt，在mmbo4上不会被导入
--对于源数据库schema上不存在，但目标数据库schema存在的对象，如mmbo4上的表t，其统计信息没有被更新
h、根据需要可以考虑是否清除存储统计信息的表
scott@MMBO4> exec dbms_stats.drop_stat_table('SCOTT','STATS_TABLE');
PL/SQL procedure successfully completed.
--对于系统级别的统计信息的导入导出，此处不做演示，需要注意的是应使用dbms_stats中相应的导入导出procedure.
--dbms_stats.gather_system_stats
--dbms_stats.import_system_stats
--dbms_stats.export_system_stats
更多参考 有关Oracle RAC请参考 有关Oracle 网络配置相关基础以及概念性的问题请参考： 有关基于用户管理的备份和备份恢复的概念请参考 有关RMAN的备份恢复与管理请参考 有关ORACLE体系结构请参考 |
/1 
