[20260413]oracle优化器缺陷再分析.txt
--//前几天听别人讲课，提到oracle优化器缺陷，视乎提到其他数据库的不存在该问题。
--//参考链接：
https://www.anbob.com/archives/9362.html => Oracle优化器outer join to inner不如YashanDB
https://www.minniebaby.tech/2025/09/08/oracle%e4%bc%98%e5%8c%96%e5%99%a8%e7%bc%ba%e9%99%b7%e4%b8%89/=>oracle优化器缺陷(三)
--//可以认为oracle的优化器确实存在缺陷，实际上许多数据库都存在查询转换，转换后提高查询的性能。
--//仅仅提出我的观点，实际上开发了解这类缺陷，选择规避就可以了。
--//顺序再重新测试看看，增加建立索引优化后的效果看看。
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.建立测试环境：
SCOTT@book01p> create table t1 as select * from all_objects ;
Table created.
SCOTT@book01p> create table t2 as select * from all_objects ;
Table created.
--//分析表略。
SCOTT@book01p> create index i_t2_object_id on t2(object_id);
Index created.
SCOTT@book01p> create index i_t1_object_name on t1(object_name);
Index created.
SCOTT@book01p> create index i_t2_object_name on t2(object_name);
Index created.
3.测试：
SCOTT@book01p> @ sl all
alter session set statistics_level = all;
Session altered.
SELECT t1.object_name ,t2.object_type ,t2.object_name n1 FROM t1 LEFT JOIN t2 ON t1.object_name = t2.object_name AND t2.object_id = 1832 WHERE t2.object_type = 'VIEW';
SCOTT@book01p> @ dpc '' outline ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2gpv7mj7jxbcj, child number 0
-------------------------------------
SELECT t1.object_name ,t2.object_type ,t2.object_name n1 FROM t1 LEFT
JOIN t2 ON t1.object_name = t2.object_name AND t2.object_id = 1832
WHERE t2.object_type = 'VIEW'
Plan hash value: 1076213087
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 2 |00:00:00.01 | 8 |
| 1 | NESTED LOOPS | | 1 | 1 | 87 | 4 (0)| 00:00:01 | 2 |00:00:00.01 | 8 |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 1 | 51 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 4 | INDEX RANGE SCAN | I_T1_OBJECT_NAME | 1 | 1 | 36 | 2 (0)| 00:00:01 | 2 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
2 - SEL$683367AA / "T2"@"SEL$1"
3 - SEL$683367AA / "T2"@"SEL$1"
4 - SEL$683367AA / "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$683367AA")
OUTER_JOIN_TO_INNER(@"SEL$2BFA4EE4" "T2"@"SEL$1")
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
OUTLINE(@"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")
INDEX_RS_ASC(@"SEL$683367AA" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$683367AA" "T2"@"SEL$1")
INDEX(@"SEL$683367AA" "T1"@"SEL$1" ("T1"."OBJECT_NAME"))
LEADING(@"SEL$683367AA" "T2"@"SEL$1" "T1"@"SEL$1")
USE_NL(@"SEL$683367AA" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T2"."OBJECT_TYPE"='VIEW')
3 - access("T2"."OBJECT_ID"=1832)
4 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME")
Note
-----
- this is an adaptive plan
63 rows selected.
--//出现nested loops，根本不存在外连接。
SCOTT@book01p> @ 10053x 2gpv7mj7jxbcj 0
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3803_a2gpv7mj7jxbcj.trc
SCOTT@book01p> @ 10053y
TRCLINE
--------------------------------------------------------------------------
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."OBJECT_NAME" "OBJECT_NAME","T2"."OBJECT_TYPE" "OBJECT_TYPE",
"T2"."OBJECT_NAME" "N1"
FROM "SCOTT"."T1" "T1","SCOTT"."T2" "T2"
WHERE "T2"."OBJECT_TYPE" = 'VIEW'
AND "T1"."OBJECT_NAME" = "T2"."OBJECT_NAME"
AND "T2"."OBJECT_ID" = 1832
--//注作了格式化处理，便于查看。
--//可以发现转换后T1，T2表之间根本不存在外连接。优化器可以选择更好的执行计划，主要原因在where查询条件是t2.object_type =
--//'VIEW'，在外连接的表T2加了查询条件,外连接消失。实际上如果外连接条件不是表T2的某个字段 is null的条件，其他t2字段的查
--//询外连接都会消失。
SELECT t1.object_name ,t2.object_type ,t2.object_name n1 FROM t1 LEFT JOIN t2 ON t1.object_name = t2.object_name AND t2.object_id = 1832 WHERE t2.object_type is null;
--//执行计划如下：
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 127 (100)| | 69913 |00:00:00.12 | 1165 | | | |
|* 1 | FILTER | | 1 | | | | | 69913 |00:00:00.12 | 1165 | | | |
|* 2 | HASH JOIN RIGHT OUTER | | 1 | 69914 | 5939K| 127 (1)| 00:00:01 | 69915 |00:00:00.09 | 1165 | 1399K| 1399K| 648K (0)|
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 1 | 51 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 4 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | |
| 5 | INDEX FAST FULL SCAN | I_T1_OBJECT_NAME | 1 | 69915 | 2457K| 125 (1)| 00:00:01 | 69915 |00:00:00.02 | 1162 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//可以发现存在外连接。
--//看看转换后的查询语句。
SCOTT@book01p> @ 10053y
TRCLINE
------------------------------------------------------------------------------------------------------------------------
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."OBJECT_NAME" "OBJECT_NAME","T2"."OBJECT_TYPE" "OBJECT_TYPE",
"T2"."OBJECT_NAME" "N1"
FROM "SCOTT"."T1" "T1","SCOTT"."T2" "T2"
WHERE "T2"."OBJECT_TYPE" IS NULL
AND "T1"."OBJECT_NAME" = "T2"."OBJECT_NAME"(+)
AND "T2"."OBJECT_ID"(+) = 1832
--//在连接条件上t2表后面存在加号。
--//顺便提一下，以前在看sql语句，非常害怕看到使用left join,right join的写法，脑子子总是下意识转换成加号的写法，总是心里
--//存在某种抵触。出现left join相当于右边的表存在加号，right join相当于左边的表存在加号。
--//我个人建议开发应该采用加号的写法，实际上oracle的优化器后台还是转化为加号的写法。
4.继续：
--//看看换成substr(t2.object_type,1,4) = 'VIEW'的情况。
SELECT t1.object_name ,t2.object_type ,t2.object_name n1 FROM t1 LEFT JOIN t2 ON t1.object_name = t2.object_name AND t2.object_id = 1832 WHERE substr(t2.object_type,1,4) = 'VIEW';
SCOTT@book01p> @ dpc '' outline ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID faznsk2buh9pg, child number 0
-------------------------------------
SELECT t1.object_name ,t2.object_type ,t2.object_name n1 FROM t1 LEFT
JOIN t2 ON t1.object_name = t2.object_name AND t2.object_id = 1832
WHERE substr(t2.object_type,1,4) = 'VIEW'
Plan hash value: 1687226376
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 127 (100)| | 2 |00:00:00.06 | 468 | | | |
|* 1 | FILTER | | 1 | | | | | 2 |00:00:00.06 | 468 | | | |
|* 2 | HASH JOIN RIGHT OUTER | | 1 | 69915 | 5940K| 127 (1)| 00:00:01 | 69915 |00:00:00.05 | 468 | 1399K| 1399K| 428K (0)|
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 1 | 51 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 4 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | |
| 5 | INDEX FAST FULL SCAN | I_T1_OBJECT_NAME | 1 | 69915 | 2457K| 125 (1)| 00:00:01 | 69915 |00:00:00.01 | 465 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2BFA4EE4
3 - SEL$2BFA4EE4 / "T2"@"SEL$1"
4 - SEL$2BFA4EE4 / "T2"@"SEL$1"
5 - SEL$2BFA4EE4 / "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$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")
INDEX_FFS(@"SEL$2BFA4EE4" "T1"@"SEL$1" ("T1"."OBJECT_NAME"))
INDEX_RS_ASC(@"SEL$2BFA4EE4" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$2BFA4EE4" "T2"@"SEL$1")
LEADING(@"SEL$2BFA4EE4" "T1"@"SEL$1" "T2"@"SEL$1")
USE_HASH(@"SEL$2BFA4EE4" "T2"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$2BFA4EE4" "T2"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUBSTR("T2"."OBJECT_TYPE",1,4)='VIEW')
2 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME")
4 - access("T2"."OBJECT_ID"=1832)
60 rows selected.
--//可以看出选择执行计划不好，选择外连接。
--//你可以认为oracle优化器不够智能，使用函数后无法做出合理的优化。
SCOTT@book01p> @ 10053x faznsk2buh9pg 0
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3803_afaznsk2buh9pg.trc
SCOTT@book01p> @ 10053y
TRCLINE
--------------------------------------------------------------------------
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."OBJECT_NAME" "OBJECT_NAME","T2"."OBJECT_TYPE" "OBJECT_TYPE",
"T2"."OBJECT_NAME" "N1"
FROM "SCOTT"."T1" "T1","SCOTT"."T2" "T2"
WHERE SUBSTR("T2"."OBJECT_TYPE",1,4) = 'VIEW'
AND "T1"."OBJECT_NAME" = "T2"."OBJECT_NAME"(+)
AND "T2"."OBJECT_ID"(+) = 1832
--//转化后外连接存在。
5.可以优化吗？
--//改写使用like.
SELECT t1.object_name
,t2.object_type
,t2.object_name n1
FROM t1
LEFT JOIN t2 ON t1.object_name = t2.object_name AND t2.object_id = 1832
WHERE substr(t2.object_type,1,4) = 'VIEW' and t2.object_type like 'VIEW%';
SELECT t1.object_name
,t2.object_type
,t2.object_name n1
FROM t1
LEFT JOIN t2 ON t1.object_name = t2.object_name AND t2.object_id = 1832
WHERE t2.object_type like 'VIEW%';
SCOTT@book01p> @ dpc '' outline ''
PLAN_TABLE_OUTPUT
------------------------------------------------
SQL_ID g37xffjkkhn18, child number 0
-------------------------------------
SELECT t1.object_name ,t2.object_type ,t2.object_name n1 FROM
t1 LEFT JOIN t2 ON t1.object_name = t2.object_name AND t2.object_id =
1832 WHERE substr(t2.object_type,1,4) = 'VIEW' and t2.object_type like
'VIEW%'
Plan hash value: 1076213087
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 2 |00:00:00.01 | 8 |
| 1 | NESTED LOOPS | | 1 | 1 | 87 | 4 (0)| 00:00:01 | 2 |00:00:00.01 | 8 |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 1 | 51 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | I_T2_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 4 | INDEX RANGE SCAN | I_T1_OBJECT_NAME | 1 | 1 | 36 | 2 (0)| 00:00:01 | 2 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
2 - SEL$683367AA / "T2"@"SEL$1"
3 - SEL$683367AA / "T2"@"SEL$1"
4 - SEL$683367AA / "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$683367AA")
OUTER_JOIN_TO_INNER(@"SEL$2BFA4EE4" "T2"@"SEL$1")
OUTLINE(@"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")
INDEX_RS_ASC(@"SEL$683367AA" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$683367AA" "T2"@"SEL$1")
INDEX(@"SEL$683367AA" "T1"@"SEL$1" ("T1"."OBJECT_NAME"))
LEADING(@"SEL$683367AA" "T2"@"SEL$1" "T1"@"SEL$1")
USE_NL(@"SEL$683367AA" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("T2"."OBJECT_TYPE" LIKE 'VIEW%' AND SUBSTR("T2"."OBJECT_TYPE",1,4)='VIEW'))
3 - access("T2"."OBJECT_ID"=1832)
4 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME")
Note
-----
- this is an adaptive plan
--//可以发现只要添加等价条件t2.object_type like 'VIEW%'，可以选择合理的执行计划.
SCOTT@book01p> @ 10053x g37xffjkkhn18 0
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3803_ag37xffjkkhn18.trc
SCOTT@book01p> @ 10053y
TRCLINE
------------------------------------------------------------------------------------------------------------------------
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."OBJECT_NAME" "OBJECT_NAME","T2"."OBJECT_TYPE" "OBJECT_TYPE",
"T2"."OBJECT_NAME" "N1"
FROM "SCOTT"."T1" "T1","SCOTT"."T2" "T2"
WHERE SUBSTR("T2"."OBJECT_TYPE",1,4) = 'VIEW'
AND "T2"."OBJECT_TYPE" LIKE 'VIEW%'
AND "T1"."OBJECT_NAME" = "T2"."OBJECT_NAME"
AND "T2"."OBJECT_ID" = 1832
--//实际上只要这样写，外连接不存在，直接取消left，right就可以了,这也是我为什么建议采用+的写法的原因。
--//直接写成如下，结果集根本不存在不同。
SELECT t1.object_name
,t2.object_type
,t2.object_name n1
FROM t1,t2
where t1.object_name = t2.object_name AND t2.object_id = 1832 and substr(t2.object_type,1,4) = 'VIEW';
SELECT t1.object_name,t2.object_type,t2.object_name n1
FROM t1
JOIN t2
ON t1.object_name = t2.object_name
AND t2.object_id = 1832
WHERE substr(t2.object_type,1,4) = 'VIEW' ;
--//两者结果集合一样的。
6.进一步探究：
--//为什么我反对left join，right join的写法：
--//假如t2.object_id = 1832 写成 t1.object_id = 1832呢？
--//先在t1.object_id字段建立索引。
SCOTT@book01p> create index i_t1_object_id on t1(object_id);
Index created.
SELECT t1.object_name
,t2.object_type
,t2.object_name n1
FROM t1 left join t2 on
t1.object_name = t2.object_name AND t1.object_id = 1832 where substr(t2.object_type,1,4) = 'VIEW';
SCOTT@book01p> SCOTT@book01p> @ dpc '' outline ''
PLAN_TABLE_OUTPUT
------------------------------------
SQL_ID 4j17m4969tjzw, child number 0
-------------------------------------
SELECT t1.object_name ,t2.object_type ,t2.object_name n1 FROM
t1 left join t2 on t1.object_name = t2.object_name AND t1.object_id =
1832 where substr(t2.object_type,1,4) = 'VIEW'
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 | | | | 1193 (100)| | 1 |00:00:00.04 | 2959 | | | |
|* 1 | FILTER | | 1 | | | | | | 1 |00:00:00.04 | 2959 | | | |
|* 2 | HASH JOIN OUTER | | 1 | 69915 | 5940K| 3624K| 1193 (1)| 00:00:01 | 69916 |00:00:00.04 | 2959 | 7387K| 2994K| 6852K (0)|
| 3 | TABLE ACCESS FULL| T1 | 1 | 69915 | 2799K| | 411 (1)| 00:00:01 | 69915 |00:00:00.01 | 1479 | | | |
| 4 | TABLE ACCESS FULL| T2 | 1 | 69916 | 3140K| | 412 (1)| 00:00:01 | 69916 |00:00:00.01 | 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
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUBSTR("T2"."OBJECT_TYPE",1,4)='VIEW')
2 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME" AND "T1"."OBJECT_ID"=CASE WHEN ("T2"."OBJECT_NAME" IS NOT NULL) THEN 1832 ELSE 1832 END )
55 rows selected.
--//仔细看直接计划，t1根本无法利用object_id的索引。仔细看id2连接条件变成了
--//"T1"."OBJECT_ID"=CASE WHEN ("T2"."OBJECT_NAME" IS NOT NULL) THEN 1832 ELSE 1832 END )
SCOTT@book01p> @ 10053x 4j17m4969tjzw 0
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3803_a4j17m4969tjzw.trc
SCOTT@book01p> @ 10053y
TRCLINE
------------------------------------------------------------------------------------------------------------------------
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."OBJECT_NAME" "OBJECT_NAME","T2"."OBJECT_TYPE" "OBJECT_TYPE",
"T2"."OBJECT_NAME" "N1"
FROM "SCOTT"."T1" "T1","SCOTT"."T2" "T2"
WHERE SUBSTR("T2"."OBJECT_TYPE",1,4) = 'VIEW'
AND "T1"."OBJECT_NAME"= "T2"."OBJECT_NAME"(+)
AND "T1"."OBJECT_ID" = CASE WHEN ("T2"."OBJECT_NAME"(+) IS NOT NULL) THEN 1832 ELSE 1832 END
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//注意看下划线转化后的条件，我敢打赌，如果不看转换，如果不使用left join大部分开发写不出来这样的where语句。
--//更想不到oracle转换的语句是这个样子。
--//oracle的优化器没有错，许多情况下是开发写条件时放错了位置,不应该放在连接条件，而应该放在查询条件。
--//真实的环境应该写成如下：
SELECT t1.object_name
,t2.object_type
,t2.object_name n1
FROM t1 left join t2 on
t1.object_name = t2.object_name where t1.object_id = 1832 and substr(t2.object_type,1,4) = 'VIEW';
Plan hash value: 235837914
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 1 |00:00:00.01 | 10 |
|* 1 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 10 |
| 2 | NESTED LOOPS OUTER | | 1 | 1 | 87 | 5 (0)| 00:00:01 | 2 |00:00:00.01 | 10 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 41 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 4 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 1 | 46 | 3 (0)| 00:00:01 | 2 |00:00:00.01 | 6 |
|* 6 | INDEX RANGE SCAN | I_T2_OBJECT_NAME | 1 | 1 | | 2 (0)| 00:00:01 | 2 |00:00:00.01 | 4 |
----------------------------------------------------------------------------------------------------------------------------------------------------
--//不要以为这类错误不常见，很可能就隐藏在生产系统里面，只不过在where语句里面写了t2表的字段，导致外连接消失，这些错误就被掩盖了。
--//看看如下语句的执行计划：
SELECT t1.object_name
,t2.object_type
,t2.object_name n1
FROM t1 left join t2 on
t1.object_name = t2.object_name AND t1.object_id = 1832 where t2.object_type = 'VIEW';
Plan hash value: 1796329315
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 1 |00:00:00.03 | 9 | 11 |
| 1 | NESTED LOOPS | | 1 | 1 | 87 | 5 (0)| 00:00:01 | 1 |00:00:00.03 | 9 | 11 |
| 2 | NESTED LOOPS | | 1 | 1 | 87 | 5 (0)| 00:00:01 | 2 |00:00:00.02 | 7 | 9 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 41 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 6 |
|* 4 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 5 |
|* 5 | INDEX RANGE SCAN | I_T2_OBJECT_NAME | 1 | 1 | | 2 (0)| 00:00:01 | 2 |00:00:00.01 | 4 | 3 |
|* 6 | TABLE ACCESS BY INDEX ROWID | T2 | 2 | 1 | 46 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 2 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
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 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';
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3116162/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
