Oracleに関するTips | Oracle/MySQL向け負荷テスト・性能測定ツール

Tips・サマリー
Tips

Tips・サマリー

負荷の高いSQLを調査する方法
待機イベントについて
表領域の使用状況を確認する方法
負荷テスト実施にあたっての事前確認項
Oracleのバックアップ取得方法

Tips

Tips.1 負荷の高いSQLを調査する方法

動的パフォーマンスビュー「V$SQL」を使用し、負荷の高いSQL文を調査するためのサンプルSQLを示します：


select round(CPU_TIME/1000,2)     "CPU_TIME(ms)",
       round(ELAPSED_TIME/1000,2) "ELAPSED_TIME(ms)",
       BUFFER_GETS,
       DISK_READS,
       round(BUFFER_GETS/EXECUTIONS,2) "BUFFER_GETS/EXEC",
       round(DISK_READS/EXECUTIONS,2)  "DISK_READS/EXEC",
       EXECUTIONS,
       PARSE_CALLS,
       HASH_VALUE,
       ADDRESS
from V$SQL
WHERE EXECUTIONS > 0
order by CPU_TIME desc;

#	カラム名	説明
1	CPU_TIME	該当SQLをOracleが処理するために使用したCPU時間の累積値を示します。単位はマイクロ（1/100万）秒です。
2	ELAPSED_TIME	該当SQLの処理をOracleが開始し、処理を完了しきるまでにかかった経過時間の累積値を示します。単位はマイクロ（1/100万）秒です。
3	BUFFER_GETS	該当SQLの処理を行うために走査したデータベースバッファキャッシュのデータブロック数の累積値を示します。
4	DISK_READS	該当SQLの処理を行うためにディスクから読み取ったデータブロック数の累積値を示します。
5	EXECUTIONS	該当SQLの実行を行った累積数を示します。
6	PARSE_CALLS	該当SQLの解析を行った累積数を示します。
7	HASH_VALUE ADDRESS	HASH_VALUEとADRRESSの値を使用し、動的パフォーマンスビュー「V$SQLTEXT」から該当SQLの全文を取得することができます。

注意点①

STATSPACKレポートのSQLセクションでは以下の項目でSQL文がソートされて出力されます：

CPU（CPU_TIME）
Elapsed（ELAPSED_TIME）
Gets（BUFFER_GETS）
Reads（DISK_READS）
Executions
Parse Calls（PARSE_CALLS）

同様の結果を得たい場合には、上記SQL文の「order by」句の条件を変更してSQLを実行してください。

注意点②

アプリケーションがバインド変数を使用せず、WHERE句にリテラルを使用しているような場合、同種のSQLであってもOracle内部で再利用されず、すべて別のSQLとみなされます。上記サンプルSQLだけではこの種のSQL文は特定できないことに注意してください。

以下はSQLの先頭128バイトをソートして出力するサンプルSQLです：


column SQL_TEXT for A128
select * from
   (select ADDRESS,
           HASH_VALUE,
           substr(SQL_TEXT,1,128) SQL_TEXT
    from V$SQL
    order by SQL_TEXT desc)
where rownum <= 256;

注意点③

SQL文の全文を取得したい場合には以下のSQLを使用し、動的パフォーマンスビュー「V$SQLTEXT」を検索します。これはV$SQLのカラム「SQL_TEXT」では、SQLの先頭から1000バイトまでしか表示されないためです。


SELECT SQL_TEXT FROM V$SQLTEXT
WHERE HASH_VALUE = XXXXXXXXXX and ADDRESS = 'XXXXXXXX'
ORDER BY PIECE;

Tips.2 待機イベントについて

以下にOracleの主要な待機イベントの意味と対策を示します。

