Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 简述一个HASH JOIN的处理过程
发新帖
Maclean Liu(刘相兵

2135

积分

502

好友

184

主题

[RAC性能调优] 简述一个HASH JOIN的处理过程

1#
发表于 2013-1-9 22:07:29 | 查看: 3877| 回复: 1
简述一个HASH JOIN的处理过程
  1. SQL> select * from v$version;

  3. BANNER
  4. ----------------------------------------------------------------
  5. Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
  6. PL/SQL Release 10.2.0.5.0 - Production
  7. CORE    10.2.0.5.0      Production
  8. TNS for Linux: Version 10.2.0.5.0 - Production
  9. NLSRTL Version 10.2.0.5.0 - Production


  12. alter system flush buffer_cache;

  14. alter session set events '10104 trace name context forever, level 2 : 10046 trace name context forever,level 8';



  18. select /*+ USE_HASH(E D) */ D.LOC
  19. from scott.emp E,scott.dept D
  20. where
  21. E.SAL>=200 and E.DEPTNO=D.DEPTNO;



  25. Execution Plan
  26. ----------------------------------------------------------
  27. Plan hash value: 615168685

  29. ---------------------------------------------------------------------------
  30. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  31. ---------------------------------------------------------------------------
  32. |   0 | SELECT STATEMENT   |      |    14 |   252 |     7  (15)| 00:00:01 |
  33. |*  1 |  HASH JOIN         |      |    14 |   252 |     7  (15)| 00:00:01 |
  34. |   2 |   TABLE ACCESS FULL| DEPT |     4 |    44 |     3   (0)| 00:00:01 |
  35. |*  3 |   TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
  36. ---------------------------------------------------------------------------

  38. Predicate Information (identified by operation id):
  39. ---------------------------------------------------

  41.    1 - access("E"."DEPTNO"="D"."DEPTNO")
  42.    3 - filter("E"."SAL">=200)
  43.    
  44.    
  45.    Statistics
  46. ----------------------------------------------------------
  47.           0  recursive calls
  48.           0  db block gets
  49.          15  consistent gets
  50.           0  physical reads
  51.           0  redo size
  52.         712  bytes sent via SQL*Net to client
  53.         492  bytes received via SQL*Net from client
  54.           2  SQL*Net roundtrips to/from client
  55.           0  sorts (memory)
  56.           0  sorts (disk)
  57.          14  rows processed



  61.      1* select object_name from dba_objects where object_id=51573
  62. SQL> /

  64. OBJECT_NAME
  65. --------------------------------------------------------------------------------
  66. DEPT

  68. SQL> select object_name from dba_objects where object_id=51575;

  70. OBJECT_NAME
  71. --------------------------------------------------------------------------------
  72. EMP



  76. SQL> select count(*) from scott.dept;

  78.   COUNT(*)
  79. ----------
  80.          4

  82.    
  83.    
  84. 执行步骤：

  86. 1.

  88.    WAIT #1: nam='db file sequential read' ela= 48 file#=4 block#=11 blocks=1 obj#=51573 tim=1325917491675207
  89. WAIT #1: nam='db file scattered read' ela= 75 file#=4 block#=12 blocks=5 obj#=51573 tim=1325917491675589

  91. 51573  =》51573　　　先对DEPT表(BUILD TABLE)做全表扫描，无过滤和access， 如ID2



  95. 2.

  97. HASH JOIN BUILD HASH TABLE (PHASE 1) ***

  99. DEPT 共4行数据，共分成8个bucket


  102. 3,
  103. WAIT #1: nam='db file sequential read' ela= 32 file#=4 block#=27 blocks=1 obj#=51575 tim=1325917491676541
  104. WAIT #1: nam='db file scattered read' ela= 49 file#=4 block#=28 blocks=5 obj#=51575 tim=1325917491676748


  107. 51575=> EMP 全表扫描EMP探测表(probe table)， 并过滤filter("E"."SAL">=200)


  110. 4.
  111. FETCH #1:c=2999,e=3355,p=12,cr=14,cu=0,mis=0,r=1,dep=0,og=1,tim=1325917491677239   ==》 返回1行
  112. FETCH #1:c=0,e=151,p=0,cr=1,cu=0,mis=0,r=13,dep=0,og=1,tim=1325917491678051        ==》 返回13行


  115. 共返回14行数据，注意




  120. 不要孤立的看步骤3和步骤4， 实际运行时探测表 扫描一部分 就会做一部分的HASH JOIN 并返回一部分的数据。




  125.    
  126.    

  128. =====================
  129. PARSING IN CURSOR #1 len=102 dep=0 uid=0 oct=3 lid=0 tim=1325917491673381 hv=3195267918 ad='a38af540'
  130. select /*+ USE_HASH(E D) */ D.LOC
  131. from scott.emp E,scott.dept D
  132. where
  133. E.SAL>=200 and E.DEPTNO=D.DEPTNO
  134. END OF STMT
  135. PARSE #1:c=3000,e=3091,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1325917491673369
  136. EXEC #1:c=0,e=112,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1325917491673735
  137. WAIT #1: nam='SQL*Net message to client' ela= 9 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1325917491673812
  138. kxhfInit(): enter
  139. kxhfInit(): exit
  140. *** RowSrcId: 1 HASH JOIN STATISTICS (INITIALIZATION) ***
  141. Join Type: INNER join
  142. Original hash-area size: 4665563
  143. Memory for slot table: 2949120
  144. Calculated overhead for partitions and row/slot managers: 1716443
  145. Hash-join fanout: 8


  148. Number of partitions: 8
  149. Number of slots: 24
  150. Multiblock IO: 15
  151. Block size(KB): 8
  152. Cluster (slot) size(KB): 120
  153. Minimum number of bytes per block: 8160
  154. Bit vector memory allocation(KB): 128
  155. Per partition bit vector length(KB): 16
  156. Maximum possible row length: 48
  157. Estimated build size (KB): 0
  158. Estimated Build Row Length (includes overhead): 27
  159. # Immutable Flags:
  160.   Not BUFFER(execution) output of the join for PQ
  161.   Evaluate Left Input Row Vector
  162.   Evaluate Right Input Row Vector
  163. # Mutable Flags:
  164.   IO sync
  165. kxhfSetPhase: phase=BUILD
  166. WAIT #1: nam='db file sequential read' ela= 48 file#=4 block#=11 blocks=1 obj#=51573 tim=1325917491675207
  167. WAIT #1: nam='db file scattered read' ela= 75 file#=4 block#=12 blocks=5 obj#=51573 tim=1325917491675589
  168. kxhfAddChunk: add chunk 0 (sz=32) to slot table
  169. kxhfAddChunk: chunk 0 (lbs=0x7f5e9ff28b20, slotTab=0x7f5e9ff28ce8) successfuly added
  170. kxhfSetPhase: phase=PROBE_1
  171. qerhjFetch: max build row length (mbl=20)
  172. *** RowSrcId: 1 END OF BUILD (PHASE 1) ***
  173.   Revised row length: 19
  174.   Revised build size: 0KB
  175. kxhfResize(enter): resize to 12 slots (numAlloc=4, max=24)



  179. kxhfResize(exit): resized to 12 slots (numAlloc=4, max=12)
  180.   Slot table resized: old=24 wanted=12 got=12 unload=0
  181. *** RowSrcId: 1 HASH JOIN RESIZE BUILD (PHASE 1) ***
  182. Total number of partitions: 8
  183. Number of partitions which could fit in memory: 8
  184. Number of partitions left in memory: 8
  185. Total number of slots in in-memory partitions: 4
  186. kxhfResize(enter): resize to 10 slots (numAlloc=4, max=12)
  187. kxhfResize(exit): resized to 10 slots (numAlloc=4, max=10)
  188.   set work area size to: 2651K (10 slots)
  189. WAIT #1: nam='db file sequential read' ela= 32 file#=4 block#=27 blocks=1 obj#=51575 tim=1325917491676541
  190. WAIT #1: nam='db file scattered read' ela= 49 file#=4 block#=28 blocks=5 obj#=51575 tim=1325917491676748
  191. *** RowSrcId: 1 HASH JOIN BUILD HASH TABLE (PHASE 1) ***
  192. Total number of partitions: 8
  193. Number of partitions left in memory: 8
  194. Total number of rows in in-memory partitions: 4
  195.    (used as preliminary number of buckets in hash table)
  196. Estimated max # of build rows that can fit in avail memory: 122700
  197. ### Partition Distribution ###
  198. Partition:0    rows:1          clusters:1      slots:1      kept=1
  199. Partition:1    rows:0          clusters:0      slots:0      kept=1
  200. Partition:2    rows:1          clusters:1      slots:1      kept=1
  201. Partition:3    rows:0          clusters:0      slots:0      kept=1
  202. Partition:4    rows:0          clusters:0      slots:0      kept=1
  203. Partition:5    rows:1          clusters:1      slots:1      kept=1
  204. Partition:6    rows:0          clusters:0      slots:0      kept=1
  205. Partition:7    rows:1          clusters:1      slots:1      kept=1
  206. *** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***
  207. Revised number of hash buckets (after flushing): 4
  208. Allocating new hash table.
  209. *** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***
  210. Requested size of hash table: 1
  211. Actual size of hash table: 8


  214. Number of buckets: 8
  215. Match bit vector allocated: FALSE
  216. *** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***
  217. Total number of rows (may have changed): 4
  218. Number of in-memory partitions (may have changed): 8
  219. Final number of hash buckets: 8
  220. Size (in bytes) of hash table: 64
  221. qerhjBuildHashTable(): done hash-table on partition=7, index=4 last_slot#=0 rows=1 total_rows=1
  222. qerhjBuildHashTable(): done hash-table on partition=5, index=5 last_slot#=3 rows=1 total_rows=2
  223. qerhjBuildHashTable(): done hash-table on partition=2, index=6 last_slot#=1 rows=1 total_rows=3
  224. qerhjBuildHashTable(): done hash-table on partition=0, index=7 last_slot#=2 rows=1 total_rows=4
  225. kxhfIterate(end_iterate): numAlloc=4, maxSlots=10
  226. *** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***
  227. ### Hash table ###
  228. # NOTE: The calculated number of rows in non-empty buckets may be smaller
  229. #       than the true number.
  230. Number of buckets with   0 rows:          4
  231. Number of buckets with   1 rows:          4
  232. Number of buckets with   2 rows:          0
  233. Number of buckets with   3 rows:          0
  234. Number of buckets with   4 rows:          0
  235. Number of buckets with   5 rows:          0
  236. Number of buckets with   6 rows:          0
  237. Number of buckets with   7 rows:          0
  238. Number of buckets with   8 rows:          0
  239. Number of buckets with   9 rows:          0
  240. Number of buckets with between  10 and  19 rows:          0
  241. Number of buckets with between  20 and  29 rows:          0


  244. Number of buckets with between  30 and  39 rows:          0
  245. Number of buckets with between  40 and  49 rows:          0
  246. Number of buckets with between  50 and  59 rows:          0
  247. Number of buckets with between  60 and  69 rows:          0
  248. Number of buckets with between  70 and  79 rows:          0
  249. Number of buckets with between  80 and  89 rows:          0
  250. Number of buckets with between  90 and  99 rows:          0
  251. Number of buckets with 100 or more rows:          0
  252. ### Hash table overall statistics ###
  253. Total buckets: 8 Empty buckets: 4 Non-empty buckets: 4
  254. Total number of rows: 4
  255. Maximum number of rows in a bucket: 1
  256. Average number of rows in non-empty buckets: 1.000000
  257. FETCH #1:c=2999,e=3355,p=12,cr=14,cu=0,mis=0,r=1,dep=0,og=1,tim=1325917491677239
  258. WAIT #1: nam='SQL*Net message from client' ela= 511 driver id=1650815232 #bytes=1 p3=0 obj#=51575 tim=1325917491677839
  259. WAIT #1: nam='SQL*Net message to client' ela= 8 driver id=1650815232 #bytes=1 p3=0 obj#=51575 tim=1325917491677937
  260. qerhjFetch: max probe row length (mpl=0)
  261. *** RowSrcId: 1, qerhjFreeSpace(): free hash-join memory
  262. kxhfRemoveChunk: remove chunk 0 from slot table
  263. FETCH #1:c=0,e=151,p=0,cr=1,cu=0,mis=0,r=13,dep=0,og=1,tim=1325917491678051
  264. *** 2013-01-09 08:51:58.996
  265. WAIT #1: nam='SQL*Net message from client' ela= 7341414 driver id=1650815232 #bytes=1 p3=0 obj#=51575 tim=1325917499019584
  266. STAT #1 id=1 cnt=14 pid=0 pos=1 obj=0 op='HASH JOIN  (cr=15 pr=12 pw=0 time=3364 us)'
  267. 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)'
  268. 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)'
  269. WAIT #0: nam='SQL*Net message to client' ela= 24 driver id=1650815232 #bytes=1 p3=0 obj#=51575 tim=1325917499019933
  270. WAIT #0: nam='SQL*Net message from client' ela= 3073598 driver id=1650815232 #bytes=1 p3=0 obj#=51575 tim=1325917502093832
  271. WAIT #0: nam='SQL*Net message to client' ela= 10 driver id=1650815232 #bytes=1 p3=0 obj#=51575 tim=1325917502093935
  272. WAIT #0: nam='SQL*Net message from client' ela= 3006627 driver id=1650815232 #bytes=1 p3=0 obj#=51575 tim=1325917505100616
  273. 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 并返回一部分的数据。
的

相关帖子

下载专业ORACLE数据库恢复工具PRM-DUL  For Oracle http://www.parnassusdata.com/zh-hans/emergency-services

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 ： 13764045638  QQ: 47079569   
2#
发表于 2013-1-10 10:20:14
咋一看，有点不明白。做做实验看看。。。

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-5-7 21:18 , Processed in 0.047940 second(s), 24 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569