張開(kāi)基

(中石化石油工程設(shè)計(jì)有限公司,東營(yíng) 257000)

SQL執(zhí)行計(jì)劃與直方圖關(guān)系研究①

張開(kāi)基

(中石化石油工程設(shè)計(jì)有限公司,東營(yíng) 257000)

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

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

1 引言

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

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

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

2 SQL 執(zhí)行計(jì)劃與直方圖簡(jiǎn)介

2.1 執(zhí)行計(jì)劃描述

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

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

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

2.3 直方圖描述

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

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

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

3 SQL 執(zhí)行計(jì)劃與直方圖關(guān)系實(shí)驗(yàn)

3.1 實(shí)驗(yàn)意義

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

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

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

3.2 實(shí)驗(yàn)過(guò)程

實(shí)驗(yàn)分以下幾種情況:

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

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

組合一:(cursor_sharing 為 exact,實(shí)驗(yàn)字段上無(wú)直方圖).確認(rèn)目前數(shù)據(jù)庫(kù)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唯一值個(gè)數(shù)是10個(gè),其中,值為5的記錄共有9991行,其它只有一行.我們已在列 B 上創(chuàng)建索引.實(shí)驗(yàn)在這種情況下,有無(wú)直方圖對(duì)執(zhí)行計(jì)劃的影響.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

組合三:(cursor_sharing 為 force,實(shí)驗(yàn)字段上無(wú)直方圖).首先修改cursor_sharing值為force:

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

圖7 修改 cursor_shaing 值

cursor_sharing取值 force,oracle會(huì)強(qiáng)制 sql綁定變量,無(wú)論實(shí)驗(yàn)字段上有無(wú)直方圖.

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

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

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

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

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

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

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

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

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

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

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

組合五:(cursor_sharing 為 similar,實(shí)驗(yàn)字段上無(wú)直方圖).首先修改cursor_sharing值為similar:

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

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

圖12 修改 cursor_sharing 值

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

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

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

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

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

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

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

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

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

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

4 結(jié)語(yǔ)

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

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

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

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

4高攀,施蔚然.基于Oracle數(shù)據(jù)庫(kù)的 SQL語(yǔ)句優(yōu)化.電腦編程技巧與維護(hù),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

張開(kāi)基.SQL執(zhí)行計(jì)劃與直方圖關(guān)系研究.計(jì)算機(jī)系統(tǒng)應(yīng)用,2017,26(10):246–250.http://www.c-s-a.org.cn/1003-3254/6008.html

2017-01-22; 采用時(shí)間:2017-02-23