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'可以改变大小写。