Explain plan 和 Statspack

查看查询计划，安装Statspack。都是最常用的东西。
记录一下，方便自己以后查找。

[oracle@database ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Apr 28 11:02:37 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;

Synonym created.

SQL> GRANT ALL ON PLAN_TABLE TO PUBLIC;

Grant succeeded.

SQL> @/u1/oracle/product/9.2/sqlplus/admin/plustrce
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> GRANT PLUSTRACE TO PUBLIC;

Grant succeeded.

SQL>

/*

SET AUTOT OFF
SET AUTOT ON EXPLAIN
SET AUTOT ON STATISTICS
SET AUTOT ON
SET AUTOT TRACEONLY

*/


---------
Statspack
---------

SQL> CONNECT / AS SYSDBA
Connected.
SQL>

SQL> DEFINE DEFAULT_TABLESPACE='TOOLS'
SQL> DEFINE TEMPORARY_TABLESPACE='TEMP'
SQL> DEFINE PERFSTAT_PASSWORD='MY_PERFSTAT_PASSWORD'
SQL> @/u1/oracle/product/9.2/rdbms/admin/spcreate
.....

.....
Statspack装好之后，执行snap过程
SQL> CONNECT perfstat/my_perfstat_password
SQL> EXECUTE statspack.snap;

在Statspack收集到数据之后才能查看统计结果。
SQL> @/u1/oracle/product/9.2/rdbms/admin/spreport

PS:Ultra edit里面'Alt+F5'和'Ctrl+F5'可以改变大小写。