上篇中，我們介紹了dbms_stats的重要參數(shù)method_opt的使用和默認(rèn)參數(shù)。本篇中我們繼續(xù)來(lái)討論這個(gè)參數(shù)的作用。

4、method_opt的格式塊

從官方介紹上看，method_opt格式是一個(gè)字符串結(jié)構(gòu)，有如下結(jié)構(gòu)：

For all [indexed | hidden] columns size xxx

或者

For columns size xxx column

在含義中，字符串包括兩個(gè)層面：一個(gè)是要確定要收集數(shù)據(jù)表哪些列的統(tǒng)計(jì)量，另一個(gè)是要確定收集直方圖的時(shí)候設(shè)置多少個(gè)bucket。

目前實(shí)踐場(chǎng)景中，第一個(gè)層面一般都是選擇收集所有的統(tǒng)計(jì)量。也就是使用all columns選項(xiàng)。早期CBO和一些特殊場(chǎng)景下，可以考慮使用indexed和hidden取值，但是并不推薦。

All indexed columns表示只是將出現(xiàn)在數(shù)據(jù)表索引中的數(shù)據(jù)列收集統(tǒng)計(jì)量。非索引列是不會(huì)收集統(tǒng)計(jì)量。同時(shí)，只有索引列才會(huì)創(chuàng)建直方圖。

我們給實(shí)驗(yàn)數(shù)據(jù)表T添加索引對(duì)象。

SQL> create index idx_t_owner on t(owner);

Index created

SQL> create index idx_t_status on t(status);

Index created

SQL> create index idx_t_id on t(object_id);

Index created

--刪除原有的統(tǒng)計(jì)量

SQL> exec dbms_stats.delete_table_stats(user,'T');

PL/SQL procedure successfully completed

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for all indexed columns size 254');

PL/SQL procedure successfully completed

SQL> select column_name, num_buckets, histogram from dba_tab_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAMENUM_BUCKETS HISTOGRAM

------------------------------ ----------- ---------------

OWNER27 FREQUENCY

OBJECT_ID254 HEIGHT BALANCED

STATUS2 FREQUENCY

Indexed columns方法顯然是反映了Oracle在列統(tǒng)計(jì)量收集問題上的一種想法。但是，這種思考是欠考慮的。因?yàn)槲覀儾荒鼙ＷCwhere條件后面出現(xiàn)的所有列均是索引列，而且其他操作，如group by等也是有列因素在其中的。

Hidden columns選項(xiàng)的范圍更小，只有那些virtual columns才會(huì)被收集統(tǒng)計(jì)量。Hidden columns統(tǒng)計(jì)量可以幫助提高虛擬列的成本評(píng)估。

Size部分表示的是生成直方圖的時(shí)候，選擇的bucket個(gè)數(shù)。Size參數(shù)可以有如下控制值：

Auto：10g之后的默認(rèn)選項(xiàng)。根據(jù)列使用的情況和數(shù)據(jù)分布情況進(jìn)行直方圖的創(chuàng)建。根據(jù)我們?cè)谏掀械挠懻?#xff0c;auto情況下要依據(jù)col_usage$基礎(chǔ)表和數(shù)據(jù)的傾斜情況來(lái)判斷。

整數(shù)：直接指定bucket的個(gè)數(shù)。在11g版本中，bucket個(gè)數(shù)在1-254之間。如果選擇直接指定bucket個(gè)數(shù)的方法，我們可以找到的是一個(gè)最大bucket數(shù)量。具體真實(shí)的個(gè)數(shù)要根據(jù)收集過程中的實(shí)際情況而定。如上面的代碼片段，我們要求生成254 bucket的直方圖，但是只有離散度最好的object_id生成了高度均衡的254 bucket直方圖，其他都比較少。應(yīng)該說，實(shí)際收集過程中的bucket個(gè)數(shù)，與distinct值和直方圖類型相關(guān)。

Skewonly：根據(jù)數(shù)據(jù)列的分布情況來(lái)判斷直方圖的生成。

SQL> exec dbms_stats.delete_table_stats(user,'T');

PL/SQL procedure successfully completed

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for all columns sizeskewonly');

PL/SQL procedure successfully completed

SQL> select column_name, num_buckets, histogram from dba_tab_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAMENUM_BUCKETS HISTOGRAM

------------------------------ ----------- ---------------

OWNER29 FREQUENCY

OBJECT_NAME254 HEIGHT BALANCED

SUBOBJECT_NAME110 FREQUENCY

OBJECT_ID1 NONE

DATA_OBJECT_ID254 HEIGHT BALANCED

OBJECT_TYPE35 FREQUENCY

CREATED254 HEIGHT BALANCED

LAST_DDL_TIME254 HEIGHT BALANCED

TIMESTAMP254 HEIGHT BALANCED

STATUS2 FREQUENCY

TEMPORARY2 FREQUENCY

GENERATED2 FREQUENCY

