Oracleで時間のかかるSQLクエリトップ5

Oracleでパフォーマンスの悪いSQLクエリを見つけるにはどうしたらよいですか？

Oracle は共有 SQL 領域の統計情報を保持しており、SQL 文字列ごとに 1 行が含まれています(v$sqlarea)。 しかし、その中のどれが悪いパフォーマンスなのか、どのように特定できるのでしょうか？

JosephStyons
編集された質問 5日 12月 2008 в 5:44
プログラミング
oracle performance
25日 11月 2008 в 9:27
15 ビュー

私は、このSQLステートメントが役に立つと思いました（申し訳ありませんが、原著者に帰属させることはできません。）

SELECT * FROM
(SELECT
    sql_fulltext,
    sql_id,
    elapsed_time,
    child_number,
    disk_reads,
    executions,
    first_load_time,
    last_load_time
FROM    v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10
/

これは、現在SQLキャッシュに格納されているSQL文のうち、経過時間順に上位のものを見つけるものです。 ステートメントは時間とともにキャッシュから消えていくので、昼間に出社したときに昨晩のバッチジョブを診断しようとしても無駄かもしれません。

また、disk_readsやexecutionsで並べることもできます。 貧弱なアプリケーションでは、同じ SQL 文を何度も送信してしまうことがあるので、実行回数は有用です。 このSQLは、バインド変数を正しく使っていることを前提にしています。

そして、ある文の sql_id と child_number を取り出して、この baby:- に送り込みます。

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &child));

これは、SQLキャッシュからの実際のプランと、SQLのフルテキストを表示します。

WW.
編集した答え 6日 11月 2014 в 12:33
解説 (1)

このようなものでディスク集約的なフルテーブルスキャンを見つけることができます。


SELECT Disk_Reads DiskReads, Executions, SQL_ID, SQL_Text SQLText, 
   SQL_FullText SQLFullText 
FROM
(
   SELECT Disk_Reads, Executions, SQL_ID, LTRIM(SQL_Text) SQL_Text, 
      SQL_FullText, Operation, Options, 
      Row_Number() OVER 
         (Partition By sql_text ORDER BY Disk_Reads * Executions DESC) 
         KeepHighSQL
   FROM
   (
       SELECT Avg(Disk_Reads) OVER (Partition By sql_text) Disk_Reads, 
          Max(Executions) OVER (Partition By sql_text) Executions, 
          t.SQL_ID, sql_text, sql_fulltext, p.operation,p.options
       FROM v$sql t, v$sql_plan p
       WHERE t.hash_value=p.hash_value AND p.operation='TABLE ACCESS' 
       AND p.options='FULL' AND p.object_owner NOT IN ('SYS','SYSTEM')
       AND t.Executions > 1
   ) 
   ORDER BY DISK_READS * EXECUTIONS DESC
)
WHERE KeepHighSQL = 1
AND rownum
Leigh Riffel
編集した答え 8日 12月 2008 в 2:49
解説 (1)

インスタンスのアクティビティの期間中、実行ごとに取得する平均バッファを取得できます。


SELECT username,
       buffer_gets,
       disk_reads,
       executions,
       buffer_get_per_exec,
       parse_calls,
       sorts,
       rows_processed,
       hit_ratio,
       module,
       sql_text
       -- elapsed_time, cpu_time, user_io_wait_time, ,
  FROM (SELECT sql_text,
               b.username,
               a.disk_reads,
               a.buffer_gets,
               trunc(a.buffer_gets / a.executions) buffer_get_per_exec,
               a.parse_calls,
               a.sorts,
               a.executions,
               a.rows_processed,
               100 - ROUND (100 * a.disk_reads / a.buffer_gets, 2) hit_ratio,
               module
               -- cpu_time, elapsed_time, user_io_wait_time
          FROM v$sqlarea a, dba_users b
         WHERE a.parsing_user_id = b.user_id
           AND b.username NOT IN ('SYS', 'SYSTEM', 'RMAN','SYSMAN')
           AND a.buffer_gets > 10000
         ORDER BY buffer_get_per_exec DESC)
 WHERE ROWNUM
sjngm
編集した答え 23日 12月 2011 в 5:55
解説 (0)

askTom-Oracleから入手した完全な情報。 それがあなたを助けることを願っています。

select * 
from v$sql 
where buffer_gets > 1000000 
or disk_reads > 100000 
or executions > 50000
解説 (0)

それはあなたが持っている神託のバージョンに依存します、9i以下Statspackはあなたが求めているものです、10g以上、あなたはawrを望みます、これらのツールは両方ともあなたに最高のsqlとたくさんを与えます他のもの。

解説 (0)

次のクエリは、多数のディスク読み取りを実行するSQLステートメントを返します(問題のあるユーザーとクエリが実行された回数も含まれます)。

SELECT t2.username, t1.disk_reads, t1.executions,
    t1.disk_reads / DECODE(t1.executions, 0, 1, t1.executions) as exec_ratio,
    t1.command_type, t1.sql_text
  FROM v$sqlarea t1, dba_users t2
  WHERE t1.parsing_user_id = t2.user_id
    AND t1.disk_reads > 100000
  ORDER BY t1.disk_reads DESC

クエリをSYSとして実行し、過剰と見なされるものに応じてディスク読み取りの数を調整します(100,000は私にとっては機能します)。

最近、このクエリを使用して、ステートメントを実行する前に「プランの説明」を利用することを拒否するユーザーを追跡しました。

このクエリは古いOracle SQLチューニングブック(残念ながらもうありません)で見つかりました。申し訳ありませんが、帰属はありません。

Filippos Karapetis
編集した答え 23日 1月 2017 в 7:54
解説 (0)

この方法はいくつかありますが、tkprofでググってみてください。

GUIはありません...完全にコマンドラインなので、Oracle初心者にはちょっと怖いかもしれません。

このリンクは良いスタートとなりそうです。

http://www.oracleutilities.com/OSUtil/tkprof.html

解説 (2)

検索していると、次のようなクエリがあり、1つの仮定で仕事をこなしています（クエリ実行時間 >6秒）。

SELECT ユーザー名、sql_text、sofar、totalwork、units

FROM v$sql,v$session_longops

WHERE sql_address = address AND sql_hash_value = hash_value

ORDER BY address, hash_value, child_number;

上記のクエリで、現在のユーザーの詳細がリストアップされると思います。

コメントをお待ちしています。

解説 (1)