ITPub博客

首页 > 数据库 > Oracle > Let's talk about Oracle Autotrace

Let's talk about Oracle Autotrace

原创 Oracle 作者:qiuyb 时间:2006-04-11 14:56:04 0 删除 编辑
在SQL*Plus中,你可以通过设置autotrace选项来在执行SQL命令的同时,自动的获得语句的执行计划和附加的统计信息。AUTOTRACE是一个很出色的Oracle SQL语句的诊断工具,与Explain plan不同的是这条SQL是实际执行了的,同时AUTOTRACE使用起来也极为方便。[@more@]

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

1、报错示例:
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

2、解决方法:

a.以SYS用户运行plustrce.sql脚本

cd $ORACLE_HOME/sqlplus/admin

oracle>sqlplus '/ as sysdba';
SQL>@plustrce.sql

b.给任何想使用Autotrace的用户授PLUSTRACE权限。

SQL>grant plustrace to hr;

c.同时被授权的用户比如hr用户的PLAN_TABLE这个表必须存在。如果不存在这样:

cd $ORACLE_HOME/sqlplus/admin

oracle>sqlplus hr/hr; --hr为示例用户
SQL>@utlxplan.sql

二、设置Autotrace的命令。

序号命令解释
1SET AUTOTRACE OFF此为默认值,即关闭Autotrace
2SET AUTOTRACE ON EXPLAIN只显示执行计划
3SET AUTOTRACE ON STATISTICS 只显示执行的统计信息
4SET AUTOTRACE ON包含2,3两项内容
5SET AUTOTRACE TRACEONLY 与ON相似,但不显示语句的执行结果。

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

序号列名 解释
1ID_PLUS_EXP每一步骤的行号
2PARENT_ID_PLUS_EXP每一步的Parent的级别号
3PLAN_PLUS_EXP实际的每步
4OBJECT_NODE_PLUS_EXP Dblink或并行查询时才会用到

四、AUTOTRACE Statistics常用列解释

序号统计列解释
1db block gets从buffer cache中读取的block的数量
2consistent gets从buffer cache中读取的undo数据的block的数量
3physical reads从磁盘读取的block的数量
4redo sizeDML生成的redo的大小
5sorts (memory)在内存执行的排序量
7sorts (disk)在磁盘上执行的排序量

五、示例

oracle@yang:~> sqlplus hr/hr

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Apr 12 15:46:56 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL>set autotrace on;

SQL> select b.DEPARTMENT_NAME,sum(a.SALARY)
2 from employees a,departments b
3 where a.DEPARTMENT_ID=b.DEPARTMENT_ID
4 group by b.DEPARTMENT_NAME
5 /

DEPARTMENT_NAME SUM(A.SALARY)
------------------------------ -------------
Accounting 20300
Administration 4400
Executive 58000
Finance 51600
Human Resources 6500
IT 28800
Marketing 19000
Public Relations 10000
Purchasing 24900
Sales 304500
Shipping 156400

11 rows selected.

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
42 consistent gets
0 physical reads
0 redo size
678 bytes sent via SQL*Net to client
498 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
11 rows processed

SQL> set autotrace on
SQL> /

DEPARTMENT_NAME SUM(A.SALARY)
------------------------------ -------------
Accounting 20300
Administration 4400
Executive 58000
Finance 51600
Human Resources 6500
IT 28800
Marketing 19000
Public Relations 10000
Purchasing 24900
Sales 304500
Shipping 156400

11 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=25 Card=27 Bytes=621
)

1 0 SORT (GROUP BY) (Cost=25 Card=27 Bytes=621)
2 1 HASH JOIN (Cost=21 Card=106 Bytes=2438)
3 2 TABLE ACCESS (FULL) OF 'DEPARTMENTS' (Cost=10 Card=27
Bytes=432)

4 2 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=10 Card=107 B
ytes=749)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
42 consistent gets
0 physical reads
0 redo size
678 bytes sent via SQL*Net to client
498 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
11 rows processed

SQL> edit
Wrote file afiedt.buf

1 select b.DEPARTMENT_NAME,sum(a.SALARY)
2 from employees a,departments b
3 where a.DEPARTMENT_ID=b.DEPARTMENT_ID
4* group by b.DEPARTMENT_NAME
SQL> /

DEPARTMENT_NAME SUM(A.SALARY)
------------------------------ -------------
Accounting 20300
Administration 4400
Executive 58000
Finance 51600
Human Resources 6500
IT 28800
Marketing 19000
Public Relations 10000
Purchasing 24900
Sales 304500
Shipping 156400

11 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=25 Card=27 Bytes=621
)

1 0 SORT (GROUP BY) (Cost=25 Card=27 Bytes=621)
2 1 HASH JOIN (Cost=21 Card=106 Bytes=2438)
3 2 TABLE ACCESS (FULL) OF 'DEPARTMENTS' (Cost=10 Card=27
Bytes=432)

4 2 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=10 Card=107 B
ytes=749)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
42 consistent gets
0 physical reads
0 redo size
678 bytes sent via SQL*Net to client
498 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
11 rows processed

SQL>

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

请登录后发表评论 登录
全部评论
  • 博文量
    75
  • 访问量
    650230