收集时间信息

TIMED_STATISTICS = true

Statspack的安装

set ORACLE_HOME=c:\oracle\ora92

@$ORACLE_HOME\rdbms\admin\spcreate.sql

输入perfstat用户的密码：根据password  profile，需要数字、字母和特殊字符

输入perfstat用户的默认表空间：USERS   至少需要140M空间

输入perfstat用户的临时表空间：TEMP

会在C:\Documents and Settings\Administrator下产生spcusr.lis记录创建用户的过程

产生spctab.lis记录创建对象的过程

生成快照，收集某个时间点的统计信息

conn perfstat/dating_123

execute STATSPACK.snap

隔一段时间,再次执行

execute STATSPACK.snap

会在C:\Documents and Settings\Administrator下产生spcpkg.lis记录以上脚本的执行过程

自动收集统计信息

@c:\oracle\ora92\rdbms\admin\spauto.sql

会在C:\Documents and Settings\Administrator下产生spauto.lis记录以上脚本的执行过程

产生报告

@c:\oracle\ora92\rdbms\admin\spreport.sql

enter value for begin_snap: 1   回车  根据实际情况指定开始和结束snap号

enter value for end_snap:2  回车

enter value for report_name:sp_1_2  回车

查找sp_1_2.LST报告

STATSPACK

The STATSPACK package has been available with Oracle Database from Oracle8.1.6. When initially installed roughly 80 MBs of the users default tablespace is used. This may grow later with the tables storing snapshot information.

Installation of the STATSPACK Package

Installing the STATSPACK utility creates the Perfstat user, who owns all PL/SQL code and database objects created (including the STATSPACK tables, the constraints and the STATSPACK package). During the installation you will be prompted for the Perfstat user's default and temporary tablespaces.

Collecting Statistics

Take a snapshot of performance data log in to SQL*Plus as the Perfstat user, and execute the STATSPACK.snap procedure. This stores the current values for the performance statistics in the STATSPACK tables, which can be used as a baseline snapshot for comparison with another snapshot taken at a later time.

仅供自己使用，如有错误，请多指教！