一條SQL語句的執行計劃變化探究

最近有個同事碰到一個問題，想讓我給點思路。我大體瞭解了一下，是一個系統目前在做壓力測試，但是經業務反饋發現某個環節的處理時間有些長，排查了一圈，最後這件事情就落在了DB這邊，希望DB能夠給點意見，是否存在一些效能瓶頸。
    我們從開發同學那裡得到的一個基本的SQL語句，根據關鍵字從v$sql中做了提取，發現對應的SQL語句的執行時間還是OK的。

得到的SQL語句如下:
SQL_ID        SQL_FULLTEXT
------------- ----------------------------------------------------------------------------------------------------
6h4w0u8stp3z0 select APP_ID,GOODS_ID,ORDER_ID,ORDER_STATUS,GOODS_REGISTER_ID,GOODS_NUMBER,GROUP_ID,GOODS_PRICE,US
              ER_ID,ROLE_ID,ROLE_NAME,CHANNEL_ID,PUSH_NUM,PUSH_INFO from TB_ORDE
              R WHERE ORDER_ID=:1  AND USER_ID=:2  AND SUBSTR(CHANNEL_ID,0,4)=:3
這樣一個語句，如此來看效能上應該是沒有多少改進的空間了。我檢視了資料庫層面的相關統計資訊，發現DB time極低，比如Elapsed time為60分鐘，DB time就不到1分鐘，
類似下面這樣的輸出。
              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      3295 24-Aug-16 13:00:17        38        .9
  End Snap:      3296 24-Aug-16 14:00:20        44        .9
   Elapsed:               60.05 (mins)
   DB Time:                0.03 (mins)
如此之低的情況下，很難和效能問題聯絡起來。通過得到的資料情況分析，細化到ASH報告也沒有發現任何異常，所以我們可以說DB層面沒有效能瓶頸，這個問題還需要進一步的確認。
當然交代完這件事情，主要任務就完成了。就簡單再看了看這個問題。
執行計劃的情況如下，看到這樣的執行計劃似乎也沒有任何可挑剔的。

謂詞資訊如下：

看到這裡我開始有一些疑惑，作為一個訂單表，訂單號應該是作為主鍵的，看到索引的情況，發現確實是。

表結構如下所示，在分析之前還是需要說明這些基本情況的。

那麼問題就來了，按道理是需要走唯一性索引代價最低，為什麼執行計劃缺走了另外一個索引，由期望中的唯一性索引掃描變為了範圍索引掃描，這是疑點1.
解答這個問題的過程中發現，其實會引出更多的問題，原本的問題只是開始，因為後面要走的路還有很多。
對於這個問題，我們得求助於10053事件，這個診斷事件能夠從根本上解釋清楚這個原因來。
當然開啟10053，我開啟了1級，日誌量相對要更多一些。
    開啟10053事件
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
    執行SQL語句
    結束10053事件
ALTER SESSION SET EVENTS '10053 trace name context off';
    其中需要說明一點的是，如果採用如下的這種方式開啟診斷事件，是不行的。
alter session set current_schema=ordermob;
select 。。。。
    from OP_ORDER WHERE ORDER_ID='160824165342672424'  AND USER_ID='15000501196112'  AND SUBSTR(CHANNEL_ID,0,4)=5046 ;
可以使用如下的方式來代替。
select 。。。
    from ordermob.OP_ORDER WHERE ORDER_ID='160824165342672424'  AND USER_ID='15000501196112'  AND SUBSTR(CHANNEL_ID,0,4)=5046 ;
10053事件中查詢轉換結果如下：
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "OP_ORDER"."APP_ID" "APP_ID",。。。。 FROM "ORDERMOB"."TB_ORDER" "OP_ORDER" WHERE "OP_ORDER"."ORDER_ID"='160824165342672424' AND "OP_ORDER"."USER_ID"='15000501196112' AND TO_NUMBER(SUBSTR(TO_CHAR("OP_ORDER"."CHANNEL_ID"),0,4))=5046
kkoqbc: optimizing query block SEL$1 (#0)

統計資訊的一些明細資訊如下：比如CLUF是叢集因子。
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: OP_ORDER  Alias: OP_ORDER
    #Rows: 2143  #Blks:  50  AvgRowLen:  157.00  ChainCnt:  0.00
Index Stats::
  Index: IDX_OP_ORDER_PUSH_STATE  Col#: 25
    LVLS: 1  #LB: 6  #DK: 1  LB/K: 6.00  DB/K: 50.00  CLUF: 50.00
  Index: IND_ORDER_USERID  Col#: 15
    LVLS: 1  #LB: 10  #DK: 230  LB/K: 1.00  DB/K: 2.00  CLUF: 625.00
  Index: SYS_C0011155  Col#: 4
    LVLS: 1  #LB: 9  #DK: 2143  LB/K: 1.00  DB/K: 1.00  CLUF: 1271.00

而下面的這段內容就讓人更加疑惑了。Density代表列的密度，可以看到Density的值ORDER_ID對應的為0.000467，而USER_ID對應的為0.000233，
表中目前存在2000多條記錄，在Oracle中，表裡沒有直方圖資訊的時候，是按照1/NDV的形式來計算的。而這裡OERDER_ID對應的值，其實就是1/2143得到的值就是0.000467
而user_id的值如果按照1/NDV的形式的話，應該是0,0043478這樣的值，很顯然這裡不是這個值。
***************************************
1-ROW TABLES:  OP_ORDER[OP_ORDER]#0
Access path analysis for OP_ORDER
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for OP_ORDER[OP_ORDER]
  Column (#4): ORDER_ID(
    AvgLen: 19 NDV: 2143 Nulls: 0 Density: 0.000467
  Column (#15):
    NewDensity:0.000233, OldDensity:0.000233 BktCnt:2143, PopBktCnt:2084, PopValCnt:171, NDV:230
  Column (#15): USER_ID(
    AvgLen: 15 NDV: 230 Nulls: 0 Density: 0.000233
    Histogram: Freq  #Bkts: 230  UncompBkts: 2143  EndPtVals: 230

這是為什麼呢，我們來看看直方圖的資訊。可以看到ORDER_ID列是沒有直方圖資訊的，而USER_ID列卻含有。
SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,HISTOGRAM
        FROM DBA_TAB_COL_STATISTICS WHERE OWNER='ORDERMOB' AND TABLE_NAME='OP_ORDER'
COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
APP_ID                                    1           1 FREQUENCY
CHANNEL_ID                                6           6 FREQUENCY
ACCOUNT                                 230           1 NONE
ORDER_ID                               2143           1 NONE
GOODS_ID                                 28           1 NONE
USER_ID                                 230         230 FREQUENCY
CREATE_DATE                            2010         254 HEIGHT BALANCED
這裡需要提一下直方圖分為兩種，一種是頻率直方圖，顯示為：FREQUENCY，另外一種是高度平衡直方圖，顯示為：HEIGHT BALANCED
高度均衡直方圖適用於 資料分佈不均勻 ，由於列中資料很多，這時資料比較密集，不利於分析和評估，這時直方圖需要均衡化。
頻率直方圖適用於資料分佈很均勻的情況。當然如果資料很平均，其實也沒有太大的意義，直方圖本身就是適用於對應列中資料分佈比較傾斜的列（不均勻）
那麼問題似乎有了一些眉目，我們知道在Oracle中收集統計資訊的時候是推薦使用FOR ALL COLUMNS SIZE AUTO的選項的。
收集統計資訊的語句大概是這樣的形式。
exec dbms_stats.gather_table_stats(tabname => 'OP_ORDER',ownname => 'ORDERMOB',method_opt => 'FOR ALL COLUMNS SIZE AUTO');
所以我大膽做了一個測試，那就是取消直方圖的資訊。
exec dbms_stats.gather_table_stats(tabname => 'OP_ORDER',ownname => 'ORDERMOB',method_opt => 'FOR ALL COLUMNS SIZE 1');
再次檢視執行計劃，發現就會採用唯一性索引掃描了，達到了我們預期的效果。
當然問題來了，這個是為什麼呢,收集統計資訊中的auto選項是什麼含義呢。為什麼兩個資料型別一樣的(varchar2(64)）的列，境遇卻大大不同。且聽我下回慢慢道來。

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