12C CBQT OR扩展BUG带来的问题

IT那活儿发布于2023-01-11 13:19 / 2168人阅读

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

案例一：12.2的OR查询走不了索引

一条简单的SQL，在11G下运行的很正常，走索引，到18C下，却走了全表扫描，而且收集统计信息也无法走索引。

遇到这样的情况，我的经验就是怀疑这可能是一个BUG，具体解决可以从10053trace中分析或者搜索MOS文档或者用神器SQLT的xplore功能。

废话少说，先看SQL语句：

SELECT *
FROM (SELECT A.*,
ROW_NUMBER() OVER(PARTITION BY POLICYNO, CLASSCODE ORDER BY STOPDATE DESC) NN
FROM TESTID_RISKCON A
WHERE (POLICYNO = :B1 OR GPOLICYNO = :B1)
AND POLIST NOT IN (1)) T1
WHERE T1.NN = 1

Plan hash value: 1623810908
------------------------------------------------------------------------------------------------------------------

| Id  | Operation | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows | A-Time   | Buffers |

------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT         | | 1 | | | 3672K(100)| | | | 0 |00:00:00.01 | 0 |

|* 1 | VIEW                    | | 1 | 256 | 99840 | 3672K (1)| 00:02:24 | | | 0 |00:00:00.01 | 0 |

|* 2 | WINDOW SORT PUSHED RANK| | 1 | 256 | 49664 | 3672K (1)| 00:02:24 | | | 0 |00:00:00.01 | 0 |

| 3 | PARTITION RANGE ALL | | 1 | 256 | 49664 | 3672K (1)| 00:02:24 | 1 | 43 | 0 |00:00:00.01 | 0 |

|* 4 | TABLE ACCESS FULL    | TESTID_RISKCON | 2 | 256 | 49664 | 3672K (1)| 00:02:24 | 1 | 43 | 0 |00:00:00.01 | 1 |

------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("T1"."NN"=1)

   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "POLICYNO","CLASSCODE" ORDER BY INTERNAL_FUNCTION("STOPDATE") DESC )<=1)

   4 - filter((("GPOLICYNO"=:B1 OR "POLICYNO"=:B1) AND "POLIST"<>1))

TESTID_RISKCON表的列GPOLICYNO和PLICYNO都有索引，在12.2里走全表扫描，在11g里，对于这个OR条件，是可以走索引联合查询的，如下所示：

--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)|
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |      1 | |       | 12 (100)|
|* 1 |  VIEW | |      1 | 52 | 20280 | 12 (9)|
|* 2 |   WINDOW SORT PUSHED RANK | |      1 | 52 |  9828 | 12 (9)|
| 3 |    CONCATENATION | |      1 | |       | |
|* 4 |     TABLE ACCESS BY GLOBAL INDEX ROWID| TESTID_RISKCON |      1 | 2 |   378 | 3 (0)||* 5 |      INDEX RANGE SCAN | IDX_TESTID_RISKCON_01 |      1 | 2 |       | 2 (0)||* 6 |     TABLE ACCESS BY GLOBAL INDEX ROWID| TESTID_RISKCON |      1 | 50 |  9450 | 8 (0)||* 7 |      INDEX RANGE SCAN | IDX_TESTID_RISKCON_02 |      1 | 50 |       | 2 (0)|
--------------------------------------------------------------------------------------------------------------
                                                                                                             
Query Block Name / Object Alias (identified by operation id): 
------------------------------------------------------------- 
   1 - SEL$2 / T1@SEL$1 
   2 - SEL$2 
   4 - SEL$2_1 / A@SEL$2 
   5 - SEL$2_1 / A@SEL$2 
   6 - SEL$2_2 / A@SEL$2_2 
   7 - SEL$2_2 / A@SEL$2_2

对于这个问题，首先是收集统计信息，然而并没有啥用，因此，想到是BUG的原因，很简单，去MOS上搜索下，查到如下信息：

BadExecution Plan With OR Query After Update To 12.2.0.1 (Doc ID2536570.1)

按照给出的解决方案如下：

Apply Patch 29450812 if available for your version
OR
Workarounds:
Set _optimizer_cbqt_or_expansion=false
Or 
Use USE_CONCAT hint in query
Or
Set optimizer_features_enable=12.1.0.2

主要原因是12.2开始对or扩展使用COSTBASEDTRANSFORMATION，导致BUG，最终通过在语句级关闭_optimizer_cbqt_or_expansion参数搞定。

案例二：12.2后到19c merge语句的OR操作走不了索引

