13 オプティマイザ統計の収集
この章では、DBMS_STATS.GATHER_*_STATS
プログラム・ユニットの使用方法について説明します。
関連項目:
-
DBMS_STATS.GATHER_TABLE_STATS
について学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
13.1 自動オプティマイザ統計収集の構成
Oracle Databaseでは、オプティマイザ統計を自動的に収集できます。
13.1.1 自動オプティマイザ統計収集について
自動化メンテナンス・タスク・インフラストラクチャ(AutoTask)は、メンテナンス・ウィンドウと呼ばれるOracle Schedulerウィンドウで自動的に実行するタスクをスケジュールします。
デフォルトでは、1つのウィンドウが週の各曜日にスケジュールされます。自動オプティマイザ統計収集はAutoTaskの一部として実行されます。この収集は、デフォルトでは事前に定義されたすべてのメンテナンス・ウィンドウで実行されます。
ノート:
自動オプティマイザ統計収集をプラガブル・データベースとともに使用すると、データの可視性と権限の要件が一致しない場合があります。
オプティマイザ統計を収集する場合、データベースで内部プロシージャがコールされます。このプロシージャは、GATHER_DATABASE_STATS
プロシージャをGATHER AUTO
オプションを指定して実行する場合と同様に動作します。自動統計収集はデータベース内のすべてのプリファレンス・セットに従います。
手動収集と自動収集の主な違いは、自動収集では統計が必要となるデータベース・オブジェクトが優先される点です。自動収集では、メンテナンス・ウィンドウがクローズされる前にすべてのオブジェクトが評価され、統計がないか、非常に古い統計があるオブジェクトが優先されます。
ノート:
手動で統計を収集する場合は、自動収集のオブジェクト優先付けをDBMS_AUTO_TASK_IMMEDIATE
パッケージを使用して再現できます。このパッケージでは、自動夜間統計収集ジョブで実行されるものと同じ統計収集ジョブが実行されます。
関連項目:
コンテナ・データベース(CDB)内での管理機能の動作をまとめた表が記載されている『Oracle Database管理者ガイド』を参照してください
13.1.2 Cloud Controlを使用した自動オプティマイザ統計収集の構成
Cloud Controlを使用して、自動オプティマイザ統計収集など、すべての自動メンテナンス・タスクを有効化および無効化できます。
ほとんどの場合、デフォルトのウィンドウ・タイミングで問題なく機能します。しかし、バルク・ロードなどの操作を、ウィンドウ内で行うことも考えられます。そのような場合、自動統計収集と同時に操作を行うことにより競合が発生するのを回避するには、必要に応じてウィンドウを変更することをお薦めします。
前提条件
「Cloud Controlのデータベース・ホーム・ページへのアクセス」の説明に従って、「データベース・ホーム」ページにアクセスします。
Cloud Controlを使用して自動オプティマイザ統計収集を管理するには:
-
「管理」メニューから、「Oracle Scheduler」を選択し、「自動化メンテナンス・タスク」を選択します。
自動化メンテナンス・タスク・ページが表示されます。
このページには、事前定義されたタスクが表示されます。各タスクに関する情報を取得するには、タスクに対応するリンクをクリックします。
-
「構成」をクリックします。
「自動化メンテナンス・タスク構成」ページが表示されます。
デフォルトでは、自動オプティマイザ統計収集は
MAINTENANCE_WINDOW_GROUP
内の事前に定義されたすべてのメンテナンス・ウィンドウで実行されます。 -
次のステップを実行します。
-
「オプティマイザ統計の収集」の「タスク設定」セクションで、「有効」または「無効」のいずれかを選択して自動化タスクを有効化または無効化します。
ノート:
自動統計収集は、ディクショナリ・オブジェクトおよびユーザー・オブジェクトに対する問合せの最適な計画をオプティマイザが生成する上で重要であるため、無効にしないことを強くお薦めします。自動収集を無効にする場合は、ディクショナリ・スキーマおよびユーザー・スキーマに対して適切な自動統計収集の方針を立てるようにしてください。
-
週の特定の日に統計収集を無効化するには、ウィンドウ名の横にある該当するチェック・ボックスを選択します。
-
ウィンドウ・グループの特性を変更するには、「ウィンドウ・グループの編集」をクリックします。
-
ウィンドウの時間を変更するには、ウィンドウの名前(「MONDAY_WINDOW」など)をクリックして、「スケジュール」セクションで「編集」をクリックします。
ウィンドウの編集ページが表示されます。
このページでは、ウィンドウ実行の期間や開始時間などのパラメータを変更できます。
-
「適用」をクリックします。
-
関連項目:
Oracle Enterprise Manager Cloud Controlのオンライン・ヘルプを参照してください。
13.1.3 コマンドラインからの自動オプティマイザ統計収集の構成
Cloud Controlを使用せずに自動オプティマイザ統計収集を構成する場合は、コマンドラインを使用する必要があります。
ここでは次のオプションを指定できます。
-
DBMS_AUTO_TASK_ADMIN
PL/SQLパッケージに含まれるENABLE
またはDISABLE
プロシージャを実行します。コマンドラインで使用する手法としては、このパッケージをお薦めします。
ENABLE
およびDISABLE
プロシージャのどちらに対しても、window_name
パラメータを使用して特定のメンテナンス・ウィンドウを指定できます。 -
STATISTICS_LEVEL
(初期化レベル)をBASIC
に設定して、自動化SQLチューニング・アドバイザなどすべてのアドバイザおよび統計の収集を無効化します。ノート:
監視機能と多くの自動機能が無効になるため、
STATISTICS_LEVEL
はBASIC
に設定しないことを強くお薦めします。
DBMS_AUTO_TASK_ADMINを使用して自動統計収集を管理するには:
-
SQL*PlusまたはSQL Developerで、管理者権限を持つユーザーとしてデータベースにログインします。
-
次のいずれかの操作を行います。
-
自動タスクを有効にするには、次のPL/SQLブロックを実行します。
BEGIN DBMS_AUTO_TASK_ADMIN.ENABLE ( client_name => 'auto optimizer stats collection' , operation => NULL , window_name => NULL ); END; /
-
自動タスクを無効にするには、次のPL/SQLブロックを実行します。
BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE ( client_name => 'auto optimizer stats collection' , operation => NULL , window_name => NULL ); END; /
-
-
データ・ディクショナリを問い合せて、変更を確認します。
たとえば、次のように
DBA_AUTOTASK_CLIENT
を問い合せます。COL CLIENT_NAME FORMAT a31 SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT WHERE CLIENT_NAME = 'auto optimizer stats collection';
出力例は次のように表示されます。
CLIENT_NAME STATUS ------------------------------- -------- auto optimizer stats collection ENABLED
自動統計収集のウィンドウ属性を変更するには:
-
管理者権限でSQL*Plusをデータベースに接続します。
-
必要に応じてメンテナンス・ウィンドウの属性を変更します。
たとえば、月曜日のメンテナンス・ウィンドウが午前5時に開始されるように変更するには、次のPL/SQLプログラムを実行します。
BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE ( 'MONDAY_WINDOW' , 'repeat_interval' , 'freq=daily;byday=MON;byhour=05;byminute=0;bysecond=0' ); END; /
関連項目:
-
DBMS_AUTO_TASK_ADMIN
パッケージについて学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください -
STATISTICS_LEVEL
初期化パラメータについて学習するには、Oracle Databaseリファレンスを参照してください
13.2 高頻度自動オプティマイザ統計収集の構成
この軽量タスクにより、標準の自動統計収集を補完します。
13.2.1 高頻度自動オプティマイザ統計収集について
自動統計収集は高頻度に発生するように構成できます。
高頻度自動オプティマイザ統計収集の目的
AutoTaskは、メンテナンス・ウィンドウで自動的に実行するタスクをスケジュールします。デフォルトでは、1つのウィンドウが週の各曜日にスケジュールされます。自動オプティマイザ統計収集(DBMS_STATS
)は、事前定義されたすべてのメンテナンス・ウィンドウで実行されます。
統計は、2つの連続する統計収集タスク間で失効することがあります。データが頻繁に変更されると、失効した統計が原因でパフォーマンスの問題が発生することがあります。たとえば、証券会社は取引時間中に膨大なデータを受け取ることがあり、この期間中にオプティマイザは失効した統計を使用するようになってしまいます。
高頻度自動オプティマイザ統計収集により、標準の統計収集ジョブを補完します。デフォルトでは、この収集は15分ごとに実行されます。そのため、統計が失効するまでの時間が短くなります。
高頻度自動オプティマイザ統計収集の仕組み
この高頻度タスクの有効化と無効化、実行間隔の設定および最大実行時間の設定のために、DBMS_STATS.SET_GLOBAL_PREFS
プロシージャを使用します。高頻度タスクは「軽量」なものであり、失効した統計のみを収集します。存在しないオブジェクトの統計のパージやオプティマイザ統計アドバイザの起動などのアクションは実行されません。標準の自動ジョブでは、これらの追加タスクが実行されます。
メンテナンス・ウィンドウで実行される自動統計収集ジョブは、この高頻度のジョブの影響を受けません。高頻度タスクはメンテナンス・ウィンドウで実行できますが、メンテナンス・ウィンドウの自動統計収集ジョブが実行中のあいだは実行されません。このタスクは、DBA_AUTO_STAT_EXECUTIONS
を問い合せることで監視できます。
13.2.2高頻度自動オプティマイザ統計収集のプリファレンスの設定
このタスクの有効化と無効化には、DBMS_STATS.SET_GLOBAL_PREFS
を使用します。
DBMS_STATS.SET_GLOBAL_PREFS
を使用すると、次のいずれかの値にプリファレンスを設定できます。
-
AUTO_TASK_STATUS
高頻度自動オプティマイザ統計収集を有効または無効にします。値は次のとおりです。
-
ON
—高頻度自動オプティマイザ統計収集を有効にします。 -
OFF
—高頻度自動オプティマイザ統計収集を無効にします。これはデフォルトです。
-
-
AUTO_TASK_MAX_RUN_TIME
高頻度自動オプティマイザ統計収集の実行の最大実行時間を秒単位で構成します。最大値は、
3600
(1時間)です。これはデフォルト値です。 -
AUTO_TASK_INTERVAL
高頻度自動オプティマイザ統計収集の実行間隔を秒単位で指定します。最小値は
60
です。デフォルト値は、900
(15分)です。
高頻度タスクを構成するには:
-
管理権限を持つユーザーとしてデータベースにログインします。
-
高頻度タスクを有効にするには、
AUTO_TASK_STATUS
プリファレンスをON
に設定します。次の例では、自動タスクを有効化します。
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON');
-
最大実行時間を設定するには、
AUTO_TASK_MAX_RUN_TIME
プリファレンスを目的の秒数に設定します。次の例では、最大実行時間を10分に設定します。
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_MAX_RUN_TIME','600');
-
頻度を設定するには、
AUTO_TASK_INTERVAL
プリファレンスを目的の秒数に設定します。次の例では、頻度を8分に設定します。
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','240');
13.2.3 高頻度自動オプティマイザ統計収集: 例
この例では、DML文の実行を有効にしてから、高頻度統計収集ジョブを有効にします。
この例では、次のことを想定しています。
-
管理者としてデータベースにログインしている。
-
sh
スキーマの統計が最新になっている。 -
高頻度自動オプティマイザ統計収集
が有効になっていない。
-
sales
表およびcustomers
表の統計についてデータ・ディクショナリを問い合せます(出力例も示します)。SET LINESIZE 170 SET PAGESIZE 5000 COL TABLE_NAME FORMAT a20 COL PARTITION_NAME FORMAT a20 COL NUM_ROWS FORMAT 9999999 COL STALE_STATS FORMAT a3 SELECT TABLE_NAME, PARTITION_NAME, NUM_ROWS, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SH' AND TABLE_NAME IN ('CUSTOMERS','SALES') ORDER BY TABLE_NAME, PARTITION_NAME; TABLE_NAME PARTITION_NAME NUM_ROWS STA -------------------- -------------------- -------- --- CUSTOMERS 55500 NO SALES SALES_1995 0 NO SALES SALES_1996 0 NO SALES SALES_H1_1997 0 NO SALES SALES_H2_1997 0 NO SALES SALES_Q1_1998 43687 NO SALES SALES_Q1_1999 64186 NO SALES SALES_Q1_2000 62197 NO SALES SALES_Q1_2001 60608 NO SALES SALES_Q1_2002 0 NO SALES SALES_Q1_2003 0 NO SALES SALES_Q2_1998 35758 NO SALES SALES_Q2_1999 54233 NO SALES SALES_Q2_2000 55515 NO SALES SALES_Q2_2001 63292 NO SALES SALES_Q2_2002 0 NO SALES SALES_Q2_2003 0 NO SALES SALES_Q3_1998 50515 NO SALES SALES_Q3_1999 67138 NO SALES SALES_Q3_2000 58950 NO SALES SALES_Q3_2001 65769 NO SALES SALES_Q3_2002 0 NO SALES SALES_Q3_2003 0 NO SALES SALES_Q4_1998 48874 NO SALES SALES_Q4_1999 62388 NO SALES SALES_Q4_2000 55984 NO SALES SALES_Q4_2001 69749 NO SALES SALES_Q4_2002 0 NO SALES SALES_Q4_2003 0 NO SALES 918843 NO
前述の出力は、どの統計も失効していないことを示しています。
-
sales
およびcustomers
に対してDMLを実行します。-- insert 918K rows in sales INSERT INTO sh.sales SELECT * FROM sh.sales; -- update around 15% of sales rows UPDATE sh.sales SET amount_sold = amount_sold + 1 WHERE amount_sold > 100; -- insert 1 row into customers INSERT INTO sh.customers(cust_id, cust_first_name, cust_last_name, cust_gender, cust_year_of_birth, cust_main_phone_number, cust_street_address, cust_postal_code, cust_city_id, cust_city, cust_state_province_id, cust_state_province, country_id, cust_total, cust_total_id) VALUES(188710, 'Jenny', 'Smith', 'F', '1966', '555-111-2222', '400 oracle parkway','94065',51402, 'Redwood Shores', 52564, 'CA', 52790, 'Customer total', '52772'); COMMIT;
sales
行の合計数は100%増加していますが、customers
に追加された行は1行のみです。 -
オプティマイザ統計をディスクに保存します。
EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
-
表の統計を再度問い合せます(出力例も示します)。
SELECT TABLE_NAME, PARTITION_NAME, NUM_ROWS, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SH' AND TABLE_NAME IN ('CUSTOMERS','SALES') ORDER BY TABLE_NAME, PARTITION_NAME; TABLE_NAME PARTITION_NAME NUM_ROWS STA -------------------- -------------------- -------- --- CUSTOMERS 55500 NO SALES SALES_1995 0 NO SALES SALES_1996 0 NO SALES SALES_H1_1997 0 NO SALES SALES_H2_1997 0 NO SALES SALES_Q1_1998 43687 YES SALES SALES_Q1_1999 64186 YES SALES SALES_Q1_2000 62197 YES SALES SALES_Q1_2001 60608 YES SALES SALES_Q1_2002 0 NO SALES SALES_Q1_2003 0 NO SALES SALES_Q2_1998 35758 YES SALES SALES_Q2_1999 54233 YES SALES SALES_Q2_2000 55515 YES SALES SALES_Q2_2001 63292 YES SALES SALES_Q2_2002 0 NO SALES SALES_Q2_2003 0 NO SALES SALES_Q3_1998 50515 YES SALES SALES_Q3_1999 67138 YES SALES SALES_Q3_2000 58950 YES SALES SALES_Q3_2001 65769 YES SALES SALES_Q3_2002 0 NO SALES SALES_Q3_2003 0 NO SALES SALES_Q4_1998 48874 YES SALES SALES_Q4_1999 62388 YES SALES SALES_Q4_2000 55984 YES SALES SALES_Q4_2001 69749 YES SALES SALES_Q4_2002 0 NO SALES SALES_Q4_2003 0 NO SALES 1837686 SALES 918843 YES 31 rows selected.
前述の出力は、統計が
customers
については失効していないが、sales
については失効していることを示しています。 -
高頻度自動オプティマイザ統計収集を構成します。
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON'); EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_MAX_RUN_TIME','180'); EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','240');
前述のPL/SQLプログラムでは、高頻度収集を有効にして、最大実行時間を3分に設定し、タスクの実行間隔を4分に設定しています。
-
数分待機してから、データ・ディクショナリを問い合せます。
COL OPID FORMAT 9999 COL STATUS FORMAT a11 COL ORIGIN FORMAT a20 COL COMPLETED FORMAT 99999 COL FAILED FORMAT 99999 COL TIMEOUT FORMAT 99999 COL INPROG FORMAT 99999 SELECT OPID, ORIGIN, STATUS, TO_CHAR(START_TIME, 'DD/MM HH24:MI:SS' ) AS BEGIN_TIME, TO_CHAR(END_TIME, 'DD/MM HH24:MI:SS') AS END_TIME, COMPLETED, FAILED, TIMED_OUT AS TIMEOUT, IN_PROGRESS AS INPROG FROM DBA_AUTO_STAT_EXECUTIONS ORDER BY OPID;
出力には、高頻度ジョブが2回実行されたことと、標準の自動統計収集ジョブが1回実行されたことが示されます。
ID ORIGIN STATUS BEGIN_TIME END_TIME COMP FAIL TIMEO INPRO --- -------------------- -------- -------------- -------------- ---- ---- ----- ----- 790 HIGH_FREQ_AUTO_TASK COMPLETE 03/10 14:54:02 03/10 14:54:35 338 3 0 0 793 HIGH_FREQ_AUTO_TASK COMPLETE 03/10 14:58:11 03/10 14:58:45 193 3 0 0 794 AUTO_TASK COMPLETE 03/10 15:00:02 03/10 15:00:20 52 3 0 0
13.3 オプティマイザ統計の手動収集
自動統計収集の代替または補足として、DBMS_STATS
パッケージを使用してオプティマイザ統計を手動で収集できます。
関連項目:
-
DBMS_STATS
パッケージについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
13.3.1 DBMS_STATSを使用した手動統計収集について
DBMS_STATS
パッケージを使用してオプティマイザ統計を操作します。オブジェクト、スキーマおよびデータベースなどの様々なレベルに細分化してオブジェクトおよび列の統計を収集できます。また、物理システムの統計を収集できます。
次の表では、オプティマイザ統計収集のDBMS_STATS
プロシージャの概要が示されています。このパッケージでは、表クラスタの統計は収集されません。ただし、表クラスタの個々の表の統計は収集できます。
表13-1 オプティマイザ統計収集のDBMS_STATSプロシージャ
プロシージャ | 目的 |
---|---|
|
索引統計を収集します |
|
表、列および索引の各統計を収集します。 |
|
スキーマのすべてのオブジェクトの統計を収集します。 |
|
|
|
データベース内ですべてのオブジェクトの統計を収集します。 |
OPTIONS
パラメータがGATHER STALE
またはGATHER AUTO
に設定されている場合は、失効した統計を含むすべての表および統計がないすべての表の統計がGATHER_SCHEMA_STATS
プロシージャおよびGATHER_DATABASE_STATS
プロシージャで収集されます。監視対象の表の変更が10%を超えた場合は、これらの統計が失効していると判断され、再び統計が収集されます。
ノート:
「自動オプティマイザ統計収集の構成」の説明に従って、夜間に統計を自動収集するジョブを構成できます。
関連項目:
-
DBMS_STATS
パッケージについてさらに学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
13.3.2 オプティマイザ統計の手動収集のガイドライン
適度な速度で変更されるデータベース・オブジェクトに対しては、ほとんどの場合、自動収集で十分です。
次の表に示すように、自動収集が適切でないか使用できない場合があります。
表13-2 手動で統計を収集する理由
問題 | さらに学習するには |
---|---|
特定のタイプのバルク・ロードを実行する際に、問合せを即座に実行する必要があるために、統計収集のメンテナンス・ウィンドウを待機できない場合。 | 「バルク・ロードのためのオンライン統計収集」 |
代表的ではないワークロードを実行しているときに、固定表の統計を自動統計収集で収集する場合。 | 「固定オブジェクトの統計情報の収集」 |
自動統計収集はシステム統計を収集しません。 | 「システム統計の手動収集」 |
日中に削除または切り捨てられて再作成される変化しやすい表。 | 「動的統計を使用した変化しやすい表の統計の収集」 |
13.3.2.1 サンプル・サイズの設定のガイドライン
オプティマイザ統計において、サンプリングとは表の行のランダムなサブセットから統計を収集することです。サンプリングでは、データベースによる全表スキャンと表全体のソートを避けることができるため、統計収集に必要なリソースが最小化されます。
データベースでは、表内のすべての表が処理される場合、つまり100%のサンプルが取得される場合に最も正確な統計が収集されます。ただし、サンプル・サイズが大きくなるほど、統計収集操作にかかる時間が増加します。課題は、妥当な時間で正確な統計を取得できるようなサンプル・サイズを決定することです。
DBMS_STATS
では、サンプリングする表内の行の割合を制御するESTIMATE_PERCENT
パラメータをユーザーが指定した場合にサンプリングが使用されます。パフォーマンスの向上を最大化するとともに必要な統計の正確性を達成するには、ESTIMATE_PERCENT
パラメータでデフォルト設定のDBMS_STATS.AUTO_SAMPLE_SIZE
を使用することをお薦めします。この場合、Oracle Databaseは、サンプル・サイズを自動的に選択します。この設定により、次の使用が有効になります。
-
サンプリングよりはるかに高速なハッシュ・ベースのアルゴリズム
このアルゴリズムではすべての行が読み取られ、100%のサンプルから生成された統計と同じぐらい正確な統計が生成されます。この手法を使用して計算された統計は確定的です。
-
増分統計
-
同時統計
-
新規のヒストグラム・タイプ
DBA_TABLES.SAMPLE_SIZE
列は、統計を収集するために使用される実際のサンプル・サイズを示します。
関連項目:
-
DBMS_STATS.AUTO_SAMPLE_SIZE
についてさらに学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
13.3.2.2 パラレル統計収集のガイドライン
デフォルトでは、表または索引レベルに指定された並列度で統計が収集されます。
この設定は、DBMS_STATS
収集プロシージャのdegree
引数で上書きできます。degree
をDBMS_STATS.AUTO_DEGREE
に設定することをお薦めします。この設定を使用すると、オブジェクト・サイズおよびパラレル化関連の初期化パラメータに基づいて適切な並列度が選択されます。
データベースでは、ほとんどの統計をシリアルまたはパラレルで収集できます。ただし、クラスタ索引、ドメイン索引およびビットマップ結合索引などの一部の索引統計は、データベースによってパラレルで収集されることはありません。データベースでは、パラレルでの統計収集時にサンプリングを使用できます。
ノート:
統計のパラレル収集と同時収集を混同しないでください。
関連項目:
-
DBMS_STATS.AUTO_DEGREE
についてさらに学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
13.3.2.3 パーティション・オブジェクトのガイドライン
パーティション表および索引に対して、DBMS_STATS
は、各パーティションの個別の統計を収集できます。また、全表または全索引のグローバル統計も収集できます。
コンポジット・パーティションについても同様に、DBMS_STATS
はサブパーティション、パーティション、全表および全索引の個別の統計を収集できます。
収集するパーティション統計のタイプを決定するには、DBMS_STATS
プロシージャのgranularity
引数を指定します。granularity
をデフォルト値のAUTO
に設定し、パーティションのタイプに応じてサブパーティション統計、パーティション統計またはグローバル統計を収集することをお薦めします。ALL
設定では、すべてのタイプの統計が収集されます。
関連項目:
13.3.2.4 変更頻度の高いオブジェクトのガイドライン
表の変更が頻繁に行われる場合は、表が失効しないように十分な回数の統計を収集しますが、収集のオーバーヘッドでパフォーマンスが低下しない回数にします。
新しい統計の収集が週に一度または月に一度しか必要がない場合もあります。ベスト・プラクティスは、スクリプトまたはジョブ・スケジューラを使用して、DBMS_STATS.GATHER_SCHEMA_STATS
プロシージャおよびDBMS_STATS.GATHER_DATABASE_STATS
プロシージャを定期的に実行することです。
13.3.2.5 外部表のガイドライン
外部表に対するデータ操作は許可されないため、データベースで外部表の統計が失効としてマークされることはありません。たとえば基礎となるデータファイルが変更されたなどの理由で、外部表に新しい統計が必要な場合は、統計を再収集します。
外部表の場合、内部表に使用する同じDBMS_STATS
プロシージャを使用します。DBMS_STATS.SET_TABLE_STATS
およびDBMS_STATS.GET_TABLE_STATS
のscanrate
パラメータは、Oracle Databaseが表のデータをスキャンする速度(MB/秒)を指定し、外部表にのみ関係します。SCAN_RATE_
列は、DBA_TAB_STATISTICS
およびDBA_TAB_PENDING_STATS
データ・ディクショナリ・ビューに表示されます。
関連項目:
-
SET_TABLE_STATS
およびGET__TABLE_STATS
について学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください -
DBA_TAB_STATISTICS
ビューについて学習するには、Oracle Databaseリファレンスを参照してください
13.3.3 オプティマイザ統計が失効するタイミングの判別
表に失効した統計があるということは、表のデータを正確に反映していないということになります。データベース・オブジェクトに新しいデータベース統計が必要かどうかの判断に役立つように、データベースには表監視機能が提供されています。
監視では、最新の統計収集以降の、表でのDML操作の概数と、表が切り捨てられたかどうかが追跡されます。統計が失効しているかどうかを確認するには、DBA_TAB_STATISTICS
およびDBA_IND_STATISTICS
のSTALE_STATS
列を問い合せます。この列は、DBA_TAB_MODIFICATIONS
ビューおよびDBMS_STATS
のSTALE_PERCENT
プリファレンスのデータに基づきます。
ノート:
Oracle Database 12cリリース2 (12.2)から、DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
を使用することでビュー・メタデータが最新であることを確認する必要がなくなりました。DBA_TAB_STATISTICS
、DBA_IND_STATISTICS
およびDBA_TAB_MODIFICATIONS
ビューに示される統計は、ディスクおよびメモリーの両方から取得されています。
STALE_STATS
列の値は、次のとおりです。
-
YES
統計が失効しています。
-
NO
統計は失効していません。
-
null
統計が収集されていません。
GATHER AUTO
オプションを指定してGATHER_SCHEMA_STATS
またはGATHER_DATABASE_STATS
を実行すると、統計がないか失効しているオブジェクトのみの統計が収集されます。
失効した統計を判別するには:
-
SQL*Plusを起動して、必要な権限を持つユーザーとしてデータベースにログインします。
-
データ・ディクショナリを問い合せて失効している統計を確認します。
次の例では、
sh.sales
表で失効している統計を問い合せます(出力の一部を含む)。COL PARTITION_NAME FORMAT a15 SELECT PARTITION_NAME, STALE_STATS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME = 'SALES' AND OWNER = 'SH' ORDER BY PARTITION_NAME; PARTITION_NAME STA --------------- --- SALES_1995 NO SALES_1996 NO SALES_H1_1997 NO SALES_H2_1997 NO SALES_Q1_1998 NO SALES_Q1_1999 NO . . .
関連項目:
DBA_TAB_MODIFICATIONS
ビューについて学習するには、『Oracle Databaseリファレンス』を参照してください
13.3.4 スキーマ統計および表統計の収集
表統計を収集するにはGATHER_TABLE_STATS
を使用し、スキーマ内のすべてのオブジェクトの統計を収集するにはGATHER_SCHEMA_STATS
を使用します。
DBMS_STATSを使用してスキーマ統計を収集するには:
-
SQL*Plusを起動し、実行するプロシージャに対する適切な権限でデータベースに接続します。
-
GATHER_TABLE_STATS
プロシージャまたはGATHER_SCHEMA_STATS
プロシージャを実行して、必要なパラメータを指定します。一般的には次のパラメータがあります。
-
所有者 -
ownname
-
オブジェクト名 -
tabname
、indname
、partname
-
並列度 -
degree
-
例13-1 表統計の収集
この例では、DBMS_STATS
パッケージを使用して、並列設定が2
のsh.customers
表の統計を収集します。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'sh'
, tabname => 'customers'
, degree => 2
);
END;
/
関連項目:
GATHER_TABLE_STATS
プロシージャについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
13.3.5 固定オブジェクトの統計情報の収集
固定オブジェクトとは、動的パフォーマンス表とその索引です。これらのオブジェクトは、現在のデータベース・アクティビティを記録します。
他のデータベース表とは異なり、オプティマイザ統計が見つからない場合にX$
表を参照するSQL文の動的統計が自動的に使用されることはありません。かわりに、オプティマイザは事前定義されたデフォルト値を使用します。このデフォルト値は代表的なものではない場合があり、最適ではない実行計画につながる可能性があります。したがって、固定オブジェクトの統計を最新のものにしておくことが重要です。
Oracle Databaseは、固定オブジェクトの統計が以前に収集されていない場合、自動統計収集の一部として自動的に収集します。DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
をコールして手動で固定オブジェクトの統計を収集することもできます。統計は、代表的なアクティビティがデータベースで行われているときに収集することをお薦めします。
前提条件
このプロシージャを実行するには、SYSDBA
またはANALYZE ANY DICTIONARY
システム権限が必要です。
GATHER_FIXED_OBJECTS_STATSを使用してスキーマ統計を収集するには:
-
SQL*PlusまたはSQL Developerで、必要な権限を持つユーザーとしてデータベースにログインします。
-
必要なパラメータを指定して、
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
プロシージャを実行します。一般的には次のパラメータがあります。
-
現在の統計情報の保存場所を示す表の識別子 -
stattab
-
stattab
内の統計情報を関連付ける識別子(オプション) -statid
-
stattab
を含んだスキーマ(現在のスキーマと異なる場合) -statown
-
例13-2 表統計の収集
この例では、DBMS_STATS
パッケージを使用して固定オブジェクトの統計を収集します。
BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/
関連項目:
-
GATHER_TABLE_STATS
プロシージャについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
13.3.6 動的統計を使用した変化しやすい表の統計の収集
変化しやすい表(日中の間に著しく変更される表)の統計はすぐに失効します。たとえば、表は削除または切捨てが行われたり、再構築されたりする可能性があります。
変化しやすいオブジェクトの統計をNULLに設定した場合は、Oracle Databaseでは動的統計を使用して最適化中に必要な統計が動的に収集されます。OPTIMIZER_DYNAMIC_SAMPLING
初期化パラメータでこの機能を制御します。
前提条件
このチュートリアルでは、次のことが前提となっています。
-
oe.orders
表は非常に変化しやすい表です。 -
orders
表の統計を削除した上でロックし、データベースでその表の統計が収集されないようにします。この方法では、データベースによって、必要な統計を問合せ最適化の一部として動的に収集できます。 -
oe
ユーザーは、DBMS_XPLAN.DISPLAY_CURSOR
の問合せに必要な権限を持っています。
オプティマイザ統計を削除およびロックするには:
-
oe
ユーザーとしてデータベースに接続した上でoe
表の統計を削除します。たとえば、次のプロシージャを実行します。
BEGIN DBMS_STATS.DELETE_TABLE_STATS('OE','ORDERS'); END; /
-
oe
表の統計をロックします。たとえば、次のプロシージャを実行します。
BEGIN DBMS_STATS.LOCK_TABLE_STATS('OE','ORDERS'); END; /
-
orders
表を問い合せます。たとえば、次の文を使用します。
SELECT COUNT(order_id) FROM orders;
-
カーソル内の計画を問い合せます。
次のコマンドを実行します(出力の一部を含む)。
SET LINESIZE 150 SET PAGESIZE 0 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR); SQL_ID aut9632fr3358, child number 0 ------------------------------------- SELECT COUNT(order_id) FROM orders Plan hash value: 425895392 --------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| ORDERS | 105 | 2 (0)| 00:00:01 | --------------------------------------------------------------------- Note ----- - dynamic statistics used for this statement (level=2)
前述の実行計画のノートには、データベースで
SELECT
文の動的統計を使用したことが示されています。
関連項目:
-
代表的な統計を収集してロックする処理(変化しやすい表の統計が失効しないようにするための代替的な手法)方法を学習するには、「オプティマイザ統計のロックおよびロック解除」を参照してください
13.3.7 オプティマイザ統計の同時収集
Oracle Databaseでは、複数の表またはパーティションの統計を同時に収集できます。
13.3.7.1 同時統計収集について
デフォルトでは、パーティション表の各パーティションは順に収集されます。
同時統計収集モードを有効にすると、スキーマ内の複数の表、表内の複数のパーティションまたはサブパーティションに対して、オプティマイザ統計をデータベースで同時に収集できます。データベースで複数のプロセッサをすべて使用可能にすることで、同時性により、統計の収集に必要な全体の時間を減らすことができます。
ノート:
同時統計収集モードはパラレル問合せ処理に依存しませんが、その使用は可能です。
13.3.7.1.1 DBMS_STATSによる統計の同時収集の方法
Oracle Databaseでは、複数のツールおよびテクノロジを使用して、複数の統計収集ジョブを同時に作成および管理します。
データベースでは次を使用します。
-
Oracle Scheduler
-
Oracle Database Advanced Queuing(AQ)
-
Oracle Database Resource Manager(Resource Manager)
DBMS_STATS.SET_GLOBAL_PREF
を使用してCONCURRENT
プリファレンスを設定することで同時統計収集を有効化します。
データベースでは可能なかぎり多くの同時ジョブが実行されます。Job Schedulerは、同時に実行するジョブの数およびキューに入れるジョブの数を決定します。ジョブの実行が完了すると、データベースですべての表、パーティションおよびサブパーティションの統計の収集が完了するまで、スケジューラによってさらにジョブがデキューされ実行されます。ジョブの最大数は、JOB_QUEUE_PROCESSES
初期化パラメータと使用可能なシステム・リソースによる制約を受けます。
ほとんどの場合、DBMS_STATS
プロシージャによって表のパーティションまたはサブパーティションごとに別のジョブが作成されます。ただし、パーティションまたはサブパーティションが空または非常に小さい場合には、ジョブ・メンテナンスのオーバーヘッドを減らすために、データベースで自動的にそのオブジェクトと他の小さなオブジェクトを1つのジョブに一括する場合があります。
次の図には異なるレベルでのジョブの作成が示されています。表3はパーティション化された表で、その他の表はパーティション化されていません。ジョブ3は、表3のコーディネータ・ジョブとして動作し、その表のパーティションごとにジョブを作成するほか、表3のグローバル統計用に別のジョブを作成します。次の例では、増分統計収集が有効化されていることを前提としています。無効化されている場合、パーティションのジョブの完了後にグローバル統計パーティション・レベルの統計からグローバル統計が導出されます。
関連項目:
-
DBMS_STATS
パッケージについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください -
JOB_QUEUE_PROCESSES
初期化パラメータについて学習するには、Oracle Databaseリファレンスを参照してください。
13.3.7.1.2 同時統計収集とリソースの管理
DBMS_STATS
パッケージでは、ユーザーが開始した統計収集コールの一部である同時統計収集ジョブによって使用されるリソースを明示的に管理することはありません。
したがって、データベースでは、同時統計収集の間にシステム・リソースをすべて使用する場合があります。この状況に対処するには、Resource Managerを使用して同時統計収集ジョブで消費するリソースに上限を設けます。Resource Managerで同時統計収集を有効にしておく必要があります。
システムによって提供されるコンシューマ・グループであるORA$AUTOTASK
には、すべての統計収集ジョブが登録されています。ORA$AUTOTASK
に対して適切なリソースが割り当てられているリソース計画を作成して、同時統計収集で使用可能なリソースがすべて消費されることを回避できます。独自のリソース計画が存在せず、かつそれを作成しない場合は、システムによって提供されるDEFAULT_PLAN
を含むResource Managerのアクティブ化を検討してください。
ノート:
ORA$AUTOTASK
コンシューマ・グループは、メンテナンス・ウィンドウ中に自動実行されるメンテナンス・タスクと共有されます。したがって、自動統計収集で同時実行性がアクティブ化されている場合は、追加のステップを要することなく、データベースでリソースが自動管理されます。
関連項目:
Resource Managerについて学習するには、Oracle Database管理者ガイドを参照してください
13.3.7.2 同時統計収集の有効化
同時統計収集を有効化するには、DBMS_STATS.SET_GLOBAL_PREFS
プロシージャを使用してCONCURRENT
プリファレンスを設定します。
有効な値は次のとおりです。
-
MANUAL
手動統計収集でのみ、同時実行性が有効化されます。
-
AUTOMATIC
自動統計収集でのみ、同時実行性が有効化されます。
-
ALL
手動と自動の両方の統計収集で、同時実行性が有効化されます。
-
OFF
手動と自動の両方の統計収集で、同時実行性が無効化されます。これはデフォルト値です。
この項のこのチュートリアルでは、同時統計収集を有効化する方法について説明します。
前提条件
このチュートリアルの前提条件は、次のとおりです。
-
統計収集の標準的な権限に加えて、次の権限を所有している必要があります。
-
CREATE JOB
-
MANAGE SCHEDULER
-
MANAGE ANY QUEUE
-
-
SYSAUX
表領域をオンラインにする必要があるのは、スケジューラでその内部表とビューをこの表領域に格納するためです。 -
JOB_QUEUE_PROCESSES
初期化パラメータを少なくとも4
に設定する必要があります。 -
Resource Managerを有効化する必要があります。
デフォルトでは、Resource Managerは無効になっています。リソース計画が存在しない場合は、システムによって提供される
DEFAULT_PLAN
を含むResource Managerの有効化を検討してください。
前提条件
このチュートリアルでは、次の操作を行うものとします。
-
同時統計収集の有効化
-
sh
スキーマの統計の収集 -
sh
統計の収集の監視
同時統計収集を有効化するには:
-
適切な権限でSQL*Plusをデータベースに接続し、Resource Managerを有効化します。
次の例では、Resource Managerのデフォルトの計画が使用されています。
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DEFAULT_PLAN';
-
JOB_QUEUE_PROCESSES
初期化パラメータをCPUのコア数の少なくとも2倍に設定します。Oracle Real Application Clustersでは、
JOB_QUEUE_PROCESSES
設定が各ノードに適用されます。システムに4個のCPUコアがあると想定します。次の例では、パラメータを
8
(コア数の2倍)に設定します。ALTER SYSTEM SET JOB_QUEUE_PROCESSES=8;
-
パラメータの変更が有効になったことを確認します。
たとえば、SQL*Plusに次のコマンドを入力します(出力例も示します)。
SHOW PARAMETER PROCESSES; NAME TYPE VALUE -------------------------------- ----------- ----- _high_priority_processes string VKTM aq_tm_processes integer 1 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 8 log_archive_max_processes integer 4 processes integer 100
-
同時統計を有効化します。
たとえば、次のPL/SQL無名ブロックを実行します。
BEGIN DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','ALL'); END; /
-
統計が有効化されたことを確認します。
たとえば、次の問合せを実行します(出力例も示します)。
SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM DUAL; DBMS_STATS.GET_PREFS('CONCURRENT') ---------------------------------- ALL
-
SH
スキーマの統計を収集します。たとえば、次のプロシージャを実行します。
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SH');
-
別のセッションでは、
DBA_OPTSTAT_OPERATION_TASKS
を問い合せることでジョブの進行状況を監視します。たとえば、次の問合せを実行します(出力例も示します)。
SET LINESIZE 1000 COLUMN TARGET FORMAT a8 COLUMN TARGET_TYPE FORMAT a25 COLUMN JOB_NAME FORMAT a14 COLUMN START_TIME FORMAT a40 SELECT TARGET, TARGET_TYPE, JOB_NAME, TO_CHAR(START_TIME, 'dd-mon-yyyy hh24:mi:ss') FROM DBA_OPTSTAT_OPERATION_TASKS WHERE STATUS = 'IN PROGRESS' AND OPID = (SELECT MAX(ID) FROM DBA_OPTSTAT_OPERATIONS WHERE OPERATION = 'gather_schema_stats'); TARGET TARGET_TYPE JOB_NAME TO_CHAR(START_TIME,' --------- ------------------------- -------------- -------------------- SH.SALES TABLE (GLOBAL STATS ONLY) ST$T292_1_B29 30-nov-2012 14:22:47 SH.SALES TABLE (COORDINATOR JOB) ST$SD290_1_B10 30-nov-2012 14:22:08
-
元のセッションでは、同時統計収集を無効化します。
たとえば、次の問合せを実行します。
EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','OFF');
関連項目:
-
DBMS_STATS.SET_GLOBAL_PREFS
プロシージャの使用方法を学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください。
13.3.7.3 統計収集操作の監視
データ・ディクショナリ・ビューを使用して、統計収集ジョブを監視できます。
次のビューが関連します。
-
このビューには、統計収集操作の一部として実行されるまたは現在実行されているタスクの履歴が含まれています(
DBA_OPTSTAT_OPERATIONS
に記録されます)。各タスクは、親操作に対応して処理されるターゲット・オブジェクトを示します。 -
このビューには、
DBMS_STATS
パッケージを使用して表、スキーマおよびデータベース・レベルで実行されるまたは現在実行されている統計操作の履歴が含まれています。
前述のビューのTARGET
列には、統計収集ジョブのターゲット・オブジェクトが次の形式で表示されます。
OWNER.TABLE_NAME.PARTITION_OR_SUBPARTITION_NAME
統計収集ジョブの名前はすべて文字列のST$
で開始されます。
現在実行されている統計タスクおよびジョブを表示するには:
-
現在実行されている統計収集タスクをすべてのユーザー・セッションからリストするには、次のSQL文を使用します(出力例も示します)。
SELECT OPID, TARGET, JOB_NAME, (SYSTIMESTAMP - START_TIME) AS elapsed_time FROM DBA_OPTSTAT_OPERATION_TASKS WHERE STATUS = 'IN PROGRESS'; OPID TARGET JOB_NAME ELAPSED_TIME ---- ------------------------- ------------- -------------------------- 981 SH.SALES.SALES_Q4_1998 ST$T82_1_B29 +000000000 00:00:00.596321 981 SH.SALES ST$SD80_1_B10 +000000000 00:00:27.972033
完了した統計タスクおよびジョブを表示するには:
-
特定の操作の完了したタスクおよびジョブのみをリストするには、まず統計収集操作の名前、ターゲットおよび開始時刻に基づいて
DBA_OPTSTAT_OPERATIONS
ビューで操作IDを特定します。操作IDを特定したら、DBA_OPTSTAT_OPERATION_TASKS
ビューを問い合せて、その操作で対応するタスクを検索します。たとえば、ID 981の操作をリストするには、SQL*Plusで次のコマンドを使用します(出力例も示します)。
VARIABLE id NUMBER EXEC :id := 981 SELECT TARGET, JOB_NAME, (END_TIME - START_TIME) AS ELAPSED_TIME FROM DBA_OPTSTAT_OPERATION_TASKS WHERE STATUS <> 'IN PROGRESS' AND OPID = :id; TARGET JOB_NAME ELAPSED_TIME ------------------------- ------------- -------------------------- SH.SALES_TRANSACTIONS_EXT +000000000 00:00:45.479233 SH.CAL_MONTH_SALES_MV ST$SD88_1_B10 +000000000 00:00:45.382764 SH.CHANNELS ST$SD88_1_B10 +000000000 00:00:45.307397
失敗した統計収集タスクおよびジョブを表示するには:
-
次のSQL文を使用します(出力例の一部も示します)。
SET LONG 10000 SELECT TARGET, JOB_NAME AS NM, (END_TIME - START_TIME) AS ELAPSED_TIME, NOTES FROM DBA_OPTSTAT_OPERATION_TASKS WHERE STATUS = 'FAILED'; TARGET NM ELAPSED_TIME NOTES ------------------ -- -------------------------- ----------------- SYS.OPATCH_XML_INV +000000007 02:36:31.130314 <error>ORA-20011: Approximate NDV failed: ORA-29913: error in
関連項目:
DBA_SCHEDULER_JOBS
ビューについて学習するには、『Oracle Databaseリファレンス』を参照してください
13.3.8 パーティション・オブジェクトの増分統計の収集
増分統計では、変更されたパーティションのみがスキャンされます。パーティション・レベルの統計からグローバル統計を導出して大規模パーティション表の統計を収集する場合、増分統計メンテナンスによってパフォーマンスが向上します。
13.3.8.1 増分統計の目的
一般的な例では、アプリケーションによってレンジ・パーティション表の新規パーティションにデータがロードされます。アプリケーションで新しいパーティションの追加とデータのロードが行われると、データベースでは、新しいパーティションの統計を収集し、グローバル統計を最新の状態に維持することが必要になります。
通常、データ・ウェアハウス・アプリケーションは、大規模パーティション表にアクセスします。多くの場合、これらの表は日付列でパーティション化され、頻繁なDML変更の影響を受けるのは最近のパーティションのみです。増分統計を使用しない場合、通常は、統計収集で次の2段階アプローチが使用されます。
-
データベースで表をスキャンしてグローバル統計を収集する。
グローバル統計収集のための全表スキャンでは、表のサイズによっては非常に負荷がかかる可能性があります。表にパーティションが追加されるに従い、
GATHER_TABLE_STATS
の実行時間が長くなります。これは、グローバル統計には全表スキャンが必要になるためです。パーティションの小規模なサブセットのみを変更する場合でも、データベースで表全体のスキャンを実行する必要があります。 -
データベースで変更されたパーティションをスキャンしてパーティション・レベルの統計を収集する。
増分メンテナンスでは、次の理由でデータ・ウェアハウス・アプリケーションの場合に大きなパフォーマンス上の利点があります。
-
データベースでは、表を1回のみスキャンしてパーティション統計を収集し、パーティション・レベルの統計を集計することによりグローバル統計を導出する必要があります。これにより、データベースは、増分統計を使用しない場合に必要な2つの全スキャン(パーティション・レベルの統計スキャンとグローバル・レベルの統計スキャン)を回避します。
-
後続の統計収集で、データベースは、失効したパーティションのスキャンと統計(シノプシスを含む)の更新のみが必要です。データベースは、新しいパーティション統計からグローバル統計を導出できるため、全表スキャンが不要です。
増分統計を使用する場合、データベースは、グローバルまたは表レベルの統計を変更するパーティションで引き続き統計を収集する必要があります。増分統計メンテナンスは、最初からの表統計の収集と同じ統計が得られますが、パフォーマンスが向上します。
13.3.8.2 DBMS_STATSによるパーティション表のグローバル統計の導出方法
増分統計メンテナンスが有効な場合、DBMS_STATS
では、変更されたパーティションのみの統計を収集し、シノプシスを作成します。また、データベースは、パーティション・レベルのシノプシスをグローバル・シノプシスに自動的にマージし、パーティション・レベルの統計およびグローバル・シノプシスからグローバル統計を導出します。
データベースでパーティション・レベルの統計からグローバル統計を導出して計算する際に全表スキャンが回避されます。たとえば、グローバル・レベルの行数はパーティションの行数の合計です。グローバル・ヒストグラムもパーティションのヒストグラムから導出できます。
ただし、列の NDVなど、パーティション・レベルの統計からすべての統計を導出することはできません。次の例は、表の2つのパーティションのNDVを示しています。
表13-2 3つのパーティションのNDV
オブジェクト | 列値 | NDV |
---|---|---|
Partition 1 | 1,3,3,4,5 | 4 |
Partition 2 | 2,3,4,5,6 | 5 |
個々のパーティションのNDVを加算して表のNDVを計算するとNDVは9となりますが、これは正しくありません。したがって、より正確な手法のシノプシスが必要です。
13.3.8.2.1 パーティション・レベルのシノプシス
シノプシスは、パーティションの列ごとに個別値の数(NDV)を追跡する特殊なタイプの統計です。個別値をサンプリングする内部管理構造としてシノプシスをみなすことができます。
パーティション・レベルのシノプシスをマージすることにより、各列のグローバル・レベルのNDVを正確に導出できます。表13-3の例では、データベースでシノプシスを使用して列のNDVを6として計算できます。
各パーティションは、増分モードでシノプシスを維持します。表に新しいパーティションが追加された場合は、新しいパーティションの統計のみを収集する必要があります。データベースは、既存のパーティションのシノプシスとともに新しいパーティションのシノプシスを集計することにより、グローバル統計を自動的に更新します。後続の統計収集操作は、シノプシスを使用しない場合よりも高速です。
データベースでは、SYSAUX
表領域のデータ・ディクショナリ表WRI$_OPTSTAT_SYNOPSIS_HEAD$
およびWRI$_OPTSTAT_SYNOPSIS$
にシノプシスを格納します。DBA_PART_COL_STATISTICS
ディクショナリ・ビューには、パーティションの列統計の情報が含まれます。NOTES
列にキーワードINCREMENTAL
が含まれている場合、この列にシノプシスが含まれています。
関連項目:
DBA_PART_COL_STATISTICS
についてさらに学習するには、『Oracle Databaseリファレンス』を参照してください
13.3.8.2.2 NDVアルゴリズム: 適応サンプリングおよびHyperLogLog
Oracle Database 12cリリース2 (12.2)から、HyperLogLogアルゴリズムにより、NDV (個別値の数)計算パフォーマンスを改善できます。また、シノプシスに必要な記憶域も削減できます。
NDVを計算するレガシー・アルゴリズムでは、適応サンプリングを使用します。シノプシスは、個別値のサンプルです。NDVを計算する場合、データベースは最初に各個別値をハッシュ表に格納します。個別値は個別のハッシュ・バケットを占有するため、5000の個別値を持つ列には5000のハッシュ・バケットがあります。データベースは次にハッシュ・バケットの数を半分にし、少数のバケットが残るまで継続して結果を半分にします。ハッシュ表分割の数に基づいてサンプリング・レートが変更されるため、アルゴリズムは適応しています。
列のNDVを計算するため、データベースは次の式を使用します。Bは、すべての分割が実行された後に残るハッシュ・バケットの数です。Sは分割の数です。
NDV = B * 2^S
適応サンプリングは正確なNDV統計を生成しますが、次の影響があります。
-
特に表に多数の列とパーティションが含まれ、各列のNDVが高い場合、シノプシスは大量のディスク領域を占有します。
たとえば、列ごとの平均NDVが5,000の60列の表に300,000パーティションがあります。この例では、各パーティションに300,000エントリ(60 x 5000)が含まれます。合計で、シノプシス表には、少なくとも720GBの記憶域を占有する900億エントリ(300,000の二乗)が含まれます。
-
シノプシスの一括処理は、パフォーマンスに悪影響を与える可能性があります。
データベースが失効したパーティションの統計を再収集する前に、関連付けられたシノプシスを削除する必要があります。大量のUNDOおよびREDOデータが生成されるため、一括削除は遅くなる可能性があります。
動的サンプリングとは対照的に、HyperLogLogアルゴリズムはランダム化手法を使用します。アルゴリズムは複雑ですが、基本的な理解はランダム値のストリームでn個別値が平均1/n離れているということです。したがって、ストリームの最小値がわかっている場合、個別値の数を大まかに見積もることができます。たとえば、値が0から1の範囲であり、確認されている最小値が.2である場合、数値は平均して.2ずつ均等に離れるため、NDVの見積りは5です。
HyperLogLogアルゴリズムは、元の見積りを拡張して修正します。データベースはハッシュ関数を各列値に適用するため、一連のハッシュ値が列と同じカーディナリティになります。基本的な見積りの場合、NDVは2nと等しくなります。nはバイナリ表現のハッシュ値で確認された後続のゼロの最大数です。データベースでは、出力の一部を使用して値を異なるハッシュ・バケットに分割して、NDV見積りを絞り込みます。
適応サンプリングと比較したHyperLogLogアルゴリズムの利点は次のとおりです。
-
新しいアルゴリズムの正確性は、元のアルゴリズムと似ています。
-
必要なメモリーは非常に少なく、一般的にシノプシス・サイズの大幅な削減につながります。
多数のパーティションが存在し、NDVが高い多数の列がある場合、シノプシスが大きくなる可能性があります。HyperLogLogアルゴリズムを使用するシノプシスは、よりコンパクトです。シノプシスを作成および削除するとバッチの実行時間に影響があります。パーティションを管理する操作プロシージャにより、実行時間が削減されます。
DBMS_STATS
プリファレンスAPPROXIMATE_NDV_ALGORITHM
により、データベースがNDV計算に使用するアルゴリズムが決定されます。
関連項目:
APPROXIMATE_NDV_ALGORITHM
プリファレンスについて学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください
13.3.8.2.3 シノプシスを使用したグローバル統計の集計: 例
この例では、データベースでsales
表の最初の6個のパーティションの統計が収集され、各パーティションのシノプシス(S1
、S2
など)が作成されます。データベースでは、パーティション・レベルの統計およびシノプシスを集計してグローバル統計が作成されます。
次の図は、sales
表に追加される新しいパーティションを示しています。このパーティションには5月24日のデータが格納されます。データベースでは、新しく追加されたパーティションの統計の収集、他のパーティションのシノプシスの取得、グローバル統計を作成するためのシノプシスの集計が行われます。
13.3.8.3 パーティション表の統計の収集: 基本ステップ
この項では、パーティション表のオプティマイザ統計の収集方法について説明します。
13.3.8.3.1 増分統計メンテナンスの考慮事項
増分統計メンテナンスを有効にすると、様々な影響があります。
特に、次の点に注意してください。
-
表がコンポジット・パーティションを使用する場合は、データベースは変更されたサブパーティションの統計のみを収集します。データベースは、未変更のサブパーティションに対しては、サブパーティション・レベルの統計を収集しません。このようにして、データベースは未変更のパーティションをスキップして作業を減らします。
-
表が増分統計を使用しており、この表にローカルでパーティション化された索引がある場合は、データベースは、変更された(未変更でない)索引パーティションに対してグローバル・レベルで索引統計を収集します。データベースは、パーティション・レベルの統計からグローバル索引統計を生成することはしません。そのかわりに、データベースは全索引スキャンを実行してグローバル索引統計を収集します。
-
ハイブリッド・パーティション表には、内部パーティションと外部パーティションの両方が格納されています。内部パーティションに対してのみ、DDL変更は個々のパーティションおよび表自体に対する増分統計メンテナンスを起動します。たとえば、
june18
が内部パーティションの場合、ALTER TABLE ... MODIFY PARTITION jun18 ...
は統計の収集時に増分統計メンテナンスをトリガーします。june18
が外部パーティションの場合、増分メンテナンスは発生しません。 -
SYSAUX
表領域では、パーティション表のグローバル統計を維持するために追加の領域が使用されます。
関連項目:
-
ハイブリッド・パーティション表の作成方法を学習するには、『Oracle Database VLDBおよびパーティショニング・ガイド』を参照してください
-
DBMS_STATS
についてさらに学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
13.3.8.3.2 SET_TABLE_PREFSを使用した増分統計の有効化
パーティション表の増分統計メンテナンスを有効化するには、DBMS_STATS.SET_TABLE_PREFS
を使用してINCREMENTAL
値をtrue
に設定します。INCREMENTAL
をfalse
(デフォルト)に設定すると、データベースでは全表スキャンを使用してグローバル統計が維持されます。
-
パーティション表の
PUBLISH
値がtrue
です。 -
パーティション表の
INCREMENTAL
値がtrue
です。 -
統計収集プロシージャでは、
ESTIMATE_PERCENT
にAUTO_SAMPLE_SIZE
、GRANULARITY
にAUTO
を指定する必要があります。
例13-3 増分統計の有効化
パーティション表sh.sales
のPUBLISH
値がtrue
であるとします。次のプログラムにより、この表の増分統計が有効になります。
EXEC DBMS_STATS.SET_TABLE_PREFS('sh', 'sales', 'INCREMENTAL', 'TRUE');
13.3.8.3.3 APPROXIMATE_NDV_ALGORITHM設定について
DBMS_STATS.APPROXIMATE_NDV_ALGORITHM
プリファレンスは、シノプシス生成アルゴリズム(HyperLogLogまたは適応サンプリング)を指定します。INCREMENTAL_STALENESS
プリファレンスは、適応サンプリング形式を使用するシノプシスを再フォーマットする時期を制御します。
APPROXIMATE_NDV_ALGORITHM
プリファレンスには、次の指定可能な値があります。
-
REPEAT OR HYPERLOGLOG
これはデフォルトです。
INCREMENTAL
が表で有効である場合、データベースは適応サンプリング・アルゴリズムを使用する既存のシノプシスの形式を保存します。ただし、データベースはHyperLogLog形式で新しいシノプシスを作成します。既存のパフォーマンスが許容範囲であり、レガシー・コンテンツを再フォーマットするパフォーマンス・コストを発生させたくない場合、この方法が有益です。 -
ADAPTIVE SAMPLING
データベースは、すべてのシノプシスの適応サンプリング・アルゴリズムを使用します。これは最も保守的なオプションです。
-
HYPERLOGLOG
データベースでは、新しいシノプシスおよび失効したシノプシスのすべてにHyperLogLogアルゴリズムを使用します。
INCREMENTAL_STALENESS
プリファレンスは、シノプシスが失効とみなされる時期を制御します。APPROXIMATE_NDV_ALGORITHM
プリファレンスがHYPERLOGLOG
に設定されている場合、次のINCREMENTAL_STALENESS
設定が適用されます。
-
ALLOW_MIXED_FORMAT
これはデフォルトです。この値が指定され、次の条件が満たされている場合、データベースは既存の適応サンプリング・シノプシスを失効とみなしません。
-
シノプシスが新規です。
-
手動で統計を収集します。
したがって、レガシー形式およびHyperLogLog形式のシノプシスは共存できます。ただし、一定期間で、自動統計収集ジョブは、古い形式を使用するシノプシスの統計を再収集し、HyperLogLog形式のシノプシスに置き換えます。これにより、自動統計収集ジョブは徐々に古い形式を除去します。手動統計収集ジョブは、適応サンプリング形式を使用するシノプシスを再フォーマットしません。
-
-
Null
レガシー形式のシノプシスを使用したパーティションは失効とみなされ、ただちにデータベースは失効したシノプシスの統計を再収集します。利点は、パフォーマンス・コストの発生が1回のみであることです。短所は、大きい表のすべての統計を再収集するとリソース集中型になる可能性があることです。
13.3.8.3.4 シノプシス生成の構成: 例
これらの例は、シノプシスを新しいHyperLogLog形式に切り替える積極的および保守的の2つの異なる方法を示します。
例13-4 シノプシスを再フォーマットする保守的な方法
この例では、sh.sales
表に対して複合フォーマットのシノプシスの共存を許可します。複合フォーマットでは、統計の正確性が低下します。ただし、表のすべてのパーティションの統計を再収集する必要はありません。
新しいシノプシスおよび失効したシノプシスのすべてにHyperLogLogアルゴリズムを使用するようにするには、APPROXIMATE_NDV_ALGORITHM
プリファレンスをHYPERLOGLOG
に設定します。自動統計収集ジョブが一定期間に失効したシノプシスを徐々に再フォーマットするようにするには、INCREMENTAL_STALENESS
プリファレンスをALLOW_MIXED_FORMAT
に設定します。
BEGIN
DBMS_STATS.SET_TABLE_PREFS
( ownname => 'sh'
, tabname => 'sales'
, pname => 'approximate_ndv_algorithm'
, pvalue => 'hyperloglog' );
DBMS_STATS.SET_TABLE_PREFS
( ownname => 'sh'
, tabname => 'sales'
, pname => 'incremental_staleness'
, pvalue => 'allow_mixed_format' );
END;
例13-5 シノプシスを再フォーマットする積極的な方法
この例では、すべてのシノプシスで強制的にsh.sales
表にhyperloglogアルゴリズムを使用します。この場合、データベースは表のすべてのパーティションの統計を再収集する必要があります。
新しいシノプシスおよび失効したシノプシスのすべてにHyperLogLogアルゴリズムを使用するようにするには、APPROXIMATE_NDV_ALGORITHM
プリファレンスをHYPERLOGLOG
に設定します。データベースで表のすべてのパーティションの統計をただちに再収集し、新しい形式で格納するように強制するには、INCREMENTAL_STALENESS
プリファレンスをnullに設定します。
BEGIN
DBMS_STATS.SET_TABLE_PREFS
( ownname => 'sh'
, tabname => 'sales'
, pname => 'approximate_ndv_algorithm'
, pvalue => 'hyperloglog' );
DBMS_STATS.SET_TABLE_PREFS
( ownname => 'sh'
, tabname => 'sales'
, pname => 'incremental_staleness'
, pvalue => 'null' );
END;
13.3.8.4 パーティション・メンテナンス操作の増分統計の維持
パーティション・メンテナンス操作は、表パーティションの追加、交換、マージ、分割など、パーティション関連の操作です。
Oracle Databaseにより、増分統計メンテナンスの次のサポートが提供されます。
-
パーティション・メンテナンス操作により統計の収集がトリガーされた場合、データベースでは、以前は古いセグメントで削除されていたシノプシスを再利用できるようになりました。
-
DBMS_STATS
によって非パーティション表にシノプシスを作成できるようになりました。シノプシスによって、データベースでは増分統計をパーティション交換操作の一部として維持できます。パーティション交換後にパーティションの統計を明示的に収集する必要はありません。
表でDBMS_STATS
プリファレンスのINCREMENTAL
をtrue
に設定した場合は、INCREMENTAL_LEVEL
プリファレンスによって、どのシノプシスをいつ収集するのかが制御されます。このプリファレンスの値は、次のいずれかです。
-
TABLE
DBMS_STATS
によって当該の表にある表レベルのシノプシスが収集されます。INCREMENTAL_LEVEL
に設定できるのは、表レベルのTABLE
のみです。スキーマ、データベースまたはグローバル・レベルには設定できません。 -
PARTITION
(デフォルト)DBMS_STATS
によってパーティション表のパーティション・レベルのシノプシスのみが収集されます。
パーティション交換の実行時に、交換対象のパーティションを交換した後でシノプシスを使用する場合は、パーティションと交換する表でINCREMENTAL
をtrue
に、INCREMENTAL_LEVEL
をTABLE
に設定することをお薦めします。
前提条件
このチュートリアルでは、次のことが前提となっています。
-
空のパーティション
p_sales_01_2010
をsales
表にロードします。 -
ステージング表
t_sales_01_2010
を作成し、その表に値を移入します。 -
データベースで、増分統計をパーティション交換操作の一部として維持します。パーティション交換後にパーティションの統計を明示的に収集する必要はありません。
増分統計をパーティション交換操作の一部として維持するには:
-
ステージング表
t_sales_01_2010
に対する増分統計のプリファレンスを設定します。たとえば、次の文を実行します。
BEGIN DBMS_STATS.SET_TABLE_PREFS ( ownname => 'sh' , tabname => 't_sales_01_2010' , pname => 'INCREMENTAL' , pvalue => 'true' ); DBMS_STATS.SET_TABLE_PREFS ( ownname => 'sh' , tabname => 't_sales_01_2010' , pname => 'INCREMENTAL_LEVEL' , pvalue => 'table' ); END;
-
ステージング表
t_sales_01_2010
の統計を収集します。たとえば、次のPL/SQLコードを実行します。
BEGIN DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'SH' , tabname => 'T_SALES_01_2010' ); END; /
DBMS_STATS
によってt_sales_01_2010
の表レベルのシノプシスが収集されます。 -
sh.sales
表でINCREMENTAL
プリファレンスがtrue
になるようにします。たとえば、次のPL/SQLコードを実行します。
BEGIN DBMS_STATS.SET_TABLE_PREFS ( ownname => 'sh' , tabname => 'sales' , pname => 'INCREMENTAL' , pvalue => 'true' ); END; /
-
INCREMENTAL
をtrue
に設定する前にsh.sales
の統計を収集していない場合は、交換するパーティションの統計を収集します。たとえば、次のPL/SQLコードを実行します。
BEGIN DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'sh' , tabname => 'sales' , pname => 'p_sales_01_2010' , pvalue => granularity=>'partition' ); END; /
-
パーティション交換を実行します。
たとえば、次のSQL文を使用します。
ALTER TABLE sales EXCHANGE PARTITION p_sales_01_2010 WITH TABLE t_sales_01_2010;
交換後は、パーティション化された表に、パーティション
p_sales_01_2010
の統計とシノプシスの両方が存在するようになります。Oracle Database 12cより前のリリースでは、前述の文によって
p_sales_01_2010
とt_sales_01_2010
のセグメント・データおよび統計を入れ替えていました。データベースでは、t_sales_01_2010
などのパーティション化されていない表のシノプシスを維持していませんでした。パーティション化された表のグローバル統計を収集するには、p_sales_01_2010
のパーティションを再スキャンしてそのシノプシスを取得する必要がありました。
関連項目:
DBMS_STATS.SET_TABLE_PREFS
についてさらに学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください
13.3.8.5 失効したまたはロックされたパーティション統計のある表の増分統計の維持
Oracle Database 12cから、たとえパーティションまたはサブパーティションの統計が失効したりロックされても、パーティション化された表のグローバル統計を増分統計によって自動計算できるようになりました。
Oracle Database 12cより前のリリースで増分統計が有効化されている場合は、パーティションでDMLが実行されると、オプティマイザは、そのパーティションの統計が失効したと判断します。したがって、グローバル統計を正確に集計するには、DBMS_STATS
で再び統計を収集する必要があります。さらに、統計がロックされたパーティションでDMLが実行される場合は、DBMS_STATS
でそのパーティションの統計を再収集できないため、全表スキャンのみがグローバル統計を収集する手段となります。統計の再収集では、パフォーマンスにオーバーヘッドが生じます。
Oracle Database 12cでは、パーティションまたはサブパーティションの統計が失効しているかどうかを判別する方法が、統計プリファレンスのINCREMENTAL_STALENESS
で制御されます。このプリファレンスの値は、次のいずれかです。
-
USE_STALE_PERCENT
DMLの変更が表に対して指定した
STALE_PERCENT
プリファレンスを下回る場合はパーティションまたはサブパーティションが失効しているとはみなされません。STALE_PERCENT
のデフォルト値は10
です。これはDMLによって10%を超える行で変更が生じると、その表が失効しているとみなされることを意味します。 -
USE_LOCKED_STATS
ロックされたパーティションまたはサブパーティションの統計は、DMLの変更にかかわらず、失効しているとみなされることはありません。
-
NULL
(デフォルト)DMLに変更が生じた場合は、パーティションまたはサブパーティションが失効しているとみなされます。この動作はOracle Database 11gの動作と同じです。デフォルト値を使用する場合、増分モードで収集される統計は非増分モードで収集される統計と同じであることが保証されます。デフォルト値以外が使用されると、増分モードで収集された統計は、非増分モードで収集された統計よりも精度が低くなる可能性があります。
USE_STALE_PERCENT
とUSE_LOCKED_STATS
は合わせて指定できます。たとえば、次のような無名ブロックを記述できます。
BEGIN
DBMS_STATS.SET_TABLE_PREFS (
ownname => null
, table_name => 't'
, pname => 'incremental_staleness'
, pvalue => 'use_stale_percent,use_locked_stats'
);
END;
前提条件
このチュートリアルでは、次のことが前提となっています。
-
パーティション化された表の
STALE_PERCENT
が10
に設定されています。 -
INCREMENTAL
値はtrue
に設定されています。 -
表には、以前に
INCREMENTAL
モードで収集された統計があります。 -
INCREMENTAL_STALENESS
の設定や統計のロックの有無、DMLの変更割合に応じて統計収集がどのように変化するのかを確認します。
失効したまたはロックされたパーティション統計のある表をテストするには:
-
INCREMENTAL_STALENESS
をNULL
に設定します。その後、DMLアクティビティのため、1つのパーティションの5%の行が変更されます。
-
DBMS_STATS
を使用して表の統計を収集します。DBMS_STATS
によって、5%のDMLアクティビティが含まれたパーティションの統計が再収集され、グローバル統計が増分的に維持されます。 -
INCREMENTAL_STALENESS
をUSE_STALE_PERCENT
に設定します。その後、DMLアクティビティのため、1つのパーティションの5%の行が変更されます。
-
DBMS_STATS
を使用して表の統計を収集します。DBMS_STATS
によって、DMLアクティビティが含まれていたパーティションの統計が再収集されることはなく(変更が10%の失効のしきい値を下回るため)、グローバル統計が増分的に維持されます。 -
パーティション統計をロックします。
その後、DMLアクティビティのため、1つのパーティションの20%の行が変更されます。
-
DBMS_STATS
を使用して表の統計を収集します。統計がロックされているため、
DBMS_STATS
によってパーティションの統計が再収集されることはありません。データベースでは、全表スキャンを使用してグローバル統計が収集されます。その後、DMLアクティビティのため、1つのパーティションの5%の行が変更されます。
-
DBMS_STATS
を使用して表の統計を収集します。この表の統計が収集される場合は、統計が失効しているとみなされないため、
DBMS_STATS
ではパーティションの統計が再収集されません。データベースでは、このパーティションの既存の統計を使用してグローバル統計が増分的に維持されます。 -
INCREMENTAL_STALENESS
をUSE_LOCKED_STATS
およびUSE_STALE_PERCENT
に設定します。その後、DMLアクティビティのため、1つのパーティションの20%の行が変更されます。
-
DBMS_STATS
を使用して表の統計を収集します。USE_LOCKED_STATS
が設定されているため、DBMS_STATS
は、統計が失効している状態を無視し、ロックされた統計を使用します。データベースでは、このパーティションの既存の統計を使用してグローバル統計が増分的に維持されます。
関連項目:
DBMS_STATS.SET_TABLE_PREFS
についてさらに学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください
13.4 システム統計の手動収集
システム統計は、オプティマイザに対してハードウェア特性(I/OとCPUのパフォーマンスおよび使用率など)を記述します。
13.4.1 システム統計について
システム統計によってCPUおよび記憶域のパフォーマンスが測定され、オプティマイザでは計画を評価するときにこれらの入力を使用できます。
問合せが実行されるときにはCPUが消費されます。多くの場合、問合せによって、記憶域サブシステムのリソースも消費されます。一般的な問合せでは、計画ごとに、CPUとI/Oを消費する割合が異なる場合があります。オプティマイザは、コスト・メトリックを使用して、最も高速に実行されると見積もった計画を選択します。オプティマイザがCPUおよび記憶域の速度を認識している場合は、各代替計画のコストをより的確に判断できます。
次の図に、3つの使用可能な計画がある問合せを示します。プランごとに、使用するCPUおよびI/Oの量が異なります。この例では、オプティマイザはPlan 1に最も低いコストを割り当てています。
データベースは、インスタンスの初回起動時に、非作業負荷統計と呼ばれる重要なシステム統計を自動的に収集します。通常、これらの特性が変更されるのは、ハードウェア構成の一部がアップグレードされた場合のみです。
次の図は、パフォーマンスが高い記憶域を追加した後の同じデータベースを示しています。システム統計を収集することで、オプティマイザが記憶域のパフォーマンスを考慮できます。この例では、パフォーマンスが高い記憶域によって、Plan 2とPlan 3の相対コストが大幅に低くなっています。Plan 1は使用するI/Oが少ないため、パフォーマンスの向上はごくわずかです。ここでは、Plan 3に最も低いコストが割り当てられています。
高速I/Oインフラストラクチャを備えたシステムでは、システム統計によって、問合せが索引アクセス方式よりも表スキャンを選択する可能性が高くなります。
13.4.2 システム統計収集のガイドライン
手動で収集する十分な理由がないかぎり、システム統計のデフォルトを使用することをお薦めします。
システム統計は、データベースで実行されるすべてのSQL文に影響を及ぼすため、パフォーマンスにとって重要です。システム統計を変更すると、予期しない、または望ましくない方法で、SQL実行計画が変更される可能性があります。そのため、システム統計を変更する前にオプションを慎重に検討することをお薦めします。
システム統計の手動収集を検討する場合
Oracle Exadataを使用し、かつ、データベースが純粋なデータ・ウェアハウス負荷を実行している場合は、表スキャンの方が優先度が高いため、EXADATA
オプションを使用してシステム統計を収集することで、パフォーマンスが向上する可能性があります。ただし、Exadataでも、ほとんどの作業負荷にデフォルトが最適です。
Oracle Exadataを使用しない場合に、システム統計を手動で収集するときには、次のことをお薦めします。
-
CPUの高速化、メモリーの増設、ディスク記憶域の交換など、環境に物理的な変化が生じたときにシステム統計を収集します。他のどの表領域でも使用されていない記憶域に新しい表領域を作成した場合は、非作業負荷統計を収集することをお薦めします。
-
システムの作業負荷が最も一般的であるときに統計を取得します。作業負荷統計の収集には、さらなるオーバーヘッドは発生しません。
デフォルト統計の使用を検討する場合
ほとんどの場合、システム統計のデフォルトを使用することをお薦めします。システム統計をデフォルト値に戻すには、DBMS_STATS.DELETE_SYSTEM_STATS
を実行し、データベースを停止した後、再度オープンします。適切なデフォルトが確実に使用されるようにするには、新しく作成したデータベースでもこのステップを実行することをお薦めします。
13.4.3 DBMS_STATSを使用したシステム統計の収集
システム統計を手動で収集するには、DBMS_STATS.GATHER_SYSTEM_STATS
プロシージャを使用します。
13.4.3.1 GATHER_SYSTEM_STATSプロシージャについて
DBMS_STATS.GATHER_SYSTEM_STATS
プロシージャは、指定された期間のアクティビティを分析するか(作業負荷統計)、作業負荷をシミュレートします(非作業負荷統計)。
DBMS_STATS.GATHER_SYSTEM_STATS
への入力引数には次があります。
-
NOWORKLOAD
オプティマイザによって、作業負荷とは無関係に、システムの特性のみに基づいた統計が収集されます。
-
INTERVAL
指定された時間(分)が経過すると、オプティマイザによって、データ・ディクショナリまたは代替の表のいずれか(
stattab
で指定)のシステム統計が更新されます。統計は、指定された時間間隔のシステム・アクティビティに基づきます。 -
START
およびSTOP
START
は統計収集を初期化します。STOP
は、(START
以降の)経過期間の統計を算出し、データ・ディクショナリまたは代替の表(stattab
で指定)をリフレッシュします。オプティマイザではINTERVAL
は無視されます。 -
EXADATA
システム統計では、大きなI/Oサイズや高いI/Oスループットなど、Exadataを使用することで提供されるすぐれた機能が考慮されます。オプティマイザでは、CPU速度に加えて、マルチブロック読取りカウントやI/Oスループットの統計が設定されます。
次の表に、DBMS_STATS
で収集されるオプティマイザのシステム統計と、特定のシステム統計を収集または手動設定するためのオプションを示します。
表13-4 DBMS_STATSパッケージ内のオプティマイザのシステム統計
パラメータ名 | 説明 | 初期化 | 統計の収集または設定のオプション | 単位 |
---|---|---|---|---|
|
作業負荷のない場合のCPU速度を表します。CPU速度は、1秒当たりのCPU平均サイクル数です。 |
システム起動時 |
|
100万/秒 |
|
ディスク・ヘッドがデータの読取り位置に移動する時間を表します。I/Oシーク時間は、シーク時間、待機時間およびオペレーティング・システム・オーバーヘッド時間を合計したものです。 |
システム起動時 10(デフォルト) |
|
ミリ秒 |
|
1回の読取りリクエストでOracleデータベースによるデータの読取りが可能な速度を表します。 |
システム起動時 4096(デフォルト) |
|
バイト/ミリ秒 |
|
作業負荷をかけた場合のCPU速度を表します。CPU速度は、1秒当たりのCPU平均サイクル数です。 |
なし |
|
100万/秒 |
|
最大I/Oスループットは、I/Oサブシステムが発揮できる最大スループットです。 |
なし |
|
バイト/秒 |
|
スレーブI/Oスループットは、パラレル実行サーバーの平均I/Oスループットです。 |
なし |
|
バイト/秒 |
|
単一ブロック読取り時間は、単一ブロックをランダムに読み取る平均時間です。 |
なし |
|
ミリ秒 |
|
マルチブロック読取りは、マルチブロックを順に読み取る平均時間です。 |
なし |
|
ミリ秒 |
|
マルチブロック・カウントは、平均マルチブロック順次読取りカウントです。 |
なし |
|
ブロック |
関連項目:
システム統計を収集および削除するためのDBMS_STATS
パッケージのプロシージャの詳細は、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください
13.4.3.2 作業負荷統計の収集
データベースの作業負荷が最も一般的であるときにDBMS_STATS.GATHER_SYSTEM_STATS
を使用して統計を取得することをお薦めします。
たとえば、データベースのアプリケーションで日中にOLTPトランザクションを処理し、夜間にOLAPレポートを生成できます。
13.4.3.2.1 作業負荷統計について
作業負荷統計は指定時間内のアクティビティを分析します。
作業負荷統計には、表13-4にリストされている次の統計が含まれています。
-
単一ブロック(
sreadtim
)およびマルチブロック(mreadtim
)の読取り時間 -
マルチブロック・カウント(
mbrc
) -
CPU速度(
cpuspeed
) -
最大システム・スループット(
maxthr
) -
パラレル実行の平均スループット(
slavethr
)
データベースでは、作業負荷の開始から終了までの間の、物理的な順次およびランダム読取りの数を比較して、sreadtim
、mreadtim
およびmbrc
を計算します。これらの値は、バッファ・キャッシュが同期読取りリクエストを完了したときに変更されるカウンタを通して実装されます。
このカウンタはバッファ・キャッシュ内にあるため、これらにはI/O遅延のほかに、ラッチの競合およびタスク・スイッチングに関連する待機も含まれます。したがって、作業負荷統計は、作業負荷ウィンドウ中のシステム・アクティビティに依存します。システムがI/Oバウンド(ラッチ競合およびI/Oスループットの両方)の場合は、データベースで統計を使用した後に、統計によってI/O集中を低減化する計画が促進されます。
図13-4に示すように、作業負荷統計を収集する場合には、オプティマイザでは、作業負荷統計のために収集されたmbrc
値が使用されて、全表スキャンのコストが見積られます。
作業負荷統計を収集する際に、一連の作業負荷の間に表のスキャンが行われない(OLTPシステムではよく発生します)場合は、mbrc
値およびmreadtim
値がデータベースで収集されない可能性があります。しかし、全表スキャンはDSSシステムで頻繁に行われています。これらのスキャンは並列に実行され、バッファ・キャッシュを経由しない場合があります。このようなケースでは、索引検索でバッファ・キャッシュを使用するため、データベースでもsreadtim
が収集されています。
mbrc
またはmreadtim
値を収集できないか、または収集してもそれらを検証できない場合、sreadtim
およびcpuspeed
値が収集されていると、sreadtim
およびcpuspeed
のみがコスト計算に使用されます。この場合、オプティマイザは初期化パラメータDB_FILE_MULTIBLOCK_READ_COUNT
の値を使用して全表スキャンを見積ることができます。ただし、DB_FILE_MULTIBLOCK_READ_COUNT
が0
であるか、設定されていない場合は、オプティマイザで値8
を使用してコストを計算します。
作業負荷統計を収集するには、DBMS_STATS.GATHER_SYSTEM_STATS
プロシージャを使用します。GATHER_SYSTEM_STATS
プロシージャでは、経過した期間に応じた統計を使用してデータ・ディクショナリまたはステージング表をリフレッシュします。収集する期間を設定するには、次の手法のいずれかを使用します。
-
作業負荷ウィンドウの開始に
START
指定し、作業負荷ウィンドウの終了にSTOP
を指定します。 -
統計収集が自動的に停止する前に、
INTERVAL
とその時間間隔(分単位)を指定します。必要な場合は、GATHER_SYSTEM_STATS (gathering_mode=>'STOP')
を使用することでスケジュールよりも早く収集を終了できます。
関連項目:
DB_FILE_MULTIBLOCK_READ_COUNT
初期化パラメータについて学習するには、Oracle Databaseリファレンスを参照してください
13.4.3.2.2 システム統計収集の開始および停止
このチュートリアルでは、GATHER_SYSTEM_STATS
のSTART
パラメータとSTOP
パラメータを使用した作業負荷の時間間隔を設定する方法について説明します。
前提条件
このチュートリアルでは、次のことが前提となっています。
-
10 a.m.と11 a.m.の間の時間が毎日の作業負荷の標準時間です。
-
データ・ディクショナリにあるシステム統計を直接収集します。
STARTおよびSTOPを使用して作業負荷統計を収集するには:
-
SQL*Plusを起動し、管理者権限でデータベースに接続します。
-
統計収集を開始します。
たとえば、10 a.m.に次のプロシージャを実行して収集を開始します。
EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode => 'START' );
-
作業負荷を生成します。
-
統計収集を終了します。
たとえば、11 a.m.に次のプロシージャを実行して収集を終了します。
EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode => 'STOP' );
ここで、オプティマイザでは、作業負荷統計を使用して、日々の通常の作業負荷の間に有効な実行計画を生成できるようになります。
-
オプションで、システム統計を問い合せます。
たとえば、次の問合せを実行します。
COL PNAME FORMAT a15 SELECT PNAME, PVAL1 FROM SYS.AUX_STATS$ WHERE SNAME = 'SYSSTATS_MAIN';
関連項目:
DBMS_STATS.GATHER_SYSTEM_STATS
プロシージャについて学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください
13.4.3.2.3 指定された間隔でのシステム統計の収集
このチュートリアルでは、GATHER_SYSTEM_STATS
のINTERVAL
パラメータを使用した作業負荷の時間間隔を設定する方法について説明します。
前提条件
このチュートリアルでは、次のことが前提となっています。
-
データベースのアプリケーションで日中にOLTPトランザクションを処理し、夜間にOLAPレポートを実行します。代表的な統計を収集するには、日中の2時間と夜間の2時間で収集します。
-
workload_stats
という名前の表に統計を格納します。 -
収集された統計間の切替えを行います。
INTERVALを使用して作業負荷統計を収集するには:
-
SQL*Plusを起動して、本番データベースに管理者
dba1
として接続します。 -
本番統計を保持するための表を作成します。
たとえば、次のPL/SQLプログラムを実行して、ユーザー統計表
workload_stats
を作成します。BEGIN DBMS_STATS.CREATE_STAT_TABLE ( ownname => 'dba1' , stattab => 'workload_stats' ); END; /
-
JOB_QUEUE_PROCESSES
が0
でないことを確認して、DBMS_JOB
ジョブとOracle Schedulerジョブが実行されるようにします。ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 1;
-
日中の間に統計を収集します。
たとえば、次のプログラムを使用して2時間分の統計を収集します。
BEGIN DBMS_STATS.GATHER_SYSTEM_STATS ( interval => 120 , stattab => 'workload_stats' , statid => 'OLTP' ); END; /
-
夜の間に統計を収集します。
たとえば、次のプログラムを使用して2時間分の統計を収集します。
BEGIN DBMS_STATS.GATHER_SYSTEM_STATS ( interval => 120 , stattab => 'workload_stats' , statid => 'OLAP' ); END; /
-
日中または夜に、適切な統計をデータ・ディクショナリにインポートします。
たとえば、日中に次のプログラムを使用して、ステージング表からディクショナリにOLTP統計をインポートできます。
BEGIN DBMS_STATS.IMPORT_SYSTEM_STATS ( stattab => 'workload_stats' , statid => 'OLTP' ); END; /
たとえば、夜の間に次のプログラムを使用して、ステージング表からディクショナリにOLAP統計をインポートできます。
BEGIN DBMS_STATS.IMPORT_SYSTEM_STATS ( stattab => 'workload_stats' , statid => 'OLAP' ); END; /
関連項目:
DBMS_STATS.GATHER_SYSTEM_STATS
プロシージャについて学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください
13.4.3.3 非作業負荷統計の収集
非作業負荷統計ではI/Oシステムの特性を取得します。
デフォルトでは、Oracle Databaseで非作業負荷統計とCPUコスト・モデルが使用されます。非作業負荷統計の値は、インスタンスの初回起動時にデフォルトで初期化されます。また、DBMS_STATS.GATHER_SYSTEM_STATS
プロシージャを使用して、非作業負荷統計を手動で収集できます。
非作業負荷統計には、表13-4にリストされている次のシステム統計が含まれています。
-
I/O転送速度(
iotfrspeed
) -
I/Oシーク時間(
ioseektim
) -
CPU速度(
cpuspeednw
)
作業負荷統計と非作業負荷統計の主な違いは、収集方法にあります。非作業負荷統計ではすべてのデータ・ファイルに対してランダムな読取りを実行してデータを収集する一方で、作業負荷統計ではデータベース・アクティビティが発生した際に更新されるカウンタを使用します。作業負荷統計を収集する場合には、Oracle Databaseでは非作業負荷統計ではなく作業負荷統計が使用されます。
非作業負荷統計を収集するには、引数を含まないか、収集モードをnoworkload
に設定した状態でDBMS_STATS.GATHER_SYSTEM_STATS
を実行します。非作業負荷統計の収集プロセスの際に、I/Oシステムにオーバーヘッドが発生します。この収集プロセスは、I/Oのパフォーマンスおよびデータベースのサイズによって数秒から数分かかることがあります。
非作業負荷統計を収集した場合は、データベースでその情報が分析され、その整合性が検証されます。場合によっては、非作業負荷統計の値がデフォルト値のままになることがあります。再び統計を収集するか、またはSET_SYSTEM_STATS
を使用してその値をI/Oシステムの仕様に手動設定するかのいずれが可能です。
前提条件
このチュートリアルでは、非作業負荷統計を手動で収集するものとします。
非作業負荷統計を手動収集するには:
-
SQL*Plusを起動し、管理者権限でデータベースに接続します。
-
非作業負荷統計を収集します。
たとえば、次の文を実行します。
BEGIN DBMS_STATS.GATHER_SYSTEM_STATS ( gathering_mode => 'NOWORKLOAD' ); END;
-
オプションで、システム統計を問い合せます。
たとえば、次の問合せを実行します。
COL PNAME FORMAT a15 SELECT PNAME, PVAL1 FROM SYS.AUX_STATS$ WHERE SNAME = 'SYSSTATS_MAIN';
関連項目:
DBMS_STATS.GATHER_SYSTEM_STATS
プロシージャについて学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください
13.4.4 システム統計の削除
DBMS_STATS.DELETE_SYSTEM_STATS
プロシージャはシステム統計を削除します。
このプロシージャではINTERVAL
またはSTART
およびSTOP
の各オプションを使用して収集された作業負荷統計が削除され、デフォルト値が非作業負荷統計にリセットされます。ただし、stattab
パラメータで統計を格納する表が指定されている場合は、サブプログラムによって、関連するstatid
を持つすべてのシステム統計が統計表から削除されます。
データベースを新しく作成した場合は、システム統計を削除し、データベースを停止した後、データベースを再度オープンすることをお薦めします。この一連のステップによって、システム統計の適切なデフォルトがデータベースで確実に設定されます。
前提条件
このチュートリアルでは、次のことが前提となっています。
-
特定の集中型の作業負荷の統計を収集しましたが、オプティマイザでそれらの統計が使用されないようにします。
-
ユーザーが指定した表ではなく、デフォルトの場所に作業負荷統計が格納されています。
システム統計を削除するには:
-
SQL*Plusで、管理権限を持つユーザーとしてデータベースにログインします。
-
システム統計を削除します。
たとえば、次の文を実行します。
EXEC DBMS_STATS.DELETE_SYSTEM_STATS;
関連項目:
DBMS_STATS.DELETE_SYSTEM_STATS
プロシージャについて学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください
13.5 レポート作成モードでの統計収集関数の実行
DBMS_STATS
統計収集プロシージャをレポート作成モードで実行できます。
REPORT_*
プロシージャを使用した場合、オプティマイザは実際には統計を収集しません。かわりに、指定された統計収集関数を使用した場合に処理されるオブジェクトがレポートされます。
次の表に、DBMS_STATS.REPORT_GATHER_*_STATS
ファンクションを示します。追加パラメータのdetail_level
とformat
を使用すると、すべてのファンクションで、入力パラメータが、対応するGATHER_*_STATS
プロシージャと同じものになります。サポートされている形式は、XML
、HTML
およびTEXT
です。
表13-5 DBMS_STATSレポート作成モード・ファンクション
ファンクション | 説明 |
---|---|
|
レポート作成モードで |
|
レポート作成モードで |
|
レポート作成モードで |
|
レポート作成モードで |
|
レポート作成モードで |
|
レポート作成モードで自動統計収集ジョブを実行します。このプロシージャでは実際に統計は収集されませんが、ジョブを実行することで影響を受ける可能性があるすべてのオブジェクトがレポートされます。 |
前提条件
このチュートリアルでは、oe
スキーマのGATHER_SCHEMA_STATS
を実行することで影響を受ける可能性があるオブジェクトのHTMLレポートを生成するものとします。
GATHER_SCHEMA_STATSの実行によって影響を受けるオブジェクトのレポートを作成するには:
-
SQL*Plusを起動し、管理者権限でデータベースに接続します。
-
DBMS_STATS.REPORT_GATHER_SCHEMA_STATS
ファンクションを実行します。たとえば、SQL*Plusで次のコマンドを実行します。
SET LINES 200 PAGES 0 SET LONG 100000 COLUMN REPORT FORMAT A200 VARIABLE my_report CLOB; BEGIN :my_report :=DBMS_STATS.REPORT_GATHER_SCHEMA_STATS( ownname => 'OE' , detail_level => 'TYPICAL' , format => 'HTML' ); END; /
次の図は、サンプル・レポートの一部を示しています。
関連項目:
DBMS_STATS
についてさらに学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください