ITPub博客

首页 > 数据库 > Oracle > oracle 一个or语句因字段数据分布不均并缺少直方图引起的全表扫描优化分析

oracle 一个or语句因字段数据分布不均并缺少直方图引起的全表扫描优化分析

原创 Oracle 作者:darren__chan 时间:2018-12-08 21:01:42 0 删除 编辑

以下SQL 走了全表扫描,效率下降,而SQL中谓词字段选择性非常低,通过直方图,并从btree转bitmap后性能提供,于是对此过程进行分析。

Select Count(*) From pmc.DesignXXXXX t Where 1=1  and OrganId='C00000220'And CategoryCode=2 and IsEnable=1 and isdelete=0 or (PublicStatus=1  and isdelete=0 );
  COUNT(*)
----------
      1845


较差的执行计划:通过扫描表方式,逻辑读需要844525:

=====================================================

Execution Plan
----------------------------------------------------------
Plan hash value: 527126818
-----------------------------------------------------------------------------------
| Id  | Operation	   | Name	 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |		 |	1|    19 |   229K  (1)| 00:45:58 |
|   1 |  SORT AGGREGATE    |		 |	1|    19  |	      |	       	|
|*  2 |   TABLE ACCESS FULL| DESIGNXXXXX |  4744K|    85M|   229K  (1)| 00:45:58 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ISDELETE"=0 AND ("PUBLICSTATUS"=1 OR "ORGANID"='C00000220'
	      AND "CATEGORYCODE"=2 AND "ISENABLE"=1))
Statistics
----------------------------------------------------------
  	  1  recursive calls
  	  0  db block gets
 844525  consistent gets
 842418  physical reads
  	  0  redo size
  	527  bytes sent via SQL*Net to client
  	520  bytes received via SQL*Net from client
  	  2  SQL*Net roundtrips to/from client
  	  0  sorts (memory)
  	  0  sorts (disk)
	  1  rows processed

该SQL是如何选择的执行计划(通过10053进行追踪):


oracle进行了次以下几种方式的cost 比较:

1.评估通过全表扫描需要的cost是229760.92. 

 Access Path: TableScan
    Cost:  229760.92  Resp: 229760.92  Degree: 0
      Cost_io: 229075.00  Cost_cpu: 25302994949
      Resp_io: 229075.00  Resp_cpu: 25302994949


2.评估通过位图索引的方式cost是741028,这里是已经同时用bitmap方式将or两边进行联结的消耗。

   ****** trying bitmap/domain indexes ******   

....       

  Bitmap nodes:

    Used IND_DESIGNXXXXX_ISENABLE_ORG

      Cost = 35.099036, sel = 0.000494

    Used IND_DESIGNXXXXX_CATEGORYCODE

      Cost = 1281.621955, sel = 0.034894

  Bitmap nodes:

    Used IND_PUBLICSTATUS

      Cost = 17275.447942, sel = 0.471383

    Used  bitmap node 

  Bitmap nodes:

    Used  bitmap node 

  Access path: Bitmap index - accepted

    Cost: 741028.481879 Cost_io: 740534.527080 Cost_cpu: 18221443693.247154 Sel: 0.471392    


因为该语句中存在or ,即分别计算or左右的访问路径消耗,再来进行组合。

3.or右边通过IND_PUBLICSTATUS索引范围扫描 cost是429957

  Access Path: index (AllEqRange)

    Index: IND_PUBLICSTATUS

    resc_io: 429587.00  resc_cpu: 13681713060

    ix_sel: 0.477347  ix_sel_with_filters: 0.477347 

    Cost: 429957.89  Resp: 429957.89  Degree: 1



4.or左边分别计算使用以下索引的的消耗

1)DESIGNXXXXX_TIME_ORGANID的消耗是88778。

  Access Path: index (SkipScan)

    Index: DESIGNXXXXX_TIME_ORGANID

    resc_io: 88761.00  resc_cpu: 643271006

    ix_sel: 0.000509  ix_sel_with_filters: 0.000509 

    Cost: 88778.44  Resp: 88778.44  Degree: 1

    

