[20181220]使用提示OR_EXPAND优化.txt

Bertha 。 2022-01-10 02:49 121阅读 0赞

\[20181220\]使用提示OR\_EXPAND优化.txt  
  
\--//链接http://www.itpub.net/thread-2107240-2-1.html,http://www.itpub.net/thread-2107231-2-1.html的讨论.  
\--//ZALBB建议在18c下尝试看看,我们这里仅仅1台18c,而且还是生产系统,正好前几天在办公机器重新安装12c,在12c测试看看.  
\--//主要问题感觉oracle对于这样的sql有点奇怪....  
  
1.环境:  
SCOTT@test01p> @ ver1  
PORT\_STRING                    VERSION        BANNER                                                                               CON\_ID  
\------------------------------ -------------- -------------------------------------------------------------------------------- ----------  
IBMPC/WIN\_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0  
  
create table t1 as select rownum id1 ,rownum id2 ,lpad('x',100,'x') name from dual connect by level<=6000;  
create table t2 as select rownum id1 ,rownum id2 ,lpad('x',100,'x') name from dual connect by level<=6000;  
create index i\_t1\_id1 on t1(id1);  
create index i\_t1\_id2 on t1(id2);  
create index i\_t2\_id1 on t2(id1);  
  
\--//分析略.  
  
2.测试:  
SCOTT@test01p> alter session set statistics\_level = all;  
Session altered.  
  
SCOTT@test01p> select  \* from t1 where t1.id1 in  (select  t2.id1 from t2 where t2.id1=11 ) or  (t1.id2=10 );  
       ID1        ID2 NAME  
\---------- ---------- ----------------------------------------------------------------------------------------------------  
        10         10 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  
        11         11 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  
  
SCOTT@test01p> @ dpc '' ''  
PLAN\_TABLE\_OUTPUT  
\-------------------------------------  
SQL\_ID  gz5pqkg6svm7k, child number 0  
\-------------------------------------  
select  \* from t1 where t1.id1 in  (select  t2.id1 from t2 where  
t2.id1=11 ) or  (t1.id2=10 )  
Plan hash value: 1962644737  
\-------------------------------------------------------------------------------------------------------------------------  
| Id  | Operation          | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  
\-------------------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT   |          |      1 |        |       |    30 (100)|          |      2 |00:00:00.01 |     115 |  
|\*  1 |  FILTER            |          |      1 |        |       |            |          |      2 |00:00:00.01 |     115 |  
|   2 |   TABLE ACCESS FULL| T1       |      1 |   6000 |   638K|    30   (0)| 00:00:01 |   6000 |00:00:00.01 |     113 |  
|\*  3 |   FILTER           |          |   5999 |        |       |            |          |      1 |00:00:00.01 |       2 |  
|\*  4 |    INDEX RANGE SCAN| I\_T2\_ID1 |      1 |      1 |     4 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |  
\-------------------------------------------------------------------------------------------------------------------------  
Query Block Name / Object Alias (identified by operation id):  
\-------------------------------------------------------------  
   1 - SEL$1  
   2 - SEL$1 / T1@SEL$1  
   3 - SEL$2  
   4 - SEL$2 / T2@SEL$2  
Predicate Information (identified by operation id):  
\---------------------------------------------------  
   1 - filter(("T1"."ID2"=10 OR  IS NOT NULL))  
   3 - filter(11=:B1)  
   4 - access("T2"."ID1"=:B1)  
32 rows selected.  
  
\--//执行计划存在1个全表扫描.里面的索引选择性很好,oracle并没有选择合理的执行计划.  
\--//而且有1个小小的细节,id=4的starts=1,而前面的id=3的starts=5999.你可以看出这里oracle显示执行计划有1个小小的bug.  
\--//id=4的starts应该是5999.这样看到的逻辑读不应该是后面的2而是2\*5999 = 11998.  
\--//而且你可以看出oracle忽略的id=4多次INDEX RANGE SCAN的成本.  
\--//链接http://www.itpub.net/thread-2107240-2-1.html里面的显示倒是正确的.它的版本是11.2.0.4.180717.  
  
