sql优化工具 oracle,【Oracle】SQL优化的重要工具 AUTOTRACE

【AUTOTRACE】SQL优化的重要工具--AUTOTRACE

提到SQL优化，不能不提AUTOTRACE的强大功能。使用起来非常便捷，不过在是使用之前，需要做一些配置的工作。简要的描述一下这个过程，供没有使用过的朋友参考。

1.使用sys用户执行plustrce脚本

sys@ora10g> @?/sqlplus/admin/plustrce

sys@ora10g> drop role plustrace;

drop role plustrace

*

ERROR at line 1:

ORA-01919: role 'PLUSTRACE' does not exist

sys@ora10g> create role plustrace;

Role created.

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

Grant succeeded.

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

Grant succeeded.

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

Grant succeeded.

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

Grant succeeded.

sys@ora10g> set echo off

2.将plustrace授权给public用户，以便保证所有用户都有权使用autotrace功能

sys@ora10g> grant plustrace to public;

Grant succeeded.

3.连接到具体的用户中，这里是sec用户

sys@ora10g> conn sec/sec

Connected.

4.使用“set autotrace”查看autotrace的命令提示

sec@ora10g> set autotrace

Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

5.这里演示一种设置方法

sec@ora10g> set autotrace traceonly

sec@ora10g> select * from cat;

20 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3442307057

--------------------------------------------------------------------------------

| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |        |   646 | 47804 |    33   (4)| 00:00:01 |

|*  1 |  FILTER  &nbs