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 オプティマイザが必要と判断した場合は、自動的に動的統計が使用されます。 結果の統計は統計リポジトリ内で永続的であり、他の問合せに対しても利用できます。
Qiitaにも書いたやで彡(ﾟ)(ﾟ)
Oracle Database 12c で OPTIMIZER_DYNAMIC_SAMPLING=11 をセットすると、 Dynamic Sampling の 結果 が RESULT CACHE に格納されて、異なるSQL で 共有される。 http://qiita.com/ora_gonsuke777/items/bdc2fbb1f9138f8f7df8