3.是否通过提示优化sql语句:  
\--//首先想到的是USE\_CONCAT.  
select /\*+ USE\_CONCAT(@"SEL$1" 8 OR\_PREDICATES(1)) \*/ \* from t1 where t1.id1 in  (select /\*+unnest \*/ t2.id1 from t2 where t2.id1=11 ) or  (t1.id2=10 );  
  
\--//执行计划如下:  
\-------------------------------------------------------------------------------------------------------------------------------------------  
| Id  | Operation                            | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  
\-------------------------------------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT                     |          |      1 |        |       |    33 (100)|          |      2 |00:00:00.01 |     118 |  
|   1 |  CONCATENATION                       |          |      1 |        |       |            |          |      2 |00:00:00.01 |     118 |  
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1       |      1 |      1 |   109 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |  
|\*  3 |    INDEX RANGE SCAN                  | I\_T1\_ID2 |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |  
|\*  4 |   FILTER                             |          |      1 |        |       |            |          |      1 |00:00:00.01 |     114 |  
|\*  5 |    TABLE ACCESS FULL                 | T1       |      1 |   5999 |   638K|    30   (0)| 00:00:01 |   5999 |00:00:00.01 |     112 |  
|\*  6 |    FILTER                            |          |   5999 |        |       |            |          |      1 |00:00:00.01 |       2 |  
|\*  7 |     INDEX RANGE SCAN                 | I\_T2\_ID1 |      1 |      1 |     4 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |  
\-------------------------------------------------------------------------------------------------------------------------------------------  
Query Block Name / Object Alias (identified by operation id):  
\-------------------------------------------------------------  
   1 - SEL$1  
   2 - SEL$1\_1 / T1@SEL$1  
   3 - SEL$1\_1 / T1@SEL$1  
   5 - SEL$1\_2 / T1@SEL$1\_2  
   6 - SEL$2  
   7 - SEL$2   / T2@SEL$2  
Predicate Information (identified by operation id):  
\---------------------------------------------------  
   3 - access("T1"."ID2"=10)  
   4 - filter( IS NOT NULL)  
   5 - filter(LNNVL("T1"."ID2"=10))  
   6 - filter(11=:B1)  
   7 - access("T2"."ID1"=:B1)  
  
\--//很奇怪id=4,依旧选择过滤,unnest提示没有用.实际上使用USE\_CONCAT相当每个or分支加入LNNVL(条件)来排他符合条件的记录.  
\--//也就是oracle依旧选择的执行计划不是很理想,甚至比前面还要差.  
  
4.尝试OR\_EXPAND提示:  
select /\*+ OR\_EXPAND \*/ \* from t1 where t1.id1 in  (select  /\*+ unnest \*/ t2.id1 from t2 where t2.id1=11 ) or  (t1.id2=10 );  
  
\--//执行计划如下:  
Plan hash value: 1716482303  
\----------------------------------------------------------------------------------------------------------------------------------------------------  
| Id  | Operation                              | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  
\----------------------------------------------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT                       |                 |      1 |        |       |     5 (100)|          |      2 |00:00:00.01 |       9 |  
|   1 |  VIEW                                  | VW\_ORE\_BA8ECEFB |      1 |      2 |   156 |     5   (0)| 00:00:01 |      2 |00:00:00.01 |       9 |  
|   2 |   UNION-ALL                            |                 |      1 |        |       |            |          |      2 |00:00:00.01 |       9 |  
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED | T1              |      1 |      1 |   109 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |  
|\*  4 |     INDEX RANGE SCAN                   | I\_T1\_ID2        |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |  
|   5 |    NESTED LOOPS SEMI                   |                 |      1 |      1 |   113 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |  
|\*  6 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1              |      1 |      1 |   109 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |  
|\*  7 |      INDEX RANGE SCAN                  | I\_T1\_ID1        |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |  
|\*  8 |     INDEX RANGE SCAN                   | I\_T2\_ID1        |      1 |      1 |     4 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |  
\----------------------------------------------------------------------------------------------------------------------------------------------------  
Query Block Name / Object Alias (identified by operation id):  
\-------------------------------------------------------------  
   1 - SET$9162BF3C   / VW\_ORE\_BA8ECEFB@SEL$BA8ECEFB  
   2 - SET$9162BF3C  
   3 - SET$9162BF3C\_1 / T1@SEL$1  
   4 - SET$9162BF3C\_1 / T1@SEL$1  
   5 - SEL$C90BA1D5  
   6 - SEL$C90BA1D5   / T1@SEL$1  
   7 - SEL$C90BA1D5   / T1@SEL$1  
   8 - SEL$C90BA1D5   / T2@SEL$2  
