How does the METHOD_OPT parameter work —— method_opt 參數(shù)應用

這篇文章將詳細介紹 method_opt 參數(shù)怎樣影響目標列上的統(tǒng)計信息以及為目標列收集什么類型的統(tǒng)計信息.

Method_opt 可能是存儲過程 dbms_stats.gather_*_stats 中最令人費解的參數(shù). 這個參數(shù)最常見的功能就是控制直方圖的收集方式, 但實際上它的功能遠不及此.  它的實際功能如下所示：

• 控制哪些列收集基本的統(tǒng)計信息
• 收集直方圖,
• 收集擴展的統(tǒng)計信息

Method_opt 參數(shù)用法分為兩個部分, 如下圖所示：

“For all [indexed | hidden] columns” 這一部分控制著哪些列將會收集列的基本統(tǒng)計信息(目標列上的最小值, 最大值, 列上不同值的數(shù)量, 空值的數(shù)量等等). 系統(tǒng)默認值為 FOR ALL COLUMNS, 它將收集表上所有列(包括隱藏列)的基本的統(tǒng)計信息. 此外, 它的其他可選值如下所示：

• FOR ALL INDEXED COLUMNS 指定只有含有索引的字段才能收集列的基本統(tǒng)計信息. 一般不推薦使用這個選項值, 因為在數(shù)據(jù)庫環(huán)境中的所有 sql 語句所使用的字段, 比如 select 后面的字段, where 后面字段, group by 中的字段, 并不只是會引用含有索引的字段.
• FOR ALL HIDDEN COLUMNS  指定表中所有不可見的字段才能收集列的基本統(tǒng)計信息, 也就是說不會去收集表上實際可見的列的統(tǒng)計信息. 同樣的一般也不推薦使用這個選項值.  這個選項值通常只用于這種情況, 在一個所有列的統(tǒng)計信息都是準確的表中新增了一個或幾個不可見或者說是虛擬的列, 只需要收集這個或者這幾個不可見列的統(tǒng)計信息, 而不再重復去其他列的統(tǒng)計信息, 那么就使用 for all hidden columns 這個選項.

如果一個字段(列)并不包括在這部分收集基本統(tǒng)計信息的列的列表中, 收集完成后只會計算這個列的平均長度.  而列的平均長度通常會用于計算行的平均長度.

"Size [size_clause]" 這一部分控制收集直方圖的方式, size 后面可以有以下選項：      

• AUTO  Oracle 自己決定根據(jù)列的統(tǒng)計信息(sys.col_usage$)以及列的數(shù)據(jù)傾斜程度(均勻分布程度)決定哪些列需要收集直方圖      
• Integer  指定收集直方圖的桶數(shù), 桶數(shù)最小為 1 最大為 254 (針對 11g 及以前的版本, 12c 后沒有這個限制).注意如果桶數(shù)為 1, 即 size 1 意味著不建立直方圖, 如果已經(jīng)有直方圖的列則會刪除該列的直方圖.
• REPEAT 只在已經(jīng)有直方圖的列上重新收集直方圖. repeat 會確保在全局級別上對已經(jīng)存在直方圖的列重新收集直方圖. 一般不推薦使用這個選項, 因為新的直方圖使用的桶數(shù)將不能超過舊的直方圖中的桶數(shù). 假設當前直方圖中桶數(shù)為 5, 當使用 size repeat 重新收集直方圖時, 新的直方圖使用的桶數(shù)將不能超過 5 , 這鐘方式可能不會取得好的效果.
• SKEWONLY 只在數(shù)據(jù)不均勻分布的列上收集直方圖.

如果 method_opt 的默認參數(shù) FOR ALL COLUMNS SIZE AUTO 在你的數(shù)據(jù)環(huán)境不適用, 可能你遇到的情況屬于下面兩種情況：

• 除了指定的列, 在其它列上創(chuàng)建直方圖
• 只在指定的列上創(chuàng)建直方圖