SECONDARY2 FREQUENCY

NAMESPACE17 FREQUENCY

EDITION_NAME0 NONE

15 rows selected

從上面的結(jié)果看，如果使用skewonly選項(xiàng)，Oracle會(huì)去分析數(shù)據(jù)列的分布情況。如果數(shù)據(jù)呈現(xiàn)出偏移傾斜的情況，會(huì)去生成直方圖。

Repeat：Oracle的統(tǒng)計(jì)量是一個(gè)累積的過程。使用repeat選項(xiàng)去收集哪些當(dāng)前已經(jīng)有統(tǒng)計(jì)量的列。

5、特定列的統(tǒng)計(jì)量收集

For all columns是收集所有的數(shù)據(jù)列，從格式上，method_opt還支持一種特定列統(tǒng)計(jì)量的收集格式。具體格式為for columns size xxx [column name]。

SQL> exec dbms_stats.delete_table_stats(user,'T');

PL/SQL procedure successfully completed

SQL> select column_name, num_buckets, histogram from dba_tab_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAMENUM_BUCKETS HISTOGRAM

------------------------------ ----------- ---------------

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for columns size 10 owner');

PL/SQL procedure successfully completed

SQL> select column_name, num_buckets, histogram from dba_tab_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAMENUM_BUCKETS HISTOGRAM

------------------------------ ----------- ---------------

OWNER10 HEIGHT BALANCED

這樣的格式中，只給指定的列生成指定數(shù)量bucket的統(tǒng)計(jì)量。那么，如果需要收集多個(gè)列的統(tǒng)計(jì)量，可以按照兩種格式進(jìn)行指定。

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for columns size 10 owner for columns size 5 object_name');

PL/SQL procedure successfully completed

SQL> select column_name, num_buckets, histogram from dba_tab_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAMENUM_BUCKETS HISTOGRAM

------------------------------ ----------- ---------------

OWNER10 HEIGHT BALANCED

OBJECT_NAME5 HEIGHT BALANCED

SQL> exec dbms_stats.delete_table_stats(user,'T');

PL/SQL procedure successfully completed

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for columns size 10 owner object_name object_id');

PL/SQL procedure successfully completed

SQL> select column_name, num_buckets, histogram from dba_tab_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAMENUM_BUCKETS HISTOGRAM

------------------------------ ----------- ---------------

OWNER10 HEIGHT BALANCED

OBJECT_NAME10 HEIGHT BALANCED

OBJECT_ID10 HEIGHT BALANCED

6、拓展extended統(tǒng)計(jì)量收集

Oracle CBO優(yōu)化器的統(tǒng)計(jì)量統(tǒng)計(jì)維度默認(rèn)是單列。當(dāng)SQL語(yǔ)句中出現(xiàn)多個(gè)條件列的時(shí)候，其估算的行數(shù)是不準(zhǔn)確的。在目前的版本中，有兩個(gè)方法來(lái)提高估算精確度，一個(gè)是采用動(dòng)態(tài)采樣Dynamic Sampling進(jìn)行實(shí)時(shí)收集，另一個(gè)就是采用11g的拓展統(tǒng)計(jì)量Extended Statistic。

在之前的文章中，我們介紹過如何創(chuàng)建Extended統(tǒng)計(jì)量。我們使用method_opt，也可以進(jìn)行拓展統(tǒng)計(jì)量收集。

--Sys用戶下

SQL> exec dbms_stats.delete_table_stats(user,'T');

PL/SQL procedure successfully completed

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for columns size 10 (owner, object_type)');

begin dbms_stats.gather_table_stats(user,'T',method_opt => 'for columns size 10 (owner, object_type)'); end;

ORA-20000: Unable to create extension: not supported for SYS owned table

ORA-06512:在"SYS.DBMS_STATS", line 20337

ORA-06512:在"SYS.DBMS_STATS", line 20360

ORA-06512:在line 1

轉(zhuǎn)換到scott普通用戶下進(jìn)行試驗(yàn)。

SQL> conn scott/tiger@wilson;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

SQL> drop table t purge;

Table dropped

SQL> create table t as select * from dba_objects;

Table created

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for columns size 10 (owner, object_type)');

PL/SQL procedure successfully completed

SQL> select column_name, num_buckets, histogram from dba_tab_col_statistics where wner='SCOTT' and table_name='T';

COLUMN_NAMENUM_BUCKETS HISTOGRAM

------------------------------ ----------- ---------------

SYS_STUXJ8K0YTS_5QD1O0PEA514IY10 HEIGHT BALANCED

7、結(jié)論

在諸多Oracle dbms_stats參數(shù)中，method_opt靈活性很高。使用好這個(gè)參數(shù)，可以幫助我們更好的進(jìn)行精確化統(tǒng)計(jì)量定義，提高SQL執(zhí)行效率。

總結(jié)

以上是生活随笔為你收集整理的oracle method_opt,统计量收集Method_Opt参数使用（下）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。