Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1104266
  • 博文数量: 145
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3510
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
个人简介

About me:Oracle ACE,optimistic,passionate and harmonious. Focus on oracle programming,peformance tuning,db design, j2ee,Linux/AIX,web2.0 tech,etc

文章分类

全部博文(145)

文章存档

2023年(28)

2022年(43)

2020年(62)

2014年(3)

2013年(9)

分类: Oracle

2020-07-06 23:48:28

1.第一个案例:12.2的OR查询走不了索引 

一条简单的SQL,在11G下运行的很正常,走索引,到18C下,却走了全表扫描,而且收集统计信息无效。遇到这种问题,我的经验就是怀疑这是一个BUG,具体解决可以从10053 trace中分析或者搜索MOS文档或者用神器SQLTexplore功能,对于SQLT的使用,可以参考:http://blog.chinaunix.net/uid-7655508-id-5833181.html

http://blog.chinaunix.net/uid-7655508-id-5833184.html

废话少说,先上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上搜索下,查到如下信息:

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

 
在18c中,可以通过fix control参数控制这个补丁:

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语句使用ORECOST OR EXPANSION)检查没有通过,然后就走不能使用OR扩展,从而走了全表扫描


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

修改参数_optimizer_cbqt_or_expansionfalse

可以使用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 PROFILESQL PATCH等在不改语句情况下避免BUG,从而获得性能的提升。

阅读(5075) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~