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

対応バージョン

Oracle Database 10.1 – 12.1

更新履歴

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

動的統計とは

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

参考資料（ハード解析時に動的統計が実行される旨の記載）
https://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-09
3 SQLの処理

サンプリングレベル

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

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

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

サンプリングレベル1～10までは、サンプリングレベルごとの一定の条件に従い動的統計が実行されるか決まります。また、サンプル対象のブロック数についてもレベルごとに決まります。

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

サンプリングレベル11

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

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

https://docs.oracle.com/cd/E57425_01/121/TGSQL/tgsql_astat.htm#GUID-DEE2AF8B-5F4B-4FE7-9F0E-7D188921EBCC
Oracle Database SQLチューニング・ガイド
12cリリース1(12.1)
B71277-09
表13-1 動的統計レベル

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

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

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)
....

ヒント句による指定

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)
.....