[Sql*Plus] Set up Explain Plan and Autotrace
之所以把Explain Plan 和 Autotrace的设置放到一起来说，是因为这2者都依赖于同一张表PLAN_TABLE，因此都需要首先创建PLAN_TABLE.
1. Create Table PLAN_TABLE
有时候貌似不需要手动去创建这张表，PLAN_TABLE在数据库安装过程中已经被创建好了。我们可以首先测试下PLAN_TABLE是否已经存在，
scott@ORCL> desc plan_table;Name
Null? Type ------------------------------------------------------------------------------------------------STATEMENT_ID
VARCHAR2(30)
PLAN_ID
NUMBER
TIMESTAMP DATEREMARKS
VARCHAR2(4000)
OPERATIONVARCHAR2(30)
OPTIONS
VARCHAR2(255)
OBJECT_NODE
VARCHAR2(128)
OBJECT_OWNER
VARCHAR2(30)
OBJECT_NAME
VARCHAR2(30)
OBJECT_ALIAS
VARCHAR2(65)
OBJECT_INSTANCE
NUMBER(38)
OBJECT_TYPE
VARCHAR2(30)
OPTIMIZER
VARCHAR2(255)
SEARCH_COLUMNS
NUMBER
ID
NUMBER(38)
PARENT_ID
NUMBER(38)
DEPTHNUMBER(38)
POSITIONNUMBER(38)
COST
NUMBER(38)
CARDINALITY
NUMBER(38)
BYTES
NUMBER(38)
OTHER_TAG
VARCHAR2(255)
PARTITION_START
VARCHAR2(255)
PARTITION_STOP
VARCHAR2(255)
PARTITION_ID
NUMBER(38)
OTHER
LONG
OTHER_XML
CLOBDISTRIBUTION
VARCHAR2(30)
CPU_COST
NUMBER(38)
IO_COST
NUMBER(38)
TEMP_SPACE
NUMBER(38)
ACCESS_PREDICATES
VARCHAR2(4000)
FILTER_PREDICATES
VARCHAR2(4000)
PROJECTION
VARCHAR2(4000)
TIMENUMBER(38)
QBLOCK_NAME
VARCHAR2(30)
scott@ORCL>
可以看到表plan_table是存在的，如果这张表不存在，可以通过如下方法来创建，
(1) 首先以sysdba身份login to SQL*PLUS 然后运行如下命令
@<oracle_home>\RDBMS\ADMIN\utlxplan.sql
文件utlxplan.sql的内容其实就是创建table PLAN_TABLE的script,
create table PLAN_TABLE (
statement_id varchar2(30),
plan_id number,
timestamp date,
remarks varchar2(4000),
operation varchar2(30),options varchar2(255),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_alias varchar2(65),
object_instance numeric,object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric, parent_id numeric,depth numeric,
position numeric,
cost numeric, cardinality numeric, bytes numeric,other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,other long,
distribution varchar2(30),
cpu_cost numeric, io_cost numeric, temp_space numeric,access_predicates varchar2(4000),
filter_predicates varchar2(4000),
projection varchar2(4000),
time numeric,
qblock_name varchar2(30),
other_xml clob);
(2) 使得Table PLAN_TABLE全局可见
一般可以创建一个synonym关联到PLAN_TABLE，然后grant SELECT ON PLAN_TABLE to PUBLIC
这里需要注意的是，其实表PLAN_TABLE在数据库中存在的形式可能并不跟文件utlplan.sql给出的一样，下面是我本机的Oracle （11g R1) 的查询结果，
sys@ORCL> select owner, object_id, object_type, temporary from all_objects where object_name='PLAN_TABLE';
OWNER OBJECT_ID OBJECT_TYPE T
------------------------------ ---------- ------------------- -PUBLIC 4816 SYNONYM Nsys@ORCL>
可以看出PLAN_TABLE并不是一个表，而只是一个synonym, 而且它指向的表名字是PLAN_TABLE$, 而且是个GTT.
SQL> select owner, synonym_name, table_owner, table_name from all_synonyms where synonym_name = 'PLAN_TABLE';
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
-------------------- ------------------------------ ------------------------------ ------------------------------PUBLIC PLAN_TABLE SYS PLAN_TABLE$
sys@ORCL> select table_name, temporary, partitioned from user_tables where table_name='PLAN_TABLE$'
2 /
TABLE_NAME T PAR
------------------------------ - ---PLAN_TABLE$ Y NOsys@ORCL>
其实PLAN_TABLE是ＧＴＴ是很有好处的，因为这样一个Session对这个表的操作不会影响到其他session.
2. Setup Explain Plan
其实在PLAN_TABLE被设置好之后，EXPLAIN PLAN 不需要其他设置就可以用了，因为在Oracle 9.2之后可以直接通过DBMS_XPLAN来查看执行计划的信息。在9.2之前还是需要utlxplp.sql 和 utlxpls来查看执行计划结果的， 因为这两个文件包含大量的script来帮助获得执行计划信息。
在9.2之后这两个文件的内容都很简单，
(1) @<oracle_home>\RDBMS\ADMIN\utlxplp.sql (Utility eXplain PLan Parallel)
其实这个文件(9.2之后）中只有如下一条SQL语句，也是调用DBMS_XPLAN.DISPLAY
select * from table(dbms_xplan.display());
(2) @<oracle_home>\RDBMS\ADMIN\utlxpls.sql (Utility eXplain PLan Serial)
其实这个文件(9.2之后）也是只有如下一条SQL语句
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
【
Explain Plan 的用法如下，
explain plan
[set statement_id = ‘text’]
[into [owner.]table_name]
for statement;
】
3. Setup Autotrace
在设置autotrace之前，我们测试下。以Scott 登陆, 尝试下set autotrace on命令，
SQL> conn scott/scottConnected.
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS reportSQL>
很显然，我们遇到了问题，从错误信息可以看出，当前schema (scott)没有被授予PLUSTRACE 这个角色。
下面来看看如何设置autotrace。
在PLAN_TABLE设置好之后(参见Create Table PLAN_TABLE), 剩下的事情就是创建一个role, 并使任何需要set autotrace on的user都被授予该role.
具体步骤如下，
1. Log in to SQL*PLUS as SYSDBA
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jan 7 00:26:32 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
idle> conn sys/sys as sysdba
Connected.
sys@ORCL>
2. Run SQL>@<ORACLE_HOME>\SQLPLUS\admin\plustrace.sql
这个文件的内容其实很简单，就是创建一个role – plustrace, 然后赋予访问一些动态性能视图的权限给该role
set echo on
drop role plustrace;
create role plustrace;
grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant plustrace to dba with admin option;
set echo off
sys@ORCL> @"E:\app\fangyu\product\11.1.0\db_1\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.
sys@ORCL>
sys@ORCL> set echo off
sys@ORCL>
3. 让该role全局可见
sys@ORCL> grant plustrace to public;
Grant succeeded.
测试下，看看Scott现在可以不以用autotrace了
sys@ORCL> conn scott/scott
Connected.
scott@ORCL> set autotrace on
scott@ORCL>
可以看出现在Scott已经可以用autotrace了！
--------------------------------------
Regards,
FangwenYu