buffer busy waits ( R10.1以降 : Read by other session ）

[意味]

以下のいずれかのケースにおいて、複数のセッションが同一データブロックにアクセスしようとして待機（データブロック競合）が発生した際に検知されるイベント：

あるセッションがアクセス対象となるデータブロックをディスク上からキャッシュ（DATABASE BUFFER CACHE）上に読み取っている
あるセッションがキャッシュ上のデータブロックを更新している

[具体例]

ひとつのデータブロック内の行数が多すぎる
多数のセッションが同一データブロックにINSERTしようとしている

[対策]

アクセス対象となっているデータファイルを高速なディスク上に配置する。
CPUを高速なものに置き換える。
自動セグメント領域管理（ASSM）を使用する。
PCTFREEを増やす（ひとつのブロック内に格納される行数を減らす）
ブロックサイズを小さくする。

log buffer space

[意味]

セッションがログ・バッファ（LOG_BUFFER）にデータを書き込む速度が、LGWRによる書き出しを上回り、ログ・バッファへの書き込みを待機する際に発生するイベント。

[具体例]

ログ・バッファ（LOG_BUFFER）の値が小さすぎる。

[対策]

初期化パラメータ「LOG_BUFFER」の値を大きくする。

log file parallel write

[意味]

LGWRプロセスがREDOログ・バッファ（LOG_BUFFER）内のデータをREDOログファイルに書き込む際に発生する待機イベント。

[対策]

REDOログファイルを高速なディスク上に配置する。

log file sync

[意味]

サーバプロセスがコミットを発行し、待機する際に発生するイベント。

[対策]

バッチプログラム等であれば、コミットの回数を減らす。

db file sequential read

[意味]

セッションがインデックススキャン（索引を使用した順次読み取り）により待機した際に発生するイベント。

[対策]

データベース・バッファ・キャッシュサイズを増やす。

[補足]

本イベント「db file sequential read」はデータベースの構造およびSQL文が適切に構成されているとした場合、やむを得ない（望ましい）待機イベントといえます。

db file scattered read

[意味]

[具体例]

検索対象となっているテーブルに適切なインデックスが作成されていない
初期化パラメータ「DB_FILE_MULTIBLOCK_READ_COUNT」の値が大きく過ぎ、オプティマイザが全表検索を選択している

[対策]

データベースの設計上、フルテーブルスキャンが望ましくない場合：
- 検索対象となっているテーブルに適切なインデックスを作成する。
データベースの設計上、フルテーブルスキャンが望ましい場合：
- 初期化パラメータ「DB_FILE_MULTIBLOCK_READ_COUNT」の値を大きくする
- アクセス対象となるデータファイルを高速なディスク上に配置する

Tips.3 表領域の使用状況を確認する方法

以下にOracleの表領域の使用状況（使用済み容量・空き容量・領域サイズ）を確認するSQL文を示します。

各表領域ごとの領域サイズ）


select TABLESPACE_NAME, BYTES/1024/1024 "Total MBytes" from dba_data_files
       order by TABLESPACE_NAME;

各表領域ごとの使用済み容量）


select TABLESPACE_NAME, sum(BYTES)/1024/1024 "Used MBytes" from dba_extents
       group by TABLESPACE_NAME
       order by TABLESPACE_NAME;

各表領域ごとの空き容量）


select TABLESPACE_NAME, sum(BYTES)/1024/1024 "Free MBytes" from dba_free_space
       group by TABLESPACE_NAME
       order by TABLESPACE_NAME;

Tips.4 負荷テスト実施にあたっての事前確認項目

以下にOracleの負荷テストを実施するにあたって事前に検討・決定しておくべき事項を示します。

性能統計情報取得ツール

Oracle性能統計情報の取得要領を検討する。以下、例：

STATSPACK
AWR
SQL（V$SYSTEM_EVENT, V$SQLなど）

OS性能統計情報取の取得要領を検討する。以下、例：

vmstat
mpstat
iostat
sar
top

Oracle初期化パラメータ

STATISTICS_LEVEL : TYPICAL以上
TIMED_STATISTICS : TRUE

メモリキャッシュ

アプリケーションの再起動実施有無
ORACLEの再起動実施有無
オペレーティングシステムの再起動実施有無
性能統計情報取得開始前の負荷がけ実施時間

Oracleオプティマイザ

オプティマイザ統計（DBMS_STATS）情報の取得有無

テストデータ

テストデータのバックアップ／リストア実施要領検討

EXPORT/IMPORT
オフライン（コールド）バックアップ

ディスク領域メンテナンス

性能統計情報格納リポジトリ（例：SYSAUX表領域）のサイズ検討
アーカイブREDOログファイルの削除タイミング検討

Tips.5 Oracleのバックアップ取得方式

物理バックアップ

Oracleデータベースを構成する物理ファイル（制御ファイル・オンラインREDOログファイル・データファイルなど）をOSコマンドや「Recovery Manager」といったツールを使用してコピーを作成することにより行うバックアップ方式。

[用途]

ディスク破損などのハードウェア障害対策
物理的なオペレーションミス（誤ってデータファイルを削除）などのユーザエラー対策

[リカバリ可能な時点]

アーカイブREDOログ・オンラインREDOログを使用して最終コミットの時点までリカバリすることが可能

論理バックアップ

Oracleデータベース内の論理的なオブジェクト（テーブルの定義情報やデータ）を「exp」といったツールを使用し、ファイル（ダンプファイル）に出力させることによって行うバックアップ方式。

[用途]

データベース間でのデータ移行
データベースのバージョンアップに伴うデータ移行
論理的なオペレーションミス（誤ってテーブルを削除）などのユーザエラー対策

[リカバリ可能な時点]

バックアップ取得時点
アーカイブ／オンラインREDOログを使用したリカバリは不可

オフライン（コールド）バックアップ

データベースを停止させた状態で取得するバックアップ方式。オンラインバックアップ（後述）と比較してバックアップ・リストアの手順がシンプルであるため、システム停止が可能な場合は基本的にオフラインバックアップの実施を検討する。

[バックアップ対象]

すべての制御ファイル
すべてのデータファイル
すべてのオンラインREDOログファイル ※
オンラインREDOログファイルは必ずしもバックアップに含めなくとも良い。すべての制御ファイルとすべてのデータファイルをリストアし、RESETLOGS OPENを行うことで、新規にオンラインREDOログを生成することができるため。

オンライン（ホット）バックアップ

データベースを稼動させた状態で取得するバックアップ方式。システム停止が困難な場合、本バックアップ方式の実施を検討する。データベースをアーカイブログモード（Archivelog mode）で運用する必要あり。

バックアップの取得方法としては以下のふたつの手法がある：

Begin/End Backup + OSのコピーコマンド
Recovery Manager

1. については、SQL「alter <表領域名> begin backup」を使用してデータファイルをバックアップモードに変更してから、データファイルをOSコマンドなどを使用してコピーを作成し、バックアップとするもの。コピー完了後は「alter <表領域名> end backup」を発行する。

2. についてはOracleのバックアップユーティリティであるRecovery Managerを使用して、バックアップ（バックアップセットやイメージコピー）を作成する。