随笔-314  评论-209  文章-0  trackbacks-0
ORACLE SQL_TRACE的使用

“SQL TRACE”是Oracle提供的用于进行SQL跟踪的手段，是强有力的辅助诊断工具。在日常的数据库问题诊断和解决中，“SQL TRACE”是非常常用的方法。

一般，一次跟踪可以分为以下几步：

1、界定需要跟踪的目标范围，并使用适当的命令启用所需跟踪。

 

2、经过一段时间后，停止跟踪。此时应该产生了一个跟踪结果文件。

 

3、找到跟踪文件，并对其进行格式化，然后阅读或分析。

 

本文就“SQL TRACE”的这些使用作简单探讨，并通过具体案例对SQL_TRACE的使用进行说明。

 

一、“SQL TRACE”的启用。

(A)SQL_TRACE说明

SQL_TRACE可以作为初始化参数在全局启用，也可以通过命令行方式在具体session启用。

 

1．在全局启用

在参数文件(pfile/spfile)中指定: SQL_TRACE = true

   在全局启用SQL_TRACE会导致所有进程的活动被跟踪，包括后台进程及所有用户进程，这通常会导致比较严重的性能问题，所以在生产环境中要谨慎使用。

提示: 通过在全局启用SQL_TRACE，我们可以跟踪到所有后台进程的活动，很多在文档中的抽象说明，通过跟踪文件的实时变化，我们可以清晰的看到各个进程之间的紧密协调。

 

2．在当前session级设置

大多数时候我们使用SQL_TRACE跟踪当前会话的进程。通过跟踪当前进程可以发现当前操作的后台数据库递归活动(这在研究数据库新特性时尤其有效)，研究SQL执行，发现后台错误等。

在session级启用和停止SQL_TRACE方式如下:

启用当前session的跟踪:

SQL> alter session set SQL_TRACE=true;

Session altered.

 

此时的SQL操作将被跟踪:

 

SQL> select count(*) from dba_users;

 

 COUNT(*)

----------

       34

 

结束跟踪:

SQL> alter session set SQL_TRACE=false;

Session altered.

 

3．跟踪其它用户进程

   在很多时候我们需要跟踪其他用户的进程，而不是当前用户，这可以通过Oracle提供的系统包DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION来完成

通过v$session我们可以获得sid、serial#等信息:

获得进程信息，选择需要跟踪的进程:

SQL> select sid,serial#,username from v$session where username =’***’

 

      SID   SERIAL# USERNAME

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

        8      2041 SYS

        9       437 EYGLE

设置跟踪:

SQL> exec dbms_system.set_SQL_TRACE_in_session(9,437,true)

PL/SQL procedure successfully completed.

….

可以等候片刻，跟踪session执行任务,捕获sql操作…

….

停止跟踪:

SQL> exec dbms_system.set_SQL_TRACE_in_session(9,437,false)

PL/SQL procedure successfully completed.

 

(B) 10046事件说明

10046事件是Oracle提供的内部事件，是对SQL_TRACE的增强.

10046事件可以设置以下四个级别:

1 - 启用标准的SQL_TRACE功能,等价于SQL_TRACE。

4 - Level 1 加上绑定值(bind values)

8 - Level 1 + 等待事件跟踪

12 - Level 1 + Level 4 + Level 8

 

类似SQL_TRACE方法，10046事件可以在全局设置，也可以在session级设置。

对于10046事件的设置，涉及到了oracle的“诊断事件”的概念。

 

可以参考以下链接了解详情。http://www.itpub.net/323537,1.html 

1． 在全局设置

在参数文件中增加:

EVENT="10046 trace name context forever,level 12"

此设置对所有用户的所有进程生效、包括后台进程.

 

2． 对当前session设置

通过alter session的方式修改，需要alter session的系统权限:

SQL> alter session set events '10046 trace name context forever';

Session altered.

 

SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

 

SQL> alter session set events '10046 trace name context off';

Session altered.

 

3． 对其他用户session设置

通过DBMS_SYSTEM.SET_EV系统包来实现:

 

SQL> desc DBMS_SYSTEM.SET_EV;

 

Parameter Type          Mode Default?

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

SI       BINARY_INTEGER IN           

SE       BINARY_INTEGER IN           

EV       BINARY_INTEGER IN           

LE       BINARY_INTEGER IN           

NM       VARCHAR2      IN  

其中的参数SI、SE来自v$session视图:

查询获得需要跟踪的session信息:

SQL> select sid,serial#,username from v$session where username is not null;

SID SERIAL# USERNAME

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

8 2041 SYS

9 437 EYGLE

 

执行跟踪:

SQL> exec dbms_system.set_ev(9,437,10046,8,'eygle');

PL/SQL procedure successfully completed.

 

结束跟踪:

SQL> exec dbms_system.set_ev(9,437,10046,0,'eygle');

PL/SQL procedure successfully completed.

 

(C)对启用方法的一些总结。

因为trace的目标范围不同，导致必须使用不同的方法。

