【沃趣科技】直方圖系列1

沃趣科技作為國內領先的資料庫雲平臺解決方案提供商，一直致力於企業級資料庫雲平臺產品的研發，為使用者提供高效能、高可用、可擴充套件的的資料庫雲環境及不同業務場景需求的資料庫平臺，滿足客戶對極致效能、資料安全、容災備份、業務永續等需求。沃趣科技憑藉專業的團隊，優質的產品，前沿的技術，貼心的服務贏得了客戶的信任與尊重，也獲得了市場的認同。

————————————————————————————————————————————————————

在這個關於直方圖的簡短系列中，我們將討論為什麼需要直方圖以及Oracle建立它們的方法。我們將評估建立它們的成本以及給我們帶來不必要的問題，然後我們將檢查它在您的執行計劃中給您帶來潛在的問題。這篇文章的概述將侷限於12c之前的Oracle版本，其中出現了在減少開銷和提高穩定性的新型直方圖和收集方法。

一個簡單的例子

不久前，我的一個客戶在他們的應用程式的一個非常簡單的部分中發現了一些奇怪的效能問題。他們在處理一個線上銷售系統，在一天的過程中，他們需要執行表單的常規報告:“向我顯示最近下的訂單，但尚未發出”。這個需求變成了一個非常簡單的SQL：

select {list of columns}
from orders
where      status = ‘R’
order by
order_id
;

在一天中的任何一個時刻，只有少量的訂單與這個謂詞匹配——一個包含數百萬行的表中有一兩百個訂單。 在status列上有一個索引，以允許有效的訪問路徑，由於簡單的原因，新訂單將位於表的一部分中，該部分是新增到表中的最新塊組，並且這些塊將被快取在記憶體中，因此假設查詢非常快的執行。 問題是，在某些時間，該報告需要幾十秒才能執行出結果，而不是即時出現結果的。

當然，第一個要檢查的是檢查執行計劃是否符合預期，即查詢是否試圖做一些有效的事情。當查詢快速執行時，Oracle正在使用預期的索引，當查詢緩慢執行時，Oracle正在執行表掃描。所以問題從“為什麼查詢速度慢？”到“為什麼最佳化器有時認為表掃描是個更好的建議呢”？這兩個方面來思考這個問題。

看過了以上業務活動描述，以及給出了文章的標題，您可能已經有了一個非常好的想法：這個資料集非常的傾斜、當最佳化器“看到”傾斜時，我們得到了正確的計劃，當最佳化器沒有看到傾斜時，我們得到了錯誤的計劃。

下面的查詢（針對資料模型）突出顯示了問題的型別：  

select status, count(*)
from orders
group by status
order by
    status
;
    S       COUNT(*)
    C       529,100
    P           300
    R           300
    S           300
    X       500,000

如您所見，大多數資料最終都處於兩種狀態之一（取決於訂單最終到達客戶的方式），少量資料分散在其他幾個值上。當您看到這樣的資料並知道您需要訪問“稀有值”或“熱資料”值時，您的想法可能會轉向兩個方向之一：虛擬列（這可能意味著基於函式的索引，或虛擬列的11g實現，甚至11g“擴充套件統計”）或直方圖。

虛擬列

在我看來，最好的解決方案來自虛擬列（或11g以前的基於函式的索引），因為這允許我們維護一個非常小的、精確定位的索引，儘管資料集很大。因此，我們可以建立如下索引並收集統計資訊：

create index ord_new on orders(
    case status when 'R' then 'R' else null end
);
begin
    dbms_stats.gather_table_stats(
        user,
        'orders',
        method_opt => 'for all hidden columns size 1'
    );
end;
/

儘管我需要收集包含索引定義的隱藏列的統計資訊，但是在建立索引後收集所有隱藏列的統計資訊代價可能很高，這樣我就可以檢查user_tab_cols 列以獲取最新的列名，這將類似於sys_nc00037$，並僅收集該特定列的統計資訊列。 （注意： 技術上，“else null”是多餘的，但我更喜歡顯式地包含最終選項。）

