一個SQL效能問題的優化探索

今天同事問我一個問題，看起來比較常規，但是仔細分析了一圈，發現實在是有些暈，我隱隱感覺這是一個bug，但是有感覺問題還有很多需要確認和理解的細節。

    同事說有個10gR2的資料庫SQL語句有效能問題，DB time有報警，但是還沒有明確的思路，讓我幫忙看看有什麼好的建議。我也沒有問是哪個SQL語句，根據IP登入到伺服器端檢視了下資料庫的負載，發現負載已經有了較大的波動。這是事後補充的一張較為全面的DB time監控圖。

   然後根據負載的情況，定位了大體的時間範圍，就大體是11點左右。

得到了一個SQL的DB time佔比圖，可以很清楚看到是一個SQL導致了效能消耗極大。

 SNAP_ID SQL_ID        EXECUTIONS_DELTA ELAPSED_TI PER_TOTAL
---------- ------------- ---------------- ---------- ----------
    108652 5dw94xr3c8hy8                4 4338s      92%
    108652 0p1ntk9bv0hrr             6582 20s        0%
    108652 2bfm7hrkwu0s7             1240 0s         0%
    108652 3qd7k5q9gsnmx              434 4s         0%
    108652 4c075dx0fqsjk             7530 1s         0%    馬上定位到這個SQL語句，和同事確認，就是這個語句，看語句的執行情況，可以看到執行的效率很差,這個SQL語句大體是下面的樣子。

SQL_FULLTEXT
----------------------------------------------------------------------------------------------------
SELECT ID,CN,CARD_NO,TO_CHAR(CHARGE_DATE,'yyyy-MM-dd HH24:mi:ss') AS CHARGE_DATE ,IP,POINT,PRESENT_P
OINT,SUCCESS,CARD_TYPE,PRODUCTNUM,SALEMETHOD,ITEM_ID,SERVICEID,ORDER_NO,TO_CHAR(EXPIREDATE,'yyyy-MM-
dd HH24:mi:ss') AS EXPIREDATE,REMARK FROM RECHARGE_LOG WHERE CARD_NO = TO_NUMBER(:1)

   看到這個地方，很明顯是SQL的效能問題，而效能瓶頸似乎在於過濾條件，是不是隱式轉換導致的全表掃描呢？

   檢視執行計劃，我發現說對了一半，確實是全表掃描，而另一半就很難說得清楚了。因為這個表的欄位CARD_NO資料型別是number（20,0），對於隱式轉換，其實對於varchar2->number 的方式是相容的，而反過來number->varchar2就很容易出現問題，具體可以參考：

MySQL和Oracle中的隱式轉換（r6筆記第45天)

語句的執行計劃如下：

檢視謂詞資訊可以清楚的看到是一個filter的過濾條件，難道這個表沒有索引，或者索引有什麼特別之處？

然後這是一個很常規的非唯一性索引。

CREATE INDEX "BILL"."IDX_RECHARGE_LOG_CARD_NO" ON "BILL"."RECHARGE_LOG" ("CARD_NO")

有索引卻不用，這個實在有些奇怪，看到這裡就開始有些疑惑了。

是不是繫結變數的值有一些問題。如果要檢視這類資訊有兩種方式，一種是從快取中檢視V$SQL_BIND_CAPTURE，另外一種是從快照資料中檢視dba_hist_sqlbind得到。

select instance_number, sql_id,name, datatype_string, last_captured,value_string from dba_hist_sqlbind where sql_id='5dw94xr3c8hy8' order by LAST_CAPTURED,POSITION;

輸出的結果如下：

SQL_ID        NAME   DATATYPE_STRING LAST_CAPTURE VALUE_STRING
- ------------- ------ --------------- ------------ ------------------------------
5dw94xr3c8hy8 :1     VARCHAR2(128)   03-JAN-17    71307116310414511505
5dw94xr3c8hy8 :1     VARCHAR2(128)   03-JAN-17    71447219414105360326
5dw94xr3c8hy8 :1     VARCHAR2(128)   03-JAN-17    71447219414105360326

可以看到，這個繫結變數無可挑剔，是一個標準的字元型別。

而通過客戶端模擬復現的時候，發現輸出卻大大不同，因為嘗試模擬，卻發現優化器會自動去走索引掃描而非全表掃描。

這樣一個輸出結果就很尷尬了。

嘗試了多種方式復現，但是優化器都會乖乖的走索引。

如果這是一個很緊急的情況下，需要迅速做出響應，那麼可以注意幾個地方，一個是統計資訊，一個是執行計劃。統計資訊和同事確認，他說之前收集過一次，而且檢視統計資訊的情況，竟然收集時間是今天，這一點值得進一步確認，因為沒有手工觸發過，另外一點就是執行計劃，如果執行計劃發現了變化，對於一個核心業務，影響範圍較大的SQL語句，需要儘快修復，修改執行計劃就是一種快速解決方式，當然對於這個問題，檢視歷史情況，這個語句的執行頻率並不高，而且效能SQL也已經在執行中，我們先把問題確認清楚，再操作不遲。

除了需要確認統計資訊收集時間外，還需要確認準確度,大體來看，欄位也存在直方圖，bucket數為254。

SQL>select owner,table_name,
               column_name,
               utl_raw.cast_to_number(low_value) low,
               utl_raw.cast_to_number(high_value) hight
    from dba_tab_col_statistics
         where table_name = 'RECHARGE_LOG'
          and column_name = 'CARD_NO'
OWNER    TABLE_NAME       COLUMN_NAM        LOW                          HIGHT
------------------------- ---------- ---------- ------------------------------
TEST     RECHARGE_LOG     CARD_NO             0           71657919645407470488

  可以通過SQLT得到這條語句執行計劃的變化，可以赫然看到執行計劃的天壤之別。

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     1818596128        .013
      878630897    4247.848

兩者在引數設定上看起來是一樣的，唯一的就是掃描路徑不同，一個索引，一個全表掃描。

走索引的部分：

DBMS_LOB.CLOSE(sql_txt);
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.4')]',
q'[OPT_PARAM('optimizer_index_cost_adj' 30)]',
q'[OPT_PARAM('optimizer_index_caching' 90)]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[INDEX_RS_ASC(@"SEL$1" "RECHARGE_LOG"@"SEL$1" ("RECHARGE_LOG"."CARD_NO"))]',
q'[END_OUTLINE_DATA]');

走全表掃描的部分:

DBMS_LOB.CLOSE(sql_txt);
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.4')]',
q'[OPT_PARAM('optimizer_index_cost_adj' 30)]',
q'[OPT_PARAM('optimizer_index_caching' 90)]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "RECHARGE_LOG"@"SEL$1")]',
q'[END_OUTLINE_DATA]');

 所以上面的資訊，可以看出影響這個執行計劃，還和引數optimizer_index_cost_adj和optimizer_index_caching有關，但是根據經驗值，似乎也在合理範圍內。所以，看起來合理的資料，得到了一個不合理的結果。解釋起來就很糾結了。

   預知後事如何，且聽下回分解。

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