Outline Data  
\-------------  
  /\*+  
      BEGIN\_OUTLINE\_DATA  
      IGNORE\_OPTIM\_EMBEDDED\_HINTS  
      OPTIMIZER\_FEATURES\_ENABLE('12.2.0.1')  
      DB\_VERSION('12.2.0.1')  
      ALL\_ROWS  
      OUTLINE\_LEAF(@"SEL$C90BA1D5")  
      UNNEST(@"SEL$2")  
      OUTLINE\_LEAF(@"SET$9162BF3C\_1")  
      OUTLINE\_LEAF(@"SET$9162BF3C")  
      OR\_EXPAND(@"SEL$1" (1) (2))  
      OUTLINE\_LEAF(@"SEL$BA8ECEFB")  
      OUTLINE(@"SET$9162BF3C\_2")  
      OUTLINE(@"SEL$2")  
      OUTLINE(@"SET$9162BF3C")  
      OR\_EXPAND(@"SEL$1" (1) (2))  
      OUTLINE(@"SEL$1")  
      NO\_ACCESS(@"SEL$BA8ECEFB" "VW\_ORE\_BA8ECEFB"@"SEL$BA8ECEFB")  
      INDEX\_RS\_ASC(@"SET$9162BF3C\_1" "T1"@"SEL$1" ("T1"."ID2"))  
      BATCH\_TABLE\_ACCESS\_BY\_ROWID(@"SET$9162BF3C\_1" "T1"@"SEL$1")  
      INDEX\_RS\_ASC(@"SEL$C90BA1D5" "T1"@"SEL$1" ("T1"."ID1"))  
      BATCH\_TABLE\_ACCESS\_BY\_ROWID(@"SEL$C90BA1D5" "T1"@"SEL$1")  
      INDEX(@"SEL$C90BA1D5" "T2"@"SEL$2" ("T2"."ID1"))  
      LEADING(@"SEL$C90BA1D5" "T1"@"SEL$1" "T2"@"SEL$2")  
      USE\_NL(@"SEL$C90BA1D5" "T2"@"SEL$2")  
      END\_OUTLINE\_DATA  
  \*/  
Predicate Information (identified by operation id):  
\---------------------------------------------------  
   4 - access("T1"."ID2"=10)  
   6 - filter(LNNVL("T1"."ID2"=10))  
   7 - access("T1"."ID1"=11)  
   8 - access("T2"."ID1"=11)  
       filter("T1"."ID1"="T2"."ID1")  
  
\--//12c下oracle选择正确的执行计划.可以发现id=2使用UNION-ALL,也就是oracle做了查询转换成union all的形式.  
\--//另外我曾经尝试将ounline date的提示信息加入到11g环境,执行计划依旧没有选择OR\_EXPAND.  
\--//通过10053事件看看.  
  
SCOTT@test01p> @ 10053x cg5kmfhgczjfd 0  
PL/SQL procedure successfully completed.  
  
ORE: after OR Expansion:\*\*\*\*\*\*\* UNPARSED QUERY IS \*\*\*\*\*\*\*  
SELECT "VW\_ORE\_BA8ECEFB"."ITEM\_1" "ID1","VW\_ORE\_BA8ECEFB"."ITEM\_2" "ID2","VW\_ORE\_BA8ECEFB"."ITEM\_3" "NAME" FROM  ( (SELECT "T1"."ID1" "ITEM\_1","T1"."ID2" "ITEM\_2","T1"."NAME" "ITEM\_3" FROM "SCOTT"."T1" "T1" WHERE "T1"."ID2"=10) UNION ALL  (SELECT "T1"."ID1" "ITEM\_1","T1"."ID2" "ITEM\_2","T1"."NAME" "ITEM\_3" FROM "SCOTT"."T1" "T1" WHERE "T1"."ID1"=ANY (SELECT /\*+ UNNEST \*/ "T2"."ID1" "ID1" FROM "SCOTT"."T2" "T2" WHERE "T2"."ID1"=11) AND LNNVL("T1"."ID2"=10))) "VW\_ORE\_BA8ECEFB"  
  
