AUTOTRACE是一个SQL*Plus工具,用于跟踪SQL的执行计划,收集执行时所耗用资源的统计信息。系统账户本身具有AUTOTRACE，其他账户需要通过手动赋予
一. 用系统账户登录（DBA）
1 SQL> set autotrace traceonly 2 SQL> select * from hr.jobs; 3 4 19 rows selected. 5 6 7 Execution Plan 8 ---------------------------------------------------------- 9 Plan hash value: 944056911 10 11 -------------------------------------------------------------------------- 12 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 13 -------------------------------------------------------------------------- 14 | 0 | SELECT STATEMENT | | 19 | 627 | 3 (0)| 00:00:01 | 15 | 1 | TABLE ACCESS FULL| JOBS | 19 | 627 | 3 (0)| 00:00:01 | 16 -------------------------------------------------------------------------- 17 18 Statistics 19 ---------------------------------------------------------- 20 0 recursive calls 21 0 db block gets 22 9 consistent gets 23 0 physical reads 24 0 redo size 25 1452 bytes sent via SQL*Net to client 26 396 bytes received via SQL*Net from client 27 3 SQL*Net roundtrips to/from client 28 0 sorts (memory) 29 0 sorts (disk) 30 19 rows processed 31 32 SQL>
二.授予其他用户AUTOTRACE 功能
2.1 创建基础表 运行$ORACLE_HOME/rdbms/admin/utlxplan脚本来创建plan_table
1 SQL> conn /as sysdba --用系统账户登录 2 Connected. 3 SQL> start $ORACLE_HOME/rdbms/admin/utlxplan --执行utlxplan脚本 4 5 Table created. 6 7 SQL> create public synonym plan_table for plan_table;--为表plan_table创建公共同义词 8 9 Synonym created. 10 11 --如果需要限制Autotrace权限，可以把对public的授权改为对特定user的授权。 12 SQL> grant all on plan_table to public;--将同义词表plan_table授予给所有用户 13 14 Grant succeeded. 15 16 SQL>
2.2 创建角色 运行$ORACLE_HOME/sqlplus/admin/plustrce.sql脚本
1 SQL> start $ORACLE_HOME/sqlplus/admin/plustrce.sql --执行创建角色的脚本 2 SQL> 3 --以下是脚本执行过程：首先 删除PLUSTRACE角色，重建PLUSTRACE角色，赋予权限 4 SQL> drop role plustrace; 5 drop role plustrace 6 * 7 ERROR at line 1: 8 ORA-01919: role 'PLUSTRACE' does not exist 9 10 11 SQL> create role plustrace; 12 13 Role created. 14 15 SQL> 16 SQL> grant select on v_$sesstat to plustrace; 17 18 Grant succeeded. 19 20 SQL> grant select on v_$statname to plustrace; 21 22 Grant succeeded. 23 24 SQL> grant select on v_$mystat to plustrace; 25 26 Grant succeeded. 27 28 SQL> grant plustrace to dba with admin option; 29 30 Grant succeeded. 31 32 SQL> 33 SQL> set echo off 34 SQL>
2.3 角色的授予
在创建角色后，DBA首先被授予了该角色，且可以将角色授予其它组和用户。可以手工把plustrace授予给public,则该数据库内所有的用户都将拥有plustrace角色的权限。也可以单独授予给某个组和用户。
1 --授予给单独用户 2 SQL> grant plustrace to hr; 3 4 Grant succeeded. 5 6 --授予给所有用户 7 SQL> grant plustrace to public; 8 9 Grant succeeded. 10 11 SQL>
现在所有用户即可使用autotrace 功能
三、AUTOTRACE的几个选项
1 --在sql提示符下输入set autot后将会给出设置autotrace的提示，如下： 2 SQL> set autot 3 Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] 4 5 set autotrace off :缺省值，将不生成autotrace 报告 6 set autotrace on :包含执行计划和统计信息 7 set autotrace traceonly :等同于set autotrace on，但不显示查询输出的结果 8 set autotrace on explain :只显示优化器执行路径报告 9 set autotrace on statistics :只显示执行统计信息
四.hr用户示例
1 SQL> conn hr/hr123 2 Connected. 3 SQL> set autotrace traceonly 4 SQL> select * from jobs; 5 6 19 rows selected. 7 8 9 Execution Plan 10 ---------------------------------------------------------- 11 Plan hash value: 944056911 12 13 -------------------------------------------------------------------------- 14 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 15 -------------------------------------------------------------------------- 16 | 0 | SELECT STATEMENT | | 19 | 627 | 3 (0)| 00:00:01 | 17 | 1 | TABLE ACCESS FULL| JOBS | 19 | 627 | 3 (0)| 00:00:01 | 18 -------------------------------------------------------------------------- 19 20 21 Statistics 22 ---------------------------------------------------------- 23 1 recursive calls 24 0 db block gets 25 9 consistent gets 26 0 physical reads 27 0 redo size 28 1452 bytes sent via SQL*Net to client 29 396 bytes received via SQL*Net from client 30 3 SQL*Net roundtrips to/from client 31 0 sorts (memory) 32 0 sorts (disk) 33 19 rows processed 34 35 SQL>