當然，我可能希望對其他不常訪問的值執行類似的查詢，以便可以再建立兩個類似於上面的索引，或者建立一個包含這三個值的索引—下面是使用11g虛擬列方法的示例：

alter table orders
add (
    interesting_status    generated always as (
        case status
             when 'C' then null
             when 'X' then null
                    else status
        end
    ) virtual
)
/
begin
    dbms_stats.gather_table_stats(
        user,
        'orders',
        method_opt => 'for columns interesting_status size 1'
    );
end;
/
create index ord_is on orders(interesting_status);

基於虛擬列/函式的索引方法（無論您使用哪種方法）有一個限制—您必須更改應用程式程式碼以利用它—11g中的“適當”虛擬列使程式碼看起來比FBI程式碼更整潔，但仍必須進行更改，例如（對於我給出的FBI示例）： 

select {list of columns}
from orders
where      case status when 'R' then 'R' else null end = ‘R’
order by
    order_id
;

直方圖

如果我們不能改變應用程式程式碼該怎麼辦？ 我們必須確保最佳化器知道這個問題，因為如果我們不知道，那麼基本最佳化器模型將對基數（行計數）產生錯誤的估計，並選擇錯誤的執行路徑。 在最簡單的級別上，我們為最佳化器收集的統計資訊將顯示： “表中有1030000行，此列有5個不同的值，沒有空值，並且這些值從‘C’均勻分佈到‘X’。 ”。 有了這些資訊，最佳化器對謂詞“status='C'”的基數估計將派生為： 總行數/非重複值數=206000。 當然，假設使用100%個樣本（估計百分比＝＝100）來收集統計資料； 如果使用大於11G的版本，或者11G中還沒有轉換成“近似NDV”機制，則結果可能稍微不那麼可預測。

這就是直方圖發揮作用的地方——它們允許我們向最佳化器提供有關列中值分佈的更詳細資訊。 在12c之前，它們有兩種型別： 頻率直方圖和高度平衡直方圖——在我們的例子中，我們需要一個頻率直方圖。 （注： 12c有兩種新的直方圖型別： Top-N和hybrid）。

原則上，頻率直方圖是一段時間內資料的精確影像，而高度平衡直方圖是資料分佈的近似影像，它試圖捕捉頻繁訪問值的細節和其餘部分的不均勻部分。 當一列包含的不同值不超過254個（12c中為2048個）時，就可以建立一個頻率直方圖，而高度平衡直方圖的精確度要低得多，並且不能真正捕獲超過127個頻繁值的資訊。 在本文的其餘部分，我將直接使用頻率直方圖而不使用高度平衡直方圖。

頻率直方圖

在我們的示例中，我們只有5個不同列值，而模型資料集僅包含超過1百萬行。我可以讓Oracle透過收集具有以下方法引數設定的表stats來收集列的直方圖：“for columns status size 254”。（注意，雖然我知道只有5個值，我也可以要求最大值，Oracle會發現5是足夠的）。如果我還將estimate_percent設定為100，則在該列的“user_tab_histograms ”檢視中將出現以下結果： 

select
    ep_let             status,
    endpoint_number - ep_lag   ct,
    ep_val
from
  (
select
    to_char(endpoint_value,'FMxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')       ep_val,
    chr(
          to_number(
          substr(to_char(endpoint_value,'FMxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'),1,2),
          'XX'
          )
    )     ep_let,
    endpoint_number,
    lag(endpoint_number,1,0) over(order by endpoint_number) ep_lag
from
    user_tab_histograms
where
    table_name = 'ORDERS'
and      column_name = 'STATUS'
    )
order by
    ep_let
/
S         CT EP_VAL
- ---------- -------------------------------
C     529100 43202020202009e7ff86dab2800000
P        300 50202020202016f62c60904c200000
R        300 52202020202029a671551080e00000
S        300 53202020202032fe93cf509b400000
X     500000 5820202020202b817684cb40800000

