Help us understand the problem. What is going on with this article?

Oracle 直近で実行したSQLの処理時間を調べる

はじめに

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を抽出できます。
  • その他目的に合わせて色々アレンジしてください。
kenken2go
アラ還おやじです。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした