SQL執行計劃與直方圖關系研究①

張開基

(中石化石油工程設計有限公司,東營 257000)

SQL執行計劃與直方圖關系研究①

張開基

(中石化石油工程設計有限公司,東營 257000)

sql語句調優是數據庫性能調優的重要方面.要達到同樣的執行結果,sql語句有多種寫法,不同的寫法其性能差別很大.即使同一個sql語句,oracle也有多種途徑去執行,即有多個執行計劃.oracle比較這多個執行計劃的性能優劣,耗費資源多少,來選擇最優的執行計劃.oracle在評估各個執行計劃的性能時,需要借助sql語句執行的環境,即統計信息,來計算出每個執行計劃耗費資源的多少.因此,盡可能收集準確的統計信息,對于oracle能否選擇最優的執行計劃,至關重要.其中,直方圖的收集與否起著很重要的作用.本文通過實驗來驗證直方圖對sql執行計劃的影響,從而明確何種情況下需要收集直方圖.

sql調優; 執行計劃; 統計信息; 直方圖

1 引言

對oracle數據庫的性能調優是數據庫管理員日常工作的重要內容.調優方法有多種,包括對數據庫內存的調整,對數據庫數據存儲的優化等等.其中,sql調優是數據庫管理員做的最多的一項工作,也是效果比較明顯的一種調優方法[1].

應用開發人員在開發過程中,往往只關注執行結果是否正確,而忽略了不同的實現方法之間可能存在的性能差異.因此,基于oracle應用系統的很多性能問題,是由應用系統的sql語句性能較差引起的,所以,對sql語句的調優,往往是數據庫管理員性能調優的重要手段.

在sql語句的調優過程中,通過sql執行計劃來了解sql語句的性能如何,是必須掌握的內容.而oracle對sql執行計劃的選擇,受多種因素的影響,比如有無索引,有無統計信息,當前的優化器模式是哪種等等[2].其中,cursor_sharing 參數值的選擇,直方圖的有無,直接影響了oracle對sql執行計劃的確定.搞清直方圖對sql執行計劃的影響,對于sql語句的調優,至關重要.

2 SQL 執行計劃與直方圖簡介

2.1 執行計劃描述

為了執行一條sql語句,oracle需要執行某些步驟的操作,每一步驟可能是從數據庫中物理檢索數據行,或者用某種方法準備數據行,供發出語句的用戶使用[3].Oracle用來執行語句的這些步驟的組合即為執行計劃.執行計劃是sql優化中最為復雜也是最為關鍵的部分,只有知道了oracle在內部到底是如何執行該sql語句的,才能知道優化器選擇的執行計劃是否是最優的.

2.2 執行計劃的獲取

獲取執行計劃的方法有很多,常用的有兩種,一種是利用autotrace命令,前提是用戶擁有plustrace角色,這樣用戶就可以利用set autotrace命令來執行sql語句查看執行計劃.這種方式在oracle9i及以下版本中是理論上的執行計劃,不一定是oracle實際選擇的,而在oracle10g之后,這種方式獲取的執行計劃已比較準確,本文采取的是這種方式; 一種是利用sql_trace跟蹤用戶會話獲取用戶的跟蹤文件,跟蹤文件中詳細列出了用戶執行的sql語句和sql語句的執行計劃,這樣獲取的執行計劃是oracle實際選擇的執行計劃.另外,還可以查詢動態性能視圖,從內存中直接獲取語句的執行計劃.

2.3 直方圖描述

sql執行計劃的確定,受數據庫對象統計信息的影響.統計信息主要是描述數據庫中表、索引的大小、規模、數據分布狀況等的一類信息.比如,表的行數、塊數、平均每行的大小、索引的leaf blocks、索引字段的行數、不同值的大小等,都屬于統計信息.CBO正是根據這些統計信息數據,計算出不同訪問路徑下,不同join方式下,各種執行計劃的成本,最后選擇出成本最小的執行計劃[4].

在oracle中直方圖是一種對數據分布質量情況進行描述的工具.它會按照某一列不同值出現數量的多少,以及出現的頻率高低來繪制數據的分布情況,以便能夠指導優化器根據數據的分布做出正確的選擇.在某些情況下,表列中的數值分布將影響優化器使用索引還是執行全表掃描.當where子句的值具有不成比例數量的數值時,將出現這種情況,使得全表掃描比索引訪問的成本更低.這種情況下如果where子句的過濾謂詞列上有一個合理正確的直方圖,將會對優化器做出正確的選擇發揮巨大的作用,使得SQL語句執行成本最低從而提升性能.

