12C CBQT OR擴展BUG帶來的問題

IT那活兒發布于2023-01-11 13:20 / 2617人閱讀

第一個案例：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

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

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

| 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

Workarounds:

Set _optimizer_cbqt_or_expansion=false

Use USE_CONCAT hint in query

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

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

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

| 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

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

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

| 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，從而獲得性能提升。

END

GPU云服務器云服務器大數據帶來的問題租用idc機房解決的問題和帶來的效益中文域名帶來的好處大數據帶來的變革

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/129992.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的文章

消息中間件故障分析一例

閱讀 1346·2023-01-11 13:20
RAC雙節點crash回復一例

閱讀 1684·2023-01-11 13:20
ORA-600處理一例

閱讀 1132·2023-01-11 13:20
雙節點RAC實例2 HANG 故障分析一例

閱讀 1858·2023-01-11 13:20
RAC集群節點1重啟分析一例

閱讀 4100·2023-01-11 13:20
CRS啟動報錯CRS-1656處理分享

閱讀 2704·2023-01-11 13:20
oracle 12CR2打補丁報錯處理一例

閱讀 1385·2023-01-11 13:20
分布式緩存組件故障分析及監控優化

閱讀 3597·2023-01-11 13:20

国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務器低至59元/年，更有多臺、長期優惠，快來選購！

12C CBQT OR擴展BUG帶來的問題

相關文章

集成安裝之Oracle12C補丁升級數據字典更新報錯處理

DBASK問答集萃第四期

OGG Integrated Native DDL簡單測試

發表評論

0條評論

IT那活兒

男|高級講師

TA的文章

消息中間件故障分析一例

RAC雙節點crash回復一例

ORA-600處理一例

雙節點RAC實例2 HANG 故障分析一例

RAC集群節點1重啟分析一例

CRS啟動報錯CRS-1656處理分享

oracle 12CR2打補丁報錯處理一例

分布式緩存組件故障分析及監控優化

最新活動