15 オプティマイザ統計の使用の制御
DBMS_STATSを使用すると、オプティマイザによる統計の使用時期および使用方法を指定できます。
この項では、次の項目について説明します。
15.1 オプティマイザ統計のロックおよびロック解除
統計はロックして変更されないようにできます。
統計をロックすると、ロックを解除するまで統計を変更できません。統計および生成される計画が変化しないことを保証する必要がある場合に、ロック・プロシージャは静的環境において役に立ちます。たとえば、非常に変化しやすい表など、ある表またはスキーマで、DBMS_STATS_JOBプロセスによって新しい統計が収集されないようにすることが必要な場合もあります。
表の統計をロックすると、依存するすべての統計がロックされます。ロックされる統計には、表の統計、列の統計、ヒストグラムおよび依存する索引統計が含まれます。ロックされている統計を上書きするには、DELETE_*_STATSおよびRESTORE_*_STATSをtrueに設定するなど、様々なDBMS_STATSプロシージャのFORCE引数の値を設定できます。
この項では、次の項目について説明します。
15.1.1 統計のロック
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パッケージおよびタイプ・リファレンスを参照してください
15.1.2 統計のロック解除
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パッケージおよびタイプ・リファレンスを参照してください
15.2 保留中のオプティマイザ統計の公開
デフォルトでは、統計収集が終了すると、データベースで統計が自動的に公開されます。
その一方で、保留中の統計を使用してその統計を保存することで、収集後すぐに公開されないようにできます。この手法は、保留中の統計を使用してセッション内の問合せをテストする際に役立ちます。テストの結果が十分であれば、その統計を公開してデータベース全体で使用することができます。
この項では、次の項目について説明します。
15.2.1 保留中のオプティマイザ統計について
データベースでは、公開された統計と同様に、データ・ディクショナリに保留中の統計が格納されています。
デフォルトでは、オプティマイザは公開された統計を使用します。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リファレンスを参照してください
15.2.2 オプティマイザ統計の公開のためのユーザー・インタフェース
DBMS_STATSパッケージを使用して、統計の公開に関する操作を実行できます。
次の表に、関連するプログラム・ユニットを示します。
表15-1 オプティマイザ統計の公開に関連するDBMS_STATSプログラム・ユニット
| プログラム・ユニット | 説明 |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
すべてのオブジェクトまたは特定のオブジェクトのみの有効な保留中の統計を公開します。 |
|
|
保留中の統計を削除します。 |
|
|
保留中の統計をエクスポートします。 |
OPTIMIZER_USE_PENDING_STATISTICS初期化パラメータでは、データベースで保留中の統計が使用可能な場合は、その統計を使用するかどうかが決定されます。デフォルト値はfalseで、オプティマイザが公開された統計のみを使用することを意味します。trueに設定した場合は、オプティマイザが既存の保留中の統計を使用するように指定されます。このパラメータをデータベース・レベルではなく、セッション・レベルで設定することがベスト・プラクティスです。
データ・ディクショナリ・ビューでは、公開された統計に関するアクセス情報を使用できます。表15-2には、関連するビューがリストされています。
表15-2 オプティマイザ統計の公開に関連するビュー
| ビュー | 説明 |
|---|---|
|
|
現行ユーザーがアクセス可能な表のオプティマイザ統計を表示します。 |
|
|
|
|
|
現行のユーザーが所有する表パーティションの列統計およびヒストグラム情報を示します。 |
|
|
現行のユーザーが所有するパーティション・オブジェクトのサブパーティションの列統計およびヒストグラム情報を示します。 |
|
|
現行ユーザーがアクセス可能な索引のオプティマイザ統計を表示します。 |
|
|
現行ユーザーがアクセス可能な表、パーティションおよびサブパーティションの保留中の統計を示します。 |
|
|
現行ユーザーがアクセス可能な列の保留中の統計を示します。 |
|
|
現行ユーザーがアクセス可能な表、パーティションおよびサブパーティションの保留中の統計で、 |
関連項目:
-
DBMS_STATSパッケージについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください -
USER_TAB_PENDING_STATSおよび関連するビューについて学習するには、Oracle Databaseリファレンスを参照してください
15.2.3 公開された統計と保留中の統計の管理
この項では、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.example.icom' 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; /
15.3 テスト用の人為的なオプティマイザ統計の作成
テスト目的でオプティマイザにユーザー作成統計を提供するため、DBMS_STATS.SET_*_STATSプロシージャを使用できます。これらのプロシージャは、オプティマイザに指定された統計の人為的な値を提供します。
この項では、次の項目について説明します。
15.3.1 人為的なオプティマイザ統計について
テスト目的のため、DBMS_STATS.SET_*_STATSプロシージャを使用して表、索引またはシステムの人為的な統計を手動で作成できます。
stattabがnullの場合、DBMS_STATS.SET_*_STATSプロシージャは、人為的な統計をデータ・ディクショナリに直接挿入します。また、ユーザーが作成した表を指定することもできます。
注意:
DBMS_STATS.SET_*_STATSプロシージャは、開発テストのみを目的としています。本番データベースで使用しないでください。データ・ディクショナリで統計を設定する場合、Oracle Databaseでは、設定された統計を実際の統計とみなします。これは、失効の基準を満たしていない場合に統計収集ジョブが人為的な統計を再収集しない可能性があることを意味します。
DBMS_STATS.SET_*_STATSプロシージャの一般的なユース・ケースは、次のとおりです。
-
表の行またはブロックの数が変更される場合の実行計画の変更方法の表示
たとえば、
SET_TABLE_STATSでは、小さいまたは空の表の行およびブロックの数を大きい数に設定できます。変更された統計を使用して問合せを実行すると、オプティマイザが実行計画を変更する可能性があります。たとえば、行カウントを増やすと、オプティマイザが全表スキャンではなく索引スキャンを選択する可能性があります。様々な値をテストすると、オプティマイザが一定期間で実行計画を変更する方法を確認できます。 -
一時表の実際の統計の作成
大きい一時表が複数のSQL文で参照されるときにオプティマイザが実行する内容を確認することが必要になる場合があります。通常の表を作成して代表的なデータをロードし、
GET_TABLE_STATSを使用して統計を取得できます。一時表を作成した後、SET_TABLE_STATSを呼び出してこれらの統計の使用をオプティマイザに通知できます。
オプションで、ユーザー作成表の統計の一意のIDを指定できます。SET_*_STATSプロシージャには、対応するGET_*_STATSプロシージャがあります。
表15-3 オプティマイザ統計設定のDBMS_STATSプロシージャ
| DBMS_STATSプロシージャ | 説明 |
|---|---|
SET_TABLE_STATS |
numrows、numblks、avgrlenなどのパラメータを使用して、表統計またはパーティション統計を設定します。
データベースがインメモリー列ストアを使用する場合、 オプティマイザは、キャッシュ・データを使用して、索引または統計表にアクセスするためのキャッシュ・ブロック数を見積ります。合計コストは、ディスクのデータ・ブロック読込み時のI/Oコストに、バッファ・キャッシュ内のキャッシュ・ブロック読取り時のCPUコスト、さらにデータ処理にかかるCPUコストを組み合せた合計値です。 |
SET_COLUMN_STATS |
distcnt、density、nullcntなどのパラメータを使用して列統計を設定します。
ユーザー定義統計を処理するこのプロシージャのバージョンでは、 |
SET_SYSTEM_STATS |
iotfrspeed、sreadtim、cpuspeedなどのパラメータを使用して、システム統計を設定します。
|
SET_INDEX_STATS |
numrows、numlblks、avglblk、clstfct、indlevelなどのパラメータを使用して、索引統計を設定します。
ユーザー定義統計を処理するこのプロシージャのバージョンでは、 |
関連項目:
オプティマイザ統計の設定に関するDBMS_STATS.SET_TABLE_STATSなどのプロシージャについてさらに学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
15.3.2 表の人為的なオプティマイザ統計の設定
このトピックでは、DBMS_STATS.SET_TABLE_STATSを使用して表の人為的な統計の設定方法について説明します。基本ステップは、SET_INDEX_STATSおよびSET_SYSTEM_STATSと同じです。
-
SYSが所有していないオブジェクトの場合、オブジェクトの所有者であるか、ANALYZE ANY権限を持つ必要があります。 -
SYSが所有するオブジェクトの場合、ANALYZE ANY DICTIONARY権限またはSYSDBA権限を持つ必要があります。 -
表、列または索引の
GET_*_STATSを呼び出す場合、参照されるオブジェクトが存在する必要があります。
-
指定された表に
DBMS_STATS.SET_TABLE_STATSを使用するために必要な権限があります。 -
データ・ディクショナリに統計を格納します。
15.3.3 オプティマイザ統計の設定: 例
この例では、表のオプティマイザ統計の収集方法、人為的な統計の設定方法およびオプティマイザが異なる統計に基づいて選択する計画の比較方法を示します。
-
DBA権限を持つユーザーとしてデータベースにログインします。
-
オプティマイザが索引スキャンを選択する時期をテストします。
-
contractorsという表を作成し、salary列を索引付けします。CREATE TABLE contractors ( con_id NUMBER, last_name VARCHAR2(50), salary NUMBER, CONSTRAINT cond_id_pk PRIMARY KEY(con_id) ); CREATE INDEX salary_ix ON contractors(salary); -
単一行をこの表に挿入します。
INSERT INTO contractors VALUES (8, 'JONES',1000); COMMIT; -
表の統計を収集します。
EXECUTE DBMS_STATS.GATHER_TABLE_STATS( user, tabname => 'CONTRACTORS' ); -
表および索引の行数を問い合せます(出力例も示します)。
SQL> SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME = 'CONTRACTORS'; NUM_ROWS ---------- 1 SQL> SELECT NUM_ROWS FROM USER_INDEXES WHERE INDEX_NAME = 'SALARY_IX'; NUM_ROWS ---------- 1 -
dynamic_samplingヒントを使用して動的サンプリングを無効化し、給与が1000である契約者を問い合せます。SELECT /*+ dynamic_sampling(contractors 0) */ * FROM contractors WHERE salary = 1000; -
オプティマイザが選択した実行計画を問い合せます(出力例も示します)。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR); SQL_ID cy0wzytc16g9n, child number 0 ------------------------------------- SELECT /*+ dynamic_sampling(contractors 0) */ * FROM contractors WHERE salary = 1000 Plan hash value: 5038823 ---------------------------------------------------------------------- | Id | Operation | Name |Rows|Bytes|Cost (%CPU)| Time| ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | |* 1 | TABLE ACCESS FULL| CONTRACTORS | 1 | 12 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("SALARY"=1000) 19 rows selected.1行のみが表に存在するため、オプティマイザは、索引レンジ・スキャンよりも全表スキャンを選択します。
-
SET_TABLE_STATSおよびSET_INDEX_STATSを使用して、10個のデータ・ブロックに格納される2000行の表の統計をシミュレートします。BEGIN DBMS_STATS.SET_TABLE_STATS( ownname => user , tabname => 'CONTRACTORS' , numrows => 2000 , numblks => 10 ); END; / BEGIN DBMS_STATS.SET_INDEX_STATS( ownname => user , indname => 'SALARY_IX' , numrows => 2000 ); END; / -
表および索引の行数を問い合せます(出力例も示します)。
SQL> SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME = 'CONTRACTORS'; NUM_ROWS ---------- 2000 SQL> SELECT NUM_ROWS FROM USER_INDEXES WHERE INDEX_NAME = 'SALARY_IX'; NUM_ROWS ---------- 2000これで、実際は1行のみが1つのブロックに存在しても、オプティマイザは10個のブロックに2000行を含む表を仮定します。
-
共有プールをフラッシュして計画の再利用の可能性を排除し、
contractorsの同じ問合せを実行します。ALTER SYSTEM FLUSH SHARED_POOL; SELECT /*+ dynamic_sampling(contractors 0) */ * FROM contractors WHERE salary = 1000; -
オプティマイザが選択した実行計画を人為的な統計に基づいて問い合せます(サンプル出力を含みます)。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR); SQL_ID cy0wzytc16g9n, child number 0 ------------------------------------- SELECT /*+ dynamic_sampling(contractors 0) */ * FROM contractors WHERE salary = 1000 Plan hash value: 996794789 --------------------------------------------------------------------------------- |Id| Operation | Name |Rows|Bytes|Cost(%CPU)|Time| --------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | | |3(100)| | | 1| TABLE ACCESS BY INDEX ROWID BATCHED|CONTRACTORS|2000|24000|3 (34)|00:00:01| |*2| INDEX RANGE SCAN |SALARY_IX |2000| |1 (0)|00:00:01| --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("SALARY"=1000) 20 rows selected.行数およびブロック配分について人為的に生成された統計に基づいて、オプティマイザは、よりコスト効率が高い索引レンジ・スキャンを検討します。