ORACLE12.2开始对OR扩展使用CBQT，目的是用基于COST的方法，让CBO更加准确判断可能的访问路径，但是在12.2中却存在BUG，导致不能走索引，从而影响性能，参考案例1，在12.2中也有个补丁可以解决：

CBQT ORE DOES NOT APPLY TO CORRELATED SCALAR SUBQUERY WITH OE

在18c中，可以通过fixcontrol参数控制这个补丁：

select value,sql_feature,description,optimizer_feature_enable from V$SYSTEM_FIX_CONTROL where BUGNO=26019148;

     VALUE SQL_FEATURE DESCRIPTION OPTIMIZER_FEATURE_ENABLE
---------- ------------------------------ ------------------------------ -------------------------
         1 QKSFM_OR_EXPAND_26019148 Allow ORE in select list subq 18.1.0

按理说在18C已经解决了这个问题，但是在19C中测试，对于merge语句，还是无效（12.2后还是不行），如下所示：

drop table t1; 
drop table t2; 
create table t1 (id number, name varchar2(4000)) ; 
create table t2 (id number, name varchar2(4000), ext varchar2(100)) ; 
create index t2_idx1 on t2(id); 
create index t2_idx2 on t2(name); 

begin                                                                                  
  dbms_stats.gather_table_stats(ownname => user,tabname => t1,no_invalidate => false);
  dbms_stats.gather_table_stats(ownname => user,tabname => t2,no_invalidate => false);
end; 
/ 
--MERGE语句如下
MERGE INTO t2 USING ( 
  SELECT id,name FROM t1 
) x ON ( 
  x.id = t2.id or x.name = t2.name 
) 
WHEN MATCHED THEN UPDATE SET ext = xxx                                              
WHEN NOT MATCHED THEN INSERT (id) VALUES (1) ;

执行计划如下所示：

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 4096058702 
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | |     1 | 2067 |     4   (0)| 00:00:01 |
| 1 |  MERGE | T2 |       | |            | |
| 2 |   VIEW | |       | |            | |
| 3 |    MERGE JOIN OUTER | |     1 | 4094 |     4   (0)| 00:00:01 |
| 4 |     TABLE ACCESS FULL | T1 |     1 | 2015 |     2   (0)| 00:00:01 |
| 5 |     BUFFER SORT | |     1 | 2079 |     2   (0)| 00:00:01 |
| 6 |      VIEW | VW_LAT_8626BD41 |     1 | 2079 |     2   (0)| 00:00:01 |
|* 7 |       TABLE ACCESS FULL| T2 |     1 | 2079 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   7 - filter("ID"="T2"."ID" OR "NAME"="T2"."NAME")

在19c中对于merge语句竟然还走全表扫描，通过10053文件可以看到：

