SQL調優之使用并行特征

Developer 發來郵件，叫我調整下面的SQL。

注：HPUX ,8CPU，RAC 4節點，數據倉庫環境
-
 

-----郵件內容--------

Hi Robinson,

Could you take a look at the SQL below? It runs very slowly.

select b.prod_4_id, a.SRCE_REGN_ID, count(1)

  from adwu.GLOBL_DEMND_FRCST_WK_FCT a, adwu.prod_9005_gdf_wk_fdim b

 where a.prod_skid = b.prod_skid

   and b.prod_4_id in
       ('1105060745', '1105060767', '1106406452', '1106540881')

   and ETL_RUN_ID = '304898'
 group by b.prod_4_id, a.SRCE_REGN_ID;

----郵件內容-------------

通過OC得知，上面的SQL要跑40分鐘左右。

SQL> select count(*) from adwu.GLOBL_DEMND_FRCST_WK_FCT; ---表GLOBL_DEMND_FRCST_WK_FCT有10億條數據

  COUNT(*)
----------
1079544821

SQL> select count(*) from adwu.prod_9005_gdf_wk_fdim;--表prod_9005_gdf_wk_fdim有1千多萬的數據

  COUNT(*)
----------
   1186493

Elapsed: 00:00:01.20

表GLOBL_DEMND_FRCST_WK_FCT是個 組合分區表，有900多個sub partition分區信息如下：

...............省略..............................................

TABLESPACE "DEM_PLAN01M"
PARTITION BY RANGE ("DAY_SKID")
SUBPARTITION BY LIST ("SRCE_REGN_ID")
SUBPARTITION TEMPLATE (
  SUBPARTITION "NA" values ( 'NA' ),
  SUBPARTITION "LA" values ( 'LA' ),
  SUBPARTITION "WE" values ( 'WE' ),
  SUBPARTITION "CE" values ( 'CE' ),
  SUBPARTITION "GC" values ( 'GC' ),
  SUBPARTITION "NE" values ( 'NE' ),
  SUBPARTITION "AA" values ( 'AA' ),
  SUBPARTITION "GL" values ( 'GL' ) )
PARTITION "P2008052"  VALUES LESS THAN (

.................省略..............................................

表prod_9005_gdf_wk_fdim不是分區表

執行計劃如下：

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 453637057

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                            |    23 |   713 |   175K (12)| 00:25:26 |       |       |
|   1 |  SORT GROUP BY                   |                            |    23 |   713 |   175K (12)| 00:25:26 |       |       |
|*  2 |   HASH JOIN                      |                            |  2212K|    65M|   174K (11)| 00:25:25 |       |       |
|*  3 |    VIEW                          | index$_join$_002           | 23153 |   384K|  2190   (2)| 00:00:20 |       |       |
|*  4 |     HASH JOIN                    |                            |       |       |            |       |       |       |
|   5 |      INLIST ITERATOR             |                            |       |       |            |       |       |       |
|   6 |       BITMAP CONVERSION TO ROWIDS|                            | 23153 |   384K|     8   (0)| 00:00:01 |       |       |
|*  7 |        BITMAP INDEX SINGLE VALUE | PROD_9005_GDF_WK_FDIM_BX16 |       |       |            |       |       |       |
|   8 |      INDEX FAST FULL SCAN        | PROD_9005_GDF_WK_FDIM_PK   | 23153 |   384K|  2180   (2)| 00:00:19 |       |       |
|   9 |    PARTITION RANGE ALL           |                            |  3255K|    43M|   172K (12)| 00:25:05 |     1 |   119 |
|  10 |     PARTITION LIST ALL           |                            |  3255K|    43M|   172K (12)| 00:25:05 |     1 |     8 |
|* 11 |      TABLE ACCESS FULL           | GLOBL_DEMND_FRCST_WK_FCT   |  3255K|    43M|   172K (12)| 00:25:05 |     1 |   952 |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."PROD_SKID"="B"."PROD_SKID")
   3 - filter("B"."PROD_4_ID"='1105060745' OR "B"."PROD_4_ID"='1105060767' OR "B"."PROD_4_ID"='1106406452' OR
              "B"."PROD_4_ID"='1106540881')
   4 - access(ROWID=ROWID)
   7 - access("B"."PROD_4_ID"='1105060745' OR "B"."PROD_4_ID"='1105060767' OR "B"."PROD_4_ID"='1106406452' OR
              "B"."PROD_4_ID"='1106540881')
  11 - filter("ETL_RUN_ID"=304898)

