如何在SQL*PLUS中使用Autotrace？(看执行计划)

何在SQL*PLUS中使用Autotrace？

如何在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