Oracle Database 12c で OPTIMIZER_DYNAMIC_SAMPLING=11 をセットすると、Dynamic Sampling の 結果 が RESULT CACHE に格納されて、異なるSQL で 共有される。

表題の通り、Dynamic Sampling を 11 にセットすると、Dynamic Sampling の 結果 が Result Cache に格納されて、異なるSQL(sql_id が 異なる SQL) で Dynamic Dampling の 結果が共有されるんやで。彡(ﾟ)(ﾟ)

従来の動作
SQLの実行計画作成時(Hard Parse時)に Dynamic Sampling が動作する。
※Dynamic Samplingは実行計画作成の都度動作する。

Dynamic Sampling = 11 の動作

1. SQLの実行計画作成時(Hard Parse時)に Dynamic Sampling が動作する。
2. Dynamic Sampling の 結果 は Result Cache に 格納される。
3. 異なるSQL でも Dynamic Sampling は 動作するが、Result Cache に
   　 格納された結果を参照するため、負荷は低い。

検証してみるやで。まずはデータのセットアップ。

CREATE TABLE TBL_A AS SELECT LEVEL AS C1 FROM DUAL CONNECT BY LEVEL <= 1000000;
ALTER TABLE TBL_A ADD CONSTRAINT TBL_A_PK PRIMARY KEY(C1) USING INDEX;
EXEC DBMS_STATS.DELETE_TABLE_STATS(USER, 'TBL_A');
SET LINESIZE 170;
COLUMN TABLE_NAME FORMAT A30;
COLUMN INDEX_NAME FORMAT A30;
SELECT TABLE_NAME, NUM_ROWS FROM USER_TAB_STATISTICS WHERE TABLE_NAME = 'TBL_A';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
TBL_A                                     ★統計はNULL

SELECT TABLE_NAME, INDEX_NAME, NUM_ROWS FROM USER_IND_STATISTICS WHERE TABLE_NAME = 'TBL_A';

TABLE_NAME                     INDEX_NAME                       NUM_ROWS
------------------------------ ------------------------------ ----------
TBL_A                          TBL_A_PK                                  ★統計はNULL

DYNAMIC SAMPLING=11 を 仕込んで、トレース採りながらSQLを実行すると、、、

VAR B1 NUMBER;
EXEC :B1 := 100;
ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING=11;
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'AYSHIBAT';
EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE);
SELECT COUNT(*) FROM TBL_A WHERE C1 <= :B1;
EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE;

下記のように、result_cacheヒント付きの内部SQLが走るんやで彡(ﾟ)(ﾟ)

：
SQL ID: abq7rvtukthra Plan Hash: 1525449891

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring 
  optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) ★result_cacheヒントが付いている。
  */ SUM(C1) 
FROM
 (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "TBL_A")  */ 1 AS C1 FROM 
  "TBL_A" SAMPLE BLOCK(51.3809, 8) SEED(1)  "TBL_A" WHERE ("TBL_A"."C1"<=:B1))
   innerQuery


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1
：

違うSQLを動かしてみると、、、

VAR B1 NUMBER;
EXEC :B1 := 100;
ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING=11;
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'AYSHIBAT';
EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE);
SELECT /* dummy */ COUNT(*) FROM TBL_A WHERE C1 <= :B1; ★ダミーのコメントを付けている。
EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE;

このSQLで内部的に動作する Dynamic Sampling の トレース は以下の通りで、
ブロック読込が発生していないんやで彡(ﾟ)(ﾟ)

：
SQL ID: abq7rvtukthra Plan Hash: 1525449891

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring 
  optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) 
  */ SUM(C1) 
FROM
 (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "TBL_A")  */ 1 AS C1 FROM 
  "TBL_A" SAMPLE BLOCK(51.3809, 8) SEED(1)  "TBL_A" WHERE ("TBL_A"."C1"<=:B1))
   innerQuery


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          0          0           1
                                                      ^^^ブロック読込が 3⇒0に減っている。
：

この内部SQLの実行計画をDBMS_XPLAN.DISPLAY_CURSORで抜いてみると……

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'abq7rvtukthra', format => 'ALL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  abq7rvtukthra, child number 0
-------------------------------------
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring
optimizer_features_enable(default) no_parallel
result_cache(snapshot=3600) */ SUM(C1) FROM (SELECT /*+
qb_name("innerQuery") NO_INDEX_FFS( "TBL_A")  */ 1 AS C1 FROM "TBL_A"
SAMPLE BLOCK(51.3809, 8) SEED(1)  "TBL_A" WHERE ("TBL_A"."C1"<=:B1))
innerQuery

Plan hash value: 1525449891

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |       |       |     2 (100)|          |
|   1 |  RESULT CACHE      | 4bbkkn6kfr0f639rwfzqbccvrf |       |       |            |          | ★RESULT CACHE が使われている。
|   2 |   SORT AGGREGATE   |                            |     1 |    25 |            |          |
|*  3 |    INDEX RANGE SCAN| TBL_A_PK                   |  3267 | 81675 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
：

RESULT CACHE が使われているやね彡(^)(^)

マニュアルもどうぞ(｀･ω･)ゞ

Oracle Database SQLチューニング・ガイド 12cリリース1 (12.1) B71277-05
動的統計レベルについて
http://docs.oracle.com/cd/E57425_01/121/TGSQL/tgsql_astat.htm#GUID-DEE2AF8B-5F4B-4FE7-9F0E-7D188921EBCC
：
11 オプティマイザが必要と判断した場合は、自動的に動的統計が使用されます。
結果の統計は統計リポジトリ内で永続的であり、他の問合せに対しても利用できます。