Oracle跟踪生成及Tkprof分析性能实战(附实战Demo)系列一

本文阅读目录如下：

1、概述

2、Oracle跟踪

3、Tkprof分析

4、简单实例分析实战

 

1、概述

最近研究了一下Oracle的跟踪和对跟踪的分析，在网上搜索了好多资料终于对这一块有了一个大致的了解，本人之前用sqlserver用的比较多，感觉Oracle在这一块确实很不方便啊，Oracle根本没有像SqlServer Profiler那样专业性的图形化工具,还有SqlServer在做性能优化的时候在查询分析器里面能够估算的执行计划和实际计划，sqlserver在这方面确实非常独到啊哈哈，有一些复杂的sql语句、存储过程、函数等在查询分析器里面一估算就能看出在哪个地方出现了问题，而且直接给出修改的建议，我们这个非DBA级别的研发人员用这些工具也可以做DBA的工作了-_-，当然还有DBCC命令等，当然本文不是讲sqlserver的跟踪和优化的，言归正传，本文主要讲解Oracle的跟踪和分析的。

2、Oracle跟踪

2、1 sql脚本跟踪

主要是通过查看v$sql和v$sqlarea两个内置视图来监视所有sql的执行过程。

v$sqlarea和v$sql两个视图的不同之处在于，v$sql中为每一条SQL保留一个条目，而v$sqlarea中根据sql_text进行group by，通过version_count计算子指针的个数。下面对这个问题进行一点延伸探讨。

首先介绍一下v$sql视图，v$sql视图列举了共享SQL区（Shared SQL Area）中的SQL统计信息，这个视图中的信息未经分组，每个SQL指针都包含一条独立的记录。这个视图的主要字段如下：

Column	Datatype	Descrption
SQL_TEXT	VARCHAR2(1000)	当前SQL指针的前1000个字符（也就是说这里记录的SQL是不完整的）
EXECUTIONS	NUMBER	执行次数
DISK_READS	NUMBER	这个子指针Disk Read的次数
BUFFER_GETS	NUMBER	这个子指针的Buffer Gets数量
OPTIMIZER_MODE	VARCHAR2(10)	SQL执行的优化器模式
OPTIMIZER_COST	NUMBER	SQL执行成本
HASH_VALUE	NUMBER	在Library Cache中父指针的Hash Value值

 具体sql查看语句，如下：

select SQL_TEXT,EXECUTIONS,DISK_READS,BUFFER_GETS,OPTIMIZER_MODE,OPTIMIZER_COST,HASH_VALUE,a.MODULE,a.first_load_time from v$sqlarea a
       where 1=1
       --and a.LAST_ACTIVE_TIME >=  to_date( '2013-04-18 14:35:00','yyyy-MM-dd HH24:mi:ss')
       --and a.LAST_ACTIVE_TIME <  to_date( '2013-04-18 18:24:00','yyyy-MM-dd HH24:mi:ss')
       --and a.MODULE_HASH <> 0
       and a.MODULE = 'w3wp.exe'
       order by a.first_load_time desc 

其中a.MODULE 的类型是w3wp.exe时代表监视的是iis站点访问数据库的情况，这个sql脚本跟踪类似于sqlserver profiler中的sql分析；

2、2 sql_trace跟踪

当SQL语句出现性能问题时，我们可以用SQL_TRACE来跟踪SQL的执行情况，通过跟踪，我们可以了解一条SQL或者PL/SQL包的运行情况，SQL_TRACE命令会将SQL执行的整个过程输出到一个trace文件中，我们可以读这个trace 文件来了解在这个SQL执行过程中Oracle 都做了哪些操作，我这里把跟踪又分为实例database跟踪、当前session跟踪和特定session跟踪3种情况

 2.2.1 全局跟踪

alter database set sql_trace=true; 这个是把整个Oracle实例的所有会话都会被跟踪，这样sql语句会特别多，也不好区分是哪个会话的sql出现了问题，所以很少使用；

2.2.2 当前session跟踪

alter session set sql_trace=true;这个是跟踪当前的session，一般是在查询分析器中执行例如pl/sql、sqlplus等连接工具，具体示例代码如下：

-- 创建一个测试t表
create table t as select * from dba_objects;
alter session set tracefile_identifier='limqtest1';--通过设置trace 文件标识
alter session set sql_trace=true;--sql命令启动SQL_TRACE
select * from t where object_id <100; --被跟踪的sql语句
alter session set sql_trace =false;--sql命令关闭SQL_TRACE,这时候生成了trace文件

 

默认的跟踪文件是在10.2.0\db_1\admin\orcl\udump这个路径下面，文件格式类似为：orcl_ora_57832.trc ，当然我们也可以直接用一些sql查出来，主要有两种方式，如下：

(1)sql语句查询最新的trace文件路径，见

sql查询trc

SELECT d.VALUE
|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc'
AS "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;

(2)通过sql函数查询trace文件路径：见

函数查询trc

create or replace function gettracename  return varchar2 is
  v_result varchar2(200);
begin
  SELECT    d.VALUE
      || '/'
      || LOWER (RTRIM (i.INSTANCE, CHR (0)))
      || '_ora_'
      || p.spid
      || '.trc' into v_result
  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; 
  return v_result;
