oracle trace 工具,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