[20250714]使用or_expand提示遇到的疑惑.txt

–//生产系统遇到的问题，使用or_expand提示，通过例子说明问题。

1.环境：

SCOTT@book01p> @ver2

==============================

PORT_STRING                   : x86_64/Linux 2.4.xx

VERSION                       : 21.0.0.0.0

BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production

BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production

Version 21.3.0.0.0

BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production

CON_ID                        : 0

PL/SQL procedure successfully completed.

2.建立测试环境：

–//drop table t1 purge ;

create table t1 as select * from all_objects;

create index i_t1_object_id on t1(object_id);

–//create index i_t1_data_object_id on t1(data_object_id);

–//create index i_t1_object_name on t1(object_name);

–//分析表略。

–//当前仅仅建立object_id字段索引。

–//建立测试执行sql语句：

$ cat g1.txt

set term off

variable v_id number ;

variable v_did number ;

variable v_name varchar2(32) ;

exec :v_id := 76191;

exec :v_did := 76191;

exec :v_name := ‘DEPT’;

set term on

SELECT /*+ &&1 */

      t1.object_id

      ,t1.object_name

      ,t1.object_type

  FROM t1

 WHERE

       ( ( :v_did = ” OR :v_did IS NULL) OR data_object_id = :v_did)

and       ( ( :v_id = ” OR :v_id IS NULL) OR object_id = :v_id)

and       ( ( :v_name = ” OR :v_name IS NULL) OR object_name = :v_name)

;

–//注：主要模拟开发的写法，另外注意查询条件有索引的条件写在中间。

3.测试：

SCOTT@book01p> @ sl all

alter session set statistics_level = all;

Session altered.

SCOTT@book01p> @ g1.txt ”

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE

———- —————————— ——————————

     76191 DEPT                           TABLE

–//执行计划如下，在没有任何提示的情况下，执行计划选择全表扫描。

Plan hash value: 3617692013

——————————————————————————————————————–

| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |

——————————————————————————————————————–

|   0 | SELECT STATEMENT  |      |      1 |        |       |   414 (100)|          |      1 |00:00:00.01 |    1486 |

|*  1 |  TABLE ACCESS FULL| T1   |      1 |      9 |   477 |   414   (1)| 00:00:01 |      1 |00:00:00.01 |    1486 |

——————————————————————————————————————–

Query Block Name / Object Alias (identified by operation id):

————————————————————-

   1 – SEL$1 / “T1″@”SEL$1”

Outline Data

————-

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE(‘21.1.0’)

      DB_VERSION(‘21.1.0’)

      ALL_ROWS

      OUTLINE_LEAF(@”SEL$1″)

      FULL(@”SEL$1″ “T1″@”SEL$1”)

      END_OUTLINE_DATA

  */

Peeked Binds (identified by position):

————————————–

   3 – (NUMBER, Primary=1)

   6 – (NUMBER, Primary=4)

   9 – (VARCHAR2(30), CSID=852, Primary=7)

Predicate Information (identified by operation id):

—————————————————

   1 – filter(((:V_ID IS NULL OR “OBJECT_ID”=:V_ID) AND (:V_DID IS NULL OR “DATA_OBJECT_ID”=:V_DID) AND

              (:V_NAME IS NULL OR “OBJECT_NAME”=:V_NAME)))

–//加入提示OR_EXPAND(@”SEL$1″)

SCOTT@book01p> @ g1.txt ‘OR_EXPAND(@”SEL$1″)’

OBJECT_ID OBJECT_NAME                    OBJECT_TYPE

———- —————————— ——————————

     76191 DEPT                           TABLE

–//执行计划如下：

Plan hash value: 3861657155

————————————————————————————————————————————-

| Id |Operation                              |Name           |Starts|E-Rows |E-Bytes|Cost (%CPU)|E-Time  |A-Rows|   A-Time  |Buffers|

————————————————————————————————————————————-

|   0|SELECT STATEMENT                       |               |     1|       |       |  416 (100)|        |     1|00:00:00.01|      4|

|   1| VIEW                                  |VW_ORE_BA8ECEFB|     1|   177 | 16284 |  416   (1)|00:00:01|     1|00:00:00.01|      4|

|   2|  UNION-ALL                            |               |     1|       |       |           |        |     1|00:00:00.01|      4|

|*  3|   FILTER                              |               |     1|       |       |           |        |     0|00:00:00.01|      0|

|*  4|    TABLE ACCESS FULL                  |T1             |     0|   176 |  9328 |  414   (1)|00:00:01|     0|00:00:00.01|      0|

|*  5|   FILTER                              |               |     1|       |       |           |        |     1|00:00:00.01|      4|

|*  6|    TABLE ACCESS BY INDEX ROWID BATCHED|T1             |     1|     1 |    53 |    2   (0)|00:00:01|     1|00:00:00.01|      4|

|*  7|     INDEX RANGE SCAN                  |I_T1_OBJECT_ID |     1|     1 |       |    1   (0)|00:00:01|     1|00:00:00.01|      3|

————————————————————————————————————————————-

Query Block Name / Object Alias (identified by operation id):

————————————————————-

   1 – SET$2A13AF86   / “VW_ORE_BA8ECEFB”@”SEL$BA8ECEFB”

   2 – SET$2A13AF86

   3 – SET$2A13AF86_1

   4 – SET$2A13AF86_1 / “T1″@”SET$2A13AF86_1”

   5 – SET$2A13AF86_2

   6 – SET$2A13AF86_2 / “T1″@”SET$2A13AF86_2”

   7 – SET$2A13AF86_2 / “T1″@”SET$2A13AF86_2”

Outline Data

————-

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE(‘21.1.0’)

      DB_VERSION(‘21.1.0’)

      ALL_ROWS

      OUTLINE_LEAF(@”SET$2A13AF86_2″)

      OUTLINE_LEAF(@”SET$2A13AF86_1″)

      OUTLINE_LEAF(@”SET$2A13AF86″)

      OUTLINE_LEAF(@”SEL$9162BF3C”)

      OR_EXPAND(@”SEL$1″ (1) (2))

      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~

      OUTLINE(@”SEL$1″)

      NO_ACCESS(@”SEL$9162BF3C” “VW_ORE_BA8ECEFB”@”SEL$BA8ECEFB”)

      FULL(@”SET$2A13AF86_1″ “T1″@”SET$2A13AF86_1”)

      INDEX_RS_ASC(@”SET$2A13AF86_2″ “T1″@”SET$2A13AF86_2” (“T1″.”OBJECT_ID”))

      BATCH_TABLE_ACCESS_BY_ROWID(@”SET$2A13AF86_2″ “T1″@”SET$2A13AF86_2”)

      END_OUTLINE_DATA

  */

Peeked Binds (identified by position):

————————————–

   3 – (NUMBER, Primary=1)

   6 – (NUMBER, Primary=4)

   9 – (VARCHAR2(30), CSID=852, Primary=7)

Predicate Information (identified by operation id):

—————————————————

   3 – filter(:V_ID IS NULL)

   4 – filter(((:V_DID IS NULL OR “DATA_OBJECT_ID”=:V_DID) AND (:V_NAME IS NULL OR “OBJECT_NAME”=:V_NAME)))

   5 – filter(LNNVL(:V_ID IS NULL))

   6 – filter(((:V_DID IS NULL OR “DATA_OBJECT_ID”=:V_DID) AND (:V_NAME IS NULL OR “OBJECT_NAME”=:V_NAME)))

   7 – access(“OBJECT_ID”=:V_ID)

