专栏首页乐沙弥的世界dbms_stats 导入导出 schema 级别统计信息

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 条评论
登录 后参与评论

相关文章

  • PL/SQL 下SQL结果集以html形式发送邮件

          在运维的过程中,有时候需要定时将SQL查询的数据结果集以html表格形式发送邮件,因此需要将SQL查询得到的结果集拼接成html代码。对于这种情形通...

    Leshami
  • PXC 5.7 mysqldump: Error 2013

    最近在mysqldump时,遭遇mysqldump: Error 2013错误。以为是常见的参数设置有问题,调整之后,也没有任何成效。原来发生了OOM,以下是其...

    Leshami
  • 执行计划中各字段各模块描述

          在SQL语句的执行计划中,包含很多字段项和很多模块,其不同字段代表了不同的含义且在不同的情形下某些字段、模块显示或不显示,下 面的描述给出了执行计划...

    Leshami
  • 《Java从入门到失业》第一章:计算机基础知识(1.2):计算机组成及基本原理

      这里说的计算机主要指微型计算机,俗称电脑。一般我们见到的有台式机、笔记本等,另外智能手机、平板也算。有了一台计算机,我们就能做很多事情了,比如我在写这篇博客...

    用户7801119
  • Flutter 环境搭建

    要获得 Flutter,请先使用 git 克隆 Flutter,然后将该 flutter 工具添加到您的用户路径。运行 flutter doctor 显示您可能...

    s_在路上
  • 自然语言处理 | 隐马尔可夫模型(2)

    Defu Li
  • 神经网络图灵机

    摘要 本文通过引入一个使用注意力程序进行交互的外部存储器(external memory)来增强神经网络的能力。新系统可以与图灵机或者冯·诺依曼体系相类比,但每...

    昱良
  • 谷歌推出 Kotlin 免费课程,让你轻松入门 Android 开发!

    最近,微软推出了一套免费的 Python 在线视频课程,为 Python 初学者提供学习材料。无独有偶,谷歌近日也宣布推出两个 Kotlin 免费在线课程,正如...

    GitHubDaily
  • 1044. 火星数字(20)

    地球人的0被火星人称为tret。 地球人数字1到12的火星文分别为:jan, feb, mar, apr, may, jun, jly, aug, sep,...

    AI那点小事
  • 水果

    题意:就是最后给出哪个地方哪种水果买了多少钱,首先是地方联系水果,水果又联系数量~!这摆明了是用map啊,并且还应该是镶嵌的map. 就是迭代器写的时候难写了...

    用户7727433

扫码关注云+社区

领取腾讯云代金券