ORACLE 使用AUTOTRACE功能

1.用sys使用者執行指令碼ultxplan.sql
建立這個表的指令碼是：（UNIX:$ORACLE_HOME/rdbms/admin, Windows:%ORACLE_HOME%rdbmsadmin）ultxplan.sql。
SQL> connect as sysdba;
SQL> @C:oracleora92rdbmsadminutlxplan.sql;
SQL> create public synonym plan_table for plan_table; --建立同義詞
SQL> grant all on plan_table to public;--授權所有使用者[@more@]

1.用sys使用者執行指令碼ultxplan.sql
建立這個表的指令碼是：（UNIX:$ORACLE_HOME/rdbms/admin, Windows:%ORACLE_HOME%rdbmsadmin）ultxplan.sql。
SQL> connect as sysdba;
SQL> @C:oracleora92rdbmsadminutlxplan.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%sqlplusadmin）中；
SQL> @C:oracleora92sqlplusadminplustrce.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.