19c新機能検証の第3回になります。
今回は強化されたオプティマイザ統計メンテナンス機能の内、高頻度自動オプティマイザ統計収集について検証します。
この機能はExadataでのみ利用可能で、検証に使用した環境は以下の通りです。
まず、概略は以下の通りです。
・機能について
データが頻繁に変更される場合に古くなるオプティマイザ統計を改善。
データが頻繁に更新される場合でも手動で統計取集する必要がない。
- 自動オプティマイザ統計収集だけでは古い統計によりパフォーマンス問題になる可能性があった。
・処理概要
標準の統計収集ジョブを補完する（標準の統計収集ジョブ中は実行されない）
より頻繁に発生するように自動統計収集を構成できる。
- デフォルトでは、15分ごとに収集が行われる 。
高頻度タスクは「軽量」で、古い統計（10%を超える変更）のみを収集する。
以下のDBMS_STATS.SET_GLOBAL_PREFSパラメータでタスクの設定を行う。
- AUTO_TASK_STATUS（デフォルトはOFF）
- AUTO_TASK_MAX_RUN_TIME（デフォルトは3600秒
- AUTO_TASK_INTERVAL（デフォルトは900秒）
デフォルトでは無効になっているため、下記の方法で設定を有効にします。
デフォルト値を確認します。OFFになっていますね。
SQL> select dbms_stats.get_prefs('AUTO_TASK_STATUS') from dual;
DBMS_STATS.GET_PREFS('AUTO_TASK_STATUS')
--------------------------------------------------------------------------------
OFF
高頻度自動オプティマイザ統計収集を有効に設定します。
ONに変わりました。
SQL> exec DBMS_STATS.SET_GLOBAL_PREFS ('AUTO_TASK_STATUS','ON');
PL/SQL procedure successfully completed.
SQL> select dbms_stats.get_prefs('AUTO_TASK_STATUS') from dual;
DBMS_STATS.GET_PREFS('AUTO_TASK_STATUS')
--------------------------------------------------------------------------------
ON
準備が出来たので、実際に検証して行きます。
手動で統計情報取得処理を行い、所要時間を確認します。
SQL> exec dbms_stats.gather_table_stats('TEST','TEMP1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.74
SQL> exec dbms_stats.gather_table_stats('TEST','TEST3');
PL/SQL procedure successfully completed.
Elapsed: 00:01:46.65
SQL> exec dbms_stats.gather_table_stats('TEST','TEST2');
PL/SQL procedure successfully completed.
Elapsed: 00:02:04.39
統計を失効させる為、10%を超えるデータをバルクインサートします。
SQL>insert /*+ append */ into temp1 select * from t3 where rownum<= 1800001;
1800001 rows created.
Elapsed: 00:00:00.67
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL>insert /*+ append */ into test2 select * from t3 where rownum<= 4236201;
4236201 rows created.
Elapsed: 00:00:26.05
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> insert /*+ append */ into test3 select * from t3 where rownum<= 2179201;
2179201 rows created.
Elapsed: 00:00:24.38
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
統計が失効した事を確認します。stale_stats がYESになっています。
SQL> select table_name,last_analyzed,notes,stale_stats from user_
tab_statistics order by last_analyzed;
TABLE_NAME LAST_ANALYZED NOTES STALE_S
---------- ------------------- ------------------------- -------
TEMP1 2019/11/18 16:49:24 YES
TEST3 2019/11/18 16:49:41 YES
TEST2 2019/11/18 16:51:49 YES
デフォルトで15分おきに実行されるので、暫く待ってから確認しました。
統計が取得されています。
SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> select table_name,last_analyzed,notes,stale_stats from user_
tab_statistics order by last_analyzed;
TABLE_NAME LAST_ANALYZED NOTES STALE_S
---------- ------------------- ------------------------- -------
TEMP1 2019/11/18 17:01:12 NO
TEST3 2019/11/18 17:01:21 NO
TEST2 2019/11/18 17:03:18 NO
AWRレポートには高頻度自動統計関連と思われる処理はレポートされておらず、手動の統計情報取得処理より短時間で実行されていた事が判ります。
性能影響を確認するため、temp1,test2,test3にdeleteとバルク・インサートを継続的に実行し、高頻度自動統計が取得されていることを確認します。
STATS_UPDATE_TIMEが変化していっているのが判りますね。
SQL> select TABLE_NAME,STATS_UPDATE_TIME from USER_TAB_STATS_HISTORY order by 2
TABLE_NAME STATS_UPDATE_TIME
---------- ---------------------------------------------------------------------------
（略）
TEMP1 13-NOV-19 03.17.52.855723 PM +09:00
TEMP1 13-NOV-19 04.35.34.324448 PM +09:00
TEST2 13-NOV-19 04.36.05.426733 PM +09:00
TEST3 13-NOV-19 04.44.59.150675 PM +09:00
TEMP1 13-NOV-19 04.51.01.274805 PM +09:00
TEST2 13-NOV-19 04.52.17.338103 PM +09:00
TEST3 13-NOV-19 05.07.13.765934 PM +09:00
（略）
TEMP1 14-NOV-19 08.52.20.554842 AM +09:00
TEST2 14-NOV-19 08.53.44.536068 AM +09:00
TEST3 14-NOV-19 09.07.44.931617 AM +09:00
TEMP1 14-NOV-19 09.08.34.109170 AM +09:00
TEST3 14-NOV-19 10.33.41.733063 AM +09:00
TEST2 14-NOV-19 10.35.44.377472 AM +09:00
197 rows selected.
11/13 15:00-11/14 11:00までのAWRを確認しました。
高頻度自動統計関連の処理と思われるのは以下のプロシージャです。
/* KAPS */ dbms_stats.gather_st_job_continuous_proc;
また、上記処理の一部として以下のようなSQLが実行されているようです。
/* SQL Analyze(2) */ select
/*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0)
no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad */
to_char(count("COL2")), substrb(dump(min("COL2"), 16, 0, 64), 1, 240), substrb(dump(max("COL2"), 16, 0, 64), 1, 240),
to_char(count("COL3")), substrb(dump(min("COL3"), 16, 0, 64), 1, 240), substrb(dump(max("COL3"), 16, 0, 64), 1, 240),
to_char(count("COL4")), substrb(dump(min("COL4"), 16, 0, 64), 1, 240), substrb(dump(max("COL4"), 16, 0, 64), 1, 240),
to_char(count("COL5")), substrb(dump(min("COL5"), 16, 0, 64), 1, 240), substrb(dump(max("COL5"), 16, 0, 64), 1, 240),
to_char(count("COL6")), substrb(dump(min("COL6"), 16, 0, 64), 1, 240), substrb(dump(max("COL6"), 16, 0, 64), 1, 240),
to_char(count("COL7")), substrb(dump(min("COL7"), 16, 0, 64), 1, 240), substrb(dump(max("COL7"), 16, 0, 64), 1, 240),
to_char(count("COL1")), substrb(dump(min("COL1"), 16, 0, 64), 1, 240), substrb(dump(max("COL1"), 16, 0, 64), 1, 240),
count(rowidtochar(rowid))
from "TEST"."T2" t /* TOPN, NIL, NIL, NDV, NIL, NIL, NDV, NIL, NIL, NDV, NIL, NIL, NDV, NIL, NIL, TOPN, NIL, NIL, TOPN, NIL,
NIL, RWID, U254, U254, U254, U254, U254, U254, U254U*/
上記の処理がAWRにレポートされたのは下記4回のみで、処理時間は最大でも3分程度となっています。
処理時間が長かった時はinsertの処理時間も長くなっているので、統計情報取得とinsert処理が同時実行されて性能劣化した可能性がありますが、通常は数秒もかかっていません。
13日 20:00
| Total | Per Execution | Percentage of Total | |||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| SQL Id | Elapsed (s) | CPU (s) | IOWait (s) | Gets | Reads | Rows | Cluster (s) | Execs | Elapsed (s) | CPU (s) | IOWait (s) | Gets | Reads | Rows | Cluster (s) | DB time | DB CPU | IO Wait | Gets | Reads | Cluster | Execs | SQL Text |
| 93w8sdhx08qwf | 181.84 | 114.08 | 76.00 | 11,638,333 | 2,776,355 | 0 | 4.54 | 0 | 11.67 | 10.70 | 0.10 | 2.86 | 13.36 | 5.50 | 0.00 | begin /* KAPS */ dbms_stats.ga... |
13日 21:00
| Total | Per Execution | Percentage of Total | |||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| SQL Id | Elapsed (s) | CPU (s) | IOWait (s) | Gets | Reads | Rows | Cluster (s) | Execs | Elapsed (s) | CPU (s) | IOWait (s) | Gets | Reads | Rows | Cluster (s) | DB time | DB CPU | IO Wait | Gets | Reads | Cluster | Execs | SQL Text |
| 93w8sdhx08qwf | 59.31 | 23.47 | 41.34 | 1,588,469 | 1,393,058 | 0 | 0.40 | 0 | 3.80 | 2.25 | 0.05 | 0.41 | 6.16 | 0.55 | 0.00 | begin /* KAPS */ dbms_stats.ga... |
14日 4:00
| Total | Per Execution | Percentage of Total | |||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| SQL Id | Elapsed (s) | CPU (s) | IOWait (s) | Gets | Reads | Rows | Cluster (s) | Execs | Elapsed (s) | CPU (s) | IOWait (s) | Gets | Reads | Rows | Cluster (s) | DB time | DB CPU | IO Wait | Gets | Reads | Cluster | Execs | SQL Text |
| 93w8sdhx08qwf | 177.84 | 71.44 | 120.65 | 4,516,198 | 4,177,066 | 0 | 10.66 | 0 | 9.94 | 6.61 | 0.09 | 1.19 | 9.60 | 24.07 | 0.00 | begin /* KAPS */ dbms_stats.ga... |
14日 5:00
| Total | Per Execution | Percentage of Total | |||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| SQL Id | Elapsed (s) | CPU (s) | IOWait (s) | Gets | Reads | Rows | Cluster (s) | Execs | Elapsed (s) | CPU (s) | IOWait (s) | Gets | Reads | Rows | Cluster (s) | DB time | DB CPU | IO Wait | Gets | Reads | Cluster | Execs | SQL Text |
| 93w8sdhx08qwf | 17.43 | 12.42 | 5.92 | 1,427,158 | 1,317,490 | 0 | 0.05 | 0 | 0.96 | 1.14 | 0.00 | 0.37 | 3.07 | 0.13 | 0.00 | begin /* KAPS */ dbms_stats.ga... | |||||||
| 4yyxk82jtzq44 | 4.76 | 4.40 | 0.37 | 1,291,604 | 1,291,520 | 1 | 0.00 | 1 | 4.76 | 4.40 | 0.37 | 1,291,604.00 | 1,291,520.00 | 1.00 | 0.00 | 0.26 | 0.40 | 0.00 | 0.34 | 3.01 | 0.00 | 0.00 | /* SQL Analyze(2) */ select /*... |
ちなみに、多数のAWRレポートをまとめて出力させるスクリプトは以下の記事で紹介しています。
次はサイズの大きなテーブル（約250GB）で性能影響を確認してみました。T2,T3とも5時台にinsert開始し、T2 は6時前にcommitしましたが、T3は週開けてからcommitしました。
T3は19時台と20時台に統計情報が取得されています。
SQL> select TABLE_NAME,STATS_UPDATE_TIME from user_tab_stats_history where table_name in ('T2','T3') order by 2;
TABLE_NAME STATS_UPDATE_TIME
---------- ----------------------------------------
（略）
T2 15-NOV-19 06.41.38.240843 PM +09:00
T3 15-NOV-19 07.11.56.420146 PM +09:00
T3 15-NOV-19 08.13.38.617608 PM +09:00
11/15 19:00-20:00のAWRを確認したところ、T3の高頻度自動オプティマイザ統計収集はデフォルトの処理時間上限である3600秒内に終わらなかったので、一旦終了し、20時台に再開したと思われます。
インデックス統計についても確認しました。
インデックス統計も取得されていますが、自動で作成されたインデックスは、INVISIBLEの間は高頻度自動統計は取得されません。
自動インデックスについては別の機会に触れますが、INVISIBLEの間は実体が無いので統計情報が取られないのは想定通りですね。
SQL> select table_name,last_analyzed from tabs order by 2;
TABLE_NAME LAST_ANALYZED
---------- -------------------
TEST2 2019/11/14 17:43:11
TEMP1 2019/11/15 10:51:14
TEST3 2019/11/15 10:51:34
10 rows selected.
SQL> select table_name,index_name,last_analyzed,visibility from ind order by 3;
TABLE_NAME INDEX_NAME LAST_ANALYZED VISIBILIT
---------- ------------------------------ ------------------- ---------
TEST3 SYS_AI_b3prtm66rh7j4 2019/11/11 16:29:08 INVISIBLE
TEST2 SYS_AI_1ugut3ncafcz7 2019/11/11 16:29:08 INVISIBLE
TEST2 SYS_AI_d8um6z8gc0gmd 2019/11/14 17:44:58 VISIBLE
TEST2 SYS_AI_3fra6uk8v9jbk 2019/11/14 17:45:14 VISIBLE
TEST3 SYS_AI_310mju3y45090 2019/11/15 10:53:04 VISIBLE
TEST3 SYS_AI_d1798g0553uxs 2019/11/15 10:53:13 VISIBLE
TEST3 SYS_AI_7m0m5m59yu50p 2019/11/15 10:53:22 VISIBLE
ヒストグラム統計も確認します。ヒストグラム統計は、取得はされているテーブルと取得されないテーブルが混在しています。
SQL> select TABLE_NAME,COLUMN_NAME,HISTOGRAM,LAST_ANALYZED from user_tab_columns order by LAST_ANALYZED
TABLE_NAME COLUMN_NAM HISTOGRAM LAST_ANALYZED
---------- ---------- --------------- -------------------
TEST2 COL7 FREQUENCY 2019/11/14 17:43:11
TEST2 COL6 NONE 2019/11/14 17:43:11
TEST2 COL5 NONE 2019/11/14 17:43:11
TEST2 COL4 NONE 2019/11/14 17:43:11
TEST2 COL3 NONE 2019/11/14 17:43:11
TEST2 COL2 FREQUENCY 2019/11/14 17:43:11
TEST2 COL1 NONE 2019/11/14 17:43:11
TEMP1 COL1 NONE 2019/11/15 10:51:14
TEMP1 COL2 NONE 2019/11/15 10:51:14
TEMP1 COL3 NONE 2019/11/15 10:51:14
TEMP1 COL4 NONE 2019/11/15 10:51:14
TEMP1 COL5 NONE 2019/11/15 10:51:14
TEMP1 COL6 NONE 2019/11/15 10:51:14
TEMP1 COL7 NONE 2019/11/15 10:51:14
検証結果に関して、サポートに確認してみました。
Q．高頻度自動統計取得時にヒストグラム統計が取得されたテーブルと、されていないテーブルがあります。
高頻度自動統計取得時には新規にヒストグラム統計は取得せず、既存のヒストグラム統計があれば
更新する、という認識で正しいですか？
A．はい、ご認識の通りです。
Q．高頻度自動統計取得時に、数分のズレはありますが、インデックス統計も取得されています。
高頻度自動統計取得時にはインデックス統計も取得されるという認識で正しいですか？
A．はい、ご認識の通りです。
Q．AWRレポートに、dbms_stats.gather_st_job_continuous_procの他に添付の通り、
/* SQL Analyze(2) */ で始まり、T3テーブルの各カラムの数、最小値、最大値を取得するSQLもレポートされていました。
これもdbms_stats.gather_st_job_continuous_procの一部として実行されたものですか？
A．はい、ご認識の通りです。
/* SQL Analyze(2) */ で始まり処理は、dbms_stats.gather_st_job_continuous_procの一部として実行されたものです。
以下をご参照頂ければと存知ます。
awrrpt_rac_251_252.html
-------------------------------------------------------------
Top SQL with Top Events
SQL ID Plan Hash Executions % Activity Event % Event Top Row Source % Row Source SQL Text
Ga5g7dryucm9p 463314188 1 19.81 CPU + Wait for CPU 19.81 OPTIMIZER STATISTICS GATHERING★ 12.04 /* SQL
Analyze(2) */ select /*...
以下、SQLチューニングガイドからの補足です。
13.2.1 高頻度自動オプティマイザ統計収集について
この高頻度タスクの有効化と無効化、実行間隔の設定および最大実行時間の設定のためDBMS_STATS.SET_GLOBAL_PREFSプロシージャを使用します。高頻度タスクは「軽量」なものであり、失効した統計のみを収集します。存在しないオブジェクトの統計のパージやオプティマイザ統計アドバイザの起動などのアクションは実行されません。標準の自動ジョブでは、これらの追加タスクが実行されます。
検証結果のまとめは以下の通りです。
・総括
デフォルトでは無効で、DBMS_STATS.SET_GLOBAL_PREFS ('AUTO_TASK_STATUS','ON')の実行で有効になる。
インデックス統計も取得される。
新規にヒストグラム統計は取得せず、既存のヒストグラム統計があれば更新する。
通常の統計情報取得処理よりは「軽量」だが、サイズの大きなテーブルだとかなりの時間がかかる。
・考察
有効・無効をDB全体でしか設定できない為、大きなサイズのテーブルに頻繁に10%を超える更新が発生する環境ではパフォーマンス影響が懸念される。デフォルトでは15分おきに実行で、最大実行時間が1時間なので、デフォルト設定のままでは場合によっては複数の高頻度統計がパラレルで実行されてしまう為、最大実行時間 ＜ 実行頻度となるように設定するのが望ましいと思われる。
