1、telnet到远程的数据库服务器
 telnet   远程数据库的IP

2、切换到数据库的用户
  su  -  oraprod

3、用超级用户进入数据库
$ sqlplus   "/as   sysdba"

4、查看参数，是否可以用job（可以用job进行自动的收集Statpack Report的数据）
SQL> show   parameter   job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
job_queue_processes                  integer     10

5、该参数可以收集操作系统的信息
SQL> show   parameter   timed_statistics

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
timed_statistics                     boolean     TRUE

6、为了创建表空间，表空间的数据文件放在哪比较好
SQL> select   file_name   from   dba_data_files;

FILE_NAME
------------------------------------------------------
/u01/oracle/proddata/system09.dbf
/u01/oracle/proddata/system08.dbf
/u01/oracle/proddata/system07.dbf
/u01/oracle/proddata/system06.dbf
/u01/oracle/proddata/system05.dbf
/u01/oracle/proddata/system04.dbf
/u01/oracle/proddata/system03.dbf
......

21 rows selected.

7、查看哪个目录的空间比较大，确定perfstat表空间的数据文件的位置
$ df   -h
 

8、创建表空间：
SQL> create   tablespace   perfstat
  2  datafile   '/u01/oracle/proddata/perfstat.dbf'
  3  size   2000M；

Tablespace created.

9、创建Statspack需要的脚本
SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password:（可以输入perfstat，便于记忆）

Pressing will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace:perfstat

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS  DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP                           TEMPORARY *

Pressing will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace:temp

10、测试是否成功，生成数据库的一个快照
SQL> execute   statspack.snap

PL/SQL procedure successfully completed.

11、再生成一个快照
SQL> execute   statspack.snap

PL/SQL procedure successfully completed.

12、取两个快照之间时间段的Statspack报告
SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql
Listing all Completed Snapshots

                                                       Snap
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- ------------------
DQOMS        DQOMS                1 12 Dec 2008 11:53     5
                                  2 12 Dec 2008 11:54     5

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:1
Begin Snapshot Id specified: 1

Enter value for end_snap:2

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2.  To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name:（输入要生产文件的名称：2000927Statspack）

13、用job进行定时产生Statspack的快照，便于收集数据（默认为1小时产生一个快照）
SQL> @$ORACLE_HOME/rdbms/admin/spauto.sql

PL/SQL procedure successfully completed.

Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:

     JOBNO
----------
        21

Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:

NAME_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
TYPE
-----------
VALUE_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
job_queue_processes
integer
10

Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

       JOB NEXT_DATE    NEXT_SEC
---------- ------------ ----------------
        21 12-DEC-08    12:00:00

11、当最好生成需要的Statspack之后，记住移除任务，查看任务：
SQL> select   job, log_user, priv_user, last_date,next_date, interval from user_jobs;

12、移除生成Statspack快照的任务：
SQL> execute   dbms_job.remove('21')

PL/SQL procedure successfully completed

完毕！

3.纪录数据库信息

conn perfstat/password
exec statspack.snap

4.察看纪录信息，生成报告

conn perfstat/password
@$ORACLE_HOME/rdbms/admin/spreport.sql

5.察看sql信息，生成报告

conn perfstat/password
@$ORACLE_HOME/rdbms/admin/sprepsql.sql

6.自动纪录信息
conn perfstat/password
@$ORACLE_HOME/rdbms/admin/spauto.sql