| Oracle® Database SQLチューニング・ガイド 12c リリース1 (12.1) B71277-09 |
|
 前 |
 次へ |
この章では、拡張統計など、オプティマイザ統計の管理に関連する概念およびタスクの詳細について説明します。
この章の内容は次のとおりです。
デフォルトでは、オプティマイザ統計が存在しないか、失効したか、不十分な場合に、動的統計によって、解析の間に再帰的SQLが自動実行されて、表ブロックの小さなランダム・サンプルがスキャンされます。
この項の内容は次のとおりです。
関連項目:
動的統計レベルでは、データベースで動的統計を収集するタイミングと、統計の収集にオプティマイザが使用するサンプル・サイズの両方が制御されます。
動的統計レベルを設定するには、OPTIMIZER_DYNAMIC_SAMPLING初期化パラメータまたは文ヒントのいずれかを使用します。
注意:
Oracle Database 12c リリース1 (12.1)より前のリリースでは、動的統計は動的サンプリングと呼ばれていました。
次の表は、動的統計のレベルについて説明しています。次の点に注意してください。
動的統計が無効化されていない場合、SQL文でパラレル実行が使用されていると、データベースによって動的統計を使用することが選択されることがあります。
OPTIMIZER_ADAPTIVE_FEATURES初期化パラメータがtrueで、かつ、関連するSQL計画ディレクティブが存在する場合は、オプティマイザによって動的統計が使用されます。結果の統計は、他の問合せで使用できるように、データベースによってサーバーの結果キャッシュに保持されます。
表13-1 動的統計レベル
| レベル | オプティマイザで動的統計を使用するタイミング | サンプル・サイズ(ブロック) |
|---|---|---|
0 |
動的統計を使用しません。 |
該当なし |
1 |
次の基準を満たす場合のみ、統計を含まないすべての表に動的統計を使用します。
|
32 |
2 |
文の少なくとも1つの表に統計がない場合は、動的統計を使用します。これはデフォルト値です。 |
64 |
3 |
次のいずれかの条件に当てはまる場合は、動的統計を使用します。
|
64 |
4 |
次のいずれかの条件に当てはまる場合は、動的統計を使用します。
|
64 |
5 |
基準はレベル4と同じですが、異なるサンプル・サイズがデータベースによって使用されます。 |
128 |
6 |
基準はレベル4と同じですが、異なるサンプル・サイズがデータベースによって使用されます。 |
256 |
7 |
基準はレベル4と同じですが、異なるサンプル・サイズがデータベースによって使用されます。 |
512 |
8 |
基準はレベル4と同じですが、異なるサンプル・サイズがデータベースによって使用されます。 |
1024 |
9 |
基準はレベル4と同じですが、異なるサンプル・サイズがデータベースによって使用されます。 |
4086 |
10 |
基準はレベル4と同じですが、異なるサンプル・サイズがデータベースによって使用されます。 |
すべてのブロック |
11 |
オプティマイザによって必要と判断された場合は、自動的に適応動的サンプリングが使用されます。 |
自動的に決定 |
関連項目:
OPTIMIZER_DYNAMIC_SAMPLING初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。
すべてのSQL文に利点のある、データベースレベルの設定を決定することは容易ではありません。動的統計のレベルを設定する場合は、OPTIMIZER_DYNAMIC_SAMPLING初期化パラメータをセッション・レベルで設定することをお薦めします。
前提条件
このチュートリアルでは、次のことが前提となっています。
次の問合せに対して選択性の見積りを修正する場合で、2つの相関列にWHERE句の述語があります。
SELECT * FROM sh.customers WHERE cust_city='Los Angeles' AND cust_state_province='CA';
前述の問合せはシリアル処理を使用します。
sh.customers表は、問合せの条件を満たす932の行を含みます。
sh.customers表に収集された統計があります。
cust_city列とcust_state_province列に索引を作成しています。
OPTIMIZER_DYNAMIC_SAMPLING初期化パラメータが、デフォルト・レベルの2に設定されています。
動的統計レベルを手動設定する手順は次のとおりです。
適切な権限でSQL*Plusをデータベースに接続し、実行計画を次のようにEXPLAINします。
EXPLAIN PLAN FOR SELECT * FROM sh.customers WHERE cust_city='Los Angeles' AND cust_state_province='CA';
その計画を次のように問い合せます。
SET LINESIZE 130 SET PAGESIZE 0 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
出力が次のように表示されます(この例はページに収まるように再フォーマットされています)。
------------------------------------------------------------------------------- |Id| Operation | Name |Rows|Bytes|Cost | Time | ------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 53| 9593|53(0)|00:00:01| | 1| TABLE ACCESS BY INDEX ROWID|CUSTOMERS | 53| 9593|53(0)|00:00:01| |*2| INDEX RANGE SCAN |CUST_CITY_STATE_IND| 53| 9593| 3(0)|00:00:01| ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CUST_CITY"='Los Angeles' AND "CUST_STATE_PROVINCE"='CA')
WHERE句の列には実世界の相関関係がありますが、オプティマイザはロサンゼルスがカリフォルニアにあることを認識せず、両方の述語により、戻される行の数が減少すると想定しています。このように、表には条件を満たす932の行が含まれますが、太字で示すようにオプティマイザは53と見積っています。
データベースでこの計画に動的統計を使用したとすれば、計画出力のNoteセクションでこの事実が指摘されることになります。オプティマイザで動的統計が使用されなかった理由は、文がシリアルに実行されたことや、標準統計が存在することのほか、OPTIMIZER_DYNAMIC_SAMPLINGパラメータがデフォルトの2に設定されていることがあげられます。
次の文を使用して、セッションで動的統計レベルを4に設定します。
ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING=4;
この計画を再び次のようにEXPLAINします。
EXPLAIN PLAN FOR SELECT * FROM sh.customers WHERE cust_city='Los Angeles' AND cust_state_province='CA';
この新しい計画では、太字で示されている値932のように、行数の見積りがより正確に示されています。
PLAN_TABLE_OUTPUT --------------------------------------------------------------------------- Plan hash value: 2008213504 --------------------------------------------------------------------------- | Id | Operation | Name |Rows | Bytes |Cost (%CPU)|Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 932 | 271K| 406 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL| CUSTOMERS | 932 | 271K| 406 (1)| 00:00:05 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("CUST_CITY"='Los Angeles' AND "CUST_STATE_PROVINCE"='CA') Note ----- - dynamic statistics used for this statement (level=4)
計画の一番下のメモは、サンプリング・レベルが4であることを示します。追加された動的統計によって、オプティマイザにcust_city列とcust_state_province列間の実世界の関係を認識させることで、行数が53ではなく932という、より正確な見積りを生成できるようになります。
関連項目:
DYNAMIC_SAMPLINGヒントを使用したサンプリング・レベルの設定の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
OPTIMIZER_DYNAMIC_SAMPLING初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。
一般的には、繰返しのないOLTP問合せなど、コンパイル時間を可能なかぎり速くすることが必要な問合せに対して、動的統計のコストが発生しないようにすることがベスト・プラクティスです。
セッション・レベルの動的統計を無効化する手順は次のとおりです。
適切な権限でSQL*Plusをデータベースに接続します。
動的統計レベルを0に設定します。
たとえば、次の文を実行します。
ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING=0;
関連項目:
OPTIMIZER_DYNAMIC_SAMPLING初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。
デフォルトでは、統計収集が終了すると、データベースで統計が自動的に公開されます。その一方で、保留中の統計を使用してその統計を保存することで、収集後すぐに公開されないようにできます。この手法は、保留中の統計を使用してセッション内の問合せをテストする際に役立ちます。テストの結果が十分であれば、その統計を公開してデータベース全体で使用することができます。
データベースでは、公開された統計と同様に、データ・ディクショナリに保留中の統計が格納されています。
デフォルトでは、オプティマイザは公開された統計を使用します。OPTIMIZER_USE_PENDING_STATISTICS初期化パラメータをtrue(デフォルトはfalse)に設定することで、デフォルトの動作を変更できます。
次の図の上部には、sh.customers表の統計を収集して、それを保留中の状態でデータ・ディクショナリに格納するオプティマイザが示されています。ダイアグラムの下部には、sh.customersの問合せの処理に、公開された統計のみを使用するオプティマイザが示されています。
場合によっては、オプティマイザが、公開された統計と保留中の統計の組合せを使用することがあります。たとえば、customers表の公開された統計および保留中の統計の両方がデータベースに格納されているとします。orders表については、公開された統計のみがデータベースに格納されているとします。OPTIMIZER_USE_PENDING_STATS = trueの場合、オプティマイザはcustomersには保留中の統計を使用し、ordersには公開された統計を使用します。OPTIMIZER_USE_PENDING_STATS = falseの場合、オプティマイザはcustomersおよびordersに公開された統計を使用します。
関連項目:
OPTIMIZER_USE_PENDING_STATISTICS初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。
DBMS_STATSパッケージを使用して、統計の公開に関する操作を実行できます。表13-2には、関連するプログラム・ユニットがリストされています。
表13-2 オプティマイザ統計の公開に関連するDBMS_STATSプログラム・ユニット
| プログラム・ユニット | 説明 |
|---|---|
|
|
|
|
|
|
|
すべてのオブジェクトまたは特定のオブジェクトのみの有効な保留中の統計を公開します。 |
|
保留中の統計を削除します。 |
|
保留中の統計をエクスポートします。 |
OPTIMIZER_USE_PENDING_STATISTICS初期化パラメータでは、データベースで保留中の統計が使用可能な場合は、その統計を使用するかどうかが決定されます。デフォルト値はfalseで、オプティマイザが公開された統計のみを使用することを意味します。trueに設定した場合は、オプティマイザが既存の保留中の統計を使用するように指定されます。このパラメータをデータベース・レベルではなく、セッション・レベルで設定することがベスト・プラクティスです。
データ・ディクショナリ・ビューでは、公開された統計に関するアクセス情報を使用できます。表13-3には、関連するビューがリストされています。
表13-3 オプティマイザ統計の公開に関連するビュー
| ビュー | 説明 |
|---|---|
|
現行ユーザーがアクセス可能な表のオプティマイザ統計を表示します。 |
|
|
|
現行のユーザーが所有する表パーティションの列統計およびヒストグラム情報を示します。 |
|
現行のユーザーが所有するパーティション・オブジェクトのサブパーティションの列統計およびヒストグラム情報を示します。 |
|
現行ユーザーがアクセス可能な索引のオプティマイザ統計を表示します。 |
|
現行ユーザーがアクセス可能な表、パーティションおよびサブパーティションの保留中の統計を示します。 |
|
現行ユーザーがアクセス可能な列の保留中の統計を示します。 |
|
現行ユーザーがアクセス可能な表、パーティションおよびサブパーティションの保留中の統計で、 |
関連項目:
DBMS_STATSパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
USER_TAB_PENDING_STATSおよび関連するビューの詳細は、『Oracle Databaseリファレンス』を参照してください。
この項では、DBMS_STATSプログラム・ユニットを使用してオプティマイザ統計の公開動作を変更する方法のほか、これらの統計をエクスポートおよび削除する方法について説明します。
前提条件
このチュートリアルでは、次のことが前提となっています。
新しく収集された統計が保留中の状態になるように、sh.customers表およびsh.sales表のプリファレンスを変更します。
現行のセッションで保留中の統計を使用します。
sh.customers表の統計を収集し、保留中の統計を公開します。
sh.sales表の保留中の統計を収集しますが、それらを公開せずに削除することとします。
新しく収集された統計を公開するように、sh.customers表およびsh.sales表のプリファレンスを変更します。
公開された統計と保留中の統計を管理する手順は次のとおりです。
SQL*Plusを起動し、ユーザーshとしてデータベースに接続します。
グローバル・オプティマイザ統計の公開設定を問い合せます。
次の問合せを実行します(出力例も示します)。
sh@PROD> SELECT DBMS_STATS.GET_PREFS('PUBLISH') PUBLISH FROM DUAL;
PUBLISH
-------
TRUE
値trueは、統計を収集する際に統計がデータベースで公開されることを示します。特定の表プリファレンスが設定されていないかぎり、各表でこの値が使用されます。
GET_PREFSを使用する場合は、スキーマ名または表名も指定できます。この関数は、表のプリファレンスが設定されている場合はそのプリファレンスを戻します。それ以外の場合はグローバル・プリファレンスが戻されます。
保留中の統計を問い合せます。
たとえば、次の問合せを実行します(出力例も示します)。
sh@PROD> SELECT * FROM USER_TAB_PENDING_STATS; no rows selected
この例では、現在、データベースにshスキーマの保留中の統計が格納されていないことが示されています。
sh.customers表の公開プリファレンスを変更します。
たとえば、次のプロシージャを実行することで統計を保留中としてマークします。
BEGIN
DBMS_STATS.SET_TABLE_PREFS('sh', 'customers', 'publish', 'false');
END;
/
その後、customers表で統計を収集する場合は、収集ジョブの完了時にデータベースで統計が自動的に公開されなくなります。かわりに、新規に収集された統計はUSER_TAB_PENDING_STATS表に格納されます。
sh.customersの統計を収集します。
たとえば、次のプログラムを実行します。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('sh','customers');
END;
/
保留中の統計を問い合せます。
たとえば、次の問合せを実行します(出力例も示します)。
sh@PROD> SELECT TABLE_NAME, NUM_ROWS FROM USER_TAB_PENDING_STATS; TABLE_NAME NUM_ROWS ------------------------------ ---------- CUSTOMERS 55500
この例は、現在、データベースにsh.customers表の保留中の統計が格納されていることを示しています。
このセッションで保留中の統計を使用するようにオプティマイザに指示します。
次に示すように、OPTIMIZER_USE_PENDING_STATISTICS初期化パラメータをtrueに設定します。
ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = true;
ワークロードを実行します。
次の例は、Bruce Chalmersという名前のすべての顧客の電子メール・アドレスを変更します。
UPDATE sh.customers SET cust_email='ChalmersB@company.com' WHERE cust_first_name = 'Bruce' AND cust_last_name = 'Chalmers'; COMMIT;
オプティマイザは、このセッションですべてのSQL文をコンパイルする際に、公開された統計ではなく保留中の統計を使用します。
sh.customersの保留中の統計を公開します。
たとえば、次のプログラムを実行します。
BEGIN
DBMS_STATS.PUBLISH_PENDING_STATS('SH','CUSTOMERS');
END;
/
sh.sales表の公開プリファレンスを変更します。
たとえば、次のプログラムを実行します。
BEGIN
DBMS_STATS.SET_TABLE_PREFS('sh', 'sales', 'publish', 'false');
END;
/
その後、sh.sales表で統計を収集する場合は、収集ジョブの完了時にデータベースで統計が自動的に公開されなくなります。かわりに、データベースではUSER_TAB_PENDING_STATS表に統計が格納されます。
sh.salesの統計を収集します。
たとえば、次のプログラムを実行します。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('sh','sales');
END;
/
sh.salesの保留中の統計を削除します。
考えが変わって、sh.salesの保留中の統計を削除することにしたと仮定します。次のプログラムを実行します。
BEGIN
DBMS_STATS.DELETE_PENDING_STATS('sh','sales');
END;
/
sh.customers表とsh.sales表の公開プリファレンスを変更してデフォルト設定に戻します。
たとえば、次のプログラムを実行します。
BEGIN
DBMS_STATS.SET_TABLE_PREFS('sh', 'customers', 'publish', null);
DBMS_STATS.SET_TABLE_PREFS('sh', 'sales', 'publish', null);
END;
/
DBMS_STATSでは拡張統計の収集が可能で、複数の述語が1つの表の異なる列に存在する場合や、述語で式を使用する場合に、拡張統計によりカーディナリティ予測を改善できます。拡張は、列グループまたは式のいずれかになります。
Oracle Databaseでは次の種類の拡張統計がサポートされます。
列グループの統計
この種類の拡張統計では、同じ表の複数の列が1つのSQL文に同時に存在する場合のカーディナリティ予測を改善できます。たとえば、自動車メーカーと車種、市と都道府県などです。「列グループの統計の管理」を参照してください。
式の統計
この種類の拡張統計では、組込みファンクションやユーザー定義ファンクションなどの式を述語で使用する際にオプティマイザの見積りが改善されます。たとえば、従業員の姓に適用されたUPPERファンクションなどです。
注意:
仮想列の拡張統計を作成することはできません。
関連項目:
仮想列の制限事項は、『Oracle Database SQL言語リファレンス』を参照してください。
列グループは1つの単位として扱われる一連の列です。
基本的に、列グループは仮想列です。列グループの統計を収集することで、問合せでそれらの列をグループ化した際に、オプティマイザでカーディナリティ予測をより正確に算出できます。
次の項では、列グループの統計の概要および列グループの統計を手動で管理する方法について説明します。
関連項目:
DBMS_STATSパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
WHERE句で1つの述語の選択性を決定する場合は、個々の列の統計が役に立ちます。ただし、WHERE句に同じ表の異なる列にある複数の述語が含まれている場合は、個々の列の統計では列間の関係が示されません。これは、列グループによって解決される問題です。
オプティマイザでは、述語の選択性を別々に計算してから組み合せることになります。ただし、個々の列間の関係が存在する場合は、オプティマイザでカーディナリティ予測を算出するタイミングを考慮に入れることができず、表の述語ごとの選択性に行数を掛けることで作成します。
次の図は、sh.customers表のcust_state_province列およびcountry_id列で統計を収集する2つの方法を対比しています。この図は、DBMS_STATSによって各列の個別の統計とグループの統計が収集される様子を示しています。列グループにはシステム生成の名前があります。
注意:
オプティマイザでは、等価述語、INリスト述語、さらにGROUP BYカーディナリティの見積りに対して列グループの統計を使用します。
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の値の間に実世界での関連があることをオプティマイザに認識させることができます。これらの統計によって、オプティマイザは、より正確なカーディナリティ予測を行うことが可能になります。
いくつかのDBMS_STATSプログラム・ユニットには、列グループに関連するプリファレンスがあります。
表13-4 DBMS_STATS列グループのプログラム・ユニット
| プログラム・ユニット | 説明 |
|---|---|
|
指定されたワークロードでSQL文を繰り返し、それらをコンパイルした上で、その文に表示される列の列使用情報をシードします。 適切な列グループを決定するには、データベースで代表的なワークロードを監視する必要があります。監視期間中は問合せ自体を実行する必要はありません。かわりに、ワークロードで長時間実行される一部の問合せに対して |
|
ワークロード内のフィルタ述語、結合述語および このファンクションを使用して、特定の表に記録された列使用情報を確認できます。 |
|
拡張機能(列グループまたは式のいずれか)を作成します。ユーザー生成の統計収集ジョブまたは自動の統計収集ジョブのいずれかで表の統計を収集した場合、データベースでは拡張機能の統計が収集されます。 |
関連項目:
DBMS_STATSパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
DBMS_STATS.SEED_COL_USAGEおよびREPORT_COL_USAGEを使用して、指定のワークロードに基づいて表で必要な列グループを決定できます。この手法は、作成する拡張統計がわからない場合に役に立ちます。この手法は、式の統計に対しては機能しません。
注意:
SQLチューニング・セットの列使用はシードできます(「SQLチューニング・セットの管理」を参照)。
前提条件
このチュートリアルでは、次のことが前提となっています。
country_id列およびcust_state_province列を参照する述語を使用するsh.customers_test表(customers表から作成)の問合せに対して、カーディナリティ予測が正しくありません。
データベースでワークロードを5分間(300秒間)監視します。
データベースで必要な列グループを自動的に決定します。
列グループを検出する手順は次のとおりです。
SQL*Plusをユーザーshとしてデータベースに接続した上で、customers_test表を作成し、その統計を収集します。
CONNECT SH/SH DROP TABLE customers_test; CREATE TABLE customers_test AS SELECT * FROM customer; EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'customers_test');
ワークロード監視を有効化します。
別のSQL*Plusセッションでは、SYSとして接続して次のPL/SQLプログラムを実行し、300秒間の監視を有効化します。
BEGIN DBMS_STATS.SEED_COL_USAGE(null,null,300); END; /
ユーザー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行のカーディナリティが示されています。
オプションで、表に記録された列使用情報を確認します。
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レポートにある列のセットは列グループの候補になります。
関連項目:
DBMS_STATSパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
表13-4に説明されているように、DBMS_STATS.CREATE_EXTENDED_STATSファンクションを使用して、DBMS_STATS.SEED_COL_USAGEの実行によって以前に検出された列グループを作成できます。
前提条件
このチュートリアルでは、「特定のワークロードに対して有用な列グループの検出」の手順が実行済であることが前提となっています。
列グループを作成する手順は次のとおりです。
ウィンドウの監視中に取得された使用情報に基づいて、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つの列グループが作成されました。
表の統計を再収集します。
GATHER_TABLE_STATSを実行してcustomers_testの統計を再収集します。
EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'customers_test');
ユーザー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で作成された列グループには高さ調整済ヒストグラムがあります。
再び計画を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パッケージおよびタイプ・リファレンス』を参照してください。
場合によっては、作成が必要な列グループがわかっていることがあります。DBMS_STATS.GATHER_TABLE_STATSファンクションのMETHOD_OPT引数では、列グループの統計を自動的に作成および収集できます。FOR COLUMNSを使用して列のグループを指定することで、新しい列グループを作成できます。
前提条件
このチュートリアルでは、次のことが前提となっています。
shスキーマのcustomers表にあるcust_state_province列およびcountry_id列の列グループを作成します。
表全体および新しい列グループで統計(ヒストグラムを含む)を収集します。
列グループを作成し、そのグループの統計を収集する手順は次のとおりです。
SQL*Plusを起動し、ユーザーshとしてデータベースに接続します。
列グループを作成して統計を収集します。
たとえば、次の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パッケージおよびタイプ・リファレンス』を参照してください。
列グループの名前を取得するには、DBMS_STATS.SHOW_EXTENDED_STATS_NAMEファンクションまたはデータベース・ビューを使用します。また、ビューを使用して、個別値の数や列グループでのヒストグラムの有無などの情報を取得できます。
前提条件
このチュートリアルでは、次のことが前提となっています。
shスキーマのcustomers表にあるcust_state_province列およびcountry_id列の列グループは作成済です。
列グループ名、個別値の数のほか、列グループに対してヒストグラムが作成済であるかどうかを判別します。
列グループを監視する手順は次のとおりです。
SQL*Plusを起動し、ユーザーshとしてデータベースに接続します。
列グループ名を判別するには、次のうちの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")
個別値の数と、列グループに対してヒストグラムが作成済かどうかを問い合せます。
たとえば、次の問合せを実行します。
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パッケージおよびタイプ・リファレンス』を参照してください。
DBMS_STATS.DROP_EXTENDED_STATSファンクションを使用して、表から列グループを削除します。
前提条件
このチュートリアルでは、次のことが前提となっています。
shスキーマのcustomers表にあるcust_state_province列およびcountry_id列の列グループは作成済です。
列グループを削除します。
列グループを削除する手順は次のとおりです。
SQL*Plusを起動し、ユーザーshとしてデータベースに接続します。
列グループを削除します。
たとえば、次の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パッケージおよびタイプ・リファレンス』を参照してください。
式の統計と呼ばれる拡張統計の種類は、WHERE句に式を使用する述語がある場合にオプティマイザの見積りを改善します。
この項の内容は次のとおりです。
WHERE句列に適用されたフォーム(function(col)=constant)内の式については、ファンクション索引が存在しないかぎり、オプティマイザではこのファンクションが述語のカーディナリティに及ぼす影響を認識しません。ただし、式function(col)自体に対する式の統計を収集できます。
次の図は、ファンクションを使用する問合せの計画を生成するために統計を使用するオプティマイザを示しています。上部には、列の統計をチェックするオプティマイザが示されています。下部には、問合せで使用される式に対応する統計をチェックするオプティマイザが示されています。式の統計によってより正確な見積りが生成されます。
図13-3に示すとおり、式の統計が利用できない場合、オプティマイザは最適ではない計画を生成する可能性があります。
関連項目:
SQL関数の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
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プロシージャを使用することで、この見積りを修正できます。
DBMS_STATSを使用して、ユーザー指定の式の統計を作成できます。
次のプログラム・ユニットのいずれかを使用できます。
GATHER_TABLE_STATSプロシージャ
CREATE_EXTENDED_STATISTICSファンクションに続くGATHER_TABLE_STATSプロシージャ
前提条件
このチュートリアルでは、次のことが前提となっています。
UPPER(cust_state_province)ファンクションを使用するsh.customersの問合せに対する選択性の見積りが間違っています。
UPPER(cust_state_province)式の統計を収集します。
式の統計を作成する手順は次のとおりです。
SQL*Plusを起動し、ユーザーshとしてデータベースに接続します。
表の統計を収集します。
たとえば、次のコマンドを実行して、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パッケージおよびタイプ・リファレンス』を参照してください。
データベース・ビューの DBA_STAT_EXTENSIONSとDBMS_STATS.SHOW_EXTENDED_STATS_NAMEファンクションを使用して、式の統計に関する情報を取得します。また、ビューを使用して、個別値の数や列グループでのヒストグラムの有無などの情報を取得できます。
前提条件
このチュートリアルでは、次のことが前提となっています。
LOWER(cust_state_province)式の拡張統計が作成されています。
列グループ名、個別値の数のほか、列グループに対してヒストグラムが作成済であるかどうかを判別します。
式の統計を監視する手順は次のとおりです。
SQL*Plusを起動し、ユーザーshとしてデータベースに接続します。
統計の拡張の名前と定義を問い合せます。
たとえば、次の問合せを実行します。
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"))
個別値の数を問い合せ、式に対してヒストグラムが作成済かどうかを判別します。
たとえば、次の問合せを実行します。
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リファレンス』を参照してください。
DBMS_STATS.DROP_EXTENDED_STATSファンクションを使用して、表から列グループを削除します。
前提条件
このチュートリアルでは、次のことが前提となっています。
LOWER(cust_state_province)式の拡張統計が作成されています。
式の統計を削除します。
式の統計を削除する手順は次のとおりです。
SQL*Plusを起動し、ユーザーshとしてデータベースに接続します。
列グループを削除します。
たとえば、次の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パッケージおよびタイプ・リファレンス』を参照してください。
統計はロックして変更されないようにできます。統計をロックすると、ロックを解除するまで統計を変更できません。
統計および生成される計画が変化しないことを保証する必要がある場合に、ロック・プロシージャは静的環境において役に立ちます。たとえば、非常に変化しやすい表など、ある表またはスキーマで、DBMS_STATS_JOBプロセスによって新しい統計が収集されないようにすることが必要な場合もあります。
表の統計をロックすると、依存するすべての統計がロックされます。ロックされる統計には、表の統計、列の統計、ヒストグラムおよび依存する索引統計が含まれます。ロックされている統計を上書きするには、DELETE_*_STATSおよびRESTORE_*_STATSをtrueに設定するなど、様々なDBMS_STATSプロシージャのFORCE引数の値を設定できます。
DBMS_STATSパッケージには、LOCK_SCHEMA_STATSおよびLOCK_TABLE_STATSという、統計をロックするための2つのプロシージャが用意されています。
前提条件
このチュートリアルでは、次のことが前提となっています。
oe.orders表およびhrスキーマの統計が収集済です。
oe.orders表の統計とhrスキーマの統計が変更されないようにします。
統計をロックする手順は次のとおりです。
SQL*Plusを起動し、oeユーザーとしてデータベースに接続します。
oe.ordersの統計をロックします。
たとえば、次のPL/SQLプログラムを実行します。
BEGIN
DBMS_STATS.LOCK_TABLE_STATS('OE','ORDERS');
END;
/
hrユーザーとしてデータベースに接続します。
hrスキーマ内の統計をロックします。
たとえば、次のPL/SQLプログラムを実行します。
BEGIN
DBMS_STATS.LOCK_SCHEMA_STATS('HR');
END;
/
関連項目:
DBMS_STATS.LOCK_TABLE_STATSプロシージャの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
DBMS_STATSパッケージには、UNLOCK_SCHEMA_STATSおよびUNLOCK_TABLE_STATSという、統計をロック解除するための2つのプロシージャが用意されています。
前提条件
このチュートリアルでは、次のことが前提となっています。
oe.orders表およびhrスキーマの統計がロックされています。
これらの統計をロック解除します。
統計をロック解除する手順は次のとおりです。
SQL*Plusを起動し、oeユーザーとしてデータベースに接続します。
oe.ordersの統計をロック解除します。
たとえば、次のPL/SQLプログラムを実行します。
BEGIN
DBMS_STATS.UNLOCK_TABLE_STATS('OE','ORDERS');
END;
/
hrユーザーとしてデータベースに接続します。
hrスキーマ内の統計をロック解除します。
たとえば、次のPL/SQLプログラムを実行します。
BEGIN
DBMS_STATS.UNLOCK_SCHEMA_STATS('HR');
END;
/
関連項目:
DBMS_STATS.UNLOCK_TABLE_STATSプロシージャの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
この項の内容は次のとおりです。
データ・ディクショナリ内の統計を変更する場合は常に、データベースで古いバージョンの統計が自動的に保存されます。新しく収集した統計によって最適ではない実行計画が作成される場合は、以前の統計に戻すことが必要な場合もあります。
オプティマイザ統計をリストアすることが、最適ではない計画のトラブルシューティングに役立つ可能性があります。次の図は、統計のリストアを時系列で示しています。このグラフでは、統計の収集が8月10日と8月20日に行われます。8月24日に、現行の統計がオプティマイザで最適ではない計画が生成される原因となっている可能性があるとDBAで判定されています。8月25日に、管理者は8月10日に収集された統計をリストアしています。
統計のリストアは、統計のインポートおよびエクスポートに類似しています。次のような状況では通常、統計のエクスポートではなく統計のリストアを行います。
統計の古いバージョンをリカバリする場合。たとえば、オプティマイザ動作を以前の日付までリストアすることが必要な場合があります。
データベースで統計履歴の保存および消去を管理する場合。
次のような場合は、統計をリストアするのではなく、エクスポートします。
複数の統計セットを試験的に使用して値を増減させる場合。
データベース間で統計を移動する場合。たとえば、本番システムからテスト・システムに統計を移動する場合などです。
既知の統計セットを統計のリストアに必要な保存日数よりも長期間にわたって保存する場合。
関連項目:
統計をリストアおよびインポートするためのプロシージャの概要については、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
前のバージョンの統計をリストアする場合は、次の制限が適用されます。
DBMS_STATS.RESTORE_*_STATSプロシージャでは、ユーザー定義統計はリストアできません。
統計の収集にANALYZEコマンドが使用された場合、古いバージョンの統計は格納されません。
表を削除すると、自動ヒストグラム収集機能が使用するワークロード情報と、RESTORE_*_STATSプロシージャが使用する保存された統計履歴が消失します。このデータなしでは、これらの機能は適切に動作しません。表からすべての行を削除し、DBMS_STATSを使用してこれらの統計をリストアするには、同じ表を削除して再作成するかわりにTRUNCATEを使用します。
DBMS_STATS.RESTORE_*_STATSプロシージャを使用して統計をリストアできます。表13-5にリストされたプロシージャでは、引数としてタイムスタンプを受け入れ、指定された日時(as_of_timestamp)の統計をリストアします。
表13-5 DBMS_STATSリストア・プロシージャ
| プロシージャ | 説明 |
|---|---|
|
指定されたタイムスタンプですべてのディクショナリ表( |
|
指定されたタイムスタンプですべての固定表の統計をリストアします。 |
|
指定されたタイムスタンプでスキーマのすべての表の統計情報をリストアします。 |
|
指定されたタイムスタンプのシステム統計をリストアします。 |
|
指定されたタイムスタンプの表の統計をリストアします。また、このプロシージャでは、関連付けられた索引と列の統計がリストアされます。指定されたタイムスタンプで表の統計がロックされていた場合は、プロシージャでその統計がロックされます。 |
ディクショナリ・ビューには統計の変更日時が表示されています。次のビューを使用して、リストア操作に使用するタイムスタンプを決定できます。
DBA_OPTSTAT_OPERATIONSビューには、DBMS_STATSを使用してスキーマ・レベルとデータベース・レベルで実行された統計操作の履歴が含まれます。
DBA_TAB_STATS_HISTORYビューには表の統計の変更履歴が含まれます。
前提条件
このチュートリアルでは、次のことが前提となっています。
oe.orders表に最新の統計を収集した後に、オプティマイザは、その表の問合せに最適ではない計画の選択を開始しました。
計画が改善されたかどうかを確認するために、最新の統計収集より前の統計をリストアします。
オプティマイザ統計をリストアする手順は次のとおりです。
SQL*Plusを起動し、管理者権限でデータベースに接続します。
oe.ordersの統計履歴を問い合せます。
たとえば、次の問合せを実行します。
COL TABLE_NAME FORMAT a10
SELECT TABLE_NAME,
TO_CHAR(STATS_UPDATE_TIME,'YYYY-MM-DD:HH24:MI:SS') AS STATS_MOD_TIME
FROM DBA_TAB_STATS_HISTORY
WHERE TABLE_NAME='ORDERS'
AND OWNER='OE'
ORDER BY STATS_UPDATE_TIME DESC;
次に出力の例を示します。
TABLE_NAME STATS_MOD_TIME ---------- ------------------- ORDERS 2012-08-20:11:36:38 ORDERS 2012-08-10:11:06:20
オプティマイザ統計を以前の変更時にリストアします。
たとえば、oe.orders表の統計を2012年8月10日にリストアします。
BEGIN
DBMS_STATS.RESTORE_TABLE_STATS( 'OE','ORDERS',
TO_TIMESTAMP('2012-08-10:11:06:20','YYYY-MM-DD:HH24:MI:SS') );
END;
/
DBMS_STATSによって指定された日時の統計がリストアされるので、8/10から8/20までの間で任意の日付を指定できます。
関連項目:
DBMS_STATS.RESTORE_TABLE_STATSプロシージャの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
デフォルトでは、データベースは、31日間オプティマイザ統計を保存し、その日数より後の統計は消去するようにスケジュールされています。DBMS_STATSパッケージを使用して、保存期間を決定したり、保存期間を変更したり、古い統計を手動で消去したりすることができます。
この項の内容は次のとおりです。
DBMS_STATSプロシージャを使用して、オプティマイザ統計の履歴情報を取得できます。この情報は、データベースでオプティマイザ統計を保存する期間やその後に統計のリストアが可能な期間を決定する必要がある場合に役に立ちます。
オプティマイザ統計の履歴に関する情報を取得するには次のプロシージャを使用できます。
GET_STATS_HISTORY_RETENTION
このファンクションでは、現行の統計履歴の保存値を取得できます。
GET_STATS_HISTORY_AVAILABILITY
このファンクションでは、統計履歴が使用可能な場合に最も古いタイムスタンプを取得します。ユーザーが統計情報をリストアする場合、最も古いタイムスタンプよりさらに前のタイムスタンプにはできません。
オプティマイザ統計の履歴情報を取得する手順は次のとおりです。
SQL*Plusを起動し、必要な権限でデータベースに接続します。
次のPL/SQLプログラムを実行します。
DECLARE
v_stats_retn NUMBER;
v_stats_date DATE;
BEGIN
v_stats_retn := DBMS_STATS.GET_STATS_HISTORY_RETENTION;
DBMS_OUTPUT.PUT_LINE('The retention setting is ' || v_stats_retn || '.');
v_stats_date := DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY;
DBMS_OUTPUT.PUT_LINE('Earliest restore date is ' || v_stats_date || '.');
END;
/
関連項目:
DBMS_STATS.GET_STATS_HISTORY_RETENTIONプロシージャの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
デフォルトでは、データベースは、31日間オプティマイザ統計を保存します。DBMS_STATS.ALTER_STATS_HISTORY_RETENTIONプロシージャを使用して保存期間を構成できます。
前提条件
このプロシージャを実行するには、SYSDBA権限、またはANALYZE ANY DICTIONARYとANALYZE ANYの両方のシステム権限のいずれかを所有している必要があります。
前提条件
このチュートリアルでは、次のことが前提となっています。
オプティマイザ統計の現行の保存期間は31日です。
年次報告書の一環として年に1回問合せを実行します。(現在、最適ではない計画が作成されている場合に)前年の計画にアクセスできるように、統計履歴を366日以上保持しておくには、保存期間を366日に設定します。
オプティマイザ統計の保存期間の変更に使用できるPL/SQLプロシージャset_opt_stats_retentionを作成します。
オプティマイザ統計の保存期間を変更する手順は次のとおりです。
SQL*Plusを起動し、必要な権限でデータベースに接続します。
保存期間を変更するプロシージャを作成します。
たとえば、次のプロシージャを作成します。
CREATE OR REPLACE PROCEDURE set_opt_stats_retention
( p_stats_retn IN NUMBER )
IS
v_stats_retn NUMBER;
BEGIN
v_stats_retn := DBMS_STATS.GET_STATS_HISTORY_RETENTION;
DBMS_OUTPUT.PUT_LINE('Old retention setting is ' ||v_stats_retn|| '.');
DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(p_stats_retn);
v_stats_retn := DBMS_STATS.GET_STATS_HISTORY_RETENTION;
DBMS_OUTPUT.PUT_LINE('New retention setting is ' ||v_stats_retn|| '.');
END;
/
保存期間を366日に変更します。
たとえば、前の手順で作成したプロシージャを実行します(出力例も示します)。
SQL> EXECUTE set_opt_stats_retention(366) The old retention setting is 31. The new retention setting is 366. PL/SQL procedure successfully completed.
関連項目:
DBMS_STATS.ALTER_STATS_HISTORY_RETENTIONプロシージャの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
自動消去は、STATISTICS_LEVEL初期化パラメータをTYPICALまたはALLに設定した場合に有効になります。(現在の時間 - ALTER_STATS_HISTORY_RETENTION設定)と(最新の統計情報収集の時間 - 1)よりも古い履歴はすべて消去されます。
PURGE_STATSプロシージャを使用すると、古い統計を手動で消去できます。引数を指定しない場合、このプロシージャでは、自動消去ポリシーが使用されます。before_timestampパラメータを指定する場合は、指定されたタイムスタンプより前に保存された統計がデータベースで消去されます。
前提条件
このプロシージャを実行するには、SYSDBA権限、またはANALYZE ANY DICTIONARYとANALYZE ANYの両方のシステム権限のいずれかを所有している必要があります。
前提条件
このチュートリアルでは、1週間より前の統計を消去するものとします。
オプティマイザ統計を消去する手順は次のとおりです。
SQL*Plusを起動し、必要な権限でデータベースに接続します。
DBMS_STATS.PURGE_STATSプロシージャを実行します。
たとえば、プロシージャを次のように実行します。
EXEC DBMS_STATS.PURGE_STATS( SYSDATE-7 );
関連項目:
DBMS_STATS.PURGE_STATSプロシージャの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
データ・ディクショナリとユーザー定義の統計表間でオプティマイザ統計をエクスポートおよびインポートできます。また、データベース間で統計のコピーもできます。
インポートとエクスポートは、本番統計を使用したアプリケーションのテストを行う場合に特に役立ちます。DBMS_STATSを使用して本番データベースからテスト・データベースにスキーマの統計をエクスポートすることで、開発者は、アプリケーションのデプロイ前に実行計画を実際の環境に合せることができます。
データベース間でオプティマイザ統計を転送する場合は、DBMS_STATSを使用してステージング表間で統計をコピーし、ツールを使用して表のコンテンツに宛先データベースがアクセスできるようにする必要があります。
インポートとエクスポートは、本番統計を使用したアプリケーションのテストを行う場合に特に役立ちます。DBMS_STATS.EXPORT_SCHEMA_STATSを使用して本番データベースからテスト・データベースにスキーマの統計をエクスポートすることで、開発者は、アプリケーションのデプロイ前に実行計画を実際の環境に合せることができます。
次の図は、Oracle Data Pumpおよびftpを使用したプロセスを示しています。
図13-5に示すとおり、基本的な手順は次のようになります。
本番データベースで、DBMS_STATS.EXPORT_SCHEMA_STATSを使用してデータ・ディクショナリからステージング表に統計をコピーします。
Oracle Data Pumpを使用してステージング表から.dmpファイルに統計をエクスポートします。
ftpなどの転送ツールを使用して本番ホストからテスト・ホストに.dmpファイルを転送します。
テスト・データベースで、Oracle Data Pumpを使用して.dmpファイルからステージング表に統計をインポートします。
DBMS_STATS.IMPORT_SCHEMA_STATSを使用してステージング表からデータ・ディクショナリに統計をコピーします。
DBMS_STATS.EXPORT_SCHEMA_STATSプロシージャを使用して統計を転送します。
前提条件と制限
オプティマイザ統計のエクスポートの準備をする際は、次のことに注意してください。
統計をエクスポートする前に、統計を保持するための表を作成する必要があります。統計表は、DBMS_STATS.CREATE_STAT_TABLEプロシージャで作成します。
オプティマイザでは、ユーザー所有の表に格納された統計は使用されません。オプティマイザで使用されるのは、データ・ディクショナリに格納されている統計のみです。オプティマイザにユーザー定義の表の統計を使用させるには、DBMS_STATSインポート・プロシージャを使用してデータ・ディクショナリにこれらの統計をインポートします。
データ・ポンプ・エクスポートとインポート・ユーティリティは、データベースから表とともにオプティマイザ統計をエクスポートおよびインポートします。列にシステム生成の名前が付けられている場合、元のエクスポート(exp)では統計をデータとともにエクスポートできませんが、この制限はデータ・ポンプのエクスポートには適用されません。
注意:
DBMS_STATSを使用した統計のエクスポートおよびインポートは、データ・ポンプ・エクスポートおよびインポートの使用とは異なる操作になります。
前提条件
このチュートリアルでは、次のことが前提となっています。
本番データベースで代表的なshスキーマの統計を生成し、DBMS_STATSを使用してそれらをテスト・データベースにインポートします。
管理ユーザーのdba1は、本番データベースとテスト・データベースの両方に存在します。
opt_stats表を作成してスキーマ統計を格納します。
Oracle Data Pumpを使用してopt_stats表をエクスポートおよびインポートします。
スキーマ統計を生成してそれらを別のデータベースにインポートする手順は次のとおりです。
本番ホストで、SQL*Plusを起動して、管理者dba1として本番データベースに接続します。
本番統計を保持するための表を作成します。
たとえば、次のPL/SQLプログラムを実行して、ユーザー統計表opt_statsを作成します。
BEGIN
DBMS_STATS.CREATE_STAT_TABLE (
ownname => 'dba1'
, stattab => 'opt_stats'
);
END;
/
スキーマ統計を収集します。
たとえば、スキーマ統計を次のように手動で収集します。
-- generate representative workload
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SH');
DBMS_STATSを使用して統計をエクスポートします。
たとえば、スキーマ統計を取得してそれらを以前に作成したopt_stats表に格納します。
BEGIN
DBMS_STATS.EXPORT_SCHEMA_STATS (
ownname => 'dba1'
, stattab => 'opt_stats'
);
END;
/
Oracle Data Pumpを使用して、統計表のコンテンツをエクスポートします。
たとえば、オペレーティング・スキーマ・プロンプトでexpdpコマンドを実行します。
expdp dba1 DIRECTORY=dpump_dir1 DUMPFILE=stat.dmp TABLES=opt_stats
dumpファイルをテスト・データベースのホストに転送します。
テスト・ホストにログインし、Oracle Data Pumpを使用して統計表のコンテンツをインポートします。
たとえば、オペレーティング・スキーマ・プロンプトでimpdpコマンドを実行します。
impdp dba1 DIRECTORY=dpump_dir1 DUMPFILE=stat.dmp TABLES=opt_stats
テスト・ホストで、SQL*Plusを起動して、管理者dba1としてテスト・データベースに接続します。
DBMS_STATSを使用してユーザー統計表から統計をインポートし、それらをデータ・ディクショナリに格納します。
次のPL/SQLプログラムでは、opt_stats表からデータ・ディクショナリにスキーマ統計をインポートします。
BEGIN
DBMS_STATS.IMPORT_SCHEMA_STATS(
ownname => 'dba1'
, stattab => 'opt_stats'
);
END;
/
関連項目:
DBMS_STATS.CREATE_STAT_TABLEファンクションの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
統計転送機能の概要は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
Oracle Data Pumpの詳細は、『Oracle Databaseユーティリティ』を参照してください。
DBMS_STATS統計収集プロシージャをレポート作成モードで実行できます。この場合、オプティマイザは、実際には統計を収集せずに、指定された統計収集関数を使用した場合に処理されるオブジェクトをレポートします。
表13-6には、DBMS_STATS.REPORT_GATHER_*_STATSファンクションがリストされています。追加パラメータのdetail_levelとformatを使用すると、すべてのファンクションで、入力パラメータが、対応するGATHER_*_STATSプロシージャと同じものになります。サポートされている形式は、XML、HTMLおよびTEXTです。
表13-6 DBMS_STATSレポート作成モード・ファンクション
| ファンクション | 説明 |
|---|---|
|
レポート作成モードで |
|
レポート作成モードで |
|
レポート作成モードで |
|
レポート作成モードで |
|
レポート作成モードで |
|
レポート作成モードで自動統計収集ジョブを実行します。このプロシージャでは実際に統計は収集されませんが、ジョブを実行することで影響を受ける可能性があるすべてのオブジェクトがレポートされます。 |
前提条件
このチュートリアルでは、oeスキーマのGATHER_SCHEMA_STATSを実行することで影響を受ける可能性があるオブジェクトのHTMLレポートを生成するものとします。
GATHER_SCHEMA_STATSの実行によって影響を受けるオブジェクトのレポートを作成する手順は次のとおりです。
SQL*Plusを起動し、管理者権限でデータベースに接続します。
DBMS_STATS.REPORT_GATHER_SCHEMA_STATSファンクションを実行します。
たとえば、SQL*Plusで次のコマンドを実行します。
SET LINES 200 PAGES 0
SET LONG 100000
COLUMN REPORT FORMAT A200
VARIABLE my_report CLOB;
BEGIN
:my_report :=DBMS_STATS.REPORT_GATHER_SCHEMA_STATS(
ownname => 'OE' ,
detail_level => 'TYPICAL' ,
format => 'HTML' );
END;
/
次の図は、サンプル・レポートの一部を示しています。
関連項目:
DBMS_STATSの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
DBMS_STATS関数を使用して、特定の統計収集操作または特定の日時の間に発生した操作のレポートを作成できます。
表13-7にはファンクションがリストされています。
表13-7 DBMS_STATSレポート作成ファンクション
| ファンクション | 説明 |
|---|---|
|
2つの時点間で発生したすべての統計操作のレポートを生成します。自動統計収集の実行のみを含めることで、レポートの範囲を絞り込むことができます。また、プラガブル・データベース(PDB)IDのセットを指定することで、指定されたPDBの統計操作のみのレポートがデータベースで作成されます。 |
|
指定された操作のレポートを生成します。オプションで、コンテナ・データベース(CDB)内の特定のPDB IDを指定できます。 |
前提条件
このチュートリアルでは、次のHTMLレポートを生成するものとします。
過去1日以内のすべての統計収集操作
最新の統計収集操作
過去1日のすべての操作のレポートを作成する手順は次のとおりです。
SQL*Plusを起動し、管理者権限でデータベースに接続します。
DBMS_STATS.REPORT_STATS_OPERATIONSファンクションを実行します。
たとえば、次のコマンドを実行します。
SET LINES 200 PAGES 0
SET LONG 100000
COLUMN REPORT FORMAT A200
VARIABLE my_report CLOB;
BEGIN
:my_report := DBMS_STATS.REPORT_STATS_OPERATIONS (
since => SYSDATE-1
, until => SYSDATE
, detail_level => 'TYPICAL'
, format => 'HTML'
);
END;
/
次の図は、サンプル・レポートを示しています。
個々の操作についてDBMS_STATS.REPORT_SINGLE_STATS_OPERATIONファンクションを実行します。
たとえば、次のプログラムを実行して、操作848のレポートを生成します。
BEGIN
:my_report :=DBMS_STATS.REPORT_SINGLE_STATS_OPERATION (
OPID => 848
, FORMAT => 'HTML'
);
END;
次の図は、サンプル・レポートを示しています。
関連項目:
統計管理用Cloud Control GUIの詳細は、「オプティマイザ統計管理のグラフィカル・インタフェース」を参照してください
DBMS_STATSの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
SQL計画ディレクティブは、データベースによって自動的に管理されます。ディレクティブは、53週間使用されない場合、自動的に消去されます。
DBMS_SPDのプロシージャおよびファンクションを使用してディレクティブを手動で管理できます。
表13-8に、一般的に使用されるプロシージャおよびファンクションの一部を示します。DBMS_SPDパッケージの完全な構文およびセマンティクスは、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
表13-8 DBMS_SPDのプロシージャ
| プロシージャ | 説明 |
|---|---|
|
ディレクティブをメモリーから |
|
SQL計画ディレクティブを削除します。 |
前提条件
DBMS_SPDのAPIを実行するには、ADMINISTER SQL MANAGEMENT OBJECT権限が必要です。
前提条件
このチュートリアルでは、次の操作を行うものとします。
shスキーマのすべてのディレクティブを永続記憶域に書き込みます。
shスキーマのすべてのディレクティブを削除します。
shスキーマのすべての計画ディレクティブを書き込んでから削除する手順は次のとおりです。
SQL*Plusを起動し、管理者権限でデータベースにログインします。
SQL計画ディレクティブを強制的にディスクに書き込みます。
たとえば、次のDBMS_SPDプログラムを実行します。
BEGIN DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE; END; /
データ・ディクショナリを問い合せて、shスキーマの既存のディレクティブに関する情報を確認します。
例13-1では、ディレクティブに関する情報をデータ・ディクショナリに問い合せます。
shスキーマの既存のSQL計画ディレクティブを削除します。
次のPL/SQLプログラム・ユニットでは、ID 1484026771529551585を指定してSQL計画ディレクティブを削除します。
BEGIN DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE ( directive_id => 1484026771529551585 ); END; /
例13-1 shスキーマのディレクティブの表示
SELECT TO_CHAR(d.DIRECTIVE_ID) dir_id, o.OWNER, o.OBJECT_NAME,
o.SUBOBJECT_NAME col_name, o.OBJECT_TYPE, d.TYPE, d.STATE, d.REASON
FROM DBA_SQL_PLAN_DIRECTIVES d, DBA_SQL_PLAN_DIR_OBJECTS o
WHERE d.DIRECTIVE_ID=o.DIRECTIVE_ID
AND o.OWNER IN ('SH')
ORDER BY 1,2,3,4,5;
DIR_ID OWN OBJECT_NA COL_NAME OBJECT TYPE STATE REASON
------------------- --- --------- ---------- ------- -------- ---------- ------------
1484026771529551585 SH CUSTOMERS COUNTRY_ID COLUMN DYNAMIC_ SUPERSEDED SINGLE TABLE
SAMPLING CARDINALITY
MISESTIMATE
1484026771529551585 SH CUSTOMERS CUST_STATE_ COLUMN DYNAMIC_ SUPERSEDED SINGLE TABLE
PROVINCE SAMPLING CARDINALITY
MISESTIMATE
1484026771529551585 SH CUSTOMERS TABLE DYNAMIC_ SUPERSEDED SINGLE TABLE
SAMPLING CARDINALITY
MISESTIMATE
関連項目:
DBA_SQL_PLAN_DIRECTIVESの詳細は、『Oracle Databaseリファレンス』を参照してください。
DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVEプロシージャの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
