[20250714]使用or_expand提示遇到的疑惑.txt
[20250714]使用or_expand提示遇到的疑惑.txt
--//生產(chǎn)系統(tǒng)遇到的問題，使用or_expand提示，通過例子說明問題。
1.環(huán)境：
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.建立測試環(huán)境：
--//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);
--//分析表略。
--//當(dāng)前僅僅建立object_id字段索引。
--//建立測試執(zhí)行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)
;
--//注：主要模擬開發(fā)的寫法，另外注意查詢條件有索引的條件寫在中間。
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
--//執(zhí)行計(jì)劃如下，在沒有任何提示的情況下，執(zhí)行計(jì)劃選擇全表掃描。
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
--//執(zhí)行計(jì)劃如下：
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")
--//注意實(shí)際上outline里面記錄的是OR_EXPAND(@"SEL$1" (1) (2))。
--//但是當(dāng)我加入提示OR_EXPAND(@"SEL$1" (1) (2))時會出現(xiàn)什么情況呢。
SCOTT@book01p> @ g1.txt 'OR_EXPAND(@"SEL$1" (1) (2))'
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- ------------------------------ ------------------------------
76191 DEPT TABLE
--//執(zhí)行計(jì)劃如下：
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))
--//執(zhí)行計(jì)劃反而時選擇全表掃描。
--//可以抽取前面的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)
;
--//執(zhí)行發(fā)現(xiàn)執(zhí)行計(jì)劃還是選擇全表掃描，結(jié)果不再貼出。
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"
--//查看跟蹤文件內(nèi)容：
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種情況，對應(yīng)中間條件( ( :v_id = '' OR :v_id IS NULL) OR object_id = :v_id)。
--//實(shí)際上oracle將條件改寫為(:v_id IS NULL OR object_id = :v_id).
--//注意不要被里面:b1,:b2之類綁定變量名字迷惑，非常容易誤解。oracle重新命名的綁定變量名字，每行都是從B1開始。
--//另外oracle在上面的情況拆分存在問題，我的理解應(yīng)該這樣拆分。
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)
--//轉(zhuǎn)換后注解部分丟失OR_EXPAND。里面的綁定變量被替換為:Bn之類的變量名，注意這里轉(zhuǎn)換后，在執(zhí)行計(jì)劃的Peeked Binds
--//(identified by position):部分看不見實(shí)際的綁定變量值。可以查看前面的執(zhí)行計(jì)劃。
--//查看跟蹤文件內(nèi)容：
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) )有效。執(zhí)行計(jì)劃不再貼出。
--//oracle在處理這類or的優(yōu)化時存在一些問題，后面的參數(shù)僅僅表示拆分的數(shù)量。
5.繼續(xù)：
--//如果寫成如下：
$ 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
--//執(zhí)行計(jì)劃如下：
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))。如果再次執(zhí)行采用OR_EXPAND(@"SEL$1" (1) (2))提示,執(zhí)行計(jì)劃還是選擇全表掃描。
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"
--//查看跟蹤文件內(nèi)容：
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
--//拆分確實(shí)不好理解為什么這樣操作。
6.小結(jié)：
--//使用or_expand提示優(yōu)化要做一些嘗試，如果建立data_object_id索引，object_name索引，可能會發(fā)生執(zhí)行計(jì)劃的變化。
--//另外無法使用sql profile交換的方式控制執(zhí)行計(jì)劃，除非修改里面的提示OR_EXPAND(@"SEL$1" )。
--//使用sql patch的方式情況也是類似，像前面的情況如果OR_EXPAND(@"SEL$1" (1) (2) )打補(bǔ)丁，執(zhí)行計(jì)劃選擇全表掃描。
--//如果建立如下索引：
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
--//執(zhí)行計(jì)劃如下：
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" )
--//而執(zhí)行使用提示：
SCOTT@book01p> @ g1.txt 'OR_EXPAND(@"SEL$1" (1) (2) (3) (4) )'
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- ------------------------------ ------------------------------
76191 DEPT TABLE
--//執(zhí)行計(jì)劃如下：
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) )
--//前后對比，可以發(fā)現(xiàn)實(shí)際上2個執(zhí)行計(jì)劃完全不同，前面會使用I_T1_DATA_OBJECT_ID索引，而后者根本不會。
--//雖然outline記錄的提示都是OR_EXPAND(@"SEL$1" (1) (2) (3) (4) )。
--//有點(diǎn)無法理解的是這樣情況下使用OR_EXPAND(@"SEL$1" (1) (2) (3) (4) (5) (6) (7) (8) )提示，執(zhí)行計(jì)劃又選擇全表掃描。
--//查看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
--//總之，在優(yōu)化使用提示or_expand提示時注意這些細(xì)節(jié)，即使使用sql patch也可能在不小心導(dǎo)致執(zhí)行計(jì)劃發(fā)生變化，無法很好的控制
--//執(zhí)行計(jì)劃。
--//最后建議開發(fā)還是少使用這些所謂的技巧，少寫這類風(fēng)格的sql語句，在我看來這類sql語句根本不允許出現(xiàn)在生產(chǎn)系統(tǒng)中。
--//你可以再嘗試建立索引：
--//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))，執(zhí)行計(jì)劃變成全表掃描。
--//不再展開分析。
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';
--//生產(chǎn)系統(tǒng)遇到的問題，使用or_expand提示，通過例子說明問題。
1.環(huán)境：
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.建立測試環(huán)境：
--//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);
--//分析表略。
--//當(dāng)前僅僅建立object_id字段索引。
--//建立測試執(zhí)行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)
;
--//注：主要模擬開發(fā)的寫法，另外注意查詢條件有索引的條件寫在中間。
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
--//執(zhí)行計(jì)劃如下，在沒有任何提示的情況下，執(zhí)行計(jì)劃選擇全表掃描。
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
--//執(zhí)行計(jì)劃如下：
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")
--//注意實(shí)際上outline里面記錄的是OR_EXPAND(@"SEL$1" (1) (2))。
--//但是當(dāng)我加入提示OR_EXPAND(@"SEL$1" (1) (2))時會出現(xiàn)什么情況呢。
SCOTT@book01p> @ g1.txt 'OR_EXPAND(@"SEL$1" (1) (2))'
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- ------------------------------ ------------------------------
76191 DEPT TABLE
--//執(zhí)行計(jì)劃如下：
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))
--//執(zhí)行計(jì)劃反而時選擇全表掃描。
--//可以抽取前面的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)
;
--//執(zhí)行發(fā)現(xiàn)執(zhí)行計(jì)劃還是選擇全表掃描，結(jié)果不再貼出。
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"
--//查看跟蹤文件內(nèi)容：
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種情況，對應(yīng)中間條件( ( :v_id = '' OR :v_id IS NULL) OR object_id = :v_id)。
--//實(shí)際上oracle將條件改寫為(:v_id IS NULL OR object_id = :v_id).
--//注意不要被里面:b1,:b2之類綁定變量名字迷惑，非常容易誤解。oracle重新命名的綁定變量名字，每行都是從B1開始。
--//另外oracle在上面的情況拆分存在問題，我的理解應(yīng)該這樣拆分。
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)
--//轉(zhuǎn)換后注解部分丟失OR_EXPAND。里面的綁定變量被替換為:Bn之類的變量名，注意這里轉(zhuǎn)換后，在執(zhí)行計(jì)劃的Peeked Binds
--//(identified by position):部分看不見實(shí)際的綁定變量值。可以查看前面的執(zhí)行計(jì)劃。
--//查看跟蹤文件內(nèi)容：
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) )有效。執(zhí)行計(jì)劃不再貼出。
--//oracle在處理這類or的優(yōu)化時存在一些問題，后面的參數(shù)僅僅表示拆分的數(shù)量。
5.繼續(xù)：
--//如果寫成如下：
$ 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
--//執(zhí)行計(jì)劃如下：
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))。如果再次執(zhí)行采用OR_EXPAND(@"SEL$1" (1) (2))提示,執(zhí)行計(jì)劃還是選擇全表掃描。
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"
--//查看跟蹤文件內(nèi)容：
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
--//拆分確實(shí)不好理解為什么這樣操作。
6.小結(jié)：
--//使用or_expand提示優(yōu)化要做一些嘗試，如果建立data_object_id索引，object_name索引，可能會發(fā)生執(zhí)行計(jì)劃的變化。
--//另外無法使用sql profile交換的方式控制執(zhí)行計(jì)劃，除非修改里面的提示OR_EXPAND(@"SEL$1" )。
--//使用sql patch的方式情況也是類似，像前面的情況如果OR_EXPAND(@"SEL$1" (1) (2) )打補(bǔ)丁，執(zhí)行計(jì)劃選擇全表掃描。
--//如果建立如下索引：
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
--//執(zhí)行計(jì)劃如下：
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" )
--//而執(zhí)行使用提示：
SCOTT@book01p> @ g1.txt 'OR_EXPAND(@"SEL$1" (1) (2) (3) (4) )'
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- ------------------------------ ------------------------------
76191 DEPT TABLE
--//執(zhí)行計(jì)劃如下：
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) )
--//前后對比，可以發(fā)現(xiàn)實(shí)際上2個執(zhí)行計(jì)劃完全不同，前面會使用I_T1_DATA_OBJECT_ID索引，而后者根本不會。
--//雖然outline記錄的提示都是OR_EXPAND(@"SEL$1" (1) (2) (3) (4) )。
--//有點(diǎn)無法理解的是這樣情況下使用OR_EXPAND(@"SEL$1" (1) (2) (3) (4) (5) (6) (7) (8) )提示，執(zhí)行計(jì)劃又選擇全表掃描。
--//查看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
--//總之，在優(yōu)化使用提示or_expand提示時注意這些細(xì)節(jié)，即使使用sql patch也可能在不小心導(dǎo)致執(zhí)行計(jì)劃發(fā)生變化，無法很好的控制
--//執(zhí)行計(jì)劃。
--//最后建議開發(fā)還是少使用這些所謂的技巧，少寫這類風(fēng)格的sql語句，在我看來這類sql語句根本不允許出現(xiàn)在生產(chǎn)系統(tǒng)中。
--//你可以再嘗試建立索引：
--//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))，執(zhí)行計(jì)劃變成全表掃描。
--//不再展開分析。
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';
浙公網(wǎng)安備 33010602011771號