oracle 10046详解

 

如果你对oracle性能调优很感兴趣或者比较专长，那么你对oracle的10046事件一定不会陌生。10046event是oracle用于系统性能分析时的一个最重要的事件。当激活这个事件后，将通知oracle kernel追踪会话的相关即时信息，并写入到相应trace文件中。这些有用的信息主要包括sql是如何进行解析，绑定变量的使用情况，会话中发生的等待事件等

10046event 可分成不同的级别（level），分别追踪记录不同程度的有用信息。对于这些不同的级别，应当注意的是向下兼容的，即高一级的trace信息包含低于此级的所有信息。

10046event的追踪级别大致有：

level 1：跟踪sql语句，包括解析、执行、提取、提交和回滚等。

level 4：包括变量的详细信息

level 8：包括等待事件

level 12：包括绑定变量与等待事件

其中，level 1相当于打开了sql_trace

10046event的启用和关闭：

前提条件：（先确保要event的会话环境符合条件）

1、必须确保timed_statistics为TRUE，这个参数可以在会话级上进行修改。

2、为了确保trace输出能够完整进行，还要调整此会话对trace文件大小的限制，一般将此限制取消，即将max_dump_file_size设置为UNLIMITED，或者设置为一个很大的阙值。

在满足了上述条件后，就可以启用10046event对会话进行后台跟踪了。

这里还有几种方式来启用10046event：

一种是在当前会话启用event，可以利用alter session + 事件名称 + level，

如：sql>alter session set event ‘10046 trace name context forever, level 12’;

另外一种是启用别的会话进行event跟踪，可以利用oracle提供的dbms_system来完成。

如：sql>exec dbms_system.set_ev(sid,serial#,10046,12,’’);

注意：

sql>exec dbms_system.set_sql_trace_in_session(sid,serial#,true);

相当于打开了sql_trace。

event的关闭：

可以在通过下面的语句来关闭当前会话的event：

sql>alter session set event ‘10046 trace name context off’;

也可以利用dbms_system包来关闭某个会话的event：

sql>execute dbms_system.set_ev(sid,serial#,0,’’);

这里应当值得一提的是，TRACE将消耗相当的系统资源，因此我们在使用TRACE的时候应当慎重。对于正式的系统，应当只在必要的时候进行TRACE操作，并且应当及时关闭。

当利用事件trace完当前或某个session后，接下来我们的工作就是找到oracle生成的trace了。Oracle的初始化文件中user_dump_dest参数的设置将决定trace文件的生成位置。

从trace文件中查找和发现有用的信息，然后寻找必要的性能调整点并进行相应的调整：

大部分情况下，通过10046事件trace到文件里的信息包含了此会话中存在的性能问题，可以根据trace到的等待事件、SQL语句执行情况以及绑定变量的使用情况来进行分析和查找。

oracle提供了一个工具tkprof来对trace文件进行格式的翻译，以便trace文件中记录的信息能够被我们容易掌握和获取。

基本用法：

 

tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ]
   table=schema.tablename   Use 'schema.tablename' with 'explain=' option.
   explain=user/password    Connect to ORACLE and issue EXPLAIN PLAIN.
   print=integer    List only the first 'integer' SQL statements.
   aggregate=yes|no
   insert=filename  List SQL statements and data inside INSERT statements.
   sys=no           tkprofdoes not list SQL statements run as user SYS.
   record=filename  Record non-recursive statements found in the trace file.
   sort=option      Set of zero or more of the following sort options:
     prscnt  number of times parse was called
     prscpu  cpu time parsing
     prsela  elapsed time parsing
     prsdsk  number of disk reads during parse
     prsqry  number of buffers for consistent read during parse

 

例：

C:>tkprof D:oracleadminora9iudumpora9i_ora_2060.trc d:report.txt
   
    执行完后,在reprot.txt中查询刚才的语句内容如下:
    select count(*)
   from
   sys_dept
  
  
   call     count       cpu    elapsed       disk      query    current        rows
   ------- ------  -------- ---------- ---------- ---------- ----------  ----------
   Parse        1      0.00       0.01          1         31          0           0
   Execute      1      0.00       0.00          0          0          0           0
   Fetch        1      0.00       0.01          5          7          0           1
   ------- ------  -------- ---------- ---------- ---------- ----------  ----------
   total        3      0.00       0.03          6         38          0           1
   
   Misses in library cache during parse: 1
   Optimizer goal: CHOOSE
   Parsing user id: 62     
  
   通过设置tkprof的关键字[EXPLAIN = <username/password>],也可以在跟踪文件中增加SQL语句的执行计划：
  C:>tkprof D:oracleadminora9iudumpora9i_ora_2060.trc d:report.txt explain=test/test;  
  
  ********************************************************************************
  
  select count(*)
  from
   sys_dept
  
  
  call     count       cpu    elapsed       disk      query    current        rows
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  Parse        2      0.00       0.01          1         31          0           0
  Execute      2      0.00       0.00          0          0          0           0
  Fetch        2      0.00       0.01          5         14          0           2
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  total        6      0.00       0.03          6         45          0           2
  
  Misses in library cache during parse: 1
  Optimizer goal: CHOOSE
  Parsing user id: 62 
  
  Rows     Row Source Operation
  -------  ---------------------------------------------------
        1  SORT AGGREGATE
       16   TABLE ACCESS FULL SYS_DEPT

   

 

 

小知识：

检查当前会话的sql_trace状态或级别：

SQL>set serveroutput on

1 declare i_event number;

2 begin

  3 sys.dbms_system.read_ev(10046,i_event);

  4 dbms_output.put_line(‘the session sql_trace level is: ‘||i_event);

  5 end;

6 /

the session sql_trace level is: 12

PL/SQL 过程已成功完成。