ORACLE降序索引BUG

原创张程 2020-04-08

1206

原文作者：Jonathan lewis
原文地址：https://jonathanlewis.wordpress.com/2018/08/22/descending-bug/

在星期一发布了关于阅读执行计划和相关信息的帖子后，我再ODC数据库论坛上注意到一个问题，是关于询问谓词“in ({list of values})” 和通过OR连接的谓词“column = {constant}”之间的区别。通过执行计划中谓词的描述你能看到，其实没有区别：

SELECT  c1, c2, c3, c4, c5, c6, c7, c8..  
FROM    TAB1  
WHERE   STS IN ( 'A', 'B')  
AND     cnt < '4'  
AND     dt < sysdate  
and     rownum <=1;  
   
---------------------------------------------------------------------------------------------------------  
| Id  | Operation                     | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |  
---------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT              |                         |     1 |  1847 | 33399   (1)| 00:03:14 |  
|*  1 |  COUNT STOPKEY                |                         |       |       |            |          |  
|   2 |   INLIST ITERATOR             |                         |       |       |            |          |  
|*  3 |    TABLE ACCESS BY INDEX ROWID| TAB1                    |   114K|   201M| 33399   (1)| 00:03:14 |  
|*  4 |     INDEX RANGE SCAN          | TAB1_STS_IDX            |   114K|       |   723   (1)| 00:00:05 |  
---------------------------------------------------------------------------------------------------------  
   
Predicate Information (identified by operation id):  
---------------------------------------------------  
   1 - filter(ROWNUM<=1)  
   3 - filter("cnt"<'4' AND "dt"<SYSDATE@!)  
   4 - access("STS"='A' OR "STS"='B')

请注意谓词部分告诉我们，原始的“sts in ( ‘A’, ‘B’ )” 谓词已经被转换为“sts = ‘A’ or sts = ‘B’”
在周一发布的帖子中我指出，IN-List扩展的第一步是首先对列表中的项目进行排序并去重。我突然想到，如果我将相关的唯一列定义为降序索引，那么Oracle是否会对列表进行降序排序。当然，我需要测试一下这个想法，如下是一个对应创建数据的脚本：

rem
rem     Script:         descending_bug_06.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Aug 2018
rem     Purpose:
rem
rem     Last tested
rem             18.3.0.0        Crashes
rem             12.2.0.1        Crashes
rem             12.1.0.2        Crashes
rem             11.2.0.4        Bad Plan
rem
 
create table t1
nologging
pctfree 95 pctused 5
as
with generator as (
        select 
                rownum id
        from dual 
        connect by 
                level <= 1e4 -- > comment to avoid WordPress format issue
)
select
        rownum                          id,
        lpad(rownum,10,'0')             v1,
        case mod(rownum,1000)
                when 0 then 'A'
                when 3 then 'B'
                when 6 then 'C'
                       else 'D'
        end                             sts,
        case mod(rownum,1000)
                when 0 then '1'
                when 3 then '2'
                when 6 then '3'
                       else '4'
        end                             cnt,
        lpad('x',100,'x')               padding
from
        generator       v1,
        generator       v2
where
        rownum <= 1e5 -- > comment to avoid WordPress format issue
;
 
create index t1_i1a on t1(sts) nologging;
create index t1_i1d on t1(sts desc) nologging;
 
begin
        dbms_stats.gather_table_stats(
                ownname     => null,
                tabname     => 'T1',
                method_opt  => 'for all columns size skewonly'
        );
end;
/

脚本中有一个奇怪的地方，如果你以降序的方式定义索引中的每一列，你就犯了一个错误没有列应该被定义成降序。只有当你希望在单个索引中同时使用升序和降序扫描时，这个脚本才是合适的。为了使每一列降序，唯一要做的就是是索引中的每一行增大一个字节。也同时引入了遇到一定限制和错误的机会。
脚本中的一个重要细节是创建对象之后，我进行了收集统计信息。即使在19.3中，对我的演示而言，这样做也很重要。因为（a）创建降序索引将导致自动创建一个隐藏的虚拟列以表示每个降序列，并且我想确保在虚拟列上有统计信息和（b）sts列默认不会生成直方图，我希望在sts列和该列的虚拟隐藏列中生成（频率）直方图。
创建了数据并确保生成了sts列和sys_nc00006$（对应的隐藏列）直方图之后，可以运行下面的测试脚本：

