14 拡張統計の管理

DBMS_STATSでは拡張統計の収集が可能で、複数の述語が1つの表の異なる列に存在する場合や、述語で式を使用する場合に、拡張統計によりカーディナリティ予測を改善できます。

拡張は、列グループまたは式のいずれかになります。列グループ統計では、同じ表の複数の列が1つのSQL文に同時に存在する場合のカーディナリティの見積りを改善できます。式統計では、組込みファンクションやユーザー定義ファンクションなどの式を述語で使用する際にオプティマイザの見積りが改善されます。

ノート:

仮想列の拡張統計を作成することはできません。

この章のトピックは、次のとおりです:

関連項目:

仮想列の制限事項は、『Oracle Database SQL言語リファレンス』を参照してください。

14.1 列グループの統計の管理

列グループは1つの単位として扱われる一連の列です。

基本的に、列グループは仮想列です。列グループの統計を収集することで、問合せでそれらの列をグループ化した際に、オプティマイザでカーディナリティ予測をより正確に算出できます。

次の項では、列グループの統計の概要および列グループの統計を手動で管理する方法について説明します。

関連項目:

DBMS_STATSパッケージについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください

14.1.1 列グループの統計について

WHERE句で1つの述語の選択性を決定する場合は、個々の列の統計が役に立ちます。

同じ表の異なる列に関する複数の述語がWHERE句に含まれている場合は、個々の列の統計では列間の関係が示されません。これは、列グループによって解決される問題です。

オプティマイザでは、述語の選択性を別々に計算してから組み合せることになります。ただし、個々の列間の関係が存在する場合は、オプティマイザでカーディナリティ予測を算出するタイミングを考慮に入れることができず、表の述語ごとの選択性に行数を掛けることで作成します。

次の図は、sh.customers表のcust_state_province列およびcountry_id列で統計を収集する2つの方法を対比しています。この図は、DBMS_STATSによって各列の個別の統計とグループの統計が収集される様子を示しています。列グループにはシステム生成の名前があります。

図14-1 列グループの統計

「図14-1 列グループの統計」の説明

ノート:

オプティマイザでは、等価述語、INリスト述語、さらにGROUP BYカーディナリティの見積りに対して列グループの統計を使用します。

この項では、次の項目について説明します。

14.1.1.1 列グループの統計が必要な理由: 例

この例では、列グループの統計によって、どのように、オプティマイザのカーディナリティ予測の正確性が高まるかを示します。

DBA_TAB_COL_STATISTICS表の次の問合せには、sh.customers表のcust_state_province列とcountry_id列で収集された統計に関する情報が示されています。

COL COLUMN_NAME FORMAT a20
COL NDV FORMAT 999

SELECT COLUMN_NAME, NUM_DISTINCT AS "NDV", HISTOGRAM
FROM   DBA_TAB_COL_STATISTICS
WHERE  OWNER = 'SH'
AND    TABLE_NAME = 'CUSTOMERS'
AND    COLUMN_NAME IN ('CUST_STATE_PROVINCE', 'COUNTRY_ID');

次に出力の例を示します。

COLUMN_NAME                 NDV HISTOGRAM
-------------------- ---------- ---------------
CUST_STATE_PROVINCE         145 FREQUENCY
COUNTRY_ID                   19 FREQUENCY

次の問合せに示すとおり、カリフォルニアには3341人の顧客が居住しています。

SELECT COUNT(*)
FROM   sh.customers 
WHERE  cust_state_province = 'CA';

 COUNT(*)
----------
    3341

州がCAで国のIDが52790(USA)である顧客の問合せに対する実行計画を検討します。

EXPLAIN PLAN FOR
  SELECT *
  FROM   sh.customers
  WHERE  cust_state_province = 'CA'
  AND    country_id=52790;
 
Explained.
 
sys@PROD> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 1683234692
 
--------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows | Bytes |Cost (%CPU)| Time  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |  128 | 24192 | 442 (7)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |  128 | 24192 | 442 (7)| 00:00:06 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
 
   1 - filter("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"=52790)
 
13 rows selected.