\--//格式化显示如下:  
SELECT "VW\_ORE\_BA8ECEFB"."ITEM\_1" "ID1"  
      ,"VW\_ORE\_BA8ECEFB"."ITEM\_2" "ID2"  
      ,"VW\_ORE\_BA8ECEFB"."ITEM\_3" "NAME"  
  FROM ( (SELECT "T1"."ID1" "ITEM\_1"  
                ,"T1"."ID2" "ITEM\_2"  
                ,"T1"."NAME" "ITEM\_3"  
            FROM "SCOTT"."T1" "T1"  
           WHERE "T1"."ID2" = 10)  
        UNION ALL  
        (SELECT "T1"."ID1" "ITEM\_1"  
               ,"T1"."ID2" "ITEM\_2"  
               ,"T1"."NAME" "ITEM\_3"  
           FROM "SCOTT"."T1" "T1"  
          WHERE     "T1"."ID1" = ANY (SELECT /\*+ UNNEST \*/  
                                            "T2"."ID1" "ID1"  
                                        FROM "SCOTT"."T2" "T2"  
                                       WHERE "T2"."ID1" = 11)  
                AND LNNVL ("T1"."ID2" = 10))) "VW\_ORE\_BA8ECEFB";  
  
\--//也就是oracle查询转换为 UNION ALL的形式.  
\--//你可以看到第2个条件人为的加入LNNVL ("T1"."ID2" = 10).  
\--// OR\_EXPAND 提示 与 USE\_CONCAT 提示到底有什么不同?  
  
5.补充使用USE\_CONCAT看到的情况:  
  
select /\*+ USE\_CONCAT(@"SEL$1" 8 OR\_PREDICATES(1)) \*/ \* from t1 where t1.id1 in  (select /\*+unnest \*/ t2.id1 from t2 where t2.id1=11 ) or  (t1.id2=10 );  
  
SCOTT@test01p> @ 10053x 18h6hkqcqq3w2 0  
PL/SQL procedure successfully completed.  
  
\--//看这些太烦,不过可以发现如下:  
LORE: Or-Expansion validity checks failed on query block SEL$2 (\#2) because Cost based OR expansion enabled  
  
SYS@test01p> @ hide or\_exp  
old  10:  and lower(a.ksppinm) like lower('%&1%')  
new  10:  and lower(a.ksppinm) like lower('%or\_exp%')  
NAME                               DESCRIPTION                                       DEFAULT\_VALUE SESSION\_VALUE SYSTEM\_VALUE  
\---------------------------------- ------------------------------------------------- ------------- ------------- ------------  
\_no\_or\_expansion                   OR expansion during optimization disabled         TRUE          FALSE         FALSE  
\_optimizer\_cbqt\_or\_expansion       enables cost based OR expansion                   TRUE          ON            ON  
\_optimizer\_interleave\_or\_expansion interleave OR Expansion during CBQT               TRUE          TRUE          TRUE  
\_optimizer\_or\_expansion            control or expansion approach used                TRUE          DEPTH         DEPTH  
\_optimizer\_or\_expansion\_subheap    Use subheap for optimizer or-expansion            TRUE          TRUE          TRUE  
\_or\_expand\_nvl\_predicate           enable OR expanded plan for NVL/DECODE predicate  TRUE          TRUE          TRUE  
6 rows selected.  
\--//也就是12c缺省打开因为以上原因.不过我尝试"\_optimizer\_cbqt\_or\_expansion"=off也无效.放弃!!  
  
\--//我也尝试提高全表扫描的成本看看是否执行计划会发生改变,不过依旧没用.  
SCOTT@test01p> exec dbms\_stats.SET\_TABLE\_STATS(user,'T1',NUMBLKS=>800000000000);  
PL/SQL procedure successfully completed.

转载于:https://www.cnblogs.com/lfree/p/10150079.html