29 rows selected.

請注意觀察執行計劃：其實這里的統計信息是不準確的，因為10億數據表的FULL SCAN 才3255K，說明統計信息出問題了。不過憑俺SQL調優的經驗，即使現在對10億數據表再去收集統計信息，執行計劃也不會變的。同樣會對10表進行全表掃描。對于1千萬的表，使用了2個索引，一個是主鍵，一個是位圖索引，這里沒有什么好說的。

對于這個SQL，可以在10億上面的3個列建立組合索引，從而避免對10億大表全表掃描，不過這樣做會讓導入，更新，刪除變得很慢，而且也浪費空間。所以我放棄了這總方法（一般對2列建立組合索引，超過3列就。。。。。）

好了，怎么優化呢？我這里是倉庫環境，10億的那張表有900多個分區，那么你想到了什么？并行運算啊

對于倉庫環境，如果表已經經過分區，那么我們可以使用并行掃描的方法來提高速度。

SQL> select table_name,degree,instances,status from dba_tables where
  2  owner=upper('&owner') and table_name=upper('&table_name');
Enter value for owner: ADWU
Enter value for table_name: GLOBL_DEMND_FRCST_WK_FCT
old   2: owner=upper('&owner') and table_name=upper('&table_name')
new   2: owner=upper('ADWU') and table_name=upper('GLOBL_DEMND_FRCST_WK_FCT')

TABLE_NAME                     DEGREE               INSTANCES
------------------------------ -------------------- --------------------------
GLOBL_DEMND_FRCST_WK_FCT                1                   1

SQL> alter table adwu.GLOBL_DEMND_FRCST_WK_FCT parallel 8;

Table altered.

執行下面的SQL

SQL> select b.prod_4_id, a.SRCE_REGN_ID, count(1)
  2    from adwu.GLOBL_DEMND_FRCST_WK_FCT a, adwu.prod_9005_gdf_wk_fdim b
  3   where a.prod_skid = b.prod_skid
  4     and b.prod_4_id in
  5         ('1105060745', '1105060767', '1106406452', '1106540881')
  6     and ETL_RUN_ID = '304898'
  7   group by b.prod_4_id, a.SRCE_REGN_ID
  8  ;

PROD_4_ID                                     SRCE_REGN_ID                                                                         COUNT(1)
--------------------------------------------- ------------------------------------------------------------------------------------------ ----------
1105060745                                    GL                                                                                      11628
1106406452                                    GL                                                                                      97529
1105060767                                    GL                                                                                       2215

Elapsed: 00:04:10.14

這里，這個查詢只花了4分鐘，大大的超出了開發人員的預期。不過我這樣做也有問題，因為我設置了degree，這個將會導致對表的查詢更傾向于全表掃描，所以這里不能這么設置，可以使用HINT 提示來讓優化器選擇并行運算，而不是設置degree。

所以最終，讓開發人員使用下面SQL：

SQL> Select /*+ parallel(a,8)  */ b.prod_4_id, a.SRCE_REGN_ID, count(1)
  2    from adwu.GLOBL_DEMND_FRCST_WK_FCT a, adwu.prod_9005_gdf_wk_fdim b
  3   where a.prod_skid = b.prod_skid
  4     and b.prod_4_id in
  5         ('1105060745', '1105060767', '1106406452', '1106540881')
  6     and ETL_RUN_ID = '304898'
  7   group by b.prod_4_id, a.SRCE_REGN_ID;

PROD_4_ID                                     SRCE_REGN_ID                                                                         COUNT(1)
--------------------------------------------- ------------------------------------------------------------------------------------------ -------
1105060745                                    GL                                                                                      11628
1105060767                                    GL                                                                                       2215
1106406452                                    GL                                                                                      97529

Elapsed: 00:04:39.72

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]