文档章节

SQL跟踪方法介绍 & TKPROF 语句分析工具

 尘埃洛鼎
发布于 2015/03/26 14:38
字数 3235
阅读 52
收藏 0

 

sql_trace介绍

--更改sql_trace部分参数

grant alter session to userid;

当前oracle用户必须具有alter session权限

alter session set tracefile_identifier='look_for_me';

对跟踪文件做标记

alter session set timed_statistics=true|false;

允许sql trace和其他的一些动态性能视图收集与时间有关的参数

alter session set max_dump_file_size=unlimited;

指定跟踪文件的大小无限制

alter system set user_dump_dest=/oracle/oracle/diag/rdbms/orcl/orcl/trace;

指定跟踪文件的路径

 

--开启和关闭sql_trace

1. alter session set sql_trace=true|false;

执行这个语句将在当前会话中启动sql_trace的默认模式,属于交互式方式

2. alter system set sql_trace=true|false;

全局开启sql_trace文件设置,对所有session都有效

 

注:查询当前session默认跟踪文件位置:

select c.value || '\' || d.instance_name || '_ora_' || a.spid || '.trc' trace from v$process a, v$session b, v$parameter c, v$instance d where a.addr = b.paddr and b.sid = sys_context('userenv', 'sid') and c.name = 'user_dump_dest';

 

10046事件

跟踪级别介绍:

Level 0 停用SQL跟踪,相当于SQL_TRACE=FALSE

Level 1 标准SQL跟踪,相当于SQL_TRACE=TRUE

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

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

Level 12 Level 1的基础上增加绑定变量和等待事件的信息

 

1. alter session set events

设置10046事件来跟踪,使用这个不但可以获得sql_trace的所有结果,也能得到sql使用的绑定变量的值和sql的等待事件

alter session set events '10046 trace name context forever,level <n>';

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

2. init.ora文件中插入下面的行:event = 10046 trace name context forever,level 12;

全局设置10046事件来跟踪,对所有session都有效

 

注:sql_trace10046设置代码跟踪只能针对本会话或者系统级进行会话跟踪,具体设置某个非本会话的跟踪需要采用oradebug或者

dbms_system.set_ev或者dbms_monitor.session_trace_enable

 

使用oradebug

 

--用如下语句找出要跟踪的spid

select * from v$session a where audsid = userenv('sessionid'); --查询当前的sessionid

select s.USERNAME,

       s.OSUSER,

       s.SID,

       s.PADDR,

       s.PROCESS,

       p.spid     os_process_id,

       p.pid      oracle_process_id

  from v$session s, v$process p

 where s.paddr = p.addr

   and s.username = upper('LTWEBGIS')

   and s.SID = 145;

 

--设置跟踪进程id

SQL> connect / as sysdba

SQL> oradebug setospid 5672;该语句为跟踪其他会话(5672v$processspid),若跟踪本会话,执行 oradebug setmypid

SQL> connect / as sysdba

SQL> oradebug setorapid 15 --输入的15v$processpid

 

--设置跟踪文件大小无限制

SQL> oradebug unlimit;

 

--设置跟踪,级别8

SQL> oradebug event 10046 trace name context forever,level 8;

 

--使用oradebug获取本会话跟踪文件位置

SQL> oradebug setmypid

SQL> oradebug tracefile_name

 

--获取任意会话跟踪文件位置

SQL> oradebug setospid 5392

已处理的语句

SQL> oradebug tracefile_name

d:\oracle\product\10.2.0\admin\fgisdb\udump\fgisdb_ora_5600.trc

 

 

--关闭跟踪

SQL> oradebug event 10046 trace name context off;

 

注:执行该语句(SQL> oradebug setospid 5672;)时,提示“ORA-01031: 权限不足”,

原因:oradebugsysdba的命令(一般用户执行提示权限不足),而且是sqlplus特有的命令,不能在plsql工具中执行(否则提示无效sql)。

可以用oradebug help命令查看oradebug工具说明。

 