我已經展示了一種將endpoint_value從其內部數字形式轉換為等效字元形式的方法，如果你的手邊有ASCII碼，你會發現端點值的十六進位制表示後面附加了很多空格（0x20）-有關轉換的實際執行方式的詳細資訊，請參見：

請注意，直方圖有效地儲存為累積頻率，我使用lag（）分析函式對其進行了分解，該函式允許您看到Oracle為資料中的每個不同值儲存精確的計數。

當最佳化器根據謂詞“status='R'”計算基數時，有了這些資訊（並假設我的SQL確實使用了文字值），它可以檢查這是直方圖中的一個值，並報告它在其中記錄的計數。 在這種特殊情況下，頻率直方圖是一個巨大的幫助，所以，您可能會問，為什麼我們不簡單地為應用程式中的每一列（或者可能只是出現在where子句中的列）建立直方圖呢？

具有頻率直方圖的威脅

直方圖有四個主要的缺陷，我可以用以下要點來標記，然後我將依次檢查：

• 它們不能很好地與繫結變數混合。

• 它們的計算成本很高。

• 取樣時它們可能非常不穩定。

• 你必須在適當的時候收集它們。

我的觀點是，如果查詢使用文字值，最佳化器就能夠從直方圖中選擇正確的條目。 如果您對此查詢使用了繫結變數，那麼最佳化器將在第一個解析呼叫上使用“bind peeking”，並仍然生成正確的基數（和執行計劃）； 但是直到11g中的“自適應遊標共享”和12c中的“自適應執行計劃”出現，這一個計劃（本質上）是您為查詢的所有後續執行保留的計劃，無論繫結變數的值如何更改。 在我的例子中，bind變數本來是可以的，因為對這個表的唯一查詢是“status={rare value}”的非常簡單的查詢，而狀態“R”的計劃對於“P”和“s”來說是可以的，但通常情況下你不會那麼幸運。 如果您已經在列上建立了直方圖，那麼您應該期望在應用程式中做一些事情，讓最佳化器能夠很好地處理直方圖，這可能意味著在where子句中使用文字，這可能意味著要做一些更微妙的事情，比如編寫應用程式程式碼來檢查使用者的請求，並從一個簡短的列表中選擇最合適的SQL在每種情況下執行。

假設您已經找到了如何在程式碼中最好地使用頻率直方圖的方法，那麼在最佳化器希望讀取直方圖時，您仍然存在無法確保直方圖準確的問題。 下面是我告訴它以100%的樣本量收集上述資料的頻率直方圖時執行的SQL Oracle：  

select
    substrb(dump(val,16,0,32),1,120) ep,
    cnt
from     (
    select
        /*+
            no_expand_table(t) index_rs(t)   no_parallel(t)
            no_parallel_index(t) dbms_stats cursor_sharing_exact
            use_weak_name_resl dynamic_sampling(0) no_monitoring
            no_substrb_pad
        */
        max("STATUS") val,
        count(*) cnt
    from
        "TEST_USER"."ORDERS" t
    where
        "STATUS" is not null
    group by
        nlssort("STATUS", 'NLS_SORT = binary')
    )
order by
    nlssort(val,'NLS_SORT = binary')
;

準確的查詢將取決於Oracle的版本以及Oracle是否認為該列需要頻率直方圖或高度平衡直方圖，但一般原則是，您將看到一個聚合查詢，它將處理大量資料，並且將為您標識的每一列顯示查詢的變體作為直方圖的目標。 收集直方圖是一項的操代價很高的操作。

您可以透過取樣而不是計算來降低收集直方圖的成本。 在執行此操作時，您將看到類似的SQL出現，儘管有一些變化，特別是Oracle經常會將原始行的樣本複製到它為此目的建立的全域性臨時表中，然後對全域性termporary表執行查詢。 這可能導致構建直方圖所做的工作要少得多，但它帶來了不同的威脅。 下面是我給Oracle提供“auto_sample_size”選項來收集直方圖時原始資料的直方圖內容：

S         CT EP_VAL
- ---------- -------------------------------
C       2868 43202020202009e7ff86dab2800000
P          2 50202020202016f62c60904c200000
S          1 53202020202032fe93cf509b400000
X       2627 5820202020202b817684cb40800000

