这是道森Oracle 培训班中的一个学生提到的问题，测试了下，发现确实非常之怪异，如下是我的测试过程，大家一起来研究一下。
这个sql无论是用use_nl还是use_nl+order 的hint都无法改变这个执行计划，下面我们来看下为什么Oracle会走出这样的执行计划。
我怀疑可能跟直方图有关系，收集下直方图，发现一样的现象，如下：
我们可以看到，Oracle仍然是走了nest loop semi，而且驱动表是BIG1,这显然是有问题的。 下面我们用10053 event来跟踪下优化器是如何来评估
出这个执行计划的。
从10053 trace，我们可以看到，本质上Oracle将其进行了查询转换，修改为如下等价语句：
下面我们来看下详细的计算成本：
从上面的信息来看，我们发现几种类型的连接方式的cost 计算如下：
很明显，根据Oracle的计算，认为这种nest loop的成本代价是197.55，也就是198，认为这是效率最高的。
关于这个cost=198，我们可以很容易进行计算，如下：
根据计算最后的IO COST之和确实是196+2=198，和执行计划是一致的。 但是仍然无法解释为什么在10g中Oracle会这样走？
下面我们来看下110203的测试情况。
将10g的2个表的数据导入到11g环境中，然后重新收集统计信息：
我们发现在11gR2环境中，执行计划完全不一样了，虽然也是走的nest loop，对于小表这里进行了index full scan，而大表
这里进行了index range scan，很显然这里是把小表视为驱动表了，这才是正常的执行计划。
下面我们来看下11.2.0.3版本中的10053 event的跟踪trace信息：
根据trace内容，我们知道这几种join方式的成本如下：
仍然是nest loop的成本是最低的，跟10g的类似，然而差异也比较大。
从10053的trace 看不出有什么异常，我将优化器降低到10.2.0.5版本后，测试发现结果居然也不一致：
我们对比10g和11g的执行计划，发现其实10g是先执行owner='SYS'的条件，而11g是进行objecT_id=过滤，如下是两个版本的SQL差异：
我们可以发现，10g中oracle查询转换之后object_id的条件在前，owner条件在后。而在11gR2中版本恰好相反(注意，11g中会多2个列，后面查询时去掉）。
当我将查询改写之后的SQL拿到10g的环境测试，奇怪的事情发生了。
大家可以看到，根据10053 event的trace我们发现10gR2版本中查询改写的SQL，如果拿出来单独执行的话，是ok的。
但是为什么执行原始SQL，其执行计划就是不对呢？ 真是有点匪夷所思了。
大家一起来讨论下这个问题。目前尚未找到根本的原因。
+++++++++++++ 10.2.0.5 ++++++++++++++++
www.killdb.com> show user
USER is "ROGER"
www.killdb.com>CREATE TABLE BIG1 AS SELECT * FROM DBA_OBJECTS;
Table created.
www.killdb.com>CREATE INDEX INDBIG1 ON BIG1(OBJECT_ID);
Index created.
www.killdb.com>CREATE TABLE SMALL1 AS SELECT * FROM
2 (SELECT * FROM DBA_OBJECTS ORDER BY DBMS_RANDOM.RANDOM)WHERE ROWNUM < 10;
Table created.
www.killdb.com>CREATE INDEX INDSMALL1 ON SMALL1(OBJECT_ID);
Index created.
www.killdb.com>select count(1) from big1;
COUNT(1)
----------
50738
www.killdb.com>select count(1) from SMALL1;
COUNT(1)
----------
9
www.killdb.com>
www.killdb.com>set autot off
www.killdb.com>exec dbms_stats.gather_table_stats(ownname=> 'ROGER', tabname=> 'BIG1', estimate_percent=>100, method_opt=>'for all indexed columns size 1',cascade=>true);
PL/SQL procedure successfully completed.
www.killdb.com>exec dbms_stats.gather_table_stats(ownname=> 'ROGER', tabname=> 'SMALL1', estimate_percent=>100, method_opt=>'for all indexed columns size 1',cascade=>true);
PL/SQL procedure successfully completed.
www.killdb.com>
www.killdb.com>set autot on
www.killdb.com> SELECT t.* FROM BIG1 T WHERE T.OBJECT_ID IN(SELECT T1.OBJECT_ID FROM SMALL1 T1 WHERE T.OWNER='SYS');
.......省略执行返回结果
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost ({39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 882 | 198 (2)| 00:00:03 |
| 1 | NESTED LOOPS SEMI | | 9 | 882 | 198 (2)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| BIG1 | 22667 | 2058K| 197 (1)| 00:00:03 |
|* 3 | INDEX RANGE SCAN | INDSMALL1 | 1 | 5 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."OWNER"='SYS')
3 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
这个sql无论是用use_nl还是use_nl+order 的hint都无法改变这个执行计划，下面我们来看下为什么Oracle会走出这样的执行计划。
我怀疑可能跟直方图有关系，收集下直方图，发现一样的现象，如下：
www.killdb.com>exec dbms_stats.gather_table_stats('ROGER','BIG1');
PL/SQL procedure successfully completed.
www.killdb.com>exec dbms_stats.gather_table_stats('ROGER','SMALL1');
PL/SQL procedure successfully completed.
www.killdb.com>select /*+ gather_plan_statistics */ t.* FROM BIG1 T WHERE T.OBJECT_ID IN(SELECT T1.OBJECT_ID FROM SMALL1 T1 WHERE T.OWNER='SYS');
。。。。。。
www.killdb.com>select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
SQL_ID 744z7fvx3unrc, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ t.* FROM BIG1 T WHERE T.OBJECT_ID IN(SELECT
T1.OBJECT_ID FROM SMALL1 T1 WHERE T.OWNER='SYS')
Plan hash value: 856030748
------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | | 10 |00:00:00.04 | 1414 |
| 1 | NESTED LOOPS SEMI | | 2 | 9 | 10 |00:00:00.04 | 1414 |
|* 2 | TABLE ACCESS FULL| BIG1 | 2 | 22667 | 45348 |00:00:00.20 | 1408 |
|* 3 | INDEX RANGE SCAN | INDSMALL1 | 45348 | 1 | 10 |00:00:00.32 | 6 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."OWNER"='SYS')
3 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
22 rows selected.
我们可以看到，Oracle仍然是走了nest loop semi，而且驱动表是BIG1,这显然是有问题的。 下面我们用10053 event来跟踪下优化器是如何来评估
出这个执行计划的。
www.killdb.com>alter session set events '10053 trace name context forever, level 1';
Session altered.
www.killdb.com>explain plan for
2 select /*+ gather_plan_statistics */ t.* FROM BIG1 T WHERE T.OBJECT_ID IN(SELECT T1.OBJECT_ID FROM SMALL1 T1 WHERE T.OWNER='SYS');
Explained.
www.killdb.com>alter session set events '10053 trace name context off';
Session altered.
从10053 trace，我们可以看到，本质上Oracle将其进行了查询转换，修改为如下等价语句：
SELECT "T"."OWNER" "OWNER",
"T"."OBJECT_NAME" "OBJECT_NAME",
"T"."SUBOBJECT_NAME" "SUBOBJECT_NAME",
"T"."OBJECT_ID" "OBJECT_ID",
"T"."DATA_OBJECT_ID" "DATA_OBJECT_ID",
"T"."OBJECT_TYPE" "OBJECT_TYPE",
"T"."CREATED" "CREATED",
"T"."LAST_DDL_TIME" "LAST_DDL_TIME",
"T"."TIMESTAMP" "TIMESTAMP",
"T"."STATUS" "STATUS",
"T"."TEMPORARY" "TEMPORARY",
"T"."GENERATED" "GENERATED",
"T"."SECONDARY" "SECONDARY"
FROM "ROGER"."SMALL1" "T1", "ROGER"."BIG1" "T"
WHERE "T"."OBJECT_ID" = "T1"."OBJECT_ID"
AND "T"."OWNER" = 'SYS'
下面我们来看下详细的计算成本：
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 2398 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: BIG1 Alias: T ++++ 大表的统计信息
#Rows: 50738 #Blks: 719 AvgRowLen: 93.00
Column (#4): OBJECT_ID(NUMBER)
AvgLen: 5.00 NDV: 50738 Nulls: 0 Density: 1.9709e-05 Min: 2 Max: 58844
Index Stats::
Index: INDBIG1 Col#: 4
LVLS: 1 #LB: 112 #DK: 50738 LB/K: 1.00 DB/K: 1.00 CLUF: 1151.00
***********************
Table Stats::
Table: SMALL1 Alias: T1 ++++ 小表的统计信息
#Rows: 9 #Blks: 4 AvgRowLen: 90.00
Column (#4): OBJECT_ID(NUMBER)
AvgLen: 5.00 NDV: 9 Nulls: 0 Density: 0.11111 Min: 2753 Max: 43769
Index Stats::
Index: INDSMALL1 Col#: 4
LVLS: 0 #LB: 1 #DK: 9 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Table: SMALL1 Alias: T1
Card: Original: 9 Rounded: 9 Computed: 9.00 Non Adjusted: 9.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 3.00 Resp: 3.00 Degree: 0
Cost_io: 3.00 Cost_cpu: 30376
Resp_io: 3.00 Resp_cpu: 30376
Access Path: index (index (FFS))
Index: INDSMALL1
resc_io: 2.00 resc_cpu: 8201
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Access Path: index (FFS)
Cost: 2.00 Resp: 2.00 Degree: 1
Cost_io: 2.00 Cost_cpu: 8201
Resp_io: 2.00 Resp_cpu: 8201
Access Path: index (FullScan)
Index: INDSMALL1
resc_io: 1.00 resc_cpu: 8921
ix_sel: 1 ix_sel_with_filters: 1
Cost: 1.00 Resp: 1.00 Degree: 1
Best:: AccessPath: IndexRange Index: INDSMALL1
Cost: 1.00 Degree: 1 Resp: 1.00 Card: 9.00 Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#1): OWNER(VARCHAR2)
AvgLen: 6.00 NDV: 20 Nulls: 0 Density: 9.0106e-05
Histogram: Freq #Bkts: 20 UncompBkts: 5549 EndPtVals: 20
Table: BIG1 Alias: T
Card: Original: 50738 Rounded: 22667 Computed: 22667.06 Non Adjusted: 22667.06
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 196.72 Resp: 196.72 Degree: 0
Cost_io: 196.00 Cost_cpu: 20707995
Resp_io: 196.00 Resp_cpu: 20707995
Best:: AccessPath: TableScan
Cost: 196.72 Degree: 1 Resp: 196.72 Card: 22667.06 Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]: BIG1[T]#0 SMALL1[T1]#1
***************
Now joining: SMALL1[T1]#1
***************
NL Join
Outer table: Card: 22667.06 Cost: 196.72 Resp: 196.72 Degree: 1 Bytes: 93
Inner table: SMALL1 Alias: T1
Access Path: TableScan
NL Join: Cost: 24778.64 Resp: 24778.64 Degree: 1
Cost_io: 24754.00 Cost_cpu: 709235347
Resp_io: 24754.00 Resp_cpu: 709235347
Access Path: index (index (FFS))
Index: INDSMALL1
resc_io: 0.27 resc_cpu: 8201
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Inner table: SMALL1 Alias: T1
Access Path: index (FFS)
NL Join: Cost: 6344.18 Resp: 6344.18 Degree: 1
Cost_io: 6337.00 Cost_cpu: 206610036
Resp_io: 6337.00 Resp_cpu: 206610036
Access Path: index (AllEqJoinGuess)
Index: INDSMALL1
resc_io: 0.00 resc_cpu: 1050
ix_sel: 0.11111 ix_sel_with_filters: 0.11111
NL Join: Cost: 197.55 Resp: 197.55 Degree: 1
Cost_io: 196.00 Cost_cpu: 44508345
Resp_io: 196.00 Resp_cpu: 44508345
Best NL cost: 197.55
resc: 197.55 resc_io: 196.00 resc_cpu: 44508345
resp: 197.55 resp_io: 196.00 resp_cpu: 44508345
Semi Join Card: 9.00 = outer (22667.06) * sel (3.9704e-04)
Join Card - Rounded: 9 Computed: 9.00
SM Join
Outer table:
resc: 196.72 card 22667.06 bytes: 93 deg: 1 resp: 196.72
Inner table: SMALL1 Alias: T1
resc: 1.00 card: 9.00 bytes: 5 deg: 1 resp: 1.00
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 314 Row size: 113 Total Rows: 22667
Initial runs: 2 Merge passes: 1 IO Cost / pass: 172
Total IO sort cost: 486 Total CPU sort cost: 51288534
Total Temp space used: 5727000
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 1 Row size: 16 Total Rows: 9
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 28781471
Total Temp space used: 0
SM join: Resc: 686.50 Resp: 686.50 [multiMatchCost=0.00]
SM cost: 686.50
resc: 686.50 resc_io: 683.00 resc_cpu: 100786922
resp: 686.50 resp_io: 683.00 resp_cpu: 100786922
SM Join (with index on outer)
Access Path: index (FullScan)
Index: INDBIG1
resc_io: 1264.00 resc_cpu: 41473820
ix_sel: 1 ix_sel_with_filters: 1
Cost: 1265.44 Resp: 1265.44 Degree: 1
Outer table:
resc: 1265.44 card 22667.06 bytes: 93 deg: 1 resp: 1265.44
Inner table: SMALL1 Alias: T1
resc: 1.00 card: 9.00 bytes: 5 deg: 1 resp: 1.00
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 1 Row size: 16 Total Rows: 9
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 28781471
Total Temp space used: 0
SM join: Resc: 1267.44 Resp: 1267.44 [multiMatchCost=0.00]
HA Join
Outer table:
resc: 196.72 card 22667.06 bytes: 93 deg: 1 resp: 196.72
Inner table: SMALL1 Alias: T1
resc: 1.00 card: 9.00 bytes: 5 deg: 1 resp: 1.00
using dmeth: 2 #groups: 1
Cost per ptn: 114.66 #ptns: 1
hash_area: 124 (max=819) buildfrag: 291 probefrag: 1 ppasses: 1
Hash join: Resc: 312.38 Resp: 312.38 [multiMatchCost=0.00]
HA Join (swap)
Outer table:
resc: 1.00 card 9.00 bytes: 5 deg: 1 resp: 1.00
Inner table: BIG1 Alias: T
resc: 196.72 card: 22667.06 bytes: 93 deg: 1 resp: 196.72
using dmeth: 2 #groups: 1
Cost per ptn: 0.58 #ptns: 1
hash_area: 60 (max=819) buildfrag: 1 probefrag: 291 ppasses: 1
Hash join: Resc: 198.30 Resp: 198.30 [multiMatchCost=0.00]
HA cost: 198.30
resc: 198.30 resc_io: 197.00 resc_cpu: 37375060
resp: 198.30 resp_io: 197.00 resp_cpu: 37375060
Best:: JoinMethod: NestedLoopSemi
Cost: 197.55 Degree: 1 Resp: 197.55 Card: 9.00 Bytes: 98
***********************
Best so far: Table#: 0 cost: 196.7195 card: 22667.0575 bytes: 2108031
Table#: 1 cost: 197.5465 card: 8.9996 bytes: 882
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000
*********************************
Number of join permutations tried: 1
*********************************
(newjo-save) [0 1 ]
Final - All Rows Plan: Best join order: 1
Cost: 197.5465 Degree: 1 Card: 9.0000 Bytes: 882
Resc: 197.5465 Resc_io: 196.0000 Resc_cpu: 44508345
Resp: 197.5465 Resp_io: 196.0000 Resc_cpu: 44508345
kkoipt: Query block SEL$5DA710D3 (#1)
============
Plan Table
============
---------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 198 | |
| 1 | NESTED LOOPS SEMI | | 9 | 891 | 198 | 00:00:03 |
| 2 | TABLE ACCESS FULL | BIG1 | 22K | 2061K | 197 | 00:00:03 |
| 3 | INDEX RANGE SCAN | INDSMALL1| 1 | 5 | 0 | |
---------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("T"."OWNER"='SYS')
3 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
Content of other_xml column
===========================
db_version : 10.2.0.5
parse_schema : ROGER
plan_hash : 856030748
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$5DA710D3" "T"@"SEL$1")
INDEX(@"SEL$5DA710D3" "T1"@"SEL$2" ("SMALL1"."OBJECT_ID"))
LEADING(@"SEL$5DA710D3" "T"@"SEL$1" "T1"@"SEL$2")
USE_NL(@"SEL$5DA710D3" "T1"@"SEL$2")
END_OUTLINE_DATA
*/
从上面的信息来看，我们发现几种类型的连接方式的cost 计算如下：
hash join：
Hash join: Resc: 312.38 Resp: 312.38 [multiMatchCost=0.00]
hash join(swap):
Hash join: Resc: 198.30 Resp: 198.30 [multiMatchCost=0.00]
sort merge join：
SM join: Resc: 1267.44 Resp: 1267.44 [multiMatchCost=0.00]
Nest loop：
Best:: JoinMethod: NestedLoopSemi
Cost: 197.55 Degree: 1 Resp: 197.55 Card: 9.00 Bytes: 98
很明显，根据Oracle的计算，认为这种nest loop的成本代价是197.55，也就是198，认为这是效率最高的。
关于这个cost=198，我们可以很容易进行计算，如下：
www.killdb.com>select OPERATION,OPTIONS,IO_COST,CPU_COST FROM PLAN_TABLE;
OPERATION OPTIONS IO_COST CPU_COST
---------------------------------------- ------------------------------ ---------- ----------
SELECT STATEMENT 196 44508345
NESTED LOOPS SEMI 196 44508345
TABLE ACCESS FULL 196 20707995
INDEX RANGE SCAN 0 1050
www.killdb.com>select pname, pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';
PNAME PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW 2398.34881
IOSEEKTIM 10
IOTFRSPEED 4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM
9 rows selected.
www.killdb.com>select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
2 (select value from v$parameter where name = 'db_block_size') /
3 (select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "sreadtim"
4 from dual;
sreadtim
----------
12
www.killdb.com>select ceil(44508345/2398.34881/1000/12) from dual;
CEIL(44508345/2398.34881/1000/12)
---------------------------------
2
www.killdb.com>
www.killdb.com>select ceil(719/8*26/12+20707995/2398.34881/12/719) from dual;
CEIL(719/8*26/12+20707995/2398.34881/12/719)
--------------------------------------------
196
www.killdb.com>select 196+2 from dual;
196+2
----------
198
www.killdb.com>
根据计算最后的IO COST之和确实是196+2=198，和执行计划是一致的。 但是仍然无法解释为什么在10g中Oracle会这样走？
下面我们来看下110203的测试情况。
++++++++++ 11.2.0.3 +++++++++++++
SQL> conn roger/roger
Connected.
SQL> CREATE TABLE BIG1 AS SELECT * FROM DBA_OBJECTS where 1=2;
Table created.
SQL> CREATE TABLE SMALL1 AS SELECT * FROM DBA_OBJECTS where 1=2;
Table created.
SQL> CREATE INDEX INDBIG1 ON BIG1(OBJECT_ID);
Index created.
SQL> CREATE INDEX INDSMALL1 ON SMALL1(OBJECT_ID);
Index created.
将10g的2个表的数据导入到11g环境中，然后重新收集统计信息：
SQL> exec dbms_stats.gather_table_stats('ROGER','BIG1');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('ROGER','SMALL1');
PL/SQL procedure successfully completed.
SQL> set autot on
SQL> SELECT t.* FROM BIG1 T WHERE T.OBJECT_ID IN(SELECT T1.OBJECT_ID FROM SMALL1 T1 WHERE T.OWNER='SYS')
2 /
........
Execution Plan
----------------------------------------------------------
Plan hash value: 3321828108
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost ({39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 918 | 7 (15)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 9 | 918 | 7 (15)| 00:00:01 |
| 3 | SORT UNIQUE | | 9 | 45 | 1 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | INDSMALL1 | 9 | 45 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | INDBIG1 | 1 | | 1 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| BIG1 | 1 | 97 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
6 - filter("T"."OWNER"='SYS')
我们发现在11gR2环境中，执行计划完全不一样了，虽然也是走的nest loop，对于小表这里进行了index full scan，而大表
这里进行了index range scan，很显然这里是把小表视为驱动表了，这才是正常的执行计划。
下面我们来看下11.2.0.3版本中的10053 event的跟踪trace信息：
************************************
Cost-based predicate pushdown (JPPD)
************************************
.......
SELECT "T"."OWNER" "OWNER",
"T"."OBJECT_NAME" "OBJECT_NAME",
"T"."SUBOBJECT_NAME" "SUBOBJECT_NAME",
"T"."OBJECT_ID" "OBJECT_ID",
"T"."DATA_OBJECT_ID" "DATA_OBJECT_ID",
"T"."OBJECT_TYPE" "OBJECT_TYPE",
"T"."CREATED" "CREATED",
"T"."LAST_DDL_TIME" "LAST_DDL_TIME",
"T"."TIMESTAMP" "TIMESTAMP",
"T"."STATUS" "STATUS",
"T"."TEMPORARY" "TEMPORARY",
"T"."GENERATED" "GENERATED",
"T"."SECONDARY" "SECONDARY",
"T"."NAMESPACE" "NAMESPACE",
"T"."EDITION_NAME" "EDITION_NAME"
FROM "ROGER"."SMALL1" "T1", "ROGER"."BIG1" "T"
WHERE "T"."OWNER" = 'SYS'
AND "T"."OBJECT_ID" = "T1"."OBJECT_ID"
****************
QUERY BLOCK TEXT
****************
SELECT t.* FROM BIG1 T WHERE T.OBJECT_ID IN(SELECT T1.OBJECT_ID FROM SMALL1 T1 WHERE T.OWNER='SYS')
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$5DA710D3 nbfros=2 flg=0
fro(0): flg=0 objn=77542 hint_alias="T"@"SEL$1"
fro(1): flg=0 objn=77543 hint_alias="T1"@"SEL$2"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 1752 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: NO VALUE blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: BIG1 Alias: T
#Rows: 50738 #Blks: 748 AvgRowLen: 94.00 ChainCnt: 0.00
Column (#4): OBJECT_ID(
AvgLen: 5 NDV: 50738 Nulls: 0 Density: 0.000020 Min: 2 Max: 58844
Index Stats::
Index: INDBIG1 Col#: 4
LVLS: 1 #LB: 112 #DK: 50738 LB/K: 1.00 DB/K: 1.00 CLUF: 1147.00
***********************
Table Stats::
Table: SMALL1 Alias: T1
#Rows: 9 #Blks: 5 AvgRowLen: 91.00 ChainCnt: 0.00
Column (#4): OBJECT_ID(
AvgLen: 5 NDV: 9 Nulls: 0 Density: 0.111111 Min: 2753 Max: 43769
Index Stats::
Index: INDSMALL1 Col#: 4
LVLS: 0 #LB: 1 #DK: 9 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
Access path analysis for SMALL1
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for SMALL1[T1]
Table: SMALL1 Alias: T1
Card: Original: 9.000000 Rounded: 9 Computed: 9.00 Non Adjusted: 9.00
Access Path: TableScan
Cost: 3.00 Resp: 3.00 Degree: 0
Cost_io: 3.00 Cost_cpu: 37497
Resp_io: 3.00 Resp_cpu: 37497
Access Path: index (index (FFS))
Index: INDSMALL1
resc_io: 2.00 resc_cpu: 8201
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 2.00 Resp: 2.00 Degree: 1
Cost_io: 2.00 Cost_cpu: 8201
Resp_io: 2.00 Resp_cpu: 8201
Access Path: index (FullScan)
Index: INDSMALL1
resc_io: 1.00 resc_cpu: 8921
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 1.00 Resp: 1.00 Degree: 1
Best:: AccessPath: IndexRange
Index: INDSMALL1
Cost: 1.00 Degree: 1 Resp: 1.00 Card: 9.00 Bytes: 0
Access path analysis for BIG1
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for BIG1[T]
Column (#1):
NewDensity:0.000093, OldDensity:0.000010 BktCnt:5405, PopBktCnt:5400, PopValCnt:18, NDV:25
Column (#1): OWNER(
AvgLen: 6 NDV: 25 Nulls: 0 Density: 0.000093
Histogram: Freq #Bkts: 23 UncompBkts: 5405 EndPtVals: 23
Table: BIG1 Alias: T
Card: Original: 50738.000000 Rounded: 22454 Computed: 22454.26 Non Adjusted: 22454.26
Access Path: TableScan
Cost: 205.04 Resp: 205.04 Degree: 0
Cost_io: 204.00 Cost_cpu: 21761557
Resp_io: 204.00 Resp_cpu: 21761557
Best:: AccessPath: TableScan
Cost: 205.04 Degree: 1 Resp: 205.04 Card: 22454.26 Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: BIG1[T]#0 SMALL1[T1]#1
***************
Now joining: SMALL1[T1]#1
***************
NL Join
Outer table: Card: 22454.26 Cost: 205.04 Resp: 205.04 Degree: 1 Bytes: 94
Access path analysis for SMALL1
Inner table: SMALL1 Alias: T1
Access Path: TableScan
NL Join: Cost: 30653.09 Resp: 30653.09 Degree: 1
Cost_io: 30612.00 Cost_cpu: 863723686
Resp_io: 30612.00 Resp_cpu: 863723686
Access Path: index (index (FFS))
Index: INDSMALL1
resc_io: 0.27 resc_cpu: 8201
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Inner table: SMALL1 Alias: T1
Access Path: index (FFS)
NL Join: Cost: 6296.80 Resp: 6296.80 Degree: 1
Cost_io: 6287.00 Cost_cpu: 205916691
Resp_io: 6287.00 Resp_cpu: 205916691
Access Path: index (AllEqJoinGuess)
Index: INDSMALL1
resc_io: 0.00 resc_cpu: 1050
ix_sel: 0.111111 ix_sel_with_filters: 0.111111
NL Join : Cost: 206.60 Resp: 206.60 Degree: 1
Cost_io: 205.00 Cost_cpu: 33549907
Resp_io: 205.00 Resp_cpu: 33549907
Best NL cost: 206.60
resc: 206.60 resc_io: 205.00 resc_cpu: 33549907
resp: 206.60 resp_io: 205.00 resc_cpu: 33549907
Semi Join Card: 8.999705 = outer (22454.263830) * sel (0.000401)
Join Card - Rounded: 9 Computed: 9.00
Outer table: BIG1 Alias: T
resc: 205.04 card 22454.26 bytes: 94 deg: 1 resp: 205.04
Inner table: SMALL1 Alias: T1
resc: 1.00 card: 9.00 bytes: 5 deg: 1 resp: 1.00
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 324 Area size: 284672 Max Area size: 57041920
Degree: 1
Blocks to Sort: 314 Row size: 114 Total Rows: 22454
Initial runs: 2 Merge passes: 1 IO Cost / pass: 172
Total IO sort cost: 486 Total CPU sort cost: 43376827
Total Temp space used: 5858000
SORT ressource Sort statistics
Sort width: 324 Area size: 284672 Max Area size: 57041920
Degree: 1
Blocks to Sort: 1 Row size: 16 Total Rows: 9
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 21022391
Total Temp space used: 0
SM join: Resc: 695.10 Resp: 695.10 [multiMatchCost=0.00]
SM Join
SM cost: 695.10
resc: 695.10 resc_io: 691.00 resc_cpu: 86165236
resp: 695.10 resp_io: 691.00 resp_cpu: 86165236
SM Join (with index on outer)
Access Path: index (FullScan)
Index: INDBIG1
resc_io: 1260.00 resc_cpu: 43474854
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 631.03 Resp: 631.03 Degree: 1
Outer table: BIG1 Alias: T
resc: 631.03 card 22454.26 bytes: 94 deg: 1 resp: 631.03
Inner table: SMALL1 Alias: T1
resc: 1.00 card: 9.00 bytes: 5 deg: 1 resp: 1.00
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 324 Area size: 284672 Max Area size: 57041920
Degree: 1
Blocks to Sort: 1 Row size: 16 Total Rows: 9
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 21022391
Total Temp space used: 0
SM join: Resc: 633.03 Resp: 633.03 [multiMatchCost=0.00]
Outer table: BIG1 Alias: T
resc: 205.04 card 22454.26 bytes: 94 deg: 1 resp: 205.04
Inner table: SMALL1 Alias: T1
resc: 1.00 card: 9.00 bytes: 5 deg: 1 resp: 1.00
using dmeth: 2 #groups: 1
Cost per ptn: 114.72 #ptns: 1
hash_area: 124 (max=13927) buildfrag: 291 probefrag: 1 ppasses: 1
Hash join: Resc: 320.75 Resp: 320.75 [multiMatchCost=0.00]
Outer table: SMALL1 Alias: T1
resc: 1.00 card 9.00 bytes: 5 deg: 1 resp: 1.00
Inner table: BIG1 Alias: T
resc: 205.04 card: 22454.26 bytes: 94 deg: 1 resp: 205.04
using dmeth: 2 #groups: 1
Cost per ptn: 0.61 #ptns: 1
hash_area: 124 (max=13927) buildfrag: 1 probefrag: 291 ppasses: 1
Hash join: Resc: 206.64 Resp: 206.64 [multiMatchCost=0.00]
HA Join
HA cost: 206.64 swapped
resc: 206.64 resc_io: 205.00 resc_cpu: 34523321
resp: 206.64 resp_io: 205.00 resp_cpu: 34523321
Best:: JoinMethod: NestedLoopSemi
Cost: 206.60 Degree: 1 Resp: 206.60 Card: 9.00 Bytes: 99
***********************
Best so far: Table#: 0 cost: 205.0352 card: 22454.2638 bytes: 2110676
Table#: 1 cost: 206.5960 card: 8.9997 bytes: 891
***********************
Join order[2]: SMALL1[T1]#1 BIG1[T]#0
SORT ressource Sort statistics
Sort width: 324 Area size: 284672 Max Area size: 57041920
Degree: 1
Blocks to Sort: 1 Row size: 16 Total Rows: 9
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 21022391
Total Temp space used: 0
***************
Now joining: BIG1[T]#0
***************
NL Join
Outer table: Card: 9.00 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 5
Access path analysis for BIG1
Inner table: BIG1 Alias: T
Access Path: TableScan
NL Join: Cost: 1022.58 Resp: 1022.58 Degree: 1
Cost_io: 1016.00 Cost_cpu: 138319837
Resp_io: 1016.00 Resp_cpu: 138319837
Access Path: index (AllEqJoinGuess)
Index: INDBIG1
resc_io: 2.00 resc_cpu: 15773
ix_sel: 0.000020 ix_sel_with_filters: 0.000020
NL Join (ordered): Cost: 7.00 Resp: 7.00 Degree: 1
Cost_io: 6.00 Cost_cpu: 21066284
Resp_io: 6.00 Resp_cpu: 21066284
Best NL cost: 7.00
resc: 7.00 resc_io: 6.00 resc_cpu: 21066284
resp: 7.00 resp_io: 6.00 resc_cpu: 21066284
Join Card: 8.999705 = outer (9.000000) * inner (22454.263830) * sel (0.000045)
Join Card - Rounded: 9 Computed: 9.00
Outer table: SMALL1 Alias: T1
resc: 2.00 card 9.00 bytes: 5 deg: 1 resp: 2.00
Inner table: BIG1 Alias: T
resc: 205.04 card: 22454.26 bytes: 94 deg: 1 resp: 205.04
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 324 Area size: 284672 Max Area size: 57041920
Degree: 1
Blocks to Sort: 314 Row size: 114 Total Rows: 22454
Initial runs: 2 Merge passes: 1 IO Cost / pass: 172
Total IO sort cost: 486 Total CPU sort cost: 43376827
Total Temp space used: 5858000
SM join: Resc: 695.10 Resp: 695.10 [multiMatchCost=0.00]
SM Join
SM cost: 695.10
resc: 695.10 resc_io: 691.00 resc_cpu: 86165236
resp: 695.10 resp_io: 691.00 resp_cpu: 86165236
Outer table: SMALL1 Alias: T1
resc: 2.00 card 9.00 bytes: 5 deg: 1 resp: 2.00
Inner table: BIG1 Alias: T
resc: 205.04 card: 22454.26 bytes: 94 deg: 1 resp: 205.04
using dmeth: 2 #groups: 1
Cost per ptn: 0.61 #ptns: 1
hash_area: 124 (max=13927) buildfrag: 1 probefrag: 291 ppasses: 1
Hash join: Resc: 207.64 Resp: 207.64 [multiMatchCost=0.00]
HA Join
HA cost: 207.64
resc: 207.64 resc_io: 205.00 resc_cpu: 55545112
resp: 207.64 resp_io: 205.00 resp_cpu: 55545112
Best:: JoinMethod: NestedLoop
Cost: 7.00 Degree: 1 Resp: 7.00 Card: 9.00 Bytes: 99
***********************
Best so far: Table#: 1 cost: 2.0003 card: 9.0000 bytes: 25
Table#: 0 cost: 7.0021 card: 8.9997 bytes: 891
***********************
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
*********************************
Number of join permutations tried: 2
*********************************
SORT ressource Sort statistics
Sort width: 324 Area size: 284672 Max Area size: 57041920
Degree: 1
Blocks to Sort: 1 Row size: 16 Total Rows: 5
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 21021629
Total Temp space used: 0
Consider using bloom filter between T1[SMALL1] and T[BIG1] with ??
kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000) rejected because not a hash join
Enumerating distribution method (advanced)
--- Distribution method for
join between T1[SMALL1](serial) and T[BIG1](serial); jm = 12; right side access path = IndexRange
---- NLJ default -> BROADCAST-LEFT
(newjo-save) [1 0 ]
Trying or-Expansion on query block SEL$5DA710D3 (#1)
Transfer Optimizer annotations for query block SEL$5DA710D3 (#1)
id=0 frofkks[i] (index start key) predicate="T"."OBJECT_ID"="T1"."OBJECT_ID"
id=0 frofkke[i] (index stop key) predicate="T"."OBJECT_ID"="T1"."OBJECT_ID"
id=0 frofand predicate="T"."OWNER"='SYS'
Final cost for query block SEL$5DA710D3 (#1) - All Rows Plan:
Best join order: 2
Cost: 7.0021 Degree: 1 Card: 9.0000 Bytes: 891
Resc: 7.0021 Resc_io: 6.0000 Resc_cpu: 21066284
Resp: 7.0021 Resp_io: 6.0000 Resc_cpu: 21066284
。。。。。。
SELECT t.* FROM BIG1 T WHERE T.OBJECT_ID IN(SELECT T1.OBJECT_ID FROM SMALL1 T1 WHERE T.OWNER='SYS')
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
-------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 7 | |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 9 | 891 | 7 | 00:00:01 |
| 3 | SORT UNIQUE | | 9 | 45 | 1 | 00:00:01 |
| 4 | INDEX FULL SCAN | INDSMALL1| 9 | 45 | 1 | 00:00:01 |
| 5 | INDEX RANGE SCAN | INDBIG1 | 1 | | 1 | 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | BIG1 | 1 | 94 | 1 | 00:00:01 |
-------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
5 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
6 - filter("T"."OWNER"='SYS')
根据trace内容，我们知道这几种join方式的成本如下：
sort merge join：
SM join: Resc: 695.10 Resp: 695.10 [multiMatchCost=0.00]
hash join：
Hash join: Resc: 207.64 Resp: 207.64 [multiMatchCost=0.00]
Nest loop：
Cost: 7.00 Degree: 1 Resp: 7.00 Card: 9.00 Bytes: 99
仍然是nest loop的成本是最低的，跟10g的类似，然而差异也比较大。
从10053的trace 看不出有什么异常，我将优化器降低到10.2.0.5版本后，测试发现结果居然也不一致：
SQL> alter session set optimizer_features_enable='10.2.0.5';
Session altered.
SQL> select /*+ gather_plan_statistics */ t.* FROM BIG1 T WHERE T.OBJECT_ID IN(SELECT T1.OBJECT_ID FROM SMALL1 T1 WHERE T.OWNER='SYS');
.......省略执行返回结果
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 744z7fvx3unrc, child number 2
-------------------------------------
select /*+ gather_plan_statistics */ t.* FROM BIG1 T WHERE T.OBJECT_ID
IN(SELECT T1.OBJECT_ID FROM SMALL1 T1 WHERE T.OWNER='SYS')
Plan hash value: 3425505090
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:00.01 | 23412 | 732 |
|* 1 | FILTER | | 1 | | 5 |00:00:00.01 | 23412 | 732 |
| 2 | TABLE ACCESS FULL| BIG1 | 1 | 50738 | 50738 |00:00:00.01 | 738 | 732 |
|* 3 | FILTER | | 50738 | | 5 |00:00:00.04 | 22674 | 0 |
|* 4 | INDEX RANGE SCAN| INDSMALL1 | 22674 | 1 | 5 |00:00:00.03 | 22674 | 0 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - filter(:B1='SYS')
4 - access("T1"."OBJECT_ID"=:B1)
24 rows selected.
我们对比10g和11g的执行计划，发现其实10g是先执行owner='SYS'的条件，而11g是进行objecT_id=过滤，如下是两个版本的SQL差异：
+++ 10.2.0.5
SELECT "T"."OWNER" "OWNER",
"T"."OBJECT_NAME" "OBJECT_NAME",
"T"."SUBOBJECT_NAME" "SUBOBJECT_NAME",
"T"."OBJECT_ID" "OBJECT_ID",
"T"."DATA_OBJECT_ID" "DATA_OBJECT_ID",
"T"."OBJECT_TYPE" "OBJECT_TYPE",
"T"."CREATED" "CREATED",
"T"."LAST_DDL_TIME" "LAST_DDL_TIME",
"T"."TIMESTAMP" "TIMESTAMP",
"T"."STATUS" "STATUS",
"T"."TEMPORARY" "TEMPORARY",
"T"."GENERATED" "GENERATED",
"T"."SECONDARY" "SECONDARY"
FROM "ROGER"."SMALL1" "T1", "ROGER"."BIG1" "T"
WHERE "T"."OBJECT_ID" = "T1"."OBJECT_ID"
AND "T"."OWNER" = 'SYS'
++++11.2.0.3
SELECT "T"."OWNER" "OWNER",
"T"."OBJECT_NAME" "OBJECT_NAME",
"T"."SUBOBJECT_NAME" "SUBOBJECT_NAME",
"T"."OBJECT_ID" "OBJECT_ID",
"T"."DATA_OBJECT_ID" "DATA_OBJECT_ID",
"T"."OBJECT_TYPE" "OBJECT_TYPE",
"T"."CREATED" "CREATED",
"T"."LAST_DDL_TIME" "LAST_DDL_TIME",
"T"."TIMESTAMP" "TIMESTAMP",
"T"."STATUS" "STATUS",
"T"."TEMPORARY" "TEMPORARY",
"T"."GENERATED" "GENERATED",
"T"."SECONDARY" "SECONDARY",
"T"."NAMESPACE" "NAMESPACE",
"T"."EDITION_NAME" "EDITION_NAME"
FROM "ROGER"."SMALL1" "T1", "ROGER"."BIG1" "T"
WHERE "T"."OWNER" = 'SYS'
AND "T"."OBJECT_ID" = "T1"."OBJECT_ID"
我们可以发现，10g中oracle查询转换之后object_id的条件在前，owner条件在后。而在11gR2中版本恰好相反(注意，11g中会多2个列，后面查询时去掉）。
当我将查询改写之后的SQL拿到10g的环境测试，奇怪的事情发生了。
www.killdb.com>set autot off
www.killdb.com>select * from v$version where rownum < 2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
www.killdb.com> SELECT /*+ gather_plan_statistics */ T.OWNER OWNER,
2 T.OBJECT_NAME OBJECT_NAME,
3 T.SUBOBJECT_NAME SUBOBJECT_NAME,
4 T.OBJECT_ID OBJECT_ID,
5 T.DATA_OBJECT_ID DATA_OBJECT_ID,
6 T.OBJECT_TYPE OBJECT_TYPE,
7 T.CREATED CREATED,
8 T.LAST_DDL_TIME LAST_DDL_TIME,
9 T.TIMESTAMP TIMESTAMP,
10 T.STATUS STATUS,
11 T.TEMPORARY TEMPORARY,
12 T.GENERATED GENERATED,
13 T.SECONDARY SECONDARY
14 FROM ROGER.SMALL1 T1, ROGER.BIG1 T
15 WHERE T.OBJECT_ID = T1.OBJECT_ID
16 AND T.OWNER = 'SYS'
17 /
.............
www.killdb.com>select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS'));
.....
Plan hash value: 2084139354
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 | | 0 |00:00:00.01 | 0 |
|* 1 | TABLE ACCESS BY INDEX ROWID| BIG1 | 2 | 1 | 10 |00:00:00.01 | 46 |
| 2 | NESTED LOOPS | | 2 | 9 | 38 |00:00:00.01 | 28 |
| 3 | INDEX FULL SCAN | INDSMALL1 | 2 | 9 | 18 |00:00:00.01 | 4 |
|* 4 | INDEX RANGE SCAN | INDBIG1 | 18 | 1 | 18 |00:00:00.01 | 24 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OWNER"='SYS')
4 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
28 rows selected.
www.killdb.com>SELECT /*+ gather_plan_statistics */T.OWNER OWNER,
2 T.OBJECT_NAME OBJECT_NAME,
3 T.SUBOBJECT_NAME SUBOBJECT_NAME,
4 T.OBJECT_ID OBJECT_ID,
5 T.DATA_OBJECT_ID DATA_OBJECT_ID,
6 T.OBJECT_TYPE OBJECT_TYPE,
7 T.CREATED CREATED,
8 T.LAST_DDL_TIME LAST_DDL_TIME,
9 T.TIMESTAMP TIMESTAMP,
10 T.STATUS STATUS,
11 T.TEMPORARY TEMPORARY,
12 T.GENERATED GENERATED,
13 T.SECONDARY SECONDARY
14 FROM ROGER.SMALL1 T1, ROGER.BIG1 T
15 WHERE T.OWNER = 'SYS'
16 AND T.OBJECT_ID = T1.OBJECT_ID
17 /
........
www.killdb.com> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS'));
.......
Plan hash value: 2084139354
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 | | 0 |00:00:00.01 | 0 |
|* 1 | TABLE ACCESS BY INDEX ROWID| BIG1 | 1 | 1 | 5 |00:00:00.01 | 23 |
| 2 | NESTED LOOPS | | 1 | 9 | 19 |00:00:00.01 | 14 |
| 3 | INDEX FULL SCAN | INDSMALL1 | 1 | 9 | 9 |00:00:00.01 | 2 |
|* 4 | INDEX RANGE SCAN | INDBIG1 | 9 | 1 | 9 |00:00:00.01 | 12 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OWNER"='SYS')
4 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
28 rows selected.
大家可以看到，根据10053 event的trace我们发现10gR2版本中查询改写的SQL，如果拿出来单独执行的话，是ok的。
但是为什么执行原始SQL，其执行计划就是不对呢？ 真是有点匪夷所思了。
大家一起来讨论下这个问题。目前尚未找到根本的原因。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