下面假設我們只想在 sales 表上的 cust_id 列上創(chuàng)建直方圖.  記住, method_opt 參數(shù)不僅可以指定哪些列收集基本的統(tǒng)計信息, 還可以指定哪些列收集直方圖, 因此我們需要考慮把 method_opt 參數(shù)分為兩個部分.

 下面圖中的第一部分指定了哪些列將收集基本的統(tǒng)計信息. 在這里我們想要收集表上所有列的基本統(tǒng)計信息, 因此我們使用 For all columns. 但是關于 size 部分, 我們應該怎樣設置參數(shù)呢？ 因為我們只想在一個列上收集直方圖, 所以我們先指定 size 1 來阻止所有列收集直方圖（刪除所有列的直方圖）

下面圖中的第二部分指定了 cust_id 列需要收集直方圖,  “For columns” 是 method_opt 語法中額外的部分, 它允許在參數(shù)設置部分為指定的列提供明確的操作. 在這里我們使用 FOR COLUMNS SIZE 254 CUST_ID 來指定 cust_id 列收集直方圖. 因此最終的 Method_opt 參數(shù)設置如下:

下面我們就使用 DBMS_STATS.GATHER_TABLE_STATS 來執(zhí)行收集統(tǒng)計信息, 雖然在系統(tǒng)中 sales 表中有很多列會用在  where 查詢語句中, 但在這里我們只是在 cust_id 列上建立直方圖.

       BEGIN
               dbms_stats.Gather_table_stats('SH', 'SALES', method_opt => 'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 CUST_ID');
       END;
       /

       PL/SQL procedure successfully completed.

 
      SQL>  SELECT  column_name, num_distinct, histogram    

                  FROM user_tab_col_statistics    

                  WHERE   table_name = 'SALES';

對于 method_opt 參數(shù)在很多種情況下需要用到更復雜的設置, 比如你不想收集指定列的任何統(tǒng)計信息. 目前并沒有方法告訴 Oracle 怎么樣不去收集指定列的統(tǒng)計信息, 但是你可以反過來考慮, 你可以通過 for columns 語法明確的列出需要收集統(tǒng)計信息的列. 下面我們演示不收集 sales 表中 prog_id 列的統(tǒng)計信息的方法：

       BEGIN
             dbms_stats.delete_column_stats('SH', 'SALES', 'PROD_ID');
       END;
       /

    PL/SQL procedure completed successfully. 

 BEGIN 
       dbms_stats.Gather_table_stats('SH', 'SALES', 

 method_opt => 'FOR COLUMNS SIZE 254 CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD'); 
   END; /

   PL/SQL procedure completed successfully.   

-- 即使我們沒有收集 prod_id 列的統(tǒng)計信息, oracle 仍然會準確的計算每行的平均長度.

 SQL> SELECT num_rows, avg_row_len
         FROM   user_tables
         WHERE  table_name = 'SALES';

注意:  for columns 語句只能用于 gather_table_stats 存儲過程.

最后, 在本文的開頭我提到 method_opt 參數(shù)可以用于收集擴展的統(tǒng)計信息. 擴展的統(tǒng)計信息包含兩類列的統(tǒng)計信息: 列的組合以及列的表達式的統(tǒng)計信息. 在下面的例子中, oracle 將會收集 sales 表上 prod_id 和 cust_id 列的組合的統(tǒng)計信息以及所有基本列的統(tǒng)計信息, 同時, oracle 還將會為列的組合的統(tǒng)計信息自動生成名字.

在收集統(tǒng)計信息的語句中, 我們不建議在 method_opt 中直接指定參數(shù), 而建議使用 dbms_stats.set_table_prefs 存儲過程

總結, GATHER_DICTIONARY_STATS, GATHER_DATABASE_STATS, 和 GATHER_SCHEMA_STATS 存儲過程只接受 “ FOR ALL [INDEXED|HIDDEN] columns ” 語法, 不能指定具體的列名.

       GATHER_TABLE_STATS procedure 存儲過程可以接受以 “for columns ” 格式的額外參數(shù), 使用這種語法可以控制以下內容：

• 哪些列將收集基本的統(tǒng)計信息
• 哪些列將收集直方圖以及指定直方圖的桶數(shù)
• 收集擴展的統(tǒng)計信息