如果你把這些數字加起來，你會發現Oracle從表中提取了5498行樣本，所以當它估計任何給定值的行數時，它會檢查直方圖並乘以1030000/5498（分子是根據使用者表計算的行數。 行數減去使用者表的行數。 行數減去使用者表的行數。 ），因此，status='S'的估計值為187，status='P'的估計值為375，這兩個值都是相當合理的（尤其是與沒有直方圖的1030000/5相比）。  

但我們該怎麼處理“R”狀態呢？ –它沒有出現在樣本中，所以沒有出現在直方圖中。 在這種情況下，最佳化器只需將直方圖中最不受歡迎的值的基數減半，因此基數將計算為94。 同樣，在這種情況下，這也不算太糟，也不會改變關鍵的執行計劃，但是如果您在Oracle每天取樣的行中運氣不好，您可以理解，您的執行計劃可能會相當隨機地改變。 你能在這組資料中找出主要的威脅嗎？

如果Oracle在對資料進行取樣時沒有發現任何罕見的值，最後顯示一個直方圖，該直方圖顯示資料在C和X之間以大約50/50的比例分割，每行大約50萬行，會發生什麼情況？ 對status='R'的查詢將使用“最不常訪問值的一半”–估計約為250000； 這正是發生在我的客戶身上的情況。 stats（正在進行預設的10g過時統計資料的夜間收集）會收集此表上的統計資料，並忽略所有罕見的值，在接下來的24小時內（或者直到下一個stats集合），最佳化器將決定在一個非常大的表上使用一個tabscan，而不是使用一個非常合適的索引。

未能在直方圖中捕獲關鍵資訊的想法將我們引向直方圖的最後一個關鍵問題——如果在收集統計資料時關鍵資訊永遠不存在，會怎麼樣。 想象一下，我的訂單處理系統中的罕見值只出現在早上6:00到下午6:00到晚上10:00之間。 它們都已經從系統中處理出來了。 當預設的stats集合在深夜執行時，表中的唯一值是“C”和“X”，但是當查詢在白天執行時，我們感興趣的唯一值正是收集統計資料時不存在的值。 即使是100%的樣本，如果你在錯誤的時間收集資料，你的系統中也可能有部分資料會誤導你。 您需要對系統有足夠的瞭解，以便知道應用程式程式碼本身應該對統計資料的質量負責。 這可能意味著您編寫程式碼以在一天中的特定時間收集統計資料； 這可能意味著您編寫程式碼以直接操作儲存的統計資料（我們將在檢視高度平衡直方圖時檢視該策略）。

結  論

當資料值的分佈高度傾斜時，如果要確保最佳化器不會因此產生非常糟糕的執行計劃，您需要對此做些什麼。如果您可以控制應用程式程式碼特性（如虛擬列或基於函式的索引），則可能有助於處理特殊值；但如果無法更改程式碼，則可能需要依賴直方圖。不過，即使有直方圖，繫結變數也很容易導致問題——即使有11g和12c中的新特性，用於自適應遊標和自適應執行計劃。

對頻率直方圖（更簡單的型別）的簡要檢查向我們展示了它們對於具有少量不同值的列的用處，特別是如果您的SQL使用文字。柱狀圖，即使是簡單的頻率柱狀圖，對於Oracle來說，建立柱狀圖的成本也很高，除非它對一小部分資料進行取樣，然後，如果真正感興趣的資料是暫時的，並且只佔總數的一小部分，柱狀圖可能會引入不穩定性。事實上，即使您在錯誤的時間使用了100%的樣本，由於最佳化器對缺失值的處理，產生的直方圖仍然可能導致問題。 

| 譯者簡介

湯健·沃趣科技資料庫技術專家

沃趣科技資料庫工程師，多年Oracle資料庫從業經驗，深入理解Oracle資料庫結構體系，現主要參與公司一體機產品安裝、測試、最佳化，並負責電信行業資料庫以及系統運維。