当前位置:  数据库>oracle

Oracle直方图详解

    来源: 互联网  发布时间:2017-06-27

    本文导语: 1 直方图的含义 在Oracle数据库中,CBO会默认认为目标列的数据在其最小值LOW_VALUE和最大值HIGH_VALUE之间均匀分布的,并且会按照这个均匀分布原则来计算对目标列施加查询条件后的可选择率以及结果集的Cardinality,进而据此来计算...

1 直方图的含义

在Oracle数据库中,CBO会默认认为目标列的数据在其最小值LOW_VALUE和最大值HIGH_VALUE之间均匀分布的,并且会按照这个均匀分布原则来计算对目标列施加查询条件后的可选择率以及结果集的Cardinality,进而据此来计算成本值并选择执行计划。但目标列的数据是均匀分布这个原则并不总是正确的,在实际的系统中,我们很容易就能看到一些目标列的数据分布是不均匀的,甚至是极度倾斜、分布极度不均衡的。对这样的列如果还按照均匀分布的原则去计算可选择率与Cardinality,并据此来计算成本、选择执行计划,那么CBO所选择的执行计划就可能是不合理的,甚至是错误的。

看一个由于数据分布极不均衡而导致CBO选错执行计划的例子:

个由于数据分布极不均衡而导致CBO选错执行计划的例子:

zx@ORCL>create table t1 (a number(5),b varchar2(5));
 
Table created.
 
zx@ORCL>declare cnt number(5) := 1;
  2  begin
  3  loop
  4  insert into t1 values(1,'1');
  5  if cnt=10000 then
  6  exit;
  7  end if;
  8  cnt:=cnt+1;
  9  end loop;
 10  insert into t1 values(2,'2');
 11  commit;
 12  end;
 13  /
 
PL/SQL procedure successfully completed.
 
zx@ORCL>select b,count(*) from t1 group by b;
 
B                COUNT(*)
--------------- ----------
1                    10000
2                        1
 
zx@ORCL>create index t1_ix_b on t1(b);
 
Index created.

对表T1不收集直方图统计信息的方式收集一下统计信息:

zx@ORCL>exec dbms_stats.gather_table_stats(USER,'T1',estimate_percent=>100,method_opt=>'for all columns size 1');
 
PL/SQL procedure successfully completed.
 
zx@ORCL>select * from t1 where b='2';
 
        A B
---------- ---------------
        2 2
 
