Oracle數(shù)據(jù)庫里的統(tǒng)計(jì)信息是這樣的一組數(shù)據(jù)：它存儲在數(shù)據(jù)字典里，且從多個(gè)維度描述了Oracle數(shù)據(jù)庫里對象的詳細(xì)信息。CBO會利用這些統(tǒng)計(jì)信息來計(jì)算目標(biāo)SQL各種可能的、不同的執(zhí)行路徑的成本，并從中選擇一條成本值最小的執(zhí)行路徑來作為目標(biāo)SQL的執(zhí)行計(jì)劃。

網(wǎng)站建設(shè)哪家好，找創(chuàng)新互聯(lián)！專注于網(wǎng)頁設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、微信平臺小程序開發(fā)、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了梨林免費(fèi)建站歡迎大家使用！

Oracle數(shù)據(jù)庫里的統(tǒng)計(jì)信息可以分為如下6種類型：

• 表的統(tǒng)計(jì)信息

• 索引的統(tǒng)計(jì)信息

• 列的統(tǒng)計(jì)信息

• 系統(tǒng)統(tǒng)計(jì)信息

• 數(shù)據(jù)字典統(tǒng)計(jì)信息

• 內(nèi)部對象統(tǒng)計(jì)信息

表的統(tǒng)計(jì)信息用于描述Oracle數(shù)據(jù)庫里表的詳細(xì)信息，它包含了一些典型的維度，如記錄數(shù)、表塊(表里的數(shù)據(jù)塊)數(shù)量、平均行長度等。

索引的統(tǒng)計(jì)信息于描述Oracle數(shù)據(jù)庫里索引的詳細(xì)信息，它包含了一些典型的維度，如索引的層級、葉子塊的數(shù)量、聚簇因子等。

列的統(tǒng)計(jì)信息于描述Oracle數(shù)據(jù)庫里列的詳細(xì)信息，它包含了一些典型的維度，如列的distinct值的數(shù)量、列的NULL值的數(shù)量、列的最小值、列的最大值以及直方圖等。

系統(tǒng)統(tǒng)計(jì)信息于描述Oracle數(shù)據(jù)庫所在的數(shù)據(jù)庫服務(wù)器的系統(tǒng)處理能力，它包含了CPU和I/O這兩個(gè)維度，借助于系統(tǒng)統(tǒng)計(jì)信息，Oracle可以更清楚地知道目標(biāo)數(shù)據(jù)庫服務(wù)器的實(shí)際處理能力。

數(shù)據(jù)字典統(tǒng)計(jì)信息用于熱核Oracle數(shù)據(jù)庫里數(shù)據(jù)字典基表(如TAB$、IND$等)、數(shù)據(jù)字典基表上的索引，以及這些數(shù)據(jù)字典的列的詳細(xì)信息，描述上述數(shù)據(jù)字典基表的統(tǒng)計(jì)信息與描述普通表、索引、列的統(tǒng)計(jì)信息沒有本質(zhì)區(qū)別。

內(nèi)部對象統(tǒng)計(jì)信息用于描述Oracle數(shù)據(jù)庫里的一些內(nèi)部表(如X$系列表)的詳細(xì)信息，它的維度和普通表的統(tǒng)計(jì)信息的維度類似，只不過其表塊的數(shù)量為0，因?yàn)閄$系統(tǒng)表實(shí)際上只是Oracle自定義的內(nèi)存結(jié)構(gòu)，并不占用實(shí)際的物理存儲空間。

1、收集統(tǒng)計(jì)信息

在Oracle數(shù)據(jù)庫里，通常有兩種方法可以用來收集統(tǒng)計(jì)信息：一種是使用ANALYZE命令；另一種是使用DBMS_STATS包。表、索引、列的統(tǒng)計(jì)信息和數(shù)據(jù)字典統(tǒng)計(jì)信息用ANALYZE命令或者DBMS_STATS包收集均可，但系統(tǒng)統(tǒng)計(jì)信息和系統(tǒng)內(nèi)部對象統(tǒng)計(jì)信息只能使用DBMS_STATS包來收集。

對系統(tǒng)內(nèi)部表若使用ANALYZE命令來收集統(tǒng)計(jì)信息，會報(bào)錯(cuò)ORA-02030

