Oracleパフォーマンスチューニングの基礎知識と効率化の5手順

Oracleデータベースを運用する中で、「特定の処理が遅い」「突然画面の応答が返ってこなくなった」といった深刻なトラブルに直面したことはないでしょうか。システムのボトルネックを解消してレスポンスを改善するには、正しい現状分析と適切なチューニング手順を踏むことが不可欠です。

この記事では、初心者から中級者のDBA・アプリケーション開発者に向けて、Oracleパフォーマンスチューニングの基礎知識や現場で即実践できる5つのステップ、そして重要な分析ツールであるAWRとStatspackの使い分けをテキスト図解付きで分かりやすく解説します。

結論：パフォーマンス改善の最短手順（やることリスト）

パフォーマンス問題が発生した際は、闇雲に設定変更を行わず、以下の手順に沿って段階的にボトルネックを絞り込みます。

• 手順1：症状の把握 – AWR や Statspack、V$ビュー を使って遅延の発生時間帯や影響範囲を定量的に特定する
• 手順2：ボトルネックの特定 – V$SQL などからリソースを過剰に消費している高負荷な SQL（Top SQL）を洗い出す
• 手順3：原因の分析 – 対象 SQL の実行計画（EXPLAIN PLAN）を取得し、フルスキャンや非効率な結合がないかを確認する
• 手順4：改善の実施 – 最適なインデックスの作成、最新の統計情報収集、SQL 自体のリライトを行う
• 手順5：効果の検証 – SQLトレース（tkprof）や実行時間の比較を行い、施策前後での数値的な改善効果を証明する

パフォーマンスチューニングとは？

仕組みと定義

Oracleにおけるパフォーマンスチューニングとは、システムの応答時間（レスポンスタイム）の短縮やスループット（単位時間あたりの処理量）の向上を目的に、データベースの稼働状況を「現状分析」し、「原因特定」を経て「最適な改善」を施す一連の最適化プロセスのことです。

3層構造で考えるアプローチ

データベースシステムの負荷やボトルネックは、大きく以下の3つのレイヤー（層）に分類して切り分けを行います。

+-------------------------------------------------------------+
| 1. アプリケーション層  : SQL, PL/SQL, アプリケーションロジック|
+-------------------------------------------------------------+
| 2. データベース層      : 統計情報, 実行計画, インデックス, メモリ|
+-------------------------------------------------------------+
| 3. OS・インフラ・I/O層 : CPU, 物理メモリ, ディスクI/O, ネットワーク |
+-------------------------------------------------------------+

初心者向け一口メモ

チューニング全体の効果のうち、約8割は「アプリケーション層（SQL）」および「データベース層」の改善に起因すると言われています。OSパラメータやハードウェアの変更はリスクが高く効果が限定的な場合が多いため、まずは個別SQLの見直しやインデックス設計から着手するのが鉄則です。

パフォーマンスチューニングの進め方（5ステップ）

Oracleのパフォーマンス改善は、以下の5つのステージを順番に進めることで、手戻りなく安全に対策を実施できます。

【1. 症状の把握】 どの処理がどれくらい遅いのかを定量化
      ↓
【2. ボトルネック特定】 SQL、ディスクI/O、CPU、ロックのどこに原因があるか
      ↓
【3. 原因の分析】 実行計画を確認し、非効率なアクセスの仕組みを特定
      ↓
【4. 対策の実施】 インデックス追加、統計情報収集、SQL修正
      ↓
【5. 効果の検証】 チューニング前後の数値を比較して効果を実証

Step1：症状の把握 〜どの処理が遅いのか〜

チューニングの第一歩は、主観的な「遅い」を、客観的な「数値（秒数、待機時間）」に変換することです。環境（エディションやライセンス）に応じて以下の最適な調査手段を選択します。

主な調査手段の比較

ツール名	概要	対応エディション
AWR	データベース全体の稼働統計情報を自動で時系列分析する標準機能	Enterprise Edition（※Diagnostic Packオプション必須）
Statspack	AWRの無償簡易版。スナップショットを手動またはジョブで取得して分析	Standard Edition 2 / Enterprise Edition
V$ビュー群	リアルタイムなメモリ上のセッション・SQL情報を直接確認可能	全エディション（SE2 / EE問わず利用可能）

AWRとStatspackの違い（詳細比較）

比較項目	AWR（Automatic Workload Repository）	Statspack（スタッツパック）
ライセンス	有償（EE + Oracle Diagnostic Pack）	無償（追加コストなし）
データ収集	バックグラウンド・プロセス（MMON）が自動収集	専用スクリプトによる手動またはDBMS_JOBでの定期実行
分析対象	OS統計、ASH（アクティブ・セッション履歴）を含む包括的情報	主要なシステム・イベント、待機イベント、Top SQL等の基本指標
出力形式	HTML、Text（Enterprise Manager等のGUI画面連携も強力）	Textのみ（専用のSQLスクリプトを実行してファイル出力）

Standard Edition 2環境ではStatspackの一択となり、Enterprise Edition環境かつライセンス要件を満たしている場合は、より詳細な分析が自動で行えるAWRの利用を強く推奨します。

Step2：ボトルネックの特定

システムの処理を遅延させているボトルネックは、主に次の4つのタイプに分類されます。

• SQLの実行遅延: 不適切な実行計画による大量データスキャン
• ディスクI/Oのボトルネック: メモリ（バッファ・キャッシュ）に乗り切らない物理ディスクからの過剰な読み込み
• CPU高負荷: 大規模なソート処理、ハッシュ結合、非効率なユーザー定義関数の多用
• ロック競合: 同一データに対する更新バッティング（enq: TX – row lock contention などの待機イベント）

リアルタイムで高負荷なSQLを特定するV$ビューの活用

現在データベース内でリソースを多く消費している上位10個のSQL（Top SQL）を、累積の実行時間（elapsed_time）をベースに特定するSQL文です。

動的パフォーマンス・ビュー（V$SQL）からデータを抽出するため、実行するには SELECT ANY DICTIONARY 権限、または SYS / SYSTEM ユーザーでの接続が必要です。また、マルチテナント環境（CDB/PDB）では、対象のPDBに接続して実行してください。

SELECT sql_id, elapsed_time, executions, sql_text
FROM v$sql
ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

SQLの意図と結果:

キャッシュ上に残っているSQLの中から、通算の処理時間が長い順に10件を取得します。これにより、チューニング対象とすべき最優先の sql_id（SQL固有の識別子）を突き止めることができます。

Step3：原因の分析 〜SQL実行計画の読み方〜

特定のSQLが遅い原因の大部分は、内部的なデータの読み込み手順である「実行計画」の非効率性にあります。

実行計画を取得する基本手順

オプティマイザ（Oracleのアクセス経路判定機能）が想定している現在の実行計画を確認します。

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SQLの意図と結果:

EXPLAIN PLAN 文で対象SQLのアクセス経路を解析して PLAN_TABLE に格納し、直後の DBMS_XPLAN.DISPLAY 関数で人間が読みやすい表形式に整形して結果を出力します。

実行計画で注目すべき重要キーワード

内部操作（オペレーション）	処理の仕組み	チューニングのチェックポイント
TABLE ACCESS FULL	テーブル全体のデータを最初から最後まで全件走査（フルスキャン）	検索条件に合致する適切なインデックスが不足していないかを確認
INDEX RANGE SCAN	インデックスの特定範囲のみをスキャンする効率的なアクセス	絞り込み条件で正しくインデックスが活用されているかを評価
NESTED LOOPS	片方のテーブルをループさせながらもう片方を結合（入れ子ループ）	駆動表（外側）が小規模で、内部表（内側）に結合キーのインデックスがあるか
HASH JOIN	結合キーをメモリ上にハッシュ展開して大量データを高速結合	メモリ（PGA）を消費するため、オプティマイザ用の統計情報が最新か

Step4：改善の実施

ボトルネックの原因に応じて、以下のいずれか（または組み合わせ）の改善策を実施します。

1. インデックス（索引）の作成

WHERE 句の絞り込み条件に指定されている列に対して、適切なBツリー・インデックスを作成し、フルスキャンの回避を図ります。

CREATE INDEX idx_emp_dept ON employees (department_id);

SQLの意図と結果:

employees テーブルの department_id 列にインデックスを作成します。以降、同列を条件とした検索において全件走査からインデックス範囲スキャン（INDEX RANGE SCAN）に切り替わり、I/O量が劇的に削減されます。

2. オプティマイザ統計情報の収集

統計情報が古いと、オプティマイザがデータ量を誤認し、非効率な実行計画を選択してしまいます。最新のデータ状態を反映させるために統計情報を手動で再収集します。

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMPLOYEES');

SQLの意図と結果:

SCOTT スキーマ内の EMPLOYEES テーブルのレコード件数やデータ分布の最新状態を計測し、辞書情報に保存します。これにより、オプティマイザが最新の実態に即した正しい実行計画を選択できるようになります。

3. SQL自体のリライト（書き換え）

• 不要な列の排除: SELECT * の使用をやめ、アプリケーションで本当に必要な列名だけを明示的に指定することで、ネットワークおよびメモリの負荷を減らします。
• 冗長なネストの削除: サブクエリや IN 句、EXISTS 句が複雑に入り組んでいる場合は、結合（JOIN）に書き換えられないか検討します。

Step5：効果検証 〜チューニングは「やって終わり」ではない〜

対策を実施した後は、実際の処理時間がどれほど短縮されたかを定量的な数値で確認します。セッションごとの詳細な挙動を追跡するには SQLトレース を利用します。

SQLトレースの取得と解析手順

以下の手順を、調査対象のSQLを実行するセッション内で実行します。

ALTER SESSION SET SQL_TRACE = TRUE;

SELECT * FROM employees WHERE department_id = 10;

ALTER SESSION SET SQL_TRACE = FALSE;

SQLの意図と結果:

対象SQLの実行に伴うディスク読み込み回数、CPU時間、パース（解析）回数などの詳細ログをサーバー上の .trc（トレースファイル）に出力します。

生成されたトレースファイルはテキスト形式ですが、そのままでは非常に読みづらいため、Oracle標準のユーティリティコマンド tkprof（ティーケープロフ）を使用して、OSのコマンドライン上で整形します。

tkprof ora_12345.trc output.txt sort=elapsed

コマンドの意図と結果:

未整形のログファイル ora_12345.trc を読み込み、実行時間の長い順（sort=elapsed）に並び替えた美しい解析結果レポート output.txt を生成します。

トラブルシューティング（代表的なORAエラーと対処法）

チューニング操作中や、高負荷な状態のOracleデータベースで発生しやすいエラーの原因と具体的な解決手順です。

エラーコード	主な原因	確認方法	参照系・安全な対処手順
ORA-01555 snapshot too old	長時間実行中のSQLが、他セッションの更新によって上書きされた過去データ（UNDO）を見失った	アラートログの確認、およびエラーが発生したSQLの処理時間の特定	更新頻度の高い時間帯を避けてバッチを実行するか、UNDO_RETENTION パラメータの値を拡張する（要DBA権限）
ORA-01652 unable to extend temp segment	大規模なソート処理やハッシュ結合により、一時表領域（TEMP）の容量が枯渇した	V$SORT_USAGE を確認し、領域を過剰消費しているSQLを特定	SQL側のソート処理（ORDER BY, DISTINCT）を削減するか、一時ファイル（TEMPfile）の自動拡張（AUTOEXTEND ON）を設定または追加する

運用・監視・セキュリティ上の注意点

パフォーマンスチューニングを行う際は、システム全体へ与える影響をあらかじめ想定しておく必要があります。

