您好,登录后才能下订单哦!
本篇内容主要讲解“oracle中SQL全表扫描过程分析”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“oracle中SQL全表扫描过程分析”吧!
以下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,有时甚至可能引发异常的性能问题,这点是需要重点注意的。
到此,相信大家对“oracle中SQL全表扫描过程分析”有了更深的了解,不妨来实际操作一番吧!这里是亿速云网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。