統計情報の収集および公開の個別実施
このチュートリアルでは、プリファレンスを変更して、統計情報を個別に収集および公開する方法について説明します。
約30分
このチュートリアルでは、以下のトピックについて説明します。
 このアイコンの上にカーソルを置くと、すべてのスクリーンショットがロードし、表示されます。 （警告： すべてのスクリーンショットが同時にロードされるため、ご使用のインターネット接続によってはレスポンス・タイムが遅くなる場合があります。）
注： 各手順に関連したスクリーンショットのみを表示する場合は、それぞれの手順にあるアイコンの上にカーソルを置いてください。 スクリーンショットをクリックすると、非表示になります。
Oracle Database 11gより前のリリースでは、オプティマイザ統計を収集した場合、収集が完了すると統計情報は自動的に公開されていました。 しかし、Oracle Database 11gでは、統計の収集と公開を別々に実行できます。 これらのプロセスを切り離すことにより、新しく収集した統計を公開する前にテストできるようになります。 このOBEチュートリアルでは、所定の表に存在する公開統計と保留統計を確認し、統計を公開せずに収集する方法および保留統計の公開方法について学習します。 また、保留統計を公開する前にテストし、条件を満たしているか確認します。
トピック・リストに戻る
このチュートリアルを始める前に、次の手順を完了している必要があります。
| 1. |
Oracle Database 11gをインストールし、SHユーザーのロックが解除されていることを確認します。 |
| 2. |
stats.zipファイルを作業ディレクトリにダウンロードして解凍します。 |
| 3. |
CUSTOMERS_OBE表を作成し、データをロードする必要があります。 端末ウィンドウを開いて、次のコマンドを実行します。
cd <sql_files_directory>
imp sh/sh file= customers_obe.dmp log=imp.log full=y
|
トピック・リストに戻る
ここでは、統計収集用のプリファレンスまたはパラメータのデフォルト設定を変更します。 考慮する必要のあるパラメータの1つに、STALE_PERCENTがあります。 STALE_PERCENTは、表内でどれだけの割合の行が変更されたら、表の統計が古くなり再収集が必要になるかを決定するパラメータです。 以下の手順を実行します。
| 1. |
まず、デフォルト設定を確認します。 端末ウィンドウを開いて、次のコマンドを実行します。
cd <sql_files_directory>
sqlplus /nolog
set echo on
@check_sales_pref
check_sales_pref.sqlコードの内容は、次のとおりです。
connect sh/sh
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent
from dual;
|
| 2. |
SALES表に設定されているデフォルト値は、10%です。 データベース全体にパラメータやプリファレンスを変更するには、DBA権限が必要です。 sysdbaとして接続したら、データベース全体にSTALE_PERCENTを13%に設定します。 次に、STALE_PERCENTを再度確認します。 端末ウィンドウから、次のSQLスクリプトを実行します。
@change_global_pref
@check_sales_pref
change_global_pref.sqlコードとcheck_sales_pref.sqlコードの内容は、次のとおりです。
connect / as sysdba
execute dbms_stats.set_global_prefs('STALE_PERCENT', '13');
connect sh/sh
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent
from dual;
|
| 3. |
ここまでで、SALES表のSTALE_PERCENTは、グローバル値と同じ13%に設定されました。 1つの表に対するSTALE_PERCENTのみを変更する場合は、どのようにすれば良いでしょうか。 この場合は、set_table_prefsプロシージャを使用します。 SALES表のSTALE_PERCENTを65%に設定します。 このスキーマに属する1つの表のみを変更するため、sysdbaとしてログインする必要はありません。 次に、STALE_PERCENTを再度確認します。 端末ウィンドウから、次のSQLスクリプトを実行します。
@change_table_pref
@check_sales_pref
change_table_pref.sqlコードとcheck_sales_pref.sqlコードの内容は、次のとおりです。
execute dbms_stats.set_table_prefs('SH', 'SALES', 'STALE_PERCENT', '65');
connect sh/sh
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent
from dual;
|
| 4. |
SALES表のSTALE_PERCENTは65%に変更されましたが、このスキーマに含まれるその他の表はどうなっているでしょうか。 PRODUCTS表のSTALE_PERCENTを確認しましょう。新たなグローバル・デフォルト値である13%に設定されているはずです。 端末ウィンドウから、次のSQLスクリプトを実行します。
@check_products_pref
check_products_pref.sqlコードの内容は、次のとおりです。
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'PRODUCTS') stale_percent
from dual;
|
| 5. |
次に、すべてをリセットしてデフォルトに戻します。 まず、SALES表に設定した表プリファレンスを削除します。これにより、この表にグローバルのデフォルト値が適用されます。 端末ウィンドウから、次のSQLスクリプトを実行します。
@reset_table_prefs
@check_sales_pref
reset_table_prefs.sqlコードとcheck_sales_pref.sqlコードの内容は、次のとおりです。
execute dbms_stats.delete_table_prefs('SH', 'SALES', 'STALE_PERCENT');
connect sh/sh
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent
from dual;
|
| 6. |
次に、グローバルのデフォルト値をリセットするために、sysdbaとして再接続します。 デフォルト値を覚えておく必要はありません。 プリファレンス値をNULLに設定するだけで、"出荷時"のデフォルト値がリストアされます。 端末ウィンドウから、次のSQLスクリプトを実行します。
@reset_global_prefs
@check_sales_pref
reset_table_prefs.sqlコードとcheck_sales_pref.sqlコードの内容は、次のとおりです。
connect / as sysdba
execute dbms_stats.set_global_prefs('STALE_PERCENT', null);
connect sh/sh
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent
from dual;
|
トピック・リストに戻る
ここでは、所定の表に存在する公開統計と保留統計を確認した後、統計を公開せずに収集する方法を学習します。 以下の手順を実行します。
| 1. |
まず、日付書式を変更してから、CUSTOMERS_OBE表の統計情報をリセットします。 端末ウィンドウから、次のスクリプトを実行します。
@reset_table_stats
reset_table_stats.sqlコードの内容は、次のとおりです。
connect sh/sh
alter session set nls_date_format='mm/dd hh24:mi:ss';
-- delete statistics
exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');
|
| 2. |
次に、CUSTOMERS_OBE表の公開統計を確認します。 端末ウィンドウから、次のコマンドを実行します。
@show_public_stats CUSTOMERS_OBE
show_public_stats.sqlコードの内容は、次のとおりです。
-- tables
select table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len
from user_tables
where table_name = '&1';
-- indexes
select index_name, last_analyzed ANALYZE_TIME, num_rows,
leaf_blocks, distinct_keys
from user_indexes
where table_name = '&1'
order by index_name;
-- columns
select column_name, last_analyzed ANALYZE_TIME, num_distinct,
num_nulls, density
from user_tab_columns
where table_name = '&1'
order by column_name;
|
| 3. |
次に、CUSTOMERS_OBE表の保留統計を確認します。 端末ウィンドウから、次のコマンドを実行します。
@show_pending_stats CUSTOMERS_OBE
show_pending_stats.sqlコードの内容は、次のとおりです。
-- tables
select table_name, last_analyzed "analyze time", num_rows, blocks, avg_row_len
from user_tab_pending_stats
where table_name = '&1' and partition_name is null;
-- indexes
select index_name, last_analyzed "analyze time", num_rows,
leaf_blocks, distinct_keys
from user_ind_pending_stats
where table_name = '&1' and partition_name is null
order by index_name;
-- columns
select column_name, last_analyzed "analyze time", num_distinct,
num_nulls, density
from user_col_pending_stats
where table_name = '&1' and partition_name is null
order by column_name;
|
| 4. |
現在、CUSTOMERS_OBE表には公開統計も保留統計も作成されていません。 Oracle Database 11gのデフォルト動作は以前と同じであり、統計の収集が完了次第、その統計は公開されます。 端末ウィンドウから次のスクリプトを実行して、プリファレンスを確認します。
@check_publish_prefs
check_publish_prefs.sqlコードの内容は、次のとおりです。
select dbms_stats.get_prefs('PUBLISH') publish from dual;
|
| 5. |
公開モードを表レベルで確認または変更することもできます。 CUSTOMERS_OBE表の公開プリファレンス値を確認します。 グローバルのデフォルト値と同じ値に設定されています。 端末ウィンドウから、次のSQLスクリプトを実行します。
@check_table_publish_prefs
check_table_publish_prefs.sqlコードの内容は、次のとおりです。
select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;
|
| 6. |
CUSTOMERS_OBE表の公開値をfalseに設定します。 これにより、今後収集される統計は自動的に公開されません。 端末ウィンドウから、次のSQLスクリプトを実行します。
@set_table_publish_prefs_false
set_table_publish_prefs_false.sqlコードの内容は、次のとおりです。
exec dbms_stats.set_table_prefs('SH', 'CUSTOMERS_OBE', 'PUBLISH', 'false');
|
| 7. |
次のスクリプトを実行して、先ほどの変更が有効になっていることを確認します。
@check_table_publish_prefs
check_table_publish_prefs.sqlコードの内容は、次のとおりです。
select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;
|
| 8. |
次に、以下のスクリプトを実行してCUSTOMERS_OBE表の統計を収集します。
@gather_table_stats
gather_table_stats.sqlコードの内容は、次のとおりです。
execute dbms_stats.gather_table_stats('SH', 'CUSTOMERS_OBE');
|
| 9. |
CUSTOMERS_OBE表の公開プリファレンスはfalseに設定されているため、統計収集タスクを実行した後も、この表の公開統計は存在しないはずです。 端末ウィンドウから、次のスクリプトを実行します。
@show_public_stats CUSTOMERS_OBE
show_public_stats.sqlコードの内容は、次のとおりです。
-- tables
select table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len
from user_tables
where table_name = '&1';
-- indexes
select index_name, last_analyzed ANALYZE_TIME, num_rows,
leaf_blocks, distinct_keys
from user_indexes
where table_name = '&1'
order by index_name;
-- columns
select column_name, last_analyzed ANALYZE_TIME, num_distinct,
num_nulls, density
from user_tab_columns
where table_name = '&1'
order by column_name;
|
| 10. |
次に、保留統計があるかどうかを確認します。 端末ウィンドウから、次のスクリプトを実行します。
@show_pending_stats CUSTOMERS_OBE
show_pending_stats.sqlコードの内容は、次のとおりです。
-- tables
select table_name, last_analyzed "analyze time", num_rows, blocks, avg_row_len
from user_tab_pending_stats
where table_name = '&1' and partition_name is null;
-- indexes
select index_name, last_analyzed "analyze time", num_rows,
leaf_blocks, distinct_keys
from user_ind_pending_stats
where table_name = '&1' and partition_name is null
order by index_name;
-- columns
select column_name, last_analyzed "analyze time", num_distinct,
num_nulls, density
from user_col_pending_stats
where table_name = '&1' and partition_name is null
order by column_name;
|
トピック・リストに戻る
CUSTOMERS_OBE表に関する統計情報は、すべて保留状態にあります。 新しい統計情報をテストするには、保留統計表から統計情報をエクスポートして、テスト・システムへインポートします。 統計情報が条件を満たさない場合、保留統計表からこれらを削除するだけで、本番に影響を与えずに済みます。 以下の手順を実行します。
| 1. |
次のスクリプトを実行して、保留統計の使用を無効化します。
@set_pending_stats_off
set_pending_stats_off.sqlコードの内容は、次のとおりです。
alter session set optimizer_use_pending_statistics = false;
alter session set optimizer_dynamic_sampling = 0;
|
| 2. |
次に、問合せに対するEXPLAIN PLANを取得して表示します。 端末ウィンドウから、次のスクリプトを実行します。
@get_execplan
get_execplan.sqlコードの内容は、次のとおりです。
explain plan for
select * from customers_obe where CUST_CREDIT_LIMIT=1500;
select plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));
この計画では、索引を参照した後、CUST_CREDIT_LIMIT=1500である表内のすべての行に対して単一行アクセスを行う必要があるため、最適な計画とは言えません。 表全体の20%以上の行で、CUST_CREDIT_LIMITの値に1500が設定されています。CUSTOMERS_OBE表のサイズが増加するに従って、問合せに一致する行の数も増加するため、この計画のパフォーマンスはさらに悪化します。
|
| 3. |
オプティマイザが保留統計表にある統計情報を使用した方が結果が向上するかどうかを確認するため、次のスクリプトを実行して、optimizer_use_pending_statisticsをtrueに設定します。
@set_optimizer_pending_stats_true
set_optimizer_pending_stats_true.sqlコードの内容は、次のとおりです。
alter session set optimizer_use_pending_statistics = true;
|
| 4. |
次に、問合せに対するEXPLAIN PLANを確認します。
@get_execplan
get_execplan.sqlコードの内容は、次のとおりです。
explain plan for
select * from customers_obe where CUST_CREDIT_LIMIT=1500;
select plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));
保留統計を使用した計画の方がはるかに良いことが分かります。 この場合、全表スキャンを行うことで、表に対する1度の処理で一致するすべての行を取得できます。 この計画が、本番で実行するべき計画です。
|
トピック・リストに戻る
ここでは、保留統計を公開します。 以下の手順を実行します。
| 1. |
統計情報が条件を満たす場合、次のスクリプトを実行してこの統計を公開します。
@publish_pending_stats
publish_pending_stats.sqlコードの内容は、次のとおりです。
exec dbms_stats.publish_pending_stats(null, null);
|
| 2. |
ここで、CUSTOMERS_OBE表の公開統計を確認すると、これまでに収集したすべての統計が表示されます。 端末ウィンドウから、次のスクリプトを実行します。
@show_public_stats CUSTOMERS_OBE
show_public_stats.sqlコードの内容は、次のとおりです。
-- tables
select table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len
from user_tables
where table_name = '&1';
-- indexes
select index_name, last_analyzed ANALYZE_TIME, num_rows,
leaf_blocks, distinct_keys
from user_indexes
where table_name = '&1'
order by index_name;
-- columns
select column_name, last_analyzed ANALYZE_TIME, num_distinct,
num_nulls, density
from user_tab_columns
where table_name = '&1'
order by column_name;
|
| 3. |
次に、CUSTOMERS_OBE表の保留統計を確認します。 端末ウィンドウから、次のスクリプトを実行します。
@show_pending_stats CUSTOMERS_OBE
show_pending_stats.sqlコードの内容は、次のとおりです。
-- tables
select table_name, last_analyzed "analyze time", num_rows, blocks, avg_row_len
from user_tab_pending_stats
where table_name = '&1' and partition_name is null;
-- indexes
select index_name, last_analyzed "analyze time", num_rows,
leaf_blocks, distinct_keys
from user_ind_pending_stats
where table_name = '&1' and partition_name is null
order by index_name;
-- columns
select column_name, last_analyzed "analyze time", num_distinct,
num_nulls, density
from user_col_pending_stats
where table_name = '&1' and partition_name is null
order by column_name;
統計情報は公開されたため、保留統計はなくなっています。
|
トピック・リストに戻る
このOBEチュートリアルで使用した統計情報とプリファレンスをリセットするには、以下の手順に従います。
| 1. |
まず、表の統計情報をリセットします。 端末ウィンドウから、次のスクリプトを実行します。
@delete_table_stats
delete_table_stats.sqlコードの内容は、次のとおりです。
exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');
|
| 2. |
次に、CUSTOMERS_OBE表のグローバル値をPUBLISHに設定します。 端末ウィンドウから、次のスクリプトを実行します。
@set_global_publish
set_global_publish.sqlコードの内容は、次のとおりです。
select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;
|
トピック・リストに戻る
このチュートリアルで学習した内容は、次のとおりです。
 |
グローバル・プリファレンスと表統計プリファレンスの変更 |
|
保留統計の収集 |
|
保留統計のテスト |
|
保留統計の公開 |
|
統計情報とプリファレンスのリセット |
トピック・リストに戻る
|