大量邏輯讀的瓶頸分析和優化
原創 轉載請註明出處
我的工作記錄：
昨天早上生產庫出現了大約一個小時資源耗盡的情況，分析得出語句如下：
MERGE INTO T_DPD_AGENT_CHANGE A
USING (SELECT A.AGENT_ID,
A.ENTER_COMPANY_DATE ENTER_DATE,
A.LEAVE_COMPANY_DATE LEAVE_DATE,
A.AGENT_STATUS,
D1.DEPT_ID TEAM,
D1.PRINCIPAL_ID TEAM_LEADER,
D2.DEPT_ID DEPT,
D2.PRINCIPAL_ID DEPT_LEADER,
D3.DEPT_ID REGION,
D3.PRINCIPAL_ID REGION_LEADER,
A.YEAR_MONTH
FROM T_AGENT_BACKUP A,
T_DEPT_BACKUP D1,
T_DEPT_BACKUP D2,
T_DEPT_BACKUP D3
WHERE A.AGENT_CATE = 1
AND A.YEAR_MONTH = D1.YEAR_MONTH
AND A.YEAR_MONTH = D2.YEAR_MONTH
AND A.YEAR_MONTH = D3.YEAR_MONTH
AND A.DEPT_ID = D1.DEPT_ID
AND D1.PARENT_ID = D2.DEPT_ID
AND D2.PARENT_ID = D3.DEPT_ID
UNION
SELECT A.AGENT_ID,
A.ENTER_COMPANY_DATE ENTER_DATE,
A.LEAVE_COMPANY_DATE LEAVE_DATE,
A.AGENT_STATUS,
D1.DEPT_ID TEAM,
D1.PRINCIPAL_ID TEAM_LEADER,
D2.DEPT_ID DEPT,
D2.PRINCIPAL_ID DEPT_LEADER,
D3.DEPT_ID REGION,
D3.PRINCIPAL_ID REGION_LEADER,
NULL
FROM T_AGENT A, T_DEPT D1, T_DEPT D2, T_DEPT D3
WHERE A.AGENT_CATE = 1
AND A.DEPT_ID = D1.DEPT_ID
AND D1.PARENT_ID = D2.DEPT_ID
AND D2.PARENT_ID = D3.DEPT_ID) T
ON (A.AGENT_ID = T.AGENT_ID AND A.AGENT_STATUS = T.AGENT_STATUS AND A.TEAM = T.TEAM AND A.DEPT = T.DEPT AND A.REGION = T.REGION AND NVL(A.ENTER_DATE, DATE '1900-01-01') = NVL(T.ENTER_DATE, DATE '1900-01-01') AND NVL(A.LEAVE_DATE, DATE '1900-01-01') = NVL(T.LEAVE_DATE, DATE '1900-01-01') AND (A.YEAR_MONTH = T.YEAR_MONTH OR T.YEAR_MONTH IS NULL))
WHEN NOT MATCHED THEN
INSERT
(AGENT_ID,
ENTER_DATE,
LEAVE_DATE,
AGENT_STATUS,
TEAM,
TEAM_LEADER,
DEPT,
DEPT_LEADER,
REGION,
REGION_LEADER,
YEAR_MONTH)
VALUES
(T.AGENT_ID,
T.ENTER_DATE,
T.LEAVE_DATE,
T.AGENT_STATUS,
T.TEAM,
T.TEAM_LEADER,
T.DEPT,
T.DEPT_LEADER,
T.REGION,
T.REGION_LEADER,
T.YEAR_MONTH);
在預生產環境中這個語句的效率極低。
執行計劃如下：
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------
| 1 | MERGE | T_DPD_AGENT_CHANGE | 1 | | 1 |00:06:05.16 | 180M| 4968 |
| 2 | VIEW | | 1 | | 208K|00:41:23.42 | 180M| 4954 |
| 3 | NESTED LOOPS OUTER | | 1 | 55530 | 208K|00:41:22.38 | 180M| 4954 |
| 4 | VIEW | | 1 | 55530 | 128K|00:00:02.78 | 5403 | 3575 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 5 | SORT UNIQUE | | 1 | 55530 | 128K|00:00:02.27 | 5403 | 3575 |
| 6 | UNION-ALL | | 1 | | 128K|00:00:03.50 | 5403 | 3575 |
|* 7 | HASH JOIN | | 1 | 48752 | 121K|00:00:02.72 | 4677 | 3027 |
| 8 | TABLE ACCESS FULL | T_DEPT_BACKUP | 1 | 45639 | 48600 |00:00:00.10 | 825 | 820 |
|* 9 | HASH JOIN | | 1 | 74512 | 121K|00:00:01.83 | 3852 | 2207 |
| 10 | TABLE ACCESS FULL | T_DEPT_BACKUP | 1 | 45639 | 48600 |00:00:00.10 | 819 | 0 |
|* 11 | HASH JOIN | | 1 | 113K| 121K|00:00:01.04 | 3033 | 2207 |
| 12 | TABLE ACCESS FULL| T_DEPT_BACKUP | 1 | 45639 | 48600 |00:00:00.05 | 819 | 0 |
|* 13 | TABLE ACCESS FULL| T_AGENT_BACKUP | 1 | 114K| 121K|00:00:00.26 | 2214 | 2207 |
|* 14 | HASH JOIN | | 1 | 6778 | 6976 |00:00:00.13 | 726 | 548 |
|* 15 | HASH JOIN | | 1 | 2577 | 2336 |00:00:00.04 | 165 | 48 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 16 | TABLE ACCESS FULL | T_DEPT | 1 | 2652 | 2652 |00:00:00.03 | 55 | 48 |
|* 17 | HASH JOIN | | 1 | 2614 | 2614 |00:00:00.01 | 110 | 0 |
| 18 | TABLE ACCESS FULL| T_DEPT | 1 | 2652 | 2652 |00:00:00.01 | 55 | 0 |
| 19 | TABLE ACCESS FULL| T_DEPT | 1 | 2652 | 2652 |00:00:00.01 | 55 | 0 |
|* 20 | TABLE ACCESS FULL | T_AGENT | 1 | 6976 | 6976 |00:00:00.05 | 561 | 500 |
| 21 | VIEW | | 128K| 1 | 201K|00:39:49.69 | 180M| 1379 |
|* 22 | TABLE ACCESS FULL | T_DPD_AGENT_CHANGE | 128K| 1 | 201K|00:39:47.65 | 180M| 1379 |
紅色部分就是瓶頸所在，NEST LOOP JOIN 做了大量的對內部表的FULL SCAN 在記憶體中邏輯讀，因為NEST LOOP JION 每對驅動表中有一行合適的資料就會在內部表中進行一次掃描，這裡一共對T_DPD_AGENT_CHANGE表進行了128000此全表掃描，這個不是程式碼引起的迴圈，完全是因為NEST LOOP JION實現方式的特點導致。時間花費為39分鐘，而整個語句大約在45分鐘左右。
所以我們必須改變全表掃描的訪問路徑，或者改變JION 方式。
這裡我使用的是建立聯合索引來改變其訪問方式
SQL> create index test
2 on T_DPD_AGENT_CHANGE(AGENT_STATUS ,TEAM,REGION ,LEAVE_DATE,YEAR_MONTH );
然後分析統計資料後重新執行這個語句，執行計劃如下：
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------
| 1 | MERGE | T_DPD_AGENT_CHANGE | 1 | | 1 |00:00:24.03 | 5624K|
| 2 | VIEW | | 1 | | 215K|00:00:25.91 | 5624K|
| 3 | NESTED LOOPS OUTER | | 1 | 55530 | 215K|00:00:25.26 | 5624K|
| 4 | VIEW | | 1 | 55530 | 128K|00:00:01.61 | 5391 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 5 | SORT UNIQUE | | 1 | 55530 | 128K|00:00:01.22 | 5391 |
| 6 | UNION-ALL | | 1 | | 128K|00:00:03.22 | 5391 |
|* 7 | HASH JOIN | | 1 | 48752 | 121K|00:00:02.44 | 4665 |
| 8 | TABLE ACCESS FULL | T_DEPT_BACKUP | 1 | 45639 | 48600 |00:00:00.10 | 820 |
|* 9 | HASH JOIN | | 1 | 74512 | 121K|00:00:01.78 | 3845 |
| 10 | TABLE ACCESS FULL | T_DEPT_BACKUP | 1 | 45639 | 48600 |00:00:00.05 | 819 |
|* 11 | HASH JOIN | | 1 | 113K| 121K|00:00:01.01 | 3026 |
| 12 | TABLE ACCESS FULL | T_DEPT_BACKUP | 1 | 45639 | 48600 |00:00:00.05 | 819 |
|* 13 | TABLE ACCESS FULL | T_AGENT_BACKUP | 1 | 114K| 121K|00:00:00.24 | 2207 |
|* 14 | HASH JOIN | | 1 | 6778 | 6976 |00:00:00.07 | 726 |
|* 15 | HASH JOIN | | 1 | 2577 | 2336 |00:00:00.01 | 165 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 16 | TABLE ACCESS FULL | T_DEPT | 1 | 2652 | 2652 |00:00:00.01 | 55 |
|* 17 | HASH JOIN | | 1 | 2614 | 2614 |00:00:00.01 | 110 |
| 18 | TABLE ACCESS FULL | T_DEPT | 1 | 2652 | 2652 |00:00:00.01 | 55 |
| 19 | TABLE ACCESS FULL | T_DEPT | 1 | 2652 | 2652 |00:00:00.01 | 55 |
|* 20 | TABLE ACCESS FULL | T_AGENT | 1 | 6976 | 6976 |00:00:00.02 | 561 |
| 21 | VIEW | | 128K| 1 | 215K|00:00:23.09 | 5619K|
|* 22 | TABLE ACCESS BY INDEX ROWID| T_DPD_AGENT_CHANGE | 128K| 1 | 215K|00:00:21.72 | 5619K|
|* 23 | INDEX RANGE SCAN | TEST | 128K| 1 | 6879K|00:00:17.91 | 420K|
紅色部分顯示了優化後這個訪問執行的時間，以前的39分鐘變為了不到1分鐘，所以加聯合索引的效果非常明顯。當然我是在預生產環境做的測試
，其資料量和生產差不多，雖然機器效能有出入，但是不會影響優化器對執行計劃的判斷。
當然也可以通過SQL TRACE 10046 EVENT來檢視大量的邏輯讀。但要確定瓶頸在哪裡出現還是以上的方法為好，我也檢視SQL TRACE 發現大量的邏輯讀發生在SQL 語句執行階段，這個也很好理解，在執行過程中確實出現了大量的邏輯讀，取回階段其實只有很少行
所以這個問題解決的方法就是建立聯合索引，謝謝!
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-631028/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL 效能優化之硬體瓶頸分析MySql優化
- 磁碟IO、MEM瓶頸優化優化
- 【SQL 優化】異常的邏輯讀SQL優化
- PHP的curl造成效能瓶頸，如何優化?PHP優化
- sql優化之邏輯優化SQL優化
- Oracle效能優化方法論的發展之四：基於資源瓶頸分析的優化方法論Oracle優化
- HTTP請求的TCP瓶頸分析HTTPTCP
- SAP 效能優化之監控篇-分析一個硬體瓶頸的程式路線圖[讀書筆記]優化筆記
- Oracle物理讀和邏輯讀Oracle
- web效能優化系列之網站瓶頸識別Web優化網站
- 利用PerfDog分析遊戲效能瓶頸遊戲
- Chrome執行時效能瓶頸分析Chrome
- 軟體測試：瓶頸分析方法
- 如何使用 Wireshark 分析 TCP 吞吐瓶頸TCP
- TextView效能瓶頸，渲染優化，以及StaticLayout的一些用處TextView優化
- 應用系統瓶頸排查和分析的思考-Arthas 實戰
- sql優化案例:改變表的寫法使代價和邏輯讀降下來SQL優化
- 有關oracle邏輯讀和物理讀Oracle
- 記錄node記憶體瓶頸分析記憶體
- Linux命令----分析系統I/O的瓶頸Linux
- 各種儲存效能瓶頸場景的分析與最佳化手段
- 優化邏輯Standby的資料同步效能優化
- oracle資料庫巡檢優化-快速定位資料庫瓶頸Oracle資料庫優化
- 邏輯分析
- 谷歌BERT遭遇對手，微軟UniLM AI突破大量文字處理瓶頸谷歌微軟AI
- 效能測試-服務端瓶頸分析思路服務端
- LightDB資料庫效能瓶頸分析（一）資料庫
- 如何迅速分析出系統CPU的瓶頸在哪裡？
- 邏輯Standby建立及日常管理，優化優化
- oracle 物理讀 邏輯讀的理解Oracle
- oracle 物理讀,邏輯讀的理解Oracle
- oracle資料庫巡檢優化-快速定位資料庫瓶頸（轉）Oracle資料庫優化
- 突破效能瓶頸，實現流程自動化
- 前端瓶頸如何打破？？？前端
- MySQL調優篇 | 邏輯架構解讀（1）MySql架構
- 效能分析（6）- 如何迅速分析出系統 CPU 的瓶頸在哪裡
- 打破Kafka帶來的瓶頸？Kafka
- 戳穿泡沫：對「資訊瓶頸」理論的批判性分析