一次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来改变小数据集为构造输入，而大数据集为探测输入来改变临时表空间不足的问题，同时提高性能。