關於索引掃描的極速調優實戰(第二篇)
這條sql語句執行很頻繁,目前平均執行時間在0.5秒。開發部門希望我們能不能做點最佳化,他們也在同時想辦法從業務上來最佳化這個問題。從0.5秒的情況下,能夠再提高很多,是得費很大力氣的。
況且這個問題比較緊急,從拿到sql語句開始,就感覺到一種壓力。
最開始的注意力都集中在cycle_month和cycle_year的處理上。
對於下面的部分,是這條sql語句的關鍵,cycle_year,cycle_month是在索引列中,但是根據業務邏輯,需要把cycle_year,cycle_month拼成一個數字,然後計算cycle_year+cycle_month最大的值。
目前的實現是把cycle_year準換成為字元型,然後使用這個字串在子查詢中匹配。這樣的話,cycle_year,cycle_month作為索引列就不能直接使用索引了,還得依靠第一個索引列cycle_code.
AND (TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09')) =
(SELECT MAX(TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09'))
FROM CRDT_LMT_NOTIFICATION
自己採用瞭如下的方式來改進,但是檢視收效甚微,基本沒有變化。
AND (CYCLE_YEAR,CYCLE_MONTH) =
(SELECT substr(MAX(cycle_year*100+cycle_month),0,4),substr(MAX(cycle_year*100+cycle_month),5,6)
所以看來需要索引掃描上多下點功夫。
根據sqlprofile中的提示,使用index skip scan效率最高。
但是使用index_ss卻始終都是走index range scan.
SELECT /*+index_ss(CRDT_LMT_NOTIFICATION CRDT_LMT_NOTIFICATION_PK)*/LAST_THRESHOLD, CYCLE_MONTH, CYCLE_YEAR
FROM CRDT_LMT_NOTIFICATION
WHERE CYCLE_CODE = 25
AND ITEM_ID = 15131
AND AGREEMENT_ID = 15997361
AND OFFER_INSTANCE = 223499890
AND CUSTOMER_ID = 10349451
AND (TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09')) =
(SELECT MAX(TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09'))
FROM PM9_CRDT_LMT_NOTIFICATION
WHERE CYCLE_CODE = 25
AND ITEM_ID = 15131
AND AGREEMENT_ID = 15997361
AND OFFER_INSTANCE = 223499890
AND CUSTOMER_ID = 10349451)
SQL> @plan
Plan hash value: 2310822947
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 4281 (1)| 00:00:52 | | |
| 1 | PARTITION RANGE ITERATOR | | 1 | 37 | 2141 (1)| 00:00:26 | 13 | 25 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CRDT_LMT_NOTIFICATION | 1 | 37 | 2141 (1)| 00:00:26 | 13 | 25 |
|* 3 | INDEX RANGE SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | | 2140 (1)| 00:00:26 | 13 | 25 |
| 4 | SORT AGGREGATE | | 1 | 34 | | | | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 34 | 2140 (1)| 00:00:26 | 13 | 25 |
|* 6 | INDEX RANGE SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | 34 | 2140 (1)| 00:00:26 | 13 | 25 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CYCLE_CODE"=25 AND "CUSTOMER_ID"=10349451 AND "AGREEMENT_ID"=15997361 AND "OFFER_INSTANCE"=223499890 AND
"ITEM_ID"=15131)
filter("OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND "CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131 AND
TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')= (SELECT
MAX(TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')) FROM " CRDT_LMT_NOTIFICATION"
" CRDT_LMT_NOTIFICATION" WHERE "CYCLE_CODE"=25 AND "OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND
"CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131))
6 - access("CYCLE_CODE"=25 AND "CUSTOMER_ID"=10349451 AND "AGREEMENT_ID"=15997361 AND "OFFER_INSTANCE"=223499890 AND
"ITEM_ID"=15131)
filter("OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND "CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131)
SQL> show parameter skip_scan
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_optimizer_skip_scan_enabled boolean FALSE
對於這個引數,我使用alter sessison在session級做了改動。
alter session set "_optimizer_skip_scan_enabled"=true;
然後檢視執行計劃。效率極大的提高了。
SQL> @plan
Plan hash value: 387232563
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 3 (34)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR | | 1 | 37 | 2 (50)| 00:00:01 | KEY | KEY |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CRDT_LMT_NOTIFICATION | 1 | 37 | 2 (50)| 00:00:01 | KEY | KEY |
|* 3 | INDEX SKIP SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
| 4 | SORT AGGREGATE | | 1 | 34 | | | | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 34 | 1 (0)| 00:00:01 | KEY | KEY |
|* 6 | INDEX SKIP SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | 34 | 1 (0)| 00:00:01 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CYCLE_CODE"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND
"OFFER_INSTANCE"=TO_NUMBER(:A) AND "ITEM_ID"=TO_NUMBER(:A))
filter("OFFER_INSTANCE"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND
"ITEM_ID"=TO_NUMBER(:A) AND TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')= (SELECT
MAX(TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')) FROM "PRDUSG3O"." CRDT_LMT_NOTIFICATION"
" CRDT_LMT_NOTIFICATION" WHERE "CYCLE_CODE"=TO_NUMBER(:A) AND "OFFER_INSTANCE"=TO_NUMBER(:A) AND
"AGREEMENT_ID"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND "ITEM_ID"=TO_NUMBER(:A)))
6 - access("CYCLE_CODE"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND
"OFFER_INSTANCE"=TO_NUMBER(:A) AND "ITEM_ID"=TO_NUMBER(:A))
filter("OFFER_INSTANCE"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND
"ITEM_ID"=TO_NUMBER(:A))
為什麼skip scan效率這麼高,但是使用隱含引數禁用了它呢。
產品部門的解釋是對於skip scan在大多數的場景中,效率不是很理想,基本跟index full scan的效果一樣,所以從最佳化器內部使用隱含引數禁用,就使用了range scan.
最後使用另外一個hint解決上面的顧慮。opt_param,這個hint是在10gR2之後引進的,要解決的問題就是可以避免系統級的db引數變更。
嘗試的hint格式如下。
SELECT /*+opt_param('_optimizer_skip_scan_enabled',true)*/ ....
但是執行計劃中缺還是走了range scan。資源消耗跟沒加hint一個樣。
最後發現對於這個hint需要寫為:
這樣就能夠達到預期的目標了。從0.5秒到0.01秒,絕對是效能的極大提升。
Plan hash value: 387232563
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 6 (67)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR | | 1 | 37 | 3 (67)| 00:00:01 | 13 | 25 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CRDT_LMT_NOTIFICATION | 1 | 37 | 3 (67)| 00:00:01 | 13 | 25 |
|* 3 | INDEX SKIP SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | | 3 (67)| 00:00:01 | 13 | 25 |
| 4 | SORT AGGREGATE | | 1 | 34 | | | | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 34 | 3 (67)| 00:00:01 | 13 | 25 |
|* 6 | INDEX SKIP SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | 34 | 3 (67)| 00:00:01 | 13 | 25 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CYCLE_CODE"=25 AND "CUSTOMER_ID"=10349451 AND "AGREEMENT_ID"=15997361 AND "OFFER_INSTANCE"=223499890 AND
"ITEM_ID"=15131)
filter("OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND "CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131 AND
TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')= (SELECT
MAX(TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')) FROM "PRDUSG3O"." CRDT_LMT_NOTIFICATION"
" CRDT_LMT_NOTIFICATION" WHERE "CYCLE_CODE"=25 AND "OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND
"CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131))
6 - access("CYCLE_CODE"=25 AND "CUSTOMER_ID"=10349451 AND "AGREEMENT_ID"=15997361 AND "OFFER_INSTANCE"=223499890 AND
"ITEM_ID"=15131)
filter("OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND "CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131)
可能這個問題到此就告一段落了,我在得到了一個初步的結論之後和開發部門進行協調,他們也試圖從業務上進行簡化。
最後他們把糾結的cycle_month和cycle_year的拼接去除了。改為在程式中處理。
與其說是改進不是直接說是簡化。
SELECT
LAST_THRESHOLD, CYCLE_MONTH, CYCLE_YEAR
FROM PM9_CRDT_LMT_NOTIFICATION
WHERE ITEM_ID = :a
AND AGREEMENT_ID = :a
AND CYCLE_CODE = :a
AND OFFER_INSTANCE = :a
AND CUSTOMER_ID = :a
AND CYCLE_YEAR=:a
AND CYCLE_MONTH=:a
這條sql語句直觀來看肯定是走唯一性掃描,但是效果有多好呢。可以看看幾個指標,都已經達到了最低。
Plan hash value: 404442430
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 37 | 1 (0)| 00:00:01 | KEY | KEY |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CRDT_LMT_NOTIFICATION | 1 | 37 | 1 (0)| 00:00:01 | KEY | KEY |
|* 3 | INDEX UNIQUE SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CYCLE_CODE"=TO_NUMBER(:A) AND "CYCLE_MONTH"=TO_NUMBER(:A) AND "CYCLE_YEAR"=TO_NUMBER(:A) AND
"CUSTOMER_ID"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND "OFFER_INSTANCE"=TO_NUMBER(:A) AND
"ITEM_ID"=TO_NUMBER(:A))
在協調部署之後。速度有了極大的提升。
處理的事務數有了近10倍的提升。從十萬事務到近百萬事務 ,處理的速度還是提升了很多。
以下是事務處理的一些反饋資料。可以看到效果還是很明顯的。
TIME |
COUNT |
20141212 00 |
119844 |
20141212 01 |
57357 |
20141212 02 |
23153 |
20141212 03 |
20610 |
20141212 04 |
111148 |
20141212 05 |
102540 |
20141212 06 |
59834 |
20141212 07 |
213985 |
20141212 08 |
69733 |
20141212 09 |
137163 |
20141212 10 |
163106 |
20141212 11 |
87091 |
20141212 12 |
89880 |
20141212 13 |
841172 |
20141212 14 |
960209 |
20141212 15 |
948309 |
20141212 16 |
899030 |
20141212 17 |
870231 |
20141212 18 |
953362 |
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25723371/viewspace-1382227/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20210220]全索引掃描快速索引掃描的邏輯讀.txt索引
- 全表掃描和全索引掃描索引
- 【Oracle】 索引的掃描方式Oracle索引
- MySQL中的全表掃描和索引樹掃描MySql索引
- 全表掃描和全索引掃描繼續(PG-TiDB)索引TiDB
- [20190815]索引快速全掃描的成本.txt索引
- 關於被動式掃描的碎碎念
- AppBoxFuture: 二級索引及索引掃描查詢資料APP索引
- PostgreSQL技術內幕(七)索引掃描SQL索引
- DAST 黑盒漏洞掃描器 第二篇:規則篇AST
- MySQL——索引優化實戰MySql索引優化
- Android 基於zxing的二維碼掃描功能的簡單實現及優化Android優化
- 關於Hadoop調優Hadoop
- Java效能調優實戰-劉超-極客時間-返現優惠Java
- 實戰 nginx 調優Nginx
- 效能調優實戰
- 掃描器的存在、奧普 掃描器
- win10系統mcafee實時掃描怎麼關閉_win10系統mcafee實時掃描如何關閉Win10
- MySQL調優篇 | SQL調優實戰(5)MySql
- 京東掃描平臺EOS—JS掃描落地與實踐JS
- 索引掃描可能不如全表掃描的場景的理解__純粹資料量而言,不涉及CLUSTERING_FACTOR索引
- 樹莓派控制調速以及關於PWM的思考樹莓派
- 數倉調優實戰:GUC引數調優
- win10 安全中心關閉定期掃描方法 如何關閉win10自動掃描Win10
- MySQL調優之索引優化MySql索引優化
- AWVS掃描器掃描web漏洞操作Web
- Go 實現埠掃描器Go
- win10怎麼關閉安全掃描 win10正在執行安全掃描如何關閉Win10
- PHP 快速掃描列表建立無限極分類樹PHP
- win10怎麼關閉defender自動掃描_win10關閉defender自動掃描的步驟Win10
- 記憶體調優實戰記憶體
- 理解資料庫掃描方法-利用掃描方法對資料儲存進行優化資料庫優化
- win10系統掃描器提示掃描不到掃描器如何解決Win10
- 掃描器
- Python3 網路爬蟲實戰的視訊和掃描版書Python爬蟲
- MindSpore模型精度調優實戰:常用的定位精度除錯調優思路模型除錯
- 用Python實現圖片的清晰掃描Python
- 關於索引索引
- 掃描王 for Mac專業圖片掃描工具Mac