<form id="tznrh"><form id="tznrh"><th id="tznrh"></th></form></form>

            在這個關于直方圖的簡短系列中,我們將討論為什么需要直方圖以及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)-有關轉換的實際執行方式的詳細信息,請參見:http://jonathanlewis.wordpress.com/2010/10/05/frequency-histogram-4/請注意,直方圖有效地存儲為累積頻率,我使用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數據庫結構體系,現主要參與公司一體機產品安裝、測試、優化,并負責電信行業數據庫以及系統運維。




            沃趣科技,讓客戶用上更好的數據庫技術!
            三分快3