- 最后登录
- 2023-8-16
- 在线时间
- 1686 小时
- 威望
- 2135
- 金钱
- 50532
- 注册时间
- 2011-10-12
- 阅读权限
- 200
- 帖子
- 5207
- 精华
- 39
- 积分
- 2135
- UID
- 2
|
1#
发表于 2013-1-9 22:07:29
|
查看: 4139 |
回复: 1
简述一个HASH JOIN的处理过程- SQL> select * from v$version;
- BANNER
- ----------------------------------------------------------------
- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
- PL/SQL Release 10.2.0.5.0 - Production
- CORE 10.2.0.5.0 Production
- TNS for Linux: Version 10.2.0.5.0 - Production
- NLSRTL Version 10.2.0.5.0 - Production
-
- alter system flush buffer_cache;
-
- alter session set events '10104 trace name context forever, level 2 : 10046 trace name context forever,level 8';
- select /*+ USE_HASH(E D) */ D.LOC
- from scott.emp E,scott.dept D
- where
- E.SAL>=200 and E.DEPTNO=D.DEPTNO;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 615168685
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 14 | 252 | 7 (15)| 00:00:01 |
- |* 1 | HASH JOIN | | 14 | 252 | 7 (15)| 00:00:01 |
- | 2 | TABLE ACCESS FULL| DEPT | 4 | 44 | 3 (0)| 00:00:01 |
- |* 3 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - access("E"."DEPTNO"="D"."DEPTNO")
- 3 - filter("E"."SAL">=200)
-
-
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 15 consistent gets
- 0 physical reads
- 0 redo size
- 712 bytes sent via SQL*Net to client
- 492 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 14 rows processed
- 1* select object_name from dba_objects where object_id=51573
- SQL> /
- OBJECT_NAME
- --------------------------------------------------------------------------------
- DEPT
- SQL> select object_name from dba_objects where object_id=51575;
- OBJECT_NAME
- --------------------------------------------------------------------------------
- EMP
- SQL> select count(*) from scott.dept;
- COUNT(*)
- ----------
- 4
-
-
- 执行步骤:
-
- 1.
-
- WAIT #1: nam='db file sequential read' ela= 48 file#=4 block#=11 blocks=1 obj#=51573 tim=1325917491675207
- WAIT #1: nam='db file scattered read' ela= 75 file#=4 block#=12 blocks=5 obj#=51573 tim=1325917491675589
- 51573 =》51573 先对DEPT表(BUILD TABLE)做全表扫描,无过滤和access, 如ID2
- 2.
- HASH JOIN BUILD HASH TABLE (PHASE 1) ***
- DEPT 共4行数据,共分成8个bucket
- 3,
- WAIT #1: nam='db file sequential read' ela= 32 file#=4 block#=27 blocks=1 obj#=51575 tim=1325917491676541
- WAIT #1: nam='db file scattered read' ela= 49 file#=4 block#=28 blocks=5 obj#=51575 tim=1325917491676748
- 51575=> EMP 全表扫描EMP探测表(probe table), 并过滤filter("E"."SAL">=200)
- 4.
- FETCH #1:c=2999,e=3355,p=12,cr=14,cu=0,mis=0,r=1,dep=0,og=1,tim=1325917491677239 ==》 返回1行
- FETCH #1:c=0,e=151,p=0,cr=1,cu=0,mis=0,r=13,dep=0,og=1,tim=1325917491678051 ==》 返回13行
- 共返回14行数据,注意
-
- 不要孤立的看步骤3和步骤4, 实际运行时探测表 扫描一部分 就会做一部分的HASH JOIN 并返回一部分的数据。
-
-
- =====================
- PARSING IN CURSOR #1 len=102 dep=0 uid=0 oct=3 lid=0 tim=1325917491673381 hv=3195267918 ad='a38af540'
- select /*+ USE_HASH(E D) */ D.LOC
- from scott.emp E,scott.dept D
- where
- E.SAL>=200 and E.DEPTNO=D.DEPTNO
- END OF STMT
- PARSE #1:c=3000,e=3091,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1325917491673369
- EXEC #1:c=0,e=112,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1325917491673735
- WAIT #1: nam='SQL*Net message to client' ela= 9 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1325917491673812
- kxhfInit(): enter
- kxhfInit(): exit
- *** RowSrcId: 1 HASH JOIN STATISTICS (INITIALIZATION) ***
- Join Type: INNER join
- Original hash-area size: 4665563
- Memory for slot table: 2949120
- Calculated overhead for partitions and row/slot managers: 1716443
- Hash-join fanout: 8
- Number of partitions: 8
- Number of slots: 24
- Multiblock IO: 15
- Block size(KB): 8
- Cluster (slot) size(KB): 120
- Minimum number of bytes per block: 8160
- Bit vector memory allocation(KB): 128
- Per partition bit vector length(KB): 16
- Maximum possible row length: 48
- Estimated build size (KB): 0
- Estimated Build Row Length (includes overhead): 27
- # Immutable Flags:
- Not BUFFER(execution) output of the join for PQ
- Evaluate Left Input Row Vector
- Evaluate Right Input Row Vector
- # Mutable Flags:
- IO sync
- kxhfSetPhase: phase=BUILD
- WAIT #1: nam='db file sequential read' ela= 48 file#=4 block#=11 blocks=1 obj#=51573 tim=1325917491675207
- WAIT #1: nam='db file scattered read' ela= 75 file#=4 block#=12 blocks=5 obj#=51573 tim=1325917491675589
- kxhfAddChunk: add chunk 0 (sz=32) to slot table
- kxhfAddChunk: chunk 0 (lbs=0x7f5e9ff28b20, slotTab=0x7f5e9ff28ce8) successfuly added
- kxhfSetPhase: phase=PROBE_1
- qerhjFetch: max build row length (mbl=20)
- *** RowSrcId: 1 END OF BUILD (PHASE 1) ***
- Revised row length: 19
- Revised build size: 0KB
- kxhfResize(enter): resize to 12 slots (numAlloc=4, max=24)
- kxhfResize(exit): resized to 12 slots (numAlloc=4, max=12)
- Slot table resized: old=24 wanted=12 got=12 unload=0
- *** RowSrcId: 1 HASH JOIN RESIZE BUILD (PHASE 1) ***
- Total number of partitions: 8
- Number of partitions which could fit in memory: 8
- Number of partitions left in memory: 8
- Total number of slots in in-memory partitions: 4
- kxhfResize(enter): resize to 10 slots (numAlloc=4, max=12)
- kxhfResize(exit): resized to 10 slots (numAlloc=4, max=10)
- set work area size to: 2651K (10 slots)
- WAIT #1: nam='db file sequential read' ela= 32 file#=4 block#=27 blocks=1 obj#=51575 tim=1325917491676541
- WAIT #1: nam='db file scattered read' ela= 49 file#=4 block#=28 blocks=5 obj#=51575 tim=1325917491676748
- *** RowSrcId: 1 HASH JOIN BUILD HASH TABLE (PHASE 1) ***
- Total number of partitions: 8
- Number of partitions left in memory: 8
- Total number of rows in in-memory partitions: 4
- (used as preliminary number of buckets in hash table)
- Estimated max # of build rows that can fit in avail memory: 122700
- ### Partition Distribution ###
- Partition:0 rows:1 clusters:1 slots:1 kept=1
- Partition:1 rows:0 clusters:0 slots:0 kept=1
- Partition:2 rows:1 clusters:1 slots:1 kept=1
- Partition:3 rows:0 clusters:0 slots:0 kept=1
- Partition:4 rows:0 clusters:0 slots:0 kept=1
- Partition:5 rows:1 clusters:1 slots:1 kept=1
- Partition:6 rows:0 clusters:0 slots:0 kept=1
- Partition:7 rows:1 clusters:1 slots:1 kept=1
- *** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***
- Revised number of hash buckets (after flushing): 4
- Allocating new hash table.
- *** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***
- Requested size of hash table: 1
- Actual size of hash table: 8
- Number of buckets: 8
- Match bit vector allocated: FALSE
- *** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***
- Total number of rows (may have changed): 4
- Number of in-memory partitions (may have changed): 8
- Final number of hash buckets: 8
- Size (in bytes) of hash table: 64
- qerhjBuildHashTable(): done hash-table on partition=7, index=4 last_slot#=0 rows=1 total_rows=1
- qerhjBuildHashTable(): done hash-table on partition=5, index=5 last_slot#=3 rows=1 total_rows=2
- qerhjBuildHashTable(): done hash-table on partition=2, index=6 last_slot#=1 rows=1 total_rows=3
- qerhjBuildHashTable(): done hash-table on partition=0, index=7 last_slot#=2 rows=1 total_rows=4
- kxhfIterate(end_iterate): numAlloc=4, maxSlots=10
- *** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***
- ### Hash table ###
- # NOTE: The calculated number of rows in non-empty buckets may be smaller
- # than the true number.
- Number of buckets with 0 rows: 4
- Number of buckets with 1 rows: 4
- Number of buckets with 2 rows: 0
- Number of buckets with 3 rows: 0
- Number of buckets with 4 rows: 0
- Number of buckets with 5 rows: 0
- Number of buckets with 6 rows: 0
- Number of buckets with 7 rows: 0
- Number of buckets with 8 rows: 0
- Number of buckets with 9 rows: 0
- Number of buckets with between 10 and 19 rows: 0
- Number of buckets with between 20 and 29 rows: 0
- Number of buckets with between 30 and 39 rows: 0
- Number of buckets with between 40 and 49 rows: 0
- Number of buckets with between 50 and 59 rows: 0
- Number of buckets with between 60 and 69 rows: 0
- Number of buckets with between 70 and 79 rows: 0
- Number of buckets with between 80 and 89 rows: 0
- Number of buckets with between 90 and 99 rows: 0
- Number of buckets with 100 or more rows: 0
- ### Hash table overall statistics ###
- Total buckets: 8 Empty buckets: 4 Non-empty buckets: 4
- Total number of rows: 4
- Maximum number of rows in a bucket: 1
- Average number of rows in non-empty buckets: 1.000000
- FETCH #1:c=2999,e=3355,p=12,cr=14,cu=0,mis=0,r=1,dep=0,og=1,tim=1325917491677239
- WAIT #1: nam='SQL*Net message from client' ela= 511 driver id=1650815232 #bytes=1 p3=0 obj#=51575 tim=1325917491677839
- WAIT #1: nam='SQL*Net message to client' ela= 8 driver id=1650815232 #bytes=1 p3=0 obj#=51575 tim=1325917491677937
- qerhjFetch: max probe row length (mpl=0)
- *** RowSrcId: 1, qerhjFreeSpace(): free hash-join memory
- kxhfRemoveChunk: remove chunk 0 from slot table
- FETCH #1:c=0,e=151,p=0,cr=1,cu=0,mis=0,r=13,dep=0,og=1,tim=1325917491678051
- *** 2013-01-09 08:51:58.996
- WAIT #1: nam='SQL*Net message from client' ela= 7341414 driver id=1650815232 #bytes=1 p3=0 obj#=51575 tim=1325917499019584
- STAT #1 id=1 cnt=14 pid=0 pos=1 obj=0 op='HASH JOIN (cr=15 pr=12 pw=0 time=3364 us)'
- STAT #1 id=2 cnt=4 pid=1 pos=1 obj=51573 op='TABLE ACCESS FULL DEPT (cr=7 pr=6 pw=0 time=1334 us)'
- STAT #1 id=3 cnt=14 pid=1 pos=2 obj=51575 op='TABLE ACCESS FULL EMP (cr=8 pr=6 pw=0 time=374 us)'
- WAIT #0: nam='SQL*Net message to client' ela= 24 driver id=1650815232 #bytes=1 p3=0 obj#=51575 tim=1325917499019933
- WAIT #0: nam='SQL*Net message from client' ela= 3073598 driver id=1650815232 #bytes=1 p3=0 obj#=51575 tim=1325917502093832
- WAIT #0: nam='SQL*Net message to client' ela= 10 driver id=1650815232 #bytes=1 p3=0 obj#=51575 tim=1325917502093935
- WAIT #0: nam='SQL*Net message from client' ela= 3006627 driver id=1650815232 #bytes=1 p3=0 obj#=51575 tim=1325917505100616
- WAIT #0: nam='SQL*Net message to client' ela= 11 driver id=1650815232 #bytes=1 p3=0 obj#=51575 tim=1325917505100737
复制代码 ================================》
执行步骤:
1.
WAIT #1: nam='db file sequential read' ela= 48 file#=4 block#=11 blocks=1 obj#=51573 tim=1325917491675207
WAIT #1: nam='db file scattered read' ela= 75 file#=4 block#=12 blocks=5 obj#=51573 tim=1325917491675589
51573 =》51573 先对DEPT表(BUILD TABLE)做全表扫描,无过滤和access, 如ID2
2.
HASH JOIN BUILD HASH TABLE (PHASE 1) ***
DEPT 共4行数据,共分成8个bucket
3,
WAIT #1: nam='db file sequential read' ela= 32 file#=4 block#=27 blocks=1 obj#=51575 tim=1325917491676541
WAIT #1: nam='db file scattered read' ela= 49 file#=4 block#=28 blocks=5 obj#=51575 tim=1325917491676748
51575=> EMP 全表扫描EMP探测表(probe table), 并过滤filter("E"."SAL">=200)
4.
FETCH #1:c=2999,e=3355,p=12,cr=14,cu=0,mis=0,r=1,dep=0,og=1,tim=1325917491677239 ==》 返回1行
FETCH #1:c=0,e=151,p=0,cr=1,cu=0,mis=0,r=13,dep=0,og=1,tim=1325917491678051 ==》 返回13行
共返回14行数据,注意
不要孤立的看步骤3和步骤4, 实际运行时探测表 扫描一部分 就会做一部分的HASH JOIN 并返回一部分的数据。 |
|