10046&10053 event

exec sys.dbms_system.set_ev(SID,SERIAL#,10046,8,'');

trace文件在user_dump_dest下，<sid>_ora_<spid>.trc

-- 当前会话：
alter session set events '10046 trace name context forever, level 12';

level 1：跟踪sql语句，包括解析、执行、提取、提交和回滚等。
level 4：包括变量的详细信息
level 8：包括等待事件
level 12：包括绑定变量与等待事件

SELECT d.value || '/' || c.instance_name || '_ora_' || spid || '.trc'
  FROM v$process a, v$session b, v$instance c, v$parameter d
 WHERE b.sid = &sid
   AND b.paddr = a.addr
   AND d.name = 'user_dump_dest'

alter system flush shared_pool;

oradebug setmypid
set timing on
oradebug event 10046 trace name context forever,level 12
select sysdate from dual;
Elapsed: 00:00:00.00
SQL> 
SQL> 
SQL> DECLARE
  2    p_retcode NUMBER;
  3    p_rettext VARCHAR2(500);
  4  begin
  6    xxx.xx.xxx(xxx => '10416101900155587404',
  7                                                xxx => '10416101900155587404',
  8                                                xxx => p_retcode,
  9                                                xxx => p_rettext);
10  end;
11  /


PL/SQL procedure successfully completed.

Elapsed: 00:06:29.05
SQL> 
SQL> oradebug event 10046 trace name context off
Statement processed.
SQL> oradebug tracefile_name
/xxx_ora_22821.trc
SQL> exit

alter session set tracefile_identifier='10053_trc';


alter session set events '10053 trace name context forever, level 2';
explain plan for 
update collection_notice c     set c.gather_notice_no = '', 
c.gather_class_code = c.gather_notice_no     WHERE c.notice_no = :1
     and c.gather_notice_no is not null     AND NOT exists (select 1           
     from collection_notice a          where a.notice_no = c.gather_notice_no)
;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22818880/viewspace-2077117/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22818880/viewspace-2077117/