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: 94405691110 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 Statistics19 ----------------------------------------------------------20 0 recursive calls21 0 db block gets22 9 consistent gets23 0 physical reads24 0 redo size25 1452 bytes sent via SQL*Net to client26 396 bytes received via SQL*Net from client27 3 SQL*Net roundtrips to/from client28 0 sorts (memory)29 0 sorts (disk)30 19 rows processed31 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 off34 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 autot3 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 Plan10 ----------------------------------------------------------11 Plan hash value: 94405691112 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 Statistics22 ----------------------------------------------------------23 1 recursive calls24 0 db block gets25 9 consistent gets26 0 physical reads27 0 redo size28 1452 bytes sent via SQL*Net to client29 396 bytes received via SQL*Net from client30 3 SQL*Net roundtrips to/from client31 0 sorts (memory)32 0 sorts (disk)33 19 rows processed34 35 SQL>