10046

使用跟踪事件10046 很多时候，对数据库进行性能诊断可以使用SQL跟踪的方法，把一些信息记录在trace文件里以后分析。一般情况下我们可以通过初始化参数SQL_TRACE=TRUE来设置SQL跟踪。
　　
　　我们也可以通过设置10046事件来进行SQL跟踪，并且可以设置不同的跟踪级别，比使用SQL_TRACE获得更多的信息。
　　
　　Level 0 停用SQL跟踪，相当于SQL_TRACE=FALSE
　　Level 1 标准SQL跟踪，相当于SQL_TRACE=TRUE
　　Level 4 在level 1的基础上增加绑定变量的信息
　　Level 8 在level 1的基础上增加等待事件的信息
　　Level 12 在level 1的基础上增加绑定变量和等待事件的信息
　　
　　10046事件不但可以跟踪用户会话(trace文件位于USER_DUMP_DEST )，也可以跟踪background进程(trace文件位于BACKGROUND_DUMP_DEST )。trace文件的大小决定于4个因素：跟踪级别，跟踪时长，会话的活动级别和MAX_DUMP_FILE_SIZE参数。
　　
　　启用跟踪事件10046

　　1.在全局设置
　　
　　修改初始化参数
　　EVENT = '10046 trace name context forever, level 8'
　　
　　2.在当前session设置
　　
　　alter session set events '10046 trace name context forever, level 8';
　　alter session set events '10046 trace name context off';
　　
　　3.对其他用户session设置
　　
　　首先获得要跟踪的session的session id和serial number
　　select sid,serial#,username from v$session where username='TRACE_USERNAME';

　　exec dbms_support.start_trace_in_session(sid => 1234,serial# => 56789,waits => true,binds => true);
　　exec dbms_support.stop_trace_in_session(sid => 1234,serial# => 56789);
　　
　　或者
　　exec dbms_system.set_ev( 1234, 56789, 10046, 8, '');
　　exec dbms_system.set_ev( 1234, 56789, 10046, 0, '');
　　
　　或者
　　exec dbms_monitor.session_trace_enable(session_id => 1234,serial_num => 56789,waits => true,binds => true);
　　exec dbms_monitor.session_trace_disable(session_id => 1234,serial_num => 56789);

    对当前会话跟踪
    1 set serveroutput on   --开启信息回放
    2 alter session set tracefile_identifier='zfktrace'; --设置跟踪文件名           
    3 alter session set events '10046 trace name context forever,level 12'; --设置跟踪级别
    4 show parameter  user_dump_dest --以sys用户查看
    5  Select  f_yearmonth,f_lotid,f_lotname,SUM (f_ticketoutmoney) AS money  --执行语句
       FROM   t_base_proj_suc
       Where  f_expectenddate   > to_date('2010-01-01','yyyy-mm-dd')
       AND    f_expectenddate < trunc(sysdate,'dd')
       GROUP BY (f_yearmonth,f_lotid,f_lotname)
    6 alter session set events '10046 trace name context off'; --关闭监控
  
    7 到linux下数据库的udump 目录下 比如：/data/shark_dump/udump
    8 tkprof ba_ora_21511_zfktrace.trc zfktrac.txt       --格式化下跟踪文件

 

resutl:

TKPROF: Release 10.2.0.3.0 - Production on ÐÇÆÚÈý 1ÔÂ 13 17:11:44 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Trace file: ba_ora_21511_zfktrace.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************
       Select  f_yearmonth,f_lotid,f_lotname,SUM (f_ticketoutmoney) AS money
                                FROM   t_base_proj_suc
                                Where  f_expectenddate   > to_date('2010-01-01','yyyy-mm-dd')
                                AND    f_expectenddate < trunc(sysdate,'dd')
        GROUP BY (f_yearmonth,f_lotid,f_lotname)

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        1     15.03      28.34      34321      69197          0          15
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total         3     15.04      28.35      34321      69197          0          15

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55 

Rows     Row Source Operation
-------  ---------------------------------------------------
     15        HASH GROUP BY (cr=69197 pr=34321 pw=0 time=28345680 us)
2201633   FILTER  (cr=69197 pr=34321 pw=0 time=24234543 us)
2201633    PARTITION RANGE ITERATOR PARTITION: 37 KEY (cr=69197 pr=34321 pw=0 time=22032901 us)
2201633     TABLE ACCESS FULL T_BASE_PROJ_SUC PARTITION: 37 KEY (cr=69197 pr=34321 pw=0 time=17629607 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    2        0.00          0.00
  SQL*Net message from client                2        0.13          0.15
  db file scattered read                        394        0.40         14.29
  log file switch completion                       2        0.09          0.11
  buffer busy waits                                  1        0.00          0.00
  db file sequential read                        54        0.01          0.15



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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        8      0.00       0.00          0          0          0           0
Execute      8      0.00       0.00          0          0          0           4
Fetch        3     15.03      28.34      34321      69197          0          17
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       19     15.04      28.35      34321      69197          0          21

Misses in library cache during parse: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      11        0.00          0.00
  SQL*Net message from client                    11       84.84        169.93
  db file scattered read                        394        0.40         14.29
  log file switch completion                      2        0.09          0.11
  buffer busy waits                               1        0.00          0.00
  db file sequential read                        54        0.01          0.15


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute     0      0.00       0.00          0          0          0           0
Fetch         0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total           0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

    7  user  SQL statements in session.
    0  internal SQL statements in session.
    7  SQL statements in session.
********************************************************************************
Trace file: ba_ora_21511_zfktrace.trc
Trace file compatibility: 10.01.00
Sort options: default

       0  session in tracefile.
       7  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       7  SQL statements in trace file.
       3  unique SQL statements in trace file.
     566  lines in trace file.
     198  elapsed seconds in trace file.