動的統計（動的サンプリング）概要(OPTIMIZER_DYNAMIC_SAMPLING)

対応バージョン

Oracle Database 10.1 - 12.1

2017-04-06可読性の改善のため一部文言を修正

2016-06-28記事公開

ここでは動的統計の概要について記載します。
9iから追加された機能で、12cより動的サンプリングから動的統計と名前が変わりました。

動的統計とは

動的統計とは、表の統計情報が利用できない場合にサンプルデータを取得し、適切な実行計画を生成するための機能です。
統計情報が利用できない例として、統計情報の取得がされておらずオプティマイザ統計が存在しない、統計情報が失効している、取得した統計情報が不十分な場合などが考えられます。
動的統計が実行されると、ハード解析(※1)の間に再帰的SQL(※2)が自動実行されて、表ブロックのランダム・サンプルがスキャンされます。

参考資料（ハード解析時に動的統計が実行される旨の記載）
http://www.oracle.com/technetwork/jp/articles/chapter4-3-093504-ja.html#p01d
門外不出のOracle現場ワザ
第4章　Oracleデータベースの頭脳「オプティマイザ」徹底研究

※1 ハード解析
SQL文を実行する際に、SQL文を処理可能な形のデータ構造に分解することを解析（パース）と呼びます。
SQL文の文法などのチェック後、メモリ上（共有プール）の解析結果を利用する場合をソフト解析と呼びます。メモリ上に解析結果がない場合ハード解析を行います。
ハード解析ではオプティマイザが実行計画を生成し、行ソース・ツリーを生成します。行ソース・ツリーは表の参照順序や結合方法などの情報のことで、人が実行計画の分析をするときは行ソース・ツリーの形で目にすると思います。ハード解析の結果はメモリ上に保存され、ソフト解析に利用されます。
ハード解析と比較して、ソフト解析のほうが実行コストは低く高速です。

※2 再帰的SQL
再帰的SQLとは、ユーザーが発行したSQL文を実行するために、データベース内部で追加発行が必要になるSQL文のことを指します。
ハード解析時に動的統計が実行された場合、データベース内部ではサンプルデータ取得用のSQL文が実行されます。このように内部で自動的にコールされるSQL文のことを再帰的SQLと呼びます。

参考資料（ハード解析についてさらに詳しく知りたい方は参考になります）
https://docs.oracle.com/cd/E57425_01/121/TGSQL/tgsql_sqlproc.htm
Oracle(R) Database SQLチューニング・ガイド
12c リリース1 (12.1)
B71277-06
3 SQLの処理

サンプリングレベル

サンプリングレベルの動作の違い

動的統計ではサンプリングレベルの指定ができます。0～11まで存在し、10g以降のデフォルトではサンプリングレベル 2 となっています。0は動的統計の無効化を意味します。
10g以降ではサンプリングレベルが10まで追加され、12cよりサンプリングレベル11が追加されています。

サンプリングレベル10まで

サンプリングレベル1～10までは、サンプリングレベルごとの一定の条件に従い動的統計が実行されるか決まります。また、サンプル対象のブロック数についてもレベルごとに決まります。
サンプリングレベルの数値が高いほど動的統計が実行される対象のSQLが増え、サンプリングされるブロック数も増えます。そのため、ハード解析時の時間増大につながり、SQLレスポンスの遅延につながることがあります。サンプリングレベルが低い場合は、サンプリングのブロック数が少なくなりますが、実行計画が適切に生成されずにSQL実行が遅延するようなことも考えられます。
このように、システムに最適な動的統計のサンプルサイズを一律で設定することは困難です。

サンプリングレベル11

サンプリングレベル11では、オプティマイザで動的統計を使用するかの判断が自動で行われます。サンプル対象のブロック数についても自動で決定されます。さらにサンプリング結果が永続化されることで、動的統計のサンプリング結果が他のSQLへ反映されます。

サンプリングレベルごとの動作一覧については、以下のマニュアルが参考になります。

http://docs.oracle.com/cd/E49329_01/server.121/b71277/tgsql_astat.htm#CHDFEHGA
Oracle Database SQLチューニング・ガイド
12cリリース1(12.1)
B71277-02
表13-1 動的統計レベル

サンプリングレベルの変更

サンプリングレベルの変更は、OPTIMIZER_DYNAMIC_SAMPLING初期化パラメータを使用して行います。オンラインでの変更が可能です。

SQL> alter system set OPTIMIZER_DYNAMIC_SAMPLING = 4 scope=both;

System altered.

SQL> alter system set OPTIMIZER_DYNAMIC_SAMPLING = 4 scope=both;

System altered.

上記ではデータベース全体に設定していますが、セッションごとでも変更可能です。

SQL> -- テスト用にデータを準備します。
SQL> create table TEST_DBA_TABLES tablespace USERS as select * from DBA_TABLES;
SQL> create index TEST_DBA_TABLES_IND ON TEST_DBA_TABLES(OWNER) tablespace USERS; 
SQL> insert into TEST_DBA_TABLES select * from dba_tables;
SQL> insert into TEST_DBA_TABLES select * from dba_tables;
SQL> insert into TEST_DBA_TABLES select * from dba_tables;
SQL> insert into TEST_DBA_TABLES select * from dba_tables;
SQL> insert into TEST_DBA_TABLES select * from dba_tables;
SQL> commit;
SQL>
SQL> -- サンプリングレベルを 4 に設定します。
SQL> alter session set OPTIMIZER_DYNAMIC_SAMPLING = 4;
SQL>
SQL> set autotrace on
SQL> alter system flush shared_pool;
SQL> select  count(*)
  2    from test.TEST_DBA_TABLES DT
  3   where OWNER = 'SYS'
  4     and TABLESPACE_NAME = 'SYSTEM';

  COUNT(*)
