开启当前会话的10046事件

 

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)