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.