设置10046事件来进行SQL跟踪,可以设置不同的跟踪级别
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
10046事件由于跟踪的事件不同,生成的文档的目录也不相同,跟踪用户会话(trace文件位于USER_DUMP_DEST ),跟踪background进程(trace文件位于BACKGROUND_DUMP_DEST )。
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';
测试实例
SQL> set serverout on;
SQL> alter session set events '10046 trace name context forever, level 8';
Session altered
--为了查看生产文件省事,设置一个添加名称
SQL> alter session set tracefile_identifier='yuzh';
Session altered
SQL>执行语句
执行结果
--查看结果所在目录
SQL> show parameter user_dump_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/admin/CNDBS1T9/udump
--如果不设置别名,通过如下语句得到目标文件
SQL> select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||p.spid|| '.trc' trace_file_name
2 from (select p.spid
3 from v$mystat m, v$session s, v$process p
4 where m.statistic# = 1
5 and s.sid = m.sid
6 and p.addr = s.paddr) p,
7 (select t.instance
8 from v$thread t, v$parameter v
9 where v.name = 'thread'
10 and (v.value = 0 or t.thread# = to_number(v.value))) i,
11 (select value from v$parameter where name = 'user_dump_dest') d;
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/CNDBS1T9/udump/cndbs1t9_ora_17764.trc
--如果设置了别名,其实上述语句得出的文件也还是可用的,不过还是使用别名文件最好,文件名称如下
cndbs1t9_ora_17764_yuzh.trc
--直接解读可能比较困难,我们使用tkprof来解析一下
oracle@shdb06t $ tkprof cndbs1t9_ora_17764_yuzh.trc 123.txt
TKPROF: Release 10.2.0.4.0 - Production on Tue Aug 24 15:07:17 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
oracle@shdb06t $ cat 123.txt
解析结果如下:
-------------------------------------------------------------
select *
from
GMS_IVC.IVC_STOCK_COST b where b.enterprise_group_code=:"SYS_B_0" and
b.company_code=:"SYS_B_1" and b.unit_price=:"SYS_B_2" and b.stock_date=
to_date(:"SYS_B_3",:"SYS_B_4")
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 0.14 3.79 1023 21561 0 74
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.14 3.79 1023 21561 0 74
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
74 TABLE ACCESS BY INDEX ROWID IVC_STOCK_COST (cr=21561 pr=1023 pw=0 time=2741575 us)
24351 INDEX SKIP SCAN IVC_STOCK_COST_PK (cr=139 pr=139 pw=0 time=2405079 us)(object id 106381)
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.22 0.23
db file sequential read 1023 0.02 3.67
SQL*Net more data to client 1 0.00 0.00
********************************************************************************
begin
sys.dbms_output.get_line(line => :line, status => :status);
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.01 0 171 0 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 171 0 2
Misses in library cache during parse: 2
Misses in library cache during execute: 2
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 2 0.00 0.00
SQL*Net message from client 2 0.03 0.05
********************************************************************************
-------------------------------------------------------------
--一般需调优的评估量就是disk query current 这三个指标
--关闭10046
alter session set events '10046 trace name context off';
3.对其他用户session设置
首先获得要跟踪的session的sid和serial number
具体获得方式看具体状况
select sid,serial#,username from v$session where username='TRACE_USERNAME';
select sid,serial#,username from v$session where sql_id='*****';
测试实例:
sid=517 ,serial#=5178
dbms_support方式:
--创建监控使用的包
SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/dbmssupp.sql;
Package created.
Package body created.
SQL> exec dbms_support.start_trace_in_session(sid => 517,serial => 5178,waits => true,binds => true);
PL/SQL procedure successfully completed
SQL> exec dbms_support.stop_trace_in_session(sid => 517,serial => 5178);
PL/SQL procedure successfully completed
--查看目标文件名称
SQL> select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||p.spid|| '.trc' trace_file_name
2 from (select p.spid
3 from v$session s, v$process p
4 where s.sid=517
5 and p.addr = s.paddr) p,
6 (select t.instance
7 from v$thread t, v$parameter v
8 where v.name = 'thread'
9 and (v.value = 0 or t.thread# = to_number(v.value))) i,
10 (select value from v$parameter where name = 'user_dump_dest') d;
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/CNDBS1T9/udump/cndbs1t9_ora_19347.trc
--解析目标文件
oracle@shdb06t $ tkprof cndbs1t9_ora_19347.trc 234.txt
TKPROF: Release 10.2.0.4.0 - Production on Tue Aug 24 15:54:54 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
oracle@shdb06t $ cat 234.txt
-----------------------------------------------------------
select *
from
GMS_IVC.IVC_STOCK_COST b where b.enterprise_group_code=:"SYS_B_0" and
b.company_code=:"SYS_B_1" and b.unit_price=:"SYS_B_2" and b.stock_date=
to_date(:"SYS_B_3",:"SYS_B_4")
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.08 0.07 0 21561 0 74
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.09 0.08 0 21561 0 74
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
74 TABLE ACCESS BY INDEX ROWID IVC_STOCK_COST (cr=21561 pr=0 pw=0 time=13993 us)
24351 INDEX SKIP SCAN IVC_STOCK_COST_PK (cr=139 pr=0 pw=0 time=24564 us)(object id 106381)
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.00 0.00
SQL*Net more data to client 1 0.00 0.00
********************************************************************************
---------------------------------------------------------
dbms_system方式
SQL> exec dbms_system.set_ev( 517, 5178, 10046, 8, '');
PL/SQL procedure successfully completed
SQL> exec dbms_system.set_ev( 517, 5178, 10046, 0, '');
PL/SQL procedure successfully completed
--结果解析方法如上,结果如下
------------------------------------------------------------
select *
from
GMS_IVC.IVC_STOCK_COST b where b.enterprise_group_code=:"SYS_B_0" and
b.company_code=:"SYS_B_1" and b.unit_price=:"SYS_B_2" and b.stock_date=
to_date(:"SYS_B_3",:"SYS_B_4")
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.20 3.63 1047 43254 0 148
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.21 3.64 1047 43254 0 148
Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
74 TABLE ACCESS BY INDEX ROWID IVC_STOCK_COST (cr=21561 pr=0 pw=0 time=13993 us)
24351 INDEX SKIP SCAN IVC_STOCK_COST_PK (cr=139 pr=0 pw=0 time=24564 us)(object id 106381)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 4 0.01 0.02
SQL*Net more data to client 2 0.00 0.00
db file sequential read 1047 0.01 3.45
********************************************************************************
------------------------------------------------------------
dbms_monitor方式:
SQL> exec dbms_monitor.session_trace_enable(session_id => 517,serial_num => 5178,waits => true,binds => true);
PL/SQL procedure successfully completed
SQL> exec dbms_monitor.session_trace_disable(session_id => 517,serial_num => 5178);
PL/SQL procedure successfully completed
------------------------------------------------------------
select *
from
GMS_IVC.IVC_STOCK_COST b where b.enterprise_group_code=:"SYS_B_0" and
b.company_code=:"SYS_B_1" and b.unit_price=:"SYS_B_2" and b.stock_date=
to_date(:"SYS_B_3",:"SYS_B_4")
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.01 0.01 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.36 7.64 2190 65586 0 222
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.37 7.66 2190 65586 0 222
Misses in library cache during parse: 3
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
74 TABLE ACCESS BY INDEX ROWID IVC_STOCK_COST (cr=21561 pr=0 pw=0 time=13993 us)
24351 INDEX SKIP SCAN IVC_STOCK_COST_PK (cr=139 pr=0 pw=0 time=24564 us)(object id 106381)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6 0.00 0.00
SQL*Net message from client 6 0.01 0.02
SQL*Net more data to client 3 0.00 0.00
db file sequential read 2190 0.02 7.32
********************************************************************************
------------------------------------------------------------