ORACLE 使用AUTOTRACE功能

ORACLE9i在使用autotrace之前，需要作一些初始的设置，

 

1.用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; -- 授权所有用户

 

2.要在数据库中建立一个角色plustrace,用sys用户运行脚本plustrce.sql来创建这个角色，这个脚本在目录（UNIX:$ORACLE_HOME/sqlplus/admin, Windows:%ORACLE_HOME%/sqlplus/admin）中；

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

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

SQL>grant plustrace to public;

 

* plustrace 角色只是具有以下权限 :

grant select on v_$sesstat to plustrace;

grant select on v_$statname to plustrace;

grant select on v_$mystat to plustrace;

grant plustrace to dba with admin option;

 

plustrce.sql 脚本如下

create role plustrace;
grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant select on v_$session to plustrace;
grant plustrace to dba with admin option;

 

4.经过以上步骤的设置，就可以在sql*plus中使用autotrace了，使用非常简单，只要在执行语句之前，执行这样一条命令：

SQL>set autotrace on

即可。

 

* autotrace 功能 只能在 SQL*PLUS 里使用

 

补充：

1. ORA-01039: 视图基本对象的权限不足 的解决方法

ORA-01039: 视图基本对象的权限不足
Current SQL statement for this session:
EXPLAIN PLAN SET STATEMENT_ID='PLUS561' FOR select table_name from user_tables

I think this is because the user doesn't have access to base tables for USER_TABLES view which belongs to SYS user.

DBA role will do it, "SELECT ANY TABLE" (in 8i & 9i) , and "SELECT ANY DICTIONARY"(in 9i & 10g) system privileges should also do it. Try one of the following 3 ways and run your autotrace again:-

1. 8i & 9i:-
grant select any table to USER123;

2. 9i and 10g:-
grant select any dictionary to USER123;

3. in 8i and 9i, you can also grant accees to the base tables explicitly ( or create a role to hold the grants ) :
grant select on OBJ$ to USER123;
grant select on USER$ to USER123;
grant select on SEG$ to USER123;
grant select on TS$ to USER123;
grant select on TAB$ to USER123;

 

2. 在 SQPPLUS 中 得到更新成功或者插入成功的记录数

SQL> set feedback 1 ；

 

3. 在 SQPPLUS 中 得到语句总执行的时间

SQL> set timing on;

4 . 使用 sys 进行 autotrace 的话 统计信息 statistic 都会为 0

SQL> select count(*) from dba_objects;

 COUNT(*)

----------

     31820

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   SORT (AGGREGATE)

   2    1     VIEW OF 'DBA_OBJECTS'

   3    2       UNION-ALL

   4    3         FILTER

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

   6    5             NESTED LOOPS

   7    6               TABLE ACCESS (FULL) OF 'USER$'

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

   9    4           TABLE ACCESS (BY INDEX ROWID) OF 'IND$'

 10    9             INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)

 11    3         NESTED LOOPS

 12   11           TABLE ACCESS (FULL) OF 'USER$'

 13   11           INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)

 

Statistics

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

          0 recursive calls

          0 db block gets

          0 consistent gets

          0 physical reads

          0 redo size

          0 bytes sent via SQL*Net to client

          0 bytes received via SQL*Net from client

          0 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

          1 rows processed

 

5.AUTOTRACE 的几个常用选项

(1). set autotrace on explain; -- 只显示执行计划

SQL> set autotrace on explain;

SQL> select count(*) from dba_objects;

 

 COUNT(*)

----------

     31820

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   SORT (AGGREGATE)

   2    1     VIEW OF 'DBA_OBJECTS'

   3    2       UNION-ALL

   4    3         FILTER

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

   6    5             NESTED LOOPS

   7    6               TABLE ACCESS (FULL) OF 'USER$'

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

   9    4           TABLE ACCESS (BY INDEX ROWID) OF 'IND$'

 10    9             INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)

 11    3         NESTED LOOPS

 12   11           TABLE ACCESS (FULL) OF 'USER$'

 13   11           INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)

 

(2). set autotrace on statistics; -- 只显示统计信息

SQL> set autotrace on statistics;

SQL> select count(*) from dba_objects;

 

 COUNT(*)

----------

     31820

 

Statistics

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

          0 recursive calls

          0 db block gets

      25754 consistent gets

          0 physical reads

          0 redo size

        383 bytes sent via SQL*Net to client

        503 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

          1 rows processed

 

(3). set autotrace traceonly; -- 同 set autotrace on 只是不显示查询输出

SQL> set autotrace traceonly;

SQL> select count(*) from dba_objects;

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   SORT (AGGREGATE)

   2    1     VIEW OF 'DBA_OBJECTS'

   3    2       UNION-ALL

   4    3         FILTER

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

   6    5             NESTED LOOPS

   7    6               TABLE ACCESS (FULL) OF 'USER$'

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

   9    4           TABLE ACCESS (BY INDEX ROWID) OF 'IND$'

 10    9             INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)

 11    3         NESTED LOOPS

 12   11           TABLE ACCESS (FULL) OF 'USER$'

 13   11           INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)

 

Statistics

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

          0 recursive calls

          0 db block gets

      25754 consistent gets

          0 physical reads

          0 redo size

        383 bytes sent via SQL*Net to client

        503 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

          1 rows processed

 

(4). set autotrace traceonly explain ; -- 比较实用的选项，只显示执行计划，但是与 set autotrace on explain; 相比 不会执行语句 ，对于仅仅查看大表的 Explain Plan 非常管用。

SQL> set autotrace traceonly explain ;

SQL> select * from dba_objects;

已用时间 : 00: 00: 00.00

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   VIEW OF 'DBA_OBJECTS'

   2    1     UNION-ALL

   3    2       FILTER

   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'

   5    4           NESTED LOOPS

   6    5             TABLE ACCESS (FULL) OF 'USER$'

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

   8    3         TABLE ACCESS (BY INDEX ROWID) OF 'IND$'

   9    8           INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)

 10    2       TABLE ACCESS (BY INDEX ROWID) OF 'LINK$'

 11   10         NESTED LOOPS

 12   11           TABLE ACCESS (FULL) OF 'USER$'

 13   11           INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)

 

6. Statistics 参数的含义

recursive calls = basically sql performed on behalf of your sql.

So, if we had to PARSE the query for example, we might have

had to run some other queries to get data dictionary info.

that would be recursive calls.

db block gets = blocks gotten in "current" mode. That is,

blocks gotten as they exist right now. You'll see these

for full table scans (segment headers areread in current mode)

and modification statements (we modify the block as it

exists "right now")

consistent gets = blocks gotten in consistent read mode.

This is the mode we

read blocks in with a select for example. Also,

when you do a searched UPDATE/DELETE , we read the blocks in

consistent read mode and then get the block in current mode

to actually do the modification. A select for update will do

this as well.

physical reads = self explanatory, physical IO

redo size = self explanatory -- amount of redo generated

sorts (memory)/(disk) -- sorts done.

 

参考信息：

http://search.itpub.net/search.php?s=0661ddee4eb60b681e4f6156eb9c6418&action=showresults&searchid=392705&sortby=lastpost&sortorder=descending