----------
      5187
....
Note
-----
   - dynamic statistics used: dynamic sampling (level=4)
....

SQL> -- テスト用にデータを準備します。

SQL> create table TEST_DBA_TABLES tablespace USERS as select * from DBA_TABLES;

SQL> create index TEST_DBA_TABLES_IND ON TEST_DBA_TABLES(OWNER) tablespace USERS;

SQL> insert into TEST_DBA_TABLES select * from dba_tables;

SQL> commit;

SQL>

SQL> -- サンプリングレベルを 4 に設定します。

SQL> alter session set OPTIMIZER_DYNAMIC_SAMPLING = 4;

SQL>

SQL> set autotrace on

SQL> alter system flush shared_pool;

SQL> select count(*)

2 from test.TEST_DBA_TABLES DT

3 where OWNER = 'SYS'

4 and TABLESPACE_NAME = 'SYSTEM';

COUNT(*)

----------

5187

....

Note

-----

- dynamic statistics used: dynamic sampling (level=4)

....

ヒント句による指定

DYNAMIC_SAMPLINGヒント句でのサンプリングレベルの指定も可能です。
例えば以下の文では、サンプリングレベル0と2で動的統計を取ります。指定可能なサンプリングレベルは0から10となります。11の指定はできません。

SQL> set autotrace on
SQL> alter system flush shared_pool;
SQL> -- サンプリングレベル 0 （動的統計無し）で実行します。
SQL> select /*+ DYNAMIC_SAMPLING(DT 0) */ count(*)
  2    from test.TEST_DBA_TABLES DT
  3   where OWNER = 'SYS'
  4     and TABLESPACE_NAME = 'SYSTEM';

※ dynamic statistics used: dynamic sampling の記載が出力されません。

SQL> -- サンプリングレベル 2 で実行します。
SQL> alter system flush shared_pool;
SQL> select /*+ DYNAMIC_SAMPLING(DT 2) */ count(*)
  2    from test.TEST_DBA_TABLES DT
  3   where OWNER = 'SYS'
  4     and TABLESPACE_NAME = 'SYSTEM';

.....
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
.....

SQL> set autotrace on

SQL> alter system flush shared_pool;

SQL> -- サンプリングレベル 0 （動的統計無し）で実行します。

SQL> select /*+ DYNAMIC_SAMPLING(DT 0) */ count(*)

2 from test.TEST_DBA_TABLES DT

3 where OWNER = 'SYS'

4 and TABLESPACE_NAME = 'SYSTEM';

※ dynamic statistics used: dynamic sampling の記載が出力されません。

SQL> -- サンプリングレベル 2 で実行します。

SQL> alter system flush shared_pool;

SQL> select /*+ DYNAMIC_SAMPLING(DT 2) */ count(*)

2 from test.TEST_DBA_TABLES DT

3 where OWNER = 'SYS'

4 and TABLESPACE_NAME = 'SYSTEM';

.....

Note

-----

- dynamic statistics used: dynamic sampling (level=2)

.....

サンプリングレベル2なので、表の統計情報が既に取得されていた場合は、既存の統計情報を使用して動的統計は実行されません。

ALTER SESSIONを使用した場合は特に問題なく動作しましたが、ヒント句による指定の場合はサンプリングレベルの指定がうまく動作しませんでした。動作検証は12cR1(12.1.0.2)で行っています。
サンプリングレベル0を指定した場合は動的統計が無効となりましたが、1以上を指定した場合は、指定した値にかかわらず初期化パラメータのサンプリングレベルでメッセージ出力されていました。実装する場合は十分に動作検証したのち実装を行ってください。

参考資料
https://docs.oracle.com/cd/E60665_01/db121/SQLRF/sql_elements006.htm#BABDCGAA
Oracle Database SQL言語リファレンス
12cリリース1 (12.1)
B71278-08
コメント
DYNAMIC_SAMPLINGヒント

動的統計の無効化と推奨

レスポンス要件が厳しいOLTP処理では、サンプリングレベルを0に設定して動的統計を無効化することがOracleのベストプラクティス（効果的、最善の方法）となります。（デフォルトの動的統計のサンプリングレベルは2です。）
例えばレスポンス要件が0.1秒などの場合は、動的統計のサンプリング用SQLの時間だけで要件の時間を超えてしまうようなことが起きえます。動的統計を無効化することで、統計情報失効のタイミングで動的統計が実行されてしまいSQLが遅くなるなどの対策となります。
OLTP処理が重要なシステムでは動的統計を無効としておき、SQLごとに動的統計実装の判断をすることで、全体的にOLTPの実行が遅い事象などの抑止につながることも考えられます。

また、動的統計が有効の場合はハード解析のタイミングで機能が動作するため、ハード解析が多く実行されるSQLは影響が出やすいことに留意してください。

OLTP処理でハード解析が多く実行される例として、動的に取得したユーザーIDなどの値をSQL文に直接記載してデータを取得している場合などがあります。
このようにバインド変数化などがされておらず、毎回ハード解析が必要となり、その度に動的統計が実行される場合などは、同じテーブルに対して何回もサンプリングを行うような状況となることがあります。

参考資料
http://docs.oracle.com/cd/E49329_01/server.121/b71277/tgsql_astat.htm#CHDFHIAE
Oracle Database SQLチューニング・ガイド
12cリリース1(12.1)
B71277-02
動的統計の無効化

動的統計（動的サンプリング）概要(OPTIMIZER_DYNAMIC_SAMPLING)

動的統計とは

サンプリングレベル

サンプリングレベルの動作の違い

サンプリングレベル10まで

サンプリングレベル11

サンプリングレベルの変更

ヒント句による指定

動的統計の無効化と推奨

関連記事