本記事は、既存記事「Oracle SQLチューニングの基本」の内容を、より実務寄り・再現可能な形に再構成したリライト版です。元記事の主旨（実務で使えるSQLチューニングの要点）を保ちつつ、最短手順・再現用スクリプト・トラブル対処を強化しました。
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
導入（要約）
Oracle での SQLチューニング は、実行計画の理解・統計情報の適正化・トレース分析の３本柱で進めます。まず現状を測り（実行時間/論理I/O/待機）、実行計画を読んで改善ポイントを特定、最後にトレースで裏取りするのが王道です。
結論・最短手順（スニペット狙い）
- 対象SQLを特定：トップN高負荷SQL（AWR/SQL Monitor 等）から選ぶ。
- 実行計画を取得：
DBMS_XPLANで現行計画を確認（アクセス経路・結合方式・選択度）。 - 統計情報を整える：
DBMS_STATSでスキーマ/表/索引の統計を最新化。 - 設計改善を適用：必要列だけ取得、適切な索引、結合順/方式、不要な関数を排除。
- ヒント句は最後：恒久設計で解けない場合のみ最小限に使用。
- トレースで裏取り：
DBMS_MONITOR＋TKPROFでCPU/論理I/O/待機を定量確認。
背景と基礎
SQLチューニングとは？
反復的にSQLの性能を目標水準へ改善するプロセス。アプリ設計（事前最適化）と、既存SQLの改善（事後チューニング）は別物です。
測定指標（“一口メモ”付き）
| 指標 | ねらい | 代表的な取得方法 |
|---|---|---|
| 実行時間 | 体感性能の代表値 | SQL*Plus/SQL Developerの所要時間 |
| 論理I/O（buffer gets） | メモリアクセスの多さ | TKPROFの「query/current」 |
| 物理I/O（disk reads） | ストレージ負荷 | TKPROF、AWR |
| CPU時間 | CPU競合の度合い | TKPROF、AWR |
| 待機イベント | 真因（I/O/ロック/ネット等） | AWR/ASH、V$SESSION 系ビュー |
手順・実装（前提を明示）
前提（19c／Linux・非CDB）
- 接続ユーザー：
SYS（一部管理操作）、検証用：TUNEUSER（作成手順あり） - サンプル表：
EMP/DEPT相当を自前で作成 - 文字コード/時刻NLSは環境既定のまま
- 表領域クォータ（A案）：
USERS表領域にクォータを付与（下記スクリプトに含む） - 遅延セグメント作成が既定のため、
CREATE TABLE後の初回 INSERT 時にクォータ不足だと ORA-01950 が出る点に注意 - 影響が出る操作（統計更新/索引作成/ヒント付与）は検証環境で実施し、戻し方を用意
1) 再現用ユーザーの作成（A案：USERS表領域にクォータ付与）
検証専用ユーザー
TUNEUSERを作り、USERS表領域に書込み枠（クォータ）を与えます。
-- SYS で
CREATE USER TUNEUSER IDENTIFIED BY "StrongPwd1"
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE TO TUNEUSER;
-- A案：USERS表領域にクォータ（例：100MB）
ALTER USER TUNEUSER QUOTA 100M ON USERS;
-- （確認：任意）
SELECT username, default_tablespace FROM dba_users WHERE username = 'TUNEUSER';
SELECT tablespace_name, bytes, max_bytes
FROM dba_ts_quotas
WHERE username = 'TUNEUSER';
SQLの意図：ユーザーの既定表領域を USERS にし、INSERT 時のセグメント割り当てが行えるように100MBのクォータを設定します。
2) サンプル表とデータの作成
実行計画や索引効果の差分検証ができる最小データを投入します。
-- ここから TUNEUSER で
-- 表作成
CREATE TABLE EMP (
EMPNO NUMBER PRIMARY KEY,
ENAME VARCHAR2(50),
JOB VARCHAR2(30),
MGR NUMBER,
HIREDATE DATE,
SAL NUMBER,
COMM NUMBER,
DEPTNO NUMBER
);
CREATE TABLE DEPT (
DEPTNO NUMBER PRIMARY KEY,
DNAME VARCHAR2(50),
LOC VARCHAR2(50)
);
-- 初期データ投入
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,DATE '1980-12-17',800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,DATE '1981-02-20',1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,DATE '1981-02-22',1250,500,30);
COMMIT;
SQLの意図：最小データセットで全表走査→索引利用の差を再現しやすくします。
3) 実行計画の取得（EXPLAIN PLAN と実行時計画）
“紙の設計図（EXPLAIN PLAN）”と“実走行の記録（DISPLAY_CURSOR）”の両輪で把握。
-- 解析時の計画（オプティマイザ見積もり）
EXPLAIN PLAN FOR
SELECT * FROM EMP WHERE DEPTNO = 30;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 実行後の実計画（A-Rows等を含む）※カーソルSQL_IDがわかる場合
SELECT * FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'BASIC +ROWS +BYTES +COST +IOSTATS +PEEKED_BINDS')
);
補足：DISPLAY_CURSOR は実行済みSQLの実計画（実行行数/IO統計）を表示でき、改善効果の裏付けに有用です。
4) 索引の適用と検証
選択度の高い述語に索引を作成し、計画差分とI/O差分を確認します。
-- 改善案：DEPTNOにB*Tree索引
CREATE INDEX IDX_EMP_DEPTNO ON EMP(DEPTNO);
-- 再実行→実計画比較
SELECT ENAME FROM EMP WHERE DEPTNO = 30;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'BASIC +ROWS +IOSTATS'));
SQLの意図：TABLE ACCESS FULL → INDEX RANGE SCAN への変化を促し、論理I/Oを削減します。
5) 統計情報の更新（最新化）
統計が古いと“不適切な計画”が選ばれます。まずは正しく測る土台づくりから。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TUNEUSER', tabname=>'EMP', cascade=>TRUE);
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TUNEUSER', tabname=>'DEPT', cascade=>TRUE);
END;
/
補足：CASCADE=>TRUE で関連索引の統計も更新。大量ロード直後などは手動で対象表だけ追加実行が有効です。
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
6) 結合方式・サブクエリの見直し
小表×大表：
NESTED LOOPS、大表×大表：HASH JOINが基本形。WITHで共通サブ結果を使い回し、再評価回数を抑えます。
-- 結合の基本：選択度の高い側をドライビングに
SELECT /*+ LEADING(d) USE_NL(e) */
e.ename, d.dname
FROM dept d
JOIN emp e ON e.deptno = d.deptno
WHERE d.loc = 'CHICAGO';
-- サブクエリ最適化：WITHで一時結果を共有
WITH dept_chi AS (
SELECT /*+ MATERIALIZE */ deptno FROM dept WHERE loc = 'CHICAGO'
)
SELECT ename FROM emp WHERE deptno IN (SELECT deptno FROM dept_chi);
SQLの意図：結合順と方式をヒントで明示（原則は設計で解く）。WITHで重複評価を避け、I/OとCPUを抑えます。
7) トレースとTKPROFで裏取り
ALTER SESSION SET SQL_TRACE=TRUEは古典的ですが、19cではDBMS_MONITORの利用が推奨です。
-- 推奨：セッショントレースの有効化（必要権限を前提）
BEGIN
DBMS_MONITOR.SESSION_TRACE_ENABLE(waits=>TRUE, binds=>TRUE);
END;
/
-- 実行 → TKPROFで整形(サーバ側で)
-- $ tkprof trace_*.trc out.prf aggregate=yes sys=no sort=exeela,fchela
読み方の要点（TKPROF）
CPU/elapsed：CPU集約か待機主因かを判定query/current：論理I/Oの多寡disk：物理I/O（ストレージ負荷）rows：見積もりと実績のギャップ（統計やカーディナリティ誤差のヒント）
実行例（ミニ・ケーススタディ）
Before：不要列＋全表走査
SELECT * FROM EMP WHERE DEPTNO = 30;
-- 計画：TABLE ACCESS FULL EMP
After：必要列に絞り、索引を活かす
SELECT ENAME, JOB FROM EMP WHERE DEPTNO = 30;
-- 計画：INDEX RANGE SCAN IDX_EMP_DEPTNO → TABLE ACCESS BY INDEX ROWID EMP
効果の見どころ：論理I/O（buffer gets）が大幅減、CPU/elapsedも改善。計画とTKPROFの両面で確認。
テキスト図：チューニング思考の流れ
[症状] 遅いSQL
│
├─(1) 計測する：時間/IO/待機 を取得
│ └─ AWR/ASH, TKPROF, DISPLAY_CURSOR
│
├─(2) 計画を読む：アクセス/結合/選択度
│ └─ TABLE FULL? → 索引/述語見直し
│ └─ HASH JOIN? → メモリ/順序/索引
│
├─(3) 設計で解く：列削減・索引設計・SQL書き換え
│
├─(4) 統計を整える：DBMS_STATS
│
└─(5) 裏取り：DBMS_MONITOR + TKPROF で定量評価
トラブルシューティング（代表的ORA）
| エラー | 主因の例 | 確認ポイント | 参照系での対処順 |
|---|---|---|---|
| ORA-00942 表/ビューが存在しない | スキーマ/権限誤り | ALL_TABLES, 実行ユーザー | 所有者指定 TUNEUSER.EMP で参照 |
| ORA-01555 スナップショットが古い | 長時間実行＋UNDO不足 | 実行時間、UNDO設定 | 範囲分割、コミット粒度調整、UNDO拡張 |
| ORA-00054 リソースビジー | 排他ロック競合 | V$LOCK, V$SESSION | 実行時間帯変更、再実行、キュー制御 |
運用・監視・セキュリティ上の注意
- 自動統計に任せる：基本は自動、例外のみ手動上書き。
- ヒント濫用NG：計画固定化は将来の劣化を招く。まずは設計・統計で解決。
- 本番トレースは範囲最小・期間限定・権限最小で。トレース出力の保護（個人情報/バインド値）も徹底。
- 戻し方：作成索引は
DROP INDEX、統計はDBMS_STATS.RESTORE_*（統計履歴有効時）。
よくある質問（FAQ）
Q1. EXPLAIN PLAN と DISPLAY_CURSOR はどちらを信用すべき？
A. 実態は DISPLAY_CURSOR（実行後の実計画）を優先。EXPLAIN PLAN は見積もりの参考です。
Q2. 統計情報の更新頻度は？
A. 原則は自動統計タスクに任せる。大量ロード等のイベント後に対象表/索引のみ追加実行。
Q3. まずヒントで速くするのはダメ？
A. 一時しのぎは可。ただし根因（スキーマ設計・索引・書き方・統計）を解かないと将来劣化します。
Q4. トレースは SQL_TRACE と DBMS_MONITOR どちら？
A. 19cでは DBMS_MONITOR を推奨。バインド/待機の収集制御や対象限定がしやすい。
まとめ（要点）
- 測る→読む→直す→裏取るの順で進める（反復）。
- 実行計画は
DISPLAY_CURSORで実態確認、統計は常に整える。 - 改善は 設計とSQLの書き方が主役、ヒントは最後。
- DBMS_MONITOR＋TKPROF で定量評価し、再発防止へ。
本記事は Oracle Database 19c を対象に解説します（他バージョンは画面や既定値が異なる場合があります）。
[参考]
Oracle Database SQLチューニング・ガイド 19c
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
コメント