Oracle Databaseのオプティマイザ統計は、データベースおよびそのオブジェクトに関する詳細を表します。
この章の内容は次のとおりです。
オプティマイザのコスト・モデルは、問合せに関連するオブジェクトについて収集された統計および問合せが実行されるデータベースとホストに基づきます。
統計は、オプティマイザが各SQL文に対して最適な実行計画を選択するために不可欠のものです。
オプティマイザ統計には次のものがあります。
表統計
行数
ブロック数
行の平均長さ
列統計
列内の個別値(NDV)数
列内のNULL数
データ配分(ヒストグラム)
拡張統計
索引統計
リーフ・ブロック数
レベル数
索引クラスタ化係数
システム統計
I/Oパフォーマンスと使用率
CPUパフォーマンスと使用率
図10-1に示されているように、データベースでは、表、列、索引およびシステムのオプティマイザ統計がデータ・ディクショナリに格納されます。これらの統計は、データ・ディクショナリ・ビューを使用してアクセスできます。
注意:
オプティマイザ統計は、V$
ビューで参照可能なパフォーマンス統計とは異なるものです。
オプティマイザは、様々なタイプのデータベース・オブジェクトおよび様々な特性のデータベース環境で統計を収集します。
この項の内容は次のとおりです。
Oracle Databaseの表統計には、行およびブロックに関する情報が含まれます。
オプティマイザは、これらの統計を使用して、表スキャンおよび表結合のコストを判断します。DBMS_STATS
は、永続表と一時表の両方の統計を収集できます。
データベースは、永続表に関するすべての関連統計を追跡します。DBMS_STATS.GATHER_TABLE_STATS
は、永続表での統計の収集前にコミットされます。たとえば、DBA_TAB_STATISTICS
に格納された表統計は、次のものを追跡します。
行数および行の長さの平均
データベースでは、カーディナリティの判定の際、DBA_TAB_STATISTICSに格納された行カウントが使用されます。
データ・ブロック数
オプティマイザは、DB_FILE_MULTIBLOCK_READ_COUNT
初期化パラメータに指定されているデータ・ブロックの数を使用して、実表のアクセス・コストを判断します。
例10-1 表統計
この例では、sh.customers
表の一部の表統計を問い合せます。
sys@PROD> SELECT NUM_ROWS, AVG_ROW_LEN, BLOCKS, LAST_ANALYZED 2 FROM DBA_TAB_STATISTICS 3 WHERE OWNER='SH' 4 AND TABLE_NAME='CUSTOMERS'; NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANAL ---------- ----------- ---------- --------- 55500 181 1486 14-JUN-10
関連項目:
DBA_TAB_STATISTICS
ビューおよびDB_FILE_MULTIBLOCK_READ_COUNT
初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください
列統計では、列値およびデータ配分に関する情報が追跡されます。
オプティマイザは、列統計を使用して、正確なカーディナリティの見積りを生成し、索引の使用、結合順序、結合方法などについてより適切な決定を行います。たとえば、DBA_TAB_COL_STATISTICS
の統計は、次のものを追跡します。
個別値の数。
NULLの数
上限値および下限値
ヒストグラム関連情報
オプティマイザは拡張統計を使用できます。これは特殊な列統計です。これらの統計は、オプティマイザに列間の論理的な関係を伝える場合に役立ちます。
関連項目:
DBA_TAB_COL_STATISTICS
ビューの詳細は、『Oracle Databaseリファレンス』を参照してください
索引統計には、索引レベルの数、索引ブロックの数および索引とデータ・ブロックの関係に関する情報が含まれます。オプティマイザは、これらの統計を使用して、索引スキャンのコストを判断します。
DBA_IND_STATISTICS
ビューは索引統計を追跡します。
統計には次のものがあります。
レベル
BLEVEL
列には、ルート・ブロックからリーフ・ブロックに達するまでに必要なブロックの数が示されます。Bツリー索引には、検索用のブランチ・ブロックと、値を格納するリーフ・ブロックの2種類のブロックがあります。Bツリー索引の概要は、『Oracle Database概要』を参照してください。
個別キー
この列は、索引が付けられた個別値の数を追跡します。一意制約が定義され、かつNOT NULL
制約は定義されていない場合、この値は、NULL以外の値の数と等しくなります。
索引が付けられた各個別キーのリーフ・ブロックの平均数
索引が付けられた各個別キーによってポイントされたデータ・ブロックの平均数
関連項目:
DBA_IND_STATISTICS
ビューの詳細は、『Oracle Databaseリファレンス』を参照してください
例10-2 索引統計
この例では、sh.customers
表のcust_lname_ix
およびcustomers_pk
索引の一部の索引統計を問い合せます(出力例も示します)。
SELECT INDEX_NAME, BLEVEL, LEAF_BLOCKS AS "LEAFBLK", DISTINCT_KEYS AS "DIST_KEY", AVG_LEAF_BLOCKS_PER_KEY AS "LEAFBLK_PER_KEY", AVG_DATA_BLOCKS_PER_KEY AS "DATABLK_PER_KEY" FROM DBA_IND_STATISTICS WHERE OWNER = 'SH' AND INDEX_NAME IN ('CUST_LNAME_IX','CUSTOMERS_PK'); INDEX_NAME BLEVEL LEAFBLK DIST_KEY LEAFBLK_PER_KEY DATABLK_PER_KEY -------------- ------ ------- -------- --------------- --------------- CUSTOMERS_PK 1 115 55500 1 1 CUST_LNAME_IX 1 141 908 1 10
Bツリー索引では、索引クラスタ化係数により、姓などの索引値との関連で行の物理グループ化が測定されます。
索引クラスタ化係数は、オプティマイザが、特定の問合せで索引スキャンと全表スキャンのいずれがより効率的かを判断するのに役立ちます)。クラスタ化係数が低い場合は、索引スキャンが効率的であることを示しています。
表内のブロックの数に近いクラスタ化係数は、行が索引キーによって表ブロック内で物理的に順序付けられていることを示します。データベースが全表スキャンを実行した場合、データベースは、多くの場合、ディスク上に格納されているとおりに(行は索引キーによってソートされています)行を取得します。行の数に近いクラスタ化係数は、行が索引キーとの関連でデータベース・ブロックにランダムに分散されていることを示します。データベースが全表スキャンを実行する場合、データベースは、この索引キーによってソートされた順番では行を取得しません。
クラスタ化係数は、特定の索引のプロパティであって、表のプロパティではありません。表に複数の索引が存在する場合、ある索引のクラスタ化係数は小さく、別の索引のクラスタ化係数は大きい値になる可能性があります。表を再編成してある索引のクラスタ化係数を改善しようとすると、別の索引のクラスタ化係数が低下することがあります。
例10-3 索引クラスタ化係数
この例は、オプティマイザが、索引を使用するほうが全表スキャンよりも効果的であるかどうかを判断するために索引クラスタ化係数をどのように使用するかを示しています。
SQL*Plusを起動し、sh
としてデータベースに接続します。そして、sh.customers
表内の行とブロックの数を問い合せます(出力例も示します)。
SELECT table_name, num_rows, blocks FROM user_tables WHERE table_name='CUSTOMERS'; TABLE_NAME NUM_ROWS BLOCKS ------------------------------ ---------- ---------- CUSTOMERS 55500 1486
customers.cust_last_name
列で索引を作成します。
たとえば、次の文を実行します。
CREATE INDEX CUSTOMERS_LAST_NAME_IDX ON customers(cust_last_name);
新たに作成された索引の索引クラスタ化係数を問い合せます。
次の問合せは、クラスタ化係数が表内のブロック数よりもはるかに大きいため、customers_last_name_idx
索引のクラスタ化係数が高いことを示しています。
SELECT index_name, blevel, leaf_blocks, clustering_factor
FROM user_indexes
WHERE table_name='CUSTOMERS'
AND index_name= 'CUSTOMERS_LAST_NAME_IDX';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ----------- -----------------
CUSTOMERS_LAST_NAME_IDX 1 141 9859
customers
表の新しいコピーを作成し、cust_last_name
で行をソートします。
たとえば、次の文を実行します。
DROP TABLE customers3 PURGE; CREATE TABLE customers3 AS SELECT * FROM customers ORDER BY cust_last_name;
このcustomers3
表の統計情報データを収集します。
たとえば、GATHER_TABLE_STATS
プロシージャを次のように実行します。
EXEC DBMS_STATS.GATHER_TABLE_STATS(null,'CUSTOMERS3');
customers3
表内の行とブロックの数を問い合せます。
たとえば、次の問合せを入力します(出力例も示します)。
SELECT TABLE_NAME, NUM_ROWS, BLOCKS FROM USER_TABLES WHERE TABLE_NAME='CUSTOMERS3'; TABLE_NAME NUM_ROWS BLOCKS ------------------------------ ---------- ---------- CUSTOMERS3 55500 1485
customers3
のcust_last_name
列で索引を作成します。
たとえば、次の文を実行します。
CREATE INDEX CUSTOMERS3_LAST_NAME_IDX ON customers3(cust_last_name);
customers3_last_name_idx
索引の索引クラスタ化係数を問い合せます。
次の問合せは、customers3_last_name_idx
索引のクラスタ化係数がより低いことを示しています。
SELECT INDEX_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR
FROM USER_INDEXES
WHERE TABLE_NAME = 'CUSTOMERS3'
AND INDEX_NAME = 'CUSTOMERS3_LAST_NAME_IDX';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ----------- -----------------
CUSTOMERS3_LAST_NAME_IDX 1 141 1455
表customers3
には元のcustomers
表と同じデータがありますが、customers3
表内のデータはcust_last_name
でソートされているため、この表の索引のクラスタ化係数は大幅に低くなっています。クラスタ化係数は、ブロック数の70倍から10倍になっています。
customers
表を問い合せます。
たとえば、次の問合せを実行します(出力例も示します)。
SELECT cust_first_name, cust_last_name FROM customers WHERE cust_last_name BETWEEN 'Puleo' AND 'Quinn'; CUST_FIRST_NAME CUST_LAST_NAME -------------------- ---------------------------------------- Vida Puleo Harriett Quinlan Madeleine Quinn Caresse Puleo
問合せのカーソルを表示します。
たとえば、次の問合せを実行します(出力例の一部も示します)。
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR()); ------------------------------------------------------------------------------- | Id | Operation | Name | Rows |Bytes|Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | | | 405 (100)| | |* 1| TABLE ACCESS STORAGE FULL| CUSTOMERS | 2335|35025| 405 (1)|00:00:01| -------------------------------------------------------------------------------
前述の計画は、オプティマイザが元のcustomers
表の索引を使用しなかったことを示しています。
customers3
表を問い合せます。
たとえば、次の問合せを実行します(出力例も示します)。
SELECT cust_first_name, cust_last_name FROM customers3 WHERE cust_last_name BETWEEN 'Puleo' AND 'Quinn'; CUST_FIRST_NAME CUST_LAST_NAME -------------------- ---------------------------------------- Vida Puleo Harriett Quinlan Madeleine Quinn Caresse Puleo
問合せのカーソルを表示します。
たとえば、次の問合せを実行します(出力例の一部も示します)。
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR()); --------------------------------------------------------------------------------------- |Id| Operation | Name |Rows|Bytes|Cost(%CPU)| Time| --------------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | | |69(100)| | | 1| TABLE ACCESS BY INDEX ROWID|CUSTOMERS3 |2335|35025|69(0) |00:00:01| |*2| INDEX RANGE SCAN |CUSTOMERS3_LAST_NAME_IDX|2335| |7(0) |00:00:01| ---------------------------------------------------------------------------------------
結果セットは同じですが、オプティマイザは索引を選択します。この計画のコストは、元のcustomers
表で使用された計画のコストより大幅に低下しています。
オプティマイザが索引を使用するように強制するヒントとともにcustomers
を問い合せます。
たとえば、次の問合せを実行します(出力例の一部も示します)。
SELECT /*+ index (Customers CUSTOMERS_LAST_NAME_IDX) */ cust_first_name, cust_last_name FROM customers WHERE cust_last_name BETWEEN 'Puleo' and 'Quinn'; CUST_FIRST_NAME CUST_LAST_NAME -------------------- ---------------------------------------- Vida Puleo Caresse Puleo Harriett Quinlan Madeleine Quinn
問合せのカーソルを表示します。
たとえば、次の問合せを実行します(出力例の一部も示します)。
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR()); ----------------------------------------------------------------------------------------- | Id | Operation | Name |Rows|Bytes|Cost(%CPU)| Time | ----------------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | | | 422(100) | | | 1| TABLE ACCESS BY INDEX ROWID|CUSTOMERS |335 |35025| 422(0) |00:00:01| |*2| INDEX RANGE SCAN |CUSTOMERS_LAST_NAME_IDX|2335| | 7(0) |00:00:01| -----------------------------------------------------------------------------------------
前述の計画は、customers
の索引を使用するコストは全表スキャンよりも高いことを示しています。したがって、索引の使用によりパフォーマンスが向上するとはかぎりません。索引クラスタ化係数は、索引スキャンのほうが全表スキャンよりも効果的であるかどうかを判断するための目安です。
この例は、索引クラスタ化係数が表アクセスのコストにどのように影響するかを説明しています。
次の使用例を考えてみます。
表には9つの行があり、それらは3つのデータ・ブロックに格納されています。
col1
列は現在、値A
、B
およびC
を格納しています。
この表では、col1_idx
という名前の非一意索引がcol1
に存在します。
例10-4 配列されているデータ
次のように行がデータ・ブロックに格納されていると仮定します。
Block 1 Block 2 Block 3 ------- ------- ------- A A A B B B C C C
この例では、col1_idx
の索引クラスタ化係数は低くなります。c1
に対して同じ索引付きの列の値を持つ行が、表の中の同じデータ・ブロック内にあります。そのため、索引レンジ・スキャンを使用して値A
を持つすべての行を戻す場合、表の中のブロックを1つのみ読み取れば済むため、低いコストで済みます。
例10-5 分散されているデータ
次のように同じ行が複数のデータ・ブロックに分散されていると仮定します。
Block 1 Block 2 Block 3 ------- ------- ------- A B C A C B B A C
この例では、col1_idx
の索引クラスタ化係数は高くなります。データベースは、表の中の3つのブロックをすべて読み取って、col1
内に値A
を持つすべての行を取得する必要があります。
関連項目:
DBA_INDEXES
ビューの詳細は、『Oracle Databaseリファレンス』を参照してください
グローバル一時表は、特定の期間、セッション固有の中間データを格納する特殊な表です。
CREATE GLOBAL TEMPORARY TABLE
のON COMMIT
句は、表がトランザクション固有(DELETE ROWS
)であるか、セッション固有(PRESERVE ROWS
)であるかを示します。こうして、一時表には、トランザクションまたはセッションのいずれかの間、中間結果セットが保持されます。
グローバル一時表を作成するとき、すべてのセッションで参照できる定義を作成します。物理記憶域は割り当てられません。セッションが最初に表にデータを配置する際、データベースは記憶域を割り当てます。一時表内のデータは、現在のセッションのみが参照できます。
Oracle Database 12cからは、表レベルのプリファレンスGLOBAL_TEMP_TABLE_STATS
を設定して、グローバル一時表の統計を共有するか(SHARED
)、セッション固有のものとするか(SESSION
)を選択できるようになりました。
GLOBAL_TEMP_TABLE_STATS
がSESSION
の場合、1つのセッションでグローバル一時表の統計を収集できますが、統計はこのセッションでのみ使用できます。その一方で、ユーザーは共有バージョンの統計を引き続き維持できます。オプティマイザは、最適化時にはまずグローバル一時表にセッション固有の統計があるかどうかを確認します。ある場合、オプティマイザはそれらを使用します。ない場合は、共有の統計があればそれを使用します。
注意:
Oracle Database 12cより前のリリースでは、データベースで、グローバル一時表と非グローバル一時表のオプティマイザ統計は別々に維持されていませんでした。別々のセッションのデータが異なっている可能性があっても、データベースでは、すべてのセッションで共有されている統計の1つのバージョンが維持されていました。
セッション固有のオプティマイザ統計は、次のような特性を持っています。
統計を追跡するディクショナリ・ビューには、共有の統計と現在のセッションのセッション固有の統計の両方が表示されます。
ビューは、DBA_TAB_STATISTICS
、DBA_IND_STATISTICS
、DBA_TAB_HISTOGRAMS
およびDBA_TAB_COL_STATISTICS
です(各ビューには、対応するUSER_
およびALL_
バージョンがあります)。SCOPE
列は、統計がセッション固有であるか、共有であるかを示します。セッション固有の統計は、複数のプロセスがOracle RACでこれらの統計にアクセスできるように、データ・ディクショナリに格納する必要があります。
CREATE ... AS SELECT
では、オプティマイザ統計を自動収集します。ただし、GLOBAL_TEMP_TABLE_STATS
がSHARED
に設定されている場合、DBMS_STATS
を使用して統計を手動で収集する必要があります。
保留中の統計情報はサポートされていません。
他のセッションでは、セッション固有の統計を使用するカーソルを共有しません。
Oracle Database 12cより前のリリースと同様に、共有されている統計を使用するカーソルは、別のセッションでも共有できます。同一セッションでは、セッション固有の統計を使用するカーソルを共有できます。
デフォルトでは、一時表のGATHER_TABLE_STATS
は、同一セッションでコンパイルされた直前のカーソルを即座に無効にします。ただし、この手順によって、他のセッションでコンパイルされたカーソルが無効になることはありません。
関連項目:
GLOBAL_TEMP_TABLE_STATS
プリファレンスの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
DBA_TAB_STATISTICS
ビューの詳細は、『Oracle Databaseリファレンス』を参照してください
DBMS_STATS
は、セッション固有のグローバル一時表への変更をコミットしますが、トランザクション固有のグローバル一時表への変更はコミットしません。
Oracle Database 12cより前は、トランザクション固有の一時表(ON COMMIT DELETE ROWS
)でDBMS_STATS.GATHER_TABLE_STATS
を実行すると、表内のすべての行が削除され、統計ではその表が空と表示されていました。Oracle Database 12cからは、次のプロシージャにおいてトランザクション固有の一時表へのコミットが行われないため、これらの表の行が削除されることはありません。
GATHER_TABLE_STATS
DELETE_TABLE_STATS
DELETE_COLUMN_STATS
DELETE_INDEX_STATS
SET_TABLE_STATS
SET_COLUMN_STATS
SET_INDEX_STATS
GET_TABLE_STATS
GET_COLUMN_STATS
GET_INDEX_STATS
前述のプログラム・ユニットはGLOBAL_TEMP_TABLE_STATS
プリファレンスを確認します。たとえば、表プリファレンスがSESSION
に設定されている場合、SET_TABLE_STATS
によってセッション統計が設定され、GATHER_TABLE_STATS
によってトランザクション固有一時表のすべての行が保存されます。たとえば、表プリファレンスがSHARED
に設定されている場合、SET_TABLE_STATS
によって共有統計が設定され、GATHER_TABLE_STATS
によってトランザクション固有一時表のすべての行が削除されます。
関連項目:
グローバル一時表の詳細は、『Oracle Database概要』を参照してください
DBMS_STATS.GATHER_TABLE_STATS
プロシージャの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
システム統計は、ハードウェア特性(I/O、CPUのパフォーマンスおよび使用率など)を記述します。
システム統計を使用すると、問合せオプティマイザは、実行計画を選択する際に、I/OおよびCPUコストをより正確に見積ることができます。システム統計の更新時には、解析済のSQL文は無効にされません。データベースでは、新しいSQL文はすべて、新しい統計を使用して解析されます。
関連項目:
『Oracle Databaseリファレンス』
ユーザー定義ファンクションおよび索引の作成者は、拡張可能オプティマイザを使用して、統計集計ファンクション、選択ファンクションおよびコスト・ファンクションを作成できます。オプティマイザのコスト・モデルは拡張されて、CPUおよびI/Oコストを評価するために、ユーザーによって提供される情報が統合されます。
統計タイプは、実行計画の選択に影響を与えるユーザー定義関数のインタフェースとして機能します。ただし、統計タイプを使用するために、オプティマイザでは、列、スタンドアロン・ファンクション、オブジェクト型、索引、索引タイプ、パッケージなどのデータベース・オブジェクトにタイプをバインドするメカニズムが必要です。SQL文ASSOCIATE STATISTICS
によりこのバインドが発生することがあります。
ユーザー定義の統計のファンクションは、標準SQLデータ型およびオブジェクト型の両方を使用する列、およびドメイン索引に関連しています。統計タイプを列またはドメイン索引に関連付ける場合、DBMS_STATS
によって統計が収集されるたびに、統計タイプの統計コレクション・メソッドがコールされます。
関連項目:
拡張可能オプティマイザおよびユーザー定義統計の詳細は、Oracle Databaseデータ・カートリッジ開発者ガイドを参照してください
Oracle Databaseには、統計を収集するためのいくつかのメカニズムがあります。
この項の内容は次のとおりです。
DBMS_STATS
PL/SQLパッケージは、オプティマイザ統計を収集および管理します。
このパッケージにより、統計収集の並列度、サンプリング方法、パーティション表での統計収集の粒度など、どの統計をどのように収集するかを制御できます。
注意:
オプティマイザ統計の収集に、ANALYZE
文でCOMPUTE
句およびESTIMATE
句を使用しないでください。これらの句は非推奨になりました。かわりに、DBMS_STATS
を使用します。
DBMS_STATS
パッケージによって収集される統計は、正確な実行計画を作成するために必要です。たとえば、DBMS_STATS
によって収集される表統計には、行の数、ブロックの数、行の長さの平均などがあります。
デフォルトでは、自動オプティマイザ統計収集が使用されます。この場合、データベースでは、DBMS_STATS
が自動的に実行され、統計がないか失効しているすべてのスキーマ・オブジェクトのオプティマイザ統計が自動的に収集されます。このプロセスは、オプティマイザの管理に関連する多数の手動タスクが削減し、統計の欠如または失効のために最適ではない実行計画が生成されるリスクを大幅に減らします。DBMS_STATS
を手動で実行することで、オプティマイザ統計を更新して管理することもできます。
関連項目:
自動メンテナンス・タスクの詳細は、『Oracle Database管理者ガイド』を参照してください
DBMS_STATS
の詳細は、Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンスを参照してください
デフォルトでは、オプティマイザ統計が存在しないか、失効しているか、不十分な場合、データベースでは、解析中に動的統計が自動的に収集されます。データベースでは、再帰的SQLを使用して、表ブロックの小さなランダム・サンプルがスキャンされます。
注意:
動的統計は、統計にかわるものを提供するのではなく、統計を増強します。
動的統計は、表および索引のブロック・カウント、表および結合のカーディナリティ(概算行数)、結合列の統計、GROUP BY
統計などのオプティマイザ統計を補完します。この情報により、オプティマイザは、述語のカーディナリティに対してより正確な見積りを行うことで、計画を改善できます。
動的統計にメリットがあるのは、次の場合です。
複雑な述語のために、実行計画が最適ではなくなっている場合。
サンプリングにかかる時間が、問合せの実行時間全体のごく一部である場合。
サンプリング時間をならすために、問合せが何度も実行される場合。
Oracle Database 12cからは、次のタイプのバルク・ロード時に表統計を自動的に収集できるようになりました。ダイレクト・パス・インサートを使用した、空の表へのINSERT INTO ... SELECT
およびCREATE TABLE AS SELECT
。
注意:
デフォルトでは、パラレル・インサートはダイレクト・パス・インサートを使用します。/*+APPEND*/
ヒントを使用して、ダイレクト・パス・インサートを強制することができます。
この項の内容は次のとおりです。
関連項目:
バルク・ロードの詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。
一般に、データ・ウェアハウスからは、大量のデータがデータベースにロードされます。たとえば、販売データ・ウェアハウスでは、毎晩販売データをロードすることが考えられます。
Oracle Database 12cより前のリリースでは、失効した統計によって計画が最適なものとならない可能性を避けるため、バルク・ロードの後に手動で統計を収集する必要がありました。バルク・ロード時の自動統計収集機能には、次のような利点があります。
パフォーマンスの向上
ロード中に統計を収集することで、表統計を収集するための追加の表スキャンを回避できます。
管理性の向上
バルク・ロード後、統計を収集するためのユーザーの操作は必要ありません。
空のパーティション化された表に行を挿入する場合、挿入時にグローバル統計が収集されます。
たとえば、sales
が空のパーティション表である場合およびINSERT INTO sales SELECT
を実行する場合、データベースはsales
のグローバル統計を収集します。ただし、データベースではパーティション・レベルの統計を収集しません。
拡張構文を使用して行を空である特定のパーティションまたはサブパーティションに挿入する別のケースを仮定します。データベースでは、挿入中に空のパーティションの統計を収集します。ただし、グローバル統計は収集しません。
INSERT INTO sales PARTITION (sales_q4_2000) SELECT
を実行するとします。挿入前にパーティションsales_q4_2000
が空である場合(他のパーティションは空である必要はありません)、データベースでは挿入中に統計を収集します。さらに、INCREMENTAL
プリファレンスがsales
で有効化されている場合は、sales_q4_2000
のシノプシスも収集されます。統計はINSERT
文の直後に使用できます。ただし、トランザクションをロールバックすると、バルク・ロード中に収集された統計は自動的に削除されます。
関連項目:
INSERT
の構文およびセマンティクスについては、Oracle Database SQL言語リファレンスを参照してください
オンラインでの統計の収集時、データベースでは、索引統計は収集されずヒストグラムは作成されません。これらの統計が必要な場合には、バルク・ロード後にDBMS_STATS.GATHER_TABLE_STATS
のoptions
パラメータをGATHER AUTO
に設定して実行することをお薦めします。
たとえば、次のコマンドによって、バルク・ロードされたsh_ctas
表の統計が収集されます。
EXEC DBMS_STATS.GATHER_TABLE_STATS( user, 'SH_CTAS', options => 'GATHER AUTO' );
この例では、見つからないか、失効している統計のみ収集されます。バルク・ロード時に収集される表統計および基本列統計は収集されません。
注意:
バルク・ロードする表の表プリファレンスoptions
をGATHER AUTO
に設定できます。このようにすることで、GATHER_TABLE_STATS
の実行時にoptions
パラメータを明示的に設定する必要がなくなります。
関連項目:
バルク・ロードの詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください
状況によっては、バルク・ロードのための統計収集は自動的に発生しません。
ターゲット表、パーティションまたはサブパーティションが次のいずれかの条件に該当する場合、バルク・ロードの統計は収集されません。
空ではなく、INSERT INTO ... SELECT
を実行します。
この場合、OPTIMIZER STATISTICS GATHERING
行ソースは計画に表示されますが、この行ソースはパス・スルーされるにすぎません。オプティマイザ統計は、実際には収集されません。
注意:
空の表へのバルク・ロードによって、DBA_TAB_COL_STATISTICS.NOTES
列はSTATS_ON_LOAD
に設定されます。ただし、空ではない表へのその後のバルク・ロードでは、NOTES
列がリセットされません、。統計が収集されているかどうかを判別する1つの手法は、DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
を実行して、USER_TAB_MODIFICATIONS.INSERTS
を問い合せることです。問合せによってロードされた行数を示す行が返された場合、前回のバルク・ロード時に、統計は自動的に収集されていません。
Oracle所有のスキーマ内に存在する表(SYS
など)である。
ネストした表である。
索引構成表(IOT)である。
外部表である。
ON COMMIT DELETE ROWS
として定義されたグローバル一時表である。
仮想列を含む表である。
PUBLISH
プリファレンスがFALSE
に設定されている表である。
オプティマイザ統計がロックされています。
INCREMENTAL
がtrue
に設定されており、拡張構文が使用できないパーティション表です。
たとえば、DBMS_STATS.SET_TABLE_PREFS(null, 'sales', incremental', 'true')
を実行するとします。この場合、sales
が空であっても、INSERT INTO sales SELECT
の統計は収集されません。ただし、INSERT INTO sales PARTITION (sales_q4_2000) SELECT
の統計は自動的に収集されます。
複数表のINSERT
文を使用してロードされます。
関連項目:
デフォルトでは、バルク・ロード時に統計が収集されます。NO_GATHER_OPTIMIZER_STATISTICS
ヒントを使用すると、この機能を文レベルで無効にでき、GATHER_OPTIMIZER_STATISTICS
ヒントを使用すると、この機能を文レベルで有効にできます。
たとえば、次の文はバルク・ロードでのオンライン統計収集を無効にします。
CREATE TABLE employees2 AS SELECT /*+NO_GATHER_OPTIMIZER_STATISTICS*/ * FROM employees
関連項目:
GATHER_OPTIMIZER_STATISTICS
ヒントとNO_GATHER_OPTIMIZER_STATISTICS
ヒントの詳細は、『Oracle Database SQL言語リファレンス』を参照してください
データベースでは、様々なソースから様々なタイミングでオプティマイザ統計が収集されます。
この項の内容は次のとおりです。
オプティマイザはオプティマイザ統計に複数の異なるソースを使用します。
ソースは次のとおりです。
DBMS_STATS
実行、自動または手動
このPL/SQLパッケージは、オプティマイザ統計を収集するための主要な手段です。
SQLのコンパイル
SQLのコンパイル時に、データベースでは、以前にDBMS_STATS
を使用して収集した統計を増強することができます。この段階で、SQL文のWHERE
句の述語を満たす行が表内にどれぐらいあるのか、より正確な情報を取得するために追加の問合せが実行されます。
SQLの実行
実行時、データベースでは、以前に収集された統計をさらに増強できます。この段階では、Oracle Databaseは、SQL文の実行中にすべての行ソースによって生成された多数の行を収集します。実行の最後に、オプティマイザは、行数の見積りが、次の文の実行時に再解析の必要性があるほど不正確であるかどうかを判断します。カーソルが再解析とマークされている場合、オプティマイザは、見積りではなく、以前の実行で収集された実際の行カウントを使用します。
SQLプロファイル
SQLプロファイルは、問合せの補助統計のコレクションです。プロファイルは、データ・ディクショナリにこれらの補足的な統計を格納します。オプティマイザは、最適化時にSQLプロファイルを使用して、最適な計画を特定します。
データベースでは、データ・ディクショナリにオプティマイザ統計が格納され、必要に応じて更新または置換されます。データ・ディクショナリ・ビュー内の統計を問い合せることができます。
関連項目:
DBMS_STATS.GATHER_TABLE_STATS
プロシージャの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
SQL計画ベースラインは、オプティマイザがより最適な計画を生成するために使用できる追加情報および命令です。たとえば、SQL計画ディレクティブは、欠落している拡張を記録するようオプティマイザに指示できます。
SQLの実行時に、カーディナリティが誤って見積られた場合、データベースでSQL計画ディレクティブが作成されます。
SQLのコンパイル時に、オプティマイザは、欠落している拡張またはヒストグラムが存在するかどうかを判断するために、ディレクティブに対応する問合せを確認します。オプティマイザは欠落している拡張をすべて記録します。以降のDBMS_STATS
コールでは、この拡張に対する統計が収集されます。
ディレクティブに対応する統計が不十分な場合、オプティマイザは動的統計を使用します。たとえば、オプティマイザは列グループの統計が作成されるまで、および統計の作成後も誤った見積りが行われるたびに動的統計を収集します。この場合、オプティマイザは列グループのみを監視します。オプティマイザは、式に対する拡張は作成しません。
SQL計画ディレクティブは、特定のSQL文またはSQL IDに関連していません。ディレクティブは問合せ式で定義されるため、オプティマイザは、ほぼ同一の文に対してディレクティブを使用できます。たとえば、ディレクティブは、選択リスト・アイテムを除いて同じである問合せなど、類似のパターンを使用する問合せの場合、オプティマイザにとって有益です。
SQL計画ディレクティブは、データベースによって自動的に管理されます。ディレクティブはデータベースによって最初に共有プール内に作成されます。データベースは、SYSAUX
表領域に定期的にディレクティブを書き込みます。ディレクティブは、DBMS_SPD
パッケージで使用可能なAPIによって管理できます。
関連項目:
この例では、SQL文のSQL計画ディレクティブが自動的に作成および使用される際の仕組みを示します。
前提条件
sh
スキーマへの問合せを実行しようとしています。このスキーマ、データ・ディクショナリおよびV$
ビューに対する権限は保有しています。
データベースがSQL計画ディレクティブを使用する際の仕組みを確認する手順は次のとおりです。
sh.customers
表を問い合せます。
SELECT /*+gather_plan_statistics*/ * FROM customers WHERE cust_state_province='CA' AND country_id='US';
gather_plan_statistics
ヒントは、計画内の各操作で戻された実際の行数を示します。これにより、オプティマイザの見積りと実際に戻された行数を比較できます。
前述の問合せの計画を問い合せます。
次の例に、実行計画を示します(出力例も示します)。
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID b74nw722wjvy3, child number 0 ------------------------------------- select /*+gather_plan_statistics*/ * from customers where CUST_STATE_PROVINCE='CA' and country_id='US' Plan hash value: 1683234692 -------------------------------------------------------------------------------------------- | Id| Operation | Name | Starts |E-Rows| A-Rows | A-Time |Buffers | Reads | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 29 |00:00:00.01 | 17 | 14 | |*1 | TABLE ACCESS FULL| CUSTOMERS | 1 | 8 | 29 |00:00:00.01 | 17 | 14 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"='US'))
計画内の各操作で戻された実際の行数(A-Rows
)は、見積り(E-Rows
)と大きく異なっています。この文は、自動再最適化の候補です。
customers
問合せを再最適化できるかどうかを確認します。
次の文では、V$SQL.IS_REOPTIMIZABLE
の値を問い合せます(出力例も示します)。
SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT, IS_REOPTIMIZABLE FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT /*+gather_plan_statistics*/%'; SQL_ID CHILD_NUMBER SQL_TEXT I ------------- ------------ ----------- - b74nw722wjvy3 0 select /*+g Y ather_plan_ statistics* / * from cu stomers whe re CUST_STA TE_PROVINCE ='CA' and c ountry_id=' US'
IS_REOPTIMIZABLE
列はY
とマークされています。そのため、次の実行ではcustomers
問合せのハード解析が実行されます。オプティマイザは、この最初の実行から実行統計を使用して計画を決定します。再最適化によって得られた情報はSQL計画ディレクティブとしてデータベースに保持されます。
sh
スキーマのディレクティブを表示します。
次の例では、DBMS_SPD
を使用してディスクにSQL計画ディレクティブを書き込んでから、sh
スキーマのみのディレクティブを表示します。
EXEC DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE; SELECT TO_CHAR(d.DIRECTIVE_ID) dir_id, o.OWNER AS "OWN", o.OBJECT_NAME AS "OBJECT", 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 COL_NAME OBJECT TYPE STATE REASON ------------------- --- --------- ----------- ------ ---------------- ------ ------------ 1484026771529551585 SH CUSTOMERS COUNTRY_ID COLUMN DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE 1484026771529551585 SH CUSTOMERS CUST_STATE_ COLUMN DYNAMIC_SAMPLING USABLE SINGLE TABLE PROVINCE CARDINALITY MISESTIMATE 1484026771529551585 SH CUSTOMERS TABLE DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
SQL計画ディレクティブは、データベースによってまずメモリーに格納され、その後15分おきにディスクに書き込まれます。そのため、前述の例では、DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE
をコールして、ディレクティブがSYSAUX
表領域に強制的に書き込まれるようにしています。
ビューDBA_SQL_PLAN_DIRECTIVES
およびDBA_SQL_PLAN_DIR_OBJECTS
を使用してディレクティブを監視します。3つのエントリ(customers
表用に1つ、相関列のそれぞれの列用に1つずつ)がビューに表示されます。customers
問合せのIS_REOPTIMIZABLE
値がY
であるため、この文を再度実行すると、データベースでハード解析が再度実行され、前回の実行統計に基づいて計画が生成されます。
customers
表を再度問い合せます。
たとえば、次の文を入力します。
SELECT /*+gather_plan_statistics*/ * FROM customers WHERE cust_state_province='CA' AND country_id='US';
カーソル内の計画を問い合せます。
次の例に、実行計画を示します(出力例も示します)。
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID b74nw722wjvy3, child number 1
-------------------------------------
select /*+gather_plan_statistics*/ * from customers where
CUST_STATE_PROVINCE='CA' and country_id='US'
Plan hash value: 1683234692
---------------------------------------------------------------------------
|Id | Operation |Name |Start|E-Rows|A-Rows| A-Time |Buffers|
---------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1| | 29|00:00:00.01| 17|
|* 1| TABLE ACCESS FULL|CUSTOMERS| 1| 29| 29|00:00:00.01| 17|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"='US'))
Note
-----
- cardinality feedback used for this statement
Note
セクションは、この文に対して再最適化が使用されたことを示しています。今回は見積られた行数(E-Rows
)が正確な値になっています。SQL計画ディレクティブはまだ使用されていません。
customers
問合せのカーソルを問い合せます。
たとえば、次の問合せを実行します(出力例も示します)。
SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT, IS_REOPTIMIZABLE FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT /*+gather_plan_statistics*/%'; SQL_ID CHILD_NUMBER SQL_TEXT I ------------- ------------ ----------- - b74nw722wjvy3 0 select /*+g Y ather_plan_ statistics* / * from cu stomers whe re CUST_STA TE_PROVINCE ='CA' and c ountry_id=' US' b74nw722wjvy3 1 select /*+g N ather_plan_ statistics* / * from cu stomers whe re CUST_STA TE_PROVINCE ='CA' and c ountry_id=' US'
customers
問合せの新しい計画が存在し、子カーソルも存在します。
SQL計画ディレクティブが存在し、他の文でも使用できることを確認します。
たとえば、次の問合せを実行します。この問合せは類似していますが、元のcustomers
問合せとは異なります(STATEが CA
ではなくMA
になっています)。
SELECT /*+gather_plan_statistics*/ CUST_EMAIL FROM CUSTOMERS WHERE CUST_STATE_PROVINCE='MA' AND COUNTRY_ID='US';
カーソル内の計画を問い合せます。
次の文では、カーソルを問い合せます(出力例も示します)。
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 3tk6hj3nkcs2u, child number 0 ------------------------------------- Select /*+gather_plan_statistics*/ cust_email From customers Where cust_state_province='MA' And country_id='US' Plan hash value: 1683234692 --------------------------------------------------------------------------- |Id | Operation | Name |Starts|E-Rows|A-Rows| A-Time |Buffers| --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01| 16 | |*1 | TABLE ACCESS FULL| CUSTOMERS | 1 | 2 | 2 |00:00:00.01| 16 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("CUST_STATE_PROVINCE"='MA' AND "COUNTRY_ID"='US')) Note ----- - dynamic sampling used for this statement (level=2) - 1 Sql Plan Directive used for this statement
計画のNote
セクションは、オプティマイザがこの文に対してSQLディレクティブを使用し、動的統計も使用したことを示しています。
関連項目:
「自動再最適化」
DBA_SQL_PLAN_DIRECTIVES
、V$SQL
および他のデータベース・ビューの詳細は、『Oracle Databaseリファレンス』を参照してください
DBMS_SPD
の詳細は、『Oracle Databaseリファレンス』を参照してください
この例では、オプティマイザによって、拡張が存在し、統計が適用可能であることが確認されるまでは、SQL計画ディレクティブが使用される際の仕組みを示します。
この時点で、ディレクティブのステータスはSUPERSEDED
に変更されます。以降のコンパイルではディレクティブのかわりに統計が使用されます。
前提条件
この例では、「オプティマイザがSQL計画ディレクティブを使用する際の仕組み: 例」の手順をすでに実行していることを前提としています。
オプティマイザが拡張およびSQL計画ディレクティブを使用する際の仕組みを確認する手順は次のとおりです。
sh.customers
表の統計を収集します。
たとえば、次のPL/SQLプログラムを実行します。
BEGIN DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS'); END; /
customers
表に拡張が存在するかどうかを確認します。
たとえば、次の問合せを実行します(出力例も示します)。
SELECT TABLE_NAME, EXTENSION_NAME, EXTENSION FROM DBA_STAT_EXTENSIONS WHERE OWNER='SH' AND TABLE_NAME='CUSTOMERS'; TABLE_NAM EXTENSION_NAME EXTENSION --------- ------------------------------ ----------------------- CUSTOMERS SYS_STU#S#WF25Z#QAHIHE#MOFFMM_ ("CUST_STATE_PROVINCE", "COUNTRY_ID")
前述の出力は、cust_state_province
列およびcountry_id
列に列グループの拡張が存在することを示しています。
SQL計画ディレクティブの状態を問い合せます。
例10-6では、ディレクティブに関する情報をデータ・ディクショナリに問い合せます。
列グループの統計が存在しますが、データベースで文の再コンパイルがまだ行われていないため、ディレクティブの状態はUSABLE
になっています。次のコンパイル時に、オプティマイザは、統計が適用可能であるかどうかを確認します。適用可能な場合は、ディレクティブのステータスがSUPERSEDED
に変更されます。以降のコンパイルではディレクティブのかわりに統計が使用されます。
sh.customers
表を問い合せます。
SELECT /*+gather_plan_statistics*/ * FROM customers WHERE cust_state_province='CA' AND country_id='US';
カーソル内の計画を問い合せます。
例10-7に、実行計画を示します(出力例も示します)。
Note
セクションは、オプティマイザが拡張統計ではなくディレクティブを使用したことを示しています。コンパイル時に、データベースによって拡張統計が検証されました。
SQL計画ディレクティブの状態を問い合せます。
例10-8では、ディレクティブに関する情報をデータ・ディクショナリに問い合せます。
SUPERSEDED
に変更されたディレクティブの状態は、対応する列またはグループに拡張またはヒストグラムがあること、またはディレクティブに使用できる別のSQL計画ディレクティブが存在することを示します。
若干異なる形式の文を使用して、sh.customers
表を再度問い合せます。
たとえば、次の問合せを実行します。
SELECT /*+gather_plan_statistics*/ /* force reparse */ * FROM customers WHERE cust_state_province='CA' AND country_id='US';
共有SQL領域にカーソルがある場合は、通常、データベースでカーソルが共有されます。強制的に再解析が行われるように、この手順では、コメントを追加してSQLテキストを若干変更しています。
カーソル内の計画を問い合せます。
例10-9に、実行計画を示します(出力例も示します)。
Note
が存在しないのは、オプティマイザがSQL計画ディレクティブのかわりに拡張統計を使用したことを示しています。ディレクティブは、53週間使用されない場合、自動的に消去されます。
関連項目:
DBA_SQL_PLAN_DIRECTIVES
、V$SQL
および他のデータベース・ビューの詳細は、『Oracle Databaseリファレンス』を参照してください
DBMS_SPD
の詳細は、『Oracle Databaseリファレンス』を参照してください
例10-6 shスキーマのディレクティブの表示
EXEC DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE; 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_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE 1484026771529551585 SH CUSTOMERS CUST_STATE_ COLUMN DYNAMIC_SAMPLING USABLE SINGLE TABLE PROVINCE CARDINALITY MISESTIMATE 1484026771529551585 SH CUSTOMERS TABLE DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
例10-7 実行計画
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID b74nw722wjvy3, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ * from customers where
CUST_STATE_PROVINCE='CA' and country_id='US'
Plan hash value: 1683234692
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 29 |00:00:00.01 | 16 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 1 | 29 | 29 |00:00:00.01 | 16 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"='US'))
Note
-----
- dynamic sampling used for this statement (level=2)
- 1 Sql Plan Directive used for this statement
例10-8 shスキーマのディレクティブの表示
EXEC DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE; 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
例10-9 実行計画
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID b74nw722wjvy3, child number 0 ------------------------------------- select /*+gather_plan_statistics*/ * from customers where CUST_STATE_PROVINCE='CA' and country_id='US' Plan hash value: 1683234692 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 29 |00:00:00.01 | 17 | |* 1 | TABLE ACCESS FULL| CUSTOMERS | 1 | 29 | 29 |00:00:00.01 | 17 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"='US')) 19 rows selected.
Oracle Database 12cから、オプティマイザは、すべてのSQL文に対して、動的統計が有用であるかどうか、および使用するサンプル・サイズを自動的に決定するようになりました。以前のリリースでは、動的統計は動的サンプリングと呼ばれていました。
動的統計を使用するかどうかを決める主な要因は、最適な計画を生成するために十分な統計が使用可能かどうかということです。統計が十分でない場合、オプティマイザは動的統計を使用します。
自動動的統計は、OPTIMIZER_DYNAMIC_SAMPLING
初期化パラメータが0
に設定されていない場合に有効になります。デフォルトでは、動的統計レベルは2
に設定されます。
一般に、オプティマイザは、動的統計ではなく、デフォルトの統計を使用して、表、索引および列の最適化時に必要な統計を計算します。オプティマイザは、次のような複数の要素に基づいて、動的統計を使用するかどうかを決定します。
SQL文でパラレル実行が使用される。
SQL計画ディレクティブが存在する。
次の図は、動的統計の収集プロセスを示しています。
図10-2に示されているとおり、オプティマイザは、次の場合に動的統計を自動的に収集します。
統計が欠落している
問合せ内の表に統計がない場合、オプティマイザは最適化の前にこれらの表の基本的な統計を収集します。アプリケーションで、統計を収集するためのDBMS_STATS
へのフォローアップ・コールなしに新しいオブジェクトが作成された、または統計が収集される前にオブジェクトで統計がロックされたという理由で、統計が欠落することがあります。
この場合、統計はDBMS_STATS
パッケージを使用して収集された統計ほど高品質ではなく、完全でもありません。これは、文のコンパイル時間に及ぼす影響を限定的にするために妥協されます。
統計が十分でない
オプティマイザが、列間の相関、列データ配分の偏り、式の統計などを考慮に入れずに、述語(フィルタまたは結合)の選択性またはGROUP BY
句を見積る場合、統計は常に不十分なものになる可能性があります。
拡張統計により、オプティマイザは複雑な条件式に対して的確な品質のカーディナリティ予測を得ることができます。オプティマイザは、動的統計を使用して拡張統計の欠落を補完したり、たとえば非等価述語に対する拡張統計が使用できないときに動的統計を使用したりすることができます。
注意:
データベースは、AS OF
句を含む問合せに対して動的統計を使用しません。
関連項目:
OPTIMIZER_DYNAMIC_SAMPLING
初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。
最適化の最初に、表が動的統計の候補であるかどうかを判断する際、オプティマイザは、表に永続SQL計画ディレクティブが存在するかどうかをチェックします。各ディレクティブについて、オプティマイザは、表に関連する述語のカーディナリティを判断するときに計算する統計式を登録します。
図10-2では、データベースにより再帰的SQL文が発行されて、表ブロックの小さなランダム・サンプルがスキャンされます。データベースは、関連する単一表述語および結合を適用して、述語のカーディナリティを見積ります。
データベースでは、動的統計の結果が共有可能な統計として保持されます。データベースでは、1つの問合せのSQLコンパイルの間、同じ問合せの再コンパイルと結果を共有できます。データベースでは、同じパターンを持つ問合せの結果を再利用することもできます。
関連項目:
動的統計レベルの設定方法については、「動的統計の制御」を参照してください
OPTIMIZER_DYNAMIC_SAMPLING
初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。