DBAtlas · データベース運用アトラス

低速クエリ: 詳細な診断

クエリまたはシステム全体が遅い。段階的に絞り込む: 各ノードでチェックを実行し、結果に応じて分岐を選択する。最後に根本原因と対策にたどり着く。

何が遅いか?

特定の 1 つまたは少数のクエリが遅いのか、それともシステム全体が遅いのか? これにより、調査は「クエリ中心」または「リソース / ボトルネック中心」の 2 つの経路のいずれかに分かれる。

  • 特定の 1 つまたは少数のクエリが遅い
  • システム全体が遅い

クエリは現在実行中か?

現在実行中 / 停滞中のクエリか、それとも「全般的に遅い」クエリか? アクティブセッションを確認する。

SELECT sid, serial#, sql_id, last_call_et AS running_sec, event, blocking_session
FROM v$session WHERE status='ACTIVE' AND type='USER' ORDER BY last_call_et DESC;
  • 現在実行中 / 停滞中
  • 全般的に遅い。実行計画を確認したい

クエリは何かを待っているか?

アクティブなのに進んでいない場合、待機している可能性が高い。blocking_session / wait_event に値があるか?

SELECT sid, blocking_session, event, seconds_in_wait
FROM v$session WHERE sid=&target_sid;
  • 別のセッションにブロックされている
  • リソースを待機している (IO / ログ / ラッチ)
  • 待機なし。CPU を消費し続けている

どの待機が支配的か?

支配的な wait event がボトルネックの種類を示す。

SELECT event, total_waits, time_waited FROM v$session_event WHERE sid=&target_sid ORDER BY time_waited DESC FETCH FIRST 5 ROWS ONLY;
  • IO 待機 (db file read / PAGEIOLATCH)
  • ログ / コミット待機 (log file sync / WRITELOG)
  • ロック / エンキュー (enq: TX / LCK)

実行計画を取得する (EXPLAIN)

実行計画を取得する。フルテーブルスキャン、予期しない結合順序、推定行数と実績行数の差はあるか?

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));
  • フルスキャン / インデックス不足
  • 昨日は速かった。実行計画が突然変わった
  • 処理する行が多すぎる / N+1
  • 実行計画は良好だが遅い

統計は最新か?

推定行数と実績行数 (E-rows と A-rows) の大きな差は、統計が古いことの兆候である。

SELECT table_name, num_rows, last_analyzed FROM dba_tables WHERE table_name='&TABLE_NAME';
  • 統計が古い
  • 統計は最新だがそれでも遅い

システム全体: ボトルネックはどこか?

単一のクエリではなくすべてが遅い場合は、リソースのボトルネックを探す: CPU、IO、メモリ、それとも広範なブロッキングか?

SELECT metric_name, value FROM v$sysmetric WHERE metric_name IN ('Host CPU Utilization (%)','Average Active Sessions') AND intsize_csec=(SELECT MAX(intsize_csec) FROM v$sysmetric);
  • CPU が 100% 近い
  • IO 待機が高い
  • メモリ負荷 / キャッシュヒット率が低い
  • ブロッキングが多い

根本原因: ブロッキング

クエリは別のセッションが保持しているロックを待っている。チェーンの先頭にあるセッション (ルートブロッカー) を特定して解消する。

根本原因: IO のボトルネック

クエリがディスクから大量に読み取っている。通常はフルスキャン (インデックス不足) またはメモリ不足 (バッファキャッシュ) が原因。

根本原因: コミット / ログのボトルネック

'log file sync' / 'WRITELOG' が支配的な場合、コミットは redo/WAL の書き込みを待っている。

根本原因: インデックス不足

実行計画がフルスキャンを行っている。フィルタ / 結合列に適したインデックスを作成すると大幅に高速化する。

根本原因: 実行計画の性能低下

クエリが昨日は速かったが今日は遅い。実行計画が変わった。通常は古い統計またはパラメータの変更が原因。

根本原因: クエリの設計

実行計画は妥当だが、クエリ自体が本質的に多すぎる処理を行っている (行が多すぎる、N+1、不要な結合)。

根本原因: 古い統計

統計が最新でないため、オプティマイザが誤った推定をしている。

詳細な分析

実行計画と統計に問題がないのにそれでも遅い場合は、さらに掘り下げる。

根本原因: CPU のボトルネック

システム全体が CPU バウンドになっている。通常は、実行計画の悪い少数のクエリが CPU を消費している。

根本原因: メモリ負荷

キャッシュヒット率が低い、またはスワップが発生している: メモリが不足しているか、割り当てが誤っている。

DBAtlas で開く →