SQL執(zhí)行計劃與直方圖關系研究①

張開基

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

SQL執(zhí)行計劃與直方圖關系研究①

張開基

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

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

sql調(diào)優(yōu); 執(zhí)行計劃; 統(tǒng)計信息; 直方圖

1 引言

對oracle數(shù)據(jù)庫的性能調(diào)優(yōu)是數(shù)據(jù)庫管理員日常工作的重要內(nèi)容.調(diào)優(yōu)方法有多種,包括對數(shù)據(jù)庫內(nèi)存的調(diào)整,對數(shù)據(jù)庫數(shù)據(jù)存儲的優(yōu)化等等.其中,sql調(diào)優(yōu)是數(shù)據(jù)庫管理員做的最多的一項工作,也是效果比較明顯的一種調(diào)優(yōu)方法[1].

應用開發(fā)人員在開發(fā)過程中,往往只關注執(zhí)行結(jié)果是否正確,而忽略了不同的實現(xiàn)方法之間可能存在的性能差異.因此,基于oracle應用系統(tǒng)的很多性能問題,是由應用系統(tǒng)的sql語句性能較差引起的,所以,對sql語句的調(diào)優(yōu),往往是數(shù)據(jù)庫管理員性能調(diào)優(yōu)的重要手段.

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

2 SQL 執(zhí)行計劃與直方圖簡介

2.1 執(zhí)行計劃描述

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

2.2 執(zhí)行計劃的獲取

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

2.3 直方圖描述

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

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

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

3 SQL 執(zhí)行計劃與直方圖關系實驗

3.1 實驗意義

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

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

因此,盡可能準確的收集oracle對象的統(tǒng)計信息,搞清在不同的數(shù)據(jù)庫環(huán)境下直方圖的收集與否,對于提高sql語句執(zhí)行的性能,至關重要.

3.2 實驗過程

實驗分以下幾種情況:

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

實驗環(huán)境:oracle 版本:10.2.0.4,表空間本地管理,段自動管理.創(chuàng)建實驗表tab_1,插入實驗數(shù)據(jù).

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

表1 參數(shù)值組合

圖1 cursor_sharing 值

查詢表tab_1的數(shù)據(jù)分布情況:

圖2 表 tab_1 數(shù)據(jù)分布情況

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

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

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

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

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

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

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

圖4 組合一執(zhí)行計劃 2

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

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

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

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

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

圖5 組合二執(zhí)行計劃 1

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

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

圖6 組合二執(zhí)行計劃 2

圖7 修改 cursor_shaing 值

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

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

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

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

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

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

圖8 組合三執(zhí)行計劃 1

圖9 組合三執(zhí)行計劃 2

圖10 組合四執(zhí)行計劃 1

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

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

在cursor_sharing值為force的情況下,和cursor_sharing值為exact的情況下一樣.sql根據(jù)統(tǒng)計信息的直方圖收集情況,來選擇不同的執(zhí)行計劃.

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

圖11 組合四執(zhí)行計劃 2

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

圖12 修改 cursor_sharing 值

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

圖13 組合五執(zhí)行計劃 1

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

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

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

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

cursor_sharing 為 similar,實驗字段上有直方圖,優(yōu)化器計算出了最優(yōu)的執(zhí)行計劃,查詢b等于5和等于3的數(shù)據(jù),走了不同的執(zhí)行計劃.

圖14 組合五執(zhí)行計劃 2

圖15 組合六執(zhí)行計劃 1

圖16 組合六執(zhí)行計劃 2

4 結(jié)語

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

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

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

3戴小平.Oracle9i數(shù)據(jù)庫性能調(diào)整與優(yōu)化.安徽工業(yè)大學學報,2006,23(3):315–319.

4高攀,施蔚然.基于Oracle數(shù)據(jù)庫的 SQL語句優(yōu)化.電腦編程技巧與維護,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執(zhí)行計劃與直方圖關系研究.計算機系統(tǒng)應用,2017,26(10):246–250.http://www.c-s-a.org.cn/1003-3254/6008.html

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