登录/ 注册
 找回密码
 立即注册
关闭

CSDN热搜

程序园 精品问答 技术交流 资源下载
返回列表 发新帖
首页 › 业界区 › 安全 ›[20250714]使用or_expand提示遇到的疑惑.txt

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

奄蜊 2025-7-18 21:21:09
[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
--
---------------------------------------------------------------------------------------------------------
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';


来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

使用道具 举报
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册
发帖
奄蜊
2025-7-18 21:21:09

0

粉丝关注

17

主题发布

板块介绍填写区域，请于后台编辑
微信扫一扫