はじめに
Oracleで運用しているとどのSQLがCPUを喰っているか知りたいことが良くあります。
そこでツールを使わずSQLだけで直近のSQLの実行状況を調べる時の手順を書いてみます。
概要
実行状況はv$SQLを参照しています。
ある時点でのv$SQLのスナップショットを別テーブルに記録し、そこからの差分を表示します。
※ここで言うスナップショットはオラクルの機能名では無く、その瞬間の記録と言う意味で
使っています。実態は単なるテーブルです。
実行手順
- 管理者権限(SYS,SYSTEM）でログインします。
- スナップショットを作成します。
-- 既存スナップショットを削除（1回目は存在しないのでエラーとなる）
drop table execbase;
-- スナップショット作成
create table execbase as
select cpu_time,elapsed_time,executions,sql_id,child_number,sysdate dt from v$sql;
- 差分取得
--スナップショット取得時からの実行状況の差分表示
--CPU使用時間の多い順に表示
select
trunc((v.cpu_time-nvl(b.cpu_time,0))/1000) "cpu_dif(ms)",
trunc((v.elapsed_time-nvl(b.elapsed_time,0))/1000) "elapsed_dif(ms)",
v.executions-nvl(b.executions,0) exec_dif,
trunc(v.cpu_time/v.executions/1000) "cpu_per(ms)",
trunc((sysdate-b.dt)*86400) "keika(s)",
v.module,
u.username,
v.sql_fulltext
from execbase b,v$sql v,dba_users u
where v.sql_id=b.sql_id(+)
and v.child_number=b.child_number(+)
and v.executions > 0
and v.cpu_time > nvl(b.cpu_time,0)
and v.parsing_user_id=u.user_id
order by 1 desc;
出力項目の見方
| 項目名 | 内容 | 単位 |
|---|---|---|
| cpu_dif(ms) | CPUの消費量合計 | ミリ秒 |
| elapsed_dif(ms) | 経過時間合計 | ミリ秒 |
| exec_dif | 実行回数 | 回 |
| cpu_per(ms) | 1回当たりのCPU消費量 | ミリ秒 |
| keika | スナップショットを取得してからの経過時間 | 秒 |
| module | SQLを実行したプログラム名 | - |
| user_name | SQLを実行したID | - |
| sql_fulltext | SQL文 | - |
アレンジ
- order by 句を変えると1回当たりのCPU消費量準、実行回数順など変更できます。
- and v.executions > 0 の値を変えるとxx回以上実行したSQLを抽出できます。
- その他目的に合わせて色々アレンジしてください。