美文网首页
Oracle Tuning Tips

Oracle Tuning Tips

作者: _fenglong | 来源:发表于2018-05-06 12:28 被阅读0次

Find session info

select username, status, sid, serial#  
from v$session
where username ='USER_NAME

Enable tracing for other session

EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234, waits=>TRUE, binds=>FALSE);
or
EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>12, nm=>' ');

  • Level 0 - No trace. Just like switching sql_trace off.
  • Level 2 - The equivalent of regular sql_trace.
  • Level 4 - The same as level 2, but with the addition of bind variable -
    values.
  • Level 8 - The same as level 2, but with the addition of wait events.
  • Level 12 - The same as level 2, but with the addition of both bind variable values and wait events.
    or
    exec dbms_system.set_sql_trace_in_session(3,5023,true);

Find dump file

Trace file named with spid under user_dump_dest location
-- To find SPID

select p.PID,p.SPID,s.SID 
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = '&SESSION_ID'

/

eg: instancename_ora_spid.trc i.e orcl_ora_4393.trc

show parameter user_dump_dest
cd user_dump_dest location
ls -ltr *ora_spid*.trc
eg: ls -ltr ora_4393.trc

Show dump log file

tkprof one_big.trc output=one_big.txt sys=no

相关文章

网友评论

      本文标题:Oracle Tuning Tips

      本文链接:https://www.haomeiwen.com/subject/lfxurftx.html