しばちょう先生の試して納得！DBAへの道 indexページ▶▶
みなさん、こんにちは。”しばちょう”こと柴田長（しばた つかさ）です。
大変ご無沙汰しておりました！気付けば三年以上もの間、こちらの連載に新しい記事を投稿できずにおりましたが、いよいよ再始動させて頂きたいと考えております。少しでも再開を心待ちにされていた方がいらっしゃれば、とてもありがたいことでありますが、同時に、長い間ご期待にお応えできずに申し訳なかった気持ちです。なので、可能な限りの行動で感謝の気持ちを示していければと思いますので、今後ともご愛読のほどを宜しくお願いいたします。
今回、再開の第一弾としてお届けさせて頂くのは、Oracle Database 19cの新機能Memoptimized Rowstore – Fast Ingest（Fast Ingest：ふぁすと・いんじぇすと）と略して呼ばせて頂きますが、皆さんが大好きなパフォーマンス向上を目的とした新機能です。Oracle Database 19c の「データベース新機能ガイド」では、次のように紹介されていますね。
「Memoptimized Rowstoreの高速収集機能により、小さな大量のトランザクションを最小のトランザクション・オーバーヘッドで収集するIoT (Internet of Things)アプリケーションなどから、Oracle Databaseへの高速なデータ挿入が可能になります。高速収集を使用する挿入操作では、データを一時的にラージ・プールにバッファしてからディスクに一括して書き込みます(遅延のある非同期的な方法です)。」
IoTデバイスからOracle Databaseへ頻繁にリクエストされる一件INSERT+COMMIT処理がどこまで高速化するのか、そしてその使い方や注意点を、こちらの演習を通して体験して頂けると幸いです。以下の演習をOracle Database 19c Enterprise Editionのデータベースで試してみてください。（私は、DBRU19.8.0上の非CDBにおいて動作確認を行っています。）
【今回ご紹介するネタ一覧（逆引き）】
- Fast Ingestに対応した表の作成（演習2）
- Fast Ingestの効果測定（演習3）
- V$SQLビューのELAPSED_TIME列でINSERT処理時間を確認（演習3）
- バインド変数を使わずに繰り返しソフトパースで異なる値のレコードをINSERTする検証テクニック（オマケ1）
- NO_GATHER_OPTIMIZER_STATISTICSヒント（オマケ2）
- PL/SQLループをCOMMITを含む性能測定する検証で使うのは好ましくない理由（オマケ3）
- シェルスクリプトでINSERTをループする際の注意点（オマケ4）
演習1. 次のSQLを参考にして、今回の演習で使用するTRYスキーマを作成してください。
$ export ORACLE_SID=orcl1 $ sqlplus / as sysdba SQL> -- データベース・バージョンの確認 select BANNER_FULL from V$VERSION ; BANNER_FULL -------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.8.0.0.0 -- データファイルを格納可能なASMディスク・グループの確認 select NAME, STATE, TYPE, TOTAL_MB, FREE_MB from V$ASM_DISKGROUP ; NAME STATE TYPE TOTAL_MB FREE_MB ------------------------------ ----------- ------ ---------- ---------- DATA01 CONNECTED HIGH 36864000 36001596 DBFS_DG MOUNTED HIGH 620928 619344 -- TBS56表領域と、この表領域をデフォルト表領域とするTRYユーザーの作成 create bigfile tablespace TBS56 datafile '+DATA01(DATAFILE)' size 8g ; create user TRY identified by TRY123456 default tablespace TBS56 ; alter user TRY quota unlimited on TBS56 ; grant create session, create table to TRY ; -- 表領域の作成状況の確認 set linesize 150 pages 50000 tab off col TABLESPACE_NAME for a8 col FILE_NAME for a45 select TABLESPACE_NAME, STATUS, BYTES, FILE_NAME from DBA_DATA_FILES where TABLESPACE_NAME = 'TBS56' ; TABLESPA STATUS BYTES FILE_NAME -------- --------- ---------- --------------------------------------------- TBS56 AVAILABLE 8589934592 +DATA01/DBM01S/DATAFILE/tbs56.696.1067352299 -- TAB56N表の作成 connect TRY/TRY123456 create table TAB56N (COL1 number primary key, COL2 date, COL3 char(1000)) ;
はい、とても懐かしいですねー。お馴染みだった指の準備体操ですね。
私は業務の都合上2年間近くこのような検証作業からはかなり遠ざかった際に、想像以上に指が動かなくなってしまっていたことにエンジニア職に戻ったときに愕然とした記憶があります。その人の特性にも寄るのでしょうけど、私は不器用だったので二足のワラジを吐くことが難しかったんですね、きっと。すいません、どうでもいい話でした（笑）
でも、もう一つ脱線させてください。今度は技術ネタです。なぜ、私は表領域の作成状況を確認するクセを身に着けているかという理由となります。
上記の実行例では、ASMディスク・グループ「DATA01」上に表領域を作成していますが、このディスク・グループ名の前に半角のプラス記号「+」を追加するのが当たり前になっていると思いますが、これを全角のプラス記号「＋」にしたことがある人はいらっしゃらないでしょうか？実は、私はやってしまったことがあるんですけどね。直接端末への入力ではなく、どこからかDDL文をコピーしてきたんですね。どうなったと思いますか？なんと！ORAエラーは一切発生せずに、表領域が作れてしまうんですよ。しかーし、気を付けてください。目的のASMディスク・グループ上にデータファイルは作成されていませんので。$ORACLE_HOME/dbs ディレクトリ直下に、「???DATA(DATAFIE)」と言う奇妙なデータファイルが作成されることになります。つまり、全角のプラス記号「＋」が「???」に文字化けしてデータファイル名の一部となってしまった現象です。その場合は、そのデータファイルが作成されたノード上のデータベース・インスタンスへ接続して頂き、drop table文で綺麗に削除してもらえれば問題はありません。いやー焦りますよねー。。。
さて、Fast Ingestとは関係ないところで時間を使ってしまいましたので、スピードアップして、Fast Ingestを試していきましょう。
演習2. 演習1で作成したTAB56N表と同じ列を持つ、Fast Ingestに対応したTAB56F表を作成してください。
$ export ORACLE_SID=orcl1 $ sqlplus /nolog SQL> -- Fast Ingest対応したTAB56F表を作成（失敗例） connect TRY/TRY123456 create table TAB56F (COL1 number not null, COL2 date, COL3 char(1000)) MEMOPTIMIZE FOR WRITE ; * ERROR at line 1: ORA-62145: MEMOPTIMIZE FOR WRITE feature not allowed on segment with deferred storage.
まず、Fast Ingestで高速にレコードを挿入したい表は、必ず、「MEMOPTIMIZE FOR WRITE」句を指定しておく必要があります。しかし・・・
そうですよね、私の演習問題は失敗を体験して頂きながら学んでいただくことが多かったはず。と思い出しながら読んで頂いている方、本当にありがとうございます。
ORAエラーが発生すると焦ってしまう傾向がありますが、エラー番号とエラーメッセージを今一度読んでみてくださいね。上記の例だと「not allowd := 許可されていない」、「segment with deferred storage := 遅延のセグメント???」と言う事で、「セグメントの遅延作成とMEMOPTIMIZE FOR WRITE機能の組み合わせは許可されていない」となんとなく読み取ることができますね。もちろん、エラーメッセージガイドを確認されるのが正規の手順ですがね。
皆様、覚えておりますでしょうか？第3回の連載で登場した、Oracle Database 11g Release 2の新機能「セグメント作成の遅延」ですね。デフォルトの動作として、Create Table文実行時には表セグメントが作成されず、実際に一件目のレコードが挿入されるタイミングで、初めて表セグメントが作成される動作ですね。よって、Create Table文実行時に「SEGMENT CREATION IMMEDIATE」句を追加しておけば良いという結論になります。
SQL> -- Fast Ingest対応したTAB56F表を作成（成功） create table TAB56F (COL1 number not null, COL2 date, COL3 char(100)) SEGMENT CREATION IMMEDIATE MEMOPTIMIZE FOR WRITE ; Table created. SQL> -- TAB56N表も「SEGMENT CREATION IMMEDIATE」句を追加して作成し直し drop table TAB56N purge ; create table TAB56N (COL1 number not null, COL2 date, COL3 char(100)) SEGMENT CREATION IMMEDIATE ; Table created. SQL> -- 両表の属性「MEMOPTIMIZE_WRITE」を確認 col TABLE_NAME for a16 select TABLE_NAME, MEMOPTIMIZE_WRITE from USER_TABLES ; SQL> TABLE_NAME MEMOPTIM ---------------- -------- TAB56F ENABLED TAB56N DISABLEDは
はい、これで準備が整いましたねーと言う事で、これらの表へINSERTを繰り返した際の性能差についてみていきましょう。
演習3. 各表へ一件INSERTを繰り返し実行して、Memoptimized Rowstore – Fast Ingestの効果を測定してください。
$ sqlplus TRY/TRY123456
SQL> -- 各表へのINSERTで使用するシーケンスを作成
create sequence SEQ56N start with 10000001 increment by 1 cache 100000;
create sequence SEQ56F start with 10000001 increment by 1 cache 100000;
exit ;
### INSERTを5万回繰り返すシェルスクリプトを用意
$ cat run.sh
#!/bin/sh
MAX_VAL=50000
i=0; while [ $i -lt ${MAX_VAL} ]; do
echo "
insert /*+NO_GATHER_OPTIMIZER_STATISTICS */ into TAB56N values (SEQ56N.nextval, SYSDATE, lpad(to_char(SEQ56N.currval), 100, 'A'));
commit ;"
i=`expr $i + 1`
done | sqlplus -S TRY/TRY123456 > /dev/null
i=0; while [ $i -lt ${MAX_VAL} ]; do
echo "
insert /*+NO_GATHER_OPTIMIZER_STATISTICS MEMOPTIMIZE_WRITE */ into TAB56F values (SEQ56F.nextval, SYSDATE, lpad(to_char(SEQ56F.currval), 100, 'A'));"
i=`expr $i + 1`
done | sqlplus -S TRY/TRY123456 > /dev/null
### シェルスクリプトの実行
$ sh ./run.sh
### 一件INSERTを5万回連続実行に要した時間の確認（この測定方法は適切ではない）
$ sqlplus TRY/TRY123456
SQL>
-- 通常表
select (max(COL2) - min(COL2)) * 24 * 3600 as "ELAPSED_TIME(S)" from TAB56N ;
ELAPSED_TIME(S)
---------------
51
-- Fast Ingest機能が有効な表
select (max(COL2) - min(COL2)) * 24 * 3600 as "ELAPSED_TIME(S)" from TAB56F ;
ELAPSED_TIME(S)
---------------
50
Fast Ingest機能を使用するためには対象表で有効化されているだけではなく、INSERT文にも「MEMOPTIMIZE_WRITE」ヒント句を必ず含める必要がありますので、今回、Fast Ingest機能が有効化されているTAB56F表に対するINSERT文には、このヒント句を追加しています。また、両INSERT文に「NO_GATHER_OPTIMIZER_STATISTICS」ヒント句も追加していますが、結論としては設定する必要は無かったですが、こちらのヒント句も紹介する意味で残してあります。と言うことで、これらのINSERT文をシェルスクリプトのループで5万回のINSERTを実行しています。
一回のINSERT時間を計測するならば「set timing on」を設定すれば良いですが、今回はそれを5万回実行するので全て合計するのは現実的ではありませんね。ではどうしたら良いのか？と悩みがちですが、各表の二番目の列はDATE型でしたよね？そして、SYSDATE関数を使ってINSERT時の時刻が格納されているはずなので、その最大時刻（最後にINSERTされたレコードのCOL2列）から最小時刻（最初にINSERTされたレコードのCOL2列）を引き算することで、合計5万回のINSERTに要した時間を把握することが可能ですー！
とか、ちょびっとカッコつけていたのが悪夢の始まりでした。。。
なんと、想定外の大事件が発生！Fast Ingest機能を使っても使わなくても、ほとんど性能が変わらないという結果になってしまっています。これでは記事で紹介出来ないなぁーっと、ココまで文章を書いてきたのに、お蔵入りか？と途方にくれること半日。。。ヒラメキマシタ！実行時間の見方が間違えているはずだと。と言うことで、次をご覧ください。
$ sqlplus / as sysdba SQL> -- Fast Ingest機能の効果を正確に把握する方法 set linesize 200 pages 5000 tab off col CPU_TIME for 999,999,999 col ELAPSED_TIME for 999,999,999 select SQL_ID, SQL_TEXT, EXECUTIONS, PARSE_CALLS, CHILD_NUMBER, CPU_TIME, ELAPSED_TIME from V$SQL where SQL_TEXT like ' insert /*+NO_GATHER_OPTIMIZER_STATISTICS %' ; SQL_ID ------------- SQL_TEXT -------------------------------------------------------------------------------------------------------------------------------------------------------- EXECUTIONS PARSE_CALLS CHILD_NUMBER CPU_TIME ELAPSED_TIME ---------- ----------- ------------ ------------ ------------ f471nfsac7u2x insert /*+NO_GATHER_OPTIMIZER_STATISTICS */ into TAB56N values (SEQ56N.nextval, SYSDATE, lpad(to_char(SEQ56N.currval), 100, 'A')) 50000 50000 0 1,766,543 5,913,337 9k55g606q9rt7 insert /*+NO_GATHER_OPTIMIZER_STATISTICS MEMOPTIMIZE_WRITE */ into TAB56F values (SEQ56F.nextval, SYSDATE, lpad(to_char(SEQ56F.currval), 100, 'A')) 50000 50000 0 873,745 2,811,427
いかがでしょうか？V$SQLビューのELAPSED_TIME列で、各INSERT処理を5万回実行した際の実行時間が確認出来ます。こちらの結果を見る限り、Fast Ingest機能を使うことでINSERT処理性能が2倍以上の向上する（処理時間が減少する）が確認出来ました。パチパチぱちぱち！（こちらに記載の効果はあくまで参考情報としての扱いで、その効果を保証するものではなく、実行環境に依存しますのでご注意くださいね。）
- SQL_ID=f471nfsac7u2xの通常のINSERTは、5,913,337マイクロ秒 ／ 5万回INSERT
- SQL_ID=9k55g606q9rt7のFast IngestのINSERTは、2,811,427マイクロ秒 ／ 5万回INSERT
さらにこの性能差にプラスして、COMMITに要する時間もFast IngestのINSERTでは必要無くなる点（理由は後ほど）も、性能向上の観点ではとても大きいと思います。
ちなみに、なぜ私がPL/SQLじゃなくてシェルスクリプトのループを使っているのか等々、上記の私の検証の実施方法と大きな過ちについては、記事の最後のオマケににて詳しく解説させて頂きますので、ご興味のある方は是非最後までお付き合いいただけると嬉しいです。ここではまず、Fast Ingestの動作について、整理しておきたいと思います。
通常、INSERT文を実行した後は、皆さんは何を実行しますか？そう、COMMITを実行しますね。しかし、私のFast Ingest機能によるINSERT文の後にはCOMMITを書いていません。これは何故でしょうか？実は、Fast Ingest機能を利用したINSERTの場合、COMMITは意味を持たないからと言うのが答えです。これを説明するために、記事の冒頭に掲載したFast Ingest機能の紹介文を今一度読み直してみると、「データを一時的にラージ・プールにバッファしてからディスクに一括して書き込みます(遅延のある非同期的な方法です)。」あ、、、うーん、これだけだと分かり辛い気がしてきましたので、これについても、正確に表現させて頂きたいと思います。
通常INSERTのおさらい）
まず、通常のINSERT文のおさらいですが、Buffer Cache上のブロックにレコードを挿入します。あくまでメモリ上に新規レコードが存在しているだけですし、Commitしていないので確定もしていないので、DBインスタンスがダウンしてしまえば、この新規レコードはロールバックされます（消えてしまいます）。DBインスタンスがダウンしても新規レコードをデータベース内に残す為には、COMMITを実行してトランザクションを確定する必要がありますね。では、このCOMMIT処理のタイミングで何が起きているのかと言うと、INSERTによって生成されたREDOレコードをLGWRプロセスがディスクへ書き込んでいるのはご存知でしょう。ユーザー（フォアグラウンドプロセス）が影響を受ける待機イベント名としては、log file sync待機ですね。ディスクへの書き込み完了を待つ必要がありますからディスクのI/O性能に大きく依存します。そのREDOレコードの書き込みが完了すると、COMMIT完了の通知がユーザーに戻ります。しかし、新規レコードが挿入されたBuffer Cache上のブロックがディスクに書き込まれることは、COMMITのタイミングでは保証されません。Buffer Cache上の更新されたブロックをディスクへ書き出すDBWRプロセスはCOMMITとは非同期で動作することにより、COMMITの性能を高めている仕組み（もしも、COMMIT時に更新されたブロックを毎回ディスクへ書き出していたら、性能遅延が発生するのは想像し易いかと思います）ですね。では、DBWRが更新されたブロックをディスクへ書き出す前にDBインスタンスがダウンした場合も新規レコードが消えてしまうのかと言うと、それは無いですよね。COMMITにより変更履歴であるREDOレコードが既にディスクへ書き出されているので、そのREDOレコードを使って、自動的に対象ブロックをロールフォワード（リカバリ）する仕組みが備わっているからですね。COMMITされたレコードは絶対に守る！とOracle Databaseが約束してくれている部分です。
Fast Ingest機能によるINSERTの特徴）
次に、Fast Ingest機能によるINSERT処理に話を移しましょう。Fast Ingestは通常のOracle Databaseのトランザクション処理とは全く異なります。INSERTされた新規レコードはLarge Pool上で保持されます。この時点ではCOMMITされていませんので、DBインスタンスがダウンしたらINSERTされたレコードは消えます。ここだけ読めば通常のINSERTと違いは無いと感じてしまうでしょう。しかし、Fast Ingestの場合は、INSERT文の後にCOMMITを実行しても意味がありません。これは実はパフォーマンス・チューニング・ガイドにも次の記載があります。
「高速収集のコンテキストの場合、commit操作は意味を持ちません。これはOracleにおける従来の意味でのトランザクションではないためです。挿入をロールバックする機能はありません。高速収集バッファからディスクにフラッシュされるまで、データの問合せもできません。」
むむむ。好奇心が芽生えてくる部分ですねー。と言うことで、もう少し深堀りしてみたいと思います。この理由について私が検証する限り、Fast IngestによるINESRT処理では、変更履歴のREDOレコードが生成されません。生成されていないものをLGWRプロセスがディスクへ書き出せるはずがありませんよね。(次の実行例の通り、各INSERTの前後でv$mystatビューにて、REDO生成量（redo size）を確認すると明らかです) では、どのタイミングで新規レコードがデータベース内に残ることが保証されるのか（COMMITされるのか）と言うと、Large Poolからディスクへ一括で書き出されたタイミングのみである点が、通常トランザクションとの大きな違いとなります。さらに言えば、COMMITを実行しても意味がないので、新規レコードを挿入した処理をROLLBACKする機能も合わせて提供していません。とても思い切りの良い、割り切った機能です。これもv$mystatビューでUNDO生成量（undo change vector size）の増加量を確認すると、Fast IngestのINSERTではロールバックに必要とされるUNDOが生成されていないことが確認出来ます。
$ sqlplus TRY/TRY123456
SQL> select S.NAME, M.VALUE
from V$MYSTAT M, V$STATNAME S
where M.STATISTIC# = S.STATISTIC#
and S.NAME in ('redo size', 'undo change vector size');
NAME VALUE
------------------------------ ----------
redo size 169816
undo change vector size 37272
SQL> -- 通常INSERT
insert /*+NO_GATHER_OPTIMIZER_STATISTICS */ into TAB56N
values (SEQ56N.nextval, SYSDATE, lpad(to_char(SEQ56N.currval), 100, 'A'));
1 row created.
SQL> select S.NAME, M.VALUE
from V$MYSTAT M, V$STATNAME S
where M.STATISTIC# = S.STATISTIC#
and S.NAME in ('redo size', 'undo change vector size');
NAME VALUE
------------------------------ ----------
redo size 170940
undo change vector size 37504
SQL> -- Fast IngestによるINSERT
insert /*+NO_GATHER_OPTIMIZER_STATISTICS MEMOPTIMIZE_WRITE */ into TAB56F
values (SEQ56F.nextval, SYSDATE, lpad(to_char(SEQ56F.currval), 100, 'A'));
1 row created.
SQL> select S.NAME, M.VALUE
from V$MYSTAT M, V$STATNAME S
where M.STATISTIC# = S.STATISTIC#
and S.NAME in ('redo size', 'undo change vector size');
NAME VALUE
------------------------------ ----------
redo size 170940
undo change vector size 37504
「Fast IngestのINSERTは非同期書き出しだから高速である」と誤解されがちですが、よくよく考えて頂くと、通常のINSERTもFast IngestのINSERTも、新規レコードを含んだデータブロックがメモリからディスクへ書き出されるタイミングは、ユーザートランザクションとは非同期です。よって、Fast Ingestが高速である真の理由は「REDOもUNDOも生成しないのでCPU時間が減少し、かつ、ディスクI/O時間を含むCOMMIT時間が不要である」点であることをご理解頂けたかと思います。あと、需要な点を書き漏らしていましたので、マニュアルから抜粋しておきますね。「索引操作および制約チェックは、データがラージ・プール内の高速収集領域からディスクに書き込まれるときにのみ実行されます。バックグラウンド・プロセスでデータがディスクに書き込まれるときに主キー違反が発生した場合は、データベースによってこれらの行がデータベースに書き込まれることはありません。」このような部分も、Fast IngestによってINSERTの実行結果が高速に戻ってくるのに寄与しているでしょうね。
Fast Ingest機能を使ってINESRTをする際に、未だLarge Pool書き出されていないレコード数をV$MEMOPTIMIZE_WRITE_AREAビューで確認することが出来ます。また、DBMS_MEMOPTIMIZEパッケージのサブプログラムを使って、対象セッションにおけるLarge PoolのFast Ingest関連のデータを表示したり、強制的に書き出し（フラッシュ）させることが可能です。全てのセッションのFast IngestデータをLarge Poolからフラッシュさせるには、DBMS_MEMOPTIMIZE_ADMINパッケージのWRITES_FLUSHサブプログラムを実行します。これらについての解説は、またの機会にさせてください。
さて、Fast IngestによるINSERTの高速化の効果を体験して頂きましたが、如何でしたでしょうか？実機を使って確認してみると、マニュアルにも記載のない真の事実に気付くことも出来るので、とても興味深いですよね。是非とも上手に利用して頂きたい機能ではありますが、幾つか制限がありますので、データベース・パフォーマンス・チューニング・ガイドの「高速収集の使用に関する制限事項」セクションをご参照ください。
また、よくお問合せ頂くのですが、残念ながらアプリケーション開発者からニーズが多いJSONデータ型との組み合わせにも制限がありますのでご注意ください。19cでは次のような構文でチェック制約を利用してJSONデータ型の列を実装しますが、DBRU19.8で試した結果としては、次の通り、Fast Ingestを有効化出来ていません。
SQL> -- JSON列で拡張(32k)VARCHAR2列を利用した場合はFast Ingestの有効化に失敗 CREATE TABLE j_purchaseorder (id VARCHAR2 (32) NOT NULL PRIMARY KEY, date_loaded TIMESTAMP (6) WITH TIME ZONE, po_document VARCHAR2 (23767) CONSTRAINT ensure_json CHECK (po_document IS JSON)); alter table J_PURCHASEORDER MEMOPTIMIZE FOR WRITE ; * ERROR at line 1: ORA-62152: MEMOPTIMIZE FOR WRITE feature cannot be enabled on table with LOBs. SQL> -- JSON列で拡張(32k)VARCHAR2列を利用しなくても仮想列（VIRTUAL COLUMN）扱いとなり失敗 drop table j_purchaseorder purge; CREATE TABLE j_purchaseorder (id VARCHAR2 (32) NOT NULL PRIMARY KEY, date_loaded TIMESTAMP (6) WITH TIME ZONE, po_document VARCHAR2 (100) CONSTRAINT ensure_json CHECK (po_document IS JSON)) SEGMENT CREATION IMMEDIATE MEMOPTIMIZE FOR WRITE ; * ERROR at line 1: ORA-62154: MEMOPTIMIZE FOR WRITE feature cannot be enabled on table with VIRTUAL COLUMNS.
21cで実装されたJSONデータ型についてはちょっと試せていませんが、2021年4月現在のマニュアルには次の記載が残っているので、後日、21c上でも確認してみたいと考えています。
「JSON is only supported stored as a 4K VARCHAR2 and not as a LOB. Extended 32K string lengths are not supported (i.e. max_string_size=extended).」
久しぶりの記事執筆で、思うように筆（指）が進みませんでしたが、少しずつリハビリして、皆様の日頃の業務に貢献できる情報を展開してければと考えておりますので、どうぞ次回以降も引き続き、ご愛読の程を宜しくお願いいたします。
・・・と言うことで、
一旦、記事の終わりを宣言しておいて、ココからは後回しにしていたオマケ「私の検証の実施方法と大きな過ち」について、少し冗長で乱文となりますが解説しておきます。ご興味のある方だけお付き合いください（笑）
下記の検証結果（再掲）に基づいて、次の4点について順番に説明していきますね。
- シーケンスオブジェクトを作成した理由
- NO_GATHER_OPTIMIZER_STATISTICSヒント
- PL/SQLループじゃなくて、シェルスクリプトのループを使った理由
- とは言え、シェルスクリプトのループの注意点（大きな過ち）
$ sqlplus TRY/TRY123456
SQL> -- INSERTで使用するシーケンスを作成
create sequence SEQ56F start with 10000001 increment by 1 cache 100000;
exit ;
### INSERTを5万回繰り返すシェルスクリプトを用意
$ cat run.sh
#!/bin/sh
MAX_VAL=50000
i=0; while [ $i -lt ${MAX_VAL} ]; do
echo "
insert /*+NO_GATHER_OPTIMIZER_STATISTICS MEMOPTIMIZE_WRITE */ into TAB56F values (SEQ56F.nextval, SYSDATE, lpad(to_char(SEQ56F.currval), 100, 'A'));"
i=`expr $i + 1`
done | sqlplus -S TRY/TRY123456 > /dev/null
### シェルスクリプトの実行
$ sh ./run.sh
### 一件INSERTを5万回連続実行に要した時間の確認（この測定方法は適切ではない）
$ sqlplus TRY/TRY123456
SQL> -- Fast Ingest機能が有効な表
select (max(COL2) - min(COL2)) * 24 * 3600 as "ELAPSED_TIME(S)" from TAB56F ;
ELAPSED_TIME(S)
---------------
50
オマケ1. シーケンスオブジェクトを作成した理由
ある機能の有効性を検証する場合、その機能の効果が最大限となるように準備したいものです。全く関係ない設定がボトルネックとなり、目的の機能の有効性が証明されないケースがあるからです。
今回は、1件INSERTを5万回繰り返す処理なので、毎回INSERT文のハードパースが実行される点を回避したいと考えました。回避する方法として、リテラルなINSERT文ではなくバインド変数を利用したINSERT文を実行しようと思いましたが、SQL*Plusでバインド変数をセットする処理が重いことを思い出しました（第36回 SQLのパース処理とバインド変数の理解）ので止めました。
バインド変数をセットせずに毎回同じINSERT文を実行させたい・・・と悩んだ結果、シーケンスオブジェクトを使ってINSERT文を書けば、毎回同じINSERT文で異なる値をセット出来ると気付いたんですね。さらに、シーケンスオブジェクトのCACHE属性を大きくすることで、NEXTVALを獲得する性能がボトルネックにならないように調整しました。ちなみに、PL/SQLのループ処理を使えば、自動的にバインド変数化できるとも考えたのですが、後述三点目で解説する理由で、PL/SQLループの採用はそもそも見送りました。
オマケ2. NO_GATHER_OPTIMIZER_STATISTICSヒント
私はExadata上で今回の検証を実施していたのですが、INESRT文の実行計画を確認してみると、リアルタイム統計の取得が動いていたんですね。今回の検証では必要のない機能でしたので、何となく止めてみました。結果的には止める必要は無かった（リアルタイム統計のオーバーヘッドは全く無かった）のです。
オマケ3. PL/SQLループじゃなくて、シェルスクリプトのループを使った理由
通常、COMMITを発行した場合には、サーバー・プロセスがLGWRプロセスによるREDOログの書き出しが完了するのを待機しますが、PL/SQLブロック内でのCOMMITはPL/SQLブロック内のトランザクションの整合性を保ちはするものの、パフォーマンスを向上させる目的で、LGWRがREDOログの書き出しを完了するのを待機しないと私は理解しています。よって、今回の検証のように、COMMITの実行時間の差が重要視されるケースでは、PL/SQLループは不適切であるとの持論があります。
オマケ4. とは言え、シェルスクリプトのループの注意点（書き方の大きな過ち）
ここまで自分なりに完璧な準備を施したつもりでしたが、通常INSERTでもFast IngestによるINSERTでも、各表の二番目の列はDATE型に格納されている時刻を利用して（最後にINSERTされたレコードのCOL2列から最初にINSERTされたレコードのCOL2列を引き算する）、シェルスクリプトでの合計5万回のループ処理で生成された5万個のINSERT文をSQL*Plusで要した時間を計算する限り、50秒前後を記録するだけで、差が生まれませんでした。（気付けた大したことは無いのですが、その時は、なかなか閃かずに時間が経過・・・）データベース側が高速だとしても、SQLを発行するクライアント側がボトルネックになっているかもしれない。とようやく思い立ち、実際に、INSERT文をスペース（空）の状態にして単純な5万回ループ処理の実行時間を計測してみたところ、結果としては49秒を要しました。今思うと正直、とても恥ずかしいループの書き方をしていましたね、私。。。単純にカウントアップする処理が重いので、seqコマンドを使って書き直したら見事に解決してゼロ秒です。
$ cat loop_test.sh
#!/bin/sh
MAX_VAL=50000
date
i=0; while [ $i -lt ${MAX_VAL} ]; do
echo ""
i=`expr $i + 1`
done > /dev/null
date
$ sh ./loop_test.sh
Thu Apr 8 15:15:23 JST 2021
Thu Apr 8 15:16:12 JST 2021 ==> 49秒
$ cat loop_test2.sh
#!/bin/sh
MAX_VAL=50000
date
for i in `seq 1 ${MAX_VAL}` ; do
echo ""
done > /dev/null
date
$ sh ./loop_test2.sh
Thu Apr 8 15:31:37 JST 2021
Thu Apr 8 15:31:37 JST 2021 ==> 0秒
よって、seqコマンドを使ったシェルスクリプトのループ処理にしても良かったのですが、負荷掛けクライアント側の状況に依存せずにFast Ingestの実行時間を確認する方法もご紹介したかったので、結果的には、V$SQLビューのELAPSED_TIME列で確認して頂き、Fast Ingestの効果を皆様に体験して頂くことにしました。もちろん次の通り、seqコマンドを使ったループ処理を採用することで実際に綺麗にFast Ingestの効果を確認することもできるので、ご興味のある方は、ぜひチャレンジしてみてくださいね。
演習3（ループの書き方を変えて再チャレンジ）. 各表へ一件INSERTを繰り返し実行して、Memoptimized Rowstore – Fast Ingestの効果を測定してください。
$ sqlplus TRY/TRY123456
SQL> -- 各表へのINSERTで使用するシーケンスを作成
create sequence SEQ56N start with 10000001 increment by 1 cache 100000;
create sequence SEQ56F start with 10000001 increment by 1 cache 100000;
exit ;
### INSERTを5万回繰り返すシェルスクリプトを用意
$ cat run.sh
#!/bin/sh
MAX_VAL=50000
for i in `seq 1 ${MAX_VAL}` ; do
echo "
insert into TAB56N values (SEQ56N.nextval, SYSDATE, lpad(to_char(SEQ56N.currval), 100, 'A'));
commit ;"
done | sqlplus -S TRY/TRY123456 > /dev/null
for i in `seq 1 ${MAX_VAL}` ; do
echo "
insert /*+MEMOPTIMIZE_WRITE */ into TAB56F values (SEQ56F.nextval, SYSDATE, lpad(to_char(SEQ56F.currval), 100, 'A'));"
done | sqlplus -S TRY/TRY123456 > /dev/null
### シェルスクリプトの実行
$ sh ./run.sh
### 一件INSERTを5万回連続実行に要した時間の確認
$ sqlplus TRY/TRY123456
SQL>
-- 通常表
select (max(COL2) - min(COL2)) * 24 * 3600 as "ELAPSED_TIME(S)" from TAB56N ;
ELAPSED_TIME(S)
---------------
36
-- Fast Ingest機能が有効な表
select (max(COL2) - min(COL2)) * 24 * 3600 as "ELAPSED_TIME(S)" from TAB56F ;
ELAPSED_TIME(S)
---------------
8
非常に遠回りしてしまいましたが、Fast Ingest機能によって、5万回のINSERT処理が36秒から8秒へ高速化することが確認出来ています。素晴らしい！！！（繰り返しになりますが、こちらに記載の効果はあくまで参考情報としての扱いで、その効果を保証するものではなく、実行環境に依存しますのでご注意くださいね。）
それにしても、自分のスキルの鈍り具合が非常に悲しかったですし、それによって、最新のOracle Databaseのパフォーマンス機能の効果をスムーズに確認できなかったのは情けないですね。。。でも、正常なエラー無しのケースだけを学習していては万が一のトラブル時には対応できないです。各機能が持つ特徴や制限、正常系ではない時のエラー番号、DB以外の知識も大いに必要になるものだと、私の少ない経験からも思うので、恥ずかしいですが記事化させて頂きました。少しでも皆様の参考になったのであれば嬉しい限りです。
本当に、最後の最後までお付き合い頂きましてありがとうございました。次回以降もどうぞよろしくお願い致します。