zx@ORCL>select * from table(dbms_xplan.display_cursor(null,null,'all'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5p7b772tpcvm4, child number 0
-------------------------------------
select * from t1 where b='2'
 
Plan hash value: 3617692013
 
--------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |      |      |    7 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1  |  5001 | 25005 |    7  (0)| 00:00:01 |
--------------------------------------------------------------------------
.....省略部分输出

从执行计划可以看出执行计划走的是全表扫描,但是很显然应该走索引T1_IX_B。这是因为CBO默认认为列B的数据是均匀分布的,而列B上的distinct值只有1和2这两值,所以CBO评估出来的对列B施加等值查询条件的可选择率就是1/2,进而评估出来对列B施加等值查询条件的结果集的Cardinality就是5001:

zx@ORCL>select round(10001*(1/2)) from dual;
 
ROUND(10001*(1/2))
------------------
              5001

正因为CBO评估出上述等值查询要返回结果集的Cardinality是5001,已经占了表T1总记录数的一半,所以CBO认为此时再走列B上的索引T1_IX_B就已经不合适了,进而就选择了对列T1的全表扫描。但实际上,CBO对上述等值查询要返回结果集的Cardinality的评估已经与事实严重不符,评估出来的值是5001,其实却只有1,差了好几个数量级。

CBO这里选择了执行计划,正确的执行计划应该是走索引T1_IX_B。CBO选错执行计划的根本原因是表T1的列B的分布实际上是极度不均衡的(列B一共就两值,其中10000个1,只有1个2),CBO在评估的一开始所用的原则就错了,当然结果也就错了。

为了解决上述问题,Oracle引入了直方图(Histogram)。直方图是一种特殊的列统计信息,它详细描述了目标列的数据分布情况。直方图实际上存储在数据字典基表HISTGRM$中,可以通过数据字典DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS和DBA_SUBPART_HISTOGRAMS来分别查看表、分区表的分区和分区表的子分区的直方图统计信息。

如果对目标列收集了直方图,则意味着CBO将不再认为该目标列上的数据是均匀分布的了,CBO就会用该目标列上的直方图统计信息来计算对该列施加查询条件后的可选择率和返回结果集的Cardinality,进而据此计算成本并选择相应的执行计划。

还用上面的例子,对表T1的列B收集了直方图统计信息后,CBO正确地评估出了返回结果集的Cardinality不是5001而是1,进而就正确地选择了走索引T1_IX_B的执行计划:

zx@ORCL>exec dbms_stats.gather_table_stats(USER,'T1',estimate_percent=>100,method_opt=>'for all columns size auto',cascade=>true);
 
PL/SQL procedure successfully completed.
#清空shared_pool,生产系统不要随便执行
zx@ORCL>alter system flush shared_pool;
 
System altered.
 
zx@ORCL>select * from t1 where b='2';
 
        A B
---------- ---------------
        2 2
 
zx@ORCL>select * from table(dbms_xplan.display_cursor(null,null,'all'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5p7b772tpcvm4, child number 0
-------------------------------------
select * from t1 where b='2'
 
Plan hash value: 3579362925
 
---------------------------------------------------------------------------------------
| Id  | Operation                  | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |        |      |      |    2 (100)|          |
|  1 |  TABLE ACCESS BY INDEX ROWID| T1      |    1 |    5 |    2  (0)| 00:00:01 |
|*  2 |  INDEX RANGE SCAN          | T1_IX_B |    1 |      |    1  (0)| 00:00:01 |
---------------------------------------------------------------------------------------
.....省略部分输出

所以,直方图就是专门为了准确评估这种分布不均匀的目标列的可选择率、结果集的Cardinality而被Oracle引入的,它详细描述了目标列的数据分布情况,并将这些分布情况记录在数据字典里,相当于直观地告诉了CBO这些列的数据分布情况,于是CBO就能据此来做出相对准确的判断。

更多详情见请继续阅读下一页的精彩内容:


    
 
 

您可能感兴趣的文章:

  • [Oracle新手教程] 用PL/SQL画直方图
  • Oracle 数据库(oracle Database)性能调优技术详解
  • oracle中lpad函数的用法详解
  • oracle修改scott密码与解锁的方法详解
  • 求.bash_profile配置oracle详解
  • Oracle数据库中分区功能详解
  • oracle指定排序的方法详解
  • 详解如何应用改变跟踪技术加速Oracle递增备份
  • oracle合并列的函数wm_concat的使用详解
  • oracle select执行顺序的详解
  • 使用Oracle数据挖掘API方法详解[图文]
  • Oracle多表级联更新详解
  • 安装Linux与Oracle数据库步骤详解
  • oracle求同比,环比函数(LAG与LEAD)的详解
  • 详解Linux平台下的Oracle数据库编程
  • oracle中去掉回车换行空格的方法详解
  • Oracle中job的使用详解
  • [Oracle] Data Guard 之 Redo传输详解
  • oracle用户权限管理使用详解
  • 深入ORACLE变量的定义与使用的详解
  • 详解Oracle的几种分页查询语句
  • oracle SQL递归的使用详解
  •  
    本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
    本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • Oracle 12c发布简单介绍及官方下载地址
  • 在linux下安装oracle,如何设置让oracle自动启动!也就是让oracle那个服务自动启动,不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同?
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • 虚拟机装Oracle R12与Oracle10g
  • Oracle数据库(Oracle Database)体系结构及基本组成介绍
  • Oracle 数据库开发工具 Oracle SQL Developer
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • Oracle EBS R12 支持 Oracle Database 11g
  • Oracle 10g和Oracle 11g网格技术介绍
  • SCO unix下安装oracle,但没有光盘,请大家推荐一个oracle下载站点(unix版本的)。谢谢!!!!
  • oracle中如何把表中具有相同值列的多行数据合并成一行
  • 请问大家用oracle数据库, 用import oracle.*;下的东西么? 还是用标准库?
  • ORACLE日期相关操作
  • Linux /$ORACLE_HOME $ORACLE_HOME
  • ORACLE数据库常用字段数据类型介绍
  • ORACLE日期相关操作 iis7站长之家
  • Oracle 12c的九大最新技术特性介绍
  • 请问在solaris下安装ORACLE,用root用户和用oracle用户安装有什么区别么?
  • ORACLE中DBMS_RANDOM随机数生成包
  • 网间Oracle的连接,远程连接Oracle服务器??


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3