如何在SQL*PLUS中使用Autotrace？

sqlplus system or sys as sysdba
SQL> @?/rdbms/admin/utlxplan.sql
SQL> create public synonym plan_table for plan_table;
SQL> grant all on plan_table to public;
SQL> conn / as sysdba
SQL> @?/sqlplus/admin/plustrce.sql
SQL> grant plustrace to public;

SET AUTOTRACE OFF | ON EXPLAIN | ON STATISTICS | ON | TRACEONLY | TRACE EXPLAIN
SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出
下面看個例子吧(注意普通user中不能查看含有系統表或視圖的執行計划)

Microsoft Windows 2000 [版本 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.

C:>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 4月 8 08:33:22 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn / as sysdba
已連線.
SQL> set autot on;
SP2-0613: 無法驗證 PLAN_TABLE 格式或實體
SP2-0611: 啟動 EXPLAIN 報表時發生錯誤
SQL> conn system/password
已連線.
SQL> @?/rdbms/admin/utlxplan.sql

已建立表格.

SQL> create public synonym plan_table for plan_table;

已建立同義字.

SQL> grant all on plan_table to public;

順利授權.

SQL> conn / as sysdba
已連線.
SQL> @?/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
          *
 ERROR 在行 1:
ORA-01919: 角色 'PLUSTRACE' 不存在

SQL> create role plustrace;

已建立角色.

SQL>
SQL> grant select on v_$sesstat to plustrace;

順利授權.

SQL> grant select on v_$statname to plustrace;

順利授權.

SQL> grant select on v_$session to plustrace;

順利授權.

SQL> grant plustrace to dba with admin option;

順利授權.

SQL>
SQL> set echo off
SQL> grant plustrace to public;

順利授權.

SQL> set autot on
SQL> set autot off
SQL> conn / as sysdba
已連線.
SQL> set autot traceonly
SQL> select table_name from user_tables;

已選取 324 個資料列.

執行計畫
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     NESTED LOOPS (OUTER)
   3    2       NESTED LOOPS (OUTER)
   4    3         NESTED LOOPS (OUTER)
   5    4           NESTED LOOPS (OUTER)
   6    5             NESTED LOOPS
   7    6               TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
   8    7                 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
   9    6               TABLE ACCESS (CLUSTER) OF 'TAB$'
  10    9                 INDEX (UNIQUE SCAN) OF 'I_OBJ#' (NON-UNIQUE)
  11    5             TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
  12   11               INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
  13    4           INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
  14    3         TABLE ACCESS (CLUSTER) OF 'USER$'
  15   14           INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
  16    2       TABLE ACCESS (CLUSTER) OF 'SEG$'
  17   16         INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE)
  18    1     TABLE ACCESS (CLUSTER) OF 'TS$'
  19   18       INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)

統計值
----------------------------------------------------------
          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)
        324  rows processed

SQL> select * from scott.emp;

已選取 14 個資料列.

執行計畫
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMP'

統計值
----------------------------------------------------------
          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)
         14  rows processed

SQL>

SQL> conn scott/tiger
已連線.
SQL> set autot traceonly
SQL> select table_name from user_tables;

執行計畫
----------------------------------------------------------
ERROR:
ORA-01039: 權限不足, 無法存取構成視觀表的基底物件

SP2-0612: 產生 AUTOTRACE EXPLAIN 報表時發生錯誤

統計值
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         20  consistent gets
          0  physical reads
          0  redo size
        574  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)
          5  rows processed

SQL> select *from emp;

已選取 14 個資料列.

執行計畫
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMP'

統計值
----------------------------------------------------------
        193  recursive calls
          0  db block gets
         27  consistent gets
          4  physical reads
          0  redo size
       1306  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>