| Oracle® Database SQLチューニング・ガイド 12c リリース1 (12.1) B71277-09 |
|
 前 |
 次へ |
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リファレンス』を参照してください。