Hint Report (identified by operation id / Query Block Name / Object Alias):

Total hints for statement: 1

—————————————————————————

   5 –  SET$2A13AF86_2

           –  OR_EXPAND(@”SEL$1″)

–//注意实际上outline里面记录的是OR_EXPAND(@”SEL$1″ (1) (2))。

–//但是当我加入提示OR_EXPAND(@”SEL$1″ (1) (2))时会出现什么情况呢。

SCOTT@book01p> @ g1.txt ‘OR_EXPAND(@”SEL$1″ (1) (2))’

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE

———- —————————— ——————————

     76191 DEPT                           TABLE

–//执行计划如下：

COTT@book01p> @ dpc ” outline ”

…

Plan hash value: 3617692013

——————————————————————————————————————–

| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |

——————————————————————————————————————–

|   0 | SELECT STATEMENT  |      |      1 |        |       |   414 (100)|          |      1 |00:00:00.01 |    1486 |

|*  1 |  TABLE ACCESS FULL| T1   |      1 |      9 |   477 |   414   (1)| 00:00:01 |      1 |00:00:00.01 |    1486 |

——————————————————————————————————————–

Query Block Name / Object Alias (identified by operation id):

————————————————————-

   1 – SEL$1 / “T1″@”SEL$1”

Outline Data

————-

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE(‘21.1.0’)

      DB_VERSION(‘21.1.0’)

      ALL_ROWS

      OUTLINE_LEAF(@”SEL$1″)

      FULL(@”SEL$1″ “T1″@”SEL$1”)

      END_OUTLINE_DATA

  */

Peeked Binds (identified by position):

————————————–

   3 – (NUMBER, Primary=1)

   6 – (NUMBER, Primary=4)

   9 – (VARCHAR2(30), CSID=852, Primary=7)

Predicate Information (identified by operation id):

—————————————————

   1 – filter(((:V_ID IS NULL OR “OBJECT_ID”=:V_ID) AND (:V_DID IS NULL OR “DATA_OBJECT_ID”=:V_DID) AND

              (:V_NAME IS NULL OR “OBJECT_NAME”=:V_NAME)))

Hint Report (identified by operation id / Query Block Name / Object Alias):

Total hints for statement: 1 (U – Unused (1))

—————————————————————————

   1 –  SEL$1

         U –  OR_EXPAND(@”SEL$1″ (1) (2))

–//执行计划反而时选择全表扫描。

–//可以抽取前面的outline，写成如下：

$ cat g2.txt

set term off

variable v_id number ;

variable v_did number ;

variable v_name varchar2(32) ;

exec :v_id := 76191;

exec :v_did := 76191;

exec :v_name := ‘DEPT’;

set term on

SELECT

 /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE(‘21.1.0’)

      DB_VERSION(‘21.1.0’)

      ALL_ROWS

      OUTLINE_LEAF(@”SET$2A13AF86_2″)

      OUTLINE_LEAF(@”SET$2A13AF86_1″)

      OUTLINE_LEAF(@”SET$2A13AF86″)

      OUTLINE_LEAF(@”SEL$9162BF3C”)

      OR_EXPAND(@”SEL$1″ (1) (2))

      OUTLINE(@”SEL$1″)

      NO_ACCESS(@”SEL$9162BF3C” “VW_ORE_BA8ECEFB”@”SEL$BA8ECEFB”)

      FULL(@”SET$2A13AF86_1″ “T1″@”SET$2A13AF86_1”)

      INDEX_RS_ASC(@”SET$2A13AF86_2″ “T1″@”SET$2A13AF86_2” (“T1″.”OBJECT_ID”))

      BATCH_TABLE_ACCESS_BY_ROWID(@”SET$2A13AF86_2″ “T1″@”SET$2A13AF86_2”)

      END_OUTLINE_DATA

  */

      t1.object_id

      ,t1.object_name

      ,t1.object_type

  FROM t1

 WHERE

       ( ( :v_did = ” OR :v_did IS NULL) OR data_object_id = :v_did)

and       ( ( :v_id = ” OR :v_id IS NULL) OR object_id = :v_id)

and       ( ( :v_name = ” OR :v_name IS NULL) OR object_name = :v_name)

;

–//执行发现执行计划还是选择全表扫描，结果不再贴出。

4.分析：

–//做10053分析：

SCOTT@book01p> @ g1.txt ‘OR_EXPAND(@”SEL$1″ )’

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE

———- —————————— ——————————

     76191 DEPT                           TABLE

SCOTT@book01p> @ hash

HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID

———- ————- ———— ———- ————— ———- ——————- ———–

1827272953 az74updqfmy7t            0     129273      3861657155  6ce9f8f9  2025-07-14 09:43:16    16777219

SCOTT@book01p> @ 10053x az74updqfmy7t 0

/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3990_aaz74updqfmy7t.trc

COTT@book01p> @ 10053y ”

TRCLINE

————————————————————————————————————————

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT “VW_ORE_BA8ECEFB”.”ITEM_1″ “OBJECT_ID”,”VW_ORE_BA8ECEFB”.”ITEM_2″ “OBJECT_NAME”,”VW_ORE_BA8ECEFB”.”ITEM_3″ “OBJEC

T_TYPE” FROM  ( (SELECT “T1″.”OBJECT_ID” “ITEM_1″,”T1″.”OBJECT_NAME” “ITEM_2″,”T1″.”OBJECT_TYPE” “ITEM_3” FROM “SCOTT”.”

T1″ “T1” WHERE (:B1 IS NULL OR “T1”.”DATA_OBJECT_ID”=:B2) AND :B3 IS NULL AND (:B4 IS NULL OR “T1”.”OBJECT_NAME”=:B5)) U

NION ALL  (SELECT “T1″.”OBJECT_ID” “ITEM_1″,”T1″.”OBJECT_NAME” “ITEM_2″,”T1″.”OBJECT_TYPE” “ITEM_3” FROM “SCOTT”.”T1″ “T

1″ WHERE (:B6 IS NULL OR “T1”.”DATA_OBJECT_ID”=:B7) AND “T1”.”OBJECT_ID”=:B8 AND (:B9 IS NULL OR “T1”.”OBJECT_NAME”=:B10

) AND LNNVL(:B11 IS NULL))) “VW_ORE_BA8ECEFB”

–//格式化如下：

/* Formatted on 2025-07-14 09:47:56 (QP5 v5.277) */

SELECT “VW_ORE_BA8ECEFB”.”ITEM_1″ “OBJECT_ID”

      ,”VW_ORE_BA8ECEFB”.”ITEM_2″ “OBJECT_NAME”

      ,”VW_ORE_BA8ECEFB”.”ITEM_3″ “OBJECT_TYPE”

  FROM ( (SELECT “T1″.”OBJECT_ID” “ITEM_1”, “T1″.”OBJECT_NAME” “ITEM_2”, “T1″.”OBJECT_TYPE” “ITEM_3”

            FROM “SCOTT”.”T1″ “T1”

           WHERE     ( :B1 IS NULL OR “T1″.”DATA_OBJECT_ID” = :B2)

                 AND :B3 IS NULL

                 AND ( :B4 IS NULL OR “T1″.”OBJECT_NAME” = :B5))

        UNION ALL

        (SELECT “T1″.”OBJECT_ID” “ITEM_1”, “T1″.”OBJECT_NAME” “ITEM_2”, “T1″.”OBJECT_TYPE” “ITEM_3”

           FROM “SCOTT”.”T1″ “T1”

          WHERE     ( :B6 IS NULL OR “T1″.”DATA_OBJECT_ID” = :B7)

                AND “T1″.”OBJECT_ID” = :B8

                AND ( :B9 IS NULL OR “T1″.”OBJECT_NAME” = :B10)

                AND LNNVL ( :B11 IS NULL))) “VW_ORE_BA8ECEFB”

