ITPub博客

首页 > 数据库 > Oracle > 跟踪某一会话发出的 sql 的方法来优化SQL

跟踪某一会话发出的 sql 的方法来优化SQL

原创 Oracle 作者:passion_of_data 时间:2011-06-20 16:51:53 0 删除 编辑

简要说来,跟踪一个会话发出的SQL 主要分成下面几步:

1) 识别要跟踪的客户端程序到数据库的连接(后面都用 session 代替),主要找出能唯一识别一个session sid serial#.

2) 设定相应的参数,如打开时间开关(可以知道一个 sql  执行了多长时间),存放跟踪数据的文件的位置、最大值。

3)  启动跟踪功能

4)  让系统运行一段时间,以便可以收集到跟踪数据

5)  关闭跟踪功能

6)  格式化跟踪数据,得到我们易于理解的跟踪结果。

 

现在就每一步,给出详细的说明:

1)  识别要跟踪的客户端程序到数据库的数据库连接

查询session 信息(sql*plus 中运行)

set linesize 190

col machine format a30 wrap

col program for a40

col username format a15 wrap

set pagesize 500

select s.sid sid, s.SERIAL# "serial#", s.username, s.machine, s.program,

    p.spid ServPID, s.server

from v$session s, v$process p

where p.addr = s.paddr ;

 

如得到的一个查询结果如下:

 

SID serial# USERNAME MACHINE              PROGRAM       SERVPID      SERVER

 

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

 

  8    3   SCOTT     WORKGROUP\SUNNYXU     SQLPLUS.EXE   388       DEDICATED

 

LOGON_TIME

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

 2005.06.28 18:50:11

 

上面的结果中比较有用的列为:

sid, serial# :  这两个值联合起来唯一标识一个session

username :  程序连接数据库的用户名

machine :  连接数据库的程序所在的机器的机器名,可以 hostname 得到

program :  连接数据库的程序名,所有用java jdbc thin 的程序的名字都一样,

servpid :  与程序对应的服务器端的服务器进程的进程号,在 unix 下比较有用

server : 程序连接数据库的模式:专用模式(dedicaed)、共享模式(shared)

只有在专用模式下的数据库连接,对其进程跟踪才有效

logon_time :  程序连接数据库的登陆时间

 

根据 machine, logon_time  可以方便的识别出一个数据库连接对应的session,从而得到该session 的唯一标识sid, serial#,  为对该session 进行跟踪做好准备

 

2)  设定相应的参数

参数说明:

timed_statistics :  收集跟踪信息时,是否将收集时间信息,如果收集,则可以知道一个sql 的各个执行阶段耗费的时间情况

user_dump_dest :  存放跟踪数据的文件的位置

max_dump_file_size :  放跟踪数据的文件的最大值,防止由于无意的疏忽,使跟踪数据的文件占用整个硬盘,影响系统的正常运行

 

设置的方法:

SQL> exec sys.dbms_system.set_bool_param_in_session( -

   sid    => 8, -

   serial# => 3, -

   parnam   => 'timed_statistics', -

   bval    => true);

 

SQL> alter system set user_dump_dest='c:\temp';

--  注意这个语句会改变整个系统的跟踪文件存放的位置,所以我一般不改这个参数,而用系统的缺省值,要查看当前系统的该参数的值,可以用system 用户登陆后:

SQL> show parameter user_dump_dest

 

SQL> exec sys.dbms_system.set_int_param_in_session( -

   sid    => 8, -

   serial# => 3, -

   parnam   => 'max_dump_file_size', -

   intval => 2147483647)

 

3)  启动跟踪功能

SQL> exec sys.dbms_system.set_sql_trace_in_session(8, 3, true);

注意,只有跟踪的session 再次发出sql 语句后,才会产生trc 文件

 

4)  让系统运行一段时间,以便可以收集到跟踪数据

 

5)  关闭跟踪功能

SQL> exec sys.dbms_system.set_sql_trace_in_session(8,3,false);

 

6)  格式化跟踪数据,得到我们易于理解的跟踪结果。

对产生的trace 文件进行格式化:

在命令提示符下,运行下面的命令

tkprof dsdb2_ora_18468.trc dsdb2_trace.out SYS=NO EXPLAIN=SCOTT/TIGER

其它使用tkprof 的例子:

(a)   tkprof  tracefile.trc  sort_1.prf  explain=apps/your_apps_password  print=10

sort='(prsqry,exeqry,fchqry,prscu,execu,fchcu)'

(b)   tkprof  tracefile.trc  sort_2.prf  explain=apps/your_apps_password  print=10

sort='(prsela,exeela,fchela)'

(c)   tkprof  tracefile.trc  sort_3.prf  explain=apps/your_apps_password  print=10

sort='(prscnt,execnt,fchcnt)'

(d) tkprof tracefile.trc normal.prf explain=apps/your_apps_password

 

现对tkprof 程序做进一步的说明:

    在打开跟踪功能后,oracle 将被跟踪 session 中正在执行的SQL 的性能状态数据都收集到一个跟踪文件中。这个跟踪文件提供了许多有用的信息,例如一个 sql 的解析次数、执行次数、fetch 次数、物理读次数、逻辑读次数、CPU 使用时间等,利用这些信息可以诊断你的 sql 的问题,从而用来优化你的系统。不幸的是,生成的跟踪文件中的数据是我们难以理解的,所以要用TKPROF 工具对其进行转换,转换成我们易于理解格式。tkprof oracle提供的实用工具,类似于 sql*plus,在安装完oracle 客户端后就自动安装到系统中,直接在命令符下用就可以了。

 

当在打开跟踪功能时发生了recursive calls,则tkprof 也会产生这些 recursive calls 的统计信息,并清楚的在格式化输出文件中标名它们为 recursive calls

注意:recursive calls 的统计数据是包含在recursive calls 上的,并不包含在引起该recursive

calls 语句的sql 语句上面。所以计算一个sql 语句耗费的资源时,也要考虑该sql 语句引起recursive calls 语句花费的资源。通过将sys 参数设为 no 时,我们变可以在格式化的输出

文件中屏蔽掉这些 recursive calls 信息。

 

如何得到tkprof 的帮助信息:

运行tkprof 时,不带任何参数,就可以得到该工具的帮助信息。

 

执行计划:

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

    一个语句的执行计划是oracle 执行这个sql 语句的一系列指令。通过检验执行计划,你可以更好的知道oracle 如何执行你的sql 语句,这个信息可以帮助你决定是否你写的sql

句已经使用了索引。如果在tkprof 中指定了EXPLAIN 参数,tkprof 使用 EXPLAIN PLAN 命令来为每个被跟踪

sql 语句产生执行计划。

 

使用说明:

TKPROF 工具接受一个trace 文件作为输入文件,利用提供给命令的多个参数对trace 文件进行分析,然后将格式化好的结果放到一个输出文件中。

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25964700/viewspace-700311/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
DBA

注册时间:2011-06-20

  • 博文量
    53
  • 访问量
    310883