【优化】10046事件之生成跟踪文件

10046事件：

1）10046事件分类

10046 事件按照收集信息内容，可以分成4个级别：

Level 1：跟踪sql语句，包括解析、执行、提取、提交和回滚等；等同于SQL_TRACE 的功能

Level 4：在Level 1的基础上增加收集绑定变量的信息

Level 8：在Level 1 的基础上增加等待事件的信息

Level 12：等同于Level 4+Level 8, 即同时收集绑定变量信息和等待事件信息。

 

2）使用10046的前提条件

SYS@ORA11GR2>show parameter timed_statistics --是否收集与时间相关的统计信息

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------

timed_statistics                     boolean     TRUE（默认）

SYS@ORA11GR2>show parameter statistics_level--此参数不允许为basic

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------

statistics_level                     string      TYPICAL（默认）

SYS@ORA11GR2>show parameter max_dump_file_size--注意此参数，如果trace文件过于大的话，此参数要设置为unlimited（默认）

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------

max_dump_file_size                   string      unlimited（默认）

SYS@ORA11GR2>

 

3）非绑定变量实验：

——创建测试表

SYS@ORA11GR2>create table t(id number(5) primary key,create_date date default sysdate not null);

 

Table created.

 

——开启10046事件（收集一条或多条sql执行计划的运行状况）

SYS@ORA11GR2>alter session set events '10046 trace name context forever,level 12';

 

Session altered.

 

——对t表进行操作：

       
  begin
  for i in 21..40 loop
  execute immediate'insert into t(id) values('||i||')';
  end loop;
  end;
  /

 

PL/SQL procedure successfully completed.

 

——关闭10046事件

SYS@ORA11GR2>alter session set events '10046 trace name context off';

 

Session altered.

 

——查询跟踪文件位置：

SELECT c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
    FROM v$process a, v$session b, v$parameter c, v$instance d
   WHERE a.addr = b.paddr
        AND b.audsid = userenv('sessionid')
        AND c.name = 'user_dump_dest';

 

TRACE

-------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_18140.trc

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_10479.trc

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_10495.trc

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_14272.trc

其他的trace文件要么不存在，要么不是当前生成的trace文件；

SYS@ORA11GR2>

 

——利用tkprof工具格式化生成的trace文件

[oracle@wang trace]$ tkprof ORA11GR2_ora_18140.trc 10046.txt

 

TKPROF: Release 11.2.0.4.0 - Development on Wed Oct 12 22:00:30 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

验证：

[oracle@wang trace]$ ls 10046.txt

10046.txt

[oracle@wang trace]$ pwd

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace

——查看：

[oracle@wang trace]$ cat 10046.txt

……省略……

*******************************************************************

SQL ID: 2fmndg2ajj1n7 Plan Hash: 0

 

insert into t(id)

values

(24)

 

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          1          3           1

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------

total        2      0.00       0.00          0          1          3           1

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS   (recursive depth: 1)

Number of plan statistics captured: 1

 

Rows (1st) Rows (avg) Rows (max)  Row Source Operation

---------- ---------- ----------  ----------------------------------

         0          0          0  LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=52 us)

 

*******************************************************************

省略其他插入值的执行计划…………………………………………………………..

 

2）绑定变量实验：

——开启10046事件

SYS@ORA11GR2>alter session set events '10046 trace name context forever, level 12';

 

Session altered.

 

——进行操作生成trace文件：

SYS@ORA11GR2>begin

  2    for i in 1..20 loop

  3      execute immediate 'insert into t(id,create_date) values(:1,:2)' using i,sysdate;

  4    end loop;

  5  end;

  6  /

 

PL/SQL procedure successfully completed.

 

——查看trace文件位置：

SYS@ORA11GR2>SELECT c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace

  2     FROM v$process a, v$session b, v$parameter c, v$instance d

  3    WHERE a.addr = b.paddr

  4      AND b.audsid = userenv('sessionid')

  5      AND c.name = 'user_dump_dest';

 

TRACE

-------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_18140.trc

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_10479.trc

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_10495.trc

 

——验证：

[oracle@wang trace]$ ls /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_18140.trc

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_18140.trc

[oracle@wang trace]$ ls /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_10479.trc

ls: /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_10479.trc: No such file or directory

[oracle@wang trace]$ ls /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_10495.trc

ls: /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_10495.trc: No such file or directory

 

——利用tkprof工具格式化生成的trace文件

[oracle@wang trace]$ tkprof ORA11GR2_ora_18140.trc 10046.tkf

 

TKPROF: Release 11.2.0.4.0 - Development on Wed Oct 12 22:18:17 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

——查看：

[oracle@wang trace]$ ls 10046.t

10046.tkf  10046.txt 

[oracle@wang trace]$

 

[oracle@wang trace]$ cat 10046.tkf

………………………………省略………………………………………………

*******************************************************************

begin

  for i in 1..20 loop

    execute immediate 'insert into t(id,create_date) values(:1,:2)' using i,sysdate;

  end loop;

end;

 

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           1

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------

total        2      0.00       0.00          0          0          0           1

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

 

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ---

  SQL*Net message to client                       1        0.00          0.00

  SQL*Net message from client                     1        0.00          0.00

*******************************************************************

SQL ID: bdsmcbhvqt7by Plan Hash: 0

 

insert into t(id,create_date)

values

(:1,:2)

 

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0           0

Execute     20      0.00       0.00          0          1         62          20

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------

total       21      0.00       0.00          0          1         62          20

 

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS   (recursive depth: 1)

Number of plan statistics captured: 1

 

Rows (1st) Rows (avg) Rows (max)  Row Source Operation

---------- ---------- ----------  ----------------------------------

         0          0          0  LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=162 us)

 

*******************************************************************

 

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

转载于:http://blog.itpub.net/31397003/viewspace-2126466/