一条简单的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条件，是可以走索引联合查询的，如下所示：
-------------------------------------------------------------------------------------------------------------- |
对于这个问题，首先是收集统计信息，然而并没有啥用，因此，想到是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 |
ORACLE12.2开始对OR扩展使用CBQT，目的是用基于COST的方法，让CBO更加准确判断可能的访问路径，但是在12.2中却存在BUG，导致不能走索引，从而影响性能，参考案例1，在12.2中也有个补丁可以解决：
|
在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 |
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") |
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
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，从而获得性能提升。
文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。
转载请注明本文地址：https://www.ucloud.cn/yun/129992.html
集成安装之Oracle12C补丁升级数据字典更新报错处理 img{ display:block; margin:0 auto !important; width:100%; } body{ width:75%; ...
摘要：问题九库控制文件扩展报错库的扩展报错，用的是裸设备，和还是原来大小，主库的没有报错，并且大小没有变，求解释。专家解答从报错可以看出，控制文件从个块扩展到个块时报错，而裸设备最大只支持个块，无法扩展，可以尝试将参数改小，避免控制文件报错。 链接描述引言 近期我们在DBASK小程序新关联了运维之美、高端存储知识、一森咖记、运维咖啡吧等数据领域的公众号，欢迎大家阅读分享。 问答集萃 接下来，...
OGG Integrated Native DDL简单测试 img{ display:block; margin:0 auto !important; width:100%; } body{ width:75%;...
阅读 1250·2023-01-11 13:20
阅读 1559·2023-01-11 13:20
阅读 1013·2023-01-11 13:20
阅读 1680·2023-01-11 13:20
阅读 3972·2023-01-11 13:20
阅读 2520·2023-01-11 13:20
阅读 1356·2023-01-11 13:20
阅读 3486·2023-01-11 13:20
