ORACLE

sql trace 덤프보기

더킹123 2021. 5. 14. 16:18
728x90

sql trace 덤프보기

 

SQL> alter session set tracefile_identifier='qq';

SQL> alter session set sql_trace=true;

SQL>select *
  2     from i1, map, i2
  3     where i1.n = map.i1
  4     and i2.n = map.i2
  5     and i1.v = 'x'
  6     and i2.v = 'y';

SQL> alter session set sql_trace=false;

[oracle@ora10gr2 ~]$ cd $ORACLE_BASE/admin/orcl/udump

[oracle@ora10gr2 udump]$ls

a.txt               orcl_ora_11150.trc  orcl_ora_17008_qq.trc
orcl_ora_10960.trc  orcl_ora_11175.trc  orcl_ora_28587.trc
orcl_ora_11030.trc  orcl_ora_11231.trc  orcl_ora_28614.trc
orcl_ora_11031.trc  orcl_ora_11247.trc  orcl_ora_28628.trc
orcl_ora_11056.trc  orcl_ora_11274.trc  orcl_ora_30449.trc
orcl_ora_11095.trc  orcl_ora_12680.trc  orcl_ora_30452.trc
orcl_ora_11112.trc  orcl_ora_16153.trc  orcl_ora_4997_500.trc
orcl_ora_11130.trc  orcl_ora_16180.trc  orcl_ora_7755.trc
orcl_ora_11146.trc  orcl_ora_16194.trc  orcl_ora_7795.trc
orcl_ora_11147.trc  orcl_ora_16790.trc  orcl_ora_7796.trc

 

[oracle@ora10gr2 ~]$ tkprof orcl_ora_17008_qq.trc a.txt sys=no

[oracle@ora10gr2 ~]$ vi a.txt

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  NESTED LOOPS  (cr=2 pr=0 pw=0 time=340 us)
      0   NESTED LOOPS  (cr=2 pr=0 pw=0 time=314 us)
      0    TABLE ACCESS BY INDEX ROWID I1 (cr=2 pr=0 pw=0 time=300 us)
      0     INDEX RANGE SCAN I1_IDX (cr=2 pr=0 pw=0 time=281 us)(object id 52536)
      0    TABLE ACCESS BY INDEX ROWID MAP (cr=0 pr=0 pw=0 time=0 us)
      0     INDEX RANGE SCAN IDX_MAP (cr=0 pr=0 pw=0 time=0 us)(object id 52535)
      0   TABLE ACCESS BY INDEX ROWID I2 (cr=0 pr=0 pw=0 time=0 us)
      0    INDEX UNIQUE SCAN I2_PK (cr=0 pr=0 pw=0 time=0 us)(object id 52532)

 

-->waits 및 bind 변수 확인 곤란

 

#튜닝을 할려면 10046이벤트로 남겨야 함.

C:\> telnet 192.168.0.10

OS] export ORACLE_SID=orcl
OS] sqlplus sh/sh

 

SQL> alter session set events '10046 trace name context forever, level 12';

 

SQL> select * from scott.emp where empno = 7788;
SQL> /
SQL> /

SQL> select * from scott.emp e, scott.dept d
     where e.deptno = d.deptno
     and e.sal >= 2000;
SQL> /        

 

SQL> ed my_trace_file.sql

 

SET LINESIZE 100
COLUMN trace_file FORMAT A70

SELECT s.sid,
       s.serial#,
       pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||   
       '_ora_' || p.spid || '.trc' AS trace_file
FROM   v$session s,
       v$process p,
       v$parameter pa
WHERE  pa.name = 'user_dump_dest'
AND    s.paddr = p.addr
AND    s.sid = (select sid from v$mystat where rownum=1);

 

SQL> @ my_trace_file.sql

       SID    SERIAL# TRACE_FILE
---------- ---------- ---------------------------------------------------------------------
       159        321     /u01/app/oracle/admin/orcl/udump/orcl_ora_4418.trc

 

SQL> !vi /u01/app/oracle/admin/orcl/udump/orcl_ora_4418.trc

 

SQL> !tkprof /u01/app/oracle/admin/orcl/udump/orcl_ora_4418.trc a.txt
SQL> !vi a.txt   -->유저sql + 시스템sql

 

SQL> !tkprof /u01/app/oracle/admin/orcl/udump/orcl_ora_4418.trc b.txt sys=no
SQL> !vi b.txt   -->시스템의 sql문장 안나오게 함.

 

SQL> !tkprof /u01/app/oracle/admin/orcl/udump/orcl_ora_4418.trc c.txt

         sys=no aggregate=no sort=execpu
SQL> !vi c.txt 

 

SQL> !tkprof /u01/app/oracle/admin/orcl/udump/orcl_ora_4418.trc d.txt

          sys=no explain=scott/tiger record=rec.sql insert=ins.sql
SQL> !vi rec.sql  -->유저sql실행문
SQL> !vi ins.sql  -->tkprof 테이블만듬
SQL> !vi d.txt     

728x90