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

最新推荐文章于 2022-03-07 08:53:58 发布

cuigan8424

最新推荐文章于 2022-03-07 08:53:58 发布

阅读量96

点赞数

简要说来，跟踪一个会话发出的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)'

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 客户端后就自动安装到系统中，直接在命令符下用就可以了。