SEMI-JOIN执行计划突然变成HASH JOIN了 的原因分析

[color=red][b]甲说:[/b][/color]
A B两个表总数据量都很大,在百万以上。
idx1 idx2字段表示是索引字段
A B 两表上都有
col1字段表示普通字段

select xxx from A
where A.idx1 between mmm and nnn
and exists (select 1 from B where B.idx2 = A.col1)

满足A.idx1 between mmm and nnn条件的行一般是几百到几千行,选择性还可以。B表上idx2字段选择性很高。

以前执行计划是 NESTED LOOP-SEMI-JOIN,效率很高。
今天数据库负载突增,最后发现是该语句的执行计划变成 HASH JOIN-SEMI-JOIN,也就意味着对B表进行table access full!

无奈之下,修改查询语句为
... and exists (select /*+ NL_SJ */ 1 from B where...

哪位能指点一下为什么执行计划会变得极差?



[color=red][b]乙说:[/b]从新分析一下表在查询看看 [/color]


[b][color=red]甲说:[/color][/b]
这是未加提示的语句及执行计划,走了HASH JOIN SEMI。[/COLOR]
[PHP]
SQL> explain plan for
2 select count(*)
3 from RECORD_TEMP_A t
4 where 1 = 1
5 and t.datsendtime >= sysdate - 20
6 and t.datsendtime < sysdate - 10
7 AND EXISTS (SELECT 1
8 FROM RECORD_TEMP_B partition(P_MAXVALUE) V
9 WHERE V.MSGID = T.MSGID);

已解释。

SQL> select * from table(dbms_xplan.display());

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 64 | 17103 | | |
| 1 | SORT AGGREGATE | | 1 | 64 | | | |
|* 2 | FILTER | | | | | | |
|* 3 | HASH JOIN SEMI | | 15167 | 947K| 17103 | | |
| 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| RECORD_TEMP_A | 22142 | 648K| 10881 | ROWID | ROW L |
|* 5 | INDEX RANGE SCAN | IDX_REC_TEMP_A_SENDTIME | 39856 | | 464 | | |
|* 6 | TABLE ACCESS FULL | RECORD_TEMP_B | 682K| 22M| 5779 | 24 | 24 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
省略...

Note: cpu costing is off

已选择22行。
[/PHP]


这是加了NL_SJ提示的语句及执行计划,走了NESTED LOOPS SEMI。[/COLOR]
[PHP]
SQL> explain plan for
2 select count(*)
3 from RECORD_TEMP_A t
4 where 1 = 1
5 and t.datsendtime >= sysdate - 20
6 and t.datsendtime < sysdate - 10
7 AND EXISTS (SELECT /*+ NL_SJ */ 1
8 FROM RECORD_TEMP_B partition(P_MAXVALUE) V
9 WHERE V.MSGID = T.MSGID);

已解释。

SQL> select * from table(dbms_xplan.display());

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 64 | 77307 | | |
| 1 | SORT AGGREGATE | | 1 | 64 | | | |
|* 2 | FILTER | | | | | | |
| 3 | NESTED LOOPS SEMI | | 15167 | 947K| 77307 | | |
| 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| RECORD_TEMP_A | 22142 | 648K| 10881 | ROWID | ROW L |
|* 5 | INDEX RANGE SCAN | IDX_REC_TEMP_A_SENDTIME | 39856 | | 464 | | |
|* 6 | TABLE ACCESS BY GLOBAL INDEX ROWID| RECORD_TEMP_B | 467K| 15M| 3 | 24 | 24 |
|* 7 | INDEX RANGE SCAN | IDX_REC_B_MSGID | 1 | | 2 | | |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
省略...

Note: cpu costing is off

已选择23行。

SQL> spool off

[color=green][b]乙说:[/b][/color]

问题应该在这里
TABLE ACCESS BY GLOBAL INDEX ROWID| RECORD_TEMP_A | 22142
|* 5 | INDEX RANGE SCAN | IDX_REC_TEMP_A_SENDTIME | 39856 |

[color=darkred]这里外表评估的cardinality是错误的,跟楼主说的“满足A.idx1 between mmm and nnn条件的行一般是几百到几千行,选择性还可以”严重不符

内表结果集2万多,外表68万,CBO选择hash join无可厚非[/color]
  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值