oracle优化手段--统计信息导出和抽样提取数据

浏览: 1338

11月份参加了Oracle嘉年华,分享心得一则:

我们经常会遇到这样的情况,生产环境一段SQL非常慢,而我们又没有生产的操作权限,这时候怎么办?

很多时候我们会选择在测试环境进行测试,但由于环境的不同(表结构、数据、参数、统计信息等)必然无法每次都能完全的重现问题

这时候我们可以把数据导一份到测试环境,但又会遇到另一个问题:生产环境的数据量太大,由于测试环境表空间限制,或者其他原因,导致10几张千万级甚至上亿的表无法同步到测试环境。

即便我们申请到各种资源,各方都配合,把表同步到测试环境,也有可能出现统计信息不一致,而导致无法重现性能问题的情况。

这时候,怎么破?

刚刚上面一大段话提出几个问题:

  1. 生产环境没有操作权限
  2. 由于环境问题,无法重现问题
  3. 生产环境数据量过大
  4. 统计信息不一致

这里我说下几个问题的解决方案:

  1. 生产环境没有操作权限 ---到测试环境定位问题
  2. 由于环境数据分布等问题,无法重现问题  ---导入生产数据
  3. 生产环境数据量过大  ---使用sample抽样提取一定百分比的数据
  4. 统计信息不一致  ---导出生产统计信息,导入到测试环境

第一和第二个问题都比较简单,也好理解

第三个问题

是我们经常遇到的,几亿的数据量,全部导回是难以得到DBA的配合的,如果选择只导出10%或者1%的数据量,则会好很多

但是我们如何选取这10%的数据呢?

一、 使用rownum

SQL> create table test_objects as select * from dba_objects;
Table created
SQL> select count(*) from test_objects;
  COUNT(*)
----------
     76269
SQL> select count(*) from test_objects where rownum <= 76000;
COUNT(*)
----------
76000

二、 使用sample关键字进行选择:

SQL> select count(*) from test_objects sample(10);
  COUNT(*)
----------
      7756

这里的sample(10)就是抽样取10%的数据,速度非常快,但是sample有几个需要注意的地方:

  1. 只支持单表的查询
  2. 数据分布不均
  3. 加上 BLOCK选项时表示随机取数据块,而不是随机取记录行

Caution: 

The use of statistically incorrect assumptions when using this feature can lead to incorrect or undesirable results.

如何保证其数据的分布特征和生产尽量一致呢?

三、使用随机函数排序获取

select count(*) from (select * from test_objects ORDER BY DBMS_RANDOM.random) where rownum <= 76000;

其中一和三都需要手动算出要取的百分比数据量有多少,然后使用rownum

第四个问题

统计信息导出导入

1. 创建表ST_EMP

BEGIN
DBMS_STATS.CREATE_STAT_TABLE(OWNNAME => 'SCOTT',---收集统计信息的表的owner
STATTAB => 'ST_EMP',---收集统计信息的表名
TBLSPACE => 'USERS');
---收集统计信息的表空间
END;

2. 导出统计信息到ST_EMP

BEGIN
DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME => 'SCOTT',---要分析的表owner
TABNAME => 'EMP',---要分析的表名
STATOWN => 'SCOTT',---收集统计信息的表owner
STATTAB => 'ST_EMP',---收集统计信息的表名
STATID => 'EMP_STATS',---标示ID
CASCADE => TRUE);
END;

3. 用expdp导出统计信息表ST_EMP

4. 用impdp导入到测试环境

5. 将统计信息导入到表

BEGIN
  DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME => 'SCOTT', ---目标表owner
                                TABNAME => 'EMP',---目标表名
                                STATOWN => 'SCOTT',---收集统计信息的表owner
                                STATTAB => 'ST_EMP',---收集统计信息的表
                                CASCADE => TRUE,
                                STATID  => 'EMP_STATS');---标示ID
END;

6. 删除统计信息表 

BEGIN
  DBMS_STATS.DROP_STAT_TABLE(OWNNAME => 'SCOTT', STATTAB => 'ST_EMP');
END;


PS.

可以看看统计信息表里有啥:

Clipboard Image.png



附嘉年华自拍一张 :)

Clipboard Image.png

推荐 8
本文由 老头子 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

5 个评论

老头子的布局很赞
讲解过程比较靠谱,最后的照片亮了
66666
666啊
老头子居然不是老头子

要回复文章请先登录注册