性能優(yōu)化之日期字段索引引發(fā)的血案一例

IT那活兒發(fā)布于2023-01-11 13:20 / 2285人閱讀

前面看IT那活兒上不少兄弟都發(fā)表了文章，作為新四有好青年，咱也來湊湊熱鬧，畢竟會(huì)下蛋的雞（總結(jié)提煉輸出），才不是肉食雞，才是飛機(jī)中的殲21。今天和大家來分享一個(gè)日期字段索引引發(fā)的血案及分析過程。

先給出今天的主角SQL語句：

咱先來掐指一算，分析下SQL語句的結(jié)構(gòu)。此語句一打眼就是個(gè)統(tǒng)計(jì)語句，查詢包括兩張表，是一個(gè)正常的表等值關(guān)聯(lián)的查詢語句。

走過路過，但別錯(cuò)過的看看這個(gè)執(zhí)行計(jì)劃是否最優(yōu)？

通過仔細(xì)查看發(fā)現(xiàn)如上執(zhí)行計(jì)劃是錯(cuò)誤的。簡(jiǎn)單啰嗦一下執(zhí)行計(jì)劃。首先通過T1表的DATE字段選擇過濾出結(jié)果集，在將表T通過CUSTMGR字段選擇出結(jié)果集，再將兩個(gè)查詢的結(jié)果集通過NESTLOOP方式返回?cái)?shù)據(jù)。正常情況下如果連接條件不是通過函數(shù)進(jìn)行轉(zhuǎn)換且CUST_ID上有索引的話，會(huì)通過索引CUST_ID進(jìn)行NESTLOOP 返回結(jié)果。

那么我們?cè)趺粗肋x擇這種執(zhí)行計(jì)劃時(shí)不正確的呢？因?yàn)楸戎罢_的執(zhí)行計(jì)劃消耗更高。什么？我怎么知道的消耗更高？往下看就知道了嘛。

通過歷史視圖查詢對(duì)應(yīng)SQL_ID執(zhí)行歷史：

通過上面的查詢結(jié)果，我們可以發(fā)現(xiàn)在3:30 ，出現(xiàn)了錯(cuò)誤的執(zhí)行計(jì)劃。那么問題就來了，為什么會(huì)有錯(cuò)誤的執(zhí)行計(jì)劃，為什么在10號(hào)發(fā)生？

我們可以推斷此SQL是在每天3:30的時(shí)候進(jìn)行定時(shí)執(zhí)行，且10號(hào)前執(zhí)行效率很高，所有2500次的執(zhí)行均在秒內(nèi)時(shí)間就完成了。但在10號(hào)后，由于錯(cuò)誤執(zhí)行計(jì)劃出現(xiàn)，導(dǎo)致SQL執(zhí)行時(shí)間變長(zhǎng)，2500次的執(zhí)行不能在規(guī)定時(shí)間內(nèi)跑完，導(dǎo)致sql取數(shù)拖到了半天，占用白天的資源，進(jìn)而影響正常的白天業(yè)務(wù)。

那么為什么會(huì)發(fā)生這樣的情況呢？我們通過10053來仔細(xì)在查看一下：

錯(cuò)誤執(zhí)行計(jì)劃的10053trace：

可以看到提示：Usingprorated density: 0.000001 of col #9 as selectvity ofout-of-range/non-existent value pred

這個(gè)提示說明出現(xiàn)了越界現(xiàn)象。當(dāng)日期判斷的范圍超出了統(tǒng)計(jì)信息記錄的最大值和最小值的范圍，CBO無法計(jì)算選擇率，得出錯(cuò)誤的結(jié)果集，此時(shí)通過估計(jì)值進(jìn)行選擇，當(dāng)評(píng)估的值與實(shí)際情況差別很大時(shí)，就會(huì)出現(xiàn)選擇偏差。

查看表上日期字段的統(tǒng)計(jì)值

我們看到記錄的最大日志是07-10，在與SQL語句SERVICEDATE>= (SYSDATE -30)執(zhí)行計(jì)劃出錯(cuò)的日期進(jìn)行比較，發(fā)現(xiàn)8月10號(hào)-30天正好超過了記錄的最大日期值。這就是執(zhí)行計(jì)劃變化發(fā)生在8月10日的罪魁禍?zhǔn)住?/span>

現(xiàn)在咱改寫一下SQL語句，再看一下執(zhí)行計(jì)劃

執(zhí)行計(jì)劃已正確。

看一下對(duì)應(yīng)的10053trace信息

很明顯與之前錯(cuò)誤執(zhí)行計(jì)劃trace信息不同，得到的COST和結(jié)果集大小不同。

我們通過上面的分析知道了問題出現(xiàn)的原因，處理方法也很簡(jiǎn)單：

1. 收集表上統(tǒng)計(jì)信息
2. 固定SQL的執(zhí)行計(jì)劃
3. 創(chuàng)建組合索引，CUSTMGR+時(shí)間
3.2 監(jiān)控執(zhí)行計(jì)劃的變化

數(shù)據(jù)是千變?nèi)f化的，作為一個(gè)老司機(jī)，我們多伸一下手順帶考慮通過編寫監(jiān)控腳本來預(yù)防問題的后續(xù)發(fā)生，發(fā)生時(shí)第一時(shí)間知曉及介入。

腳本的實(shí)現(xiàn)思路：

1. 監(jiān)控系統(tǒng)中出現(xiàn)SQL_ID和PLAN_HASH_VALUE,1：N的情況，過濾掉OBJECT_STATUS失效的對(duì)象，正常一個(gè)SQL_ID與一個(gè)當(dāng)前真正使用的PLAN_HASH_VALUE對(duì)應(yīng)，比較執(zhí)行時(shí)間進(jìn)行報(bào)警。
2. 比較SQL_ID-PLAN_HASH_VALUE在歷史中是否出現(xiàn)過，比較執(zhí)行時(shí)間進(jìn)行報(bào)警。
3. 使用SQL審核平臺(tái)進(jìn)行監(jiān)控告警。

好了，本新四有好青年的首次春宮秀到此結(jié)束，咱們下回再見。