Oracle 19c リアルタイム統計 - おらくるのいる生活

19c新機能検証の第2回になります。

今回は強化されたオプティマイザ統計メンテナンス機能の内、リアルタイム統計について検証します。

この機能はExadataでのみ利用可能で、検証に使用した環境は以下の通りです。

Oracle RAC（2ノード） EE 19.5.0.0.0

Oracle Linux Server release 7.6

EXADATA DATABASE MACHINE X6-2

図解入門よくわかる最新 Oracleデータベースの基本と仕組み［第5版］ [ 水田巴 ]

価格: 2420 円
楽天で詳細を見る

機能の概略は以下の通りです。

・機能について
リアルタイム統計では新鮮な統計により最適な実行計画が作成できる。

・処理概要
従来型DML (insert/update/merge)の一部として統計を収集する。
DML中に収集される統計はごくわずかなオーバーヘッドで高速。
最悪な実行計画によるパフォーマンス低下を避けるために、最も重要な統計のみが収集される。
（例えば、最大値が正しくない統計の範囲外条件を回避するなど）Min, Max, num_rowsなど。
残りの統計は以下の収集まで延期される。
　 - 高頻度統計収集、自動統計収集ジョブ、手動の統計収集

・設定方法
デフォルトで有効なので、特別な設定は不要。

では、実機で検証してみます。

Insert処理を1000件実行し、リアルタイム統計の挙動を確認してみました。

SQL> begin
2 for i in 1..10000 loop
3 insert into t2(col1,col6) values(i,systimestamp);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

データディクショナリへの反映に最大、数分かかるので、フラッシュします。

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.

リアルタイム統計が取得されていますが、dbms_statsで取得した統計とは別に管理されるので、xxx_tablesには反映されません。

確認はxxx_tab_statisticsで行います。

SQL> select table_name,last_analyzed,notes from user_tab_statistics
where table_name='T2';
TABLE_NAME LAST_ANALYZED NOTES
---------- ------------------- -------------------------
T2 2019/11/15 18:07:03
T2 2019/11/22 13:16:57 STATS_ON_CONVENTIONAL_DML

SQL> select table_name,last_analyzed from tabs where table_name='T2';
TABLE_NAME LAST_ANALYZED
---------- -------------------
T2 2019/11/15 18:07:03

インデックス統計も確認します。取得されていない事が判ります。

SQL> select table_name,index_name,last_analyzed from ind where table_
name='T2' order by last_analyzed;

TABLE_NAME INDEX_NAME LAST_ANALYZED
---------- ------------------------------ -------------------
T2 SYS_AI_fjjrg7da66v50 2019/11/06 15:23:41
T2 SYS_AI_c214vvjs32z70 2019/11/11 14:31:59
T2 SYS_AI_gtgdu3rrx5n74 2019/11/11 16:28:03

行数を減らし、100行で確認します。

100行のinsertでも、リアルタイム統計が取得されました。

SQL> begin
2 for i in 1..100 loop
3 insert into t2(col1,col6) values(i,systimestamp);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.

SQL> commit;
Commit complete.

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.

SQL> select table_name,last_analyzed from tabs where table_name
='T2';
TABLE_NAME LAST_ANALYZED
---------- -------------------
T2 2019/11/15 18:07:03

SQL> select table_name,last_analyzed,notes from user_tab_stat
Istics where table_name='T2';
TABLE_NAME LAST_ANALYZED NOTES
---------- ------------------- -------------------------
T2 2019/11/15 18:07:03
T2 2019/11/22 13:18:32 STATS_ON_CONVENTIONAL_DML

インデックス統計は取得されません。

SQL> select table_name,index_name,last_analyzed from ind where
table_name='T2' order by last_analyzed;
TABLE_NAME INDEX_NAME LAST_ANALYZED
---------- ------------------------------ -------------------
T2 SYS_AI_fjjrg7da66v50 2019/11/06 15:23:41
T2 SYS_AI_c214vvjs32z70 2019/11/11 14:31:59
T2 SYS_AI_gtgdu3rrx5n74 2019/11/11 16:28:03

次に1行のみinsertでもリアルタイム統計が取得されるのかどうか、確認します。

SQL> select min(col1),max(col1) from t2;
MIN(COL1) MAX(COL1)
---------- ----------
1 10000000

SQL> insert into t2(col1,col6) values(50000000,systimestamp);
1 row created.

SQL> commit;
Commit complete.

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.

1件のみのinsertでは、リアルタイム統計は取得されませんでした。

次に、update時の挙動を確認する為、少数のupdateを実行します。

SQL> update t2 set col6=systimestamp where rownum <= 100;
100 rows updated.
SQL> commit;
Commit complete.
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> select table_name,last_analyzed,notes from user_tab_statistics where table_name='T2';
TABLE_NAME LAST_ANALYZED NOTES
---------- ------------------- -------------------------
T2 2019/11/15 18:07:03
T2 2019/11/22 13:18:32 STATS_ON_CONVENTIONAL_DML

テーブルの統計のLAST_ANALYZEDはひとつ前のテスト時のままで、updateしたカラムのみ、リアルタイム統計が取得された事が確認できました。

SQL> select table_name,column_name,last_analyzed,notes from user_tab_col_statistics where table_name='T2' order by
last_analyzed;
TABLE_NAME COLUMN_NAM LAST_ANALYZED NOTES
---------- ---------- ------------------- ------------------------------
T2 COL2 2019/11/15 18:07:03 HYPERLOGLOG
T2 COL3 2019/11/15 18:07:03 HYPERLOGLOG
T2 COL4 2019/11/15 18:07:03 HYPERLOGLOG
T2 COL5 2019/11/15 18:07:03 HYPERLOGLOG
T2 COL6 2019/11/15 18:07:03 HYPERLOGLOG
T2 COL1 2019/11/15 18:07:03 HYPERLOGLOG
T2 COL7 2019/11/15 18:07:03 HYPERLOGLOG
T2 COL1 2019/11/22 13:18:32 STATS_ON_CONVENTIONAL_DML
T2 COL6 2019/11/22 13:21:19 STATS_ON_CONVENTIONAL_DML
9 rows selected.

どの程度の変更でリアルタイム統計が取得されるか確認するために、ほぼ同数のupdateを何度か実行してみます。
col6のmax値が変わるように99行を更新しました。　

SQL> update t1 set col6=systimestamp where rownum < 100;
99 rows updated.
SQL> commit;
Commit complete.

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.

9 rows selected.

リアルタイム統計が取得されています。。

続いてcol6のmax値が変わるように100行を更新してみました。

SQL> update t1 set col6=systimestamp where rownum <= 100;
100 rows updated.

SQL> commit;
Commit complete.

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,column_name,LAST_ANALYZED,NOTES from user_tab_col_statistics where table_name='T1' order by LAST_
ANALYZED;
TABLE_NAME COLUMN_NAM LAST_ANALYZED NOTES
---------- ---------- ------------------- ------------------------------
T1 COL1 2019/11/07 22:01:56 HYPERLOGLOG
T1 COL2 2019/11/07 22:01:56 HYPERLOGLOG
T1 COL3 2019/11/07 22:01:56 HYPERLOGLOG
T1 COL4 2019/11/07 22:01:56 HYPERLOGLOG
T1 COL5 2019/11/07 22:01:56 HYPERLOGLOG
T1 COL7 2019/11/07 22:01:56 HYPERLOGLOG
T1 COL6 2019/11/07 22:01:56 HYPERLOGLOG
T1 COL1 2019/11/12 13:34:51 STATS_ON_CONVENTIONAL_DML
T1 COL6 2019/11/12 13:43:11 STATS_ON_CONVENTIONAL_DML

9 rows selected.

こちらではリアルタイム統計は取得されませんでした。

今のところ、どの程度の更新があればリアルタイム統計が取得されるのか、不明です。

delete処理を実行し、リアルタイム統計の挙動を確認します。

SQL> select table_name,last_analyzed,notes from user_tab_statistics where table_name='T2';
TABLE_NAME LAST_ANALYZED NOTES
---------- ------------------- ------------------------------
T2 2019/11/15 18:07:03
T2 2019/11/22 13:24:59 STATS_ON_CONVENTIONAL_DML

SQL> delete from t2 where rownum <= 100000;
100000 rows deleted.

SQL> commit;
Commit complete.

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.

SQL> select table_name,last_analyzed,notes from user_tab_statistics where table_name='T2';
TABLE_NAME LAST_ANALYZED NOTES
---------- ------------------- ------------------------------
T2 2019/11/15 18:07:03
T2 2019/11/22 13:24:59 STATS_ON_CONVENTIONAL_DML

DMLと言えば通常insert,delete,updateの事ですが、deleteについてはリアルタイム統計は取得されませんでした。

次にバルクインサートを実行ます。

SQL> insert /*+ append */ into t2 select * from t1 where rownum <= 10000;
10000 rows created.

SQL> commit;
Commit complete.

SQL> select table_name,last_analyzed,notes from user_tab_statistics where table_name='T2';
TABLE_NAME LAST_ANALYZED NOTES
---------- ------------------- ------------------------------
T2 2019/11/15 18:07:03
T2 2019/11/22 13:24:59 STATS_ON_CONVENTIONAL_DML

リアルタイム統計は取得されませんでしたが、バルクインサートは従来型DMLではないので、これは想定通りです。

リアルタイム統計でどこまで統計が取られるのか知る為にヒストグラム統計を確認してみました。

SQL> select table_name,column_name,LAST_ANALYZED,HISTOGRAM,NOTES from user_tab_col_statistics order by 1,2;
TABLE_NAME COLUMN_NAM LAST_ANALYZED HISTOGRAM NOTES
---------- ---------- ------------------- --------------- ------------------------------
T2 COL1 2019/11/22 13:18:32　　　　　STATS_ON_CONVENTIONAL_DML
T2 COL1 2019/11/15 18:07:03 NONE 　　HYPERLOGLOG
T2 COL2 2019/11/15 18:07:03 HYBRID 　HYPERLOGLOG
T2 COL3 2019/11/22 13:24:59 　　　　　STATS_ON_CONVENTIONAL_DML
T2 COL3 2019/11/15 18:07:03 NONE 　　HYPERLOGLOG
T2 COL4 2019/11/15 18:07:03 NONE 　　HYPERLOGLOG
T2 COL5 2019/11/15 18:07:03 NONE 　　HYPERLOGLOG
T2 COL6 2019/11/22 13:24:59 　　　　　STATS_ON_CONVENTIONAL_DML
T2 COL6 2019/11/15 18:07:03 NONE 　　HYPERLOGLOG
T2 COL7 2019/11/22 13:24:59 　　　　　STATS_ON_CONVENTIONAL_DML
T2 COL7 2019/11/15 18:07:03 FREQUENCY HYPERLOGLOG

リアルタイム統計取得時にはHISTOGRAMカラムがnullになっているので、ヒストグラム統計は取得されていないことが判ります。

次にリアルタイム統計取得時の負荷を確認するため、リアルタイム統計取得あり・なしで単純なinsert、updateをそれぞれ1000回ずつ実施し、処理時間を比較しました。

リアルタイム統計はデフォルトで取得される為、無の時には以下のヒント句を付けます。

/*+ NO_GATHER_OPTIMIZER_STATISTICS */

実行処理	リアルタイム統計の有無		差異
実行処理	有	無	差異
update(1回目)	3.96秒	3.45秒	0.51
update(2回目)	3.54秒	3.73秒	-0.19
insert(1回目)	18.45秒	18.22秒	0.23
insert(2回目)	16.74秒	17.61秒	-0.87

検証結果として、差異は見られませんでした。

ここまでの検証で判ったこと、不明だった点について、サポートに確認してみました。

Q．delete実行時にはリアルタイム統計は取得されない仕様なのでしょうか？
A．はい、以下ドキュメントにも関連の記載が御座いますが、Real-time statistics は現在まで従来型のDML (insert/update/merge)のみが有効であります。こちらは仕様の認識を頂いても問題ないと考えております。
Real-time statistics extends the online statistic gathering techniques to conventional insert, update and merge DML operations.
The Optimizer In Oracle Database 19c

Q．リアルタイム統計が取得される・されないに関して、閾値や条件など公開した資料がありますか？
A．残念ながら、上記御問い合わせ内容については、現在まで公開された情報が御座いません。

Q．テーブルのリアルタイム統計が取得された時、インデックス統計も取得されますか？

A．インデックス統計はアルタイム統計取得の対象では御座いません。

Q．リアルタイム統計取得時にはヒストグラム統計は取得されない仕様でしょうか？
A．はい、リアルタイム統計取得時にはヒストグラム統計が対象外となります。
リアルタイム統計取得後に、DBMS_STATS.GATHER_TABLE_STATS にて手動でヒストグラム統計を収集する事を推奨しております。

まとめると、以下の通りになります。

検証結果の総括

デフォルトで有効であり、設定は不要。
無効にするためにはNO_GATHER_OPTIMIZER_STATISTICSヒントを付与する。
従来型insert,udpate,mergeが対象。(deleteは対象外)
インデックス統計は取得されない。
ヒストグラム統計は取得されない。
パフォーマンス影響は見られない。
通常の統計情報とは別に格納されるので、xxx_tablesでは確認できず、xxx_tab_statistics,xxx_tab_col_statisticsで確認する。
Update時には対象カラムの統計のみ取得される。
従来型DML実行時に毎回必ず取得される訳ではないが、取得される条件等については公開されておらず、不明。

考察

特にデメリットも無く特別な設定も不要なので、そのまま利用できる。

ただし、インデックス統計・ヒストグラム統計は取得されない為、これだけでは十分とは言えない。