ORE: Checking validity of OR Expansion for query block SEL$2 (#0)
ORE: Predicate chain before QB validity check - SEL$2
"X"."ID"="T2"."ID" OR "X"."NAME"="T2"."NAME"
ORE: Predicate chain after QB validity check - SEL$2
"X"."ID"="T2"."ID" OR "X"."NAME"="T2"."NAME"
ORE: bypassed - Merge view query block.

也就是说，在CBO检查中，对于MERGE语句使用ORE（COSTOR EXPANSION）检查没有通过，然后就走不能使用OR扩展，从而走了全表扫描。

既然在对于MERGE语句的补丁26019148没有解决，那么只能通过如下手段解决了：

修改参数_optimizer_cbqt_or_expansion为false.

可以使用SQLPROFILE或SQLPATCH在语句级搞定，如下使用SQLPATCH：

DECLARE
  l VARCHAR2(32767);
BEGIN
  l := SYS.DBMS_SQLDIAG.create_sql_patch(
    sql_id => 67ujj1cy9c81f,
    hint_text => q[opt_param(_optimizer_cbqt_or_expansion,off)],
    name      => cbqt_ore_off);
END;
/

执行计划正确，使用了SQLPATCH，如下所示：

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 2960188956
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | |     1 | 2067 |     2   (0)| 00:00:01 |
| 1 |  MERGE | T2 |       | |            | |
| 2 |   VIEW | |       | |            | |
| 3 |    MERGE JOIN OUTER | |     1 | 4094 |     2   (0)| 00:00:01 |
| 4 |     TABLE ACCESS FULL | T1 |     1 | 2015 |     2   (0)| 00:00:01 |
| 5 |     BUFFER SORT | |     2 | 4158 |     0   (0)| 00:00:01 |
| 6 |      VIEW | VW_LAT_8626BD41 |     2 | 4158 |     0   (0)| 00:00:01 |
| 7 |       CONCATENATION | |       | |            | |
| 8 |        TABLE ACCESS BY INDEX ROWID BATCHED| T2 |     1 | 2079 |     0   (0)| 00:00:01 |
|* 9 |         INDEX RANGE SCAN | T2_IDX2 |     1 | |     0   (0)| 00:00:01 |
|* 10 |        TABLE ACCESS BY INDEX ROWID BATCHED| T2 |     1 | 2079 |     0   (0)| 00:00:01 |
|* 11 |         INDEX RANGE SCAN | T2_IDX1 |     1 | |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   9 - access("NAME"="T2"."NAME")
  10 - filter(LNNVL("NAME"="T2"."NAME"))
  11 - access("ID"="T2"."ID")

Note
-----
   - SQL patch "cbqt_ore_off" used for this statement

新特性固然好，但是往往会带来一些新的BUG，需要通过分析确认，可以通过打补丁，改语句、SQLPROFILE、SQLPATCH等方式避免触发BUG，从而获得性能提升。

本文作者：丁俊（上海新炬王翦团队）

本文来源：“IT那活儿”公众号

GPU云服务器云服务器大数据带来的问题租用idc机房解决的问题和带来的效益中文域名带来的好处大数据带来的变革

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129276.html

集成安装之Oracle12C补丁升级数据字典更新报错处理

集成安装之Oracle12C补丁升级数据字典更新报错处理 img{ display:block; margin:0 auto !important; width:100%; } body{ width:75%; ...

IT那活儿 2023-01-11 13:20 评论0 收藏795
DBASK问答集萃第四期

摘要：问题九库控制文件扩展报错库的扩展报错，用的是裸设备，和还是原来大小，主库的没有报错，并且大小没有变，求解释。专家解答从报错可以看出，控制文件从个块扩展到个块时报错，而裸设备最大只支持个块，无法扩展，可以尝试将参数改小，避免控制文件报错。链接描述引言近期我们在DBASK小程序新关联了运维之美、高端存储知识、一森咖记、运维咖啡吧等数据领域的公众号，欢迎大家阅读分享。问答集萃接下来，...

SKYZACK 2019-07-25 12:39 评论0 收藏0
OGG Integrated Native DDL简单测试

OGG Integrated Native DDL简单测试 img{ display:block; margin:0 auto !important; width:100%; } body{ width:75%;...

IT那活儿 2023-01-11 13:20 评论0 收藏1085

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师

我要关注我要私信

TA的文章

消息中间件故障分析一例

阅读 1235·2023-01-11 13:20
RAC双节点crash回复一例

阅读 1543·2023-01-11 13:20
ORA-600处理一例

阅读 996·2023-01-11 13:20
双节点RAC实例2 HANG 故障分析一例

阅读 1652·2023-01-11 13:20
RAC集群节点1重启分析一例

阅读 3958·2023-01-11 13:20
CRS启动报错CRS-1656处理分享

阅读 2456·2023-01-11 13:20
oracle 12CR2打补丁报错处理一例

阅读 1290·2023-01-11 13:20
分布式缓存组件故障分析及监控优化

阅读 3454·2023-01-11 13:20

资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

12C CBQT OR扩展BUG带来的问题

案例一：12.2的OR查询走不了索引

案例二：12.2后到19c merge语句的OR操作走不了索引

本文作者：丁俊（上海新炬王翦团队）

本文来源：“IT那活儿”公众号

相关文章

集成安装之Oracle12C补丁升级数据字典更新报错处理

DBASK问答集萃第四期

OGG Integrated Native DDL简单测试

发表评论

0条评论

IT那活儿

男|高级讲师

TA的文章

消息中间件故障分析一例

RAC双节点crash回复一例

ORA-600处理一例

双节点RAC实例2 HANG 故障分析一例

RAC集群节点1重启分析一例

CRS启动报错CRS-1656处理分享

oracle 12CR2打补丁报错处理一例

分布式缓存组件故障分析及监控优化

最新活动

资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

12C CBQT OR扩展BUG带来的问题

案例一：12.2的OR查询走不了索引

案例二：12.2后到19c merge语句的OR操作走不了索引

本文作者：丁 俊（上海新炬王翦团队）

本文来源：“IT那活儿”公众号

相关文章

发表评论

0条评论

男|高级讲师

TA的文章

最新活动

本文作者：丁俊（上海新炬王翦团队）