ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Autotrace工具使用——小工具,大用场

Autotrace工具使用——小工具,大用场

原创 Linux操作系统 作者:realkid4 时间:2011-01-30 22:56:59 0 删除 编辑

 

监控SQL语句,获取执行计划和执行成本,是每个Oracle开发人员与DBA所必须具备的能力之一。

 

Oracle彻底进入CBO时代,我们面对一种全新的局面。一方面,基于数据统计量的CBO优化器,让SQL语句执行计划生成更加科学、更加有效。但是另一方面,优化器和优化模式也变得越来越复杂。我们对于一个SQL操作的执行计划关注度和不确定程度同时在提升。

 

 

在这样的情况下,Oracle提供了一系列的观察SQL执行计划的工具,其中Autotrace工具是最简单和常用的工具。

 

 

简单的说,Autotrace工具具有对SQL语句执行计划进行分析输出,以及评估执行使用资源。下面,我们对Autotrace工具从安装、使用到结果分析进行简单的介绍。

 

Autotrace安装

 

首先,有一点需要说明:Autorace工具是在Oracle提供的Sqlplus窗口上使用。在如pl/sql developer之类的开发工具上是不能使用的。sqlplus虽然使用简单,但是存在界面交互性差的特点。所以如果在Windows平台上,可以在sqlplusw中使用autotrace。不过很遗憾的是,Oracle11g中,sqlplusw这个鸡肋工具也已经不存在了。

 

还有一点建议的是,Autotrace是一个很安全的调试工具。最好可以设置给所有Oracle可以访问到(赋给public用户)。但是,安装的过程中因为存在赋权限相关的内容,最好使用sys用户登录。

 

安装Autotrace工具,共分为两个步骤:

 

步骤一、创建plan_table数据表。Plan_table是Oracle中一个很常用的数据表。它主要为各种工具提供装载生成sql执行计划的空间。

 

//使用Windows环境下做演示;

 

SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 1月 30 20:22:07 2011

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

SQL> conn / as sysdba;

已连接。

SQL> show user

USER 为 "SYS"

 

SQL> @?\rdbms\admin\utlxplan.sql

 

表已创建。

 

 

建立数据表:plan_table是通过脚本utlxplan.sql实现的。该脚本在${ORACLE_HOME}/rdbms/admin目录下(注意windows和Linux/Unix目录结构的差异)。

 

该端演示是使用sqlplusw进行的。其中?表示${ORACLE_HOME}目录,在sqlplus中可以使用?来进行代表。如果在其他工具下,比如PL/SQL Developer下,可以在command窗口下使用 @{脚本路径}\utlxplan.sql。该脚本只是一个create table plan_table命令,如果有兴趣可以单独打开该文件。

 

之后,通过公有同义词和授权方法,将plan_table使用权给所有用户。

 

SQL> create public synonym plan_table for plan_table;

 

SQL> grant all on plan_table to public;

 

授权成功。

 

 

步骤二、安装autotrace脚本

 

autotrace工具脚本执行。在sqlplusw窗口下执行:

 

 

SQL> @?\sqlplus\admin\plustrce.sql //注意是plustrce.sql,不是plustrace.sql,笔者当年困惑了好久。。。

SQL>

SQL> drop role plustrace;

 

角色已删除。

 

SQL> create role plustrace;

 

角色已创建。

 

SQL>

SQL> grant select on v_$sesstat to plustrace;

 

授权成功。

 

SQL> grant select on v_$statname to plustrace;

 

授权成功。

 

SQL> grant select on v_$mystat to plustrace;

 

授权成功。

 

SQL> grant plustrace to dba with admin option;

 

授权成功。

 

 

上面的脚本中,只有@?\sqlplus\admin\plustrce.sql是需要输入的内容,其他都是输入脚本的语句。

 

最后,如果希望所有的用户都能使用的autotrace工具,只要将plustrace角色赋给public就可以了。

 

SQL> grant plustrace to public;

 

授权成功。

 

 

设置结束后,我们已经完全安装好autotrace工具了。下面简单介绍一下,如果和使用autotrace工具。

 

 

使用autotrace

 

使用autorace是在sqlplus环境下,通过对autotrace的开启关闭来设置。下面是一个简单的例子。

 

 

SQL> conn scott/tiger@orcl

已连接。

SQL> set timing on; //设置其可以显示出所有sql语句的执行时间,非常实用!!

SQL> set autotrace on; //设置所有的内容。

SQL> select count(*) from emp;

 

  COUNT(*)

----------                   ………………………语句执行结果

        14

 

已用时间:  00: 00: 00.03

 

执行计划

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

Plan hash value: 2937609675

 

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

| Id  | Operation        | Name   | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |        |     1 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE  |        |     1 |            |          |

|   2 |   INDEX FULL SCAN| PK_EMP |    14 |     1   (0)| 00:00:01 |

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

统计信息

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

        219  recursive calls

          0  db block gets

         41  consistent gets

          1  physical reads

          0  redo size

        408  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          5  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL>

 

 

观察这个输出结果,我们可以看出结果分为下面几个部分:

 

1、输出结果:SQL语句的结果;

2、执行计划;

3、统计信息;

 

每个部分分别承担了不同的地位和作用。但是有一点需要注意,输出的执行计划和依据,都是根据统计量进行评估的结果。实际执行的成本常常与执行计划后面每个步骤的数值有差异。

 

set autotrace命令,还存在一些变种,方便使用。

 

ü        set autotrace on explain; 只显示出执行计划;

ü        set autotrace on statistics; 只显示出统计量信息;

ü        set autotrace traceonly; 不显示查询结果;

ü        set autotrace traceonly explain; 只显示执行语句,并且不会执行语句。最适合进行大表操作的预估算;

 

 

最后,我们介绍一下统计量的各部分含义。

 

项目

含义

备注

recursive calls

递归调用SQL的个数;Oracle在执行这个SQL的时候,有时候会生成很多额外的SQL语句,这个就成为递归调用;

 

db block gets

逻辑读,从数据buffer cache中读取;进行current模式读取;

 

consistent gets

逻辑读,进行一致读模式读取;

 

physical reads

物理读成本;

 

redo size

产生重做日志大小

 

bytes sent via SQL*Net to client

利用sql*net传入到client的字节数;

 

bytes received via SQL*Net from client

利用sql*net传出client的字节数;

 

SQL*Net roundtrips to/from client

 

 

sorts (memory)

内存中排序空间使用;

 

sorts (disk)

物理存储中排序空间使用;

如果memory空间使用不足,是会使用disk的空间的;

rows processed

 

 

 

 

Autorace工具是我们经常使用的性能评测工具,能够帮助解决很多问题。与其相同地位的还有dbms_xplan包方法和set events跟踪事件文件。

 

 

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

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7744998