How does the METHOD_OPT parameter work —— method_opt 參數應用

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

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

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

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

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

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

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

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

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

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

• 除了指定的列, 在其它列上創建直方圖
• 只在指定的列上創建直方圖

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

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

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

下面我們就使用 DBMS_STATS.GATHER_TABLE_STATS 來執行收集統計信息, 雖然在系統中 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 參數在很多種情況下需要用到更復雜的設置, 比如你不想收集指定列的任何統計信息. 目前并沒有方法告訴 Oracle 怎么樣不去收集指定列的統計信息, 但是你可以反過來考慮, 你可以通過 for columns 語法明確的列出需要收集統計信息的列. 下面我們演示不收集 sales 表中 prog_id 列的統計信息的方法：

       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 列的統計信息, oracle 仍然會準確的計算每行的平均長度.

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

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

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

在收集統計信息的語句中, 我們不建議在 method_opt 中直接指定參數, 而建議使用 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 ” 格式的額外參數, 使用這種語法可以控制以下內容：

• 哪些列將收集基本的統計信息
• 哪些列將收集直方圖以及指定直方圖的桶數
• 收集擴展的統計信息