–//查看跟踪文件内容：

ORE:  Predicate list

P1 : (:B1 IS NULL OR “T1”.”DATA_OBJECT_ID”=:B2) AND (:B3 IS NULL OR “T1”.”OBJECT_ID”=:B4) AND (:B5 IS NULL OR “T1”.”OBJECT_NAME”=:B6)

P2 : :B1 IS NULL

P3 : “T1”.”OBJECT_ID”=:B1

P4 : :B1 IS NULL OR “T1”.”OBJECT_NAME”=:B2

 DNF Matrix (Before sorting OR branches)

            P1  P2  P3  P4

CNJ (#1) :   1   1   0   1

CNJ (#2) :   1   0   1   1

ORE:  Predicate list

P1 : (:B1 IS NULL OR “T1”.”DATA_OBJECT_ID”=:B2) AND (:B3 IS NULL OR “T1”.”OBJECT_ID”=:B4) AND (:B5 IS NULL OR “T1”.”OBJECT_NAME”=:B6)

P2 : :B1 IS NULL

P3 : “T1”.”OBJECT_ID”=:B1

P4 : :B1 IS NULL OR “T1”.”OBJECT_NAME”=:B2

 DNF Matrix (After OR branch sorting)

            P1  P2  P3  P4

CNJ (#1) :   1   1   0   1

CNJ (#2) :   1   0   1   1

–//oracle仅仅拆分2种情况，对应中间条件( ( :v_id = ” OR :v_id IS NULL) OR object_id = :v_id)。

–//实际上oracle将条件改写为(:v_id IS NULL OR object_id = :v_id).

–//注意不要被里面:b1,:b2之类绑定变量名字迷惑，非常容易误解。oracle重新命名的绑定变量名字，每行都是从B1开始。

–//另外oracle在上面的情况拆分存在问题，我的理解应该这样拆分。

P1 : :B1 IS NULL OR “T1”.”DATA_OBJECT_ID”=:B2

P2 : :B1 IS NULL

P3 : “T1”.”OBJECT_ID”=:B1

P4 : :B1 IS NULL OR “T1”.”OBJECT_NAME”=:B2

–//再来看看加入OR_EXPAND(@”SEL$1″ (1) (2))的情况。

SCOTT@book01p> @ g1.txt ‘OR_EXPAND(@”SEL$1″ (1) (2))’

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE

———- —————————— ——————————

     76191 DEPT                           TABLE

SCOTT@book01p> @ hashz

HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID

———- ————- ———— ———- ———- ——————- ———–

1999013600 bs80ktjvkd1r0            0      34528  772686e0  2025-07-14 10:01:33    16777218

SCOTT@book01p> @ 10053x bs80ktjvkd1r0 0

/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3990_abs80ktjvkd1r0.trc

SCOTT@book01p> @ 10053y ”

TRCLINE

————————————————————————————————————————

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT /*+  (1) (2)) */ “T1″.”OBJECT_ID” “OBJECT_ID”,”T1″.”OBJECT_NAME” “OBJECT_NAME”,”T1″.”OBJECT_TYPE” “OBJECT_TYPE” F

ROM “SCOTT”.”T1″ “T1” WHERE (:B1 IS NULL OR “T1”.”DATA_OBJECT_ID”=:B2) AND (:B3 IS NULL OR “T1”.”OBJECT_ID”=:B4) AND (:B

5 IS NULL OR “T1”.”OBJECT_NAME”=:B6)

–//转换后注解部分丢失OR_EXPAND。里面的绑定变量被替换为:Bn之类的变量名，注意这里转换后，在执行计划的Peeked Binds

–//(identified by position):部分看不见实际的绑定变量值。可以查看前面的执行计划。

–//查看跟踪文件内容：

ORE:  Predicate list

P1 : :B1 IS NULL

P2 : “T1”.”DATA_OBJECT_ID”=:B1

P3 : :B1 IS NULL

P4 : “T1”.”OBJECT_ID”=:B1

P5 : :B1 IS NULL

P6 : “T1”.”OBJECT_NAME”=:B1

 DNF Matrix (Before sorting OR branches)

            P1  P2  P3  P4  P5  P6

CNJ (#1) :   1   0   1   0   1   0

CNJ (#2) :   1   0   1   0   0   1

CNJ (#3) :   1   0   0   1   1   0

CNJ (#4) :   1   0   0   1   0   1

CNJ (#5) :   0   1   1   0   1   0

CNJ (#6) :   0   1   1   0   0   1

CNJ (#7) :   0   1   0   1   1   0

CNJ (#8) :   0   1   0   1   0   1

ORE:  Predicate list

P1 : :B1 IS NULL

P2 : “T1”.”DATA_OBJECT_ID”=:B1

P3 : :B1 IS NULL

P4 : “T1”.”OBJECT_ID”=:B1

P5 : :B1 IS NULL

P6 : “T1”.”OBJECT_NAME”=:B1

 DNF Matrix (After OR branch sorting)

            P1  P2  P3  P4  P5  P6

CNJ (#1) :   1   0   1   0   1   0

CNJ (#2) :   1   0   1   0   0   1

CNJ (#3) :   1   0   0   1   1   0

CNJ (#4) :   1   0   0   1   0   1

CNJ (#5) :   0   1   1   0   1   0

CNJ (#6) :   0   1   1   0   0   1

CNJ (#7) :   0   1   0   1   1   0

CNJ (#8) :   0   1   0   1   0   1

–//oracle仅仅拆分8种情况，在这样的情况下提示变成无效,选择全表扫描。

–//也就是在这样的情况直接使用OR_EXPAND(@”SEL$1″ (1) (2)))提示无效。

–//而使用提示 OR_EXPAND(@”SEL$1″ (1) (2) (3) (4) (5) (6) (7) (8) )有效。执行计划不再贴出。

–//oracle在处理这类or的优化时存在一些问题，后面的参数仅仅表示拆分的数量。

5.继续：

–//如果写成如下：

$ cat g1.txt

set term off

variable v_id number ;

variable v_did number ;

variable v_name varchar2(32) ;

exec :v_id := 76191;

exec :v_did := 76191;

exec :v_name := ‘DEPT’;

set term on

SELECT /*+ &&1 */

      t1.object_id

      ,t1.object_name

      ,t1.object_type

  FROM t1

 WHERE

—       ( ( :v_did = ” OR :v_did IS NULL) OR data_object_id = :v_did)

–and       ( ( :v_id = ” OR :v_id IS NULL) OR object_id = :v_id)

       ( ( :v_id = ” OR :v_id IS NULL) OR object_id = :v_id)

and       ( ( :v_did = ” OR :v_did IS NULL) OR data_object_id = :v_did)

and       ( ( :v_name = ” OR :v_name IS NULL) OR object_name = :v_name)

;

–//原来写在中间的查询条件放在前面。

SCOTT@book01p> @ g1.txt ‘OR_EXPAND(@”SEL$1″ )’

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE

———- —————————— ——————————

     76191 DEPT                           TABLE

SCOTT@book01p> @ hash

HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID

———- ————- ———— ———- ————— ———- ——————- ———–

2072084780 5kftshxxs309c            0      98604      3861657155  7b81812c  2025-07-14 15:59:20    16777218

–//执行计划如下：

Plan hash value: 3861657155

—————————————————————————————————————————————————-

| Id  | Operation                              | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |

—————————————————————————————————————————————————-

|   0 | SELECT STATEMENT                       |                 |      1 |        |       |   416 (100)|          |      1 |00:00:00.01 |       4 |

|   1 |  VIEW                                  | VW_ORE_BA8ECEFB |      1 |    177 | 16284 |   416   (1)| 00:00:01 |      1 |00:00:00.01 |       4 |

|   2 |   UNION-ALL                            |                 |      1 |        |       |            |          |      1 |00:00:00.01 |       4 |

|*  3 |    FILTER                              |                 |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |

|*  4 |     TABLE ACCESS FULL                  | T1              |      0 |    176 |  9328 |   414   (1)| 00:00:01 |      0 |00:00:00.01 |       0 |

|*  5 |    FILTER                              |                 |      1 |        |       |            |          |      1 |00:00:00.01 |       4 |

|*  6 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1              |      1 |      1 |    53 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |

|*  7 |      INDEX RANGE SCAN                  | I_T1_OBJECT_ID  |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |

—————————————————————————————————————————————————-

Query Block Name / Object Alias (identified by operation id):

————————————————————-

   1 – SET$2A13AF86   / “VW_ORE_BA8ECEFB”@”SEL$BA8ECEFB”

   2 – SET$2A13AF86

   3 – SET$2A13AF86_1

   4 – SET$2A13AF86_1 / “T1″@”SET$2A13AF86_1”

   5 – SET$2A13AF86_2

   6 – SET$2A13AF86_2 / “T1″@”SET$2A13AF86_2”

   7 – SET$2A13AF86_2 / “T1″@”SET$2A13AF86_2”

Outline Data

————-

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE(‘21.1.0’)

      DB_VERSION(‘21.1.0’)

      ALL_ROWS

      OUTLINE_LEAF(@”SET$2A13AF86_2″)

      OUTLINE_LEAF(@”SET$2A13AF86_1″)

      OUTLINE_LEAF(@”SET$2A13AF86″)

      OUTLINE_LEAF(@”SEL$9162BF3C”)

      OR_EXPAND(@”SEL$1″ (1) (2))

      ~~~~~~~~~~~~~~~~~~~~~~~~~~~

      OUTLINE(@”SEL$1″)

      NO_ACCESS(@”SEL$9162BF3C” “VW_ORE_BA8ECEFB”@”SEL$BA8ECEFB”)

      FULL(@”SET$2A13AF86_1″ “T1″@”SET$2A13AF86_1”)

      INDEX_RS_ASC(@”SET$2A13AF86_2″ “T1″@”SET$2A13AF86_2” (“T1″.”OBJECT_ID”))

      BATCH_TABLE_ACCESS_BY_ROWID(@”SET$2A13AF86_2″ “T1″@”SET$2A13AF86_2”)

      END_OUTLINE_DATA

  */

Peeked Binds (identified by position):

————————————–

   3 – (NUMBER, Primary=1)

   6 – (NUMBER, Primary=4)

   9 – (VARCHAR2(30), CSID=852, Primary=7)

Predicate Information (identified by operation id):

—————————————————

   3 – filter(:V_ID IS NULL)

   4 – filter(((:V_DID IS NULL OR “DATA_OBJECT_ID”=:V_DID) AND (:V_NAME IS NULL OR “OBJECT_NAME”=:V_NAME)))

   5 – filter(LNNVL(:V_ID IS NULL))

   6 – filter(((:V_DID IS NULL OR “DATA_OBJECT_ID”=:V_DID) AND (:V_NAME IS NULL OR “OBJECT_NAME”=:V_NAME)))

   7 – access(“OBJECT_ID”=:V_ID)

Hint Report (identified by operation id / Query Block Name / Object Alias):

Total hints for statement: 1

—————————————————————————

   5 –  SET$2A13AF86_2

           –  OR_EXPAND(@”SEL$1″ )

–//注意看下划线，OR_EXPAND(@”SEL$1″ (1) (2))。如果再次执行采用OR_EXPAND(@”SEL$1″ (1) (2))提示,执行计划还是选择全表扫描。

SCOTT@book01p> @ 10053x 5kftshxxs309c 0

/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3640_a5kftshxxs309c.trc

SCOTT@book01p> @ 10053y ”

TRCLINE

————————————————————————————————————————

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT “VW_ORE_BA8ECEFB”.”ITEM_1″ “OBJECT_ID”,”VW_ORE_BA8ECEFB”.”ITEM_2″ “OBJECT_NAME”,”VW_ORE_BA8ECEFB”.”ITEM_3″ “OBJEC

T_TYPE” FROM  ( (SELECT “T1″.”OBJECT_ID” “ITEM_1″,”T1″.”OBJECT_NAME” “ITEM_2″,”T1″.”OBJECT_TYPE” “ITEM_3” FROM “SCOTT”.”

T1″ “T1” WHERE :B1 IS NULL AND (:B2 IS NULL OR “T1”.”DATA_OBJECT_ID”=:B3) AND (:B4 IS NULL OR “T1”.”OBJECT_NAME”=:B5)) U

NION ALL  (SELECT “T1″.”OBJECT_ID” “ITEM_1″,”T1″.”OBJECT_NAME” “ITEM_2″,”T1″.”OBJECT_TYPE” “ITEM_3” FROM “SCOTT”.”T1″ “T

1″ WHERE “T1”.”OBJECT_ID”=:B6 AND (:B7 IS NULL OR “T1”.”DATA_OBJECT_ID”=:B8) AND (:B9 IS NULL OR “T1”.”OBJECT_NAME”=:B10

) AND LNNVL(:B11 IS NULL))) “VW_ORE_BA8ECEFB”

–//格式化如下：

SELECT “VW_ORE_BA8ECEFB”.”ITEM_1″ “OBJECT_ID”

      ,”VW_ORE_BA8ECEFB”.”ITEM_2″ “OBJECT_NAME”

      ,”VW_ORE_BA8ECEFB”.”ITEM_3″ “OBJECT_TYPE”

  FROM ( (SELECT “T1″.”OBJECT_ID” “ITEM_1”, “T1″.”OBJECT_NAME” “ITEM_2”, “T1″.”OBJECT_TYPE” “ITEM_3”

            FROM “SCOTT”.”T1″ “T1”

           WHERE     :B1 IS NULL

                 AND ( :B2 IS NULL OR “T1″.”DATA_OBJECT_ID” = :B3)

                 AND ( :B4 IS NULL OR “T1″.”OBJECT_NAME” = :B5))

        UNION ALL

        (SELECT “T1″.”OBJECT_ID” “ITEM_1”, “T1″.”OBJECT_NAME” “ITEM_2”, “T1″.”OBJECT_TYPE” “ITEM_3”

           FROM “SCOTT”.”T1″ “T1”

          WHERE     “T1″.”OBJECT_ID” = :B6

                AND ( :B7 IS NULL OR “T1″.”DATA_OBJECT_ID” = :B8)

                AND ( :B9 IS NULL OR “T1″.”OBJECT_NAME” = :B10)

                AND LNNVL ( :B11 IS NULL))) “VW_ORE_BA8ECEFB”

–//查看跟踪文件内容：                

ORE:  Predicate list

P1 : :B1 IS NULL

P2 : “T1”.”OBJECT_ID”=:B1

P3 : (:B1 IS NULL OR “T1”.”DATA_OBJECT_ID”=:B2) AND (:B3 IS NULL OR “T1”.”OBJECT_NAME”=:B4)

P4 : :B1 IS NULL OR “T1”.”OBJECT_NAME”=:B2

 DNF Matrix (Before sorting OR branches)

            P1  P2  P3  P4

CNJ (#1) :   1   0   1   1

CNJ (#2) :   0   1   1   1

ORE:  Predicate list

P1 : :B1 IS NULL

P2 : “T1”.”OBJECT_ID”=:B1

P3 : (:B1 IS NULL OR “T1”.”DATA_OBJECT_ID”=:B2) AND (:B3 IS NULL OR “T1”.”OBJECT_NAME”=:B4)

P4 : :B1 IS NULL OR “T1”.”OBJECT_NAME”=:B2

 DNF Matrix (After OR branch sorting)

            P1  P2  P3  P4

CNJ (#1) :   1   0   1   1

CNJ (#2) :   0   1   1   1

–//拆分确实不好理解为什么这样操作。

6.小结：

–//使用or_expand提示优化要做一些尝试，如果建立data_object_id索引，object_name索引，可能会发生执行计划的变化。

–//另外无法使用sql profile交换的方式控制执行计划，除非修改里面的提示OR_EXPAND(@”SEL$1″ )。

–//使用sql patch的方式情况也是类似，像前面的情况如果OR_EXPAND(@”SEL$1″ (1) (2) )打补丁，执行计划选择全表扫描。

–//如果建立如下索引：

create index i_t1_data_object_id on t1(data_object_id);

SCOTT@book01p> @ g1.txt ‘OR_EXPAND(@”SEL$1″  )’

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE

———- —————————— ——————————

     76191 DEPT                           TABLE

–//执行计划如下：

Plan hash value: 546662753

——————————————————————————————————————————————————–

| Id  | Operation                              | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |

——————————————————————————————————————————————————–

|   0 | SELECT STATEMENT                       |                     |      1 |        |       |   420 (100)|          |      1 |00:00:00.01 |       4 |

|   1 |  VIEW                                  | VW_ORE_BA8ECEFB     |      1 |   3512 |   315K|   420   (1)| 00:00:01 |      1 |00:00:00.01 |       4 |

|   2 |   UNION-ALL                            |                     |      1 |        |       |            |          |      1 |00:00:00.01 |       4 |

|*  3 |    FILTER                              |                     |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |

|*  4 |     TABLE ACCESS FULL                  | T1                  |      0 |   3509 |   174K|   414   (1)| 00:00:01 |      0 |00:00:00.01 |       0 |

|*  5 |    FILTER                              |                     |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |

|*  6 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1                  |      0 |      1 |    53 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |

|*  7 |      INDEX RANGE SCAN                  | I_T1_DATA_OBJECT_ID |      0 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |

|*  8 |    FILTER                              |                     |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |

|*  9 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1                  |      0 |      1 |    53 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |

|* 10 |      INDEX RANGE SCAN                  | I_T1_OBJECT_ID      |      0 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |

|* 11 |    FILTER                              |                     |      1 |        |       |            |          |      1 |00:00:00.01 |       4 |

|* 12 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1                  |      1 |      1 |    53 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |

|* 13 |      INDEX RANGE SCAN                  | I_T1_OBJECT_ID      |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |

——————————————————————————————————————————————————–

Query Block Name / Object Alias (identified by operation id):

————————————————————-

   1 – SET$171C43EC   / “VW_ORE_BA8ECEFB”@”SEL$BA8ECEFB”

   2 – SET$171C43EC

   3 – SET$171C43EC_1

   4 – SET$171C43EC_1 / “T1″@”SET$171C43EC_1”

   5 – SET$171C43EC_2

   6 – SET$171C43EC_2 / “T1″@”SET$171C43EC_2”

   7 – SET$171C43EC_2 / “T1″@”SET$171C43EC_2”

   8 – SET$171C43EC_3

   9 – SET$171C43EC_3 / “T1″@”SET$171C43EC_3”

  10 – SET$171C43EC_3 / “T1″@”SET$171C43EC_3”

  11 – SET$171C43EC_4

  12 – SET$171C43EC_4 / “T1″@”SET$171C43EC_4”

  13 – SET$171C43EC_4 / “T1″@”SET$171C43EC_4”

Outline Data

————-

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE(‘21.1.0’)

      DB_VERSION(‘21.1.0’)

      ALL_ROWS

      OUTLINE_LEAF(@”SET$171C43EC_4″)

      OUTLINE_LEAF(@”SET$171C43EC_3″)

      OUTLINE_LEAF(@”SET$171C43EC_2″)

      OUTLINE_LEAF(@”SET$171C43EC_1″)

      OUTLINE_LEAF(@”SET$171C43EC”)

      OUTLINE_LEAF(@”SEL$47D9A6EC”)

      OR_EXPAND(@”SEL$1″ (1) (2) (3) (4))

      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

      OUTLINE(@”SEL$1″)

      NO_ACCESS(@”SEL$47D9A6EC” “VW_ORE_BA8ECEFB”@”SEL$BA8ECEFB”)

      FULL(@”SET$171C43EC_1″ “T1″@”SET$171C43EC_1”)

      INDEX_RS_ASC(@”SET$171C43EC_2″ “T1″@”SET$171C43EC_2” (“T1″.”DATA_OBJECT_ID”))

      BATCH_TABLE_ACCESS_BY_ROWID(@”SET$171C43EC_2″ “T1″@”SET$171C43EC_2”)

      INDEX_RS_ASC(@”SET$171C43EC_3″ “T1″@”SET$171C43EC_3” (“T1″.”OBJECT_ID”))

      BATCH_TABLE_ACCESS_BY_ROWID(@”SET$171C43EC_3″ “T1″@”SET$171C43EC_3”)

      INDEX_RS_ASC(@”SET$171C43EC_4″ “T1″@”SET$171C43EC_4” (“T1″.”OBJECT_ID”))

      BATCH_TABLE_ACCESS_BY_ROWID(@”SET$171C43EC_4″ “T1″@”SET$171C43EC_4”)

      END_OUTLINE_DATA

  */

Peeked Binds (identified by position):

————————————–

   3 – (NUMBER, Primary=1)

   6 – (NUMBER, Primary=4)

   9 – (VARCHAR2(30), CSID=852, Primary=7)

Predicate Information (identified by operation id):

—————————————————

   3 – filter((:V_DID IS NULL AND :V_ID IS NULL))

   4 – filter((:V_NAME IS NULL OR “OBJECT_NAME”=:V_NAME))

   5 – filter((LNNVL(:V_DID IS NULL) AND :V_ID IS NULL))

   6 – filter((:V_NAME IS NULL OR “OBJECT_NAME”=:V_NAME))

   7 – access(“DATA_OBJECT_ID”=:V_DID)

   8 – filter((LNNVL(:V_ID IS NULL) AND :V_DID IS NULL))

   9 – filter((:V_NAME IS NULL OR “OBJECT_NAME”=:V_NAME))

  10 – access(“OBJECT_ID”=:V_ID)

  11 – filter((LNNVL(:V_DID IS NULL) AND LNNVL(:V_ID IS NULL)))

  12 – filter((“DATA_OBJECT_ID”=:V_DID AND (:V_NAME IS NULL OR “OBJECT_NAME”=:V_NAME)))

  13 – access(“OBJECT_ID”=:V_ID)

Hint Report (identified by operation id / Query Block Name / Object Alias):

Total hints for statement: 1

—————————————————————————

  11 –  SET$171C43EC_4

           –  OR_EXPAND(@”SEL$1″  )

–//而执行使用提示：

SCOTT@book01p> @ g1.txt ‘OR_EXPAND(@”SEL$1″ (1) (2) (3) (4) )’

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE

———- —————————— ——————————

     76191 DEPT                           TABLE

–//执行计划如下：

Plan hash value: 523270132

————————————————————————————————————————————————–

| Id |Operation                              | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |

————————————————————————————————————————————————–

|   0|SELECT STATEMENT                       |                 |      1 |        |       |   831 (100)|          |      1 |00:00:00.01 |       4 |

|   1| VIEW                                  | VW_ORE_BA8ECEFB |      1 |   3512 |   315K|   831   (1)| 00:00:01 |      1 |00:00:00.01 |       4 |

|   2|  UNION-ALL                            |                 |      1 |        |       |            |          |      1 |00:00:00.01 |       4 |

|*  3|   FILTER                              |                 |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |

|*  4|    TABLE ACCESS FULL                  | T1              |      0 |   3509 |   181K|   414   (1)| 00:00:01 |      0 |00:00:00.01 |       0 |

|*  5|   FILTER                              |                 |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |

|*  6|    TABLE ACCESS FULL                  | T1              |      0 |      1 |    53 |   414   (1)| 00:00:01 |      0 |00:00:00.01 |       0 |

|*  7|   FILTER                              |                 |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |

|*  8|    TABLE ACCESS BY INDEX ROWID BATCHED| T1              |      0 |      1 |    53 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |

|*  9|     INDEX RANGE SCAN                  | I_T1_OBJECT_ID  |      0 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |

|* 10|   FILTER                              |                 |      1 |        |       |            |          |      1 |00:00:00.01 |       4 |

|* 11|    TABLE ACCESS BY INDEX ROWID BATCHED| T1              |      1 |      1 |    53 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |

|* 12|     INDEX RANGE SCAN                  | I_T1_OBJECT_ID  |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |

————————————————————————————————————————————————–

Query Block Name / Object Alias (identified by operation id):

————————————————————-

   1 – SET$171C43EC   / “VW_ORE_BA8ECEFB”@”SEL$BA8ECEFB”

   2 – SET$171C43EC

   3 – SET$171C43EC_1

   4 – SET$171C43EC_1 / “T1″@”SET$171C43EC_1”

   5 – SET$171C43EC_2

   6 – SET$171C43EC_2 / “T1″@”SET$171C43EC_2”

   7 – SET$171C43EC_3

   8 – SET$171C43EC_3 / “T1″@”SET$171C43EC_3”

   9 – SET$171C43EC_3 / “T1″@”SET$171C43EC_3”

  10 – SET$171C43EC_4

  11 – SET$171C43EC_4 / “T1″@”SET$171C43EC_4”

  12 – SET$171C43EC_4 / “T1″@”SET$171C43EC_4”

Outline Data

————-

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE(‘21.1.0’)

      DB_VERSION(‘21.1.0’)

      ALL_ROWS

      OUTLINE_LEAF(@”SET$171C43EC_4″)

      OUTLINE_LEAF(@”SET$171C43EC_3″)

      OUTLINE_LEAF(@”SET$171C43EC_2″)

      OUTLINE_LEAF(@”SET$171C43EC_1″)

      OUTLINE_LEAF(@”SET$171C43EC”)

      OUTLINE_LEAF(@”SEL$47D9A6EC”)

      OR_EXPAND(@”SEL$1″ (1) (2) (3) (4))

      OUTLINE(@”SEL$1″)

      NO_ACCESS(@”SEL$47D9A6EC” “VW_ORE_BA8ECEFB”@”SEL$BA8ECEFB”)

      FULL(@”SET$171C43EC_1″ “T1″@”SET$171C43EC_1”)

      FULL(@”SET$171C43EC_2″ “T1″@”SET$171C43EC_2”)

      INDEX_RS_ASC(@”SET$171C43EC_3″ “T1″@”SET$171C43EC_3” (“T1″.”OBJECT_ID”))

      BATCH_TABLE_ACCESS_BY_ROWID(@”SET$171C43EC_3″ “T1″@”SET$171C43EC_3”)

      INDEX_RS_ASC(@”SET$171C43EC_4″ “T1″@”SET$171C43EC_4” (“T1″.”OBJECT_ID”))

      BATCH_TABLE_ACCESS_BY_ROWID(@”SET$171C43EC_4″ “T1″@”SET$171C43EC_4”)

      END_OUTLINE_DATA

  */

Peeked Binds (identified by position):

————————————–

   3 – (NUMBER, Primary=1)

   6 – (NUMBER, Primary=4)

   9 – (VARCHAR2(30), CSID=852, Primary=7)

Predicate Information (identified by operation id):

—————————————————

   3 – filter((:V_NAME IS NULL AND :V_ID IS NULL))

   4 – filter((:V_DID IS NULL OR “DATA_OBJECT_ID”=:V_DID))

   5 – filter((LNNVL(:V_NAME IS NULL) AND :V_ID IS NULL))

   6 – filter((“OBJECT_NAME”=:V_NAME AND (:V_DID IS NULL OR “DATA_OBJECT_ID”=:V_DID)))

   7 – filter((LNNVL(:V_ID IS NULL) AND :V_NAME IS NULL))

   8 – filter((:V_DID IS NULL OR “DATA_OBJECT_ID”=:V_DID))

   9 – access(“OBJECT_ID”=:V_ID)

  10 – filter((LNNVL(:V_NAME IS NULL) AND LNNVL(:V_ID IS NULL)))

  11 – filter((“OBJECT_NAME”=:V_NAME AND (:V_DID IS NULL OR “DATA_OBJECT_ID”=:V_DID)))

  12 – access(“OBJECT_ID”=:V_ID)

Hint Report (identified by operation id / Query Block Name / Object Alias):

Total hints for statement: 1

—————————————————————————

  10 –  SET$171C43EC_4

           –  OR_EXPAND(@”SEL$1″ (1) (2) (3) (4) )

–//前后对比，可以发现实际上2个执行计划完全不同，前面会使用I_T1_DATA_OBJECT_ID索引，而后者根本不会。

–//虽然outline记录的提示都是OR_EXPAND(@”SEL$1″ (1) (2) (3) (4) )。

–//有点无法理解的是这样情况下使用OR_EXPAND(@”SEL$1″ (1) (2) (3) (4) (5) (6) (7) (8) )提示，执行计划又选择全表扫描。

–//查看10053跟踪文件：

ORE:  Predicate list

P1 : :B1 IS NULL

P2 : “T1”.”OBJECT_ID”=:B1

P3 : (:B1 IS NULL OR “T1”.”DATA_OBJECT_ID”=:B2) AND (:B3 IS NULL OR “T1”.”OBJECT_NAME”=:B4)

P4 : :B1 IS NULL

P5 : “T1”.”OBJECT_NAME”=:B1

 DNF Matrix (After OR branch sorting)

            P1  P2  P3  P4  P5

CNJ (#1) :   1   0   1   1   0

CNJ (#2) :   1   0   1   0   1

CNJ (#3) :   0   1   1   1   0

CNJ (#4) :   0   1   1   0   1

–//总之，在优化使用提示or_expand提示时注意这些细节，即使使用sql patch也可能在不小心导致执行计划发生变化，无法很好的控制

–//执行计划。

–//最后建议开发还是少使用这些所谓的技巧，少写这类风格的sql语句，在我看来这类sql语句根本不允许出现在生产系统中。

–//你可以再尝试建立索引：

–//create index i_t1_object_name on t1(object_name);

SCOTT@book01p> @ g1.txt ‘OR_EXPAND(@”SEL$1″ )’

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE

———- —————————— ——————————

     76191 DEPT                           TABLE

Plan hash value: 2061367055

—————————————————————————————————————————————-

| Id |Operation                              |Name               |Starts|E-Rows|E-Bytes|Cost (%CPU)|E-Time  |A-Rows|   A-Time  |Buffers|

—————————————————————————————————————————————-

|   0|SELECT STATEMENT                       |                   |     1|      |       |  430 (100)|        |     1|00:00:00.01|      4|

|   1| VIEW                                  |VW_ORE_BA8ECEFB    |     1| 70174|  6304K|  430   (1)|00:00:01|     1|00:00:00.01|      4|

|   2|  UNION-ALL                            |                   |     1|      |       |           |        |     1|00:00:00.01|      4|

|*  3|   FILTER                              |                   |     1|      |       |           |        |     0|00:00:00.01|      0|

|   4|    TABLE ACCESS FULL                  |T1                 |     0| 70167|  3494K|  414   (1)|00:00:01|     0|00:00:00.01|      0|

|*  5|   FILTER                              |                   |     1|      |       |           |        |     0|00:00:00.01|      0|

|   6|    TABLE ACCESS BY INDEX ROWID BATCHED|T1                 |     0|     1|    51 |    4   (0)|00:00:01|     0|00:00:00.01|      0|

|*  7|     INDEX RANGE SCAN                  |I_T1_OBJECT_NAME   |     0|     1|       |    3   (0)|00:00:01|     0|00:00:00.01|      0|

|*  8|   FILTER                              |                   |     1|      |       |           |        |     0|00:00:00.01|      0|

|   9|    TABLE ACCESS BY INDEX ROWID BATCHED|T1                 |     0|     1|    53 |    2   (0)|00:00:01|     0|00:00:00.01|      0|

|* 10|     INDEX RANGE SCAN                  |I_T1_DATA_OBJECT_ID|     0|     1|       |    1   (0)|00:00:01|     0|00:00:00.01|      0|

|* 11|   FILTER                              |                   |     1|      |       |           |        |     0|00:00:00.01|      0|

|* 12|    TABLE ACCESS BY INDEX ROWID BATCHED|T1                 |     0|     1|    53 |    2   (0)|00:00:01|     0|00:00:00.01|      0|

|* 13|     INDEX RANGE SCAN                  |I_T1_DATA_OBJECT_ID|     0|     1|       |    1   (0)|00:00:01|     0|00:00:00.01|      0|

|* 14|   FILTER                              |                   |     1|      |       |           |        |     0|00:00:00.01|      0|

|  15|    TABLE ACCESS BY INDEX ROWID BATCHED|T1                 |     0|     1|    53 |    2   (0)|00:00:01|     0|00:00:00.01|      0|

|* 16|     INDEX RANGE SCAN                  |I_T1_OBJECT_ID     |     0|     1|       |    1   (0)|00:00:01|     0|00:00:00.01|      0|

|* 17|   FILTER                              |                   |     1|      |       |           |        |     0|00:00:00.01|      0|

|* 18|    TABLE ACCESS BY INDEX ROWID BATCHED|T1                 |     0|     1|    53 |    2   (0)|00:00:01|     0|00:00:00.01|      0|

|* 19|     INDEX RANGE SCAN                  |I_T1_OBJECT_ID     |     0|     1|       |    1   (0)|00:00:01|     0|00:00:00.01|      0|

|* 20|   FILTER                              |                   |     1|      |       |           |        |     0|00:00:00.01|      0|

|* 21|    TABLE ACCESS BY INDEX ROWID BATCHED|T1                 |     0|     1|    53 |    2   (0)|00:00:01|     0|00:00:00.01|      0|

|* 22|     INDEX RANGE SCAN                  |I_T1_OBJECT_ID     |     0|     1|       |    1   (0)|00:00:01|     0|00:00:00.01|      0|

|* 23|   FILTER                              |                   |     1|      |       |           |        |     1|00:00:00.01|      4|

|* 24|    TABLE ACCESS BY INDEX ROWID BATCHED|T1                 |     1|     1|    53 |    2   (0)|00:00:01|     1|00:00:00.01|      4|

|* 25|     INDEX RANGE SCAN                  |I_T1_OBJECT_ID     |     1|     1|       |    1   (0)|00:00:01|     1|00:00:00.01|      3|

—————————————————————————————————————————————-

Query Block Name / Object Alias (identified by operation id):

————————————————————-

   1 – SET$B2C621EA   / “VW_ORE_BA8ECEFB”@”SEL$BA8ECEFB”

   2 – SET$B2C621EA

   3 – SET$B2C621EA_1

   4 – SET$B2C621EA_1 / “T1″@”SET$B2C621EA_1”

   5 – SET$B2C621EA_2

   6 – SET$B2C621EA_2 / “T1″@”SET$B2C621EA_2”

   7 – SET$B2C621EA_2 / “T1″@”SET$B2C621EA_2”

   8 – SET$B2C621EA_3

   9 – SET$B2C621EA_3 / “T1″@”SET$B2C621EA_3”

  10 – SET$B2C621EA_3 / “T1″@”SET$B2C621EA_3”

  11 – SET$B2C621EA_4

  12 – SET$B2C621EA_4 / “T1″@”SET$B2C621EA_4”

  13 – SET$B2C621EA_4 / “T1″@”SET$B2C621EA_4”

  14 – SET$B2C621EA_5

  15 – SET$B2C621EA_5 / “T1″@”SET$B2C621EA_5”

  16 – SET$B2C621EA_5 / “T1″@”SET$B2C621EA_5”

  17 – SET$B2C621EA_6

  18 – SET$B2C621EA_6 / “T1″@”SET$B2C621EA_6”

  19 – SET$B2C621EA_6 / “T1″@”SET$B2C621EA_6”

  20 – SET$B2C621EA_7

  21 – SET$B2C621EA_7 / “T1″@”SET$B2C621EA_7”

  22 – SET$B2C621EA_7 / “T1″@”SET$B2C621EA_7”

  23 – SET$B2C621EA_8

  24 – SET$B2C621EA_8 / “T1″@”SET$B2C621EA_8”

  25 – SET$B2C621EA_8 / “T1″@”SET$B2C621EA_8”

Outline Data

————-

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE(‘21.1.0’)

      DB_VERSION(‘21.1.0’)

      ALL_ROWS

      OUTLINE_LEAF(@”SET$B2C621EA_8″)

      OUTLINE_LEAF(@”SET$B2C621EA_7″)

      OUTLINE_LEAF(@”SET$B2C621EA_6″)

      OUTLINE_LEAF(@”SET$B2C621EA_5″)

      OUTLINE_LEAF(@”SET$B2C621EA_4″)

      OUTLINE_LEAF(@”SET$B2C621EA_3″)

      OUTLINE_LEAF(@”SET$B2C621EA_2″)

      OUTLINE_LEAF(@”SET$B2C621EA_1″)

      OUTLINE_LEAF(@”SET$B2C621EA”)

      OUTLINE_LEAF(@”SEL$DC109C8D”)

      OR_EXPAND(@”SEL$1″ (1) (2) (3) (4) (5) (6) (7) (8))

      OUTLINE(@”SEL$1″)

      NO_ACCESS(@”SEL$DC109C8D” “VW_ORE_BA8ECEFB”@”SEL$BA8ECEFB”)

      FULL(@”SET$B2C621EA_1″ “T1″@”SET$B2C621EA_1”)

      INDEX_RS_ASC(@”SET$B2C621EA_2″ “T1″@”SET$B2C621EA_2” (“T1″.”OBJECT_NAME”))

      BATCH_TABLE_ACCESS_BY_ROWID(@”SET$B2C621EA_2″ “T1″@”SET$B2C621EA_2”)

      INDEX_RS_ASC(@”SET$B2C621EA_3″ “T1″@”SET$B2C621EA_3” (“T1″.”DATA_OBJECT_ID”))

      BATCH_TABLE_ACCESS_BY_ROWID(@”SET$B2C621EA_3″ “T1″@”SET$B2C621EA_3”)

      INDEX_RS_ASC(@”SET$B2C621EA_4″ “T1″@”SET$B2C621EA_4” (“T1″.”DATA_OBJECT_ID”))

      BATCH_TABLE_ACCESS_BY_ROWID(@”SET$B2C621EA_4″ “T1″@”SET$B2C621EA_4”)

      INDEX_RS_ASC(@”SET$B2C621EA_5″ “T1″@”SET$B2C621EA_5” (“T1″.”OBJECT_ID”))

      BATCH_TABLE_ACCESS_BY_ROWID(@”SET$B2C621EA_5″ “T1″@”SET$B2C621EA_5”)

      INDEX_RS_ASC(@”SET$B2C621EA_6″ “T1″@”SET$B2C621EA_6” (“T1″.”OBJECT_ID”))

      BATCH_TABLE_ACCESS_BY_ROWID(@”SET$B2C621EA_6″ “T1″@”SET$B2C621EA_6”)

      INDEX_RS_ASC(@”SET$B2C621EA_7″ “T1″@”SET$B2C621EA_7” (“T1″.”OBJECT_ID”))

      BATCH_TABLE_ACCESS_BY_ROWID(@”SET$B2C621EA_7″ “T1″@”SET$B2C621EA_7”)

      INDEX_RS_ASC(@”SET$B2C621EA_8″ “T1″@”SET$B2C621EA_8” (“T1″.”OBJECT_ID”))

      BATCH_TABLE_ACCESS_BY_ROWID(@”SET$B2C621EA_8″ “T1″@”SET$B2C621EA_8”)

      END_OUTLINE_DATA

  */

Peeked Binds (identified by position):

————————————–

   3 – (NUMBER, Primary=1)

   6 – (NUMBER, Primary=4)

   9 – (VARCHAR2(30), CSID=852, Primary=7)

Peeked Binds (identified by position):

————————————–

   3 – (NUMBER, Primary=1)

   6 – (NUMBER, Primary=4)

   9 – (VARCHAR2(30), CSID=852, Primary=7)

Predicate Information (identified by operation id):

—————————————————

   3 – filter((:V_NAME IS NULL AND :V_DID IS NULL AND :V_ID IS NULL))

   5 – filter((LNNVL(:V_NAME IS NULL) AND :V_DID IS NULL AND :V_ID IS NULL))

   7 – access(“OBJECT_NAME”=:V_NAME)

   8 – filter((LNNVL(:V_DID IS NULL) AND :V_NAME IS NULL AND :V_ID IS NULL))

  10 – access(“DATA_OBJECT_ID”=:V_DID)

  11 – filter((LNNVL(:V_NAME IS NULL) AND LNNVL(:V_DID IS NULL) AND :V_ID IS NULL))

  12 – filter(“OBJECT_NAME”=:V_NAME)

  13 – access(“DATA_OBJECT_ID”=:V_DID)

  14 – filter((LNNVL(:V_ID IS NULL) AND :V_NAME IS NULL AND :V_DID IS NULL))

  16 – access(“OBJECT_ID”=:V_ID)

  17 – filter((LNNVL(:V_NAME IS NULL) AND LNNVL(:V_ID IS NULL) AND :V_DID IS NULL))

  18 – filter(“OBJECT_NAME”=:V_NAME)

  19 – access(“OBJECT_ID”=:V_ID)

  20 – filter((LNNVL(:V_DID IS NULL) AND LNNVL(:V_ID IS NULL) AND :V_NAME IS NULL))

  21 – filter(“DATA_OBJECT_ID”=:V_DID)

  22 – access(“OBJECT_ID”=:V_ID)

  23 – filter((LNNVL(:V_NAME IS NULL) AND LNNVL(:V_DID IS NULL) AND LNNVL(:V_ID IS NULL)))

  24 – filter((“OBJECT_NAME”=:V_NAME AND “DATA_OBJECT_ID”=:V_DID))

  25 – access(“OBJECT_ID”=:V_ID)

Hint Report (identified by operation id / Query Block Name / Object Alias):

Total hints for statement: 1

—————————————————————————

  23 –  SET$B2C621EA_8

           –  OR_EXPAND(@”SEL$1″ )

–//直接使用OR_EXPAND(@”SEL$1″ (1) (2) (3) (4) (5) (6) (7) (8))，执行计划变成全表扫描。

–//不再展开分析。

7.附上测试使用的脚本：

$ cat 10053x.sql

set term off

execute dbms_sqldiag.dump_trace(p_sql_id=>’&1′,p_child_number=>&2,p_component=>’Compiler’,p_file_id=>’a’||’&&1′);

set term on

set head off

@ t

set head on

define 1=&trc

$ cat tpt-oracle-master/t.sql

SELECT value tracefile FROM v$diag_info WHERE name = ‘Default Trace File’;

$ cat 10053y.sql

— Copyright 2023 lfree. All rights reserved.

— Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.

————————————————————————————————————

—

— File name:   10053y.sql

— Purpose:     display Final query after transformations

—

— Author:      lfree

—

— Usage:

—     @ 10053y <trc_file>

—

———————————————————————————————————

SET TERM OFF

COLUMN trc_file  NEW_VALUE v_trc_file

–DEFINE trc_file = &1

SELECT NVL(‘&1′,’&TRC’) trc_file FROM DUAL ;

SELECT SUBSTR (‘&v_trc_file’, INSTR (‘&v_trc_file’, ‘/’, -1) + 1) trc_file FROM DUAL;

SET TERM ON

DEFINE trc_file = &v_trc_file

–DEFINE trc_file = &1

–COL trace_filename FOR A45

–COL adr_home FOR A45

–SELECT trace_filename, to_char(change_time, ‘dd-mm-yyyy hh24:mi:ss’) AS change_time, to_char(modify_time, ‘dd-mm-yyyy hh24:mi:ss’) AS modify_time, adr_home, con_id

–FROM gv$diag_trace_file

–WHERE lower(trace_filename) LIKE lower(‘%&v_trc_file%’)

–ORDER BY modify_time;

column trcline format a120

SELECT trcline

  FROM gv$diag_trace_file_contents

          MATCH_RECOGNIZE

          (

             PARTITION BY trace_filename

             ORDER BY line_number

             MEASURES payload AS trcline

             ALL ROWS PER MATCH

             PATTERN (a | b nc * | c | f n)

             DEFINE a AS (payload LIKE ‘qksptfSQM_GetTxt(): Anonymous Block%’)

            ,b AS (payload LIKE ‘qksptfSQM_GetTxt(): Macro Text%’)

            ,nc AS (payload NOT LIKE ‘qksptfSQM_Template(): Template Text%’)

            ,c AS (payload LIKE ‘qksptfSQM_Template(): Template Text%’)

            ,f AS (payload LIKE ‘Final query after%’)

          )

          x

 WHERE trace_filename = ‘&v_trc_file’;