?nbsp;       作用于数据库全局的，就改初始化参数。

?nbsp;       只作用于本session的，就用alter session 命令。

?nbsp;       作用于其它session的，就用DBMS_SYSTEM包。

 

再加上10046诊断事件，是SQL_TRACE的增强，又多了一套方法。

 

二、获取跟踪文件

以上生成的跟踪文件位于“user_dump_dest”参数所指定的目录中，位置及文件名可以通过以下SQL查询获得:

1.如果是查询当前session的跟踪文件，使用如下查询：

SELECT d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name 

from 

   ( select p.spid from v$mystat m,v$session s, v$process p 

     where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p, 

   ( select t.instance from v$thread t,v$parameter v 

     where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i, 

   ( select value from v$parameter where name = 'user_dump_dest') d

 

TRACE_FILE_NAME

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

D:\ORACLE\PRODUCT\10.2.0\ADMIN\MYORACLE\UDUMP\hsjf_ora_1026.trc

 

2.如果是查询其他用户session的跟踪文件，则根据用户的sid和#serial使用如下查询：

SELECT d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name 

from 

   ( select p.spid from v$session s, v$process p 

     where s.sid=’’ and s. SERIAL#='' and p.addr = s.paddr) p, 

   ( select t.instance from v$thread t,v$parameter v 

     where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i, 

   ( select value from v$parameter where name = 'user_dump_dest') d

 

TRACE_FILE_NAME

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

D:\ORACLE\PRODUCT\10.2.0\ADMIN\MYORACLE\UDUMP\hsjf_ora_1026.trc

 

三、格式化跟踪文件。

原始的跟踪文件是很难读懂的。需要使用oracle自带的tkprof命令行工具格式化一下。

SQL>$tkprof D:\ORACLE\PRODUCT\10.2.0\ADMIN\MYORACLE\UDUMP\hsjf_ora_1026.trc D:\ORACLE\PRODUCT\10.2.0\ADMIN\MYORACLE\UDUMP\hsjf_ora_1026.txt

这个就可以方便的阅读了。可以在hsjf_ora_1026.txt文件中看到所有的sql语句执行次数,CPU使用时间等数据。

 

备注：可以通过以下方法读取当前已经设置的参数

对于全局的SQL_TRACE参数的设置，可以通过show parameter命令获得。

 

当我们通过alter session的方式设置了SQL_TRACE,这个设置是不能通过show parameter的方式得到的,我们需要通过dbms_system.read_ev来获取：

SQL> set feedback off

SQL> set serveroutput on

SQL> declare

2 event_level number;

3 begin

4 for event_number in 10000..10999 loop

5 sys.dbms_system.read_ev(event_number, event_level);

6 if (event_level > 0) then

7 sys.dbms_output.put_line(

8 'Event ' ||

9 to_char(event_number) ||

10 ' is set at level ' ||

11 to_char(event_level)

12 );

13 end if;

14 end loop;

15 end;

16 /

Event 10046 is set at level 1

引用：http://blog.sina.com.cn/s/blog_4cae4a25010008do.html

posted on 2010-11-13 14:31 xzc 阅读(8606) 评论(2)  编辑  收藏 所属分类: Oracle

评论:
# re: ORACLE SQL_TRACE的使用 2010-11-13 14:31 | xzc
如何读懂tkprof


CALL ：每次SQL语句的处理都分成以下三个部分
Parse：这步将SQL语句转换成执行计划，包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在。
Execute：这步是真正的由Oracle来执行语句。对于insert、update、delete操作，这步会修改数据，对于select操作，这步就只是确定选择的记录。
Fetch：返回查询语句中所获得的记录，这步只有select语句会被执行。
COUNT:这个语句被parse、execute、fetch的次数。
CPU：这个语句对于所有的parse、execute、fetch所消耗的cpu的时间，以秒为单位。
ELAPSED：这个语句所有消耗在parse、execute、fetch的总的时间。
DISK：从磁盘上的数据文件中物理读取的块的数量。一般来说更想知道的是正在从缓存中读取的数据而不是从磁盘上读取的数据。
QUERY：在一致性读模式下，所有parse、execute、fetch所获得的buffer的数量。一致性模式的buffer是用于给一个长时间运行的事务提供一个一致性读的快照，缓存实际上在头部存储了状态。
CURRENT：在current模式下所获得的buffer的数量。一般在current模式下执行insert、update、delete操作都会获取buffer。在current模式下如果在高速缓存区发现有新的缓存足够给当前的事务使用，则这些buffer都会被读入了缓存区中。
ROWS: 所有SQL语句返回的记录数目，但是不包括子查询中返回的记录数目。对于select语句，返回记录是在fetch这步，对于insert、update、delete操作，返回记录则是在execute这步。

A、query+current/rows 平均每行所需的block数，太大的话（超过20）SQL语句效率太低
B、Parse count/Execute count parse count应尽量接近1，如果太高的话，SQL会进行不必要的reparse
C、rows Fetch/Fetch Fetch Array的大小，太小的话就没有充分利用批量Fetch的功能，增加了数据在客户端和服务器之间的往返次数。
D、disk/query+current 磁盘IO所占逻辑IO的比例，太大的话有可能是db_buffer_size过小(也跟SQL的具体特性有关)
E、elapsed/cpu 太大表示执行过程中花费了大量的时间等待某种资源
F、cpu　Or　elapsed 太大表示执行时间过长，或消耗了了大量的CPU时间，应该考虑优化
G、执行计划中的Rows 表示在该处理阶段所访问的行数，要尽量减少   回复  更多评论
  
# re: ORACLE SQL_TRACE的使用 2010-11-13 14:32 | xzc
补充点tkprof的使用方法

Tkprof工具可用来格式化sql trace产生的文件，让你更容易看懂trace的内容


用法：

tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ] ...


参数说明：

tracefile：你要分析的trace文件
outputfile：格式化后的文件
explain=user/password@connectstring
table=schema.tablename
注1：这两个参数是一起使用的，通过连接数据库对在trace文件中出现的每条sql语句查看执行计划，并将之输出到outputfile中
注2：该table必须是数据库中不存在的，如果存在会报错
print=n：只列出最初N个sql执行语句
insert=filename：会产生一个sql文件，运行此文件可将收集到的数据insert到数据库表中
sys=no：过滤掉由sys执行的语句
record=filename：可将非嵌套执行的sql语句过滤到指定的文件中去
waits=yes|no：是否统计任何等待事件
aggregate=yes|no：是否将相同sql语句的执行信息合计起来，默认为yes
sort= option：设置排序选项，选项如下：
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
prscu：number of buffers for current read during parse
prsmis：number of misses in library cache during parse
execnt：number of execute was called
execpu：cpu time spent executing
exeela：elapsed time executing
exedsk：number of disk reads during execute
exeqry：number of buffers for consistent read during execute
execu：number of buffers for current read during execute
exerow：number of rows processed during execute
exemis：number of library cache misses during execute
fchcnt：number of times fetch was called
fchcpu：cpu time spent fetching
fchela：elapsed time fetching
fchdsk：number of disk reads during fetch
fchqry：number of buffers for consistent read during fetch
fchcu：number of buffers for current read during fetch
fchrow：number of rows fetched
userid：userid of user that parsed the cursor
可根据自己的需要设置排序


举例：

1.列出前2条sql语句的执行情况：

C:\>tkprof c:\oc_ora_2892.trc c:\cc.txt print=2

2.将数据保存到数据库：

C:\>tkprof c:\oc_ora_2892.trc c:\cc.txt insert=c:\insert.sql

执行后会在c:\产生insert.sql文件，执行该文件即可将数据保存到数据库，以下为insert.sql部分内容：

REM Edit and/or remove the following CREATE TABLE
REM statement as your needs dictate.
CREATE TABLE tkprof_table
(
date_of_insert DATE
,cursor_num NUMBER
,depth NUMBER
,user_id NUMBER
,parse_cnt NUMBER
,parse_cpu NUMBER
,parse_elap NUMBER
,parse_disk NUMBER
,parse_query NUMBER
,parse_current NUMBER
,parse_miss NUMBER
,exe_count NUMBER
,exe_cpu NUMBER
,exe_elap NUMBER
,exe_disk NUMBER
,exe_query NUMBER
,exe_current NUMBER
,exe_miss NUMBER
,exe_rows NUMBER
,fetch_count NUMBER
,fetch_cpu NUMBER
,fetch_elap NUMBER
,fetch_disk NUMBER
,fetch_query NUMBER
,fetch_current NUMBER
,fetch_rows NUMBER
,ticks NUMBER
,sql_statement LONG
);
INSERT INTO tkprof_table VALUES
(
SYSDATE, 1, 0, 61, 1, 0, 418, 0, 0, 0, 1
, 1, 15625, 1435, 0, 0, 0, 0, 0
, 4, 0, 4417, 0, 24, 0, 36, 13450151
, ’select * from tblinventoryhistory
‘);

3.提取sql执行语句：

C:\>tkprof c:\oc_ora_2892.trc c:\cc.txt record=sqlstr.sql

sqlstr.sql中的内容：

alter session set sql_trace=true ;
alter session set events '10046 trace name context forever,level 12';
select * from tblinventoryhistory ;
select * from tblorder ;
select * from tblproduct ;
select * from tbluser ;
select * from tblroute ;

4.产生执行计划：

C:\>tkprof c:\oc_ora_2892.trc c:\ff.txt explain=ocuser/ocuser table=ocuser.test1

在产生的ff.txt文件中会体现其执行计划：

Rows Execution Plan
——- —————————————————
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS (FULL) OF 'TBLROUTE'   回复  更多评论
  

只有注册用户登录后才能发表评论。
网站导航:
 
Powered by: 博客园 模板提供：沪江博客 Copyright ©2024 xzc