首页 > 其他 > 详细

优化实例- not use hash to avoid temp space issue

时间:2014-03-18 19:19:36      阅读:449      评论:0      收藏:0      [点我收藏+]

在展开下面的original sql 和 execution plan之前,要知道这个SQL的问题就在于占用大量的TEMP space

 

orignal SQL

bubuko.com,布布扣
SELECT 
    roster.IC_N AS icN, roster.WORK_SHIFT_C AS workShiftC, roster.EXTRA_SHIFT_C AS extraShiftC, 
    roster.GENERATED_SHIFT_C AS generatedShiftC, roster.RESERVE_SHIFT_C AS reserveShiftCode,  
    num.STAFF_N AS staffNo, grp.scheme_n AS schemeN, deploy.deploy_department_c AS dept, 
    code.deployment_c AS deploymentC
FROM STAFF_ROSTER roster, STAFF_WORK WORK1, STAFF_CATEGORY category, staff_roster_group grp, STAFF staff, 
    staff_number num, staff_cross_deploy deploy, staff_deployment_code code 
WHERE 
        WORK1.IC_N = roster.IC_N 
    AND category.IC_N = roster.IC_N 
    AND CATEGORY.CATEGORY_C in (SQC,RQC,WOS,CES,WCES,NTCES,HEP,SOSOS,SVCOS,CCOS,CCSOS,IGCMO,SOCMO,YOCMO,LS,LSUP,LSO,IGCMO2,SOCMO2,YOCMO2,IGCMO3,SOCMO3,YOCMO3) 
    AND staff.IC_N = roster.IC_N 
    AND staff.IC_N = num.IC_N 
    AND staff.IC_N = deploy.IC_N 
    AND staff.ic_n = grp.ic_n 
    AND staff.ic_n = code.ic_n(+) 
    AND code.DEPARTMENT_C(+) = C 
    AND TO_CHAR(code.SHIFT_D(+), DD/MM/YYYY) = 11/03/2014 
    AND deploy.WORK_DEPARTMENT_C = C 
    AND work1.home_department_c = deploy.work_department_c 
    AND work1.home_department_c = roster.department_c 
    AND TO_CHAR(roster.SHIFT_D, DD/MM/YYYY) = 11/03/2014 
    AND WORK1.START_DT <= roster.shift_d 
    AND ((WORK1.END_DT > roster.shift_d) OR (WORK1.END_DT IS NULL)) 
    AND category.EFFECTIVE_D = (SELECT MAX(SC.EFFECTIVE_D) FROM STAFF_CATEGORY SC WHERE SC.IC_N = roster.IC_N AND SC.EFFECTIVE_D <= roster.SHIFT_D) 
    AND num.EFFECTIVE_D = (SELECT MAX(SN.EFFECTIVE_D) FROM STAFF_NUMBER SN WHERE SN.IC_N = roster.IC_N AND SN.EFFECTIVE_D <= roster.SHIFT_D) 
    AND TO_DATE(11/03/2014, dd/mm/yyyy) BETWEEN deploy.start_dt AND deploy.end_dt 
    AND grp.EFFECTIVE_D = (SELECT MAX(SG.EFFECTIVE_D) FROM STAFF_ROSTER_GROUP SG WHERE SG.IC_N = roster.IC_N AND SG.EFFECTIVE_D <= roster.SHIFT_D)
    
Dont be afraid

origanl EXECUTION plan