1.1用ANALYZE命令收集統(tǒng)計(jì)信息

從Oracle7開始，ANALYZE命令就可以用來收集表、索引、列的統(tǒng)計(jì)信息，以及系統(tǒng)統(tǒng)計(jì)信息。

典型用法如下：

zx@ORCL>create table t2 as select * from dba_objects;

Table created.

zx@ORCL>create index idx_t2 on t2(object_id);

Index created.

zx@ORCL>analyze index idx_t2 delete statistics;

Index analyzed.

從Oracle 10g開始，創(chuàng)建索引后Oracle會怎么收集目標(biāo)索引的統(tǒng)計(jì)信息，出現(xiàn)演示的目的，這里刪除索引IDX_T2的統(tǒng)計(jì)信息：

執(zhí)行sosi腳本，從輸出內(nèi)容可以看到表T2、表T2的列和索引IDX_T2均沒有相關(guān)的統(tǒng)計(jì)信息

zx@ORCL>select count(*) from t2;

  COUNT(*)
----------
     86852

只對表T2收集統(tǒng)計(jì)信息，并且以估算模式，采樣的比例為15%：

zx@ORCL>analyze table t2 estimate statistics sample 15 percent for table;

Table analyzed.

再次執(zhí)行sosi腳本，可以看出現(xiàn)在只用表T2有統(tǒng)計(jì)信息，表T2的列和索引IDX_T2均沒有相關(guān)的統(tǒng)計(jì)信息。而且因?yàn)椴捎玫氖枪浪隳Ｊ剿怨浪憬Y(jié)果和實(shí)際結(jié)果并不一定會完全匹配，比如表T2的實(shí)際數(shù)量與估算出的數(shù)量不一致。

只對表T2收集統(tǒng)計(jì)信息，并且以計(jì)算模式：

zx@ORCL>analyze table t2 compute statistics for table;

Table analyzed.

再次執(zhí)行sosi腳本，可以看出現(xiàn)在只用表T2有統(tǒng)計(jì)信息，表T2的列和索引IDX_T2均沒有相關(guān)的統(tǒng)計(jì)信息。而且因?yàn)椴捎玫氖怯?jì)算模式，計(jì)算模式會掃描目標(biāo)對象的所有數(shù)據(jù)，所以統(tǒng)計(jì)結(jié)果和實(shí)際結(jié)果是匹配的。

對表T2收集完統(tǒng)計(jì)信息后，現(xiàn)在對表T2的列OBJECT_NAME和OBJECT_ID以計(jì)算模式收集統(tǒng)計(jì)信息：

zx@ORCL>analyze table t2 compute statistics for columns object_name,object_id;

Table analyzed.

再次執(zhí)行sosi腳本，可以看出，現(xiàn)在列OBJECT_NAME和OBJECT_ID確實(shí)已經(jīng)有統(tǒng)計(jì)信息了

注：在崔華老師的《基于Oracle的SQL優(yōu)化》一書中提到T2原有的統(tǒng)計(jì)信息已經(jīng)被抹掉了，也就是說對同一個(gè)對象而言，新執(zhí)行的ANALYZE命令會抹掉之前ANALYZE的結(jié)果。但是在我實(shí)際的執(zhí)行結(jié)果是表T2原有的統(tǒng)計(jì)信息沒有被抹掉。我用到的環(huán)境是10.2.0.4和11.2.0.4，暫時(shí)沒有11.2.0.1的環(huán)境。

可以使用如下的命令同時(shí)以計(jì)算模式對表T2和列OBJECT_NAME、OBJECT_ID收集統(tǒng)計(jì)信息：

zx@ORCL>analyze table t2 compute statistics for table for columns object_name,object_id;

Table analyzed.

再次執(zhí)行sosi腳本，可以看到表T2和列OBJECT_NAME、OBJECT_ID上都有統(tǒng)計(jì)信息了。

使用如下命令可以以計(jì)算模式收集索引IDX_T2的統(tǒng)計(jì)信息

zx@ORCL>analyze index idx_t2 compute statistics;

Index analyzed.

