dbms_stats 导入导出 schema 级别统计信息

    在使用CBO优化器模式的Oracle数据库中,统计信息是CBO生成最佳执行计划的重要依据。这些统计信息通常包括列级、表级、索引、系统级别的统计信息等。所有的这些统计信息都可以被备份,导入导出也可以被锁定与解锁。因此相应地,我们可以导出列级、表级、索引、系统级别的统计信息。通过导出导入统计信息,可以在测试环境来模拟产生环境进行数据库性能优化,SQL调优等。本文主要描述了基于schema级别导出导入统计信息到不同的数据库。

    关于统计信息的具体描述与用法,本文不作详细介绍,大家可以参考 Oracle references 。

1、导入导出统计信息的情形或作用   a、可以把生产环境的统计信息导入到测试环境使得执行计划的产生能极大程度上等同于生产环境   b、通过导入导出可以冻结执行计划,即控制与对比不同环境中的执行计划   c、统计信息可以在重新analyze schema之前进行备份,防止analyze后性能下降   d、系统级别的统计信息可以被移植到小型服务器来模拟Oracle在大型服务器的运行环境   e、系统级别的统计信息也可以用于迁移到新服务器以保证一致的执行计划直到真正开始使用新服务器   f、由于不同的工作负载需要使用不同的统计信息,可以在这些负载运行前给予合适的统计信息(如白天的OLTP,晚上为批量job模式)

2、schema级别统计信息导出导入的主要步骤   a、收集统计信息(源schema或者含系统级别)   b、创建用于存储统计信息的表(如stats_table)   c、使用dbms_stats.export_schema_stats导出schema统计信息到表stats_table   d、使用datapump expdp导出stats_table表。(可考虑SQL*Plus copy方式实现来避免导入导出)   e、ftp或scp/cp dump文件到目的schema所在的服务器   f、使用datapump impdp导入dmp文件到目的schema,如果需要备份,应在导入之前备份原统计信息   g、使用dbms_stats.import_system_stats导入统计信息到所需的schema

3、创建统计信息的示例 --下面是一个基于schema scott级别进行收集统计信息的例子

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏电光石火

数据库中的左连接(left join)和右连接(right join)区别

Left Join / Right Join /inner join相关 关于左连接和右连接总结性的一句话: 左连接where只影向右表,右连接...

2058
来自专栏企鹅号快讯

带你认识一下mysql中数据库information

information_schema 大家在安装或使用MYSQL时,会发现除了自己安装的数据库以外,还有一个information_schema数据库。 inf...

2168
来自专栏乐沙弥的世界

Oracle 分区表

随着表的不断增大,对于新纪录的增加、查找、删除等(DML)的维护也更加困难。对于数据库中的超大型表,可通过把它的数据分成若干个小表,从而简化数据库的管理活动。...

1422
来自专栏数据库

按图索骥:SQL中数据倾斜问题的处理思路与方法

数据倾斜即表中某个字段的值分布不均匀,比如有100万条记录,其中字段A中有90万都是相同的值。这种情况下,字段A作为过滤条件时,可能会引起一些性能问题。 本文通...

2079
来自专栏Hadoop数据仓库

去除重复数据

有两个意义上的重复记录,一是完全重复的记录,也即所有字段均都重复,二是部分字段重复的记录。对于第一种重复,比较容易解决,只需在查询语句中使用distinct关键...

2145
来自专栏JAVA同学会

MySql分页查询慢|这里告诉你答案

  我们在开发的过程中使用分页是不可避免的,通常情况下我们的做法是使用limit加偏移量:select * from table where column=xx...

1352
来自专栏Java成神之路

Saiku_学习_02_Schema Workbench 开发mdx和模式文件

saiku的查询都是通过cube来进行的。因此每当我们要进行一次多维度查询时,都要先修改xml、上传、重启才能生效,不仅效率低,还不利于学习和理解MDX和模式文...

1384
来自专栏杨建荣的学习笔记

通过shell定制ash脚本(r3笔记第33天)

ash是在10g以来一个很有用的特性,能够作为awr的补充,对于排查一些历史的问题能够提供更加详细和针对性的数据。 当然个人在使用ash的时候感觉最慢的地方就是...

3294
来自专栏杨建荣的学习笔记

关于dg broker的简单配置(r5笔记第99天)

dataguard broker是在dataguard使用基础上提供的一个工具,可以把原本复杂的命令控制语句集成起来,比如switchover,failover...

4037
来自专栏电光石火

数据库中的左连接(left join)和右连接(right join)区别

Left Join / Right Join /inner join相关

2046

扫码关注云+社区

领取腾讯云代金券