ORACLE中SQLPLUS中的trace的使用

一、Sqlplus Trace 使用入门

AUTOTRACE是一项SQL*Plus功能，自动跟踪为语句生成一个执行计划并且提供与该语句的处理有关的统计。

   SQL*Plus AUTOTRACE可以用来替代SQL Trace使用，AUTOTRACE的好处是您不必设置跟踪文件的格式，并且它将自动为SQL语句显示执行计划。然而，AUTOTRACE分析和执行语句；而EXPLAIN PLAN仅分析语句。

   使用AUTOTRACE不会产生跟踪文件。

一、set autotrace语法及选项的说明

1、用法:SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

OPTION说明

SET AUTOTRACE OFF          此为默认值，即关闭Autotrace

SET AUTOTRACE ON            同时输出执行结果以及统计信息和执行计划信息。

SET AUTOTRACE ON EXPLAIN   只打开执行计划报表，显示命令结果，不显示统计信息。

SET AUTOTRACE ON STATISTICS 只打开统计信息报表，显示命令结果，不显示执行计划。

SET AUTOTRACE TRACEONLY     不显示命令的执行结果，显示执行计划和统计信息，但在traceonly的后面仍然可以追加explain或者statistics，等同于set autotrace on [explain|statistics]但是不显示执行结果。

2、Autotrace执行计划的各列的涵义

ID_PLUS_EXP            每一步骤的行号

PARENT_ID_PLUS_EXP     每一步的Parent的级别号

PLAN_PLUS_EXP          实际的每步

OBJECT_NODE_PLUS_EXP   Dblink或并行查询时才会用到

3、AUTOTRACE Statistics常用列解释

db block gets      从buffer cache中读取的block的数量

consistent gets    从buffer cache中读取的undo数据的block的数量

physical reads      从磁盘读取的block的数量

redo sizeDML       生成的redo的大小

sorts (memory)      在内存执行的排序量

sorts (disk)        在磁盘上执行的排序量

二、使用前设置及Autotrace授权

1、任何以SQL*PLUS连接的session都可以用Autotrace,不过还是要做一些设置的，否则可能报错。

报错示例：

SQL:> set autotrace on;

SP2-0613: Unable to verify PLAN_TABLE format or existence

SP2-0611: Error enabling EXPLAIN report

SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

SP2-0611: Error enabling STATISTICS report

该错误的的主要原因是由于当前用户下没有PLAN_TABLE这张表及相应的PLUSTRACE角色权限。

2、 通过以下方法可以把Autotrace的权限授予Everyone，如果你需要限制Autotrace权限，可以把对public的授权改为对特定user的授权。

D:\oracle\ora92>sqlplus /nolog

SQL> connect sys as sysdba

SQL> @?\rdbms\admin\utlxplan    --表已创建。

SQL> create public synonym plan_table for plan_table;   --同义词已创建。

SQL> grant all on plan_table to public ;

SQL> @?\sqlplus\admin\plustrce

SQL> drop role plustrace;

SQL> create role plustrace;

SQL> grant select on v_$sesstat to plustrace;

SQL> grant select on v_$statname to plustrace;

SQL> grant select on v_$session to plustrace;

SQL> grant plustrace to dba with admin option;

SQL> set echo off

  DBA用户首先被授予了plustrace角色,然后我们可以把plustrace授予public；这样所有用户都将拥有plustrace角色的权限.

SQL> grant plustrace to public ;

三、使用AutoTrace的功能

SQL> connect eqsp/eqsp

SQL> set autotrace on

SQL> set timing on

SQL> set autotrace traceonly

SQL> select table_name from user_tables;

已选择98行。

已用时间: 00: 00: 00.04

Execution Plan

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

0 SELECT STATEMENT ptimizer=CHOOSE

1 0 NESTED LOOPS

2 1 NESTED LOOPS (OUTER)

3 2 NESTED LOOPS (OUTER)

4 3 NESTED LOOPS (OUTER)

5 4 NESTED LOOPS (OUTER)

6 5 NESTED LOOPS

7 6 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'

8 7 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)

9 6 TABLE ACCESS (CLUSTER) OF 'TAB$'

10 9 INDEX (UNIQUE SCAN) OF 'I_OBJ#' (NON-UNIQUE)

11 5 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'

12 11 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)

13 4 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)

14 3 TABLE ACCESS (CLUSTER) OF 'USER$'

15 14 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)

16 2 TABLE ACCESS (CLUSTER) OF 'SEG$'

17 16 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE)

18 1 TABLE ACCESS (CLUSTER) OF 'TS$'

19 18 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)

Statistics

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

0 recursive calls

0 db block gets

1389 consistent gets

0 physical reads

0 redo size

2528 bytes sent via SQL*Net to client

569 bytes received via SQL*Net from client

8 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

98 rows processed

一、Sqlplus Trace 中统计结果的解析

recursive   calls:   递归调用
  db   block   gets:数据块读取
  consistent   gets:一致性读取
  physical   reads:物理读

  逻辑读=     consistent   gets   +   db   block   gets

  缓冲区命中率=   (逻辑读-物理读)/逻辑读

  因此，要保证尽可能少的物理读。    

    redo   size:产生的重做日志大小

    XXXX     bytes   sent   via   SQL*Net   to   client:   通过SQL   NET   发送到客户端的字节数;
    XXXX   bytes   received   via   SQL*Net   from   client:   客户端收到的字节数;

    XX     SQL*Net   roundtrips   to/from   client:   网络来回次数
    XX     sorts   (memory):在内存中进行的排序
    XX     sorts   (disk)磁盘(temporay   表空间)排序