作者介紹
蔣健，雲趣網絡科技聯合創始人，11g ocm，多年oracle設計、管理及實施經驗，精通資料庫優化，oracle cbo及并行原理，曾為多個行業的客戶的 oracle 系統實施小型機到 x86跨平台遷移和資料庫優化服務。雲趣鷹眼監控核心設計和開發者，資深python web開發者。（文章審校：楊建榮）
關于本文
随着每個版本的演進，oracle預設統計資訊搜集政策更加智能和成熟。從11g 開始， oracle 建議使用dbms_stats.auto_sample_size，不需要手工使用estimate_percent設定每個表的采樣比例。
有些新的特性比如分區表的增量統計資訊，12c 的 hybrid 柱狀圖都依賴于dbms_stats.auto_sample_size。絕大部分的情況，預設dbms_stats.auto_sample_size性能和統計資訊非常理想，但是在資料極端傾斜時，dbms_stats.auto_sample_size的采樣比例過低，可能導緻柱狀圖資訊中缺乏非熱門資料的統計。當查詢非熱門資料時，優化器的估算可能不準确，進而選擇次優的執行計劃。本文就是這樣一個例子。
案例背景
客戶反映在表userssa1.linedetail的列groupno上建了索引，但是優化器還是選擇了全表掃描，以下為執行計劃，資料庫的版本為：11.2.0.2.0
表結構如下：
統計資訊分析
可以看到，優化器對于條件groupno = '0000260455'估算行數為2511k 行記錄。全表掃描是成本更低的通路路徑，看起來是一個合理的選擇。但是過濾條件groupno = '0000260455'的估算值是否正确？我們看看這個表的統計資訊：
表linedetail的統計資訊是今天早上6點鐘搜集的，列 groupno 上确實有索引。groupno 列上的統計資訊很奇怪，唯一值數量為17，柱狀圖類型為 frequency，但是num_buckets為1。如果唯一值數量為17，那麼 frequency 柱狀圖的柱狀圖數量為什麼隻1，而不是17？而且'0000260455'這個值是否為熱門的資料呢？繼續看看這個列上實際的資料分布：
可以看到絕大部分的值為空值，'0000260455'實際隻有5行資料，為什麼優化器的估計是2511k，相差50萬倍呢？繼續檢視10053事件跟蹤中的資訊：
因為 newdensity=0.5，隻有一個 bucket 記錄熱門值（null）的數量，是以對于優化器，'0000260455'為非熱門值（unpopular value），優化器使用 newdensity 作為非熱門值（unpopular value）的選擇性因子。估算值 = num_rows * newdensity = 5023103.000000 * 0.5 = 2511551.50
newdensity 和 olddensity
10053跟蹤中有兩個密度系數，newdensity 和 olddensity。olddensity的計算公式如下：
對于frenquency 柱狀圖的非熱門值算值 = numrows * olddensity = numrows * (0.5 / num_rows) = 0.5。對于沒有記錄在 frequency 柱狀圖中的非熱門值，估算值會固定為0.5, 執行計劃中會顯示為1。
newdensity 是 10.2.0.4 之後優化器通過bug 5483301 - query with predicate value non-existent in frequency histogram runs slow引入的新算法，對于沒有記錄在 frequency 柱狀圖中的非熱門值，估算值為最不熱門值的估算值的一半，而不是固定為0.5。使用 newdensity 算法，即使統計資訊沒有及時更新，非熱門值的估算值也不會被簡單粗暴的設定為1，這是優化器的一種折中，newdensity的計算公式如下：
因為隻有一個 bucket，least_popular_value = popbktcnt = 5495，endpoint_number為5495，newdensity 而計算步驟為 **newdensity = 0.5 * bkt(least_popular_value) / endpoint_number = 0.5 * 5495 / 5495 = 0.5**。導緻對于所有非熱門值，估算值為總行數的一半，完全違背了newdensity算法的設計初衷。
dbms_stats.auto_sample_size 的缺陷
優化器預設的采樣比例為dbms_stats.auto_sample_size，但是針對重複值非常高的列，采樣的比例非常底，比如 groupno 這個列，采樣的比例隻有0.1%。雖然優化器使用了approximate_ndv算法（這個算法在12c 中通過函數approx_count_distinct提供給使用者使用），可以準确地估算 groupno 列唯一值的數量為18，但是柱狀圖的 bucket 數量隻為1，并不準确。
使用dbms_stats.auto_sample_size，即使強制指定柱狀圖的 bucket 資料量為254，重新搜集統計資訊之後buckets 的數量還是1，執行計劃依然為全表掃描。
bucket的數目為什麼會不準
原因主要有兩種：
資料存在極度的傾斜，采樣時非熱門資料可能會被遺漏（參考測試1）
資料的字首相同且長度超過32位元組（假定編碼為定長單位元組編碼），這樣的資料會被誤認為同一樣本（參考測試2）
資料初始化：
測試1：資料存在極度傾斜
（資料存在極度傾斜，收集直方圖資訊時沒有被采樣到，最後的執行計劃cbo估算行數遠大于真實行數。注：前6位相同時顯著提高采樣時被忽略的機率 采用如00000開頭，可能無法重制）
測試2：相同資料字首超長（大于32位）
這種場景下，直方圖資訊endpoint_actual_value顯示樣本标簽不準确，執行計劃估算資料也完全錯誤，與之前估算約一半的資料相比，這種情況會被認為是完全命中。隻能通過手動hint寫死執行路徑解決。
endpoint_actual_value這裡資訊明顯已經不準确（00000000000000000000000000000000隻去了前32位）
從執行計劃的估算值可發現cbo其實完全估錯，也驗證了相同字首超長時cbo估算會存在問題。
解決方案1：手動指定estimate_percent
通過強制指定estimate_percent為100%，重新搜集統計資訊之後， buckets 的資料量為18，重新解析之後，sql 的執行計劃發生改變，對于條件groupno = '0000260455'的估算值為5，通過索引idx_linedetail_groupno通路資料。本案例中資料量僅百萬級，采樣使用了100%，資料量特别大的情況比例考慮降低些。
為了避免之後對表 linedetail 統計資訊的搜集，繼續使用dbms_stats.auto_sample_size，可以使用dbms_stats.set_table_prefs對表 linedetail 定制 estimate_percent的偏好，如下：
解決方案2：使用 olddensity 算法
可以通過_fix_control關閉 newdensity，使用 olddensity 算法。使用 alter system 可以在系統全局關閉改算法。
預防
文中描述的極度傾斜的情況雖然并不多見，但在生産中也可能遇見。通過查詢dba_tab_histograms中num_buckets為1的項目，可以找出這種潛在的危險（num_buckets為2等也有可能，相對可能性比較小）。
總結
雖然 oracle 優化器的算法和預設統計資訊的收集政策越來越智能，現實世界還是有一些極端情況，比如資料極端傾斜時，需要 dba 進行手工處理，以保證統計資訊的合理準确。幸運的是，dbms_stats 提供了豐富的功能，使 dba 可以在預設統計資訊搜集政策的基礎上，進行靈活的定制。
<b></b>
<b>本文來自雲栖社群合作夥伴"dbaplus"，原文釋出時間：2016-10-25</b>