[20250619]21c使用or_expand提示.txt
[20250619]21c使用or_expand提示.txt
--//生产系统遇到的问题，我以为以前在21c下测试通过的方法在19c上可以通过，结果不行，有必要重复在21c重复测试看看。
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 ;
--//drop table t2 purge ;
create table t1 as select * from all_objects;
create table t2 as select * from all_objects;
create index i_t1_object_id on t1(object_id);
create index i_t2_object_id on t2(object_id);
create index i_t1_object_name on t1(object_name);
create index i_t2_object_name on t2(object_name);
create index i_t1_CREATED on t1(CREATED );
create index i_t2_CREATED on t2(CREATED );
--//分析表略。
$ cat f4.txt
set term off
variable v_id number ;
variable v_id1 number ;
variable v_name varchar2(20) ;
variable startdate varchar2(32) ;
variable enddate varchar2(32) ;
exec :v_id := 76191;
--exec :v_name := NULL;
exec :startdate := '2024-08-16 00:00:00'
exec :enddate := '2024-08-17 00:00:00'
set term on
SELECT /*+ &&1 */
t1.object_id
,t1.object_name
,t1.object_type
,t2.object_id
,t2.object_name
,t2.object_type
FROM t1 LEFT JOIN t2 ON t1.object_id = t2.object_id
WHERE t1.created >= SYSDATE - 360
AND ( ( :v_id = '' OR :v_id IS NULL) OR t2.object_id = :v_id)
AND ( ( :StartDate = '' OR :StartDate IS NULL) OR t2.CREATED >= :StartDate)
AND ( ( :EndDate = '' OR :EndDate IS NULL) OR t2.CREATED <= :EndDate);
--//做一个接近生产系统的例子,真实的生产系统语句基本类似(只不过查询SYSDATE - 7),注意出现LEFT JOIN,谓词条件在表T2上,使用
--//use_concat提示根本不行。
--//另外实际上开发有可能写错，如果:v_id，:StartDate，:EndDate任何一个非空，LEFT JOIN消失，换一句话将开发可能写错sql语句。
--//实际的情况如果:v_id，:StartDate，:EndDate都是null，在实际生产系统查询没有任何意义，返回一大堆记录。
--//当然还是1个可能开发( ( :v_id = '' OR :v_id IS NULL) OR t2.object_id = :v_id)应该写成
--//( ( :v_id = '' OR :v_id IS NULL) OR t1.object_id = :v_id).
3.测试：
SCOTT@book01p> @ sl all
alter session set statistics_level = all;
Session altered.
--//如果没有提示:
SCOTT@book01p> @ f4.txt ''
OBJECT_ID OBJECT_NAME OBJECT_TYPE OBJECT_ID OBJECT_NAME OBJECT_TYPE
--------- ----------- ----------- ---------- ----------- -----------
76191 DEPT TABLE 76191 DEPT TABLE
--//执行计划如下：
Plan hash value: 1764467842
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1121 (100)| | 1 |00:00:00.06 | 2959 | | | |
|* 1 | FILTER | | 1 | | | | | | 1 |00:00:00.06 | 2959 | | | |
|* 2 | HASH JOIN OUTER | | 1 | 17591 | 2027K| 1224K| 1121 (1)| 00:00:01 | 566 |00:00:00.06 | 2959 | 1123K| 1123K| 2736K (0)|
|* 3 | TABLE ACCESS FULL| T1 | 1 | 17591 | 1013K| | 415 (2)| 00:00:01 | 566 |00:00:00.01 | 1479 | | | |
| 4 | TABLE ACCESS FULL| T2 | 1 | 69913 | 4028K| | 412 (1)| 00:00:01 | 69913 |00:00:00.02 | 1480 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2BFA4EE4
3 - SEL$2BFA4EE4 / "T1"@"SEL$1"
4 - SEL$2BFA4EE4 / "T2"@"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$2BFA4EE4")
MERGE(@"SEL$8812AA4E" >"SEL$948754D7")
OUTLINE(@"SEL$948754D7")
ANSI_REARCH(@"SEL$2")
OUTLINE(@"SEL$8812AA4E")
ANSI_REARCH(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
FULL(@"SEL$2BFA4EE4" "T1"@"SEL$1")
FULL(@"SEL$2BFA4EE4" "T2"@"SEL$1")
LEADING(@"SEL$2BFA4EE4" "T1"@"SEL$1" "T2"@"SEL$1")
USE_HASH(@"SEL$2BFA4EE4" "T2"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
3 - (NUMBER, Primary=1)
6 - (VARCHAR2(30), CSID=852, Primary=4)
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(((:V_ID IS NULL OR "T2"."OBJECT_ID"=:V_ID) AND (:STARTDATE IS NULL OR "T2"."CREATED">=:STARTDATE) AND (:ENDDATE IS NULL OR
"T2"."CREATED"<=:ENDDATE)))
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
3 - filter("T1"."CREATED">=SYSDATE@!-360)
67 rows selected.
--//正常没有提示的情况，根本不会使用索引，建立开发真心不要再写这类似的sql语句，我们开发真心感谢exadata或者现在的硬件性能。
--//提交问题，开发仅仅加入类似条件t1.created >= SYSDATE - 7，认为问题解决了，实际上开发自己到一定时间就发现写的语句执行
--//缓慢，加入一个条件t1.created >= SYSDATE - 7使执行计划走日期索引，7天的数据量实际上逻辑读依旧很高，根本不是解决该问题
--//的根本方法。
SCOTT@book01p> @ f4.txt ' or_expand(@"SEL$2BFA4EE4")'
OBJECT_ID OBJECT_NAME OBJECT_TYPE OBJECT_ID OBJECT_NAME OBJECT_TYPE
--------- ----------- ----------- ---------- ----------- -----------
76191 DEPT TABLE 76191 DEPT TABLE
--//注：提示里面的信息是Query Block Name，可以从前面的Query Block Name / Object Alias (identified by operation id)。
--//查看执行计划确实可以使用or_expand，不知道19c为什么不行，不过21c下执行计划还是存在1个全表扫描。
Plan hash value: 1850264297
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 3117 (100)| | 1 |00:00:00.01 | 1486 | | | |
| 1 | VIEW | VW_ORE_4392E357 | 1 | 40214 | 7225K| | 3117 (1)| 00:00:01 | 1 |00:00:00.01 | 1486 | | | |
| 2 | UNION-ALL | | 1 | | | | | | 1 |00:00:00.01 | 1486 | | | |
|* 3 | FILTER | | 1 | | | | | | 1 |00:00:00.01 | 7 | | | |
| 4 | MERGE JOIN CARTESIAN | | 1 | 1 | 118 | | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 7 | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 1 | 1 | 59 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 6 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 1 | 1 | | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | |
| 7 | BUFFER SORT | | 1 | 1 | 59 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | 73728 | 73728 | |
|* 8 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 1 | 1 | 59 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | | | |
|* 9 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 1 | 1 | | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 10 | FILTER | | 1 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 11 | MERGE JOIN CARTESIAN | | 0 | 1 | 118 | | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 12 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 13 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 14 | BUFFER SORT | | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
|* 15 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 0 | 1 | 59 | | 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 | MERGE JOIN CARTESIAN | | 0 | 1 | 118 | | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 19 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 20 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 21 | BUFFER SORT | | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
|* 22 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 23 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 24 | FILTER | | 1 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 25 | MERGE JOIN CARTESIAN | | 0 | 1 | 118 | | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 26 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 27 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 28 | BUFFER SORT | | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
| 29 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 30 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 31 | FILTER | | 1 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 32 | NESTED LOOPS | | 0 | 166 | 19588 | | 338 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 33 | NESTED LOOPS | | 0 | 166 | 19588 | | 338 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 34 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 166 | 9794 | | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 35 | INDEX RANGE SCAN | I_T2_CREATED | 0 | 166 | | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 36 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 37 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 38 | FILTER | | 1 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 39 | HASH JOIN OUTER | | 0 | 17591 | 1889K| 1224K| 1095 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | 3574K| 1122K| |
|* 40 | TABLE ACCESS FULL | T1 | 0 | 17591 | 1013K| | 415 (2)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 41 | TABLE ACCESS FULL | T2 | 0 | 69913 | 3481K| | 412 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 42 | HASH JOIN | | 1 | 22453 | 3595K| 1224K| 1668 (1)| 00:00:01 | 0 |00:00:00.01 | 1479 | 1123K| 1123K| 2147K (0)|
| 43 | JOIN FILTER CREATE | :BF0000 | 1 | 17591 | 1013K| | 415 (2)| 00:00:01 | 566 |00:00:00.01 | 1479 | | | |
|* 44 | TABLE ACCESS FULL | T1 | 1 | 17591 | 1013K| | 415 (2)| 00:00:01 | 566 |00:00:00.01 | 1479 | | | |
| 45 | VIEW | VW_JF_SET$4BFF19FA | 1 | 70079 | 7185K| | 805 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 46 | UNION-ALL | | 1 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 47 | FILTER | | 1 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 48 | JOIN FILTER USE | :BF0000 | 0 | 15175 | 874K| | 393 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 49 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 0 | 15175 | 874K| | 393 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 50 | INDEX RANGE SCAN | I_T2_CREATED | 0 | 15175 | | | 42 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 51 | FILTER | | 1 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 52 | JOIN FILTER USE | :BF0000 | 0 | 54904 | 3163K| | 413 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 53 | TABLE ACCESS FULL | T2 | 0 | 54904 | 3163K| | 413 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
OR_EXPAND(@"SEL$2BFA4EE4" (1) (2) (3) (4) (5) (6) (7) (8))
...
*/
SCOTT@book01p> alter index i_t2_created invisible;
Index altered.
--//再次测试，还是有1次全表扫描,尝试关闭布隆过滤,还是存在1个全表扫描，结果不再贴出。有一点点奇怪的是如果我删除i_t2_created索引。
SCOTT@book01p> drop index i_t2_created;
Index dropped.
Plan hash value: 1351258686
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1125 (100)| | 1 |00:00:00.01 | 7 |
| 1 | VIEW | VW_ORE_4392E357 | 1 | 17592 | 3161K| | 1125 (1)| 00:00:01 | 1 |00:00:00.01 | 7 |
| 2 | UNION-ALL | | 1 | | | | | | 1 |00:00:00.01 | 7 |
|* 3 | FILTER | | 1 | | | | | | 0 |00:00:00.01 | 0 |
|* 4 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 |
|* 5 | HASH JOIN OUTER | | 0 | 17591 | 2027K| 1224K| 1121 (1)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 6 | TABLE ACCESS FULL | T1 | 0 | 17591 | 1013K| | 415 (2)| 00:00:01 | 0 |00:00:00.01 | 0 |
| 7 | TABLE ACCESS FULL | T2 | 0 | 69913 | 4028K| | 412 (1)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 8 | FILTER | | 1 | | | | | | 1 |00:00:00.01 | 7 |
| 9 | MERGE JOIN CARTESIAN | | 1 | 1 | 118 | | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 7 |
|* 10 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 1 | 1 | 59 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 11 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 1 | 1 | | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
| 12 | BUFFER SORT | | 1 | 1 | 59 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 13 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 59 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 14 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 1 | 1 | | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
OR_EXPAND(@"SEL$2BFA4EE4" (1) (2) )
...
*/
--//似乎只要不过度展开，就可以获得比较理想的执行计划。
--//注意Outline Data里面记录的是OR_EXPAND(@"SEL$2BFA4EE4" (1) (2) )
SCOTT@book01p> create index i_t2_CREATED on t2(CREATED );
Index created.
--//再次测试，使用提示or_expand(@"SEL$2BFA4EE4" (1) (2) )。
SCOTT@book01p> @ f4.txt 'or_expand(@"SEL$2BFA4EE4" (1) (2) )'
OBJECT_ID OBJECT_NAME OBJECT_TYPE OBJECT_ID OBJECT_NAME OBJECT_TYPE
--------- ----------- ----------- ---------- ----------- -----------
76191 DEPT TABLE 76191 DEPT TABLE
Plan hash value: 1351258686
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1125 (100)| | 1 |00:00:00.01 | 7 |
| 1 | VIEW | VW_ORE_4392E357 | 1 | 17592 | 3161K| | 1125 (1)| 00:00:01 | 1 |00:00:00.01 | 7 |
| 2 | UNION-ALL | | 1 | | | | | | 1 |00:00:00.01 | 7 |
|* 3 | FILTER | | 1 | | | | | | 0 |00:00:00.01 | 0 |
|* 4 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 |
|* 5 | HASH JOIN OUTER | | 0 | 17591 | 2027K| 1224K| 1121 (1)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 6 | TABLE ACCESS FULL | T1 | 0 | 17591 | 1013K| | 415 (2)| 00:00:01 | 0 |00:00:00.01 | 0 |
| 7 | TABLE ACCESS FULL | T2 | 0 | 69913 | 4028K| | 412 (1)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 8 | FILTER | | 1 | | | | | | 1 |00:00:00.01 | 7 |
| 9 | MERGE JOIN CARTESIAN | | 1 | 1 | 118 | | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 7 |
|* 10 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 1 | 1 | 59 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 11 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 1 | 1 | | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
| 12 | BUFFER SORT | | 1 | 1 | 59 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 13 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 59 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 14 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 1 | 1 | | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
--//虽然存在一个全表扫描，但是只要:v_id 非空，这条执行路径不会执行，可以发现starts=0.(id =6，7)。
--//当然如果exec :v_id := null;执行计划如下：
Plan hash value: 1351258686
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1125 (100)| | 8 |00:00:00.06 | 2959 | | | |
| 1 | VIEW | VW_ORE_4392E357 | 1 | 17592 | 3161K| | 1125 (1)| 00:00:01 | 8 |00:00:00.06 | 2959 | | | |
| 2 | UNION-ALL | | 1 | | | | | | 8 |00:00:00.06 | 2959 | | | |
|* 3 | FILTER | | 1 | | | | | | 8 |00:00:00.06 | 2959 | | | |
|* 4 | FILTER | | 1 | | | | | | 8 |00:00:00.06 | 2959 | | | |
|* 5 | HASH JOIN OUTER | | 1 | 17591 | 2027K| 1224K| 1121 (1)| 00:00:01 | 566 |00:00:00.06 | 2959 | 1123K| 1123K| 2513K (0)|
|* 6 | TABLE ACCESS FULL | T1 | 1 | 17591 | 1013K| | 415 (2)| 00:00:01 | 566 |00:00:00.01 | 1479 | | | |
| 7 | TABLE ACCESS FULL | T2 | 1 | 69913 | 4028K| | 412 (1)| 00:00:01 | 69913 |00:00:00.02 | 1480 | | | |
|* 8 | FILTER | | 1 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 9 | MERGE JOIN CARTESIAN | | 0 | 1 | 118 | | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 11 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 12 | BUFFER SORT | | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
|* 13 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 14 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//执行计划选择全表扫描。
$ cat f5.txt
column SESSION_ID format 9999
column VALUE format 9999
column EVENT format 9999
column DESCRIPTION format a70
column SQL_FEATURE format a24
define 1=expand
SELECT *
FROM v$session_fix_control
WHERE session_id = SYS_CONTEXT ('userenv', 'sid')
and OPTIMIZER_FEATURE_ENABLE like '2%'
AND ( LOWER (description) LIKE LOWER ('%&1%')
OR LOWER (sql_feature) LIKE LOWER ('%&&1%')
OR TO_CHAR (bugno) LIKE LOWER ('%&&1%')
OR optimizer_feature_enable LIKE LOWER ('%&&1%'))
order by 6
/
SCOTT@book01p> @ f5.txt
SESSION_ID BUGNO VALUE SQL_FEATURE DESCRIPTION OPTIMIZER_FEATURE_ENABLE EVENT IS_DEFAULT CON_ID
---------- ---------- ----- ------------------------ ---------------------------------------------------------------------- ------------------------- ----- ---------- ----------
282 27541468 1 QKSFM_OR_EXPAND_27541468 avoid disjunct chain if same index chosen by other disjunct chai 20.1.0 0 1 3
282 28498976 1 QKSFM_OR_EXPAND_28498976 Allow legacy OR expansion for DML top query block 20.1.0 0 1 3
282 29450812 1 QKSFM_OR_EXPAND_29450812 Allow legacy ORE for exotic query constructs 20.1.0 0 1 3
282 28414968 3 QKSFM_OR_EXPAND_28414968 expansion with some constant branches;fkr1 in (NOT)EXISTS subque 20.1.0 0 1 3
282 29304314 1 QKSFM_OR_EXPAND_29304314 Allow legacy OR expansion when number of sub QB exceeds threshol 20.1.0 0 1 3
282 30374470 1 QKSFM_OR_EXPAND_30374470 Obey OR expansion hint with arguments 20.1.0 0 1 3
282 30358390 1 QKSFM_OR_EXPAND_30358390 Avoid FKR during ORE if query has group/order by 20.1.0 0 1 3
282 31191224 1 QKSFM_OR_EXPAND_31191224 ORE - Fix opposite range predicate check 20.1.0 0 1 3
282 30347410 1 QKSFM_OR_EXPAND_30347410 Check for sub QB is present in given QB by traversing the operan 20.1.0 0 1 3
282 29385774 1 QKSFM_OR_EXPAND_29385774 Allow LORE when cbqt ore rejected due to sub qb is parametrised 20.1.0 0 1 3
282 31162457 1 QKSFM_OR_EXPAND_31162457 Obey ORE hint with arguments even when no index driver found 21.1.0 0 1 3
282 30617002 1 QKSFM_OR_EXPAND_30617002 Allow ORE in coalesced query block 21.1.0 0 1 3
282 28234255 3 QKSFM_OR_EXPAND_28234255 allow more cases for interleaving ORE with SU 21.1.0 0 1 3
13 rows selected.
--//20c，21c版本增加许多OR_EXPAND的fix，也许19c还不支持，能力有限放弃。
--//顺便提一下这类方式只能使用sql patch打补丁来控制执行计划。
--//有点麻烦是如果建立created索引，执行计划会变差。顺便测试一下，可以这样打补丁就没有问题了
SYS@book01p> @ sqlpatch 71vbkguj3482r 'or_expand(@"SEL$2BFA4EE4" (1) (2))'
input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12)
drop sql patch ,run exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_71vbkguj3482r');
display sql path message , run @spext 71vbkguj3482r
P
--//生产系统遇到的问题，我以为以前在21c下测试通过的方法在19c上可以通过，结果不行，有必要重复在21c重复测试看看。
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 ;
--//drop table t2 purge ;
create table t1 as select * from all_objects;
create table t2 as select * from all_objects;
create index i_t1_object_id on t1(object_id);
create index i_t2_object_id on t2(object_id);
create index i_t1_object_name on t1(object_name);
create index i_t2_object_name on t2(object_name);
create index i_t1_CREATED on t1(CREATED );
create index i_t2_CREATED on t2(CREATED );
--//分析表略。
$ cat f4.txt
set term off
variable v_id number ;
variable v_id1 number ;
variable v_name varchar2(20) ;
variable startdate varchar2(32) ;
variable enddate varchar2(32) ;
exec :v_id := 76191;
--exec :v_name := NULL;
exec :startdate := '2024-08-16 00:00:00'
exec :enddate := '2024-08-17 00:00:00'
set term on
SELECT /*+ &&1 */
t1.object_id
,t1.object_name
,t1.object_type
,t2.object_id
,t2.object_name
,t2.object_type
FROM t1 LEFT JOIN t2 ON t1.object_id = t2.object_id
WHERE t1.created >= SYSDATE - 360
AND ( ( :v_id = '' OR :v_id IS NULL) OR t2.object_id = :v_id)
AND ( ( :StartDate = '' OR :StartDate IS NULL) OR t2.CREATED >= :StartDate)
AND ( ( :EndDate = '' OR :EndDate IS NULL) OR t2.CREATED <= :EndDate);
--//做一个接近生产系统的例子,真实的生产系统语句基本类似(只不过查询SYSDATE - 7),注意出现LEFT JOIN,谓词条件在表T2上,使用
--//use_concat提示根本不行。
--//另外实际上开发有可能写错，如果:v_id，:StartDate，:EndDate任何一个非空，LEFT JOIN消失，换一句话将开发可能写错sql语句。
--//实际的情况如果:v_id，:StartDate，:EndDate都是null，在实际生产系统查询没有任何意义，返回一大堆记录。
--//当然还是1个可能开发( ( :v_id = '' OR :v_id IS NULL) OR t2.object_id = :v_id)应该写成
--//( ( :v_id = '' OR :v_id IS NULL) OR t1.object_id = :v_id).
3.测试：
SCOTT@book01p> @ sl all
alter session set statistics_level = all;
Session altered.
--//如果没有提示:
SCOTT@book01p> @ f4.txt ''
OBJECT_ID OBJECT_NAME OBJECT_TYPE OBJECT_ID OBJECT_NAME OBJECT_TYPE
--------- ----------- ----------- ---------- ----------- -----------
76191 DEPT TABLE 76191 DEPT TABLE
--//执行计划如下：
Plan hash value: 1764467842
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1121 (100)| | 1 |00:00:00.06 | 2959 | | | |
|* 1 | FILTER | | 1 | | | | | | 1 |00:00:00.06 | 2959 | | | |
|* 2 | HASH JOIN OUTER | | 1 | 17591 | 2027K| 1224K| 1121 (1)| 00:00:01 | 566 |00:00:00.06 | 2959 | 1123K| 1123K| 2736K (0)|
|* 3 | TABLE ACCESS FULL| T1 | 1 | 17591 | 1013K| | 415 (2)| 00:00:01 | 566 |00:00:00.01 | 1479 | | | |
| 4 | TABLE ACCESS FULL| T2 | 1 | 69913 | 4028K| | 412 (1)| 00:00:01 | 69913 |00:00:00.02 | 1480 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2BFA4EE4
3 - SEL$2BFA4EE4 / "T1"@"SEL$1"
4 - SEL$2BFA4EE4 / "T2"@"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$2BFA4EE4")
MERGE(@"SEL$8812AA4E" >"SEL$948754D7")
OUTLINE(@"SEL$948754D7")
ANSI_REARCH(@"SEL$2")
OUTLINE(@"SEL$8812AA4E")
ANSI_REARCH(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
FULL(@"SEL$2BFA4EE4" "T1"@"SEL$1")
FULL(@"SEL$2BFA4EE4" "T2"@"SEL$1")
LEADING(@"SEL$2BFA4EE4" "T1"@"SEL$1" "T2"@"SEL$1")
USE_HASH(@"SEL$2BFA4EE4" "T2"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
3 - (NUMBER, Primary=1)
6 - (VARCHAR2(30), CSID=852, Primary=4)
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(((:V_ID IS NULL OR "T2"."OBJECT_ID"=:V_ID) AND (:STARTDATE IS NULL OR "T2"."CREATED">=:STARTDATE) AND (:ENDDATE IS NULL OR
"T2"."CREATED"<=:ENDDATE)))
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
3 - filter("T1"."CREATED">=SYSDATE@!-360)
67 rows selected.
--//正常没有提示的情况，根本不会使用索引，建立开发真心不要再写这类似的sql语句，我们开发真心感谢exadata或者现在的硬件性能。
--//提交问题，开发仅仅加入类似条件t1.created >= SYSDATE - 7，认为问题解决了，实际上开发自己到一定时间就发现写的语句执行
--//缓慢，加入一个条件t1.created >= SYSDATE - 7使执行计划走日期索引，7天的数据量实际上逻辑读依旧很高，根本不是解决该问题
--//的根本方法。
SCOTT@book01p> @ f4.txt ' or_expand(@"SEL$2BFA4EE4")'
OBJECT_ID OBJECT_NAME OBJECT_TYPE OBJECT_ID OBJECT_NAME OBJECT_TYPE
--------- ----------- ----------- ---------- ----------- -----------
76191 DEPT TABLE 76191 DEPT TABLE
--//注：提示里面的信息是Query Block Name，可以从前面的Query Block Name / Object Alias (identified by operation id)。
--//查看执行计划确实可以使用or_expand，不知道19c为什么不行，不过21c下执行计划还是存在1个全表扫描。
Plan hash value: 1850264297
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 3117 (100)| | 1 |00:00:00.01 | 1486 | | | |
| 1 | VIEW | VW_ORE_4392E357 | 1 | 40214 | 7225K| | 3117 (1)| 00:00:01 | 1 |00:00:00.01 | 1486 | | | |
| 2 | UNION-ALL | | 1 | | | | | | 1 |00:00:00.01 | 1486 | | | |
|* 3 | FILTER | | 1 | | | | | | 1 |00:00:00.01 | 7 | | | |
| 4 | MERGE JOIN CARTESIAN | | 1 | 1 | 118 | | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 7 | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 1 | 1 | 59 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 6 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 1 | 1 | | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | |
| 7 | BUFFER SORT | | 1 | 1 | 59 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | 73728 | 73728 | |
|* 8 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 1 | 1 | 59 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | | | |
|* 9 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 1 | 1 | | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 10 | FILTER | | 1 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 11 | MERGE JOIN CARTESIAN | | 0 | 1 | 118 | | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 12 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 13 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 14 | BUFFER SORT | | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
|* 15 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 0 | 1 | 59 | | 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 | MERGE JOIN CARTESIAN | | 0 | 1 | 118 | | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 19 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 20 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 21 | BUFFER SORT | | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
|* 22 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 23 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 24 | FILTER | | 1 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 25 | MERGE JOIN CARTESIAN | | 0 | 1 | 118 | | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 26 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 27 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 28 | BUFFER SORT | | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
| 29 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 30 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 31 | FILTER | | 1 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 32 | NESTED LOOPS | | 0 | 166 | 19588 | | 338 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 33 | NESTED LOOPS | | 0 | 166 | 19588 | | 338 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 34 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 166 | 9794 | | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 35 | INDEX RANGE SCAN | I_T2_CREATED | 0 | 166 | | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 36 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 37 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 38 | FILTER | | 1 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 39 | HASH JOIN OUTER | | 0 | 17591 | 1889K| 1224K| 1095 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | 3574K| 1122K| |
|* 40 | TABLE ACCESS FULL | T1 | 0 | 17591 | 1013K| | 415 (2)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 41 | TABLE ACCESS FULL | T2 | 0 | 69913 | 3481K| | 412 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 42 | HASH JOIN | | 1 | 22453 | 3595K| 1224K| 1668 (1)| 00:00:01 | 0 |00:00:00.01 | 1479 | 1123K| 1123K| 2147K (0)|
| 43 | JOIN FILTER CREATE | :BF0000 | 1 | 17591 | 1013K| | 415 (2)| 00:00:01 | 566 |00:00:00.01 | 1479 | | | |
|* 44 | TABLE ACCESS FULL | T1 | 1 | 17591 | 1013K| | 415 (2)| 00:00:01 | 566 |00:00:00.01 | 1479 | | | |
| 45 | VIEW | VW_JF_SET$4BFF19FA | 1 | 70079 | 7185K| | 805 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 46 | UNION-ALL | | 1 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 47 | FILTER | | 1 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 48 | JOIN FILTER USE | :BF0000 | 0 | 15175 | 874K| | 393 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 49 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 0 | 15175 | 874K| | 393 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 50 | INDEX RANGE SCAN | I_T2_CREATED | 0 | 15175 | | | 42 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 51 | FILTER | | 1 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 52 | JOIN FILTER USE | :BF0000 | 0 | 54904 | 3163K| | 413 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 53 | TABLE ACCESS FULL | T2 | 0 | 54904 | 3163K| | 413 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
OR_EXPAND(@"SEL$2BFA4EE4" (1) (2) (3) (4) (5) (6) (7) (8))
...
*/
SCOTT@book01p> alter index i_t2_created invisible;
Index altered.
--//再次测试，还是有1次全表扫描,尝试关闭布隆过滤,还是存在1个全表扫描，结果不再贴出。有一点点奇怪的是如果我删除i_t2_created索引。
SCOTT@book01p> drop index i_t2_created;
Index dropped.
Plan hash value: 1351258686
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1125 (100)| | 1 |00:00:00.01 | 7 |
| 1 | VIEW | VW_ORE_4392E357 | 1 | 17592 | 3161K| | 1125 (1)| 00:00:01 | 1 |00:00:00.01 | 7 |
| 2 | UNION-ALL | | 1 | | | | | | 1 |00:00:00.01 | 7 |
|* 3 | FILTER | | 1 | | | | | | 0 |00:00:00.01 | 0 |
|* 4 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 |
|* 5 | HASH JOIN OUTER | | 0 | 17591 | 2027K| 1224K| 1121 (1)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 6 | TABLE ACCESS FULL | T1 | 0 | 17591 | 1013K| | 415 (2)| 00:00:01 | 0 |00:00:00.01 | 0 |
| 7 | TABLE ACCESS FULL | T2 | 0 | 69913 | 4028K| | 412 (1)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 8 | FILTER | | 1 | | | | | | 1 |00:00:00.01 | 7 |
| 9 | MERGE JOIN CARTESIAN | | 1 | 1 | 118 | | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 7 |
|* 10 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 1 | 1 | 59 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 11 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 1 | 1 | | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
| 12 | BUFFER SORT | | 1 | 1 | 59 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 13 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 59 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 14 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 1 | 1 | | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
OR_EXPAND(@"SEL$2BFA4EE4" (1) (2) )
...
*/
--//似乎只要不过度展开，就可以获得比较理想的执行计划。
--//注意Outline Data里面记录的是OR_EXPAND(@"SEL$2BFA4EE4" (1) (2) )
SCOTT@book01p> create index i_t2_CREATED on t2(CREATED );
Index created.
--//再次测试，使用提示or_expand(@"SEL$2BFA4EE4" (1) (2) )。
SCOTT@book01p> @ f4.txt 'or_expand(@"SEL$2BFA4EE4" (1) (2) )'
OBJECT_ID OBJECT_NAME OBJECT_TYPE OBJECT_ID OBJECT_NAME OBJECT_TYPE
--------- ----------- ----------- ---------- ----------- -----------
76191 DEPT TABLE 76191 DEPT TABLE
Plan hash value: 1351258686
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1125 (100)| | 1 |00:00:00.01 | 7 |
| 1 | VIEW | VW_ORE_4392E357 | 1 | 17592 | 3161K| | 1125 (1)| 00:00:01 | 1 |00:00:00.01 | 7 |
| 2 | UNION-ALL | | 1 | | | | | | 1 |00:00:00.01 | 7 |
|* 3 | FILTER | | 1 | | | | | | 0 |00:00:00.01 | 0 |
|* 4 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 |
|* 5 | HASH JOIN OUTER | | 0 | 17591 | 2027K| 1224K| 1121 (1)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 6 | TABLE ACCESS FULL | T1 | 0 | 17591 | 1013K| | 415 (2)| 00:00:01 | 0 |00:00:00.01 | 0 |
| 7 | TABLE ACCESS FULL | T2 | 0 | 69913 | 4028K| | 412 (1)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 8 | FILTER | | 1 | | | | | | 1 |00:00:00.01 | 7 |
| 9 | MERGE JOIN CARTESIAN | | 1 | 1 | 118 | | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 7 |
|* 10 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 1 | 1 | 59 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 11 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 1 | 1 | | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
| 12 | BUFFER SORT | | 1 | 1 | 59 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 13 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 59 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 14 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 1 | 1 | | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
--//虽然存在一个全表扫描，但是只要:v_id 非空，这条执行路径不会执行，可以发现starts=0.(id =6，7)。
--//当然如果exec :v_id := null;执行计划如下：
Plan hash value: 1351258686
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1125 (100)| | 8 |00:00:00.06 | 2959 | | | |
| 1 | VIEW | VW_ORE_4392E357 | 1 | 17592 | 3161K| | 1125 (1)| 00:00:01 | 8 |00:00:00.06 | 2959 | | | |
| 2 | UNION-ALL | | 1 | | | | | | 8 |00:00:00.06 | 2959 | | | |
|* 3 | FILTER | | 1 | | | | | | 8 |00:00:00.06 | 2959 | | | |
|* 4 | FILTER | | 1 | | | | | | 8 |00:00:00.06 | 2959 | | | |
|* 5 | HASH JOIN OUTER | | 1 | 17591 | 2027K| 1224K| 1121 (1)| 00:00:01 | 566 |00:00:00.06 | 2959 | 1123K| 1123K| 2513K (0)|
|* 6 | TABLE ACCESS FULL | T1 | 1 | 17591 | 1013K| | 415 (2)| 00:00:01 | 566 |00:00:00.01 | 1479 | | | |
| 7 | TABLE ACCESS FULL | T2 | 1 | 69913 | 4028K| | 412 (1)| 00:00:01 | 69913 |00:00:00.02 | 1480 | | | |
|* 8 | FILTER | | 1 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 9 | MERGE JOIN CARTESIAN | | 0 | 1 | 118 | | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID BATCHED | T2 | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 11 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 12 | BUFFER SORT | | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
|* 13 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 0 | 1 | 59 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 14 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//执行计划选择全表扫描。
$ cat f5.txt
column SESSION_ID format 9999
column VALUE format 9999
column EVENT format 9999
column DESCRIPTION format a70
column SQL_FEATURE format a24
define 1=expand
SELECT *
FROM v$session_fix_control
WHERE session_id = SYS_CONTEXT ('userenv', 'sid')
and OPTIMIZER_FEATURE_ENABLE like '2%'
AND ( LOWER (description) LIKE LOWER ('%&1%')
OR LOWER (sql_feature) LIKE LOWER ('%&&1%')
OR TO_CHAR (bugno) LIKE LOWER ('%&&1%')
OR optimizer_feature_enable LIKE LOWER ('%&&1%'))
order by 6
/
SCOTT@book01p> @ f5.txt
SESSION_ID BUGNO VALUE SQL_FEATURE DESCRIPTION OPTIMIZER_FEATURE_ENABLE EVENT IS_DEFAULT CON_ID
---------- ---------- ----- ------------------------ ---------------------------------------------------------------------- ------------------------- ----- ---------- ----------
282 27541468 1 QKSFM_OR_EXPAND_27541468 avoid disjunct chain if same index chosen by other disjunct chai 20.1.0 0 1 3
282 28498976 1 QKSFM_OR_EXPAND_28498976 Allow legacy OR expansion for DML top query block 20.1.0 0 1 3
282 29450812 1 QKSFM_OR_EXPAND_29450812 Allow legacy ORE for exotic query constructs 20.1.0 0 1 3
282 28414968 3 QKSFM_OR_EXPAND_28414968 expansion with some constant branches;fkr1 in (NOT)EXISTS subque 20.1.0 0 1 3
282 29304314 1 QKSFM_OR_EXPAND_29304314 Allow legacy OR expansion when number of sub QB exceeds threshol 20.1.0 0 1 3
282 30374470 1 QKSFM_OR_EXPAND_30374470 Obey OR expansion hint with arguments 20.1.0 0 1 3
282 30358390 1 QKSFM_OR_EXPAND_30358390 Avoid FKR during ORE if query has group/order by 20.1.0 0 1 3
282 31191224 1 QKSFM_OR_EXPAND_31191224 ORE - Fix opposite range predicate check 20.1.0 0 1 3
282 30347410 1 QKSFM_OR_EXPAND_30347410 Check for sub QB is present in given QB by traversing the operan 20.1.0 0 1 3
282 29385774 1 QKSFM_OR_EXPAND_29385774 Allow LORE when cbqt ore rejected due to sub qb is parametrised 20.1.0 0 1 3
282 31162457 1 QKSFM_OR_EXPAND_31162457 Obey ORE hint with arguments even when no index driver found 21.1.0 0 1 3
282 30617002 1 QKSFM_OR_EXPAND_30617002 Allow ORE in coalesced query block 21.1.0 0 1 3
282 28234255 3 QKSFM_OR_EXPAND_28234255 allow more cases for interleaving ORE with SU 21.1.0 0 1 3
13 rows selected.
--//20c，21c版本增加许多OR_EXPAND的fix，也许19c还不支持，能力有限放弃。
--//顺便提一下这类方式只能使用sql patch打补丁来控制执行计划。
--//有点麻烦是如果建立created索引，执行计划会变差。顺便测试一下，可以这样打补丁就没有问题了
SYS@book01p> @ sqlpatch 71vbkguj3482r 'or_expand(@"SEL$2BFA4EE4" (1) (2))'
input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12)
drop sql patch ,run exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_71vbkguj3482r');
display sql path message , run @spext 71vbkguj3482r
P