• インデックス追加のトレース（落とし穴）: インデックスを追加すると検索（SELECT）は高速化しますが、データの追加・更新・削除（INSERT/UPDATE/DELETE）の際にインデックスの組み換えが発生するため、書き込み処理のパフォーマンスがわずかに低下します。また、ディスク容量も追加で消費します。
• 統計情報収集のリスク: オンライン稼働中に大規模な統計情報収集（DBMS_STATS）を行うと、データ・ディクショナリへのロック競合や、実行計画の突然の変化（プラン変動）によって他のクエリが突然遅くなるリスクがあります。夜間やメンテナンスウィンドウでの実施が基本です。
• 元の状態への戻し方:
  • インデックスを削除して元の状態に戻す場合は、以下のコマンドを実行します。SQLDROP INDEX idx_emp_dept;

FAQ

Q1. Standard Edition 2（SE2）環境でAWRレポートを出力しても問題ありませんか？
A1. テクニカルには機能が呼び出せる場合がありますが、AWRの利用には Enterprise Edition の契約に加えて「Oracle Diagnostic Pack」という有償オプションライセンスの購入が必須です。SE2環境での使用はライセンス違反（違反パケットの検出対象）となるため、SE2では必ず無償の Statspack を使用してください。

Q2. 統計情報を収集したのに実行計画が変わりません。なぜですか？
A2. 対象テーブルのデータ量が極端に少ない場合、インデックスを使用するよりも全件走査（TABLE ACCESS FULL）したほうが速いとオプティマイザが判断することがあります。また、初期化パラメータ OPTIMIZER_MODE や、SQL内に記載されたヒント句が優先されている可能性もあります。

Q3. パスワードに「@」マークが含まれるユーザーでSQL*PlusからStatspack等を実行するには？
A3. 接続文字列で「scott/tiger@pwd@orcl」のように記述すると、Oracleがどこまでがパスワードでどこからが接続識別子（ネットサービス名）か判別できず、接続エラーになります。パスワードに特殊文字を含む場合は、以下のように全体をダブルクォーテーション、さらにシングルクォーテーションで囲ってエスケープしてください。
sqlplus ‘scott/”tiger@pwd”@orcl’

Q4. チューニング用に本番環境と同じテーブル構成をテスト環境に再現したいのですが。

A4. 以下の検証用サンプルDDLを使用して、検証用のスキーマに同一構造のオブジェクトを作成し、テストを行ってください。

CREATE TABLE employees (
  employee_id   NUMBER PRIMARY KEY,
  first_name    VARCHAR2(50),
  last_name     VARCHAR2(50),
  department_id NUMBER,
  salary        NUMBER
);

INSERT INTO employees VALUES (1, 'Taro', 'Yamada', 10, 500000);
INSERT INTO employees VALUES (2, 'Hanako', 'Suzuki', 20, 550000);
INSERT INTO employees VALUES (3, 'Jiro', 'Tanaka', 10, 480000);
INSERT INTO employees VALUES (4, 'Akira', 'Sato', 30, 620000);
COMMIT;

まとめ：パフォーマンス改善の5大原則

1. データによる現状把握を徹底する: 勘に頼って設定を変えず、AWR、Statspack、V$ビューから定量的なエビデンスを取得する。
2. SQL（アプリケーション層）から着手する: 最も改善幅が大きく、システム全体への副作用が少ない個別SQLの見直しから始める。
3. 実行計画と統計情報をセットで見る: なぜそのアクセス経路になったのか、オプティマイザの判断材料（統計情報）が最新か常に疑う。
4. 環境に適したツールを選ぶ: SE2ならStatspack、EE（ライセンスあり）ならAWRと、環境の制約を遵守する。
5. 対策後は必ず数値で検証する: 処理時間、ブロック読込数、CPU時間を測定し、改善効果を“見える化”して初めて完了とする。

本記事は Oracle Database 19c を対象に解説します（他バージョンは画面や既定値が異なる場合があります）。

[参考]
パフォーマンス・チューニングの概要

Oracleインデックスの仕組みと作成方法！実行計画の見方まで完全解説

Oracle SQL の検索速度が遅いと感じたとき、最初に検討すべき対策が「Oracle インデックス（索引）」の活用です。適切にインデックスを作成することで、数万〜数億件のデータアクセスを一瞬で完了させることが可能になります。この記事では…

oracle-master00000.com

2024.10.10