通俗地說,oracle中的直方圖就是描述表中列值的數據分布情況.在表列數據的唯一值分布不均勻的情況下,收集直方圖信息可以使得oracle根據數據分布情況選擇更準確的執行計劃.若是數據分布均勻,直方圖的收集沒有意義.

3 SQL 執行計劃與直方圖關系實驗

3.1 實驗意義

對于同一個查詢,可能有幾個執行計劃都符合要求,都能得到符合條件的數據.例如,參與連接的表可以有多種不同的連接方法,這取決于連接條件和優化器采用的連接方法.為了在多個執行計劃中選擇最優的執行計劃,優化器必須使用一些實際的指標來衡量每個執行計劃使用的資源(I/O次數、CPU等),這些資源也就是我們所說的代價(cost).如果一個執行計劃使用的資源多,我們就說使用執行計劃的代價大.以執行計劃的代價大小作為衡量標準,優化器選擇代價最小的執行計劃作為真正執行該查詢的執行計劃,并拋棄其它的執行計劃.

由于一系列因素都會影響語句的執行,優化器綜合權衡各個因素,在眾多執行計劃中選擇最佳的執行計劃.但是,很多情況下,優化器不能得到較真實的執行環境,就有可能選擇次優的執行計劃,這樣,oracle 性能就會受到影響.尤其在數據量較大的環境下,不能選擇最優的執行計劃,會使得oracle極其耗費系統資源,影響系統的響應時間,繼而影響用戶體驗.

因此,盡可能準確的收集oracle對象的統計信息,搞清在不同的數據庫環境下直方圖的收集與否,對于提高sql語句執行的性能,至關重要.

3.2 實驗過程

實驗分以下幾種情況:

實驗以上六種組合下,sql語句執行計劃受直方圖的影響,六種組合保證不會相互影響.

實驗環境:oracle 版本:10.2.0.4,表空間本地管理,段自動管理.創建實驗表tab_1,插入實驗數據.

組合一:(cursor_sharing 為 exact,實驗字段上無直方圖).確認目前數據庫cursor_sharing值為exact,如圖1.

表1 參數值組合

圖1 cursor_sharing 值

查詢表tab_1的數據分布情況:

圖2 表 tab_1 數據分布情況

查詢表tab_1,發現表tab_1數據分布不均勻,列B唯一值個數是10個,其中,值為5的記錄共有9991行,其它只有一行.我們已在列 B 上創建索引.實驗在這種情況下,有無直方圖對執行計劃的影響.

利用sql語句分析表,收集表的統計信息,不收集直方圖,然后利用autotrace命令查看統計信息,執行結果及執行計劃如圖3.

SQL> select * from tab_1 where b='5';

從結果可以看到,查詢b值等于5的記錄,在無直方圖的情況下,sql執行走了索引,因為b值等于5的記錄共有9991行,這種情況下走全表掃描比走索引效率更高,在無直方圖的情況下,sql走了索引,用了性能較差的執行計劃.

在實驗b=3的情況下,sql如何選擇執行計劃:

這種情況下,sql照例走了索引.

總結:在數據分布不均勻的情況下,若是不收集直方圖信息,oracle無法獲得數據的分布情況,因此不能得到最佳的執行計劃.

圖4 組合一執行計劃 2

組合二:(cursor_sharing 為 exact,實驗字段上有直方圖).

利用sql語句分析表,收集表的統計信息,并收集直方圖,然后利用autotrace命令查看統計信息,執行結果及執行計劃如圖5.

SQL> select * from tab_1 where b='5';

在實驗b=3的情況下,sql如何選擇執行計劃:

總結:這種情況下,查詢 b 等于 5 和 b 等于 3,oracle選擇了不同的執行計劃.B等于5走全表掃描,b等于3走了索引.這是符合理論的.因為oracle收集了直方圖信息,獲取了表數據的具體分布情況,因此能夠根據執行計劃的代價大小,比較準確的獲取最佳的執行計劃.

圖5 組合二執行計劃 1

以上兩種情況下,cursor_sharing 為 exact,在字段數據分布不均勻的情況下,有直方圖可得到預期的執行計劃; 無直方圖,oracle 無法判斷數據分布情況,無論檢索何值都走了索引.

組合三:(cursor_sharing 為 force,實驗字段上無直方圖).首先修改cursor_sharing值為force:

圖6 組合二執行計劃 2

圖7 修改 cursor_shaing 值

cursor_sharing取值 force,oracle會強制 sql綁定變量,無論實驗字段上有無直方圖.

收集統計信息,不收集直方圖,然后利用autotrace命令獲得列b等于5和等于3兩種值下的執行計劃如圖8、9.