bubuko.com,布布扣
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1872515827

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                          |     1 |   267 |       | 64452  (12)| 00:12:54 |
|   1 |  NESTED LOOPS                          |                          |     1 |   267 |       | 64452  (12)| 00:12:54 |
|   2 |   NESTED LOOPS                         |                          |     1 |   245 |       | 64433  (12)| 00:12:54 |
|   3 |    NESTED LOOPS                        |                          |     1 |   224 |       | 64432  (12)| 00:12:54 |
|   4 |     NESTED LOOPS OUTER                 |                          |     1 |   201 |       | 64431  (12)| 00:12:54 |
|   5 |      NESTED LOOPS                      |                          |     1 |   177 |       | 64428  (12)| 00:12:54 |
|*  6 |       HASH JOIN                        |                          |     1 |   150 |       | 64427  (12)| 00:12:54 |
|*  7 |        HASH JOIN                       |                          |     1 |   123 |       | 43227  (12)| 00:08:39 |
|*  8 |         HASH JOIN                      |                          |     1 |    96 |       | 21602  (12)| 00:04:20 |
|   9 |          TABLE ACCESS BY INDEX ROWID   | STAFF_ROSTER             |     1 |    28 |       |     3   (0)| 00:00:01 |
|  10 |           NESTED LOOPS                 |                          |    11 |   759 |       |   138   (1)| 00:00:02 |
|  11 |            NESTED LOOPS                |                          |    44 |  1804 |       |    19   (0)| 00:00:01 |
|* 12 |             TABLE ACCESS BY INDEX ROWID| STAFF_CROSS_DEPLOY       |    44 |  1364 |       |    19   (0)| 00:00:01 |
|* 13 |              INDEX RANGE SCAN          | STAFF_CROSS_DEPLOY_NNDX  |    73 |       |       |     9   (0)| 00:00:01 |
|* 14 |             INDEX UNIQUE SCAN          | STAFF_PK                 |     1 |    10 |       |     0   (0)| 00:00:01 |
|* 15 |            INDEX RANGE SCAN            | STAFF_ROSTER_PK          |     1 |       |       |     2   (0)| 00:00:01 |
|  16 |          VIEW                          | VW_SQ_2                  |  7227 |   190K|       | 21463  (12)| 00:04:18 |
|  17 |           HASH GROUP BY                |                          |  7227 |   268K|   234M| 21463  (12)| 00:04:18 |
|  18 |            MERGE JOIN                  |                          |  5097K|   184M|       |  1944  (27)| 00:00:24 |
|  19 |             SORT JOIN                  |                          |  7227 |   127K|       |    39   (8)| 00:00:01 |
|  20 |              INDEX FAST FULL SCAN      | STAFF_NUMBER_PK          |  7227 |   127K|       |    37   (3)| 00:00:01 |
|* 21 |             SORT JOIN                  |                          | 14107 |   275K|   792K|  1731  (20)| 00:00:21 |
|* 22 |              INDEX FAST FULL SCAN      | STAFF_ROSTER_IDX1        | 14107 |   275K|       |  1638  (21)| 00:00:20 |
|  23 |         VIEW                           | VW_SQ_3                  |  7283 |   192K|       | 21624  (12)| 00:04:20 |
|  24 |          HASH GROUP BY                 |                          |  7283 |   270K|   236M| 21624  (12)| 00:04:20 |
|  25 |           MERGE JOIN                   |                          |  5136K|   186M|       |  1954  (27)| 00:00:24 |
|  26 |            SORT JOIN                   |                          |  7283 |   128K|       |    47   (7)| 00:00:01 |
|  27 |             INDEX FAST FULL SCAN       | STAFF_ROSTER_GROUP_PK    |  7283 |   128K|       |    45   (3)| 00:00:01 |
|* 28 |            SORT JOIN                   |                          | 14107 |   275K|   792K|  1731  (20)| 00:00:21 |
|* 29 |             INDEX FAST FULL SCAN       | STAFF_ROSTER_IDX1        | 14107 |   275K|       |  1638  (21)| 00:00:20 |
|  30 |        VIEW                            | VW_SQ_1                  |  7137 |   188K|       | 21200  (12)| 00:04:15 |
|  31 |         HASH GROUP BY                  |                          |  7137 |   264K|   231M| 21200  (12)| 00:04:15 |
|  32 |          MERGE JOIN                    |                          |  5033K|   182M|       |  1927  (27)| 00:00:24 |
|  33 |           SORT JOIN                    |                          |  7137 |   125K|       |    24  (13)| 00:00:01 |
|  34 |            TABLE ACCESS FULL           | STAFF_CATEGORY           |  7137 |   125K|       |    22   (5)| 00:00:01 |
|* 35 |           SORT JOIN                    |                          | 14107 |   275K|   792K|  1731  (20)| 00:00:21 |
|* 36 |            INDEX FAST FULL SCAN        | STAFF_ROSTER_IDX1        | 14107 |   275K|       |  1638  (21)| 00:00:20 |
|* 37 |       TABLE ACCESS BY INDEX ROWID      | STAFF_WORK               |     1 |    27 |       |     1   (0)| 00:00:01 |
|* 38 |        INDEX UNIQUE SCAN               | STAFF_WORK_UNDX          |     1 |       |       |     0   (0)| 00:00:01 |
|  39 |      TABLE ACCESS BY INDEX ROWID       | STAFF_DEPLOYMENT_CODE    |     1 |    24 |       |     3   (0)| 00:00:01 |
|* 40 |       INDEX RANGE SCAN                 | STAFF_DEPLOYMENT_CODE_PK |     1 |       |       |     2   (0)| 00:00:01 |
|* 41 |     INDEX RANGE SCAN                   | STAFF_NUMBER_PK          |     1 |    23 |       |     1   (0)| 00:00:01 |
|* 42 |    INDEX RANGE SCAN                    | STAFF_ROSTER_GROUP_PK    |     1 |    21 |       |     1   (0)| 00:00:01 |
|  43 |   INLIST ITERATOR                      |                          |       |       |       |            |          |
|* 44 |    INDEX UNIQUE SCAN                   | STAFF_CATEGORY_PK        |     1 |    22 |       |    19   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------

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

   6 - access("ITEM_1"="ROSTER"."IC_N" AND "ITEM_2"=ROWID)
   7 - access("ITEM_5"="ROSTER"."IC_N" AND "ITEM_6"=ROWID)
   8 - access("ITEM_3"="ROSTER"."IC_N" AND "ITEM_4"=ROWID)
  12 - filter("DEPLOY"."END_DT">=TO_DATE( 2014-03-11 00:00:00, syyyy-mm-dd hh24:mi:ss) AND
              "DEPLOY"."START_DT"<=TO_DATE( 2014-03-11 00:00:00, syyyy-mm-dd hh24:mi:ss))
  13 - access("DEPLOY"."WORK_DEPARTMENT_C"=C)
  14 - access("STAFF"."IC_N"="DEPLOY"."IC_N")
  15 - access("STAFF"."IC_N"="ROSTER"."IC_N" AND "ROSTER"."DEPARTMENT_C"=C)
       filter(TO_CHAR(INTERNAL_FUNCTION("ROSTER"."SHIFT_D"),DD/MM/YYYY)=11/03/2014)
  21 - access("SN"."EFFECTIVE_D"<="ROSTER"."SHIFT_D")
       filter("SN"."EFFECTIVE_D"<="ROSTER"."SHIFT_D")
  22 - filter(TO_CHAR(INTERNAL_FUNCTION("ROSTER"."SHIFT_D"),DD/MM/YYYY)=11/03/2014)
  28 - access("SG"."EFFECTIVE_D"<="ROSTER"."SHIFT_D")
       filter("SG"."EFFECTIVE_D"<="ROSTER"."SHIFT_D")
  29 - filter(TO_CHAR(INTERNAL_FUNCTION("ROSTER"."SHIFT_D"),DD/MM/YYYY)=11/03/2014)
  35 - access("SC"."EFFECTIVE_D"<="ROSTER"."SHIFT_D")
       filter("SC"."EFFECTIVE_D"<="ROSTER"."SHIFT_D")
  36 - filter(TO_CHAR(INTERNAL_FUNCTION("ROSTER"."SHIFT_D"),DD/MM/YYYY)=11/03/2014)
  37 - filter("WORK1"."START_DT"<="ROSTER"."SHIFT_D" AND ("WORK1"."END_DT">"ROSTER"."SHIFT_D" OR "WORK1"."END_DT"
              IS NULL))
  38 - access("WORK1"."IC_N"="ROSTER"."IC_N" AND "WORK1"."HOME_DEPARTMENT_C"=C)
  40 - access("STAFF"."IC_N"="CODE"."IC_N"(+) AND "CODE"."DEPARTMENT_C"(+)=C)
       filter(TO_CHAR(INTERNAL_FUNCTION("CODE"."SHIFT_D"(+)),DD/MM/YYYY)=11/03/2014)
  41 - access("STAFF"."IC_N"="NUM"."IC_N" AND "NUM"."EFFECTIVE_D"="VW_COL_1")
       filter("NUM"."EFFECTIVE_D"="VW_COL_1")
  42 - access("STAFF"."IC_N"="GRP"."IC_N" AND "GRP"."EFFECTIVE_D"="VW_COL_1")
       filter("GRP"."EFFECTIVE_D"="VW_COL_1")
  44 - access("CATEGORY"."IC_N"="ROSTER"."IC_N" AND ("CATEGORY"."CATEGORY_C"=CCOS OR
              "CATEGORY"."CATEGORY_C"=CCSOS OR "CATEGORY"."CATEGORY_C"=CES OR "CATEGORY"."CATEGORY_C"=HEP OR
              "CATEGORY"."CATEGORY_C"=IGCMO OR "CATEGORY"."CATEGORY_C"=IGCMO2 OR "CATEGORY"."CATEGORY_C"=IGCMO3 OR
              "CATEGORY"."CATEGORY_C"=LS OR "CATEGORY"."CATEGORY_C"=LSO OR "CATEGORY"."CATEGORY_C"=LSUP OR
              "CATEGORY"."CATEGORY_C"=NTCES OR "CATEGORY"."CATEGORY_C"=RQC OR "CATEGORY"."CATEGORY_C"=SOCMO OR
              "CATEGORY"."CATEGORY_C"=SOCMO2 OR "CATEGORY"."CATEGORY_C"=SOCMO3 OR "CATEGORY"."CATEGORY_C"=SOSOS OR
              "CATEGORY"."CATEGORY_C"=SQC OR "CATEGORY"."CATEGORY_C"=SVCOS OR "CATEGORY"."CATEGORY_C"=WCES OR
              "CATEGORY"."CATEGORY_C"=WOS OR "CATEGORY"."CATEGORY_C"=YOCMO OR "CATEGORY"."CATEGORY_C"=YOCMO2 OR
              "CATEGORY"."CATEGORY_C"=YOCMO3) AND "CATEGORY"."EFFECTIVE_D"="VW_COL_1")

