10 オプティマイザ統計の概念
Oracle Databaseのオプティマイザ統計は、データベースおよびそのオブジェクトに関する詳細を表します。
この章の内容は次のとおりです。
10.1 オプティマイザ統計の概要
オプティマイザのコスト・モデルは、問合せに関連するオブジェクトについて収集された統計および問合せが実行されるデータベースとホストに基づきます。
オプティマイザは、統計を使用して、表、パーティションまたは索引から取得される行数(およびバイト数)の見積りを取得します。オプティマイザは、アクセスのコストを見積り、考えられる計画のコストを決定して、最もコストの低い実行計画を選択します。
オプティマイザ統計のタイプは次のとおりです。
-
表統計
-
行数
-
ブロック数
-
行の平均の長さ
-
-
列統計
-
列内の個別値(NDV)数
-
列内のNULL数
-
データ配分(ヒストグラム)
-
拡張統計
-
-
索引統計
-
リーフ・ブロック数
-
レベル数
-
索引クラスタ化係数
-
-
システム統計
-
I/Oパフォーマンスと使用率
-
CPUパフォーマンスと使用率
-
図10-1に示されているように、データベースでは、表、列、索引およびシステムのオプティマイザ統計がデータ・ディクショナリに格納されます。これらの統計は、データ・ディクショナリ・ビューを使用してアクセスできます。
ノート:
オプティマイザ統計は、V$
ビューで参照可能なパフォーマンス統計とは異なるものです。
10.2 オプティマイザ統計のタイプについて
オプティマイザは、様々なタイプのデータベース・オブジェクトおよび様々な特性のデータベース環境で統計を収集します。
この項では、次の項目について説明します。
10.2.1 表統計
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リファレンス』を参照してください
10.2.2 列統計
列統計では、列値およびデータ配分に関する情報が追跡されます。
オプティマイザは、列統計を使用して、正確なカーディナリティの見積りを生成し、索引の使用、結合順序、結合方法などについてより適切な決定を行います。たとえば、DBA_TAB_COL_STATISTICS
の統計は、次のものを追跡します。
-
個別値の数。
-
NULLの数
-
上限値および下限値
-
ヒストグラム関連情報
オプティマイザは拡張統計を使用できます。これは特殊な列統計です。これらの統計は、オプティマイザに列間の論理的な関係を伝える場合に役立ちます。
関連項目:
-
DBA_TAB_COL_STATISTICS
ビューの詳細は、『Oracle Databaseリファレンス』を参照してください
10.2.3 索引統計
索引統計には、索引レベルの数、索引ブロックの数および索引とデータ・ブロックの関係に関する情報が含まれます。オプティマイザは、これらの統計を使用して、索引スキャンのコストを判断します。
この項では、次の項目について説明します。
10.2.3.1 索引統計のタイプ
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
10.2.3.2 索引クラスタ化係数
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
の索引を使用するコストは全表スキャンよりも高いことを示しています。したがって、索引の使用によりパフォーマンスが向上するとはかぎりません。索引クラスタ化係数は、索引スキャンのほうが全表スキャンよりも効果的であるかどうかを判断するための目安です。
10.2.3.3 索引クラスタ化係数がコストに与える影響: 例
この例では、索引クラスタ化係数が表アクセスのコストにどのように影響するかを説明します。
次の使用例を考えてみます。
-
表には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リファレンス』を参照してください
10.2.4 グローバル一時表のセッション固有統計
グローバル一時表は、特定の期間、セッション固有の中間データを格納する特殊な表です。
CREATE GLOBAL TEMPORARY TABLE
のON COMMIT
句は、表がトランザクション固有(DELETE ROWS
)であるか、セッション固有(PRESERVE ROWS
)であるかを示します。こうして、一時表には、トランザクションまたはセッションのいずれかの間、中間結果セットが保持されます。
グローバル一時表を作成するとき、すべてのセッションで参照できる定義を作成します。物理記憶域は割り当てられません。セッションが最初に表にデータを配置する際、データベースは記憶域を割り当てます。一時表内のデータは、現在のセッションのみが参照できます。
この項では、次の項目について説明します。
10.2.4.1 グローバル一時表の共有統計およびセッション固有統計
Oracle Database 12cからは、表レベルのプリファレンスGLOBAL_TEMP_TABLE_STATS
を設定して、グローバル一時表の統計を共有するか(SHARED
)、セッション固有のものとするか(SESSION
)を選択できるようになりました。
セッション固有に設定した場合、1つのセッションでグローバル一時表の統計を収集できますが、統計はこのセッションでのみ使用できます。その一方で、ユーザーは共有バージョンの統計を引き続き維持できます。オプティマイザは、最適化時にはまずグローバル一時表にセッション固有の統計があるかどうかを確認します。ある場合、オプティマイザはそれらを使用します。ない場合は、共有の統計があればそれを使用します。
セッション固有の統計は、次のような特性を持っています。
-
統計を追跡するディクショナリ・ビューには、共有の統計と現在のセッションのセッション固有の統計の両方が表示されます。
ビューは、
DBA_TAB_STATISTICS
、DBA_IND_STATISTICS
、DBA_TAB_HISTOGRAMS
およびDBA_TAB_COL_STATISTICS
です(各ビューには、対応するUSER_
およびALL_
バージョンがあります)。SCOPE
列は、統計がセッション固有であるか、共有であるかを示します。 -
他のセッションでは、セッション固有の統計を使用するカーソルを共有できません。
Oracle Database 12cより前のリリースと同様に、共有されている統計を使用するカーソルは、別のセッションでも共有できます。同一セッションでは、セッション固有の統計を使用するカーソルを共有できます。
-
セッション固有の統計では、保留中の統計はサポートされていません。
-
GLOBAL_TEMP_TABLE_STATS
プリファレンスがSESSION
に設定されている場合、デフォルトでは、同一セッションでコンパイルされた直前のカーソルをGATHER_TABLE_STATS
が即座に無効にします。ただし、この手順によって、他のセッションでコンパイルされたカーソルが無効になることはありません。
10.2.4.2 トランザクション固有一時表へのDBMS_STATSの影響
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パッケージおよびタイプ・リファレンス』を参照してください。
10.2.5 システム統計
システム統計は、ハードウェア特性(I/O、CPUのパフォーマンスおよび使用率など)を記述します。
システム統計を使用すると、問合せオプティマイザは、実行計画を選択する際に、I/OおよびCPUコストをより正確に見積ることができます。システム統計の更新時には、解析済のSQL文は無効にされません。データベースでは、新しいSQL文はすべて、新しい統計を使用して解析されます。
10.2.6 ユーザー定義のオプティマイザ統計
ユーザー定義ファンクションおよび索引の作成者は、拡張可能オプティマイザを使用して、統計収集ファンクション、選択ファンクションおよびコスト・ファンクションを作成できます。オプティマイザのコスト・モデルは拡張されて、CPUおよびI/Oコストを評価するために、ユーザーによって提供される情報が統合されます。
統計タイプは、実行計画の選択に影響を与えるユーザー定義ファンクションのインタフェースとして機能します。ただし、統計タイプを使用するために、オプティマイザでは、列、スタンドアロン・ファンクション、オブジェクト型、索引、索引タイプ、パッケージなどのデータベース・オブジェクトにタイプをバインドするメカニズムが必要です。SQL文ASSOCIATE STATISTICS
を使用すると、このバインドを実行できます。
ユーザー定義の統計のファンクションは、標準SQLデータ型およびオブジェクト型の両方を使用する列、およびドメイン索引に関連しています。統計タイプを列またはドメイン索引に関連付ける場合、DBMS_STATS
によって統計が収集されるたびに、統計タイプの統計コレクション・メソッドがコールされます。
関連項目:
-
拡張可能オプティマイザおよびユーザー定義統計について学習するには、Oracle Databaseデータ・カートリッジ開発者ガイドを参照してください
10.3 データベースがオプティマイザ統計を収集する方法
Oracle Databaseには、統計を収集するためのいくつかのメカニズムがあります。
この項では、次の項目について説明します。
10.3.1 DBMS_STATSパッケージ
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パッケージ・プロシージャおよびタイプ・リファレンスを参照してください
10.3.2 補足的な動的統計
デフォルトでは、オプティマイザ統計が存在しないか、失効しているか、不十分な場合、データベースでは、解析中に動的統計が自動的に収集されます。データベースでは、再帰的SQLを使用して、表ブロックの小さなランダム・サンプルがスキャンされます。
ノート:
動的統計は、統計にかわるものを提供するのではなく、統計を増強します。
動的統計は、表および索引のブロック・カウント、表および結合のカーディナリティ(概算行数)、GROUP BY
統計などのオプティマイザ統計を補完します。この情報により、オプティマイザは、述語のカーディナリティに対してより正確な見積もりを行うことで、計画を改善できます。
動的統計にメリットがあるのは、次の場合です。
-
複雑な述語のために、実行計画が最適ではなくなっている場合。
-
サンプリングにかかる時間が、問合せの実行時間全体のごく一部である場合。
-
サンプリング時間をならすために、問合せが何度も実行される場合。
10.3.3 バルク・ロードのためのオンライン統計収集
Oracle Database 12cからは、次のタイプのバルク・ロード時に表統計を自動的に収集できるようになりました。ダイレクト・パス・インサートを使用した、空の表へのINSERT INTO ... SELECT
およびCREATE TABLE AS SELECT
。
ノート:
デフォルトでは、パラレル・インサートはダイレクト・パス・インサートを使用します。/*+APPEND*/
ヒントを使用して、ダイレクト・パス・インサートを強制することができます。
この項では、次の項目について説明します。
関連項目:
バルク・ロードについてさらに学習するには、Oracle Databaseデータ・ウェアハウス・ガイドを参照してください
10.3.3.1 バルク・ロードのためのオンライン統計収集の目的
一般に、データ・ウェアハウスからは、大量のデータがデータベースにロードされます。たとえば、販売データ・ウェアハウスでは、毎晩販売データをロードすることが考えられます。
Oracle Database 12cより前のリリースでは、失効した統計によって計画が最適なものとならない可能性を避けるため、バルク・ロードの後に手動で統計を収集する必要がありました。バルク・ロード時の自動統計収集機能には、次のような利点があります。
-
パフォーマンスの向上
ロード中に統計を収集することで、表統計を収集するための追加の表スキャンを回避できます。
-
管理性の向上
バルク・ロード後、統計を収集するためのユーザーの操作は必要ありません。
10.3.3.2 空のパーティション化された表への挿入時のグローバル統計
空のパーティション化された表に行を挿入する場合、挿入時にグローバル統計が収集されます。
たとえば、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言語リファレンスを参照してください
10.3.3.3 バルク・ロード時の索引統計およびヒストグラム
オンラインでの統計の収集時、データベースでは、索引統計は収集されずヒストグラムは作成されません。これらの統計が必要な場合には、バルク・ロード後に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データ・ウェアハウス・ガイド』を参照してください
10.3.3.4 バルク・ロードのためのオンライン統計収集の制限
場合によっては、バルク・ロードのためのオプティマイザ統計収集は自動的に発生しません。
特に、ターゲット表、パーティションまたはサブパーティションが次のいずれかの条件に該当する場合、バルク・ロードの統計は自動で収集されません。
-
空ではなく、
INSERT INTO ... SELECT
を実行します。この場合、
OPTIMIZER STATISTICS GATHERING
行ソースは計画に表示されますが、この行ソースはパス・スルーされるにすぎません。オプティマイザ統計は、実際には収集されません。ノート:
空の表へのバルク・ロードによって、
DBA_TAB_COL_STATISTICS.NOTES
列はSTATS_ON_LOAD
に設定されます。ただし、空ではない表へのその後のバルク・ロードでは、NOTES
列がリセットされません、。データベースが統計を収集したかどうかを判断する手法に、USER_TAB_MODIFICATIONS.INSERTS
列の問合せがあります。問合せがロードされた行数を示す行を戻す場合、最新のバルク・ロードで自動的に統計を収集していません。 -
INSERT INTO ... SELECT
を使用してロードされ、次のいずれの条件にも該当しません。ターゲット表のすべての列が指定されているか、ターゲット列のサブセットが指定され、指定されていない列にデフォルト値があります。つまり、ターゲット表のすべての列が指定されているか、または、ターゲット列のサブセットが指定され、かつ、指定されていない列にデフォルト値がある場合にのみ、バルク・ロードのための統計が自動的に収集されます。たとえば、
sales
表に列c1
、c2
、c3
およびc4
があるとします。列c4
にはデフォルト値がありません。INSERT /*+ APPEND */ INTO sales_copy SELECT c1, c2, c3 FROM sales
を実行して、sales_copy
をロードします。この場合、sales_copy
のオンライン統計は収集されません。c4
にデフォルト値があるか、それがSELECT
リストに含まれている場合に統計が収集されます。 -
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
文を使用してロードされます。
関連項目:
-
DBMS_STATS.SET_TABLE_PREFS
についてさらに学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください
10.3.3.5 バルク・ロードのためのオンライン統計収集のヒント
デフォルトでは、バルク・ロード時に統計が収集されます。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言語リファレンス』を参照してください
10.4 データベースがオプティマイザ統計を収集する場合
データベースでは、様々なソースから様々なタイミングでオプティマイザ統計が収集されます。
この項では、次の項目について説明します。
10.4.1 オプティマイザ統計のソース
オプティマイザでは、オプティマイザ統計に複数の異なるソースが使用されます。
ソースは次のとおりです。
-
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パッケージおよびタイプ・リファレンス』を参照してください。
10.4.2 SQL計画ディレクティブ
SQL計画ディレクティブは、オプティマイザがより最適な計画を生成するために使用できる追加情報および命令です。
ディレクティブは、特定のタイプの述語のカーディナリティ予測が誤っていることを示すオプティマイザによるノートであり、今後の予測の誤りを修正するために必要な統計を収集するためのDBMS_STATS
のリマインダです。たとえば、結合列にデータの偏りがある2つの表を結合する場合、SQL計画ディレクティブでは、動的統計を使用してより正確な結合カーディナリティの見積りを取得するようオプティマイザに指示できます。
この項では、次の項目について説明します。
10.4.2.1 データベースがSQL計画ディレクティブを作成する時期
データベースは、自動再最適化中に得た情報に基づいて、SQL計画ディレクティブを自動的に作成します。SQLの実行時にカーディナリティが誤って見積られた場合、データベースでSQL計画ディレクティブが作成されます。
新しいディレクティブごとに、DBA_SQL_PLAN_DIRECTIVES.STATE
列は値USABLE
を示します。この値は、データベースがディレクティブを使用して不一致を修正できることを示しています。
オプティマイザは、一緒に使用する2つの列のフィルタ述語などの問合せ式のSQL計画ディレクティブを定義します。ディレクティブは、特定のSQL文またはSQL IDに関連していません。このため、オプティマイザは、同一ではない文に対してディレクティブを使用できます。たとえば、ディレクティブは、選択リスト・アイテムを除いて同じである問合せなど、類似のパターンを使用する問合せの場合、オプティマイザにとって有益です。
実行計画のNotesセクションは、文に使用されるSQL計画ディレクティブの数を示します。DBA_SQL_PLAN_DIRECTIVES
およびDBA_SQL_PLAN_DIR_OBJECTS
ビューを問い合せて、ディレクティブの詳細情報を取得します。
関連項目:
DBA_SQL_PLAN_DIRECTIVES
についてさらに学習するには、『Oracle Databaseリファレンス』を参照してください
10.4.2.2 データベースがSQL計画ディレクティブを使用する方法
SQL文をコンパイルすると、オプティマイザがディレクティブを確認する場合に追加情報を収集してディレクティブに従います。
オプティマイザは、次の方法でディレクティブを使用します。
-
動的統計
ディレクティブに対応する統計が不十分な場合、オプティマイザは動的統計を使用します。たとえば、述語に特定の組の列を含む問合せのカーディナリティの見積りが大きく誤っている可能性があります。SQL計画ディレクティブは、これらの列を含む問合せが解析される場合は常に、オプティマイザが動的サンプリングを使用して重大なカーディナリティの見積りの誤りを回避する必要があることを示します。
動的統計には、パフォーマンス上のオーバーヘッドがあります。オプティマイザが動的統計ディレクティブを適用する問合せをハード解析するたびに、データベースでは追加のサンプリングを実行する必要があります。
Oracle Database 12cリリース2 (12.2)から、データベースは適応動的サンプリングからSQL計画ディレクティブ・ストアに統計を書き込んで、他の問合せに対して使用可能にしています。
-
列グループ
オプティマイザは、ディレクティブに対応する問合せを調査します。欠落している列グループが存在し、
DBMS_STATS
プリファレンスAUTO_STAT_EXTENSIONS
が影響する表でON
(デフォルトはOFF
)に設定されている場合、次にDBMS_STATS
が表の統計を収集する場合にオプティマイザがこの列グループを自動的に作成します。それ以外の場合、オプティマイザは、列グループを自動的に作成しません。列グループが存在する場合、次にこの文を実行するときにオプティマイザは可能な場合にSQL計画ディレクティブのかわりに列グループ統計を使用します(等価述語
GROUP BY
など)。以降の実行で、オプティマイザは、追加のSQL計画ディレクティブを作成して、結合またはGROUP BY
のカーディナリティの見積りの誤りなどの計画の他の問題に対処する場合があります。ノート:
この場合、オプティマイザは列グループのみを監視します。オプティマイザは、式に対する拡張は作成しません。
より適切なディレクティブが存在するか、ヒストグラムまたは拡張機能が存在するためにディレクティブを発生させた問題が解決する場合、DBA_SQL_PLAN_DIRECTIVES.STATE
値がUSABLE
からSUPERSEDED
に変更されます。ディレクティブ状態の詳細は、DBA_SQL_PLAN_DIRECTIVES.NOTES
列で公開されます。
関連項目:
-
DBMS_STATS.SET_TABLE_STATS
のAUTO_STAT_EXTENSIONS
プリファレンスについてさらに学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください
10.4.2.3 SQL計画ディレクティブのメンテナンス
SQL計画ディレクティブは、データベースによって自動的に作成されます。これらは手動で作成できません。
ディレクティブはデータベースによって最初に共有プール内に作成されます。データベースは、SYSAUX
表領域に定期的にディレクティブを書き込みます。データベースは、指定した数(デフォルトでは53)の週(SPD_RETENTION_WEEKS
)以降に使用されていないすべてのSQL計画ディレクティブを自動的にパージします。
DBMS_SPD
パッケージを使用してディレクティブを管理できます。たとえば、次の操作が可能です。
-
SQL計画ディレクティブ(
ALTER_SQL_PLAN_DIRECTIVE
)の有効化および無効化 -
SQL計画ディレクティブの保存期間の変更(
SET_PREFS
) -
ステージング表へのディレクティブのエクスポート(
PACK_STGTAB_DIRECTIVE
) -
ディレクティブの削除(
DROP_SQL_PLAN_DIRECTIVE
) -
データベースによるディスクへのディレクティブの強制書込み(
FLUSH_SQL_PLAN_DIRECTIVE
)
関連項目:
DBMS_SPD
パッケージについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
10.4.2.4 オプティマイザがSQL計画ディレクティブを使用する際の仕組み: 例
この例では、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| 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 OW OBJECT COL_NAME OBJECT TYPE STATE REASON ------------------- -- --------- ---------- ------ ------------- ------ ------------ 1484026771529551585 SH CUSTOMERS COUNTRY_ID COLUMN DYNAMIC_SAMPL USABLE SINGLE TABLE CARDINALITY MISESTIMATE 1484026771529551585 SH CUSTOMERS CUST_STATE COLUMN DYNAMIC_SAMPL USABLE SINGLE TABLE _PROVINCE CARDINALITY MISESTIMATE 1484026771529551585 SH CUSTOMERS TABLE DYNAMIC_SAMPL 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リファレンス』を参照してください
10.4.2.5 オプティマイザが拡張およびSQL計画ディレクティブを使用する際の仕組み: 例
この例では、オプティマイザによって、拡張が存在し、統計が適用可能であることが確認されるまでは、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.
10.4.3 データベースがデータをサンプリングする場合
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リファレンスを参照してください
10.4.4 データベースがデータをサンプリングする方法
最適化の最初に、表が動的統計の候補であるかどうかを判断する際、オプティマイザは、表に永続SQL計画ディレクティブが存在するかどうかをチェックします。
各ディレクティブについて、オプティマイザは、表に関連する述語のカーディナリティを判断するときに計算する統計式を登録します。図10-2では、データベースにより再帰的SQL文が発行されて、表ブロックの小さなランダム・サンプルがスキャンされます。データベースは、関連する単一表述語および結合を適用して、述語のカーディナリティを見積ります。
データベースでは、動的統計の結果が共有可能な統計として保持されます。データベースでは、1つの問合せのSQLコンパイルの間、同じ問合せの再コンパイルと結果を共有できます。データベースでは、同じパターンを持つ問合せの結果を再利用することもできます。
関連項目:
-
動的統計レベルの設定方法を学習するには、「動的統計のオプションの構成」を参照してください
-
OPTIMIZER_DYNAMIC_SAMPLING
初期化パラメータの詳細は、Oracle Databaseリファレンスを参照してください。