SQL> select * from tab where b='5';

這種情況,不論列數據值如何分布,執行計劃無法獲得數據值的分布情況,sql也都走了索引.

組合四:(cursor_sharing 為 force,實驗字段上有直方圖).

收集統計信息,并收集直方圖,然后利用autotrace命令獲得列b等于5和等于3兩種值下的執行計劃如圖10、11.

圖8 組合三執行計劃 1

圖9 組合三執行計劃 2

圖10 組合四執行計劃 1

SQL> select * from tab where b='5';

這種方式下,無論先執行哪條語句,都能得到預期的執行計劃.

在cursor_sharing值為force的情況下,和cursor_sharing值為exact的情況下一樣.sql根據統計信息的直方圖收集情況,來選擇不同的執行計劃.

組合五:(cursor_sharing 為 similar,實驗字段上無直方圖).首先修改cursor_sharing值為similar:

圖11 組合四執行計劃 2

理論上,cursor_sharing 的取值,當表的字段被分析過存在直方圖的時候,similar的表現和exact一樣; 當表的字段沒被分析,不存在直方圖的時候,similar的表現和force一樣.這樣避免了一味地如force一樣轉換成變量形式.因為有直方圖的情況下轉換成變量之后容易產生錯誤的執行計劃,沒有利用上統計信息,因此similar綜合了兩者的優點.實驗如圖12所示.

圖12 修改 cursor_sharing 值

收集統計信息,不收集直方圖,然后利用autotrace命令獲得列b等于5和等于3兩種值下的執行計劃如圖13、14.

圖13 組合五執行計劃 1

SQL> select * from tab where b='5';

兩種情況都走了索引,與理論相符.

組合六:(cursor_sharing 為 similar,實驗字段上有直方圖).收集統計信息,并收集直方圖,然后利用autotrace命令獲得列b等于5和等于3兩種值下的執行計劃如圖15、16.

SQL> select * from tab where b='5';

cursor_sharing 為 similar,實驗字段上有直方圖,優化器計算出了最優的執行計劃,查詢b等于5和等于3的數據,走了不同的執行計劃.

圖14 組合五執行計劃 2

圖15 組合六執行計劃 1

圖16 組合六執行計劃 2

4 結語

通過以上實驗可以得出結論:cursor_sharing無論取何值,oracle根據直方圖的有無獲得的執行計劃與理論一致:有直方圖,可以根據表列值的數據分布情況決定走全表掃描還是走索引; 無直方圖,sql選擇索引.所以,在日常維護中,數據庫管理員應根據表數據的分布情況,來決定是否收集直方圖.某一列數據分布不均勻,應針對這一列收集直方圖,使得數據庫的執行計劃優化器可以根據列的數據分布情況來選擇效率最高的執行計劃.另外,收集直方圖有系統開銷,對于數據分布比較均勻的表,為節省系統開銷,可以不收集直方圖.

1曾實.ORACLE 數據庫優化技術研究.科技信息,2011,(27):80,52.

2韓云波,宋莉.Oracle 性能調整技術研究.電腦知識與技術,2010,6(7):1554–1556.

3戴小平.Oracle9i數據庫性能調整與優化.安徽工業大學學報,2006,23(3):315–319.

4高攀,施蔚然.基于Oracle數據庫的 SQL語句優化.電腦編程技巧與維護,2010,(22):38–39.[doi:10.3969/j.issn.1006-4052.2010.22.015]

Research on Relationship between SQL Execution Plan and Histogram

ZHANG Kai-Ji

(Sinopec Petroleum Engineering Corporation,Dongying 257000,China)

Sql statement tuning is an important aspect of database performance tuning.To achieve the same effects,sql statement has a variety of wording,with different performance for the different wording.Even with only one sql statement,the oracle also has a variety of ways to implement.That is,there are multiple execution plans.The oracle compares the performance of these multiple execution plans,the cost of resources,to select the optimal execution plan.In assessing the performance of each implementation plan,the oracle needs the implementation of sql statement with the environment,that is statistical information to calculate the cost of the number of resources for each implementation plan.Therefore,it is critically important for the oracle to choose the best implementation plan to collect as much as possible accurate statistical information.Among them,the collection of the histogram plays a very important role.The experiment verifies the impact of the histogram for the sql implementation plan,which clears the circumstances under which it needs to collect histograms.

sql tuning; implementation plan; statistics; histogram

張開基.SQL執行計劃與直方圖關系研究.計算機系統應用,2017,26(10):246–250.http://www.c-s-a.org.cn/1003-3254/6008.html

2017-01-22; 采用時間:2017-02-23