怪异的SQL执行计划

本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

这是道森Oracle 培训班中的一个学生提到的问题，测试了下，发现确实非常之怪异，如下是我的测试过程，大家一起来研究一下。

+++++++++++++   10.2.0.5    ++++++++++++++++
www.killdb.com&gt; show user
USER is "ROGER"
www.killdb.com&gt;CREATE TABLE BIG1 AS SELECT * FROM DBA_OBJECTS;
Table created.
www.killdb.com&gt;CREATE INDEX INDBIG1 ON BIG1(OBJECT_ID);
Index created.
www.killdb.com&gt;CREATE TABLE SMALL1 AS SELECT * FROM                                                                            
2  (SELECT * FROM DBA_OBJECTS ORDER BY DBMS_RANDOM.RANDOM)WHERE ROWNUM &lt; 10;
Table created.
www.killdb.com&gt;CREATE INDEX INDSMALL1 ON SMALL1(OBJECT_ID);
Index created.
www.killdb.com&gt;select count(1) from big1;
COUNT(1)
----------
50738
www.killdb.com&gt;select count(1) from SMALL1;
COUNT(1)
----------
9
www.killdb.com&gt;
www.killdb.com&gt;set autot off
www.killdb.com&gt;exec dbms_stats.gather_table_stats(ownname=&gt; 'ROGER', tabname=&gt; 'BIG1', estimate_percent=&gt;100, method_opt=&gt;'for all indexed columns size 1',cascade=&gt;true);
PL/SQL procedure successfully completed.
www.killdb.com&gt;exec dbms_stats.gather_table_stats(ownname=&gt; 'ROGER', tabname=&gt; 'SMALL1', estimate_percent=&gt;100, method_opt=&gt;'for all indexed columns size 1',cascade=&gt;true);
PL/SQL procedure successfully completed.
www.killdb.com&gt;
www.killdb.com&gt;set autot on
www.killdb.com&gt; 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 (%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")

+++++++++++++ 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)

----------

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');

.......省略执行返回结果

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 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&gt;exec dbms_stats.gather_table_stats('ROGER','BIG1');
PL/SQL procedure successfully completed.
www.killdb.com&gt;exec dbms_stats.gather_table_stats('ROGER','SMALL1');
PL/SQL procedure successfully completed.
www.killdb.com&gt;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&gt;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.

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

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 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&gt;alter session set events '10053 trace name context forever, level 1';
Session altered.
www.killdb.com&gt;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&gt;alter session set events '10053 trace name context off';
Session altered.

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'

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
*/

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

*****************************

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

============

---------------------------------------+-----------------------------------+

---------------------------------------+-----------------------------------+

| 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

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&gt;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&gt;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&gt;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&gt;select ceil(44508345/2398.34881/1000/12) from dual;
CEIL(44508345/2398.34881/1000/12)
---------------------------------
2
www.killdb.com&gt;
www.killdb.com&gt;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&gt;select 196+2 from dual;
196+2
----------
198
www.killdb.com&gt;

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

----------

www.killdb.com>select ceil(44508345/2398.34881/1000/12) from dual;

CEIL(44508345/2398.34881/1000/12)

---------------------------------

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&gt; conn roger/roger
Connected.
SQL&gt; CREATE TABLE BIG1 AS SELECT * FROM DBA_OBJECTS where 1=2;
Table created.
SQL&gt; CREATE TABLE SMALL1 AS SELECT * FROM DBA_OBJECTS where 1=2;
Table created.
SQL&gt; CREATE INDEX INDBIG1 ON BIG1(OBJECT_ID);
Index created.
SQL&gt; CREATE INDEX INDSMALL1 ON SMALL1(OBJECT_ID);
Index created.

