Oracle某行系統SQL最佳化(案例四)

chenoracle發表於2021-08-15
OracleSQL

問題說明：   

業務人員反饋電信跑批速度慢，單條SQL耗時2s左右。
而聯通和移動跑批速度正常，但條SQL耗時不超過0.2s。

環境說明：

DB:Oracle 11.2.0.4.0 RAC
OS:AIX 7.1

問題分析:

快、慢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：

--聯通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;

可以看到，執行快慢兩個SQL，只有xxxxxno條件值取值不同，其他條件相同。

其中慢的SQL，條件為xxxxxno = '2'，快的SQL，條件為xxxxxno = '1'。

那麼猜測，一定是慢的SQL條件為xxxxxno = '2'時結果集比快的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很高？

分別生成快、慢兩個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？

那麼為什麼會出現執行計劃相同，結果集小的邏輯讀反而更大呢？

問題原因：

讓我們在認真看下快慢兩條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：

--聯通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條件時，兩個SQL文字是完全一樣的，分別是對msglevel, rpttime排序後取前40條記錄。

那麼出現執行計劃相同，結果集小的邏輯讀反而更大的原因很有可能是因為：

1.SQL並沒有完全執行完order by排序完、回表完然後才去執行rownum<=40操作。

而是邊對部分排序和回表邊取出部分rownum<=40的值。

2.檢查order by msglevel, rpttime排序的兩個列都包含在同一個索引裡，

也就是這兩列排序操作不需要訪問資料塊，只需範圍索引塊，

然後回表返回其他列的值，同時邊回表，邊返回部分rownum <= 40的值。

3.那麼為什麼xxxxxno過濾性差，結果集多的邏輯讀反而少了，這是因為結果集越大，越容易找到前40條符合條件的值。

例如：

有一個大紙箱，裡面混合裝了100個球，其中紅色球80個，籃色球10個，粉色球10個，

請問在紙箱裡分別取出10個紅色球、10個籃色球、10個粉色球，哪個速度更快呢？

顯然是取出10個紅色球速度更快，因為紅色球數量最多，更容易找到10個紅色球。

本次案例也是類似的道理，因為xxxxxno條件過濾性好的結果集小，想取出前40個值時，需要掃描更多的塊，索引邏輯讀更高，速度更慢。

解決方案：

可以考慮調整組合索引，將xxxxxno列加入到組合索引中，具體方案還需要充分測試後在使用。

#####chenjuchao 2021-08-15 21:05#####

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2787034/，如需轉載，請註明出處，否則將追究法律責任。

相關文章