問(wèn)題說(shuō)明：
業(yè)務(wù)人員反饋電信跑批速度慢，單條SQL耗時(shí)2s左右。 而聯(lián)通和移動(dòng)跑批速度正常，但條SQL耗時(shí)不超過(guò)0.2s。
環(huán)境說(shuō)明：
DB:Oracle 11.2.0.4.0 RAC OS:AIX 7.1
問(wèn)題分析:
快、慢SQL文本如下：
慢SQL:
--電信1.6-1.8秒 select msgserial, objaddr, endtime, retrytimes, srvtype, msgcont, report from (select msgserial, objaddr, endtime, retrytimes, srvtype, msgcont, report from CJCTABXXXX where msgstat = '0' and srcaddr = '11111' and xxxxxno = '2' and lastsndtime < to_char(sysdate, 'yyyymmddHH24MISS') order by msglevel, rpttime) where rownum <= 40;
快SQL：
--聯(lián)通0.1-0.2秒 select msgserial, objaddr, endtime, retrytimes, srvtype, msgcont, report from (select msgserial, objaddr, endtime, retrytimes, srvtype, msgcont, report from CJCTABXXXX where msgstat = '0' and srcaddr = '11111' and xxxxxno = '1' and lastsndtime < to_char(sysdate, 'yyyymmddHH24MISS') order by msglevel, rpttime) where rownum <= 40;
可以看到，執行快慢兩個(gè)SQL，只有xxxxxno條件值取值不同，其他條件相同。
其中慢的SQL，條件為xxxxxno = '2'，快的SQL，條件為xxxxxno = '1'。
那么猜測，一定是慢的SQL條件為xxxxxno = '2'時(shí)結果集比快的SQL大，所有速度才慢的，需要檢查xxxxxno不同取值下結果集大小。
---數據量: 74418(慢SQL)
select count(*) from (select msgserial, objaddr, endtime, retrytimes, srvtype, msgcont, report from CJCTABXXXX where msgstat = '0' and srcaddr = '11111' and xxxxxno = '2' and lastsndtime < to_char(sysdate, 'yyyymmddHH24MISS') order by msglevel, rpttime)
---數據量:411628(快SQL)
select count(*) from (select msgserial, objaddr, endtime, retrytimes, srvtype, msgcont, report from CJCTABXXXX where msgstat = '0' and srcaddr = '11111' and xxxxxno = '1' and lastsndtime < to_char(sysdate, 'yyyymmddHH24MISS') order by msglevel, rpttime)
疑惑1？
慢SQL的結果集有74418，遠小于快SQL的結果集411628，為什么慢SQL結果集小，邏輯讀反而很大呢？
難道是慢的SQL執行計劃選錯了，導致雖然結果集小，但是cost很高？
分別生成快、慢兩個(gè)SQL執行計劃：
慢SQL執行計劃如下：
Elapsed: 00:00:01.98 Execution Plan ---------------------------------------------------------- ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 37 | 21904 | 1424 (1)| | 1 | COUNT STOPKEY | | | | | | 2 | VIEW | | 37 | 21904 | 1424 (1)| | 3 | TABLE ACCESS BY INDEX ROWID| CJCTABXXXX | 37 | 8658 | 1424 (1)| | 4 | INDEX RANGE SCAN | ICJCTABXXXX_4 | 1757 | | 49 (0)| ----------------------------------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 682865 consistent gets 0 physical reads 0 redo size 6636 bytes sent via SQL*Net to client 542 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 40 rows processed
快SQL執行計劃如下：
Execution Plan ---------------------------------------------------------- ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 40 | 23680 | 310 (0)| | 1 | COUNT STOPKEY | | | | | | 2 | VIEW | | 40 | 23680 | 310 (0)| | 3 | TABLE ACCESS BY INDEX ROWID| CJCTABXXXX | 40 | 9360 | 310 (0)| | 4 | INDEX RANGE SCAN | ICJCTABXXXX_4 | 1757 | | 13 (0)| ----------------------------------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 16850 consistent gets 0 physical reads 0 redo size 5866 bytes sent via SQL*Net to client 542 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 40 rows processed
可以看到執行計劃也完全一樣，都是走的ICJCTABXXXX_4索引范圍掃描，
慢的SQL邏輯讀consistent gets(682865)遠高于快的SQL邏輯讀consistent gets(16850)。
疑惑2？
那么為什么會(huì )出現執行計劃相同，結果集小的邏輯讀反而更大呢？
問(wèn)題原因：
讓我們在認真看下快慢兩條SQL：
慢SQL:
--電信1.6-1.8秒 select msgserial, objaddr, endtime, retrytimes, srvtype, msgcont, report from (select msgserial, objaddr, endtime, retrytimes, srvtype, msgcont, report from CJCTABXXXX where msgstat = '0' and srcaddr = '11111' and xxxxxno = '2' and lastsndtime < to_char(sysdate, 'yyyymmddHH24MISS') order by msglevel, rpttime) where rownum <= 40;
快SQL：
--聯(lián)通0.1-0.2秒 select msgserial, objaddr, endtime, retrytimes, srvtype, msgcont, report from (select msgserial, objaddr, endtime, retrytimes, srvtype, msgcont, report from CJCTABXXXX where msgstat = '0' and srcaddr = '11111' and xxxxxno = '1' and lastsndtime < to_char(sysdate, 'yyyymmddHH24MISS') order by msglevel, rpttime) where rownum <= 40;
在不考慮xxxxxno條件時(shí)，兩個(gè)SQL文本是完全一樣的，分別是對msglevel, rpttime排序后取前40條記錄。
那么出現執行計劃相同，結果集小的邏輯讀反而更大的原因很有可能是因為：
1.SQL并沒(méi)有完全執行完order by排序完、回表完然后才去執行rownum<=40操作。
而是邊對部分排序和回表邊取出部分rownum<=40的值。
2.檢查order by msglevel, rpttime排序的兩個(gè)列都包含在同一個(gè)索引里，
也就是這兩列排序操作不需要訪(fǎng)問(wèn)數據塊，只需范圍索引塊，
然后回表返回其他列的值，同時(shí)邊回表，邊返回部分rownum <= 40的值。
3.那么為什么xxxxxno過(guò)濾性差，結果集多的邏輯讀反而少了，這是因為結果集越大，越容易找到前40條符合條件的值。
例如：
有一個(gè)大紙箱，里面混合裝了100個(gè)球，其中紅色球80個(gè)，籃色球10個(gè)，粉色球10個(gè)，
請問(wèn)在紙箱里分別取出10個(gè)紅色球、10個(gè)籃色球、10個(gè)粉色球，哪個(gè)速度更快呢？
顯然是取出10個(gè)紅色球速度更快，因為紅色球數量最多，更容易找到10個(gè)紅色球。
本次案例也是類(lèi)似的道理，因為xxxxxno條件過(guò)濾性好的結果集小，想取出前40個(gè)值時(shí)，需要掃描更多的塊，索引邏輯讀更高，速度更慢。
解決方案：
可以考慮調整組合索引，將xxxxxno列加入到組合索引中，具體方案還需要充分測試后在使用。
#####chenjuchao 2021-08-15 21:05#####
免責聲明：本站發(fā)布的內容（圖片、視頻和文字）以原創(chuàng )、來(lái)自互聯(lián)網(wǎng)轉載和分享為主，文章觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權請聯(lián)系QQ:712375056 進(jìn)行舉報，并提供相關(guān)證據，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權內容。
Copyright ? 2009-2021 56dr.com. All Rights Reserved. 特網(wǎng)科技 特網(wǎng)云 版權所有 珠海市特網(wǎng)科技有限公司 粵ICP備16109289號
域名注冊服務(wù)機構:阿里云計算有限公司（萬(wàn)網(wǎng)） 域名服務(wù)機構：煙臺帝思普網(wǎng)絡(luò )科技有限公司（DNSPod） CDN服務(wù)：阿里云計算有限公司 中國互聯(lián)網(wǎng)舉報中心 增值電信業(yè)務(wù)經(jīng)營(yíng)許可證B2
建議您使用Chrome、Firefox、Edge、IE10及以上版本和360等主流瀏覽器瀏覽本網(wǎng)站
