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的几个常用选项
set autotrace off ---------------- 不生成autotrace 报告，这是缺省模式
set autotrace on explain ------ autotrace只显示优化器执行路径报告
set autotrace on statistics -- 只显示执行统计信息
set autotrace on ----------------- 包含执行计划和统计信息
set autotrace traceonly ------ 同set autotrace on，但是不显示查询输出
(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非常管用。
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2015-5-12 16:47:21 |
赞(
操作