++++++++++ 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&gt; exec dbms_stats.gather_table_stats('ROGER','BIG1');
PL/SQL procedure successfully completed.
SQL&gt; exec dbms_stats.gather_table_stats('ROGER','SMALL1');
PL/SQL procedure successfully completed.
SQL&gt; set autot on
SQL&gt; 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 (%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')

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

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 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 -&gt; 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')

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

************************************

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

============

-------------------------------------------------+-----------------------------------+

-------------------------------------------------+-----------------------------------+

| 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

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&gt; alter session set optimizer_features_enable='10.2.0.5';
Session altered.
SQL&gt; 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&gt; 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.

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

---------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------

| 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"

+++ 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&gt;set autot off
www.killdb.com&gt;select * from v$version where rownum &lt; 2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
www.killdb.com&gt; 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&gt;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&gt;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&gt; 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.

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

---------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------

| 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，其执行计划就是不对呢？真是有点匪夷所思了。

大家一起来讨论下这个问题。目前尚未找到根本的原因。

Tags: 10053 event, dbms_stats, filter, NESTED LOOPS SEMI, oracle sql tuning
Posted in tunning on 10月 20, 2014

7 Responses to “怪异的SQL执行计划”

roger Says:

10月 20, 2014 at 下午 1:49

10.2.0.4 ,windows 7 x64测试如下：

SQL> 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: 3425505090 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------ |* 1 | FILTER | | 2 | | 12 |00:00:00.02 | 47640 | | 2 | TABLE ACCESS FULL| BIG1 | 2 | 50330 | 100K|00:00:00.01 | 1396 | |* 3 | FILTER | | 100K| | 12 |00:00:00.10 | 46244 | |* 4 | INDEX RANGE SCAN| INDSMALL1 | 46244 | 1 | 12 |00:00:00.08 | 46244 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter( IS NOT NULL) 3 - filter(:B1='SYS') 4 - access("T1"."OBJECT_ID"=:B1)
ssaer Says:

10月 21, 2014 at 上午 3:35

你偷换了一个概念.把自己弄糊涂了.
在11g中,转换前后的SQL是不等价的.11g中采用了SORT UNIQUE避免了不等价.
roger Says:

10月 21, 2014 at 下午 3:15

11g这里只是对比下，不是重点，你看我最后的测试，都是在10g测试的呀？
ssaer Says:

10月 22, 2014 at 上午 2:21

10g下本来就应该是走semi的. 那样的执行计划是正确的.
11g下应该是优化了算法或者说优化了半连接..
carcase Says:

10月 23, 2014 at 上午 9:32

你的sql是不是写错了

SELECT t.*
FROM BIG1 T
WHERE T.OBJECT_ID IN
(SELECT T1.OBJECT_ID FROM SMALL1 T1 WHERE T1.OWNER = ‘SYS’)
敬勇 Says:

1月 11, 2015 at 上午 4:32

11g中嵌套循环在通过索引访问被驱动表时使用了向量I/O，从你的11g的执行计划中可以看到两次
NESTED LOOPS,而在10g中只有一次NESTED LOOPS.我测试的结果是当在11g中指定/*+ optimizer_features_enable(‘10.2.0.5’) */ Hint后，11g中的执行结果与10g中是一样的。
敬勇 Says:

1月 11, 2015 at 上午 4:43

可能在10g中如果先访问INDSMALL1索引作为驱动结果集，再过次object_id列上的索引访问BIG1表，是通过object_id列上的索引只能执行单块读取，这样计算出来的成本还不如先全表扫描BIG1表，让它作为驱动结果集。而在11g中对于嵌套循环在通过索引访问被驱动表时使用了向量I/O,在先访问INDSMALL1索引作为驱动结果集，再通过索引访问表BIG1时，就可以使用向量I/O。但这只是我的猜测，当在11g将版本降低为10g相同的版本后，执行结果与10g中一致。

You must be logged in to post a comment.

love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

Categories

Archives

最新评论

国内圈子

oracle security

怪异的SQL执行计划

7 Responses to “怪异的SQL执行计划”

Leave a Reply