再次執(zhí)行sosi腳本，從輸出可以看到，現(xiàn)在索引IDX_T2已經(jīng)有了統(tǒng)計(jì)信息，并且之前收集的表T2和列OBJECT_NAME、OBJECT_ID上的統(tǒng)計(jì)信息并沒有被抹掉，這是因?yàn)槲覀儎偛艌?zhí)行的ANALYZE命令和之前執(zhí)行的ANALYZE命令針對的不是同一個(gè)對象。

使用如下命令可以刪除表T2、表T2的所有列及表T2的所有索引的統(tǒng)計(jì)信息：

zx@ORCL>analyze table t2 delete statistics;

Table analyzed.

再次執(zhí)行sosi腳本，從輸出可以看到，剛才收集的表T2、表T2的列OBJECT_NAME、OBJECT_ID以及索引IDX_T2的統(tǒng)計(jì)信息已經(jīng)全部被刪除了。

如果想一次性以計(jì)算模式收集表T2、表T2的所有列和表T2上的所有索引的統(tǒng)計(jì)信息，執(zhí)行如下的語句就可以了：

zx@ORCL>analyze table t2 compute statistics;

Table analyzed.

再次執(zhí)行sosi腳本，從輸出可以看到，現(xiàn)在表T2、表T2的所有列和索引IDX_T2的統(tǒng)計(jì)信息都有了。

1.2用DBMS_STATS包收集統(tǒng)計(jì)信息

從Oracle 8.1.5開始，DBMS_STATS包被廣泛用于統(tǒng)計(jì)信息的收集，用DMBS_STATS包收集統(tǒng)計(jì)信息也是Oracle官方推薦的方式。在收集CBO所需要的統(tǒng)計(jì)信息方面，可以簡單的將DBMS_STATS包理解成是ANALYZE命令的增加版。

DBMS_STATS包里最常用的就是如下4個(gè)存儲過程：

• GATHER_TABLE_STATS：用于收集目標(biāo)表、目標(biāo)表的列和目標(biāo)表上的索引的統(tǒng)計(jì)信息。

• GATHER_INDEX_STATS：用于收集指定索引的統(tǒng)計(jì)信息。

• GATHER_SCHEMA_STATS：用于收集指定schema下所有對象的統(tǒng)計(jì)信息。

• GATHER_DATABASE_STATS：用于收集全庫所有對象的統(tǒng)計(jì)信息。

現(xiàn)在介紹DBMS_STATS包在收集統(tǒng)計(jì)信息時(shí)的常見用法，還是針對上面的測試表T2，這里使用DBMS_STATS包實(shí)現(xiàn)了和ANALYZE命令一模一樣的效果。

先刪除表T2上的所有統(tǒng)計(jì)信息

analyze table t2 delete statistics;

只對表T2收集統(tǒng)計(jì)信息，并且以估算模式，采用的比例同樣為15%：

zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T2',estimate_percent=>15,method_opt=>'FOR TABLE',cascade=>false);

PL/SQL procedure successfully completed.

執(zhí)行sosi腳本，從輸出內(nèi)容可以看出，現(xiàn)在只有表T2有統(tǒng)計(jì)信息，表T2的列和索引IDX_T2均沒有相關(guān)的統(tǒng)計(jì)信息。而且因?yàn)椴捎玫墓浪隳Ｊ剑怨浪憬Y(jié)果和實(shí)際結(jié)果并不一定會完全匹配。

需要注意的是，這里Oracle數(shù)據(jù)庫的版本是11.2.0.4，我們在調(diào)用DMBS_STATS.GATHER_TABLE_STATS時(shí)指定參數(shù)METHOD_OPT的值為'FOR TABLE',這表示只收集表T2的統(tǒng)計(jì)信息。這種收集表統(tǒng)計(jì)信息的方法并不適用于Oracle數(shù)據(jù)庫所有的版本。例如這種方法就不適用于Oracle10.2.0.4和Oracle10.2.0.5，在這兩個(gè)版本里，即使指定了'FOR TABLE'，Oracle除了收集表統(tǒng)計(jì)信息之外還會對所有的列收集統(tǒng)計(jì)信息。

如果公對表T2收集統(tǒng)計(jì)信息，并且是以計(jì)算模式收集，用DBMS_STATS包實(shí)現(xiàn)的方法就是將估算模式的采樣比例(即參數(shù)ESTIMATE_PERCENT)設(shè)置為100%或NULL；

exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T2',estimate_percent=>100,method_opt=>'FOR TABLE',cascade=>false);

exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T2',estimate_percent=>NULL,method_opt=>'FOR TABLE',cascade=>false);

zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T2',estimate_percent=>100,method_opt=>'FOR TABLE',cascade=>false);

PL/SQL procedure successfully completed.

執(zhí)行sosi腳本，從輸出內(nèi)容可以看出，現(xiàn)在只有表T2的統(tǒng)計(jì)信息，表T2的列和索引IDX_T2均沒有相關(guān)的統(tǒng)計(jì)信息。而且因?yàn)椴捎玫氖怯?jì)算模式，計(jì)算模式會掃描目標(biāo)對象的所有數(shù)據(jù)，所以統(tǒng)計(jì)結(jié)果和實(shí)際結(jié)果是匹配的。

對表T2收集完統(tǒng)計(jì)信息后，現(xiàn)在我們來對表T2的列OBJECT_NAME、OBJECT_ID以計(jì)算模式收集統(tǒng)計(jì)信息(不收集直方圖)：

zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T2',estimate_percent=>100,method_opt=>'for columns size 1 object_name,object_id',cascade=>false);

PL/SQL procedure successfully completed.

執(zhí)行sosi腳本，從輸出內(nèi)容可以看出，現(xiàn)在表T2的列OBJECT_NAME、OBJECT_ID上都有統(tǒng)計(jì)信息了，并且Oracle還會同時(shí)收集表T2上的統(tǒng)計(jì)信息(注意，這和ANALYZE命令有所區(qū)別)。

使用如下命令可以以計(jì)算模式收集索引IDX_T2的統(tǒng)計(jì)信息

zx@ORCL>exec dbms_stats.gather_index_stats(ownname=>'ZX',indname=>'IDX_T2',estimate_percent=>100);

PL/SQL procedure successfully completed.

執(zhí)行sosi腳本，從輸出內(nèi)容可以看出，現(xiàn)在索引IDX_T2已經(jīng)有了統(tǒng)計(jì)信息。

使用如下命令可以刪除表T2、表T2的所有列及表T2的所有索引的統(tǒng)計(jì)信息：

zx@ORCL>exec dbms_stats.delete_table_stats(ownname=>'ZX',tabname=>'T2');

PL/SQL procedure successfully completed.

執(zhí)行sosi腳本，從輸出內(nèi)容可以看出，表T2、表T2的所有列及表T2的所有索引的統(tǒng)計(jì)信息已經(jīng)全部被刪除了。

如果想一次性以計(jì)算模式收集表T2、表T2的所有列及表T2的所有索引的統(tǒng)計(jì)信息，執(zhí)行如下語句就可以了

zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T2',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

1.3 ANALYZE和DBMS_STATS的區(qū)別

從上面的演示中可以看出ANALYZE命令和DBMS_STATS包都可以用來收集表、索引和列的統(tǒng)計(jì)信息，看起來它們在收集統(tǒng)計(jì)信息方面的效果是一模一樣的，為什么Oracle會推薦使用DBMS_STATS包來收集統(tǒng)計(jì)信息呢？

因?yàn)锳NALYZE命令和DMBS_STATS包相比，存在如下缺陷：

ANALYZE命令不能正確地收集分區(qū)表的統(tǒng)計(jì)信息，而DBMS_STATS包卻可以。ANALYZE命令只會收集最低層次對象的統(tǒng)計(jì)信息，然后推導(dǎo)和匯總出高一級的統(tǒng)計(jì)信息，比如對于有子分區(qū)的分區(qū)表而言，它只會先收集子分區(qū)統(tǒng)計(jì)信息，然后再匯總，推導(dǎo)出分區(qū)或表級的統(tǒng)計(jì)信息。有的統(tǒng)計(jì)信息是可以從當(dāng)前對象的下一級對象進(jìn)行匯總后得到的，比如表的總行數(shù)，可以由各分區(qū)的行數(shù)相加得到。但有的統(tǒng)計(jì)信息則不能從下一級對象得到，比如列上的distinct值數(shù)量NUM_DISTINCT以及DESNSITY等。

