不走索引場景的一次分析優化
一般一個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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle對索引分析的優化Oracle索引優化
- MySQL資料SQL優化中，索引不被使用的典型場景總結MySql優化索引
- Oracle union all 不走索引的優化Oracle索引優化
- 索引失效場景索引
- PostgreSQL秒殺場景優化SQL優化
- 儀表盤場景的前端優化前端優化
- oracle優化器和不走索引的原因Oracle優化索引
- 淘特 Flutter 流式場景的深度優化Flutter優化
- 本地索引和全域性索引的適用場景索引
- HBase 寫吞吐場景資源消耗量化分析及優化優化
- HBase寫吞吐場景資源消耗量化分析及優化優化
- 高頻寫入redis場景優化Redis優化
- mysql效能優化-慢查詢分析、優化索引和配置MySql優化索引
- JVM之調優及常見場景分析JVM
- 理解索引：索引優化索引優化
- twitter storm原始碼走讀之1 -- nimbus啟動場景分析ORM原始碼
- Oracle 索引的優化Oracle索引優化
- mysql 關聯更新刪除不走索引優化MySql索引優化
- HLS與RTMP在直播場景下的優劣分析以及架構分析架構
- MSSQL優化之索引優化SQL優化索引
- twitter storm原始碼走讀之2 -- tuple訊息傳送場景分析ORM原始碼
- 前端程式設計師市場分析：前面是火海，後面是刀山，走還是不走？前端程式設計師
- Mysql索引優化之索引的分類MySql索引優化
- mysql 聯合索引的兩種特殊場景MySql索引
- MySQL系列：索引失效場景總結MySql索引
- 記一次 T-SQL 查詢優化 索引的重要性SQL優化索引
- Elasitcsearch索引優化索引優化
- MySQL索引優化MySql索引優化
- mysql優化索引MySql優化索引
- SQL優化-索引SQL優化索引
- MySQL 效能優化之索引優化MySql優化索引
- ThreadLocal的使用場景分析thread
- MySQL的索引優化分析(一)MySql索引優化
- MySQL的索引優化分析(二)MySql索引優化
- 高併發場景下如何優化伺服器的效能？優化伺服器
- 有索引卻走全表掃描的實驗分析索引
- 使用Elasticsearch的動態索引和索引優化Elasticsearch索引優化
- sql優化案例一:使用了表示式不會使用索引SQL優化索引