country_id列およびcust_state_province列の単一列統計に基づいて、オプティマイザは、USAのカリフォルニアの顧客の問合せで128行戻されると見積ります。実際には、カリフォルニアには3341人の顧客が居住していますが、オプティマイザは、カリフォルニアという州がUSAという国にあることを把握していないため、両方の述語により、戻される行数が減少すると想定して、カーディナリティが大幅に過小評価されます。

列グループの統計を収集することで、country_idとcust_state_provinceの値の間に実世界での関連があることをオプティマイザに認識させることができます。これらの統計によって、オプティマイザは、より正確なカーディナリティ予測を行うことが可能になります。

関連項目:

• 「特定のワークロードに対して有用な列グループの検出」

• 「ワークロードの監視中に検出された列グループの作成」

• 「手動による列グループの作成および統計の収集」

14.1.1.2 自動および手動の列グループ統計

Oracle Databaseでは、自動または手動で列グループ統計を作成できます。

オプティマイザは、SQL計画ディレクティブを使用して、より最適な計画を生成します。DBMS_STATSプリファレンスのAUTO_STAT_EXTENSIONSがON(デフォルトではOFF)に設定されている場合、SQL計画ディレクティブでは、ワークロードの述語の使用に基づいて、列グループ統計の作成を自動的に発生させることができます。SET_TABLE_PREFS、SET_GLOBAL_PREFSまたはSET_SCHEMA_PREFSプロシージャを使用して、AUTO_STAT_EXTENSIONSを設定できます。

列グループ統計を手動で管理する場合、次のようにDBMS_STATSを使用します。

• 列グループを検出します

• 前に検出された列グループを作成します

• 列グループを手動で作成し、列グループの統計を収集します

関連項目:

• 「特定のワークロードに対して有用な列グループの検出」

• 「ワークロードの監視中に検出された列グループの作成」

• 「手動による列グループの作成および統計の収集」

• オプティマイザ統計の設定に関するDBMS_STATSプロシージャについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください

14.1.1.3 列グループの統計のユーザー・インタフェース

いくつかのDBMS_STATSプログラム・ユニットには、列グループに関連するプリファレンスがあります。

表14-1 列グループに関連するDBMS_STATS API

プログラム・ユニットまたはプリファレンス	説明
SEED_COL_USAGEプロシージャ	指定されたワークロードでSQL文を繰り返し、それらをコンパイルした上で、その文に表示される列の列使用情報をシードします。 適切な列グループを決定するには、データベースで代表的なワークロードを監視する必要があります。監視期間中は問合せ自体を実行する必要はありません。かわりに、ワークロードで長時間実行される一部の問合せに対してEXPLAIN PLANを実行することで、それらの問合せの列グループ情報がデータベースで記録されていることを確認できます。
REPORT_COL_USAGEファンクション	ワークロード内のフィルタ述語、結合述語およびGROUP BY句にあった列をリストするレポートが生成されます。 このファンクションを使用して、特定の表に記録された列使用情報を確認できます。
CREATE_EXTENDED_STATSファンクション	拡張機能(列グループまたは式のいずれか)を作成します。ユーザー生成の統計収集ジョブまたは自動の統計収集ジョブのいずれかで表の統計を収集した場合、データベースでは拡張機能の統計が収集されます。
AUTO_STAT_EXTENSIONSプリファレンス	オプティマイザ統計を収集する場合、列グループを含む拡張機能の自動作成を制御します。SET_TABLE_PREFS、SET_SCHEMA_PREFSまたはSET_GLOBAL_PREFSを使用して、このプリファレンスを設定します。 AUTO_STAT_EXTENSIONSがOFF(デフォルト)に設定されている場合、データベースでは、列グループ統計を自動的に作成しません。拡張機能を作成するには、CREATE_EXTENDED_STATSファンクションを実行するか、DBMS_STATS APIのMETHOD_OPTパラメータで明示的に拡張された統計を指定する必要があります。 ONに設定されている場合、SQL計画ディレクティブでは、ワークロードの述語の列の使用に基づいて、列グループ統計を自動的に作成できます。

関連項目:

• 「表の人為的なオプティマイザ統計の設定」

• DBMS_STATSパッケージについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。

