Oracleでパフォーマンスの悪いSQLクエリを見つけるにはどうしたらよいですか?
Oracle は共有 SQL 領域の統計情報を保持しており、SQL 文字列ごとに 1 行が含まれています(v$sqlarea)。 しかし、その中のどれが悪いパフォーマンスなのか、どのように特定できるのでしょうか?
私は、この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のフルテキストを表示します。
このようなものでディスク集約的なフルテーブルスキャンを見つけることができます。
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 <=5;
インスタンスのアクティビティの期間中、実行ごとに取得する平均バッファを取得できます。
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 <= 20
次のクエリは、多数のディスク読み取りを実行する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チューニングブック(残念ながらもうありません)で見つかりました。申し訳ありませんが、帰属はありません。
検索していると、次のようなクエリがあり、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;
上記のクエリで、現在のユーザーの詳細がリストアップされると思います。
コメントをお待ちしています。