오라클 SQL TRACE 추출 스크립트

오라클 SQL Trace 추출 스크립트

---

alter session set tracefile_identifier='test';  -- User Trace를 찾기 쉽게 네이밍 alter session set timed_statistics = true; -- alter session set statistics_level = all; alter session set max_dump_file_size = unlimited; alter session set events '10046 trace name context forever, level 12'; -- alter session set "_rowsource_execution_statistics"=true; set lines 600; -- SQL TRACE TARGET SQL Select count(*) from scott.test; alter session set events '10046 trace name context off'; SELECT p.osuser, p.process cospid, p.spid sospid, p.username dbuser, p.sid, p.serial#,   'tkprof '||d.value||'/'||lower(rtrim(i.instance,chr(0)))||'_ora_'||p.spid||'_great.trc'     ||' ./'||lower(rtrim(i.instance,chr(0)))||'_ora_'||p.spid||'.prf sys=no' tkprof_cmd,   'cat ./'||lower(rtrim(i.instance,chr(0)))||'_ora_'||p.spid||'.prf' view_cmd FROM   (SELECT s.osuser, s.process, p.spid, s.username, s.sid, s.serial#   FROM v$session s, v$process p   WHERE s.paddr = p.addr and s.sid = (select distinct sid from v$mystat)   ) p,   (SELECT instance   FROM v$thread   WHERE thread# = (SELECT to_number(value)+1 FROM v$parameter WHERE name = 'thread')   ) i,   (SELECT value FROM v$parameter WHERE name = 'user_dump_dest'   ) d;