【優化】ORACLE set autotrace in SQL*Plus

set autotrace off
set autotrace on
set autotrace traceonly

set autotrace on explain
set autotrace on statistics
set autotrace on explain statistics

set autotrace traceonly explain
set autotrace traceonly statistics
set autotrace traceonly explain statistics

set autotrace off explain
set autotrace off statistics
set autotrace off explain statistics
set autotrace off
set autotrace on
set autotrace traceonly

set autotrace on explain
set autotrace on statistics
set autotrace on explain statistics

set autotrace traceonly explain
set autotrace traceonly statistics
set autotrace traceonly explain statistics

set autotrace off explain
set autotrace off statistics
set autotrace off explain statistics

設定autotrace允許顯示DML語句的一些統計資訊和/或查詢計劃。

set autotrace on: 為語句顯示統計資訊和執行計劃。

set autotrace on explain: 只顯示執行計劃。

set autotrace on statistics: 只顯示統計資訊。

set autotrace traceonly: 顯示執行計劃和統計資訊，但是列印查詢結果。

set autotrace off: 關閉autotrace

如果用statistics選項開啟autotrace，那麼將顯示下面的統計資訊：

recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
前置條件

autotrace的執行計劃特性需要建立一個plan_table表，可以用指令碼

$ORACLE_HOME/rdbms/admin/utlxplan.sql 建立

統計特性需要使用者具有v_$sesstat, v_$statname 和 v_$session表上的查詢許可權。

Oracle安裝時附帶的$ORACLE_HOME/sqlplus/admin/plustrce.sql 會安裝plustrace角色，plustrace角色被授予了這些查詢許可權。如果plustrace角色被授予使用者，那麼他就可以具有開啟autotrace的許可權。或者你也可以把plustrace角色授予public。