14.1.2 特定のワークロードに対して有用な列グループの検出

DBMS_STATS.SEED_COL_USAGEおよびREPORT_COL_USAGEを使用して、指定のワークロードに基づいて表で必要な列グループを決定できます。

この手法は、作成する拡張統計がわからない場合に役に立ちます。この手法は、式の統計に対しては機能しません。

前提条件

このチュートリアルでは、次のことが前提となっています。

• country_id列およびcust_state_province列を参照する述語を使用するsh.customers_test表(customers表から作成)の問合せに対して、カーディナリティ予測が正しくありません。

• データベースでワークロードを5分間(300秒間)監視します。

• データベースで必要な列グループを自動的に決定します。

列グループを検出するには:

1. SQL*PlusまたはSQL Developerを起動し、ユーザーshとしてデータベースにログインします。

2. customers_test表を作成してその表の統計を収集します。

   DROP TABLE customers_test;
   CREATE TABLE customers_test AS SELECT * FROM customer;
   EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'customers_test');
   

3. ワークロード監視を有効化します。

   別のSQL*Plusセッションでは、SYSとして接続して次のPL/SQLプログラムを実行し、300秒間の監視を有効化します。

   BEGIN
     DBMS_STATS.SEED_COL_USAGE(null,null,300);
   END;
   /
   

4. ユーザーshとして、ワークロード内の2つの問合せの実行計画を実行します。

   次の例では、customers_test表の2つの問合せの実行計画が示されています。

   EXPLAIN PLAN FOR
     SELECT *
     FROM   customers_test
     WHERE  cust_city = 'Los Angeles'
     AND    cust_state_province = 'CA'
     AND    country_id = 52790;
    
   SELECT PLAN_TABLE_OUTPUT 
   FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
    
   EXPLAIN PLAN FOR
     SELECT   country_id, cust_state_province, count(cust_city)
     FROM     customers_test
     GROUP BY country_id, cust_state_province;
    
   SELECT PLAN_TABLE_OUTPUT 
   FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
   

   出力例は次のように表示されます。

   PLAN_TABLE_OUTPUT
   ------------------------------------------------------------------------
   Plan hash value: 4115398853
    
   ----------------------------------------------------
   | Id  | Operation         | Name           | Rows  |
   ----------------------------------------------------
   |   0 | SELECT STATEMENT  |                |     1 |
   |   1 |  TABLE ACCESS FULL| CUSTOMERS_TEST |     1 |
   ----------------------------------------------------
    
   8 rows selected.
    
   PLAN_TABLE_OUTPUT
   ------------------------------------------------------------------------
   Plan hash value: 3050654408
    
   -----------------------------------------------------
   | Id  | Operation          | Name           | Rows  |
   -----------------------------------------------------
   |   0 | SELECT STATEMENT   |                |  1949 |
   |   1 |  HASH GROUP BY     |                |  1949 |
   |   2 |   TABLE ACCESS FULL| CUSTOMERS_TEST | 55500 |
   -----------------------------------------------------
    
   9 rows selected.
   

   最初の計画では、932行を戻す問合せの1行のカーディナリティが示されています。2番目の計画では、145行を戻す問合せの1949行のカーディナリティが示されています。

