一次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來改變小資料集為構造輸入，而大資料集為探測輸入來改變臨時表空間不足的問題，同時提高效能。