官方文檔https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_eight.htm#i1037226set autotrace off表示關閉，是預設的(set autot off)set autotrace on表示顯示結果、執行計劃、統... ...
官方文檔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 [email protected]> set autotrace on SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled SP2-0611: Error enabling STATISTICS report 16:27:51 [email protected]> @$ORACLE_HOME/rdbms/admin/utlxplan.sql Table created. Elapsed: 00:00:00.11 16:59:54 [email protected]> conn / as sysdba; Connected. 17:00:22 [email protected]> @$ORACLE_HOME/sqlplus/admin/plustrce.sql 17:00:32 [email protected]> 17:00:32 [email protected]> 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 [email protected]> create role plustrace; Role created. Elapsed: 00:00:00.01 17:00:32 [email protected]> 17:00:32 [email protected]> grant select on v_$sesstat to plustrace; Grant succeeded. Elapsed: 00:00:00.01 17:00:32 [email protected]> grant select on v_$statname to plustrace; Grant succeeded. Elapsed: 00:00:00.01 17:00:32 [email protected]> grant select on v_$mystat to plustrace; Grant succeeded. Elapsed: 00:00:00.00 17:00:32 [email protected]> grant plustrace to dba with admin option; Grant succeeded. Elapsed: 00:00:00.01 17:00:32 [email protected]> 17:00:32 [email protected]> set echo off Elapsed: 00:00:00.01 17:01:27 [email protected]> GRANT PLUSTRACE TO scott; Grant succeeded. Elapsed: 00:00:00.00 17:01:47 [email protected]>
