10 オプティマイザ統計の概念
Oracle Databaseのオプティマイザ統計は、データベースおよびそのオブジェクトに関する詳細を表します。
10.1 オプティマイザ統計の概要
オプティマイザのコスト・モデルは、問合せに関連するオブジェクトについて収集された統計および問合せが実行されるデータベースとホストに基づきます。
オプティマイザは、統計を使用して、表、パーティションまたは索引から取得される行数(およびバイト数)の見積りを取得します。オプティマイザは、アクセスのコストを見積り、考えられる計画のコストを決定して、最もコストの低い実行計画を選択します。
オプティマイザ統計のタイプは次のとおりです。
-
表統計
-
行数
-
ブロック数
-
行の平均の長さ
-
-
列統計
-
列内の個別値(NDV)数
-
列内のNULL数
-
データ配分(ヒストグラム)
-
拡張統計
-
-
索引統計
-
リーフ・ブロック数
-
レベル数
-
索引クラスタ化係数
-
-
システム統計
-
I/Oパフォーマンスと使用率
-
CPUパフォーマンスと使用率
-
図10-1に示されているように、データベースでは、表、列、索引およびシステムのオプティマイザ統計がデータ・ディクショナリに格納されます。これらの統計は、データ・ディクショナリ・ビューを使用してアクセスできます。
ノート:
オプティマイザ統計は、V$ビューで参照可能なパフォーマンス統計とは異なるものです。
10.2 オプティマイザ統計のタイプについて
オプティマイザは、様々なタイプのデータベース・オブジェクトおよび様々な特性のデータベース環境で統計を収集します。
10.2.1 表統計
表統計には、実行計画を作成する際にオプティマイザで使用されるメタデータが含まれています。
10.2.1.1 永続表の統計
Oracle Databaseの表統計には、行およびブロックに関する情報が含まれます。
オプティマイザは、これらの統計を使用して、表スキャンおよび表結合のコストを判断します。データベースは、永続表に関するすべての関連統計を追跡します。たとえば、DBA_TAB_STATISTICSに格納された表統計は、次のものを追跡します。
-
行数
データベースでは、カーディナリティの判定の際、
DBA_TAB_STATISTICSに格納された行カウントが使用されます。 - 行の平均の長さ
-
データ・ブロック数
オプティマイザは、
DB_FILE_MULTIBLOCK_READ_COUNT初期化パラメータに指定されているデータ・ブロックの数を使用して、実表のアクセス・コストを判断します。 -
空のデータ・ブロック数
DBMS_STATS.GATHER_TABLE_STATSは、永続表での統計の収集前にコミットされます。
例10-1 表統計
この例では、sh.customers表の表統計を問い合せます。
SELECT NUM_ROWS, AVG_ROW_LEN, BLOCKS,
EMPTY_BLOCKS, LAST_ANALYZED
FROM DBA_TAB_STATISTICS
WHERE OWNER='SH'
AND TABLE_NAME='CUSTOMERS';
出力例は次のように表示されます。
NUM_ROWS AVG_ROW_LEN BLOCKS EMPTY_BLOCKS LAST_ANAL
---------- ----------- ---------- ------------ ---------
55500 189 1517 0 25-MAY-17
関連項目:
-
DBA_TAB_STATISTICSビューおよびDB_FILE_MULTIBLOCK_READ_COUNT初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください
10.2.1.2 一時表の統計
DBMS_STATSは、永続表とグローバル一時表の両方について統計を収集できますが、後者には追加の考慮事項が適用されます。
10.2.1.2.1 一時表のタイプ
一時表は、グローバル、プライベートまたはcursor-durationに分類されます。
一時表のすべてのタイプで、データはそのデータを挿入するセッションでのみ表示されます。表の違いは次のとおりです。
-
グローバル一時表は、特定の期間、セッション固有の中間データを格納するように明示的に作成された永続オブジェクトです。
定義がすべてのセッションで参照可能なので、表はグローバルです。
CREATE GLOBAL TEMPORARY TABLEのON COMMIT句は、表がトランザクション固有(DELETE ROWS)であるか、セッション固有(PRESERVE ROWS)であるかを示します。グローバル一時表のオプティマイザ統計は、共有される場合もセッション固有の場合もあります。 -
プライベート一時表は明示的に作成されるオブジェクトで、プライベートのメモリーのみメタデータによって定義され、特定の期間、セッション固有の中間データを格納します。
定義が、表を作成したセッションでのみ参照可能なので、表はプライベートです。
CREATE PRIVATE TEMPORARY TABLEのON COMMIT句は、表がトランザクション固有(DROP DEFINITION)であるか、セッション固有(PRESERVE DEFINITION)であるかを示します。 -
cursor-duration一時表は暗黙的に作成されるメモリーのみのオブジェクト、カーソルに関連付けられます。
グローバル一時表およびプライベート一時表とは異なり、
DBMS_STATSでcursor-duration一時表の統計を収集することはできません。
各表は、データの格納場所、作成および削除方法、メタデータの期間および可視性という点で違いがあります。データベースは、表の作成時ではなく、セッションが最初にデータをグローバル一時表に挿入するときに記憶域を割り当てます。
表10-1 一時表の重要な特性
| 特性 | グローバル一時表 | プライベート一時表 | cursor-duration一時表 |
|---|---|---|---|
| データの可視性 | データを挿入しているセッション | データを挿入しているセッション | データを挿入しているセッション |
| データの記憶域 | 永続 | メモリーまたは一時ファイル。ただし、セッションまたはトランザクションの存続期間のみ | メモリー内のみ |
| メタデータの可視性 | すべてのセッション | (V$ビューに基づく、USER_PRIVATE_TEMP_TABLESに)表を作成したセッション
|
カーソルを実行中のセッション |
| メタデータの期間 | 表が明示的に削除されるまで | 表が明示的に削除されるまで、またはセッション(PRESERVE DEFINITION)またはトランザクション(DROP DEFINITION)の最後まで
|
カーソルの経過期間が共有プールを超えるまで |
| 表の作成 | CREATE GLOBAL TEMPORARY TABLE (AS SELECTをサポート)
|
CREATE PRIVATE TEMPORARY TABLE (AS SELECTをサポート)
|
オプティマイザで有用とみなされたとき暗黙的に作成 |
| 既存のトランザクションに対する作成の影響 | 暗黙的コミットなし | 暗黙的コミットなし | 暗黙的コミットなし |
| 命名規則 | 永続表と同じ | ORA$PTT_で始まる必要あり |
内部的に生成される一意の名前 |
| 表の削除 | DROP GLOBAL TEMPORARY TABLE |
DROP PRIVATE TEMPORARY TABLE、あるいはセッション(PRESERVE DEFINITION)またはトランザクション(DROP DEFINITION)の最後に暗黙的に削除
|
セッション終了時に暗黙的に削除 |
関連項目:
-
一時表の管理方法の詳細は、『Oracle Database管理者ガイド』を参照
10.2.1.2.2 グローバル一時表の統計
DBMS_STATSは、グローバル一時表についても永続表と同じタイプの統計を収集します。
ノート:
プライベート一時表に関する統計情報は収集できません。
次の表に、グローバル一時表におけるオプティマイザ統計の収集および格納方法が、トランザクションとセッションのどちらに表がスコープ指定されているかに応じてどう異なるかを示しています。
表10-2 グローバル一時表のオプティマイザ統計
| 特性 | トランザクション固有 | セッション固有 |
|---|---|---|
DBMS_STATSコレクションの影響
|
コミットしない | コミット |
| 統計の格納 | メモリーのみ | ディクショナリ表 |
| ヒストグラムの作成 | サポート対象外 | サポート対象 |
次のプロシージャにおいてトランザクション固有の一時表へのコミットが行われないため、これらの表の行が削除されることはありません。
-
GATHER_TABLE_STATS -
DELETE_obj_STATS。objはTABLE、COLUMNまたはINDEXです -
SET_obj_STATS。objはTABLE、COLUMNまたはINDEXです -
GET_obj_STATS。objはTABLE、COLUMNまたはINDEXです
前述のプログラム・ユニットは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.1.2.3 グローバル一時表の共有統計およびセッション固有統計
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リファレンス』を参照してください
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 1010.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 システム統計
システム統計は、ハードウェア特性(I/O、CPUのパフォーマンスおよび使用率など)を記述します。
システム統計を使用すると、問合せオプティマイザは、実行計画を選択する際に、I/OおよびCPUコストをより正確に見積ることができます。システム統計の更新時には、解析済のSQL文は無効にされません。データベースでは、新しいSQL文はすべて、新しい統計を使用して解析されます。
10.2.5 ユーザー定義のオプティマイザ統計
ユーザー定義ファンクションおよび索引の作成者は、拡張可能オプティマイザを使用して、統計収集ファンクション、選択ファンクションおよびコスト・ファンクションを作成できます。
オプティマイザのコスト・モデルは拡張されて、CPUおよびI/Oコストを評価するために、ユーザーによって提供される情報が統合されます。統計タイプは、実行計画の選択に影響を与えるユーザー定義ファンクションのインタフェースとして機能します。ただし、統計タイプを使用するために、オプティマイザでは、列、スタンドアロン・ファンクション、オブジェクト型、索引、索引タイプ、パッケージなどのデータベース・オブジェクトにタイプをバインドするメカニズムが必要です。SQL文ASSOCIATE STATISTICSを使用すると、このバインドを実行できます。
ユーザー定義の統計のファンクションは、標準SQLデータ型およびオブジェクト型の両方を使用する列、およびドメイン索引に関連しています。統計タイプを列またはドメイン索引に関連付ける場合、DBMS_STATSによって統計が収集されるたびに、統計タイプの統計コレクション・メソッドがコールされます。
関連項目:
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 19cには、高頻度自動オプティマイザ統計収集が導入されています。この軽量タスクは、定期的に失効したオブジェクトの統計を収集します。デフォルトの間隔は15分です。自動統計収集ジョブとは対照的に、高頻度タスクでは、存在しないオブジェクトの統計のパージやオプティマイザ統計アドバイザの起動などのアクションが実行されません。高頻度タスクのプリファレンスはDBMS_STATS.SET_GLOBAL_PREFSプロシージャを使用して設定できます。また、メタデータはDBA_AUTO_STAT_EXECUTIONSを使用して確認できます。
関連項目:
-
自動メンテナンス・タスクの詳細は、『Oracle Database管理者ガイド』を参照してください
-
DBMS_STATSの詳細は、Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンスを参照してください
10.3.2 補足的な動的統計
デフォルトでは、オプティマイザ統計が存在しないか、失効しているか、不十分な場合、データベースでは、解析中に動的統計が自動的に収集されます。データベースでは、再帰的SQLを使用して、表ブロックの小さなランダム・サンプルがスキャンされます。
ノート:
動的統計は、統計にかわるものを提供するのではなく、統計を増強します。
動的統計は、表および索引のブロック・カウント、表および結合のカーディナリティ(概算行数)、GROUP BY統計などのオプティマイザ統計を補完します。この情報により、オプティマイザは、述語のカーディナリティに対してより正確な見積もりを行うことで、計画を改善できます。
動的統計にメリットがあるのは、次の場合です。
-
複雑な述語のために、実行計画が最適ではなくなっている場合。
-
サンプリングにかかる時間が、問合せの実行時間全体のごく一部である場合。
-
サンプリング時間をならすために、問合せが何度も実行される場合。
10.3.3 オンライン統計収集
状況によっては、DDLおよびDML操作によってオンライン統計収集が自動的にトリガーされます。
10.3.3.1 バルク・ロードのためのオンライン統計収集
データベースでは、ダイレクト・パス・インサートを使用したINSERT INTO ... SELECTのタイプとCREATE TABLE AS SELECTのタイプのバルク・ロード時に、表統計を自動的に収集できます。
デフォルトでは、パラレル・インサートはダイレクト・パス・インサートを使用します。/*+APPEND*/ヒントを使用して、ダイレクト・パス・インサートを強制することができます。
関連項目:
バルク・ロードの詳細は、Oracle Databaseデータ・ウェアハウス・ガイドを参照してください
10.3.3.1.1 バルク・ロードのためのオンライン統計収集の目的
一般に、データ・ウェアハウス・アプリケーションは、データベースに大量のデータをロードします。たとえば、販売データ・ウェアハウスは毎日、毎週または毎月データをロードすることがあります。
Oracle Database 12cより前のリリースでは、バルク・ロード後に手動で統計を収集することが推奨されていました。ただし、多くのアプリケーションでは、不備があったり、収集を開始するメンテナンス・ウィンドウを待機したりするために、ロード後に統計を収集しませんでした。統計の欠落は、最適ではない実行計画の主な原因になります。
バルク・ロード時の自動統計収集には次の利点があります。
-
パフォーマンスの向上
ロード中に統計を収集することで、表統計を収集するための追加の表スキャンを回避できます。
-
管理性の向上
バルク・ロード後、統計を収集するためのユーザーの操作は必要ありません。
10.3.3.1.2 パーティション化された表への挿入時のグローバル統計
パーティション化された表に行を挿入すると、データベースによって挿入時にグローバル統計が収集されます。
たとえば、salesがパーティション表の場合にINSERT INTO sales SELECTを実行すると、データベースによってグローバル統計が収集されます。ただし、データベースではパーティション・レベルの統計を収集しません。
特定のパーティションまたはサブパーティションに行を挿入するために、パーティション拡張構文を使用するという別のケースについて考えてみます。データベースは、挿入中にパーティションの統計を収集します。ただし、グローバル統計は収集しません。
INSERT INTO sales PARTITION (sales_q4_2000) SELECTを実行するとします。データベースは、挿入中に統計を収集します。salesに対してINCREMENTALプリファレンスが有効化されている場合は、データベースによってsales_q4_2000のシノプシスも収集されます。統計は挿入の直後に使用できます。ただし、トランザクションをロールバックすると、バルク・ロード中に収集された統計は自動的に削除されます。
関連項目:
-
INSERTの構文およびセマンティクスについては、Oracle Database SQL言語リファレンスを参照してください
10.3.3.1.3 バルク・ロード後のヒストグラム作成
オンライン統計の収集後に、データベースが自動的にヒストグラムを作成することはありません。
ヒストグラムが必要な場合は、バルク・ロードの後に、options=>GATHER AUTOを指定してDBMS_STATS.GATHER_TABLE_STATSを実行することをお薦めします。たとえば、次のプログラムではmyt表の統計を収集します。
EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'MYT', options=>'GATHER AUTO');
前述のPL/SQLプログラムでは、欠落している統計または失効している統計のみが収集されます。バルク・ロード時に収集される表統計および基本列統計は収集されません。
ノート:
バルク・ロードする表の表プリファレンスoptionsをGATHER AUTOに設定できます。このようにすることで、GATHER_TABLE_STATSの実行時にoptionsパラメータを明示的に設定する必要がなくなります。
関連項目:
-
バルク・ロードの詳細は、Oracle Databaseデータ・ウェアハウス・ガイドを参照してください
10.3.3.1.4 バルク・ロードのためのオンライン統計収集の制限
場合によっては、バルク・ロードでオプティマイザ統計が自動的に収集されないことがあります。
具体的には、ターゲット表、パーティションまたはサブパーティションが次のいずれかの条件に該当していると、バルク・ロードでは統計が自動的に収集されません。
-
オブジェクトにはデータが含まれています。バルク・ロードでは、オブジェクトが空の場合はオンライン統計のみが自動的に収集されます。
-
Oracle所有のスキーマ内に存在する表(
SYSなど)である。 -
次のタイプの表のいずれかです: ネストした表、索引構成表(IOT)、外部表または
ON COMMIT DELETE ROWSとして定義されたグローバル一時表。ノート:
ハイブリッド・パーティション表の内部パーティションについては、データベースによってオンライン統計が自動的に収集されます。
-
PUBLISHプリファレンスがFALSEに設定されている表である。 -
統計がロックされています。
-
マルチテーブル
INSERT文を使用してロードされます。
関連項目:
-
DBMS_STATS.SET_TABLE_PREFSの詳細は、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください
10.3.3.1.5 バルク・ロードのためのオンライン統計収集のユーザー・インタフェース
デフォルトでは、バルク・ロード時に統計が収集されます。
GATHER_OPTIMIZER_STATISTICSヒントを使用して、この機能を文レベルで有効化できます。NO_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.3.3.2 パーティション・メンテナンス操作のオンライン統計収集
Oracle Databaseは、特定のパーティション・メンテナンス操作中に、オンライン統計の同様のサポートを提供します。
MOVE、COALESCEおよびMERGEの場合、データベースでグローバル・レベルおよびパーティション・レベルの統計が次のように保守されます。
-
パーティションで増分統計または非増分統計のいずれかが使用される場合、データベースでは、グローバル表統計の
BLOCKS値が直接更新されます。この更新は統計収集操作ではありません。 -
データベースで操作後のパーティションの最新の統計が生成されます。増分統計が有効である場合は、データベースでパーティションのシノプシスが維持されます。
TRUNCATEまたはDROP PARTITIONの場合、データベースではグローバル表統計のBLOCKS値およびNUM_ROWS値が更新されます。この更新では、統計操作の収集は必要ありません。統計の更新は、増分統計または非増分統計のいずれかが使用されている場合に行われます。
ノート:
宛先セグメントが複数あるメンテナンス操作の場合、データベースはパーティション・レベルの統計を維持しません。
関連項目:
パーティション・メンテナンス操作の詳細は、『Oracle Database VLDBおよびパーティショニング・ガイド』を参照
10.3.3.3 リアルタイム統計
Oracle Databaseでは、従来型DML操作の実行中にリアルタイム統計を自動的に収集できます。
関連項目:
各種エディションおよびサービスでサポートされる機能の詳細は、『Oracle Databaseライセンス情報ユーザー・マニュアル』を参照
10.3.3.3.1 リアルタイム統計の目的
オンライン統計によって、バルク・ロードか従来型DMLかにかかわらず、古い統計によるオプティマイザの誤動作が減少します。
Oracle Database 12cでは、CREATE TABLE AS SELECT文およびダイレクト・パス・インサートに対するオンライン統計収集が導入されました。Oracle Database 19cでは、従来型DML文へのオンライン・サポートを拡張するリアルタイム統計が導入されています。いくつものDBMS_STATSジョブの実行によって統計は古くなっていくため、リアルタイム統計を使用することでオプティマイザが最適な計画を生成しやすくなります。
バルク・ロード操作では必要な統計がすべて収集されますが、リアルタイム統計は従来の統計を置き換えるのではなく、これを増強するものです。このため、DBMS_STATSを使用して(AutoTaskジョブの使用を推奨)、定期的に統計を収集する必要があります。
10.3.3.3.2 リアルタイム統計の動作
DML操作で表を変更している場合、Oracle Databaseによって最も重要な統計の値が動的に計算されます。
現在トランザクションが数万行をoe.orders表に追加しているシナリオを考えてみます。行が挿入されると、リアルタイム統計によって行数の増加が追跡されます。オプティマイザが新しい問合せのハード解析を実行する場合、オプティマイザはリアルタイム統計を使用してより正確なコスト見積りを取得できます。
10.3.3.3.3 リアルタイム統計のユーザー・インタフェース
PL/SQLパッケージ、データ・ディクショナリ・ビューおよびヒントを使用して、リアルタイム統計を管理し、アクセスすることができます。
OPTIMIZER_REAL_TIME_STATISTICS初期化パラメータ
OPTIMIZER_REAL_TIME_STATISTICS初期化パラメータがTRUEに設定されている場合、Oracle Databaseでは、従来型のDML操作中にリアルタイム統計が自動的に収集されます。デフォルトの設定はFALSEです。これは、リアルタイム統計が無効になっていることを意味します。
DBMS_STATS
デフォルトでは、DBMS_STATSサブプログラムにはリアルタイム統計が含まれます。これらの統計のみを含めるパラメータも指定できます。
表10-3 リアルタイム統計のサブプログラム
| サブプログラム | 説明 |
|---|---|
|
|
これらのサブプログラムを使用すると、統計をエクスポートできます。デフォルトでは、 |
|
|
これらのサブプログラムを使用すると、統計をインポートできます。デフォルトでは、 |
|
|
これらのサブプログラムを使用すると、統計を削除できます。デフォルトでは、 |
|
|
このファンクションは、2つのソースからの表統計を比較します。統計には、常にリアルタイム統計が含まれます。 |
|
|
このファンクションは、指定された2つのタイムスタンプ時点の表の統計を比較します。統計には、常にリアルタイム統計が含まれます。 |
ビュー
リアルタイム統計が使用可能な場合は、次の表のビューを使用してアクセスできます。パーティション・レベルの統計はサポートされていないため、表レベルのビューにのみリアルタイム統計が表示されます。DBA_*ビューには、ALL_*およびUSER_*のバージョンがあります。
表10-4 リアルタイム統計のビュー
| ビュー | 説明 |
|---|---|
|
|
このビューには、 |
|
|
このビューには、現行ユーザーがアクセス可能な表のオプティマイザ統計が表示されます。リアルタイム統計は、 |
ヒント
NO_GATHER_OPTIMIZER_STATISTICSヒントによってリアルタイム統計の収集が回避されます。
関連項目:
-
DBMS_STATSサブプログラムの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。 -
ALL_TAB_COL_STATISTICSビューの詳細は、『Oracle Databaseリファレンス』を参照してください。 -
リアルタイム統計機能が異なるエディションおよびサービスに対してサポートされているかどうかの詳細は、『Oracle Databaseライセンス情報ユーザー・マニュアルを参照してください
10.3.3.3.4 リアルタイム統計: 例
この例では、従来型のINSERT文によってリアルタイム統計の収集がトリガーされます。
この例では、shユーザーにDBAロールが付与され、shとしてデータベースにログインしていることを前提としています。次のステップを実行します。
-
sales表の統計を収集します。BEGIN DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES', METHOD_OPT=>'FOR ALL COLUMNS SIZE 2'); END; / -
salesの列レベルの統計を問い合せます。SET PAGESIZE 5000 SET LINESIZE 200 COL COLUMN_NAME FORMAT a13 COL LOW_VALUE FORMAT a14 COL HIGH_VALUE FORMAT a14 COL NOTES FORMAT a5 COL PARTITION_NAME FORMAT a13 SELECT COLUMN_NAME, LOW_VALUE, HIGH_VALUE, SAMPLE_SIZE, NOTES FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME = 'SALES' ORDER BY 1, 5;Notesフィールドは空白であり、リアルタイム統計が収集されていないことを示しています。COLUMN_NAME LOW_VALUE HIGH_VALUE SAMPLE_SIZE NOTES ------------- -------------- -------------- ----------- ----- AMOUNT_SOLD C10729 C2125349 5594 CHANNEL_ID C103 C10A 918843 CUST_ID C103 C30B0B 5595 PROD_ID C10E C20231 5593 PROMO_ID C122 C20A64 918843 QUANTITY_SOLD C102 C102 5593 TIME_ID 77C60101010101 78650C1F010101 5593 7 rows selected. -
salesの表レベルの統計を問い合せます。SELECT NVL(PARTITION_NAME, 'GLOBAL') PARTITION_NAME, NUM_ROWS, BLOCKS, NOTES FROM USER_TAB_STATISTICS WHERE TABLE_NAME = 'SALES' ORDER BY 1, 4;Notesフィールドは空白であり、リアルタイム統計が収集されていないことを示しています。PARTITION_NAM NUM_ROWS BLOCKS NOTES ------------- ---------- ---------- ----- GLOBAL 918843 3315 SALES_1995 0 0 SALES_1996 0 0 SALES_H1_1997 0 0 SALES_H2_1997 0 0 SALES_Q1_1998 43687 162 SALES_Q1_1999 64186 227 SALES_Q1_2000 62197 222 SALES_Q1_2001 60608 222 SALES_Q1_2002 0 0 SALES_Q1_2003 0 0 SALES_Q2_1998 35758 132 SALES_Q2_1999 54233 187 SALES_Q2_2000 55515 197 SALES_Q2_2001 63292 227 SALES_Q2_2002 0 0 SALES_Q2_2003 0 0 SALES_Q3_1998 50515 182 SALES_Q3_1999 67138 232 SALES_Q3_2000 58950 212 SALES_Q3_2001 65769 242 SALES_Q3_2002 0 0 SALES_Q3_2003 0 0 SALES_Q4_1998 48874 192 SALES_Q4_1999 62388 217 SALES_Q4_2000 55984 202 SALES_Q4_2001 69749 260 SALES_Q4_2002 0 0 SALES_Q4_2003 0 0 29 rows selected. -
従来型の
INSERT文を使用して、918,843行をsalesにロードします。INSERT INTO sales(prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) SELECT prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold * 2, amount_sold * 2 FROM sales; COMMIT; -
カーソルから実行計画を取得します。
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'TYPICAL'));この計画には、ステップ1に
LOAD TABLE CONVENTIONALおよびステップ2にOPTIMIZER STATISTICS GATHERINGが示されています。これは、データベースで通常の挿入時にリアルタイム統計が収集されたことを意味します。--------------------------------------------------------------------------------------- |Id| Operation | Name|Rows|Bytes|Cost (%CPU)|Time| Pstart|Pstop| --------------------------------------------------------------------------------------- | 0| INSERT STATEMENT | | | |910 (100)| | | | | 1| LOAD TABLE CONVENTIONAL |SALES| | | | | | | | 2| OPTIMIZER STATISTICS GATHERING | |918K| 25M|910 (2)|00:00:01| | | | 3| PARTITION RANGE ALL | |918K| 25M|910 (2)|00:00:01| 1 | 28 | | 4| TABLE ACCESS FULL |SALES|918K| 25M|910 (2)|00:00:01| 1 | 28 | --------------------------------------------------------------------------------------- -
salesの列レベルの統計を問い合せます。SET PAGESIZE 5000 SET LINESIZE 200 COL COLUMN_NAME FORMAT a13 COL LOW_VALUE FORMAT a14 COL HIGH_VALUE FORMAT a14 COL NOTES FORMAT a25 COL PARTITION_NAME FORMAT a13 SELECT COLUMN_NAME, LOW_VALUE, HIGH_VALUE, SAMPLE_SIZE, NOTES FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME = 'SALES' ORDER BY 1, 5;NotesフィールドにSTATS_ON_CONVENTIONAL_DMLが表示されるようになりました。これは、挿入の実行中にデータベースでリアルタイム統計が収集されたことを意味します。COLUMN_NAME LOW_VALUE HIGH_VALUE SAMPLE_SIZE NOTES ------------- -------------- -------------- ----------- ------------------------- AMOUNT_SOLD C10729 C224422D 9073 STATS_ON_CONVENTIONAL_DML AMOUNT_SOLD C10729 C2125349 5702 CHANNEL_ID C103 C10A 9073 STATS_ON_CONVENTIONAL_DML CHANNEL_ID C103 C10A 918843 CUST_ID C103 C30B0B 9073 STATS_ON_CONVENTIONAL_DML CUST_ID C103 C30B0B 5702 PROD_ID C10E C20231 9073 STATS_ON_CONVENTIONAL_DML PROD_ID C10E C20231 5701 PROMO_ID C122 C20A64 9073 STATS_ON_CONVENTIONAL_DML PROMO_ID C122 C20A64 918843 QUANTITY_SOLD C102 C103 9073 STATS_ON_CONVENTIONAL_DML QUANTITY_SOLD C102 C102 5701 TIME_ID 77C60101010101 78650C1F010101 9073 STATS_ON_CONVENTIONAL_DML TIME_ID 77C60101010101 78650C1F010101 5701サンプル・サイズは9073であり、挿入された918,843行の約1%です。
QUANTITY_SOLDおよびAMOUNT_SOLDでは、上位値と下位値が、手動で収集された統計とリアルタイム統計を結合しています。 -
データベースでオプティマイザ統計がデータ・ディクショナリに書き込まれるようにします。
EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; -
salesの表レベルの統計を問い合せます。SELECT NVL(PARTITION_NAME, 'GLOBAL') PARTITION_NAME, NUM_ROWS, BLOCKS, NOTES FROM USER_TAB_STATISTICS WHERE TABLE_NAME = 'SALES' ORDER BY 1, 4;Notesフィールドには、リアルタイム統計がグローバル・レベルで収集され、行数が1,837,686であったことが示されます。PARTITION_NAM NUM_ROWS BLOCKS NOTES ------------- ---------- ---------- ------------------------- GLOBAL 1837686 3315 STATS_ON_CONVENTIONAL_DML GLOBAL 918843 3315 SALES_1995 0 0 SALES_1996 0 0 SALES_H1_1997 0 0 SALES_H2_1997 0 0 SALES_Q1_1998 43687 162 SALES_Q1_1999 64186 227 SALES_Q1_2000 62197 222 SALES_Q1_2001 60608 222 SALES_Q1_2002 0 0 SALES_Q1_2003 0 0 SALES_Q2_1998 35758 132 SALES_Q2_1999 54233 187 SALES_Q2_2000 55515 197 SALES_Q2_2001 63292 227 SALES_Q2_2002 0 0 SALES_Q2_2003 0 0 SALES_Q3_1998 50515 182 SALES_Q3_1999 67138 232 SALES_Q3_2000 58950 212 SALES_Q3_2001 65769 242 SALES_Q3_2002 0 0 SALES_Q3_2003 0 0 SALES_Q4_1998 48874 192 SALES_Q4_1999 62388 217 SALES_Q4_2000 55984 202 SALES_Q4_2001 69749 260 SALES_Q4_2002 0 0 SALES_Q4_2003 0 0 -
quantity_sold列を問い合せます。SELECT COUNT(*) FROM sales WHERE quantity_sold > 50; -
カーソルから実行計画を取得します。
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'TYPICAL'));Noteフィールドには、問合せでリアルタイム統計が使用されたことが示されます。Plan hash value: 3519235612 ------------------------------------------------------------------------ |Id| Operation |Name|Rows|Bytes|Cost (%CPU)|Time|Pstart|Pstop| ------------------------------------------------------------------------ | 0| SELECT STATEMENT | | | |921 (100)| | | | | 1| SORT AGGREGATE | | 1| 3| | | | | | 2| PARTITION RANGE ALL| | 1| 3|921 (3)|00:00:01| 1 | 28 | |*3| TABLE ACCESS FULL | SALES | 1| 3|921 (3)|00:00:01| 1 | 28 | ------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("QUANTITY_SOLD">50) Note ----- - dynamic statistics used: stats for conventional DML
関連項目:
USER_TAB_COL_STATISTICSおよびUSER_TAB_STATISTICSの詳細は、『Oracle Databaseリファレンス』を参照してください。
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 MISESTIMATESQL計画ディレクティブは、データベースによってまずメモリーに格納され、その後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 statementNoteセクションは、この文に対して再最適化が使用されたことを示しています。今回は見積られた行数(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リファレンスを参照してください。