实际工作中经常遇到开发人员加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秒，因此对不同对象使用并行还是有分别的。