oracletrace工具,oracle性能分析工具  autotrace 的应用

ORACLE9i在使用autotrace之前&＃xff0c;需要作一些初始的设置&＃xff0c;

1.用sys用户运行脚本ultxplan.sql

建立这个表的脚本是&＃xff1a;(UNIX:$ORACLE_HOME/rdbms/admin,

Windows:%ORACLE_HOME%\rdbms\admin)ultxplan.sql。

SQL> connect sys/sys&＃64;colm2 as sysdba;

SQL>

&＃64;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来创建这个角色&＃xff0c;这个脚本在目录(UNIX:$ORACLE_HOME/sqlplus/admin,

Windows:%ORACLE_HOME%\sqlplus\admin)中&＃xff1b;

SQL>

&＃64;C:\oracle\ora92\sqlplus\admin\plustrce.sql;

3.然后将角色plustrace授予需要autotrace的用户&＃xff1b;

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.经过以上步骤的设置&＃xff0c;就可以在sql*plus中使用autotrace了&＃xff0c;使用非常简单&＃xff0c;只要在执行语句之前&＃xff0c;执行这样一条命令&＃xff1a;

SQL>set autotrace on

即可。

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

补充&＃xff1a;

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

ORA-01039: 视图基本对象的权限不足

Current SQL statement for this session:

EXPLAIN PLAN SET STATEMENT_ID&＃61;&＃39;PLUS561&＃39; FOR select table_name from

user_tables

I think this is because the user doesn&＃39;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&＃xff1b;

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&＃61;CHOOSE

1 0 SORT (AGGREGATE)

2 1 VIEW OF &＃39;DBA_OBJECTS&＃39;

3 2 UNION-ALL

4 3 FILTER

5 4 TABLE ACCESS (BY INDEX ROWID) OF &＃39;OBJ$&＃39;

6 5 NESTED LOOPS

7 6 TABLE ACCESS (FULL) OF &＃39;USER$&＃39;

8 6 INDEX (RANGE SCAN) OF &＃39;I_OBJ2&＃39; (UNIQUE)

9 4 TABLE ACCESS (BY INDEX ROWID) OF &＃39;IND$&＃39;

10 9 INDEX (UNIQUE SCAN) OF &＃39;I_IND1&＃39; (UNIQUE)

11 3 NESTED LOOPS

12 11 TABLE ACCESS (FULL) OF &＃39;USER$&＃39;

13 11 INDEX (RANGE SCAN) OF &＃39;I_LINK1&＃39; (NON-UNIQUE)

Statistics

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

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size