set serveroutput off
alter session set statistics_level = all;
 
alter index t1_i1d invisible;
 
select  sts, count(*)
from    t1
where   sts in ('B','C')
group by
        sts
;
 
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
alter index t1_i1d   visible;
alter index t1_i1a invisible;
 
select  sts, count(*)
from    t1
where   sts in ('B','C')
group by
        sts
;
 
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
alter session set statistics_level = typical;
set serveroutput on

代码中对一个索引不可见，然后通过IN-LIST迭代执行查询。然后切换索引因此当前使用的索引，可见另一个索引，并重复运行查询。我启用了行源执行统计信息，并从内存中获取了真实执行计划，以保证我不会被预估的执行计划存在的任何异常所误导。下面是在11.2.0.4运行的结果，首先是正常索引，然后是降序索引，其中做了一点小小的美化：

S   COUNT(*)
- ----------
B        100
C        100
 
SQL_ID  f20u42pmw1z6w, child number 0
-------------------------------------
select sts, count(*) from t1 where sts in ('B','C') group by  sts
 
-----------------------------------------------------------------------------------------
| Id  | Operation            | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |      1 |        |      2 |00:00:00.01 |       5 |
|   1 |  SORT GROUP BY NOSORT|        |      1 |      2 |      2 |00:00:00.01 |       5 |
|   2 |   INLIST ITERATOR    |        |      1 |        |    200 |00:00:00.01 |       5 |
|*  3 |    INDEX RANGE SCAN  | T1_I1A |      2 |    178 |    200 |00:00:00.01 |       5 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access(("STS"='B' OR "STS"='C'))
 
 
Index altered.
Index altered.
 
 
S   COUNT(*)
- ----------
C        100
B        100
 
 
SQL_ID  f20u42pmw1z6w, child number 0
-------------------------------------
select sts, count(*) from t1 where sts in ('B','C') group by  sts
 
-----------------------------------------------------------------------------------------
| Id  | Operation            | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |      1 |        |      2 |00:00:00.02 |     198 |
|   1 |  SORT GROUP BY NOSORT|        |      1 |      2 |      2 |00:00:00.02 |     198 |
|*  2 |   INDEX FULL SCAN    | T1_I1D |      1 |   1000 |    200 |00:00:00.02 |     198 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter((SYS_OP_UNDESCEND("T1"."SYS_NC00006$")='B' OR
              SYS_OP_UNDESCEND("T1"."SYS_NC00006$")='C'))

正如预期的那样，“ B”和“ C”的过滤行数为100，并且看到使用降序索引以及“sort group by nosort” 操作的查询按照降序产生了结果。问题是优化器使用了降序索引上的“index full scan” 访问，并且估算行数与实际是差异很大的（似乎是对每个值使用了5%的可选择率进行估算）。与正常的索引相比，消耗的逻辑读是巨大的-ORACLE其实是遍历了索引的每个叶子块以返回实际的结果。谓词部分看起来也很糟糕，为什么优化器没有生成更像谓词的过滤“ sys_nc00006 $ = sys_op_descend（‘B’）”？
顺便说一句，可以看到使用普通索引的执行计划中的预估行数有些低。这是Oracle使用11g中的一个小样本（约5500行）收集直方图统计信息的结果。对于频率直方图，使用12c （100%自动采样大小）的（“ approximate_ndv”）快速算法会更好。
所以11g的表现不是很好，但是我们有12.1.0.2、12.2.0.1和（在最近几周内）18.3可以使用。这是对应使用降序索引的查询在12.1.0.2和12.2.0.1结果：