91 rows selected.
execution plan

 

OK. 不用纠结于复杂的SQL 逻辑,只要知道hash 占用大量的TEMP,解决办法就是避免HASH. 

而存在hash的原因就是因为 subquery 被 unnest了,那么我们可以避免 unnest,方法是设置下面几个参数之一,或者用hint

bubuko.com,布布扣
SQL > alter session set "_optimizer_cost_based_transformation" = OFF ;

SQL > alter session set "_gby_hash_aggregation_enabled" = FALSE ;

SQL > alter session set "_unnest_subquery"=FALSE;
bubuko.com,布布扣

通过设置参数后得到的execution plan如下

bubuko.com,布布扣
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2603715670

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                          |     1 |   186 |   202   (1)| 00:00:03 |
|   1 |  NESTED LOOPS                      |                          |     1 |   186 |   196   (1)| 00:00:03 |
|   2 |   NESTED LOOPS                     |                          |     1 |   164 |   177   (1)| 00:00:03 |
|   3 |    NESTED LOOPS                    |                          |     1 |   143 |   176   (1)| 00:00:03 |
|   4 |     NESTED LOOPS                   |                          |     1 |   120 |   175   (1)| 00:00:03 |
|   5 |      NESTED LOOPS OUTER            |                          |    11 |  1023 |   164   (1)| 00:00:02 |
|   6 |       NESTED LOOPS                 |                          |    11 |   759 |   138   (1)| 00:00:02 |
|   7 |        NESTED LOOPS                |                          |    44 |  1804 |    19   (0)| 00:00:01 |
|*  8 |         TABLE ACCESS BY INDEX ROWID| STAFF_CROSS_DEPLOY       |    44 |  1364 |    19   (0)| 00:00:01 |
|*  9 |          INDEX RANGE SCAN          | STAFF_CROSS_DEPLOY_NNDX  |    73 |       |     9   (0)| 00:00:01 |
|* 10 |         INDEX UNIQUE SCAN          | STAFF_PK                 |     1 |    10 |     0   (0)| 00:00:01 |
|  11 |        TABLE ACCESS BY INDEX ROWID | STAFF_ROSTER             |     1 |    28 |     3   (0)| 00:00:01 |
|* 12 |         INDEX RANGE SCAN           | STAFF_ROSTER_PK          |     1 |       |     2   (0)| 00:00:01 |
|  13 |       TABLE ACCESS BY INDEX ROWID  | STAFF_DEPLOYMENT_CODE    |     1 |    24 |     3   (0)| 00:00:01 |
|* 14 |        INDEX RANGE SCAN            | STAFF_DEPLOYMENT_CODE_PK |     1 |       |     2   (0)| 00:00:01 |
|* 15 |      TABLE ACCESS BY INDEX ROWID   | STAFF_WORK               |     1 |    27 |     1   (0)| 00:00:01 |
|* 16 |       INDEX UNIQUE SCAN            | STAFF_WORK_UNDX          |     1 |       |     0   (0)| 00:00:01 |
|* 17 |     INDEX RANGE SCAN               | STAFF_NUMBER_PK          |     1 |    23 |     1   (0)| 00:00:01 |
|  18 |      SORT AGGREGATE                |                          |     1 |    18 |            |          |
|* 19 |       INDEX RANGE SCAN             | STAFF_NUMBER_PK          |     1 |    18 |     2   (0)| 00:00:01 |
|* 20 |    INDEX RANGE SCAN                | STAFF_ROSTER_GROUP_PK    |     1 |    21 |     1   (0)| 00:00:01 |
|  21 |     SORT AGGREGATE                 |                          |     1 |    18 |            |          |
|* 22 |      INDEX RANGE SCAN              | STAFF_ROSTER_GROUP_PK    |     1 |    18 |     2   (0)| 00:00:01 |
|  23 |   INLIST ITERATOR                  |                          |       |       |            |          |
|* 24 |    INDEX UNIQUE SCAN               | STAFF_CATEGORY_PK        |     1 |    22 |    19   (0)| 00:00:01 |
|  25 |     SORT AGGREGATE                 |                          |     1 |    18 |            |          |
|* 26 |      INDEX RANGE SCAN              | STAFF_CATEGORY_PK        |     1 |    18 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
View Code

但是用hint的方式去避免unnest 则没有成功,需要测试。

另外疑惑的地方是,max 这种函数在subquery里应该是不unnest的,为什么这里unnest了?

 

 

总结一下, 这个case的问题就在于temp占用高,而hash是典型的占用空间多,所以应该自然而然的想到其它join。

优化实例- not use hash to avoid temp space issue,布布扣,bubuko.com

优化实例- not use hash to avoid temp space issue

原文:http://www.cnblogs.com/kramer/p/3608343.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!