ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL TRACE和TKPROF,10046的使用步骤

SQL TRACE和TKPROF,10046的使用步骤

原创 Linux操作系统 作者:yantaicuiwei 时间:2011-07-06 23:25:03 0 删除 编辑

一.SQL_TRACE

SQL语句出现性能问题时,我们可以用SQL_TRACE来跟踪SQL的执行情况,通过跟踪,我们可以了解一条SQL或者PL/SQL包的运行情况,SQL_TRACE命令会将SQL执行的整个过程输出到一个trace文件中,我们可以读这个trace文件来了解在这个SQL执行过程中Oracle都做了哪些操作。

可以通过sql命令启动SQL_TRACE,或者在初始化参数里面。

SQL>altersessionset sql_trace=true;

或者

SQL> alterdatabaseset sql_trace=true;

这两条命令的区别:

session级别设置,只对当前session进行跟踪,在实例级别,会对实例上所有的SQL做跟踪,这种方式跟踪的SQL太多,代价是非常大的,所有很少用。

如果是在初始化文件里面设置,只需要在参数文件里添加一个sql_trace参数即可。

示例:

1.确定当前的trace文件。

1.1通过设置trace文件标识

SQL> alter session set tracefile_identifier='安庆怀宁';

会话已更改。

设置标识的目的就是方便我们查找生成的trace文件。我们只需要在trace目录查找文件名里带有标识的文件即可。Oracle 10g中,SQL_TRACE生成的trace文件默认路劲是$ORACLE_BASE/admin/SID/udump.

到了11gtrace默认路径在:$ORACLE_BASE/diag/rdbms/orcl/orcl/trace目录下.

1.2直接用如下SQL直接查出,当前的trace文件名。

/* Formatted on 2010/9/1 23:56:24 (QP5 v5.115.810.9015) */

SELECTd.VALUE

||'/'

||LOWER(RTRIM(i.INSTANCE,CHR(0)))

||'_ora_'

||p.spid

||'.trc'

AS"trace_file_name"

FROM(SELECTp.spid

FROMv$mystatm,v$session s,v$processp

WHEREm.statistic#=1ANDs.SID=m.SIDANDp.addr=s.paddr)p,

(SELECTt.INSTANCE

FROMv$threadt,v$parameter v

WHEREv.NAME='thread'

AND(v.VALUE=0ORt.thread#=TO_NUMBER(v.VALUE)))i,

(SELECTVALUE

FROMv$parameter

WHERENAME='user_dump_dest')d;

SQL> SELECTd.VALUE

2|| '/'

3|| LOWER (RTRIM (i.INSTANCE, CHR (0)))

4|| '_ora_'

5|| p.spid

6|| '.trc' as "trace_file_name"

7FROM (SELECT p.spid

8FROM v$mystat m, v$session s, v$process p

9WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,

10(SELECT t.INSTANCE

11FROM v$thread t, v$parameter v

12WHERE v.NAME = 'thread'

13AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,

14(SELECT VALUE

15FROM v$parameter

16WHERE NAME = 'user_dump_dest') d;

trace_file_name

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

d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_3048.trc

2.启动SQL_TRACE

SQL> alter session set sql_trace=true;

会话已更改。

3.进行相关事务操作

SQL> select * from t;

4.关闭SQL_TRACE

SQL> alter session set sql_trace=false;

会话已更改。

注意,这里是显示的关闭SQL_TRACE,在session级别,也可以直接退出SQLPLUS来终止SQL_TRACE

二.TKPROF工具

Oracle官网的资料:

Using Application Tracing Tools

http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/sqltrace.htm#PFGRF010

SQL_TRACE生成最原始的trace文件的可读性比较差,所以通常我们使用tkprof工具来处理trace文件。Tkprof工具是Oracle自带的一个工具,用于处理原始的trace文件,它的作用主要是合并汇总trace文件中的一些项,规范化文件的格式,使文件更具有可读性。

注意:tkprof 工具只能用在处理SQL_TRACE和10046事件产生的trace,其他事件如10053不能处理。

以前也整理过一篇文章:

使用Tkprof分析ORACLE跟踪文件

http://blog.csdn.net/tianlesoftware/archive/2010/05/29/5632003.aspx

刚才看了一些,也是比较粗糙,不详细,重新在整理一下。Tkprof是系统级别的,直接在系统下执行即可。先看一下tkprof的帮助文档:

C:/Users/Administrator.DavidDai>tkprof

Usage

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

:1.tkprof tracefile outfile [explain=user/password] [options...];

:2.tkprof uat_ora_14936.trc trace.txt sort=(prsdsk,exedsk,fchdsk) print=10 explain=apps/apps table=apps.temp_plan_table_a insert=storea.sql sys=no

tkprof参数介绍

table=schema.tablename Use 'schema.tablename' with 'explain=' option.

用于指定在将执行计划写进输出文件之前,TKPROF用于临时存放执行计划所用表的属主和名称,需要和explain参数一起使用。

explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.

print=integer List only the first 'integer' SQL statements.

只列出输出文件中的第一个INTEGERSQL语句,若忽略,则TKPROF将列出所有跟踪的SQL语句

aggregate=yes|no

若用户指定AGGREGATE=NOTKPROF将不会对相同SQL文本的多个用户进行汇总。

insert=filename List SQL statements and data inside INSERT statements.

SQL脚本的一种,用于将跟踪文件的动机信息存储到数据库中。

sys=no TKPROF does not list SQL statements run as user SYS.

于启动或禁止将用户SYS所发布的SQL语句列表到输出文件之中,也包括递归SQL(为执行用户的SQL语句,ORACLE还必须执行一些附加语句)语句在内。默认为YES

record=filename Record non-recursive statements found in the trace file.

对于跟踪文件中的所用非递归SQL语句,TKPROF将以指定的名称来创建某个SQL脚本。用于对跟踪文件中的用户时间进行重放。

waits=yes|no Record summary for any wait events found in the trace file.

记录trace文件中所有等待事件的摘要。

sort=option Set of zero or more of the following sort options:

在将被跟踪的SQL语句列表输出到跟踪文件之前,先将其按照指定排序选项的降序关系对其进行排序;

若指定了多种排序选项,那么根据排序选项所指定值的和的降序关系对其进行排序;

若忽略此参数,那么TKPROF将按照使用次序把语句列表到输出文件中,具体选项说明如下:

  prscnt number of times parse was called语句解析的数目。

  prscpu cpu time parsing语句解析所占用的CPU时间。

  prsela elapsed time parsing语句解析所占用的时间(总是大于或等于CPU时间);

  prsdsk number of disk reads during parse语句解析期间,从磁盘进行物理读取的数目。

prsqry number of buffers for consistent read during parse语句解析期间,一致模式块读取(CONSISTENT MODE BLOCK READ)的数目。

prscu number of buffers for current read during parse语句解析期间,当前模式读取(CURRENT MODE BLOCK READ)的数目。

  prsmis number of misses in library cache during parse语句解析期间,库缓存失败的数

目。

  execnt number of execute was called语句执行的数目。

  execpu cpu time spent executing语句执行所占用的CPU时间。

  exeela elapsed time executing语句执行所占用的时间(总是大于或等于CPU时间)。

  exedsk number of disk reads during execute语句执行期间,从磁盘进行物理读取的数目。

exeqry number of buffers for consistent read during execute语句执行期间,一致模式块读取(CONSISTENT MODE BLOCK READ)的数目。

execu number of buffers for current read during execute语句执行期间,当前模式读取(CURRENT MODE BLOCK READ)的数目。

  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取数据所占用的CPU时间。

  fchela elapsed time fetching取数据所占用的时间(总是大于或等于CPU时间)。

  fchdsk number of disk reads during fetch取数据期间,从磁盘进行物理读取的数目。

fchqry number of buffers for consistent read during fetch取数据期间,一致模式块读取(CONSISTENT MODE BLOCK READ)的数目。

fchcu number of buffers for current read during fetch取数据期间,当前模式读取(CURRENT MODE BLOCK READ)的数目。

fchrow number of rows fetched所获取的行数。


2.1explain=user/password

trace文件中输入SQL的执行计划,需要注意的是,如果不使用explain,在trace文件中我们看到的是SQL实际的执行路劲。如果使用了explaintkproftrace文件中不但输入SQL的实际执行路径,还会生成该SQL的执行计划。

2.2 sys=no

如果设置为yes,在trace文件中将输入所有的SYS用户的操作,也包含用户SQL语句引发的递归SQL

如果为no,则不输出这些信息。

不过默认情况下是yes,实际上设置为no后,trace文件具有更佳的可读性,因此一般在用tkprof工具时都手工的把该参数设置为no

2.3 aggregate=yes|no

默认情况下,tkprof工具将所有相同的SQL在输入文件中做合并,如果设置为no,则分别列出每个SQL的信息。一般合并后看起来比较简洁,如果需要查看每一个SQL单独的信息,可以把aggregate设置为no

2.4查看第一节中生成的trace文件

C:/Users/Administrator.DavidDai>cd d:/app/administrator/diag/rdbms/orcl/orcl/trace

C:/Users/Administrator.DavidDai>D:

d:/app/Administrator/diag/rdbms/orcl/orcl/trace>tkprof orcl_ora_3048_安庆怀宁.trc安徽安庆怀宁.txt sys=no

TKPROF: Release 11.2.0.1.0 - Development on星期四92 00:22:03 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.

d:/app/Administrator/diag/rdbms/orcl/orcl/trace>

生成的安徽安庆怀宁.txt文件内容如下:

TKPROF: Release 11.2.0.1.0 - Development on星期四92 00:22:03 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.

Trace file: orcl_ora_3048_安庆怀宁.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

********************************************************************************

#以上文件头信息描述了tkprof的版本信息,以及报告中一些列的含义

OVERALL TOTALS FOR ALLNON-RECURSIVE STATEMENTS#非递归SQL语句

callcountcpuelapseddiskquerycurrentrows

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

Parse40.010.030000

Execute50.000.000002

Fetch670.000.0001400980

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

total760.010.0301400982

Misses in library cache during parse: 2#shared pool中没有命令,说明做了2次硬解析,软解析此处为0

Oracle SQL的硬解析和软解析

http://blog.csdn.net/tianlesoftware/archive/2010/04/08/5458896.aspx

Misses in library cache during execute: 1

OVERALL TOTALS FOR ALLRECURSIVE STATEMENTS#递归SQL语句

callcountcpuelapseddiskquerycurrentrows

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

Parse50.000.000000

Execute570.000.000000

Fetch1130.000.0001760110

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

total1750.000.0001760110

Misses in library cache during parse: 0

5userSQL statements in session.

57internal SQL statements in session.

62SQL statements in session.

********************************************************************************

Trace file: orcl_ora_3048_安庆怀宁.trc

Trace file compatibility: 11.1.0.7

Sort options: default

3sessions in tracefile.

10userSQL statements in trace file.

114internal SQL statements in trace file.

62SQL statements in trace file.

9unique SQL statements in trace file.

613lines in trace file.

27elapsed seconds in trace file.

2.5查看trace文件

2.4中,我们看到了tkprof生成的报告,这个报告是一个汇总的结果集,如果想确切的知道SQL语句的每一步执行是如果操作的,就需要分析原始的trace文件。这个trace虽然没有tkprof工具处理之后易读,但是却能够清楚的知道SQL在那个点做了什么,以及SQL是如何工作的,这对与理解SQL语句的执行过程非常有用。

直接打开orcl_ora_3048_安庆怀宁.trc文件:

Trace file d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_3048_安庆怀宁.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Windows NT Version V6.1

CPU: 2 - type 586, 2 Physical Cores

Process Affinity: 0x0x00000000

Memory (Avail/Total): Ph:1559M/4095M, Ph+PgF:4170M/8188M, VA:2881M/4095M

Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 29

Windows thread id: 3048, image: ORACLE.EXE (SHAD)

*** 2010-09-01 23:45:51.877

*** SESSION ID:(267.996) 2010-09-01 23:45:51.877

*** CLIENT ID:() 2010-09-01 23:45:51.877

*** SERVICE NAME:(SYS$USERS) 2010-09-01 23:45:51.877

*** MODULE NAME:(sqlplus.exe) 2010-09-01 23:45:51.877

*** ACTION NAME:() 2010-09-01 23:45:51.877

……..

=====================

PARSING IN CURSOR #12 len=493 dep=1 uid=0 ct=3 lid=0 tim=488541717777 hv=2584065658 ad='b1dad758' sqlid='1gu8t96d0bdmu'

select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)

END OF STMT

PARSE #12:c=0,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2035254952,tim=488541717773

EXEC #12:c=0,e=80,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2035254952,tim=488541718176

FETCH #12:c=0,e=127,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,plh=2035254952,tim=488541718359

STAT #12 id=1 cnt=1 pid=0 pos=1 bj=0 p='MERGE JOIN OUTER (cr=4 pr=0 pw=0 time=0 us cost=2 size=189 card=1)'

STAT #12 id=2 cnt=1 pid=1 pos=1 bj=4 p='TABLE ACCESS CLUSTER TAB$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=137 card=1)'

STAT #12 id=3 cnt=1 pid=2 pos=1 bj=3 p='INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)'

STAT #12 id=4 cnt=0 pid=1 pos=2 bj=0 p='BUFFER SORT (cr=1 pr=0 pw=0 time=0 us cost=0 size=52 card=1)'

STAT #12 id=5 cnt=0 pid=4 pos=1 bj=429 p='TABLE ACCESS BY INDEX ROWID TAB_STATS$ (cr=1 pr=0 pw=0 time=0 us cost=0 size=52 card=1)'

STAT #12 id=6 cnt=0 pid=5 pos=1 bj=430 p='INDEX UNIQUE SCAN I_TAB_STATS$_OBJ# (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)'

CLOSE #12:c=0,e=11607,dep=1,type=3,tim=488541730026

…………

这个文件的可读性要差很多。对这里面的一些参数做些说明:

PARSING IN CURSOR部分:

Len:被解析SQL的长度

Dep:产生递归SQL的深度

Uiduser id

Otc: Oracle command type命令的类型

Lid:私有用户id

Tim:时间戳

Hvhash value

AdSQL address

PARSE,EXEC,FETCH部分

C:消耗的CPU time

Eelapsed time操作的用时

P: physical reads物理读的次数

Cr: consistent reads一致性方式读取的数据块

Cucurrent方式读取的数据块

Miscursor misss in cache硬分析次数

R: -rows处理的行数

Dep: depth递归SQL的深度

Ogoptimizer goal优化器模式

Timtimestamp时间戳

STATS部分:

Id:执行计划的行源号

Cnt:当前行源返回的行数

Pid:当前行源号的父号

Pos:执行计划中的位置

Obj:当前操作的对象id(如果当前行原始一个对象的话)

Op:当前行源的数据访问操作

三.10046事件

Oracle的事件很多。具体参考blog

Oracle跟踪事件set event

http://blog.csdn.net/tianlesoftware/archive/2009/12/13/4977827.aspx

10046事件主要用来跟踪SQL语句,它并不是ORACLE官方提供给用户的命令,在官方文档上也找不到事件的说明信息。但是用的却比较多,因为10046事件获取SQL的信息比SQL_TRACE更多更有利于我们对SQL的判断。

10046事件按照收集信息内容,可以分成4个级别:

Level 1等同于SQL_TRACE的功能

Level 4Level 1的基础上增加收集绑定变量的信息

Level 8Level 1的基础上增加等待事件的信息

Level 12:等同于Level 4+Level 8,即同时收集绑定变量信息和等待事件信息。

3.1对当前session使用10046事件

SQL>alter session set events ‘10046 trace name context forever, level 12’;--启动10046事件

执行相关事务

SQL>alter session set events ‘10046 trace name context off’;--关闭10046事件

该事件收集的信息也是放在trace文件中,查看trace文件的方法,参考第二节:TKPROF工具。

3.2对其他的会话进行跟踪

之前说的都是对当前session进行跟踪,在生产环境中,可能需要对其他session进行跟踪,有如下2种方法:

3.2.1SQL_TRACE跟踪

SQL> select sid,serial# from v$session where SID=267;

SIDSERIAL#

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

267996

SQL> execute dbms_system.set_sql_trace_in_session(267,996,true);--启动SQL_TRACE

PL/SQL过程已成功完成。

SQL> execute dbms_system.set_sql_trace_in_session(267,996,false);--关闭SQL_TRACE

PL/SQL过程已成功完成。

3.2.2使用10046事件跟踪

SQL> exec dbms_monitor.session_trace_enable(267,996,waits=>true,binds=>true);--启动trace

PL/SQL过程已成功完成。

SQL> exec dbms_monitor.session_trace_disable(267,996);--关闭trace

PL/SQL过程已成功完成。

注意:

如果一条SQL语句中包含了通过DBLINK进行的数据操作,我们想对这条SQL进行trace跟踪,在本地只能够trace到本地执行的SQL信息,而对于远程的SQL语句,由于它运行在远端的数据库上,我们要获得它的信息,需要到远端的数据库上,找到运行这条SQL语句的session,然后对它做Trace另外,这条SQL语句的执行计划也只能从远端数据库上捕获到。

总之,当SQL语句操作出现性能问题时,我们可以用SQL_TRACE或者10046事件进行跟踪是最合适的。如果是数据库整体性能下降,就需要使用statspack或者AWR对数据库进行分析。

Oracle AWR介绍

http://blog.csdn.net/tianlesoftware/archive/2009/10/17/4682300.aspx

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

上一篇: oracle 10g logminer
请登录后发表评论 登录
全部评论

注册时间:2010-04-15

  • 博文量
    64
  • 访问量
    236875