[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
PL/SQL procedure successfully completed.
–//注：先取消补丁，看看那个_fix_control起作用。
–//exec sys.dbms_sqldiag.drop_sql_patch(‘sqlpatch_71vbkguj3482r’);
–//前面的DESCRIPTION 不好理解,首先设置如下，全部off。
ALTER session set “_fix_control”=’27541468:off’;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER session set “_fix_control”=’28498976:off’;
ALTER session set “_fix_control”=’29450812:off’;
ALTER session set “_fix_control”=’28414968:off’;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER session set “_fix_control”=’29304314:off’;
ALTER session set “_fix_control”=’30374470:off’;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER session set “_fix_control”=’30358390:off’;
ALTER session set “_fix_control”=’31191224:off’;
ALTER session set “_fix_control”=’30347410:off’;
ALTER session set “_fix_control”=’29385774:off’;
ALTER session set “_fix_control”=’31162457:off’;
ALTER session set “_fix_control”=’30617002:off’;
ALTER session set “_fix_control”=’28234255:off’;
–//确实提示or_expand(@”SEL$2BFA4EE4″ (1) (2))不起作用。然后退出会话，重新登录,这样这些fix全部是on。
–//然后1个1个尝试设置off，每设置1次执行@ f4.txt ‘or_expand(@”SEL$2BFA4EE4″ (1) (2) )’,查看执行计划，然后退出会话，重复这些步骤。
ALTER session set “_fix_control”=’27541468:0′;
@ f4.txt ‘or_expand(@”SEL$2BFA4EE4″ (1) (2) )’
@ dpc ” outline ”
quit
–//如此重复。
–//设置如下，提示or_expand(@”SEL$2BFA4EE4″ (1) (2))无效。
–//ALTER session set “_fix_control”=’27541468:0′;,提示or_expand(@”SEL$2BFA4EE4” (1) (2))无效。
–//ALTER session set “_fix_control”=’28414968:0′;,提示or_expand(@”SEL$2BFA4EE4” (1) (2))无效。
–//ALTER session set “_fix_control”=’30374470:0′;,提示or_expand(@”SEL$2BFA4EE4” (1) (2))无效。
–//只能说明oracle的优化器太复杂了,DESCRIPTION部分显示信息不全，视乎oracle一直不断更新完善sql查询转换以及优化。
$ cat f6.txt
ALTER session set “_fix_control”=’27541468:off’;
ALTER session set “_fix_control”=’28498976:off’;
ALTER session set “_fix_control”=’29450812:off’;
ALTER session set “_fix_control”=’28414968:off’;
ALTER session set “_fix_control”=’29304314:off’;
ALTER session set “_fix_control”=’30374470:off’;
ALTER session set “_fix_control”=’30358390:off’;
ALTER session set “_fix_control”=’31191224:off’;
ALTER session set “_fix_control”=’30347410:off’;
ALTER session set “_fix_control”=’29385774:off’;
ALTER session set “_fix_control”=’31162457:off’;
ALTER session set “_fix_control”=’30617002:off’;
ALTER session set “_fix_control”=’28234255:off’;
–//先设置全部off。
SCOTT@book01p> @ f6.txt
–//如下设置on。
ALTER session set “_fix_control”=’27541468:1′;
ALTER session set “_fix_control”=’28414968:1′; –//缺省是3.
–//ALTER session set “_fix_control”=’28414968:3′;
ALTER session set “_fix_control”=’30374470:1′;
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 0 QKSFM_OR_EXPAND_28498976 Allow legacy OR expansion for DML top query block 20.1.0 0 0 3
282 29450812 0 QKSFM_OR_EXPAND_29450812 Allow legacy ORE for exotic query constructs 20.1.0 0 0 3
282 28414968 1 QKSFM_OR_EXPAND_28414968 expansion with some constant branches;fkr1 in (NOT)EXISTS subque 20.1.0 0 0 3
282 29304314 0 QKSFM_OR_EXPAND_29304314 Allow legacy OR expansion when number of sub QB exceeds threshol 20.1.0 0 0 3
282 30374470 1 QKSFM_OR_EXPAND_30374470 Obey OR expansion hint with arguments 20.1.0 0 1 3
282 30358390 0 QKSFM_OR_EXPAND_30358390 Avoid FKR during ORE if query has group/order by 20.1.0 0 0 3
282 31191224 0 QKSFM_OR_EXPAND_31191224 ORE – Fix opposite range predicate check 20.1.0 0 0 3
282 30347410 0 QKSFM_OR_EXPAND_30347410 Check for sub QB is present in given QB by traversing the operan 20.1.0 0 0 3
282 29385774 0 QKSFM_OR_EXPAND_29385774 Allow LORE when cbqt ore rejected due to sub qb is parametrised 20.1.0 0 0 3
282 31162457 0 QKSFM_OR_EXPAND_31162457 Obey ORE hint with arguments even when no index driver found 21.1.0 0 0 3
282 30617002 0 QKSFM_OR_EXPAND_30617002 Allow ORE in coalesced query block 21.1.0 0 0 3
282 28234255 0 QKSFM_OR_EXPAND_28234255 allow more cases for interleaving ORE with SU 21.1.0 0 0 3
13 rows selected.
–//使用or_expand提示有效，结果不再贴出。
4.补充测试使用use_concat提示：
$ 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 := null;
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
–FROM t1 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);
SCOTT@book01p> @ f4.txt ‘use_concat(@”SEL$2BFA4EE4″ 8 OR_PREDICATES(2))’
OBJECT_ID OBJECT_NAME OBJECT_TYPE OBJECT_ID OBJECT_NAME OBJECT_TYPE
———- ———– ———– ———- ———– ———–
76191 DEPT TABLE 76191 DEPT TABLE
Plan hash value: 3695390898
———————————————————————————————————————————————————–
| 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 | | | | 2243 (100)| | 1 |00:00:00.05 | 2959 | | | |
| 1 | CONCATENATION | | 1 | | | | | | 1 |00:00:00.05 | 2959 | | | |
|* 2 | FILTER | | 1 | | | | | | 1 |00:00:00.05 | 2959 | | | |
|* 3 | HASH JOIN OUTER | | 1 | 17553 | 2022K| 1224K| 1121 (1)| 00:00:01 | 566 |00:00:00.05 | 2959 | 1055K| 1055K| 1333K (0)|
|* 4 | TABLE ACCESS FULL | T1 | 1 | 17553 | 1011K| | 415 (2)| 00:00:01 | 566 |00:00:00.01 | 1479 | | | |
| 5 | TABLE ACCESS FULL | T2 | 1 | 69913 | 4028K| | 412 (1)| 00:00:01 | 69913 |00:00:00.02 | 1480 | | | |
|* 6 | FILTER | | 1 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 7 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 8 | HASH JOIN OUTER | | 0 | 17553 | 2022K| 1224K| 1121 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | 2713K| 1278K| |
|* 9 | TABLE ACCESS FULL| T1 | 0 | 17553 | 1011K| | 415 (2)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 10 | TABLE ACCESS FULL| T2 | 0 | 69913 | 4028K| | 412 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
———————————————————————————————————————————————————–
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$2BFA4EE4
4 – SEL$2BFA4EE4_1 / “T1″@”SEL$1”
5 – SEL$2BFA4EE4_1 / “T2″@”SEL$1”
9 – SEL$2BFA4EE4_2 / “T1″@”SEL$2BFA4EE4_2”
10 – SEL$2BFA4EE4_2 / “T2″@”SEL$2BFA4EE4_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(@”SEL$2BFA4EE4″)
MERGE(@”SEL$8812AA4E” >”SEL$948754D7″)
OUTLINE_LEAF(@”SEL$2BFA4EE4_1″)
USE_CONCAT(@”SEL$2BFA4EE4″ 8 OR_PREDICATES(2) PREDICATE_REORDERS((11 1) (1 2) (2 3) (3 4) (4 5) (5 6) (6 7) (7 8) (8 9) (9 10) (10 11)))
OUTLINE_LEAF(@”SEL$2BFA4EE4_2″)
OUTLINE(@”SEL$948754D7″)
ANSI_REARCH(@”SEL$2″)
OUTLINE(@”SEL$8812AA4E”)
ANSI_REARCH(@”SEL$1″)
OUTLINE(@”SEL$2″)
OUTLINE(@”SEL$1″)
FULL(@”SEL$2BFA4EE4_1″ “T1″@”SEL$1”)
FULL(@”SEL$2BFA4EE4_1″ “T2″@”SEL$1”)
FULL(@”SEL$2BFA4EE4_2″ “T1″@”SEL$2BFA4EE4_2”)
FULL(@”SEL$2BFA4EE4_2″ “T2″@”SEL$2BFA4EE4_2”)
LEADING(@”SEL$2BFA4EE4_1″ “T1″@”SEL$1” “T2″@”SEL$1”)
LEADING(@”SEL$2BFA4EE4_2″ “T1″@”SEL$2BFA4EE4_2” “T2″@”SEL$2BFA4EE4_2”)
USE_HASH(@”SEL$2BFA4EE4_1″ “T2″@”SEL$1”)
USE_HASH(@”SEL$2BFA4EE4_2″ “T2″@”SEL$2BFA4EE4_2”)
PX_JOIN_FILTER(@”SEL$2BFA4EE4_1″ “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):
—————————————————
2 – filter((“T2”.”OBJECT_ID”=:V_ID AND (:STARTDATE IS NULL OR “T2″.”CREATED”>=:STARTDATE) AND (:ENDDATE IS NULL OR “T2”.”CREATED”<=:ENDDATE)))
3 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
4 – filter(“T1″.”CREATED”>=SYSDATE@!-360)
6 – filter(:V_ID IS NULL)
7 – filter(((:STARTDATE IS NULL OR “T2″.”CREATED”>=:STARTDATE) AND (:ENDDATE IS NULL OR “T2”.”CREATED”<=:ENDDATE) AND
LNNVL(“T2”.”OBJECT_ID”=:V_ID)))
8 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
9 – filter(“T1″.”CREATED”>=SYSDATE@!-360)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
—————————————————————————
1 – SEL$2BFA4EE4
– use_concat(@”SEL$2BFA4EE4″ 8 OR_PREDICATES(2))
–//21c下可以使用，但是连接顺序是先连接T1。主要原因left join的缘故。
–//修改LEFT JOIN 为join测试看看。
$ 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 := null;
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
FROM t1 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);
SCOTT@book01p> @ f4.txt ‘use_concat(@”SEL$58A6D7F6″ 8 OR_PREDICATES(2))’
OBJECT_ID OBJECT_NAME OBJECT_TYPE OBJECT_ID OBJECT_NAME OBJECT_TYPE
———- ———– ———– ———- ———– ———–
76191 DEPT TABLE 76191 DEPT TABLE
–//注意提示第1个参数发生了变化。
Plan hash value: 2853707501
—————————————————————————————————————————————————
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
—————————————————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | | 832 (100)| | 1 |00:00:00.01 | 8 |
| 1 | CONCATENATION | | 1 | | | | | 1 |00:00:00.01 | 8 |
| 2 | NESTED LOOPS | | 1 | 1 | 118 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 8 |
| 3 | NESTED LOOPS | | 1 | 1 | 118 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 7 |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 1 | 59 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 5 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 6 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 7 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 1 | 59 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 1 |
|* 8 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 |
|* 9 | HASH JOIN | | 0 | 14259 | 1643K| 828 (1)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 10 | TABLE ACCESS FULL | T2 | 0 | 14259 | 821K| 413 (1)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 11 | TABLE ACCESS FULL | T1 | 0 | 17553 | 1011K| 415 (2)| 00:00:01 | 0 |00:00:00.01 | 0 |
—————————————————————————————————————————————————
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$58A6D7F6
4 – SEL$58A6D7F6_1 / “T2″@”SEL$1”
5 – SEL$58A6D7F6_1 / “T2″@”SEL$1”
6 – SEL$58A6D7F6_1 / “T1″@”SEL$1”
7 – SEL$58A6D7F6_1 / “T1″@”SEL$1”
10 – SEL$58A6D7F6_2 / “T2″@”SEL$58A6D7F6_2”
11 – SEL$58A6D7F6_2 / “T1″@”SEL$58A6D7F6_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(@”SEL$58A6D7F6″)
MERGE(@”SEL$1″ >”SEL$2″)
OUTLINE_LEAF(@”SEL$58A6D7F6_1″)
USE_CONCAT(@”SEL$58A6D7F6″ 8 OR_PREDICATES(2) PREDICATE_REORDERS((11 1) (1 2) (2 3) (3 4) (4 5) (5 6) (6 7) (7 8) (8 9) (9 10) (10 11)))
OUTLINE_LEAF(@”SEL$58A6D7F6_2″)
OUTLINE(@”SEL$2″)
OUTLINE(@”SEL$1″)
INDEX_RS_ASC(@”SEL$58A6D7F6_1″ “T2″@”SEL$1” (“T2″.”OBJECT_ID”))
BATCH_TABLE_ACCESS_BY_ROWID(@”SEL$58A6D7F6_1″ “T2″@”SEL$1”)
INDEX(@”SEL$58A6D7F6_1″ “T1″@”SEL$1” (“T1″.”OBJECT_ID”))
FULL(@”SEL$58A6D7F6_2″ “T2″@”SEL$58A6D7F6_2”)
FULL(@”SEL$58A6D7F6_2″ “T1″@”SEL$58A6D7F6_2”)
LEADING(@”SEL$58A6D7F6_1″ “T2″@”SEL$1” “T1″@”SEL$1”)
LEADING(@”SEL$58A6D7F6_2″ “T2″@”SEL$58A6D7F6_2” “T1″@”SEL$58A6D7F6_2”)
USE_NL(@”SEL$58A6D7F6_1″ “T1″@”SEL$1”)
NLJ_BATCHING(@”SEL$58A6D7F6_1″ “T1″@”SEL$1”)
USE_HASH(@”SEL$58A6D7F6_2″ “T1″@”SEL$58A6D7F6_2”)
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
————————————–
3 – (NUMBER, Primary=1)
6 – (VARCHAR2(30), CSID=852, Primary=4)
9 – (VARCHAR2(30), CSID=852, Primary=7)
Predicate Information (identified by operation id):
—————————————————
4 – filter(((:STARTDATE IS NULL OR “T2″.”CREATED”>=:STARTDATE) AND (:ENDDATE IS NULL OR “T2”.”CREATED”<=:ENDDATE)))
5 – access(“T2”.”OBJECT_ID”=:V_ID)
6 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
7 – filter(“T1″.”CREATED”>=SYSDATE@!-360)
8 – filter(:V_ID IS NULL)
9 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
10 – filter(((:STARTDATE IS NULL OR “T2″.”CREATED”>=:STARTDATE) AND (:ENDDATE IS NULL OR “T2”.”CREATED”<=:ENDDATE) AND
LNNVL(“T2”.”OBJECT_ID”=:V_ID)))
11 – filter(“T1″.”CREATED”>=SYSDATE@!-360)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
—————————————————————————
1 – SEL$58A6D7F6
– use_concat(@”SEL$58A6D7F6″ 8 OR_PREDICATES(2))
98 rows selected.
–//ok,如果不存在left join连接，这样加提示优化没有问题。
–//顺便再提一下，一些发生查询转换的SQL语句的执行计划Peeked Binds (identified by position):部分可能看不到绑定变量值。
–//实际上语句上绑定变量已经发生了替换：
COTT@book01p> @ 10053x 6zg82txdy17mt 0
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4699_a6zg82txdy17mt.trc
SCOTT@book01p> @ 10053y ”
TRCLINE
————————————————————————————————————————
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ USE_CONCAT (8 OR_PREDICATES(2) ) */ “T1″.”OBJECT_ID” “OBJECT_ID”,”T1″.”OBJECT_NAME” “OBJECT_NAME”,”T1″.”OBJEC
T_TYPE” “OBJECT_TYPE”,”T2″.”OBJECT_ID” “OBJECT_ID”,”T2″.”OBJECT_NAME” “OBJECT_NAME”,”T2″.”OBJECT_TYPE” “OBJECT_TYPE” FRO
M “SCOTT”.”T1″ “T1″,”SCOTT”.”T2″ “T2” WHERE “T1″.”CREATED”>=SYSDATE@!-360 AND (:B1 IS NULL OR “T2”.”OBJECT_ID”=:B2) AND
(:B3 IS NULL OR “T2″.”CREATED”>=:B4) AND (:B5 IS NULL OR “T2”.”CREATED”<=:B6) AND “T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”
–//转换后的语句，绑定变量已经变成:Bx..
SCOTT@book01p> @ bind_cap 6zg82txdy17mt ”
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING INST_ID
————- ———— — ———– ———- ———- ——————- ————— ——————- ———-
6zg82txdy17mt 0 YES :V_ID 1 22 2025-06-20 10:34:09 NUMBER 76191 1
YES :STARTDATE 4 32 2025-06-20 10:34:09 VARCHAR2(32) 2024-08-16 00:00:00 1
YES :ENDDATE 7 32 2025-06-20 10:34:09 VARCHAR2(32) 2024-08-17 00:00:00 1
5.总结：
–//有点出乎意料。没有想到19c下无法通过加or_expand提示。
–//最合理的建议开发应该好好想想这样的代码根本不应该出现在生产系统中。
京ICP备14020293号-4