oracle数据库级别优化分析工具之autotrace

安装

用sys用户运行脚本ultxplan.sql

建立这个表的脚本是：（UNIX:$ORACLE_HOME/rdbms/admin, Windows:%ORACLE_HOME%/rdbms/admin）ultxplan.sql。

SQL> connect sys/sys@colm2 as sysdba;

SQL> @C:/oracle/ora92/rdbms/admin/utlxplan.sql;

SQL> create public synonym plan_table for plan_table;--建立同义词

SQL> grant all on plan_table to public;--授权所有用户

要在数据库中建立一个角色plustrace,用sys用户运行脚本plustrce.sql来创建这个角色，这个脚本在目

录（UNIX:$ORACLE_HOME/sqlplus/admin, Windows:%ORACLE_HOME%/sqlplus/admin）中；

SQL> @C:/oracle/ora92/sqlplus/admin/plustrce.sql;

然后将角色plustrace授予需要autotrace的用户；

SQL>grant plustrace to public;

经过以上步骤的设置，就可以在sql*plus中使用autotrace了，autotrace功能只能在SQL*PLUS里使用

AUTOTRACE Statistics常用列解释

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

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

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

redo size: DML生成的redo的大小

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

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

eg:

SYS@db>set autotrace

Usage: SET AUTOT[RACE] [EXP[LAIN]] [STAT[ISTICS]]

SYS@db>set timing on

SYS@db>set autot trace exp stat

SYS@db>select * from tab;

3809 rows selected.

Elapsed: 00:00:00.06

Execution Plan

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

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

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

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

| 0 | SELECT STATEMENT | | 1059 | 46596 | 216 (2)|

| 1 | NESTED LOOPS OUTER | | 1059 | 46596 | 216 (2)|

| 2 | TABLE ACCESS FULL | OBJ$ | 1059 | 39183 | 158 (2)|

| 3 | TABLE ACCESS CLUSTER| TAB$ | 1 | 7 | 1 (0)|

| 4 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)|

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

Note

-----

- 'PLAN_TABLE' is old version

Statistics

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

0 recursive calls

0 db block gets

9077 consistent gets

0 physical reads

0 redo size

133502 bytes sent via SQL*Net to client

3252 bytes received via SQL*Net from client

255 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3809 rows processed