-
Notifications
You must be signed in to change notification settings - Fork 0
/
cbo_hj
161 lines (95 loc) · 8.78 KB
/
cbo_hj
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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
哈希连接(HASH JOIN)
两表关联返回少量数据应该走嵌套循环。两表关联返回大量数据应该走HASH连接。
哈希连接的算法：两表等值关联，返回大量数据，将较小的表选为驱动表，将驱动表的select列+join列读入PGA中的work area，然后对驱动表的连接列进行hash运算生成hash table，当驱动表的所有数据完全读入PGA中的work area之后，再读取被驱动表(被驱动表不需要读入PGA中的work area)，对被驱动表的连接列也进行hash运算，然后到PGA中的work area去探测hash table，找到数据就关联上，没找到数据就没关联上。哈希连接只支持等值连接。
在测试账号scott中，运行如下SQL：
SQL> select /*+ gather_plan_statistics use_hash(e,d) */
2 e.ename, e.job, d.dname
3 from emp e, dept d
4 where e.deptno = d.deptno;
......省略输出结果......
运行下面命令获取执行计划：
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 2dj5zrbcps5yu, child number 0
-------------------------------------
select /*+ gather_plan_statistics use_hash(e,d) */ e.ename, e.job,
d.dname from emp e, dept d where e.deptno = d.deptno
Plan hash value: 615168685
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 15 | | | |
|* 1 | HASH JOIN | | 1 | 15 | 14 |00:00:00.01 | 15 | 888K| 888K| 714K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| EMP | 1 | 15 | 14 |00:00:00.01 | 8 | | | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
执行计划中离HASH JOIN关键字最近的表就是驱动表。这里DEPT就是驱动表，EMP就是被驱动表。驱动表DEPT只扫描了一次(Id=2，Starts=1)，被驱动表EMP也只扫描了一次(Id=3,Starts=1)。再次强调，嵌套循环被驱动表会被扫描多次，哈希连接的被驱动表只会被扫描一次。
Used-Mem表示HASH JOIN 消耗了多少PGA，当驱动表太大，PGA不能完全容纳驱动表时，驱动表就会被溢出到临时表空间，进而产生磁盘 HASH JOIN，这时候HASH JOIN性能会严重下降。嵌套循环不需要消耗PGA。
如果 驱动表 超过了 2GB 会产生 on-disk hash join 等待事件 direct path write temp direct path read temp
嵌套循环每循环一次，会将驱动表连接列传值给被驱动表的连接列，也就是说嵌套循环会进行传值。哈希连接没有传值的过程。在进行哈希连接的时候，被驱动表的连接列会生成哈希值，到PGA中去探测驱动表所生成的hash table。哈希连接的驱动表与被驱动表的连接列都不需要创建索引
>>>>nl 驱动表 到 被驱动表 连接列 索引 找 数据
>>>>hash 被驱动表 到 驱动表的 pga 找数据
OLTP环境一般是高并发小事物居多，此类SQL返回结果很少，SQL执行计划多以嵌套循环为主，因此OLTP环境SGA设置较大，PGA设置较小(因为嵌套循环不消耗PGA)，而OLAP环境多数SQL都是大规模的ETL,此类SQL返回结果集很多，SQL执行计划通常以HASH JOIN为主，往往要消耗大量PGA，所以OLAP系统PGA设置较大。
当两表使用外连接进行关联，如果执行计划走的是哈希连接，想要更改驱动表需要使用swap_join_inputs，而不是leading。例如下面SQL：
SQL> explain plan for select /*+ use_hash(d,e) leading(e) */
2 *
3 from dept d
4 left join emp e on d.deptno = e.deptno;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 3713469723
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 812 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO"(+))
从执行计划中看到，dept与emp是采用HASH连接，这说明use_hash(d,e)生效了。执行计划中，驱动表为dept，虽然设置了leading(e)，但是没有生效。现在使用swap_join_inputs来更改外连接中HASH连接的驱动表：
SQL> explain plan for select /*+ use_hash(d,e) swap_join_inputs(e) */
2 *
3 from dept d
4 left join emp e on d.deptno = e.deptno;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 3590956717
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN RIGHT OUTER| | 14 | 812 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO"(+))
15 rows selected.
从执行计划中看到，使用swap_join_inputs更改了外连接中HASH连接的驱动表。
思考：怎么优化HASH连接？
因为HASH连接需要将驱动表的select列+join列放入PGA中，因此，应该尽量避免书写select * from....语句，将需要的列放在select list中，这样可以减少驱动表对PGA的占用，避免驱动表被溢出到临时表空间，从而提升查询性能，如果无法避免驱动表被溢出到临时表空间，可以将临时表空间创建在SSD上或者RAID 0上，加快临时数据的交换速度。
当PGA采用自动管理，单个进程的work area被限制在1G以内，如果是PGA采用手工管理，单个进程的work area不能超过2G，如果驱动表比较大，比如驱动表有4G，可以开启并行查询至少parallel(4)，将表拆分为至少4份，这样每个并行进程中的work area能够容纳1G数据，从而避免驱动表被溢出到临时表空间。如果驱动表非常大，比如有几十个GB，这时开启并行HASH也无能为力，这时应该可以考虑对表进行拆分，在后面的调优技巧章节中，笔者会为各位读者详细介绍表的拆分方法。
1. nl 用来对付 2表关联返回数据少 ， hash 用来对付 2个表关联 返回数据多
2. nl 支持 不等值查询， hash 只能等值查询
3. nl 用于oltp, hash 用于OLAP ， 有些 专业的 用来做 OLAP的数据库反而只有hash + 笛卡尔积
4. NL 被驱动表 要被干 n次 ， hash 被驱动表 只被干 1次
SQL 里面有 GROUP BY , COUNT ,MAX MIN ,....一般走hash（数据量多少决定最终HJ or NL）
>>>判断 2个表 关联 应该走啥 ----看2个表 关联之后 返回的 总行数
TiDB, GP是（纯天然的并行）集群 最怕数据分布不均衡，此外尽量在同一个节点进行关联。
其优化思路：加机器;分部建选技术高的那个。
=====================
排序合并连接(SORT MERGE JOIN)
前文提到哈希连接主要用于处理两表等值关联返回大量数据。
排序合并连接主要用于处理两表非等值关联，比如>，>=，<，<=，<>，但是不能用于instr，substr，like，regexp_like关联，instr，substr，like，regexp_like关联只能走嵌套循环。