一般一个SQL查询数据量很大,且优化余地不大时,我们必然想开启并行,用并行的方式提高数据的查询速度,然后不是任何情况下开启并行都可以达到最佳运行效果,
有时原本使用索引的执行计划,因为使用并行反而该走全表扫描,因此必须通过hint方式引导优化器采取正确的执行计划,对于如下SQL
SELECT
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;
不采取任何方式人工干预,优化器生成的执行计划将按索引查询表HI_USER_LGIN,但因HI_USER_LGIN表内数据量很大,查询非常消耗资源,因此开启并行,提高查询速度。
开始引入并行的hint如下所示
SQL_ID 7f2gdrbqzv7d8, child number 0
-------------------------------------
SELECT /*+ PARALLEL(T_LGIN,8) PARALLEL(T1,8) ALL_ROWS */
T1.RPO_NO /*-testNL0000001*/
,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
Plan hash value: 3061441924
-----------------------------------------------------------------
| Id | Operation | Name | E-Rows |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | PX COORDINATOR | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 307K|
| 3 | WINDOW SORT | | 307K|
| 4 | PX RECEIVE | | 307K|
| 5 | PX SEND HASH | :TQ10001 | 307K|
|* 6 | HASH JOIN | | 307K|
| 7 | PX RECEIVE | | 396K|
| 8 | PX SEND BROADCAST | :TQ10000 | 396K|
| 9 | PX BLOCK ITERATOR | | 396K|
|* 10 | TABLE ACCESS FULL| UP_RPO_TRACE_0602 | 396K|
| 11 | PX BLOCK ITERATOR | | 35M|
|* 12 | TABLE ACCESS FULL | HI_USER_LGIN | 35M|
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("T_LGIN"."USER_ID"="T1"."REQ_ID")
filter(("T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT"
)) AND "T_LGIN"."LGIN_DT"<="T1"."REQ_DT"))
10 - access(:Z>=:Z AND :Z<=:Z)
12 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- dynamic sampling used for this statement (level=5)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
从执行计划看,说明优化器并没有按之前的计划采用索引方式扫描HI_USER_LOGIN,而是直接采用并行全表扫描的方式,说明并行不是在原来计划的基础上添加并行,但全表扫描如此大量的数据,
会降低查询速度,因此这里尝试人工干预执行计划,令其查询HI_USER_LOGIN时走索引,查看该表上对应的连接字段上有索引X_HI_USER_LGIN_2(LGIN_DT,USER_ID),因此引导优化器,
令连接的连个表采用嵌套循环连接方式,时扫描大表时,优化器能使用大表上的索引查询连接字段的值,修改hint后的执行计划
SQL_ID g1thg0qgnk745, child number 0
-------------------------------------
SELECT /*+ LEADING(T1) USE_NL(T_LGIN) PARALLEL(T_LGIN,8)
PARALLEL(T1,8) ALL_ROWS */ T1.RPO_NO
/*-testNL*/ ,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
Plan hash value: 3231175795
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 | | 0 |00:00:00.01 | 0 | 0 |
| 1 | PX COORDINATOR | | 0 | | 0 |00:00:00.01 | 0 | 0 |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 307K| 0 |00:00:00.01 | 0 | 0 |
| 3 | WINDOW SORT | | 0 | 307K| 0 |00:00:00.01 | 0 | 0 |
| 4 | PX RECEIVE | | 0 | | 0 |00:00:00.01 | 0 | 0 |
| 5 | PX SEND HASH | :TQ10000 | 0 | | 0 |00:00:00.01 | 0 | 0 |
| 6 | NESTED LOOPS | | 1 | | 185K|00:01:06.73 | 720K| 25072 |
| 7 | NESTED LOOPS | | 1 | 307K| 185K|00:00:44.02 | 538K| 6995 |
| 8 | PX BLOCK ITERATOR | | 1 | | 67750 |00:00:01.00 | 2338 | 2129 |
|* 9 | TABLE ACCESS FULL | UP_RPO_TRACE_0602 | 15 | 396K| 67750 |00:00:00.97 | 2338 | 2129 |
|* 10 | INDEX RANGE SCAN | X_HI_USER_LGIN_2 | 67750 | 16 | 185K|00:00:42.87 | 536K| 4866 |
| 11 | TABLE ACCESS BY INDEX ROWID| HI_USER_LGIN | 185K| 1 | 185K|00:00:22.49 | 181K| 18077 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access(:Z>=:Z AND :Z<=:Z)
10 - access("T_LGIN"."USER_ID"="T1"."REQ_ID" AND "T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT"))
AND "T_LGIN"."LGIN_DT"<="T1"."REQ_DT")
filter(("T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT")) AND "T_LGIN"."LGIN_DT"<="T1"."REQ_DT"))
Note
-----
- dynamic sampling used for this statement (level=5)
显然,使用并行后,需要认为使用hint干预执行计划,使其使用合理的索引,提高查询速度
SQL_ID gyn8zxr8uchgs, child number 0
-------------------------------------
SELECT /*+ LEADING(T1) USE_HASH(T_LGIN) PARALLEL(T_LGIN,8)
PARALLEL(T1,8) ALL_ROWS */ T1.RPO_NO
/*-test01*/ ,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
Plan hash value: 3061441924
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 | | 0 |00:00:00.01 | 0 | 0 |
| 1 | PX COORDINATOR | | 0 | | 0 |00:00:00.01 | 0 | 0 |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 0 | 307K| 0 |00:00:00.01 | 0 | 0 |
| 3 | WINDOW SORT | | 0 | 307K| 0 |00:00:00.01 | 0 | 0 |
| 4 | PX RECEIVE | | 0 | 307K| 0 |00:00:00.01 | 0 | 0 |
| 5 | PX SEND HASH | :TQ10001 | 0 | 307K| 0 |00:00:00.01 | 0 | 0 |
|* 6 | HASH JOIN | | 1 | 307K| 221K|00:00:46.56 | 66137 | 66027 |
| 7 | PX RECEIVE | | 1 | 396K| 513K|00:00:00.56 | 0 | 0 |
| 8 | PX SEND BROADCAST | :TQ10000 | 0 | 396K| 0 |00:00:00.01 | 0 | 0 |
| 9 | PX BLOCK ITERATOR | | 0 | 396K| 0 |00:00:00.01 | 0 | 0 |
|* 10 | TABLE ACCESS FULL| UP_RPO_TRACE_0602 | 0 | 396K| 0 |00:00:00.01 | 0 | 0 |
| 11 | PX BLOCK ITERATOR | | 1 | 35M| 4718K|00:00:06.03 | 66137 | 66027 |
|* 12 | TABLE ACCESS FULL | HI_USER_LGIN | 22 | 35M| 4718K|00:00:04.62 | 66137 | 66027 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("T_LGIN"."USER_ID"="T1"."REQ_ID")
filter(("T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT")) AND
"T_LGIN"."LGIN_DT"<="T1"."REQ_DT"))
10 - access(:Z>=:Z AND :Z<=:Z)
12 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- dynamic sampling used for this statement (level=5)
SQL_ID db4n20k7nt2bh, child number 0
-------------------------------------
SELECT /*+ LEADING(T1) USE_NL(T_LGIN) */
T1.RPO_NO /*-testNL1*/ ,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
Plan hash value: 1850855573
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |00:03:53.23 | 5480K| 16439 | 21677 |
| 1 | WINDOW SORT | | 1 | 307K| 100 |00:03:53.23 | 5480K| 16439 | 21677 |
| 2 | NESTED LOOPS | | 1 | | 1396K|00:03:50.52 | 5480K| 16397 | 0 |
| 3 | NESTED LOOPS | | 1 | 307K| 1396K|00:03:42.31 | 4111K| 16379 | 0 |
| 4 | TABLE ACCESS FULL | UP_RPO_TRACE_0602 | 1 | 396K| 513K|00:00:05.64 | 16479 | 16378 | 0 |
|* 5 | INDEX RANGE SCAN | X_HI_USER_LGIN_2 | 513K| 1 | 1396K|00:03:35.82 | 4095K| 1 | 0 |
| 6 | TABLE ACCESS BY INDEX ROWID| HI_USER_LGIN | 1396K| 1 | 1396K|00:00:06.86 | 1368K| 18 | 0 |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T_LGIN"."USER_ID"="T1"."REQ_ID" AND "T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT")) AND
"T_LGIN"."LGIN_DT"<="T1"."REQ_DT")
filter(("T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT")) AND "T_LGIN"."LGIN_DT"<="T1"."REQ_DT"))
Note
-----
- dynamic sampling used for this statement (level=2)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/750077/viewspace-1815384/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/750077/viewspace-1815384/