【Oracle】コストの高いSQL(ハイコストSQL)を確認する方法

Oracle
2023.05.19

はじめに

この処理のどれかの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の取得結果イメージ

私が個人で運営している販売管理DBの取得結果(一部モザイク無しにしていますのでご参考までにご参照ください)

SQL_IDを先頭にコストの高い(ハイコスト)SQLが出力されました。

ここで取得したSQL_IDから「過去の実行計画を確認する方法」で実行計画を取得し、性能改善等の対応をすることが多いです。

取得項目

取得項目の簡単な説明です(取得内容はV$SQL_PLANのOracleのサイトを見た方が良いですね)。

  • SQL_ID：SQL識別子
  • COST：オプティマイザのコストベース方法で見積もった操作コスト
  • MODULE：モジュールの名前
  • TIMESTAMP：実行計画が生成された日時
  • SQL_TEXT：SQLテキストの最初の1000文字

まとめ

コストの高いSQLクエリがデータベースシステムにもたらす影響は、複数の面から深刻な問題を引き起こす可能性があります。
以下は、コストの高いSQLがもたらす主な影響についての一般的な要点です。

  1. パフォーマンス低下:
    • コストの高いSQLは、データベースシステムのパフォーマンスに直接的な悪影響を及ぼします。これらのクエリがデータベースエンジンに対して大きな負荷をかけ、クエリ実行の遅延やデータベースの応答時間の増加を引き起こす可能性があります。
  2. データベーススローンダウン:
    • コストの高いSQLが増加すると、データベースのスループットが低下し、データベース全体のスローンダウンが発生する可能性があります。これは、他のユーザーまたはアプリケーションにも影響を及ぼします。
  3. リソースの過度な使用:
    • コストの高いSQLは、CPU、メモリ、ディスクI/Oなどのリソースを過度に使用する傾向があります。これにより、他のクエリやアプリケーションが必要なリソースを得られなくなる可能性があります。
  4. スケーラビリティの問題:
    • コストの高いSQLは、スケーラビリティにも問題を引き起こす可能性があります。増加するワークロードに対応できない場合、追加のハードウェアやリソースの導入が必要になるかもしれません。
  5. セキュリティの脆弱性:
    • コストの高いSQLがリソースを過度に使用する場合、データベースエンジンを過負荷にさせる可能性があり、サービス拒否攻撃（DoS攻撃）の手段として悪用される可能性があります。
  6. ユーザーエクスペリエンスの低下:
    • データベースの応答時間が増加すると、ユーザーエクスペリエンスが低下し、ウェブアプリケーションやシステムの利用者が不満を抱く可能性があります。
  7. コストの増加:
    • コストの高いSQLは、インフラストラクチャの追加投資やパフォーマンスチューニングのコストを引き起こす可能性があります。さらに、スローンダウンや障害により、ビジネスへの損害が発生する可能性もあります。

コストの高いSQLを識別し、最適化することは、データベース管理とアプリケーションのパフォーマンス維持において非常に重要です。これにはクエリのチューニング、インデックスの適切な使用、キャッシュの最適化、ハードウェアのスケーリングなどが含まれます。適切な対策を取ることで、コストの高いSQLがもたらす影響を最小限に抑え、データベースシステムの信頼性とパフォーマンスを向上させることができます。

コメント

タイトルとURLをコピーしました