5. オプションで、表に記録された列使用情報を確認します。

   DBMS_STATS.REPORT_COL_USAGEファンクションを呼び出してレポートを生成します。

   SET LONG 100000
   SET LINES 120
   SET PAGES 0
   SELECT DBMS_STATS.REPORT_COL_USAGE(user, 'customers_test')
   FROM   DUAL;
   

   レポートは次のように表示されます。

   LEGEND:
   .......
    
   EQ         : Used in single table EQuality predicate
   RANGE      : Used in single table RANGE predicate
   LIKE       : Used in single table LIKE predicate
   NULL       : Used in single table is (not) NULL predicate
   EQ_JOIN    : Used in EQuality JOIN predicate
   NONEQ_JOIN : Used in NON EQuality JOIN predicate
   FILTER     : Used in single table FILTER predicate
   JOIN       : Used in JOIN predicate
   GROUP_BY   : Used in GROUP BY expression
   ........................................................................
    
   ########################################################################
    
   COLUMN USAGE REPORT FOR SH.CUSTOMERS_TEST
   .........................................
    
   1. COUNTRY_ID                          : EQ
   2. CUST_CITY                           : EQ
   3. CUST_STATE_PROVINCE                 : EQ
   4. (CUST_CITY, CUST_STATE_PROVINCE,
       COUNTRY_ID)                        : FILTER
   5. (CUST_STATE_PROVINCE, COUNTRY_ID)   : GROUP_BY
   ########################################################################
   

   前述のレポートでは、最初の3つの列が最初に監視された問合せの等価述語で使用されていました。

   ...
   WHERE  cust_city = 'Los Angeles'
   AND    cust_state_province = 'CA'
   AND    country_id = 52790;
   

   3つの列がすべて同じWHERE句に表示されていたため、レポートではそれらを1つのグループ・フィルタとして示しています。2番目の問合せでは、2つの列がGROUP BY句で表示されていたため、レポートではそれらをGROUP_BYとしてラベル付けしています。FILTERおよびGROUP_BYレポートにある列のセットは列グループの候補になります。

関連項目:

• 「SQLチューニング・セットの管理」

• DBMS_STATSパッケージについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください

14.1.3 ワークロードの監視中に検出された列グループの作成

DBMS_STATS.CREATE_EXTENDED_STATSファンクションを使用して、DBMS_STATS.SEED_COL_USAGEの実行によって以前に検出された列グループを作成できます。

前提条件

このチュートリアルでは、「特定のワークロードに対して有用な列グループの検出」のステップが実行済であることが前提となっています。

列グループを作成するには:

1. ウィンドウの監視中に取得された使用情報に基づいて、customers_test表の列グループを作成します。

   たとえば、次の問合せを実行します。

   SELECT DBMS_STATS.CREATE_EXTENDED_STATS(user, 'customers_test') FROM DUAL;
   

   出力例は次のように表示されます。

   ########################################################################
   EXTENSIONS FOR SH.CUSTOMERS_TEST
   ................................
   1. (CUST_CITY, CUST_STATE_PROVINCE,
       COUNTRY_ID)                     :SYS_STUMZ$C3AIHLPBROI#SKA58H_N created
   2. (CUST_STATE_PROVINCE, COUNTRY_ID):SYS_STU#S#WF25Z#QAHIHE#MOFFMM_ created
   ########################################################################
   

   データベースでは、フィルタ述語の列グループとGROUP BY操作のグループというcustomers_testの2つの列グループが作成されました。

2. 表の統計を再収集します。

   GATHER_TABLE_STATSを実行してcustomers_testの統計を再収集します。

   EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'customers_test');
   

3. ユーザーshとして、ワークロード内の2つの問合せの実行計画を実行します。

   USER_TAB_COL_STATISTICSビューをチェックして、データベースで作成された追加の統計を判別します。

   SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
   FROM   USER_TAB_COL_STATISTICS
   WHERE  TABLE_NAME = 'CUSTOMERS_TEST'
   ORDER BY 1;
   

   出力例の一部を次に示します。

   CUST_CITY                               620 HEIGHT BALANCED
   ...
   SYS_STU#S#WF25Z#QAHIHE#MOFFMM_          145 NONE
   SYS_STUMZ$C3AIHLPBROI#SKA58H_N          620 HEIGHT BALANCED
   

   この例では、DBMS_STATS.CREATE_EXTENDED_STATSファンクションから戻された2つの列グループ名が示されています。CUST_CITY、CUST_STATE_PROVINCEおよびCOUNTRY_IDで作成された列グループには高さ調整済ヒストグラムがあります。

