sql 執行計劃異常

業務某個模組單條 SQL 執行非常慢，執行時間是原來的好幾倍。問題表格對應的列上存在直方圖導致執行計劃偏差，預設情況下未特殊指定列的直方圖是收集的。

直方圖 (Histogram) 又稱質量分佈圖。是一種統計報告圖，由一系列高度不等的縱向條紋或線段表示資料分佈的情況。一般用橫軸表示資料型別，縱軸表示分佈情況。可以看出，直方圖可以用來描述資料分佈的情況。 Oracle 中也是如此，直方圖可以準確預測列資料的分佈，尤其在出現資料分佈傾斜的情況下，透過直方圖資訊，可以選擇最優的執行計劃。

此處由於 account_no 值為 3301040160005763134 數量級較大， Oracle CBO 結合 account_no 列上的直方圖計算誤認為走全表執行計劃最優。因而採用了全表掃的方式，最終導致執行效能下降。

例如，如果一到兩個值構成了表中的大部分資料 ( 資料偏斜 ) ，相關的索引就可能無法幫助減少滿足查詢所需的 I/O 數量。建立直方圖可以讓基於成本的最佳化器知道何時使用索引才最合適，或何時應該根據 WHERE 子句中的值返回表中 80 ％的記錄。

問題 sql 語句：

SELECT serial_no,

area_code,

acc_area,

trans_area,

trans_state,

account_no,

group_sub_no,

gl_class,

product_code,

cur_type,

TO_CHAR(acc_date, :"SYS_B_0"),

TO_CHAR(acc_time, :"SYS_B_1"),

TO_CHAR(sys_date, :"SYS_B_2"),

TO_CHAR(sys_time, :"SYS_B_3"),

main_code,

process_code,

acc_reason,

detail_serial,

debit_amt,

credit_amt,

balance,

pre_balance,

account_jnls_no,

trans_jnls_no,

refer_trans_no,

account_cycle_no,

clearing_times,

related_cycle_no,

TO_CHAR(related_acc_date, :"SYS_B_4"),

cash_flag,

remote_flag,

d_c_flag,

trans_flag,

summary,

summary_rem,

voucher_type,

voucher_no,

oper_no,

check_oper,

terminal_no,

channel_code,

party_type,

party_account,

party_name,

party_bank,

party_bank_name,

remark,

print_flag,

dac

FROM account_detail

WHERE account_no = :account_no__0

AND detail_serial >= :detail_serial__GE_1

ORDER BY detail_serial;

從 sql 語句上來看 account_no 和 detail_serial 列上存在聯合索引選擇性較好。

進一步排查資料庫中記錄的執行資訊。

SQL 的執行計劃來看存在兩個 hash 值和，其中值為相對於執行計劃更好一點，單條 SQL 執行時間只有 0.1 秒。

SQL 歷史執行計劃

從 SQL 12 月 1 日的歷史執行計劃來看並不穩定時好時壞，詢問後發現根據帶入的值不同執行時間也有很大偏差，其中當 account_no 帶入值為 3301040160005763134 時相應特別慢十幾分鍾都不出結果，此時本能反應統計資訊不準確或 account_no 上存在直方圖導致執行計劃不穩定，進一步排查。

account_detail 統計資訊

select to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi:ss'),NUM_ROWS,TABLE_NAME FROM DBA_TABLES WHERE TABLE_NAME='ACCOUNT_DETAIL';

LAST_ANALYZED NUM_ROWS TABLE_NAME

------------------------------------------------- ----------

2017-12-01 1:34:47 342547333 ACCOUNT_DETAIL

表格 account_detail 統計更新正常，排除統計資訊問題，進一步排查柱狀圖

檢視錶格 account_detail 列直方圖資訊

select column_name,density,num_buckets,histogram from dba_tab_col_statistics where table_name='ACCOUNT_DETAIL' and

column_name in ('ACCOUNT_NO','DETAIL_SERIAL');

COLUMN_NAME DENSITY NUM_BUCKETS HISTOGRAM

--------------- ---------- ----------- ---------------

ACCOUNT_NO .000283849 254 HEIGHT BALANCED

DETAIL_SERIAL .000204876 254 HEIGHT BALANCED

可以看到 account_no 列直方圖並非為空，而且值為 3301040160005763134 的 bucket 數較多數量級是別的值的好幾倍。這也剛好印證了 account_no 帶入其他值時執行效率正常，而帶入 3301040160005763134 時幾乎無響應。此時可以判斷是由於 account_no 列上存在直方圖導致執行計劃偏差，預設情況下未特殊指定列的直方圖是收集的。

直方圖 (Histogram) 又稱質量分佈圖。是一種統計報告圖，由一系列高度不等的縱向條紋或線段表示資料分佈的情況。一般用橫軸表示資料型別，縱軸表示分佈情況。可以看出，直方圖可以用來描述資料分佈的情況。 Oracle 中也是如此，直方圖可以準確預測列資料的分佈，尤其在出現資料分佈傾斜的情況下，透過直方圖資訊，可以選擇最優的執行計劃。

此處由於 account_no 值為 3301040160005763134 數量級較大 Oracle CBO 結合 account_no 列上的直方圖計算誤認為走全表執行計劃最優。因而採用了全表掃的方式，最終導致執行效能下降。

例如，如果一到兩個值構成了表中的大部分資料 ( 資料偏斜 ) ，相關的索引就可能無法幫助減少滿足查詢所需的 I/O 數量。建立直方圖可以讓基於成本的最佳化器知道何時使用索引才最合適，或何時應該根據 WHERE 子句中的值返回表中 80 ％的記錄。

綜合上述分析判斷，本次效能故障主要原因是表格 account_detail 列 account_no 上直方圖導致執行計劃偏差，最終導致 SQL 執行效率下降。 11G 中每列直方圖預設最多 254 個 bucket 用於存放分佈情況，而 12c 中已有很大提升，大大避免了由於直方圖導致的執行計劃異常情況。

解決辦法去掉柱狀圖的統計

EXEC DBMS_STATS.GATHER_TABLE_STATS (tabname=>'ACCOUNT_DETAIL',method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1',degree=>4,granularity=>'ALL',estimate_percent=>30,cascade=>true,no_invalidate=>false);

SQL執行計劃異常引起的效能問題

相關文章

SQL執行計劃異常 引起的效能問題

相關文章

SQL執行計劃異常引起的效能問題