About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc
全部博文(172)
分类: Oracle
2020-07-06 23:48:28
1.第一个案例:12.2的OR查询走不了索引
一条简单的SQL,在11G下运行的很正常,走索引,到18C下,却走了全表扫描,而且收集统计信息无效。遇到这种问题,我的经验就是怀疑这是一个BUG,具体解决可以从10053 trace中分析或者搜索MOS文档或者用神器SQLT的explore功能,对于SQLT的使用,可以参考:http://blog.chinaunix.net/uid-7655508-id-5833181.html
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'))
和
http://blog.chinaunix.net/uid-7655508-id-5833184.html
废话少说,先上SQL语句:
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上搜索下,查到如下信息:
Bad Execution Plan With OR Query After Update To 12.2.0.1 (Doc ID 2536570.1)
按照给出的解决方案如下:
SOLUTION
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扩展使用COST BASED TRANSFORMATION,导致BUG,最终通过在语句级关闭_optimizer_cbqt_or_expansion参数搞定。
2.第二个案例:12.2后到19c merge语句的OR操作走不了索引
ORACLE 12.2开始对OR扩展使用CBQT,目的是用基于COST的方法,让CBO更加准确判断可能的访问路径,但是在12.2中却存在BUG,导致不能走索引,从而影响性能,参考案例1,在12.2中也有个补丁可以解决:
CBQT ORE DOES NOT APPLY TO CORRELATED SCALAR SUBQUERY WITH OE
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中,可以通过fix control参数控制这个补丁:
按理说在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(COST OR EXPANSION)检查没有通过,然后就走不能使用OR扩展,从而走了全表扫描。
既然在对于MERGE语句的补丁26019148没有解决,那么只能通过如下手段解决了:
修改参数_optimizer_cbqt_or_expansion为false
可以使用SQL PROFILE或SQL PATCH在语句级搞定,如下使用SQL PATCH:
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;
/
执行计划正确,使用了SQL PATCH,如下所示:
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 |
---------------------------------------------------------------------------------------------------
Outline
Data
-------------
/*+
BEGIN_OUTLINE_DATA
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$2_2"
"T2"@"SEL$2_2")
INDEX_RS_ASC(@"SEL$2_2"
"T2"@"SEL$2_2" ("T2"."ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$2_1"
"T2"@"SEL$2")
INDEX_RS_ASC(@"SEL$2_1"
"T2"@"SEL$2" ("T2"."NAME"))
FULL(@"SEL$4"
"T1"@"SEL$4")
USE_MERGE_CARTESIAN(@"SEL$8976F1A6"
"from$_subquery$_007"@"SEL$1")
LEADING(@"SEL$8976F1A6"
"T1"@"SEL$3"
"from$_subquery$_007"@"SEL$1")
NO_ACCESS(@"SEL$8976F1A6"
"from$_subquery$_007"@"SEL$1")
FULL(@"SEL$8976F1A6"
"T1"@"SEL$3")
USE_MERGE_CARTESIAN(@"MRG$1"
"from$_subquery$_008"@"MRG$1")
USE_MERGE_CARTESIAN(@"MRG$1"
"X"@"MRG$1")
LEADING(@"MRG$1"
"T2"@"MRG$1" "X"@"MRG$1"
"from$_subquery$_008"@"MRG$1")
NO_ACCESS(@"MRG$1"
"from$_subquery$_008"@"MRG$1")
NO_ACCESS(@"MRG$1"
"X"@"MRG$1")
FULL(@"MRG$1"
"T2"@"MRG$1")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$1")
OUTLINE_LEAF(@"SEL$2_2")
USE_CONCAT(@"SEL$2" 8
OR_PREDICATES(1))
OUTLINE_LEAF(@"SEL$2_1")
OUTLINE_LEAF(@"MRG$1")
OUTLINE_LEAF(@"SEL$4")
MERGE(@"SEL$3"
>"SEL$1")
OUTLINE_LEAF(@"SEL$8976F1A6")
OUTLINE_LEAF(@"SEL$2")
ALL_ROWS
OPT_PARAM('_optimizer_cbqt_or_expansion'
'off')
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
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,需要通过分析确认,可以通过打补丁,改语句活SQL PROFILE或SQL PATCH等在不改语句情况下避免BUG,从而获得性能的提升。