4. 再び計画をEXPLAINします。

   次の例では、customers_test表の2つの問合せの実行計画が示されています。

   EXPLAIN PLAN FOR
     SELECT *
     FROM   customers_test
     WHERE  cust_city = 'Los Angeles'
     AND    cust_state_province = 'CA'
     AND    country_id = 52790;
    
   SELECT PLAN_TABLE_OUTPUT 
   FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
    
   EXPLAIN PLAN FOR
     SELECT   country_id, cust_state_province, count(cust_city)
     FROM     customers_test
     GROUP BY country_id, cust_state_province;
    
   SELECT PLAN_TABLE_OUTPUT 
   FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
   

   新しい計画では、より正確なカーディナリティ予測が示されます。

   ----------------------------------------------------
   | Id  | Operation         | Name           | Rows  |
   ----------------------------------------------------
   |   0 | SELECT STATEMENT  |                |  1093 |
   |   1 |  TABLE ACCESS FULL| CUSTOMERS_TEST |  1093 |
   ----------------------------------------------------
    
   8 rows selected.
    
   Plan hash value: 3050654408
    
   -----------------------------------------------------
   | Id  | Operation          | Name           | Rows  |
   -----------------------------------------------------
   |   0 | SELECT STATEMENT   |                |   145 |
   |   1 |  HASH GROUP BY     |                |   145 |
   |   2 |   TABLE ACCESS FULL| CUSTOMERS_TEST | 55500 |
   -----------------------------------------------------
   9 rows selected.

関連項目:

DBMS_STATSパッケージについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください

14.1.4 手動による列グループの作成および統計の収集

場合によっては、作成が必要な列グループがわかっていることがあります。

DBMS_STATS.GATHER_TABLE_STATSファンクションのMETHOD_OPT引数では、列グループの統計を自動的に作成および収集できます。FOR COLUMNSを使用して列のグループを指定することで、新しい列グループを作成できます。

前提条件

このチュートリアルでは、次のことが前提となっています。

• shスキーマのcustomers表にあるcust_state_province列およびcountry_id列の列グループを作成します。

• 表全体および新しい列グループで統計(ヒストグラムを含む)を収集します。

列グループを作成し、そのグループの統計を収集するには:

1. SQL*Plusで、shユーザーとしてデータベースにログインします。

2. 列グループを作成して統計を収集します。

   たとえば、次のPL/SQLプログラムを実行します。

   BEGIN
     DBMS_STATS.GATHER_TABLE_STATS( 'sh','customers',
     METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY ' ||
                   'FOR COLUMNS SIZE SKEWONLY (cust_state_province,country_id)' );
   END;
   /

関連項目:

DBMS_STATS.GATHER_TABLE_STATSプロシージャについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。

14.1.5 列グループ情報の表示

列グループの名前を取得するには、DBMS_STATS.SHOW_EXTENDED_STATS_NAMEファンクションまたはデータベース・ビューを使用します。

また、ビューを使用して、個別値の数や列グループでのヒストグラムの有無などの情報を取得できます。

前提条件

このチュートリアルでは、次のことが前提となっています。

• shスキーマのcustomers表にあるcust_state_province列およびcountry_id列の列グループは作成済です。

• 列グループ名、個別値の数のほか、列グループに対してヒストグラムが作成済であるかどうかを判別します。

列グループを監視するには:

1. SQL*Plusを起動し、ユーザーshとしてデータベースに接続します。

2. 列グループ名を判別するには、次のうちの1つを実行します。

   • SHOW_EXTENDED_STATS_NAMEファンクションを実行します。

     たとえば、次のPL/SQLプログラムを実行します。

     SELECT SYS.DBMS_STATS.SHOW_EXTENDED_STATS_NAME( 'sh','customers',
            '(cust_state_province,country_id)' ) col_group_name 
     FROM   DUAL;
     

     出力は、次のようなものです。

     COL_GROUP_NAME
     ----------------
     SYS_STU#S#WF25Z#QAHIHE#MOFFMM_
     

   • USER_STAT_EXTENSIONSビューを問い合せます。

     たとえば、次の問合せを実行します。

     SELECT EXTENSION_NAME, EXTENSION 
     FROM   USER_STAT_EXTENSIONS 
     WHERE  TABLE_NAME='CUSTOMERS';
     
     EXTENSION_NAME                     EXTENSION
     --------------------------------------------------------------------
     SYS_STU#S#WF25Z#QAHIHE#MOFFMM_  ("CUST_STATE_PROVINCE","COUNTRY_ID")
     

3. 個別値の数と、列グループに対してヒストグラムが作成済かどうかを問い合せます。

   たとえば、次の問合せを実行します。

   SELECT e.EXTENSION col_group, t.NUM_DISTINCT, t.HISTOGRAM
   FROM   USER_STAT_EXTENSIONS e, USER_TAB_COL_STATISTICS t
   WHERE  e.EXTENSION_NAME=t.COLUMN_NAME
   AND    e.TABLE_NAME=t.TABLE_NAME
   AND    t.TABLE_NAME='CUSTOMERS';
   
   COL_GROUP                             NUM_DISTINCT        HISTOGRAM
   -------------------------------------------------------------------
   ("COUNTRY_ID","CUST_STATE_PROVINCE")  145                 FREQUENCY

関連項目:

DBMS_STATS.SHOW_EXTENDED_STATS_NAMEファンクションについて学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください

14.1.6 列グループの削除

DBMS_STATS.DROP_EXTENDED_STATSファンクションを使用して、表から列グループを削除します。

前提条件

このチュートリアルでは、次のことが前提となっています。

• shスキーマのcustomers表にあるcust_state_province列およびcountry_id列の列グループは作成済です。

• 列グループを削除します。

列グループを削除するには:

1. SQL*Plusを起動し、ユーザーshとしてデータベースに接続します。

2. 列グループを削除します。

   たとえば、次のPL/SQLプログラムでcustomers表から列グループを削除します。

   BEGIN
     DBMS_STATS.DROP_EXTENDED_STATS( 'sh', 'customers', 
                                     '(cust_state_province, country_id)' );
   END;
   /

関連項目:

DBMS_STATS.DROP_EXTENDED_STATSファンクションについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。

14.2 式の統計の管理

式の統計と呼ばれる拡張統計の種類は、WHERE句に式を使用する述語がある場合にオプティマイザの見積りを改善します。

この項では、次の項目について説明します。

14.2.1 式の統計について

