设置SQL *PLUS 的AUTOTRACE

sqlplus的autotrace是非常有用的一个优化工具。可以显示sql的执行计划和统计信息。但是默认普通用户是不能使用的

SQL> conn scott/tiger

Connected.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

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

DEPT                           TABLE

EMP                            TABLE

BONUS                          TABLE

SALGRADE                       TABLE

MYTEST_TIMESTAMP               TABLE

T1                             TABLE

T3                             TABLE

7 rows selected.

SQL> set autotrace on;

SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled

SP2-0611: Error enabling STATISTICS report

SQL> 

使用前需要设置一下，方法很多，以下是我采用的方法:

以sys用户登录

SQL> conn / as sysdba

Connected.

SQL> show user;

USER is "SYS"

SQL> 

运行utlxplan.sql

SQL> host ls -l $ORACLE_HOME/rdbms/admin|grep plan

-rw-r--r-- 1 oracle oinstall    7917 Feb  4  2008 catplan.sql

-rw-r--r-- 1 oracle oinstall    3267 Jun  6  2004 utlxplan.sql

SQL> @?/rdbms/admin/utlxplan.sql

Table created.

SQL> 

SQL> create public synonym PLAN_TABLE for PLAN_TABLE;

create public synonym PLAN_TABLE for PLAN_TABLE

                      *

ERROR at line 1:

ORA-00955: name is already used by an existing object

SQL> select OWNER,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from dba_synonyms where table_name like upper('%plan_table%');

OWNER      SYNONYM_NAME         TABLE_OWNER          TABLE_NAME

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

PUBLIC     SQL_PLAN_TABLE_TYPE  SYS                  SQL_PLAN_TABLE_TYPE

PUBLIC     PLAN_TABLE           SYS                  PLAN_TABLE$

SQL> 

创建 PLUSTRACE role。

SQL> host ls -l $ORACLE_HOME/sqlplus/admin|grep strce

-rw-r--r-- 1 oracle oinstall  813 Mar 26  2006 plustrce.sql

SQL> @?/sqlplus/admin/plustrce.sql

SQL> 

SQL> drop role plustrace;

drop role plustrace

          *

ERROR at line 1:

ORA-01919: role 'PLUSTRACE' does not exist

SQL> create role plustrace;

Role created.

SQL> 

SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL> 

SQL> set echo off

SQL> 

SQL> conn sys/111111 as sysoper

Connected.

SQL> show user;

USER is "PUBLIC"

SQL> 

SQL> select * from USER_ROLE_PRIVS;

no rows selected

SQL> conn sys/111111 as sysdba

Connected.

SQL> show user;

USER is "SYS"

SQL> grant PLUSTRACE to public;

Grant succeeded.

（也可以把PLUSTRACE权限赋给具体的某个用户）

SQL> 

SQL> conn sys/111111 as sysoper

Connected.

SQL> 

SQL> show user;

USER is "PUBLIC"

SQL> select * from USER_ROLE_PRIVS;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_

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

PUBLIC                         PLUSTRACE                      NO  YES NO

SQL> 

现在就可以正常使用啦

[oracle@mydb1 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Dec 20 16:33:17 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn scott/tiger

Connected.

SQL> shou user;

SP2-0042: unknown command "shou user" - rest of line ignored.

SQL> show user;

USER is "SCOTT"

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

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

DEPT                           TABLE

EMP                            TABLE

BONUS                          TABLE

SALGRADE                       TABLE

MYTEST_TIMESTAMP               TABLE

T1                             TABLE

T3                             TABLE

7 rows selected.

SQL> set autotrace

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

SQL> set autotrace on;

SQL> select count(*) from emp;

  COUNT(*)

----------

        14

Execution Plan

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

Plan hash value: 2937609675

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

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

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

|   0 | SELECT STATEMENT |        |     1 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE  |        |     1 |            |          |

|   2 |   INDEX FULL SCAN| PK_EMP |    14 |     1   (0)| 00:00:01 |

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

Statistics

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

          0  recursive calls

          0  db block gets

          1  consistent gets

          0  physical reads

          0  redo size

        515  bytes sent via SQL*Net to client

        492  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> set autotrace traceonly;

SQL> select count(*) from emp;

Execution Plan

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

Plan hash value: 2937609675

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

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

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

|   0 | SELECT STATEMENT |        |     1 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE  |        |     1 |            |          |

|   2 |   INDEX FULL SCAN| PK_EMP |    14 |     1   (0)| 00:00:01 |

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

Statistics

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

          0  recursive calls

          0  db block gets

          1  consistent gets

          0  physical reads

          0  redo size

        515  bytes sent via SQL*Net to client

        492  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL>