「SQLの実行計画」から処理時間の差を理解する：しばちょう先生の試して納得！ DBAへの道（改）（1）（2/3 ページ）

データベース技術や知識は、座学だけでなく「実際に手を動かして、実際に試して」理解を深めよう──。日本オラクルのデータベーススペシャリストが「新人をDBAに育てる際に使用する課題」をベースに、DBAがすぐ実践できる即効テクニックを紹介。第1回目は「“SQLの実行計画”から処理時間の差を理解する方法」を解説します。

[柴田長，日本オラクル株式会社]

演習2：「バッファキャッシュ」上のデータをフラッシュする

$ sqlplus / as sysdba
SQL> alter system FLUSH BUFFER_CACHE;

　実行計画とは全く関係ありませんが、一般的にSQLの性能テストを実施する前にはバッファキャッシュ上にキャッシュされたデータをフラッシュ（クリア、クリーンアップなどとも呼ばれます）しておきます。

　バッファキャッシュ上にデータがキャッシュされている場合、SQL実行時にストレージへ読みに行く必要がないのでレスポンスが高速になる傾向があります。しかし、SQLの性能テストは繰り返しチューニングを行うので、SQL実行のたびにバッファキャッシュの状態が変化していてはチューニング効果を正確に把握できません。バッファキャッシュの状態を統一するのがこの工程の目的です。

　例えばインスタンス起動直後（バッファキャッシュに何もキャッシュされていない状態）にチューニング前の性能測定を行い、その後、特にバッファキャッシュをフラッシュせず（チューニング前の性能測定でキャッシュされたデータが残っている状態）にチューニング後の性能測定を行ったにもかかわらず、「チューニングの結果、SQLが高速化した」と誤って判断してしまうケースがまれにあるので注意しましょう。もちろんSQLのアクセスするデータが「バッファキャッシュ上に100％キャッシュされた統一条件下」での性能テストならば話は違いますが、要は「どの前提条件での性能テストなのか」を明確にした上で測定することです。

　ちなみに、「バッファキャッシュ上のデータが本当にフラッシュされたのか」はどう確認すればよいのか。そこに気が付いた鋭い方のために、バッファキャッシュ内をのぞくSELECT文を用意しました。ぜひ試してみてください。

$ sqlplus / as sysdba
SQL> 
col OBJECT_NAME for a36
set pagesize 50000
set linesize 150
select
  DO.OWNER, DO.OBJECT_NAME, BH.STATUS, count(*) BLOCKS
from V$BH BH, DBA_OBJECTS DO
where BH.OBJD = DO.DATA_OBJECT_ID
  and BH.TS# > 0
  and BH.STATUS != 'free'
group by DO.OWNER, DO.OBJECT_NAME, BH.STATUS
order by 1, 2, 3;

バッファキャッシュ内をのぞくSELECT文

演習3：初期化パラメータ「FILESYSTEMIO_OPTIONS」を適切に設定する

　Oracle DatabaseにおいてOSのファイルシステムキャッシュ（ページキャッシュとも呼ばれます）が使用されないように、初期化パラメータ「FILESYSTEMIO_OPTIONS」を適切に設定します。

$ sqlplus / as sysdba
SQL> 
/* 現在の設定を確認 */
show parameter filesystemio
　
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      none
　
　
/* SPFILE上の設定値を変更 */
alter system set FILESYSTEMIO_OPTIONS='SETALL' scope=spfile;
/* SPFILE上で変更した状態でデータベース・インスタンスを立ち上げ直して反映 */
shutdown immediate;
startup
/* 設定が変更されているのかを確認 */
show parameter filesystemio
　
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      setall

演習3　初期化パラメータ「FILESYSTEMIO_OPTIONS」を適切に設定する

　こちらは初期化パラメータの変更なので、それほど難しくはないと思います。ただし「このパラメータに設定可能な文字列が何を意味するのか」がリファレンスマニュアルに記載されていないので、少し解説しておきます。

　SQLの性能テストを実施する上で排除しておきたいのが、OSのファイルシステムキャッシュの影響です。前述した演習2のようにバッファキャッシュをフラッシュしても、OracleのデータファイルがOSのファイルシステムキャッシュにキャッシュされている場合、サーバープロセスがデータブロックを読みに行く先は、ストレージではなく物理メモリになります。そのためデータブロックの読み込み速度が高速化する傾向があります。

　「速いのに何が悪いの？」と思うかもしれませんが、安定性の観点からは、データファイルがOSのファイルシステムキャッシュにキャッシュされているか否かによって、SQLの実行時間にブレが生じてしまうので、好ましくありません。この影響を排除する方法は、OSのファイルシステムキャッシュから読み込まない、直接ストレージからデータを読み取るダイレクトI/Oを使うことです。

　「FILESYSTEMIO_OPTIONS」は、ファイルシステム上に配置しているデータファイルに対するI/Oの設定を行うパラメータです。ファイルシステムに対する非同期I/OやダイレクトI/Oの有効／無効を制御できます。

FILESYSTEMIO_OPTIONSのパラメータ	内容
none	ファイル・システムに対する非同期I/OとダイレクトI/Oを無効化
setall	ファイル・システムに対する非同期I/OとダイレクトI/Oを有効化
directIO	ファイル・システムに対するダイレクトI/Oを有効化（非同期I/Oは無効）
asynch	ファイル・システムに対する非同期I/Oを有効化（ダイレクトI/Oは無効）
※OSの種類やバージョンによってデフォルト設定が異なります。また、有効化できない設定値もあります

　ちなみに、データファイルの配置場所（ファイルシステム、RAWデバイスやASMディスクグループ）に依存せずに、非同期I/Oを制御する初期化パラメータ「DISK_ASYNCH_IO」も存在します。非同期I/Oを有効化したい場合には、必ずこのパラメータを「TRUE（デフォルト値）」に設定してください。

　また、FILESYSTEMIO_OPTIONS初期化パラメータでダイレクトI/Oを有効化しても、サーバープロセスがOSのファイルシステムキャッシュへ「読みに行かないだけ」であり、キャッシュはされ続けます。特にLinux OSでは物理メモリに空きがある限りキャッシュする動作になります。Oracle Databaseはバッファキャッシュでもデータをキャッシュしているので、キャッシュの二重持ちの状態になります。物理メモリの使用効率的を考えると、よくないことは明白です。このため、物理メモリに余裕がある場合には、Oracle Databaseのバッファキャッシュのサイズを大きくすることをお勧めします。