end gettracename;

运行SQL> select gettracename() from dual;即可
SQL> select gettracename() from dual;

这样我们可以直接查看trace文件查看被跟踪sql的性能问题，如下图，是我执行跟踪获取的trc文件：

我感觉阅读起来相当困难啊，还得查Oracle官方文档上的解释，当然DBA对这个应该很精通，我只好想别的办法了，于是我就百度了一下，发现可以用Tkprof这个工具生成类似sqlserver中dbcc命令类似的比较直观的性能视图，这里最后在讲解Tkprof的时候会把这个trc文件转换为txt，从而方便的查看性能的问题。

2.2.3 特定session跟踪，这个就说到重点了，因为我们是开发人员哈哈，我们经常需要跟踪的肯定是我们运行的程序，步骤如下：

（1） --查询V$SESSION视图，获取进程信息，select sid,serial#,username from v$session where   username is not null;  

查询结果

1    128    2645    SYS
2    132    4984    SYS
3    133    1952    AC_SSO
4    134    115    DBSNMP
5    138    102    SYS
6    139    9    DBSNMP
7    140    109    SYSMAN
8    141    2198    SYS
9    148    230    SYSMAN
10    149    11    SYSMAN

 

这里我需要跟踪查询结果中的AC_SSO用户，它的sid是133

（2）跟踪语句：exec dbms_system.set_sql_trace_in_session(133,1952,true);

（3）我们这时候已经启动了跟踪，我们提前打开需要跟踪的程序，然后点击需要跟踪的模块之后，这个操作可以结合前面说的sql脚本跟踪效果更佳，操作完成之后我们执行

exec dbms_system.set_sql_trace_in_session(133,1952,false);来结束我们的跟踪，这样就生成了应用程序的跟踪文件；

3、Tkprof分析

Tkprof是一个用于分析Oracle跟踪文件并且产生一个更加清晰合理的输出结果的可执行工具。如果一个系统的执行效率比较低，一个比较好的方法是通过跟踪用户的会话并且使用Tkprof工具使用排序功能格式化输出，从而找出有问题的SQL语句。

3.1 TKPROF 命令语法：
TKPROF  filename1, filename2 [ SORT  = [ opion][,option] ]
  [  PRINT = integer ]
  [ AGGREGATE  = [ YES | NO ] ]
  [ INSERT = filename3 ]
  [ SYS = [ YES | NO ]  ]
  [  [ TABLE = schema.table ] | [ EXPLAIN = user/password ]  ]
  [  RECORD = filename ]

相关说明：

相关说明

filename1  指定的输入文件，可以是多个文件联起来。
Filename2  格式化输出文件。
SORT     在输出到输出文件前，先进程排序。如果省去，则按照实际使用的顺序输出到文件中。排序选项有以下多种：
  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

 

PRINT        只列出输出文件的第一个integer 的SQL语句。默认为所有的SQL语句。
AGGREGATE    如果= NO ，则不对多个相同的SQL进行汇总。
INSERT       SQL 语句的一种，用于将跟踪文件的统计信息存储到数据库中。在TKPROF创建脚本后，在将结果输入到数据库中。
SYS         禁止或启用 将SYS用户所发布的SQL语句列表到输出文件中。
TABLE       在输出到输出文件前，用于存放临时表的用户名和表名。
EXPLAIN     对每条SQL 语句确定其执行规划。并将执行规划写到输出文件中。

其中比较有用的一个排序选项是fchela，即按照elapsed time fetching来对分析的结果排序（记住要设置初始化参数timed_statistics=true），生成的文件将把最消耗时间的sql放在最前面显示。另外一个有用的参数就是sys，这个参数设置为no可以阻止所有以sys用户执行的sql被显示出来，这样可以减少分析出来的文件的复杂度，便于查看。

3.1 示例代码：

这里我跟前面的对当前会话跟踪连起来出具一个示例，见代码：

alter session set tracefile_identifier='limqtest1';
--允许SQL_TRACE和其他的一些动态性能视图收集与时间有关的参数
alter session set titimed_statistics=true
alter session set sql_trace=true;
 
select * from t where object_id <100;--要跟踪的sql语句

alter session set timed_statistics=false;
alter session set sql_trace =false; --跟踪完成

 

找到我们的跟踪文件路径之后，我们打开cmd，然后执行如下命令：

tkprof E:\oracle\product\10.2.0\db_1\admin\orcl\udump\orcl_ora_38972_limqtest2.trc D:/abc.txt explain=system/manager aggregate=yes sys=no waits=yes sort=fchela

这里大家注意替换路径和文件名，这样我们就在d盘生成了abc.txt,打开看一下一目了然啊哈哈，如下图：

从结果上我们可以看到，查询一共是696个，记录数目总共是101个；

 

先写到这里吧，后续我会对跟踪和分析这一块来一个综合的示例demo分享，从demo中我们就可以了解到对于性能这一块我们如何去优化，然后为什么出现了性能的问题等等；

大家如果阅读了本文章感觉有帮助的话就狂点右下角的【推荐】啊哈哈！

 

转载于:https://www.cnblogs.com/limengqiang/archive/2013/04/18/OracleTraceOne.html