oracle中SQL全表扫描过程分析

本篇内容主要讲解“oracle中SQL全表扫描过程分析”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“oracle中SQL全表扫描过程分析”吧!以下SQL 走了全表扫描，效率下降，而SQL中谓词字段选免费主机域名择性非常低，通过直方图，并从btree转bitmap后性能提供，于是对此过程进行分析。较差的执行计划：通过扫描表方式，逻辑读需要844525：=====================================================该SQL是如何选择的执行计划(通过10053进行追踪)：oracle进行了次以下几种方式的cost 比较：1.评估通过全表扫描需要的cost是229760.92.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: 14.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: 12)IND_DESIGNXXXXX_CATEGORYCODE的消耗是32961. Access Path: index (AllEqRange) Index: IND_DESIGNXXXXX_CATEGORYCODE resc_io: 32934.00 resc_cpu: 1020893102 ix_sel: 0.03688免费主机域名5 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.00053)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: 14)单独 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)
于是自然而然选择了全表扫描：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================================================================以上 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条件后字段都是选择性非常的低。相应字段选择性：同样使用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语句对于开启直方图和btree转Bitma都各自存在某些bug,有时甚至可能引发异常的性能问题，这点是需要重点注意的。到此，相信大家对“oracle中SQL全表扫描过程分析”有了更深的了解，不妨来实际操作一番吧！这里是云技术网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！

相关推荐: MongoDB副本集如何添加和删除仲裁节点

小编给大家分享一下MongoDB副本集如何添加和删除仲裁节点，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ MongoDB仲裁者（Arbiter）是复制集中的一个mongodb实例，…