设置SQL PLUS AutoTrace

Initial Setup

1、第一步

This is what I like to do to get AUTOTRACE working:

cd [ORACLE_HOME]/rdbms/admin www.2cto.com

log into SQL*Plus as SYSTEM

run @utlxplan

run CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;

run GRANT ALL ON PLAN_TABLE TO PUBLIC;

2、第二步

The next step is creating and granting the PLUSTRACE role:

cd [ORACLE_HOME]/sqlplus/admin

log into SQL*Plus as SYS or AS SYSDBA

run @plustrce www.2cto.com

run GRANT PLUSTRACE TO PUBLIC;

Again, you can replace PUBLIC in the GRANT command with some user if you want.

在sqlplus 中运行脚本也可以使用代表$ORACLE_HOME

注意、可以将plblic 设置为某个用户在第二步中一定要使用SYS用户

关于Autotrace几个常用选项的说明:

SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式

SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告

SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息

SET AUTOTRACE ON ----------------- 包含执行计划和统计信息

SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出