首页 > 数据库 > Oracle >

启用AUTOTRACE功能

2012-02-24

--=======================-- 启用AUTOTRACE 功能--======================= AUTOTRACE是一个SQL*Plus工具,用于跟踪SQL的执行计划,收集执行时所耗用资源的统计信息,是SQL优化工具之一,下面给出启用AUTOTRACE ...

--=======================
-- 启用AUTOTRACE 功能
--=======================

AUTOTRACE是一个SQL*Plus工具,用于跟踪SQL的执行计划,收集执行时所耗用资源的统计信息,是SQL优化工具之一,下面给出启用
AUTOTRACE 功能步骤。

一、创建基础表
运行$ORACLE_HOME/rdbms/admin/utlxplan脚本来创建plan_table

scott@ORCL> conn system/redhat --使用system帐户登陆
Connected.
system@ORCL> start $ORACLE_HOME/rdbms/admin/utlxplan --执行utlxplan脚本

Table created.

system@ORCL> create public synonym plan_table for plan_table; --为表plan_table创建公共同义词

Synonym created.

system@ORCL> grant all on plan_table to public; --将同义词表plan_table授予给所有用户

Grant succeeded.

二、创建角色
运行$ORACLE_HOME/sqlplus/admin/plustrce.sql脚本

system@ORCL> conn / as sysdba --使用sysdba帐户登陆
onnected.

sys@ORCL> start $ORACLE_HOME/sqlplus/admin/plustrce.sql --执行创建角色的脚本
sys@ORCL>
sys@ORCL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

sys@ORCL> create role plustrace;

Role created.

sys@ORCL>
sys@ORCL> grant select on v_$sesstat to plustrace;

Grant succeeded.

sys@ORCL> grant select on v_$statname to plustrace;

Grant succeeded.

sys@ORCL> grant select on v_$mystat to plustrace;

Grant succeeded.

sys@ORCL> grant plustrace to dba with admin option;

Grant succeeded.

三、角色的授予
在创建角色后,DBA首先被授予了该角色,且可以将角色授予其它组和用户。可以手工把plustrace授予给public,
则该数据库内所有的用户都将拥有plustrace角色的权限。也可以单独授予给某个组和用户

授予给所有用户
sys@ORCL> grant plustrace to public;
授予给单独用户
sys@ORCL> grant plustrace to scott;

完成上述设置之后即可使用autotrace 功能

四、AUTOTRACE的几个选项
在sql提示符下输入set autot后将会给出设置autotrace的提示,如下

scott@ORCL> set autot
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

set autotrace off :缺省值,将不生成autotrace 报告
set autotrace on :包含执行计划和统计信息
set autotrace traceonly :等同于set autotrace on,但不显示查询输出的结果
set autotrace on explain :只显示优化器执行路径报告
set autotrace on statistics :只显示执行统计信息

scott@ORCL> set autotrace on;
scott@ORCL> select * from emp where ename='SCOTT';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 37 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ENAME"='SCOTT')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
824 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

摘自 一沙弥的世界
相关文章
最新文章
热点推荐