sqlplus的autotrace開關

官方文檔https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_eight.htm#i1037226

set autotrace off表示關閉，是預設的(set autot off)

set autotrace on表示顯示結果、執行計劃、統計信息(set autot on)

set autotrace on explain表示顯示結果、執行計劃(set autot on exp)

set autotrace on statistics表示顯示結果、統計信息(set autot on stat)

set autotrace traceonly表示顯示執行計劃、統計信息(set autuot trace)

set autotrace traceonly explain表示顯示執行計劃(set autot trace exp)

set autotrace traceonly statistics表示顯示統計信息(set autot trace stat)

統計信息可以看到資源消耗量，如物理讀、邏輯讀、redo量、排序量等

autotrace簡寫為autot

explain簡寫為exp

statistics簡寫為stat

traceonly簡寫為trace

用戶要有創建PLAN_TABLE表和有PLUSTRACE角色，才行

比如給scott用戶賦許可權，用scott用戶執行@$ORACLE_HOME/rdbms/admin/utlxplan.sql 來創建PLAN_TABLE表

然後用sys用戶執行@$ORACLE_HOME/sqlplus/admin/plustrce.sql 來創建PLUSTRACE角色

然後GRANT PLUSTRACE TO scott即可

具體如下:

16:27:36 SCOTT@edw> set autotrace on
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
16:27:51 SCOTT@edw> @$ORACLE_HOME/rdbms/admin/utlxplan.sql   

Table created.

Elapsed: 00:00:00.11
16:59:54 SCOTT@edw> conn / as sysdba;
Connected.
17:00:22 SYS@edw> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
17:00:32 SYS@edw> 
17:00:32 SYS@edw> drop role plustrace;
drop role plustrace
          *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist


Elapsed: 00:00:00.01
17:00:32 SYS@edw> create role plustrace;

Role created.

Elapsed: 00:00:00.01
17:00:32 SYS@edw> 
17:00:32 SYS@edw> grant select on v_$sesstat to plustrace;

Grant succeeded.

Elapsed: 00:00:00.01
17:00:32 SYS@edw> grant select on v_$statname to plustrace;

Grant succeeded.

Elapsed: 00:00:00.01
17:00:32 SYS@edw> grant select on v_$mystat to plustrace;

Grant succeeded.

Elapsed: 00:00:00.00
17:00:32 SYS@edw> grant plustrace to dba with admin option;

Grant succeeded.

Elapsed: 00:00:00.01
17:00:32 SYS@edw> 
17:00:32 SYS@edw> set echo off

Elapsed: 00:00:00.01
17:01:27 SYS@edw> GRANT PLUSTRACE TO scott;    

Grant succeeded.

Elapsed: 00:00:00.00
17:01:47 SYS@edw>