1.启用当前会话的10046事件 用户必须具有alter session的权限
---开启10046事件级别设置为12
SQL> alter session set events '10046 trace name context forever , level 12';
Session altered.
SQL> select * from tt;
ID NAME
---------- --------------------
100 jkkk
SQL> select * from tt where name like 'jk%';
ID NAME
---------- --------------------
100 jkkk
---关闭开启的10046事件
SQL> alter session set events '10046 trace name context off';
Session altered.
在udump下查看生成的trace文件利用tkprof工具转换trace使其变得可读(可以看到语句执行的计划)
tkprof oradb_ora_23390.trc 1.txt
********************************************************************************
SQL ID: 557aq3b2jfhga
Plan Hash: 264906180
select * from tt where name like 'jk%'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 7 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL TT (cr=7 pr=0 pw=0 time=0 us cost=3 size=8 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 17.81 28.71
********************************************************************************
2.利用DBMS_SYSTEM包中set_ev开启1006事件
--开启10046事件先查看会话的sid,seral#
SQL> select SID,SERIAL#,SQL_TRACE from v$session where USERNAME='IMUSE01';
SID SERIAL# SQL_TRACE
---------- ---------- ----------------
47 6067 DISABLED
48 4769 DISABLED
-- 在sys下执行
SQL> execute DBMS_SYSTEM.set_ev( 48,4775,10046,1,null);
PL/SQL procedure successfully completed.
SQL> select SID,SERIAL#,SQL_TRACE from v$session where USERNAME='IMUSE01';
SID SERIAL# SQL_TRACE
---------- ---------- ----------------
47 6067 DISABLED
48 4775 ENABLED
SQL> insert into tt values (2,'uuuu');
1 row created.
SQL> commit;
Commit complete.
--关闭10046事件
SQL> execute DBMS_SYSTEM.set_ev( 48,4775,10046,0,null);
PL/SQL procedure successfully completed.
SQL> select SID,SERIAL#,SQL_TRACE from v$session where USERNAME='IMUSE01';
SID SERIAL# SQL_TRACE
---------- ---------- ----------------
47 6067 DISABLED
48 4775 DISABLED
在udump下查看生成的trace文件利用tkprof工具转换trace使其变得可读(可以看到语句执行的计划)
tkprof oradb_ora_23437.trc 1.txt
********************************************************************************
SQL ID: 5yn6mmpf4vcfn
Plan Hash: 0
insert into tt values(2,'uuuu')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.03 2 1 7 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.04 2 1 7 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
0 LOAD TABLE CONVENTIONAL (cr=1 pr=2 pw=0 time=0 us)
********************************************************************************
3.通过设置sql_trace参说来开启会话级别的10046
alter session set sql_trace=true
SQL> alter session set sql_trace =true;
Session altered.
SQL> select * from tt where id=100;
ID NAME
---------- --------------------
100 jkkk
SQL> alter session set sql_trace=false;
Session altered.
在udump下查看生成的trace文件利用tkprof工具转换trace使其变得可读(可以看到语句执行的计划)
tkprof oradb_ora_23237.trc 1.txt
SQL ID: c713un4bcz1nt
Plan Hash: 264906180
select *
from
tt where id=100
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 7 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL TT (cr=7 pr=0 pw=0 time=0 us cost=3 size=8 card=1)