全セッションのSQLトレースを取得するスクリプト

全セッション*1のSQLトレースを取得するスクリプトを作ってみた。

[手順]

SQLトレース取得を開始する。

SQL> conn / as sysdba
SQL> @sqltrace_start.sql

SQLトレース取得を停止する。

SQL>  @sqltrace_stop.sql

SQLトレースの出力先を確認する。

SQL> select value from v$parameter where name='user_dump_dest';
VALUE
--------------------------------------------------------------------------------
/export/home/oracle/app/oracle/admin/orcl/udump

tkprof を使ってトレースファイルを整形する。

$ cd /export/home/oracle/app/oracle/admin/orcl/udump
$ ls -ltr
...
-rw-r-----   1 oracle   oinstall    3345 Jul 31 12:47 orcl_ora_1260.trc
-rw-r-----   1 oracle   oinstall   39547 Jul 31 12:47 orcl_ora_1270.trc
$ tkprof orcl_ora_1270.trc 1270.prf explain=system/manager aggregate=no sys=no sort=exeela

整形したトレースファイルを見る。

$ less 1270.prf
TKPROF: Release 9.2.0.7.0 - Production on Fri Jul 31 13:12:26 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Trace file: orcl_ora_1270.trc
Sort options: exeela
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
--------------------------------------------------------------------------------

*** SESSION ID:(44.9341) 2009-07-31 12:47:58.207

********************************************************************************

select parent_owner,parent_name,parent_link_name,parent_type,parent_timestamp,
  property
from
 ora_kglr7_dependencies where owner=:1 and name=:2 and type=:3 and obj#=:4
  order by order_number
...

[ソースコード]

sqltrace_start.sql

set pagesize 10000
set linesize 10000
set trimout on
set trimspool on

set serveroutput on size 1000000

declare
	cursor cu is select sid, serial# from v$session where username is not null;
begin
	for rec in cu loop
		begin
			dbms_system.set_sql_trace_in_session(rec.sid,rec.serial#,true);
			dbms_system.set_ev(rec.sid,rec.serial#,10046,12,'');
		exception
			when others then
			dbms_output.put_line('sid:'||rec.sid||' serial#:'||rec.serial#||' sqlcode:'|| sqlcode||' sqlerrm:'||sqlerrm);
		end;
	end loop;
end;
/
exit

sqltrace_stop.sql

set pagesize 10000
set linesize 10000
set trimout on
set trimspool on

set serveroutput on size 1000000

declare
	cursor cu is select sid, serial# from v$session where username is not null;
begin
	for rec in cu loop
		begin
			dbms_system.set_sql_trace_in_session(rec.sid,rec.serial#,false);
			dbms_system.set_ev(rec.sid,rec.serial#,10046,0,'');
		exception
			when others then
			dbms_output.put_line('sid:'||rec.sid||' serial#:'||rec.serial#||' sqlcode:'|| sqlcode||' sqlerrm:'||sqlerrm);
		end;
	end loop;
end;
/
exit

[参考]
SQLチューニングの基盤となる統計情報 (1/4)：Oracle SQLチューニング講座（5） - ＠IT

*1:v$session.username が NULL 以外の全セッション

ablog

不器用で落着きのない技術者のメモ

全セッションのSQLトレースを取得するスクリプト