dbms_system(必须用sys用户执行)

 

1. exec dbms_system.set_sql_trace_in_session(v$session.sid,v$session.serial#,true|false)

这个程序包可以为任何一个在v$session中的会话开启和关闭sql_trace

2. exec dbms_system.set_ev(si => 159,se => 254,ev => 10046,le =>8 ,nm => '');

会话id等参数必须设置正确,否则trace无法生成

3. exec dbms_system.set_ev(si => 159,se => 254,ev => 10046,le =>0 ,nm => '');

停止跟踪

 

参考文章:http://www.cnblogs.com/lanzi/archive/2011/01/14/1935558.html

 

TKPROF的介绍

 

TKPROF是一个用于分析Oracle跟踪文件并且产生一个更加清晰合理的输出结果的可执行工具。如果一个系统的执行效率比较低,

一个比较好的方法是通过跟踪用户的会话并且使用TKPROF工具使用排序功能格式化输出,从而找出有问题的SQL语句

 

Tkprof命令后面可以带各种类型的排序选项,具体如下:

Usage: 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:只列出最初Nsql执行语句

insert=filename:会产生一个sql文件,运行此文件可将收集到的数据insert到数据库表中

sys=no:过滤掉由sys执行的语句

record=filename:可将非嵌套执行的sql语句过滤到指定的文件中去

waits=yes|no:是否统计任何等待事件

aggregate=yes|no:是否将相同sql语句的执行信息合计起来,默认为yes

sort= option:设置排序选项,选项如下:

    prscntnumber of times parse was called

    prscpucpu time parsing

    prselaelapsed time parsing

    prsdsknumber of disk reads during parse

    prsqrynumber of buffers for consistent read during parse

    prscunumber of buffers for current read during parse

    prsmisnumber of misses in library cache during parse

    execntnumber of execute was called

    execpucpu time spent executing

    exeelaelapsed time executing

    exedsknumber of disk reads during execute

    exeqrynumber of buffers for consistent read during execute

    execunumber of buffers for current read during execute

    exerownumber of rows processed during execute

    exemisnumber of library cache misses during execute

    fchcntnumber of times fetch was called

    fchcpucpu time spent fetching

    fchelaelapsed time fetching --要设置初始化参数time_statistics=true

    fchdsknumber of disk reads during fetch

    fchqrynumber of buffers for consistent read during fetch

    fchcunumber of buffers for current read during fetch

    fchrownumber of rows fetched

    useriduserid of user that parsed the cursor

可根据自己的需要设置排序

 

sort=(prsela, exeela, fchela)

--sort选项可同时用多个,做法是用括号括起来,中间用逗号分割:

注意:最后排序是按照各个选项的数字之和进行排序,类似于order by (sort1+sort2+sort3),而不是order by sort1,sort2,sort3 

 

分析TKPROF文件:

 

CALL:每次SQL语句的处理都分成以下三个部分

Parse:这步将SQL语句转换成执行计划,包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在。

Execute:这步是真正的由Oracle来执行语句。对于insertupdatedelete操作,这步会修改数据,对于select操作,这步就只是确定选择的记录。

Fetch:返回查询语句中所获得的记录,这步只有select语句会被执行。

 

COUNT:这个语句被parseexecutefetch的次数。

 

CPU:这个语句对于所有的parseexecutefetch所消耗的cpu的时间,以秒为单位。

 

ELAPSED:这个语句所有消耗在parseexecutefetch的总的时间。

 

DISK:从磁盘上的数据文件中物理读取的块的数量。一般来说更想知道的是正在从缓存中读取的数据而不是从磁盘上读取的数据。

 

QUERY:在一致性读模式下,所有parseexecutefetch所获得的buffer的数量。一致性模式的buffer是用于给一个长时间运行的事务提供一个一致性读的快照,缓存实际上在头部存储了状态。

 

CURRENT:在current模式下所获得的buffer的数量。一般在current模式下执行insertupdatedelete操作都会获取 buffer。在current模式下如果在高速缓存区发现有新的缓存足够给当前的事务使用,则这些buffer都会被读入了缓存区中。

 

ROWS:所有SQL语句返回的记录数目,但是不包括子查询中返回的记录数目。对于select语句,返回记录是在fetch这步,对于insertupdatedelete操作,返回记录则是在execute这步。

 

Aquery+current/rows 平均每行所需的block数,太大的话(超过20SQL语句效率太低

 

BParse count/Execute count parse count应尽量接近1,如果太高的话,SQL会进行不必要的reparse

 

Crows Fetch/Fetch Fetch Array的大小,太小的话就没有充分利用批量Fetch的功能,增加了数据在客户端和服务器之间的往返次数。

 

Ddisk/query+current 磁盘IO所占逻辑IO的比例,太大的话有可能是db_buffer_size过小(也跟SQL的具体特性有关)

 

Eelapsed/cpu 太大表示执行过程中花费了大量的时间等待某种资源

 

Fcpu Or elapsed 太大表示执行时间过长,或消耗了了大量的CPU时间,应该考虑优化

 

G、执行计划中的Rows 表示在该处理阶段所访问的行数,要尽量减少

 

 

几个重要参数的用法讲解

sys参数,如果不指定默认值为yes.这个参数的含义是,输出文件中是否包含以SYS用户运行的sql语句。这个参数还是蛮有用的,我们执行sql语句的时候,后台经常会执行很多递归的语句,比如你输入了SELECT * FROM TEST;如果这个语句是硬解析的话,那么会产生很多递归的SQL,递归的去查询表的统计信息,列的统计信息,索引的统计信息等,当然递归的不止是这些。这些递归的sql都是以SYS用户运行的,如果你不希望看到这些递归SQL,那么就加上这个参数sys=no.

 

record参数,它指定的是一个路径下的文件,这个文件用来生成在跟踪文件中找到的所有的非递归SQL。比如你在SQLPLUS里执行了三条语句,select * from a;select * from b;select * from c;,那么如果你指定了这个参数如:record=c:\test.log,那么你用tkprof格式化跟踪文件后,这个test.log里就会记录这三个SQL。这个特性在有些时候还是满有用的,因为跟踪文件往往都会比较大,找起来会比较费劲,我们可以通过指定这个参数先大体了解下,跟踪文件里都有哪些非递归SQL。而且这个功能还有助于我们重演SQL语句(绑定变量的不可以)。

 

aggregate参数,它指定tkprof是否将同样文本内容的sql聚合处理,比如,你执行了十次select * from a,如果你指定这个参数为no(默认情况),那么产生的输出文件会有十个这样语句的执行信息,如果你指定的是yes,那么tkprof会把这十次的执行信息汇总显示。这个参数怎么指定就看你的需要了,个人觉得还是满有用的一个参数。

 

sort参数,这个参数是经常使用到的一个参数,它用来指定tkprof输出文件里sql语句按照什么排序,默认是按照执行的先后顺序排序的,我们可以指定它按照其他方式排序,比如磁盘读取数,CPU时间等。这个参数最经常用的方式是:sort=prsela,exeela,fchela,其实这三个值加起来就是响应时间,即按照响应时间排序。这里别产生误解,tkprof会根据prsela,exeela,fchela三个值的和进行排序,而不是像SQL语句似的一个个的排序。

 

print参数,它经常搭配sort参数一起使用,用来指定tkprof输出sql语句的数量。这两个参数搭配使用起来就比较妙,比如你想知道一个跟踪文件里响应时间排前十的SQL,那么你就可以sort=prsela,exeela,fchela print=10来搭配使用。

 

explain参数,这个参数的含义是为每一个SQL提供一个执行计划。使用的方法是explain=用户名/密码,其实原理很简单,就是通过你指定的用户名,密码登陆数据库,然后为每一个sql执行以下explain plan for sql,输出到plan_table里,最后添加到输出文件里。注意,由于explain plan for 命令要求执行操作的用户要对sql语句里包含的对象都有执行权限,如果包含视图,也要对视图基于的基础表有执行权限,否则产生不了执行计划。注意增加了这个参数后,执行tkprof会比较慢。

 

wait参数,指定输出文件中包含不包含等待事件,默认是包含的。一般都取默认值。

基本最常用到的就这些,其他的我就不介绍了,平时这些也就基本够用了。

 

© 著作权归作者所有

共有 人打赏支持
粉丝 0
博文 2
码字总数 4787
作品 0
杭州
私信 提问
Oracle中sql量化分析工具

Oracle提供的量化分析工具有explain,sqltrace,tkprof,autotrace,awr,addm,sql*profiling,sql access advisor,sql tuning advisor,ash等 此次主要整理了explain,sql*trace,tkprof,auto*trace......

sugar黑黑
2016/01/05
0
0
oracle 性能优化 07_诊断事件

一、诊断事件 诊断事件无官方技术文档支持,使用存在风险,慎用。使用诊断事件可以获取问题更多的信息,调整系统运行 特性,启用某些内部功能。用于系统故障的诊断。跟踪应用,改变系统运行模...

PeakFang-BOK
2018/10/15
0
0
Oracle中获取执行计划的几种方法分析

预估执行计划 - Explain Plan Explain plan以SQL语句作为输入,得到这条SQL语句的执行计划,并将执行计划输出存储到计划表中。 首先,在你要执行的SQL语句前加explain plan for,此时将生成的...

pczhangtl
2014/04/01
0
0
Oracle执行计划不走索引的原因总结

在Oracle数据库操作中,为什么有时一个表的某个字段明明有索引,当观察一些语的执行计划确不走索引呢?如何解决呢?本文我们主要就介绍这部分内容,接下来就让我们一起来了解一下。 不走索引...

xiaoxin
2016/04/22
212
0
Oracle 10046事件 介绍(二) ---tkprof

之前简单的写了10046事件介绍的文章http://hbxztc.blog.51cto.com/1587495/1898624,当然收集10046 trace不是最终的目的,能够读懂并且通过分析10046 trace进而分析相应sql的性能问题才是100...

技术小胖子
2017/11/07
0
0

没有更多内容

加载失败,请刷新页面

加载更多

创建多个git账号

实习开发中我们可能一个机子上配置多个git账号,如github.com,oschina.com 或者工作账号,私人账号,这时候就2个账号用一个key,肯定会冲突,有一个会提示没权限(账号和密码对应不上) ssh ...

echojson
29分钟前
1
0
rabbitmq安装教程

RabbitMQ有Windows与Linux版本的,这里先写Windows版本的安装。 以前安装软件总是在百度上找某某安装教程,结果能按照教程安装好的软件真的不多。想起先前以为大牛说的一句话,去官网按照官网...

em_aaron
今天
7
0
Android 贝塞尔曲线实践——波浪式运动

一、波浪效果如下 贝塞尔曲线自定义波浪效果的案例很多,同样方法也很简单,大多数和本案例一样使用二次贝塞尔曲线实现,同样还有一种是PathMeasure的方式,这里我们后续补充,先来看贝塞尔曲...

IamOkay
今天
3
0
Nmap之防火墙/IDS逃逸

选项 解释 -f 报文分段 --mtu 指定偏移大小 -D IP欺骗 -sI 原地址欺骗 --source-port 源端口欺骗 --data-length 指定发包长度 --randomize-hosts 目标主机随机排序 --spoof-mac Mac地址欺骗 ...

Frost729
今天
2
0
带你搭一个SpringBoot+SpringData JPA的环境

不知道大家对SpringBoot和Spring Data JPA了解多少,如果你已经学过Spring和Hibernate的话,那么SpringBoot和SpringData JPA可以分分钟上手的。 其实我在学完SpringBoot和SpringData JPA了之...

java菜分享
今天
8
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部