转载

对hint的调优

实际工作中经常遇到开发人员加hint为提高数据的批处理的速度，但为了提高处理速度经常遇到并行的hint随意使用，并行不是万能的，不合理的使用只能阻碍运行速度，使用如下SQL说明并行问题
SELECT /*+ LEADING(T1) USE_HASH(T_LGIN) PARALLEL(T1,8) */
                             T1.RPO_NO
                    ,T_LGIN.LGIN_DT
                    ,T_LGIN.USER_ID
                    ,T_LGIN.USER_IP
                    ,T_LGIN.CLNT_IP
                    ,T_LGIN.MAC_ADDR
                    ,T_LGIN.MENU_SYS_CD
                            ,ROW_NUMBER() OVER(PARTITION BY T1.RPO_NO ORDER BY T_LGIN.LGIN_DT DESC) RNK
                    FROM    MCS_HQ_READ.UP_RPO_TRACE_0602 T1
                            ,MCS_HQ.HI_USER_LGIN T_LGIN
                    WHERE   T_LGIN.USER_ID = T1.REQ_ID
                    AND     T_LGIN.LGIN_DT >= TRUNC(T1.REQ_DT)
                    AND     T_LGIN.LGIN_DT <= T1.REQ_DT
                    ORDER BY T1.RPO_NO
他的执行计划如下
SELECT STATEMENT, GOAL = HINT: ALL_ROWS   110412 306398 30027004
PX COORDINATOR
PX SEND QC (ORDER) SYS :TQ10003 110412 306398 30027004
   WINDOW SORT   110412 306398 30027004
    PX RECEIVE   110412 306398 30027004
     PX SEND RANGE SYS :TQ10002 110412 306398 30027004
      HASH JOIN   110412 306398 30027004 "T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT")) AND "T_LGIN"."LGIN_DT"<="T1"."REQ_DT"
       PX RECEIVE   506 396167 20996851
        PX SEND HASH SYS :TQ10001 506 396167 20996851
         PX BLOCK ITERATOR   506 396167 20996851
          TABLE ACCESS FULL MCS_HQ_READ UP_RPO_TRACE_0602 506 396167 20996851
       BUFFER SORT
        PX RECEIVE   109819 35155980 1582019100
         PX SEND HASH SYS :TQ10000 109819 35155980 1582019100
          TABLE ACCESS FULL MCS_HQ HI_USER_LGIN 109819 35155980 1582019100
说明连个表在做HASH连接时，驱动表是被读到内存的，对驱动表再作并行，意义不大，并且并行也是消耗资源的，所以这条sql在hint的指导下运行了1分56秒，那么如果对大表做并行情况又会怎样呢

SELECT /*+ LEADING(T1) USE_HASH(T_LGIN) PARALLEL(T_LGIN,8) */
                             T1.RPO_NO
                    ,T_LGIN.LGIN_DT
                    ,T_LGIN.USER_ID
                    ,T_LGIN.USER_IP
                    ,T_LGIN.CLNT_IP
                    ,T_LGIN.MAC_ADDR
                    ,T_LGIN.MENU_SYS_CD
                            ,ROW_NUMBER() OVER(PARTITION BY T1.RPO_NO ORDER BY T_LGIN.LGIN_DT DESC) RNK
                    FROM    MCS_HQ_READ.UP_RPO_TRACE_0602 T1
                            ,MCS_HQ.HI_USER_LGIN T_LGIN
                    WHERE   T_LGIN.USER_ID = T1.REQ_ID
                    AND     T_LGIN.LGIN_DT >= TRUNC(T1.REQ_DT)
                    AND     T_LGIN.LGIN_DT <= T1.REQ_DT
                    ORDER BY T1.RPO_NO

查看执行计划
SELECT STATEMENT, GOAL = HINT: ALL_ROWS   18966 306398 30027004
PX COORDINATOR
PX SEND QC (ORDER) SYS :TQ10002 18966 306398 30027004
   WINDOW SORT   18966 306398 30027004
    PX RECEIVE   18966 306398 30027004
     PX SEND RANGE SYS :TQ10001 18966 306398 30027004
      HASH JOIN   18966 306398 30027004 "T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT")) AND "T_LGIN"."LGIN_DT"<="T1"."REQ_DT"
       BUFFER SORT
        PX RECEIVE   3653 396167 20996851
         PX SEND BROADCAST SYS :TQ10000 3653 396167 20996851
          TABLE ACCESS FULL MCS_HQ_READ UP_RPO_TRACE_0602 3653 396167 20996851
       PX BLOCK ITERATOR   15227 35155980 1582019100
        TABLE ACCESS FULL MCS_HQ HI_USER_LGIN 15227 35155980 1582019100

这时看到总成本18966比之前的总成本110412 小了近6倍，这说明对数据量大的表且经常从磁盘读数据的表做并行是很有益处的。这条sql的执行时间是45秒，因此对不同对象使用并行还是有分别的。

正文到此结束