转--一次HASH JOIN 临时表空间不足的分析和优化思路

最近遇到一个语句, 只要一执行这个语句就会出现报错临时表空间不足，回想一下在语句中用到临时表空间无非是大量的SORT和HASH，然后通过执行计划查看如下：
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3959216560

-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
| 1 | HASH UNIQUE | | 1 | 4 | 0 |00:00:00.01 | | | |
| 2 | NESTED LOOPS OUTER | | 1 | 4 | 0 |00:00:00.01 | | | |
|* 3 | HASH JOIN | | 1 | 4 | 0 |00:00:00.01 | 703K| 703K| |
| 4 | NESTED LOOPS OUTER | | 1 | 4 | 2524K|00:02:17.15 | | | |
| 5 | NESTED LOOPS | | 1 | 4 | 2524K|00:01:34.23 | | | |
|* 6 | HASH JOIN | | 1 | 4 | 2524K|00:00:53.84 | 2047M| 29M| 55M (1)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 7 | HASH JOIN | | 1 | 4 | 11M|00:01:00.03 | 2797K| 1148K| 3144K (0)|
|* 8 | TABLE ACCESS BY INDEX ROWID | T_COMMISSION_FEE | 1 | 25517 | 31948 |00:00:00.16 | | | |
|* 9 | INDEX RANGE SCAN | PK_T_COMMISSION_FEE | 1 | 25520 | 31948 |00:00:00.03 | | | |
|* 10 | HASH JOIN | | 1 | 33714 | 11M|00:00:12.24 | 1299K| 1299K| 1925K (0)|
|* 11 | TABLE ACCESS FULL | T_GL_BIZ_INTERFACE | 1 | 7889 | 12414 |00:00:00.10 | | | |
|* 12 | TABLE ACCESS FULL | T_BIZ_ACCOUNTING_INFO | 1 | 32696 | 63896 |00:00:00.27 | | | |
| 13 | VIEW | VW_NSO_1 | 1 | 3 | 2 |00:00:00.01 | | | |
|* 14 | FILTER | | 1 | | 2 |00:00:00.01 | | | |
|* 15 | CONNECT BY WITH FILTERING | | 1 | | 2 |00:00:00.01 | 9216 | 9216 | 8192 (0)|
| 16 | TABLE ACCESS BY INDEX ROWID| test| 1 | | 1 |00:00:00.01 | | | |
|* 17 | INDEX FULL SCAN | tes123| 1 | 1 | 1 |00:00:00.01 | | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 18 | NESTED LOOPS | | 2 | | 1 |00:00:00.01 | | | |
| 19 | BUFFER SORT | | 2 | | 2 |00:00:00.01 | 9216 | 9216 | 8192 (0)|
| 20 | CONNECT BY PUMP | | 2 | | 2 |00:00:00.01 | | | |
|* 21 | INDEX RANGE SCAN | Ttest123| 2 | 3 | 1 |00:00:00.01 | | | |
| 22 | TABLE ACCESS FULL | test| 0 | 3 | 0 |00:00:00.01 | | | |
| 23 | TABLE ACCESS BY INDEX ROWID | test| 2524K| 1 | 2524K|00:00:30.72 | | | |
|* 24 | INDEX UNIQUE SCAN | test123| 2524K| 1 | 2524K|00:00:12.32 | | | |
| 25 | TABLE ACCESS BY INDEX ROWID | Ttt| 2524K| 1 | 2524K|00:00:31.35 | | | |
|* 26 | INDEX UNIQUE SCAN | tet1| 2524K| 1 | 2524K|00:00:12.00 | | | |
| 27 | TABLE ACCESS FULL | test31| 0 | 84 | 0 |00:00:00.01 | | | |
| 28 | TABLE ACCESS BY INDEX ROWID | tes1234| 0 | 1 | 0 |00:00:00.01 | | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 29 | INDEX UNIQUE SCAN | PK_T_CONTRACT_MASTER | 0 | 1 | 0 |00:00:00.01 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------

 

