一個SQL效能問題的優化探索（二）(r11筆記第38天)

繼續前幾天的一個案例一個SQL效能問題的優化探索（一）(r11筆記第33天)

如下的SQL語句存在索引欄位CARD_NO,但是執行的時候卻走了全表掃描，因為這是一個核心表，資料量很大，導致資料庫負載很高。

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)

     對於這個問題，苦於無法復現，通過各種途徑進行分析，有些資訊似乎有些矛盾。如果說是統計資訊的問題，但是檢視直方圖的資訊是有的。而且分為了200多個bucket，索引列card_no的統計資訊也沒有發現什麼問題。而且比較奇怪的是問題發生後檢視執行計劃情況，後面的語句是可以走索引的。這個問題怎麼來進一步分析呢。

    我們可以化被動為主動，我對這個列對應的索引新增了索引監控，即alter index xxxx monitoring usage;然後有了這個標識之後，就可以隨時檢視v$object_usage檢視索引是否被啟用，當然這個檢視得到的資訊畢竟很有限。我們只能知道索引是否被啟用，但是每次啟用的時間等這些沒法定位，而且因為走了索引本身執行效率就很高，所以在快照中是很可能不會被收編進來的。

  這個問題還有一個疑點就是統計資訊的收集時間，和同事確認問題發生當天他是沒有收集統計資訊的，但是檢視欄位last_analyzed的時間卻恰恰是發生收集以後。

   問題暫時擱置下來，在這兩天問題有了進一步的發現，另外一個報警引起了我的注意。報警是CPU使用率較高，檢視資料庫層面的負載也突然有了較大的抖動。

定位到相關的語句如下：

   SNAP_ID SQL_ID        EXECUTIONS_DELTA ELAPSED_TI PER_TOTAL
---------- ------------- ---------------- ---------- ----------
    108829 bbtwpb1aamud2                0 1766s      45%
    108829 7zmnkpfaumuk1                1 1141s      29%
    108829 4y30fbyjmrrqr                1 432s       11%
    108829 f7h0xrkw1wy48                1 192s       4%
    108829 63t40nvuuwrdp           258730 116s       2%

可以看到明顯有幾個SQL執行效率較差。

檢視TOP 1的語句發現竟然是一個JOB在執行。

SQL_FULLTEXT
----------------------------------------------------------------------------------------------------
DECLARE job BINARY_INTEGER := :job;  next_date TIMESTAMP WITH TIME ZONE := :mydate;  broken BOOLEAN
:= FALSE;  job_name VARCHAR2(30) := :job_name;  job_subname VARCHAR2(30) := :job_subname;  job_owner
 VARCHAR2(30) := :job_owner;  job_start TIMESTAMP WITH TIME ZONE := :job_start;  job_scheduled_start
 TIMESTAMP WITH TIME ZONE := :job_scheduled_start;  window_start TIMESTAMP WITH TIME ZONE := :window
_start;  window_end TIMESTAMP WITH TIME ZONE := :window_end;  BEGIN  BEGIN
dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'RECHARGE_LOG',cascade=>true);
END;  :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

語句的核心就是dbms_stats的部分，竟然是收集表recharege_log的統計資訊。

後面的幾個語句也是收集統計相關的遞迴SQL，我列舉出一個是因為這裡面有非常重要的一個資訊。先賣個關子。

$sh showsqltext.sh 7zmnkpfaumuk1
insert /*+ append */ into sys.ora_temp_1_ds_33892 select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dy
namic_sampling(0) no_monitoring */"CHARGE_DATE","SERVICEID","ORDER_NO","EXPIREDATE", rowid SYS_DS_ALIAS_0 from "TEST"."RECHARGE_LOG" sample ( 12.12452
80643) t

這個時候檢視語句的執行計劃情況，只有全表掃描的執行計劃。
明顯不合理嘛。看起來問題又想回到了原點。

這個時候不要氣餒，我們重新捋一捋資訊，列的統計資訊的情況如下：

>  select column_name,histogram from dba_tab_col_statistics
where table_name='RECHARGE_LOG';
COLUMN_NAME          HISTOGRAM
-------------------- ---------------
ID                   NONE
CN                   HEIGHT BALANCED
CARD_NO              HEIGHT BALANCED
CHARGE_DATE          HEIGHT BALANCED
IP                   HEIGHT BALANCED
POINT                FREQUENCY
PRESENT_POINT        NONE
SUCCESS              FREQUENCY
CARD_TYPE            FREQUENCY
PRODUCTNUM           NONE
SALEMETHOD           FREQUENCY
ITEM_ID              FREQUENCY
SERVICEID            NONE
ORDER_NO             HEIGHT BALANCED
REMARK               NONE
EXPIREDATE           NONE
ID                   NONE
CN                   NONE
CARD_NO              NONE
CHARGE_DATE          NONE
IP                   NONE
POINT                NONE
....

看起來有點不大對勁，因為CARD_NO的直方圖資訊是空的。

檢視另外一個資料字典，發現這個列的直方圖資訊竟然是存在的，輸出有上百行。

select to_char(endpoint_value) value,endpoint_number,column_name ,ENDPOINT_ACTUAL_VALUE from dba_tab_histograms where table_name = 'RECHARGE_LOG' and column_name in ('CARD_NO') ORDER BY endpoint_number

所以這個問題就比較有意思了，存在直方圖的列的統計資訊，但是兩個檢視中資訊卻有些衝突。

由此一來可以推斷是這個地方導致全表掃描的一個原因。

我們來看看另外一個潛在的問題，收集統計資訊的部分，可以看到是在凌晨3點執行，明顯就是一個定時任務。所以同事所說的沒有手工收集統計資訊是真實的。

select table_name,
        to_char(LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED
        from dba_tables where TABLE_NAME='RECHARGE_LOG';
TABLE_NAME                     LAST_ANALYZED
------------------------------ -------------------
RECHARGE_LOG                   2017-01-07 03:40:45 

  還有一個問題，那就是收集統計資訊，怎麼會有這些影響呢，我們得看看dbms_stats.gather_table_stats的引數method_opt,因為method_opt:決定histograms資訊是怎樣被統計的.method_opt的取值(預設值為FOR ALL COLUMNS SIZE AUTO)，也就意味著是一個動態的過程。這不card_no攤上事情了，直接忽略了，沒收集。

    關於這個問題，可以參考一個bug 

Bug 5949981 - Bad cardinality with histogram (Doc ID 5949981.8)

 而問題原因跟進一步，為什麼card_no沒有收集統計資訊。可以參考一條SQL語句的執行計劃變化探究（r10筆記第9天） 原因也是大同小異。

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