SQLチューニングは、データベースパフォーマンスを最大限引き出すための基本かつ最重要なスキルです。本記事では、基礎から応用までを網羅し、実務で即役立つスキルを体系的に解説します。さらに、実行計画の図解や効果測定方法も含めて解説します。
リンク
リンク
リンク
1. SQLチューニングの基礎：パフォーマンスを測る
1.1 パフォーマンス測定の基本指標
SQLのパフォーマンスは以下の指標で測定します。
| 指標 | 説明 | 確認方法 |
|---|---|---|
| 実行時間 | クエリが完了するまでの時間。 | SQL Developerの実行時間やautotrace |
| I/Oオペレーション数 | データ取得のためにディスクへアクセスする回数。 | AUTOTRACEまたはトレースログ |
| CPU使用率 | クエリ処理でCPUを使用した量。 | トレースログ、TKPROF |
| 待機イベント | クエリが待機した理由（ロック、リソース不足など）。 | V$SESSION_WAITビュー |
測定の流れ：
- 問題となるクエリを特定。
SQL_TRACEやAUTOTRACEを使い、具体的なデータを取得。- ボトルネックを特定してチューニングを開始。
2. 実行計画を深く理解する
実行計画は、SQLの実行方法を可視化するための重要なツールです。SQLがどのようにデータにアクセスしているかを理解することで、ボトルネックを効率的に解消できます。
2.1 実行計画の取得
以下のSQLを使用して、実行計画を取得できます。
EXPLAIN PLAN FOR
SELECT * FROM V$SESSION;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
SQL> EXPLAIN PLAN FOR
2 SELECT * FROM V$SESSION;
解析されました。
SQL> SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 644658511
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2117 | 0 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 2117 | 0 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 1908 | 0 (0)| 00:00:01 |
|* 3 | FIXED TABLE FULL | X$KSUSE | 1 | 1747 | 0 (0)| 00:00:01 |
|* 4 | FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) | 1 | 161 | 0 (0)| 00:00:01 |
|* 5 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 209 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND
BITAND("S"."KSUSEFLG",1)<>0)
4 - filter("S"."INDX"="W"."KSLWTSID")
5 - filter("W"."KSLWTEVT"="E"."INDX")
20行が選択されました。
2.2 実行計画の読み方（図解付き）
以下は、よく見られる実行計画の構造とその改善例です。
- TABLE ACCESS FULL: テーブル全体をスキャンするため、データ量が多い場合パフォーマンスが低下します。インデックスの利用を検討しましょう。
- INDEX RANGE SCAN: 範囲指定でインデックスを使用する場合。効率的な検索方法です。
- HASH JOIN: メモリを多く使用する結合です。適切なインデックスを用意することで回避可能な場合があります。
例: 実行計画のイメージ
元のクエリ：
SELECT * FROM EMP WHERE DEPTNO = 10;
元の実行計画（改善の余地あり）:
SELECT STATEMENT
TABLE ACCESS FULL EMP
インデックス作成後:
CREATE INDEX IDX_EMP_DEPTNO ON EMP(DEPTNO);
SELECT * FROM EMP WHERE DEPTNO = 10;
改善後の実行計画:
SELECT STATEMENT
INDEX RANGE SCAN IDX_EMP_DEPTNO
3. インデックスを最大限に活用する
インデックスの存在だけでは不十分です。適切な使い方が重要です。
3.1 インデックスの選択基準
- Bツリーインデックス: 範囲検索（例:
BETWEENや>）や精確な検索。 - ビットマップインデックス: 値の種類が少ない列（例:
性別,状態）。 - ファンクションインデックス: 関数を含む列。
例: 関数インデックスの作成
CREATE INDEX IDX_EMP_LCASE ON EMP(LOWER(ENAME));
SELECT * FROM EMP WHERE LOWER(ENAME) = 'smith';
4. データ量を減らすクエリ設計
SQLは必要なデータだけを効率的に取得するべきです。
4.1 不要な列を削減
悪い例:
SELECT * FROM EMP;
改善例:
SELECT ENAME, JOB FROM EMP;
5. JOINの効率化
JOIN処理はパフォーマンスに大きな影響を与える部分です。
5.1 JOINの種類
- NESTED LOOPS: 小規模データセットに有効。
- HASH JOIN: 大規模データセットに有効だがメモリを多く使用。
- MERGE JOIN: 両テーブルがソート済みの場合に最適。
6. サブクエリを最適化する
6.1 サブクエリの問題点
サブクエリは簡潔ですが、繰り返し評価されることでパフォーマンスが低下します。
改善例: WITH句の使用
WITH DEPT_CTE AS (
SELECT DEPTNO FROM DEPT WHERE LOC = 'NEW YORK'
)
SELECT ENAME FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT_CTE);
7. ヒント句と統計情報を活用する
ヒント句はSQLの実行方法を直接指定するためのツールです。
7.1 ヒント句の使用例
SELECT /*+ INDEX(EMP IDX_EMP_DEPTNO) */ ENAME
FROM EMP
WHERE DEPTNO = 10;
7.2 統計情報の更新
統計情報が古いと、最適な実行計画が生成されません。
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
END;
/
8. トレースとTKPROFを使った詳細分析
8.1 トレースの有効化
ALTER SESSION SET SQL_TRACE = TRUE;
8.2 TKPROFによる解析
取得したトレースログを解析して、どこがボトルネックになっているかを特定します。
9. 実践チェックリスト
最後に、SQLチューニングのポイントを簡単なチェックリストにまとめました。
| チューニング項目 | 実施状況 |
|---|---|
| インデックスが適切に利用されているか？ | ✅/❌ |
| 不要な列を取得していないか？ | ✅/❌ |
| 実行計画を確認したか？ | ✅/❌ |
| 統計情報は最新か？ | ✅/❌ |
| トレースログを解析したか？ | ✅/❌ |
まとめ：継続的改善がSQLチューニングの鍵
SQLチューニングは単発で終わる作業ではありません。データ量の増加やシステム変更に伴い、定期的な見直しが必要です。本記事で紹介したテクニックを駆使し、効率的なSQLを実現してください。
[参考]
Oracle Database SQLチューニング・ガイド 19c
コメント