はじめに
この処理のどれかのSQLに時間が掛かっているようだがどのSQLか分からない、、というときは以下の方法で調査します。
Oracleで実行したSQLの中で、ある一定以上のコストが掛かっているSQLを確認する方法を記載します。
コストの高い(ハイコスト)SQLを取得するSQL
sysユーザーにて以下SQLを実行します。
以下SQLの★部分へコストの閾値を設定します。
SET LINESIZE 32767
SET PAGESIZE 0
SELECT SQL_ID
||','||CASE WHEN COST > 1073741824+1073741824 THEN TO_CHAR(ROUND( COST/1073741824)) ||'G' WHEN COST > 1048576+1048576 THEN TO_CHAR(ROUND( COST/1048576)) ||'M' WHEN COST > 1024+1024 THEN TO_CHAR(ROUND( COST/1024)) ||'K' ELSE TO_CHAR( COST) END
||','||(select max(MODULE) from V$SQL vs where vsp.SQL_ID=vs.SQL_ID)
||','||TO_CHAR(TIMESTAMP,'yyyy/mm/dd hh24:mi:ss')
||','||(select max(SQL_TEXT) from V$SQL vs where vsp.SQL_ID=vs.SQL_ID)
FROM V$SQL_PLAN vsp
WHERE ID = 0
and COST >1048576 --★ G越:1073741824、M越:1048576、K超:1024
ORDER BY COST desc ;
「SET LINESIZE。。。」の部分は、Max値に設定しています(お好みでカスタマイズしてください)。
コストの高い(ハイコスト)SQLの取得結果イメージ

SQL_IDを先頭にコストの高い(ハイコスト)SQLが出力されました。
ここで取得したSQL_IDから「過去の実行計画を確認する方法」で実行計画を取得し、性能改善等の対応をすることが多いです。
取得項目
取得項目の簡単な説明です(取得内容はV$SQL_PLANのOracleのサイトを見た方が良いですね)。
- SQL_ID:SQL識別子
- COST:オプティマイザのコストベース方法で見積もった操作コスト
- MODULE:モジュールの名前
- TIMESTAMP:実行計画が生成された日時
- SQL_TEXT:SQLテキストの最初の1000文字
まとめ
コストの高いSQLクエリがデータベースシステムにもたらす影響は、複数の面から深刻な問題を引き起こす可能性があります。
以下は、コストの高いSQLがもたらす主な影響についての一般的な要点です。
- パフォーマンス低下:
- コストの高いSQLは、データベースシステムのパフォーマンスに直接的な悪影響を及ぼします。これらのクエリがデータベースエンジンに対して大きな負荷をかけ、クエリ実行の遅延やデータベースの応答時間の増加を引き起こす可能性があります。
- データベーススローンダウン:
- コストの高いSQLが増加すると、データベースのスループットが低下し、データベース全体のスローンダウンが発生する可能性があります。これは、他のユーザーまたはアプリケーションにも影響を及ぼします。
- リソースの過度な使用:
- コストの高いSQLは、CPU、メモリ、ディスクI/Oなどのリソースを過度に使用する傾向があります。これにより、他のクエリやアプリケーションが必要なリソースを得られなくなる可能性があります。
- スケーラビリティの問題:
- コストの高いSQLは、スケーラビリティにも問題を引き起こす可能性があります。増加するワークロードに対応できない場合、追加のハードウェアやリソースの導入が必要になるかもしれません。
- セキュリティの脆弱性:
- コストの高いSQLがリソースを過度に使用する場合、データベースエンジンを過負荷にさせる可能性があり、サービス拒否攻撃(DoS攻撃)の手段として悪用される可能性があります。
- ユーザーエクスペリエンスの低下:
- データベースの応答時間が増加すると、ユーザーエクスペリエンスが低下し、ウェブアプリケーションやシステムの利用者が不満を抱く可能性があります。
- コストの増加:
- コストの高いSQLは、インフラストラクチャの追加投資やパフォーマンスチューニングのコストを引き起こす可能性があります。さらに、スローンダウンや障害により、ビジネスへの損害が発生する可能性もあります。
コストの高いSQLを識別し、最適化することは、データベース管理とアプリケーションのパフォーマンス維持において非常に重要です。これにはクエリのチューニング、インデックスの適切な使用、キャッシュの最適化、ハードウェアのスケーリングなどが含まれます。適切な対策を取ることで、コストの高いSQLがもたらす影響を最小限に抑え、データベースシステムの信頼性とパフォーマンスを向上させることができます。
コメント