dba小白一枚，日常工作之余想继续提升技能，但又因为太懒，进度缓慢，所以在这里希望大家对文章内容进行批评指正的同时，也能对笔者进行督促。如果你觉得内容太简单没啥意思，那........................................你来打我啊，话说回来毕竟小白嘛就要有个小白的样子，你把大佬的活都干了，那大佬干啥，所以初期也遇不到啥特别经典的案例(遇到了也不会)，因此内容大多都是在别人案例的基础上进行性能问题重现，希望通过这种方式对问题以及知识点能有更好的理解与掌握吧。
不多哔哔，正式进入正题。
相信每一个dba都很清楚表连接时的各种连接方式，例如Sort Merge Join，Nested Loops，Hash Join等等，对于特别常见的表连接方式也都知道他们的适用场景及特点，比如走Nested Loops时驱动结果集要小，被驱动表连接列要有索引，走Hash Join时不要求表必须有索引，此时效率大致就是两张表全表扫描的时间......等等，吧啦吧啦说起来头头是道，原理更是一清二楚。但是你有没有想过他们的缺点呢？特别是Hash Join，如果以前你没想过，那么希望你接下来跟随文中的案例想一想，或许你会有更多的不只于此的收获哦。
环境准备
drop table small_table; create table small_table as select * from dba_objects; insert into small_table select * from dba_objects; insert into small_table select * from dba_objects; insert into small_table select * from dba_objects; commit; drop table big_table; create table big_table as select * from dba_objects; insert into big_table select * from dba_objects; insert into big_table select * from dba_objects; insert into big_table select * from dba_objects; insert into big_table select * from dba_objects; insert into big_table select * from dba_objects; insert into big_table select * from dba_objects; insert into big_table select * from dba_objects; commit; exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'SMALL_TABLE',estimate_percent=>100,no_invalidate=>false); exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'BIG_TABLE',estimate_percent=>100,no_invalidate=>false); |
设计sql语句，并确认执行计划走的是hash join
explain plan for select /*+ full(t2) use_hash(t2 t3) leading(t2)*/ t3.* from small_table t2,big_table t3 where t2.object_id=t3.object_id; select * from table(dbms_xplan.display); |
查看该语句正常执行需要多长时间 --80秒左右(别纠结这个时间，纠结你就给我换电脑)
set timing on set autotrace trace select /*+ full(t2) use_hash(t2 t3) leading(t2)*/ t3.* from small_table t2,big_table t3 where t2.object_id=t3.object_id; |
到这里，我们暂且停一停，虽然这条sql很简单，但我们也不妨思考一下，在两张表都没有索引的情况下，上面的sql走hash join的执行计划，似乎就是最优的。
接下来我将对这两张表做一些更新操作：
update small_table set object_id=0 where object_id<70000; update big_table set object_id=0 where object_id<20; commit; |
以上修改完成之后，接下来再次执行前面的sql语句，你觉得会发生什么呢？
set timing on set autotrace trace select /*+ full(t2) use_hash(t2 t3) leading(t2)*/ t3.* from small_table t2,big_table t3 where t2.object_id=t3.object_id; |
不兜圈子，直接上结果，在做了上述更新操作之后再次执行该sql语句，笔者在自己的环境上等了两个小时也没跑完(原执行时间为80秒)，那为什么会这样呢？
到这里，我们不妨来回顾一下hash join的原理：
select * from a ,b where a.id = b.id；--对于这条语句的执行：
|
Hash的目的是为了打散数据到各个桶中，那么再结合前面我所做的更新操作，你是不是已经知道答案了呢？
不知道也没关系，接下来我将跟踪sql的执行过程，看看到底是慢在了哪里，将sql语句重新跑起来，一段时间之后，收集awr报告，报告如下：
可以发现时间基本都消耗在了CPU上，而在集群、IO、并发等环节消耗时间很少。时间都消耗在CPU上那么自然就不会有异常等待事件(on cpu和等待事件是对立的)。
同时在sql语句运行的过程中在其他窗口观察sql的执行进度：
第一个窗口执行：
set autotrace trace select /*+ full(t2) use_hash(t2 t3) leading(t2)*/ t3.* from sm all_table t2,big_table t3 where t2.object_id=t3.object_id 第二个窗口执行：
|
由于我这里数据分布设置的太极端，所以在对大表扫描的时候基本上5分钟左右才能扫描一个块。现在似乎已经找到了问题的点，但为什么这么慢呢？CPU都消耗到哪里去了呢？那么接下来该如何进一步排查呢？如果你发现当sql执行时很慢很慢，并且时间都消耗在CPU上，那么就非常有必要看一看call stack，通过oradebug short_stack，间隔几秒抓取三次：
第三个窗口执行： oradebug setospid &spid oradebug short_stack oradebug short_stack oradebug short_stack |
放大看下上图中标红的部分，发现这个函数最可疑，并且正是与hash join涉及到的bucket相关。
接下来进一步查看该函数的含义：
分析至此，似乎一切都真相大白了， 从hash join 的原理我们知道，在进行hash join关联时，如果有一个bucket中的数据特别多，本次案例中object_id为0所对应的bucket中的记录为26万，那么就意味着被驱动表传到这个bucket的值需要比对26万次，这时候虽然只是一个值的比对，但时间也会很久！而且这种关联字段的比较过程是很消耗CPU的，(因此真实环境中出现这种情况多半表现为sql执行缓慢，并且无异常等待事件，时间都消耗的CPU上)至此从原理上已经对前面的场景有了很充分的解释。
最后总结
大家可能会觉得文章内容很简单没啥东西，的确，文中案例也很好模拟，其实我通过这个性能问题的重现学到更多的是思维方式或者说学习方法的变通，以往我们在学习某一个新技术、新算法的时候，大多都是他是做什么的？怎么实现的？有什么优点？......但是往往我们会忽视他的缺点，但在实际应用过程中很有可能正是命中了这一缺点，从而导致问题的产生。任何算法都不是完美的，当你在思考他的优点时，也希望你尝试着去思考他的缺点，一定会有不一样的收获。
补充知识点
如果想通过v$session_longops 视图查看sql的执行进度，有两个前提条件：
参数timed_statistics或sql_trace必须至少有一个是true
必须用analyzed或dbms_stats包分析过表或其他对象
432
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