select  sts, count(*)
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qernsRowP], [1], [], [], [], [], [], [], [], [], [], []
 
 
SQL_ID  f20u42pmw1z6w, child number 0
-------------------------------------
select sts, count(*) from t1 where sts in ('B','C') group by  sts
 
-----------------------------------------------------------------------------------------
| Id  | Operation            | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |      1 |        |      0 |00:00:00.01 |       0 |
|   1 |  SORT GROUP BY NOSORT|        |      1 |      2 |      0 |00:00:00.01 |       0 |
|   2 |   INLIST ITERATOR    |        |      1 |        |    101 |00:00:00.03 |       5 |
|*  3 |    INDEX RANGE SCAN  | T1_I1D |      2 |    200 |    101 |00:00:00.01 |       5 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access(("T1"."SYS_NC00006$"=SYS_OP_DESCEND('B') OR
              "T1"."SYS_NC00006$"=SYS_OP_DESCEND('C')))
       filter((SYS_OP_UNDESCEND("T1"."SYS_NC00006$")='B' OR
              SYS_OP_UNDESCEND("T1"."SYS_NC00006$")='C'))

查询崩溃了！但是，该执行计划看起来确是比较合理的–优化器选择了In-List迭代的方式，使用了索引范围扫描，正确估算了访问的行（索引条目），并且谓词过滤部分看起来更合理（它使用了明智的谓词进行访问，尽管filter部分看起来还是很奇怪）。从“ A-rows” 列判断，查询似乎已经崩溃，大约是在优化器从第一次迭代的范围扫描切换到第二次迭代的范围扫描的时候。

然后是Oracle 18.3 –与12c表现相同：
为了确保我顾虑的“单列条件，因此不应将其声明为降序”索引是问题的唯一原因，我在（sts，cnt）上使用了复合索引来重复测试。

结论：
即使在最新版本的Oracle中，降序索引或（更准确地说）带有降序列的索引仍然会产生问题。

脚注：
Oracle MoS有一个很棒的“ ORA-600 / ORA-7445 / ORA-700错误查找工具（文档ID 153788.1）”（目前还不允许选择18.3作为版本），所以我在12.2.0.1版本中使用它的第一个入参来查找ORA-00600错误，从文档ID 285913.1得到以下建议：“将event：10119设置为禁用no-sort fetch，然后重新解析失败的SQL。” 该示例建议将事件设置为12级，这解决了所有三个失败版本的问题-但该建议带有警告：“在系统级别设置此事件可能会影响数据库的性能。” 执行计划（在本例中为18.2）可以解释警告的原因：

S   COUNT(*)
- ----------
B    100
C    100
 
SQL_ID  f20u42pmw1z6w, child number 0
-------------------------------------
select sts, count(*) from t1 where sts in ('B','C') group by  sts
 
------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      2 |00:00:00.01 |       4 |       |       |      |
|   1 |  HASH GROUP BY     |        |      1 |      2 |      2 |00:00:00.01 |       4 |  1558K|  1558K|  659K (0)|
|   2 |   INLIST ITERATOR  |        |      1 |        |    200 |00:00:00.01 |       4 |       |       |      |
|*  3 |    INDEX RANGE SCAN| T1_I1D |      2 |    200 |    200 |00:00:00.01 |       4 |       |       |      |
------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access(("T1"."SYS_NC00006$"=SYS_OP_DESCEND('B') OR "T1"."SYS_NC00006$"=SYS_OP_DESCEND('C')))
       filter((SYS_OP_UNDESCEND("T1"."SYS_NC00006$")='B' OR SYS_OP_UNDESCEND("T1"."SYS_NC00006$")='C'))

该计划已从使用“sort group by nosort”（实际上意味着仅在运行时保持运行计数）改为使用真正的“hash group by”，这意味着必须对每个值都进行哈希算法（尽管有一个确定性的技巧，如果要哈希的下一个值与上一个值相同，则Oracle将不会执行HASH运算），并且所用的实际内存（659K）只为了计算两个不同的值的数量似乎有些过于浪费。

持续更新：该BUG在19.3中仍然存在。

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

ORACLE降序索引BUG

评论