2)IND_DESIGNXXXXX_CATEGORYCODE的消耗是32961.    

  Access Path: index (AllEqRange)

    Index: IND_DESIGNXXXXX_CATEGORYCODE

    resc_io: 32934.00  resc_cpu: 1020893102

    ix_sel: 0.036885  ix_sel_with_filters: 0.036885 

    Cost: 32961.67  Resp: 32961.67  Degree: 1

  ColGroup Usage:: PredCnt: 2  Matches Full: #2  Partial:  Sel: 0.0005

  ColGroup Usage:: PredCnt: 2  Matches Full: #2  Partial:  Sel: 0.0005

  

3)IND_DESIGNXXXXX_CATEGORYCODE的消耗是32961.      

  Access Path: index (AllEqRange)

    Index: IND_DESIGNXXXXX_ISENABLE_ORG

    resc_io: 6499.00  resc_cpu: 57845156

    ix_sel: 0.000494  ix_sel_with_filters: 0.000494 

    Cost: 6500.57  Resp: 6500.57  Degree: 1

    

4)单独 IND_DESIGNXXXXX_ISENABLE_ORG和IND_DESIGNXXXXX_CATEGORYCODE转bitmap 的消耗是1406。

  Bitmap nodes:

    Used IND_DESIGNXXXXX_ISENABLE_ORG

      Cost = 35.099036, sel = 0.000494

    Used IND_DESIGNXXXXX_CATEGORYCODE

      Cost = 1281.621955, sel = 0.034894

  Access path: Bitmap index - accepted

    Cost: 1406.374238 Cost_io: 1399.626467 Cost_cpu: 248917754.369408 Sel: 0.000017   


这里需要注意的是将or左右两边分别拿出来计算,最终合并需要统计计算两边的消耗,因此以上的所有消耗评估是:


全表扫描(Cost:  229760.92)< IND_PUBLICSTATUS索引(Cost: 429957.89)+任意左边任意一种访问路径方式 <两边直接转位图联结的方式(Cost: 741028)

 


于是自然而然选择了全表扫描:

Final cost for query block SEL$1 (#0) - All Rows Plan:

  Best join order: 1

  Cost: 229760.9246  Degree: 1  Card: 1845.0000  Bytes: 35055

  Resc: 229760.9246  Resc_io: 229075.0000  Resc_cpu: 25302994949

  Resp: 229760.9246  Resp_io: 229075.0000  Resc_cpu: 25302994949

  

我们要知道以上都只是oracle CBO评估的结果,而在日常应用中CBO如果获取的表信息不够准确便为导致评估结果不一定是正确,而我们有时无法控制的是SQL每次硬解析时获取信息是否足够准确,这也是因此偶尔会出现执行计划突变的状况。

以上SQL 通过收集直方图后便可暂时得到解决。

这是收集直方图后,较优的执行计划:分别通过btree索引转成BITMAP索引方式,逻辑读需要 2196

================================================================

Execution Plan
----------------------------------------------------------
Plan hash value: 4067119963
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation			   | Name			   | Rows  | Bytes | Cost (%CPU)| Time	   |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		   |				   |	 1 |	19 |   647   (1)| 00:00:08 |
|   1 |  SORT AGGREGATE 		   |				   |	 1 |	19 |		|	   |
|*  2 |   TABLE ACCESS BY INDEX ROWID	   | DESIGNXXXXX		   |  1901 | 36119 |   647   (1)| 00:00:08 |
|   3 |    BITMAP CONVERSION TO ROWIDS	   |				   |	   |	   |		|	   |
|   4 |     BITMAP OR			   |				   |	   |	   |		|	   |
|   5 |      BITMAP CONVERSION FROM ROWIDS |				   |	   |	   |		|	   |
|*  6 |       INDEX RANGE SCAN		   | IND_PUBLICSTATUS		   |	   |	   |	 6   (0)| 00:00:01 |
|   7 |      BITMAP AND 		   |				   |	   |	   |		|	   |
|   8 |       BITMAP CONVERSION FROM ROWIDS|				   |	   |	   |		|	   |
|*  9 |        INDEX RANGE SCAN 	   | IND_DESIGNXXXXx_ISENABLE_ORG  |	   |	   |	 3   (0)| 00:00:01 |
|  10 |       BITMAP CONVERSION FROM ROWIDS|				   |	   |	   |		|	   |
|* 11 |        INDEX RANGE SCAN 	   | IND_DESIGNXXXXXX_CATEGORYCODE |	   |	   |   102   (0)| 00:00:02 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ISDELETE"=0)
   6 - access("PUBLICSTATUS"=1)
   9 - access("ISENABLE"=1 AND "ORGANID"='C00000220')
  11 - access("CATEGORYCODE"=2)
Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
       2196  consistent gets
	  0  physical reads
	  0  redo size
	527  bytes sent via SQL*Net to client
	520  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed


以上 BITMAP CONVERSION的顺序过程:

 

 步骤1.sql通过IND_PUBLICSTATUS索引到表中获取符合条件的行,然后从获取的行中的rowid转换成bitmap,这一步是BITMAP CONVERSION FROM ROWIDS。

 步骤2.sql通过IND_DESIGNXXXXX_CATEGORYCODE索引到表中获取符合条件的行,然后同样从获取的行中的rowid转换成bitmap,这一步是BITMAP CONVERSION FROM ROWIDS。

 步骤3.sql通过IND_DESIGNXXXXX_ISENABLE_ORG索引到表中获取符合条件的行,然后同样从获取的行中的rowid转换成bitmap,这一步是BITMAP CONVERSION FROM ROWIDS。

 步骤4.sql 将步骤2和步骤3所得bitmap数据通过BITMAP AND 方式取交集。

 步骤5.sql 将步骤1所得bitmaps数据与步骤4通过BITMAP OR方式取并集。

 步骤6.sql 将步骤5最终获取的并集bitmap数据转换成ROWIDS,这一步是BITMAP CONVERSION TO ROWIDS。

 步骤7.sql 将步骤6获取的rowid通过回表方式到表中获取所需要的字段数据,这一步是ABLE ACCESS BY INDEX ROWID


为什么会这样:

当对表中的唯一度不高的列建立了index,oracle就有可能选择转为bitmap来执行。查看sql中where条件后字段都是选择性非常的低。


相应字段选择性:

COLUMN_NAME                      NUM_ROWS CARDINALITY SELECTIVITY
------------------------------ ---------- ----------- -----------
ORGANID                          21095783        2070         .01
CATEGORYCODE                     21095783          29           0
ISENABLE                         21095783           2           0
ISDELETE                         21095783           2           0
PUBLISHSTATE                     21095783           1           0
对应索引:
INDEX_NAME                         INDEX_COL              INDEX_TYPE            
--------------------------------   ---------------------- ----------------------
PMC.IND_DESIGNXXXXX_CATEGORYCODE  CATEGORYCODE           NORMAL-NONUNIQUE      
PMC.IND_DESIGNXXXXX_ISENABLE_ORG  ISENABLE,ORGANID       NORMAL-NONUNIQUE      
PMC.IND_PUBLICSTATUS               PUBLICSTATUS           NORMAL-NONUNIQUE


同样使用10053追踪增加直方图后SQL执行,此时CBO为什么可以选择到转位图的执行计划,发现增加直方图之后评估消耗只需要647,而在此之前所需消耗要高达741028。


增加直方图后的评估:

  Access path: Bitmap index - accepted

    Cost: 647.047103 Cost_io: 646.348285 Cost_cpu: 25778603.541021 Sel: 0.000103


 对比未增加直方图之前的评估:

  Access path: Bitmap index - accepted

    Cost: 741028.481879 Cost_io: 740534.527080 Cost_cpu: 18221443693.247154 Sel: 0.471392  



为什么收集直方图后评估的消耗可以这么低?

在oracle CBO 计算cost主要是IO成本+CPU成本,在计算成本之前,CBO会收集以下统计信息:

列中不同值的数量也就是NDV

列中的最小值/最大值

列中null值的数量

数据分布

直方图信息(前提是收集直方图)


对比收集直方图前后的字段信息:


收集直方图之前的字段信息:

  Column (#4): ORGANID(

    AvgLen: 10 NDV: 2023 Nulls: 4717 Density: 0.000494

  Column (#29): CATEGORYCODE(

    AvgLen: 2 NDV: 27 Nulls: 1164044 Density: 0.037037 Min: 0 Max: 66

  Column (#38): ISENABLE(

    AvgLen: 2 NDV: 2 Nulls: 1151627 Density: 0.500000 Min: 0 Max: 1

  Column (#14): ISDELETE(

    AvgLen: 3 NDV: 2 Nulls: 0 Density: 0.500000 Min: 0 Max: 1

  Column (#32): PUBLICSTATUS(

    AvgLen: 2 NDV: 2 Nulls: 1151554 Density: 0.500000 Min: 0 Max: 1


收集直方图之后的字段信息:

  Single Table Cardinality Estimation for DESIGNXXXXX[T] 

  Column (#14): 

    NewDensity:0.041803, OldDensity:0.000000 BktCnt:6033548, PopBktCnt:6033548, PopValCnt:2, NDV:2

  Column (#14): ISDELETE(

    AvgLen: 3 NDV: 2 Nulls: 0 Density: 0.041803 Min: 0 Max: 1

    Histogram: Freq  #Bkts: 2  UncompBkts: 6033548  EndPtVals: 2

  Column (#4): 

    NewDensity:0.000185, OldDensity:0.001779 BktCnt:254, PopBktCnt:160, PopValCnt:25, NDV:2027

  Column (#4): ORGANID(

    AvgLen: 10 NDV: 2027 Nulls: 4830 Density: 0.000185

    Histogram: HtBal  #Bkts: 254  UncompBkts: 254  EndPtVals: 120

  Column (#29): 

    NewDensity:0.000000, OldDensity:0.000000 BktCnt:5680066, PopBktCnt:5680055, PopValCnt:16, NDV:27

  Column (#29): CATEGORYCODE(

    AvgLen: 2 NDV: 27 Nulls: 1162620 Density: 0.000000 Min: 0 Max: 66

    Histogram: Freq  #Bkts: 27  UncompBkts: 5680066  EndPtVals: 27

  Column (#38): 

    NewDensity:0.000943, OldDensity:0.000000 BktCnt:5687407, PopBktCnt:5687407, PopValCnt:2, NDV:2

  Column (#38): ISENABLE(

    AvgLen: 2 NDV: 2 Nulls: 1150490 Density: 0.000943 Min: 0 Max: 1

    Histogram: Freq  #Bkts: 2  UncompBkts: 5687407  EndPtVals: 2

  ColGroup (#2, Index) IND_DESIGNXXXXX_ISENABLE_ORG

    Col#: 4 38    CorStregth: 2.00

  ColGroup (#3, Index) IND_DESIGNXXXXX_AUTHOR_TIME

    Col#: 6 7    CorStregth: -1.00

  ColGroup (#1, Index) DESIGNXXXXX_TIME_ORGANID

    Col#: 4 7    CorStregth: -1.00

  ColGroup Usage:: PredCnt: 3  Matches Full:  Partial: 

  Column (#32): 

    NewDensity:0.000055, OldDensity:0.000000 BktCnt:5688611, PopBktCnt:5688611, PopValCnt:2, NDV:2

  Column (#32): PUBLICSTATUS(

    AvgLen: 2 NDV: 2 Nulls: 1150387 Density: 0.000055 Min: 0 Max: 1

    Histogram: Freq  #Bkts: 2  UncompBkts: 5688611  EndPtVals: 2


在没有收集直方图之前,发现有部分字段的Density都是0.5,这个值是从1/NDV(基数)得到的,这是因为CBO有时无法正确的统计到表的数据分布,但当收集直方图后该值就改变了,因为在一个表中,不一定所有的数据都能分配平均,直方图的作用就是能找出这种不平均,

那PUBLICSTATUS字段来说,我们看到NDV是2,即是说全表之后两个值,这两个值是0或1,在没有收集直方图之前CBO可能会认为0和1的分布是各一半,此时他去评估访问该字段的路径可能是全表扫描比较好,

而实际上,表中PUBLICSTATUS=1 的数据量非常少。

sys@LVDB SQL>Select Count(*) From pmc.DesignXXXXX t where PublicStatus=1  and isdelete=0 ;


  COUNT(*)

----------

      1845


但直到PUBLICSTATUS的数据分布后,CBO评估通过IND_PUBLICSTATUS索引访问cost只需要6。这也是为什么收集直方图后能更加准确的评估访问表的消耗了。

 Access Path: index (AllEqRange)

    Index: IND_PUBLICSTATUS

    resc_io: 6.00  resc_cpu: 457729

    ix_sel: 0.000112  ix_sel_with_filters: 0.000112 

    Cost: 6.01  Resp: 6.01  Degree: 0


然后该种0或1的情况选择了转换成bitmap索引的模式。


其实如果不选择btree 转换bitmap方式,直接使用btree索引回表效率也是没问题的,只是需要将sql中的or拆成union语句

Execution Plan
----------------------------------------------------------
Plan hash value: 3766559296
------------------------------------------------------------------------------------------------------------------
| Id  | Operation			 | Name 			 | Rows  | Bytes | Cost (%CPU)| Time	 |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		 |				 |     1 |    13 |   105   (2)| 00:00:02 |
|   1 |  SORT AGGREGATE 		 |				 |     1 |    13 |	      | 	 |
|   2 |   VIEW				 |				 |     2 |    26 |   105   (2)| 00:00:02 |
|   3 |    SORT UNIQUE			 |				 |     2 |    22 |   105   (2)| 00:00:02 |
|   4 |     UNION-ALL			 |				 |	 |	 |	      | 	 |
|   5 |      SORT AGGREGATE		 |				 |     1 |    17 |     9  (12)| 00:00:01 |
|*  6 |       TABLE ACCESS BY INDEX ROWID| DESIGXXXXXXX 		 |     1 |    17 |     8   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN 	 | IND_DESIGNXXXXXX_ISENABLE_ORG |     6 |	 |     3   (0)| 00:00:01 |
|   8 |      SORT AGGREGATE		 |				 |     1 |     5 |    96   (2)| 00:00:02 |
|*  9 |       TABLE ACCESS BY INDEX ROWID| DESIGNXXXXXXX 		 |  1874 |  9370 |    95   (0)| 00:00:02 |
|* 10 |        INDEX RANGE SCAN 	 | IND_PUBLICSTATUS		 |  2046 |	 |     6   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter("CATEGORYCODE"=2 AND "ISDELETE"=0)
   7 - access("ISENABLE"=1 AND "ORGANID"='C00000281')
   9 - filter("ISDELETE"=0)
  10 - access("PUBLICSTATUS"=1)
Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
       2114  consistent gets
	  0  physical reads
	  0  redo size
	527  bytes sent via SQL*Net to client
	520  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  1  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

对于开启直方图和btree转Bitma都各自存在某些bug,有时甚至可能引发异常的性能问题,这点是需要重点注意的。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29863023/viewspace-2284608/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
一思尚存,此志不懈

注册时间:2014-09-09

  • 博文量
    174
  • 访问量
    1124623