第6步太吓人了，我这个语句没有运行完因为会报错，这个状态应该是在故障点的，可以看到需要的HASH构造区域为2G，仔细分析下这个执行计划。
其实它是一个HASH JION和一个VIEW做的HASH JION，观察一下行数，就是11M（11*1024*1024）行和2行进行的一个HANSH JION，但是很奇怪的是执行
计划选择了大数据集为构造输入，构造输入在PGA的工作区的HASH_AREA_SIZE中建立一个HASH表，如果内存不够把HASH表存储在TMEP表空间里面，
而选择了小的数据集来作为探测输入，探测输入会通过连接条件通过HASH函数和HASH表进行比对，如果存在则输出，不存在则丢弃。下面我们通过
一个小小的试验来说明：
建立表
SQL> desc test;
Name Type Nullable Default Comments
----- ------------ -------- ------- --------
员工代码 NUMBER(20) Y
员工中文名 VARCHAR2(50) Y
员工英文名 VARCHAR2(50) Y
归属机构 VARCHAR2(50) Y
岗位名称 VARCHAR2(50) Y
SQL> select count(*) from test;

COUNT(*)
----------
3399680
这个是大数据集
建立表
SQL> desc test2
Name Type Nullable Default Comments
---- ---------- -------- ------- --------
员工代码 NUMBER(20) Y
工资 NUMBER(10) Y
SQL> select count(*) from test2;

COUNT(*)
----------
3
现在试验开始
首先执行语句
SQL> select count(*) from test a,test2 b where a.员工代码=b.员工代码;

COUNT(*)
----------
15360

已用时间: 00: 00: 09.12

执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=6098 Card=1 Bytes=
14)

1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=6098 Card=13197 Bytes=184758)
3 2 TABLE ACCESS (FULL) OF 'TEST2' (TABLE) (Cost=3 Card=3
Bytes=21)

4 2 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=6063 Card=
3409202 Bytes=23864414)

可以看到这个时候小数据集作TEST2为了构造输入，在执行期间通过语句
select operation_id,operation_type,actual_mem_used,tempseg_size,tablespace
from v$session s,v$sql_workarea_active w
where s.sid=w.sid
and S.SID=151;
得出的结果如下：
OPERATION_ID OPERATION_TYPE ACTUAL_MEM_USED TEMPSEG_SIZE TABLESPACE
------------ ---------------------------------------- --------------- ------------ -------------------------------
2 HASH-JOIN 183296
HASH JION用于构造HASH表使用内存183K没有使用临时表空间。

现在我们通过HINT来改变大数据集和小数据集的顺序，执行语句如下：
SQL> select /*+ leading(a) use_hash(a b) */ count(*) from test a,test2 b where a.员工代码=b.员工代码;

COUNT(*)
----------
15360

已用时间: 00: 00: 13.82

执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=10636 Card=1 Bytes
=14)

1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=10636 Card=13197 Bytes=184758)
3 2 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=6063 Card=
3409202 Bytes=23864414)

4 2 TABLE ACCESS (FULL) OF 'TEST2' (TABLE) (Cost=3 Card=3
Bytes=21)
这个时候TEST大数据集是构造输入，同样在执行期间通过语句得出结果
OPERATION_ID OPERATION_TYPE ACTUAL_MEM_USED TEMPSEG_SIZE TABLESPACE
------------ ---------------------------------------- --------------- ------------ -------------------------------
2 HASH-JOIN 1205248 18874368 TEMP
可以看到结果不同了，使用1.2M内存，使用临时表空间近19M。
同时如果我们关注下以下信息：
SQL> select * from v$sysstat where NAME LIKE '%workarea executions%';

STATISTIC# NAME CLASS VALUE STAT_ID
---------- ---------------------------------------------------------------- ---------- ---------- ----------
294 workarea executions - optimal 64 3525 3211650785
295 workarea executions - onepass 64 4 798730793
296 workarea executions - multipass 64 0 3804491469
现在我们运行
SQL> select /*+ leading(a) use_hash(a b) */ count(*) from test a,test2 b where a.员工代码=b.员工代码;

COUNT(*)
----------
15360
在运行
SQL> select * from v$sysstat where NAME LIKE '%workarea executions%';

STATISTIC# NAME CLASS VALUE STAT_ID
---------- ---------------------------------------------------------------- ---------- ---------- ----------
294 workarea executions - optimal 64 3525 3211650785
295 workarea executions - onepass 64 5 798730793
296 workarea executions - multipass 64 0 3804491469

可以看到workarea executions - onepass 增加了1说明我们进行了一次物理交换才完成了探测（还好没有多次）。

有了上面的试验，我的语句应该就可以通过HINT来改变小数据集为构造输入，而大数据集为探测输入来改变临时表空间不足的问题，同时提高性能。