ANALYZE命令不能并行收集統(tǒng)計(jì)信息，而DBMS_STATS包卻可以。并行收集統(tǒng)計(jì)信息對數(shù)據(jù)量很大的表表而言，是非常有用的特性。對于數(shù)據(jù)量很大的表，如果不能并行收集統(tǒng)計(jì)信息，則意味著如果想精確地收集目標(biāo)對象的統(tǒng)計(jì)信息，那么耗費(fèi)的時(shí)間可能會非常長，這有可能是不能接受的。在Oracle數(shù)據(jù)庫里，DBMS_STATS包收集統(tǒng)計(jì)信息可以并行執(zhí)行，這在一定程度上緩解了對大表的統(tǒng)計(jì)信息收集過長所帶來的一系列問題。

DBMS_STATS包的并行收集是通過手工指定輸入?yún)?shù)DEGREE來實(shí)現(xiàn)的，比如對表T1進(jìn)行收集統(tǒng)計(jì)信息，同時(shí)指定并行度為4：

exec dbms_stats.gahter_table_stats(ownname=>'SCOTT',tabname=>'T1',cascade=>true,estimate_percent=>100,degree=>4);

當(dāng)然，DBMS_STATS包也不是完美的，它與ANALYZE命令相比，其缺陷在于DBMS_STATS包只能收集與CBO相關(guān)的統(tǒng)計(jì)信息，而與CBO無關(guān)的一些額外信息，比如行遷移/行鏈接的數(shù)量(CHAIN_CNT)、校驗(yàn)表和索引的結(jié)構(gòu)信息等，DBMS_STATS包就無能為力了。而ANALYZE命令可以用來分析和收集上述額外的信息，比如analyze table xxx list chained rows intoyyy可以用來分析和收集行遷移/行鏈接的數(shù)量，analyzeindex xxx validate structure可以用來分析索引的結(jié)構(gòu)。

2、查看統(tǒng)計(jì)信息

前面介紹了如何收集統(tǒng)計(jì)信息，那如何查看這些統(tǒng)計(jì)信息呢？Oracle數(shù)據(jù)庫的統(tǒng)計(jì)信息會存儲在數(shù)據(jù)字典里，我們只需要去查詢相關(guān)的數(shù)據(jù)字典就好了。如果有充裕的時(shí)間，現(xiàn)寫SQL去查詢數(shù)據(jù)字典里的統(tǒng)計(jì)信息也沒有什么，但當(dāng)我們真正碰到有性能問題的SQL時(shí)，通常會希望能在第一時(shí)間就收集到與目標(biāo)SQL相關(guān)的各種統(tǒng)計(jì)信息，以便于在第一時(shí)間定位問題所在，這時(shí)候?qū)慡QL去查詢數(shù)據(jù)字典就已經(jīng)來不及了，所以我們需要事先準(zhǔn)備好通用的查詢統(tǒng)計(jì)信息的腳本，出問題的時(shí)候只需要運(yùn)行一下腳本，就能在第一時(shí)間獲取目標(biāo)對象的所有統(tǒng)計(jì)信息了。

sosi腳本(Show Optimizer Statistics Information)就是這樣一種腳本，國內(nèi)的Oracle數(shù)據(jù)庫專家也一直在用這個(gè)腳本，它源于MOS上的文章：SCRIPT - Select to show OptimizerStatistics for CBO (文檔 ID 31412.1)，用法很簡單，只需要運(yùn)行一下sosi腳本，并指定要查看統(tǒng)計(jì)信息的表名就可以了。它支持分區(qū)表，顯示分為三部分，分別是表級別的統(tǒng)計(jì)信息，分區(qū)級別的統(tǒng)計(jì)信息和子分區(qū)級別的統(tǒng)計(jì)信息。前面做實(shí)驗(yàn)用到的也是這個(gè)腳本。

 附件是sosi腳本可以下載使用。

參考《基于Oracle的SQL優(yōu)化》

網(wǎng)站名稱：Oracle里收集與查看統(tǒng)計(jì)信息的方法
URL鏈接：http://chinadenli.net/article10/ihocgo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供服務(wù)器托管、軟件開發(fā)、網(wǎng)站設(shè)計(jì)、品牌網(wǎng)站建設(shè)、用戶體驗(yàn)、自適應(yīng)網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)