开启当前会话的10046事件

妖狐艹你老母 2022-06-11 02:41 207阅读 0赞

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