Oracle SQLのパフォーマンスチューニングのテスト方法

OracleのSQLのパフォーマンステストの方法

※ここで紹介するSQLのパフォーマンステストの方法は、Oracleデータベース全体で使用されるメモリ領域をフラッシュするので実行する際には影響範囲を確認の上、実行する事をおすすめします。

パフォーマンステストの手順は以下の通りです。

1. SYSユーザでOSのファイルシステムにダイレクトI/Oを有効化する。
2. SYSユーザで「共有プール」をフラッシュします。
3. SYSユーザで「データベース・バッファ・キャッシュ」をフラッシュします。

OSのファイルシステムにダイレクトI/Oを有効化する

Oracleのデータ・ファイルのデータがOSのファイルシステム・キャッシュにキャッシュされている場合、データを読みに行く先はどこでしょうか？

そうです！ハードディスクではなく物理メモリを読みに行ってしまいます。
キャッシュされている状況によって読み込み先が変わるとSQLのパフォーマンスが変わってしまいます。

パフォーマンスチューニングをする際には修正前後のSQLを実行する環境に差異があったら正しい性能テストができないですよね＼(^o^)／

そのため直接ハードディスクからデータを読み取るダイレクトI/Oに変更しましょう。

--ファイルシステムI/Oの設定の確認
show parameter filesystemio
--ファイルシステムI/Oの設定をダイレクトI/Oに変更
alter system set FILESYSTEMIO_OPTIONS='SETALL' scope=spfile;
--設定を適用するためにDBを再起動
shutdown immediate;
startup

>sqlplus / as sysdba
SQL> show parameter filesystemio

NAME                                 TYPE                 VALUE
------------------------------------ -------------------- --------
filesystemio_options                 string               NONE

SQL> alter system set FILESYSTEMIO_OPTIONS='SETALL' scope=spfile;

システムが変更されました。

SQL> shutdown immediate;
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。
SQL> startup
ORACLEインスタンスが起動しました。

Total System Global Area 1068937216 bytes
Fixed Size                  2260048 bytes
Variable Size             763364272 bytes
Database Buffers          297795584 bytes
Redo Buffers                5517312 bytes
データベースがマウントされました。
データベースがオープンされました。
SQL> show parameter filesystemio

NAME                                 TYPE                 VALUE
------------------------------------ -------------------- --------
filesystemio_options                 string               SETALL

「FILESYSTEMIO_OPTIONS」の設定値の説明

SYSユーザで「共有プール」をフラッシュします。

「共有プール」には、SQLの実行計画等がキャッシュされています。

キャッシュされている状況によってソフトパースとハードパースどちらかが実行されるためパフォーマンスが変わってしまいます。

パフォーマンスチューニングをする際には修正前後のSQLを実行する環境に差異があったら正しい性能テストができないですよね＼(^o^)／

そのため、「共有プール」をフラッシュして必ずハードパースされるようにしましょう。

alter system FLUSH SHARED_POOL;

SQL> alter system FLUSH SHARED_POOL;

システムが変更されました。

SYSユーザで「データベース・バッファ・キャッシュ」をフラッシュします。

「データベース・バッファ・キャッシュ」上にデータがキャッシュされている場合、バッファ・キャッシュ上を読みに行きます。
これでは、キャッシュされている状況によって、パフォーマンスが変わってしまいます。

パフォーマンスチューニングをする際には修正前後のSQLを実行する環境に差異があったら正しい性能テストができないですよね＼(^o^)／

そのため、「データベース・バッファ・キャッシュ」をフラッシュして環境を統一しましょう。

alter system FLUSH BUFFER_CACHE;

SQL> alter system FLUSH BUFFER_CACHE;

システムが変更されました。

これでパフォーマンステストをする準備ができました＼(^o^)／
SQLを実行してみましょう！

再度SQLを実行する際に、「共有プール」と「データベース・バッファ・キャッシュ」をフラッシュすることを忘れずに！