WHERE句列に適用された(function(col)=constant)形式の式の場合、ファンクションベースの索引が存在しないかぎり、オプティマイザは述語のカーディナリティへのこのファンクションの影響がわかりません。ただし、式(function(col)自身の式統計を収集できます。

次の図は、ファンクションを使用する問合せの計画を生成するために統計を使用するオプティマイザを示しています。上部には、列の統計をチェックするオプティマイザが示されています。下部には、問合せで使用される式に対応する統計をチェックするオプティマイザが示されています。式の統計によってより正確な見積りが生成されます。

図14-2 式の統計

「図14-2 式の統計」の説明

図14-2に示すとおり、式の統計が利用できない場合、オプティマイザは最適ではない計画を生成する可能性があります。

この項では、次の項目について説明します。

関連項目:

SQL関数について学習するには、『Oracle Database SQL言語リファレンス』を参照してください。

14.2.1.1 式の統計が有用なタイミング: 例

sh.customers表の次の問合せでは、カリフォルニア州に3341人の顧客がいることが示されています。

sys@PROD> SELECT COUNT(*) FROM sh.customers WHERE cust_state_province='CA';
 
  COUNT(*)
----------
      3341

LOWER()ファンクションを適用して同じ問合せの計画を検討します。

sys@PROD> EXPLAIN PLAN FOR
  2  SELECT * FROM sh.customers WHERE LOWER(cust_state_province)='ca';
Explained.

sys@PROD> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2008213504

---------------------------------------------------------------------------
|Id | Operation         | Name      | Rows | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |           |  555 |  108K |  406   (1)| 00:00:05 |
|*1 |  TABLE ACCESS FULL| CUSTOMERS |  555 |  108K |  406   (1)| 00:00:05 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(LOWER("CUST_STATE_PROVINCE")='ca')

LOWER(cust_state_province)='ca'の式の統計が存在しないため、オプティマイザの見積りが大幅にずれています。DBMS_STATSプロシージャを使用することで、この見積りを修正できます。

14.2.2 式の統計の作成

DBMS_STATSを使用して、ユーザー指定の式の統計を作成できます。

次のプログラム・ユニットのいずれかを使用できます。

• GATHER_TABLE_STATSプロシージャ

• CREATE_EXTENDED_STATISTICSファンクションに続くGATHER_TABLE_STATSプロシージャ

前提条件

このチュートリアルでは、次のことが前提となっています。

• UPPER(cust_state_province)ファンクションを使用するsh.customersの問合せに対する選択性の見積りが間違っています。

• UPPER(cust_state_province)式の統計を収集します。

式の統計を作成するには:

1. SQL*Plusを起動し、ユーザーshとしてデータベースに接続します。

2. 表の統計を収集します。

   たとえば、次のコマンドを実行して、method_opt引数に関数を指定します。

   BEGIN
     DBMS_STATS.GATHER_TABLE_STATS( 
       'sh'
   ,   'customers'
   ,   method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY ' || 
                     'FOR COLUMNS (LOWER(cust_state_province)) SIZE SKEWONLY' 
   );
   END;

関連項目:

DBMS_STATS.GATHER_TABLE_STATSプロシージャについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。

14.2.3 式の統計の表示

式の統計に関する情報を取得するには、データベース・ビューDBA_STAT_EXTENSIONSとDBMS_STATS.SHOW_EXTENDED_STATS_NAMEファンクションを使用します。

また、ビューを使用して、個別値の数や列グループでのヒストグラムの有無などの情報を取得できます。

前提条件

このチュートリアルでは、次のことが前提となっています。

• LOWER(cust_state_province)式の拡張統計が作成されています。

• 列グループ名、個別値の数のほか、列グループに対してヒストグラムが作成済であるかどうかを判別します。

式の統計を監視するには:

1. SQL*Plusを起動し、ユーザーshとしてデータベースに接続します。

2. 統計の拡張の名前と定義を問い合せます。

   たとえば、次の問合せを実行します。

   COL EXTENSION_NAME FORMAT a30
   COL EXTENSION FORMAT a35
   
   SELECT EXTENSION_NAME, EXTENSION
   FROM   USER_STAT_EXTENSIONS
   WHERE  TABLE_NAME='CUSTOMERS';
   

   出力例は次のように表示されます。

   EXTENSION_NAME                 EXTENSION
   ------------------------------ ------------------------------
   SYS_STUBPHJSBRKOIK9O2YV3W8HOUE (LOWER("CUST_STATE_PROVINCE"))
   

3. 個別値の数を問い合せ、式に対してヒストグラムが作成済かどうかを判別します。

   たとえば、次の問合せを実行します。

   SELECT e.EXTENSION expression, t.NUM_DISTINCT, t.HISTOGRAM
   FROM   USER_STAT_EXTENSIONS e, USER_TAB_COL_STATISTICS t
   WHERE  e.EXTENSION_NAME=t.COLUMN_NAME
   AND    e.TABLE_NAME=t.TABLE_NAME
   AND    t.TABLE_NAME='CUSTOMERS';
   
   EXPRESSION                            NUM_DISTINCT        HISTOGRAM
   -------------------------------------------------------------------
   (LOWER("CUST_STATE_PROVINCE"))        145                 FREQUENCY

関連項目:

• DBMS_STATS.SHOW_EXTENDED_STATS_NAMEプロシージャについて学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください

• DBA_STAT_EXTENSIONSビューについて学習するには、Oracle Databaseリファレンスを参照してください

14.2.4 式の統計の削除

表から列グループを削除するには、DBMS_STATS.DROP_EXTENDED_STATSファンクションを使用します。

前提条件

このチュートリアルでは、次のことが前提となっています。

• LOWER(cust_state_province)式の拡張統計が作成されています。

• 式の統計を削除します。

式の統計を削除するには:

1. SQL*Plusを起動し、ユーザーshとしてデータベースに接続します。

2. 列グループを削除します。

   たとえば、次のPL/SQLプログラムでcustomers表から列グループを削除します。

   BEGIN
     DBMS_STATS.DROP_EXTENDED_STATS(
       'sh'
   ,   'customers'
   ,   '(LOWER(cust_state_province))'
   );
   END;
   /

関連項目:

DBMS_STATS.DROP_EXTENDED_STATSプロシージャについて学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください