利用statspack收集数据库信息

        statspack用于9i，10g、11g也可以使用statspack，但是必须自行安装,本文参考《循序渐进ORACLE：数据库管理、优化与备份恢复》

一、安装statspack

statspack安装脚本位于$ORACLE_HOME/rdbms/admin/sp*
spcreate.sql用于安装statspack
spauto.sql用于自动生成快照
spreport.sql用于查看报告
spdrop.sql用于删除statspack，如果spcreate.sql安装失败,可以执行spdrop.sql后重新执行spcreate.sql安装statspack。

statspack需要存储信息到表空间中，所以使用前必须新建一个表空间用于存储statspack生成的信息，表空间建议500m。

SYS@orcl>create tablespace statsinfo datafile  '/u01/app/oracle/oradata/orcl/statsinfo01.dbf' size 500m;

SYS@orcl> @?/rdbms/admin/spcreate.sql      --sqlplus中?代表$ORACLE_HOME
SYS@orcl> Rem
SYS@orcl> Rem $Header: spcreate.sql 16-apr-2002.11:22:55 vbarrier Exp $
SYS@orcl> Rem
SYS@orcl> Rem spcreate.sql
SYS@orcl> Rem
SYS@orcl> Rem Copyright (c) 1999, 2002, Oracle Corporation.  All rights reserved.
SYS@orcl> Rem
SYS@orcl> Rem    NAME
SYS@orcl> Rem         spcreate.sql - Statistics Create
SYS@orcl> Rem
SYS@orcl> Rem    DESCRIPTION
SYS@orcl> Rem         SQL*PLUS command file which creates the STATSPACK user,
SYS@orcl> Rem         tables and package for the performance diagnostic tool STATSPACK
SYS@orcl> Rem
SYS@orcl> Rem    NOTES
SYS@orcl> Rem         Note the script connects INTERNAL and so must be run from
SYS@orcl> Rem         an account which is able to connect internal.
SYS@orcl> Rem
SYS@orcl> Rem    MODIFIED   (MM/DD/YY)
SYS@orcl> Rem    cdialeri    02/16/00 - 1191805
SYS@orcl> Rem    cdialeri    12/06/99 - 1103031
SYS@orcl> Rem    cdialeri    08/13/99 - Created
SYS@orcl> Rem
SYS@orcl>
SYS@orcl> --
SYS@orcl> --  Create PERFSTAT user and required privileges
SYS@orcl> @@spcusr
SYS@orcl> Rem
SYS@orcl> Rem $Header: spcusr.sql 31-may-2005.14:34:29 cdgreen Exp $
SYS@orcl> Rem
SYS@orcl> Rem spcusr.sql
SYS@orcl> Rem
SYS@orcl> Rem Copyright (c) 1999, 2005, Oracle. All rights reserved.
SYS@orcl> Rem
SYS@orcl> Rem    NAME
SYS@orcl> Rem         spcusr.sql
SYS@orcl> Rem
SYS@orcl> Rem    DESCRIPTION
SYS@orcl> Rem         SQL*Plus command file to create user which will contain the
SYS@orcl> Rem         STATSPACK database objects.
SYS@orcl> Rem
SYS@orcl> Rem    NOTES
SYS@orcl> Rem         Must be run from connected to SYS (or internal)
SYS@orcl> Rem
SYS@orcl> Rem    MODIFIED   (MM/DD/YY)
SYS@orcl> Rem    cdgreen     05/24/05 - 4246955
SYS@orcl> Rem    cdgreen     04/18/05 - 4228432
SYS@orcl> Rem    cdgreen     10/29/04 - 10gR2_sqlstats
SYS@orcl> Rem    cdgreen     08/12/04 - 10g R2
SYS@orcl> Rem    vbarrier    02/12/04 - 3412853
SYS@orcl> Rem    cdialeri    12/04/03 - 3290482
SYS@orcl> Rem    cdialeri    10/14/03 - 10g - streams - rvenkate
SYS@orcl> Rem    cdialeri    08/05/03 - 10g F3
SYS@orcl> Rem    vbarrier    02/25/03 - 10g RAC
SYS@orcl> Rem    cdialeri    11/15/02 - 10g F1
SYS@orcl> Rem    vbarrier    09/06/02 - SYSAUX and db default temp tbs
SYS@orcl> Rem    vbarrier    04/01/02 - 2290728
SYS@orcl> Rem    vbarrier    03/05/02 - Segment Statistics
SYS@orcl> Rem    cdialeri    02/07/02 - 2218573
SYS@orcl> Rem    cdialeri    11/30/01 - 9.2 - features 1
SYS@orcl> Rem    cdialeri    04/26/01 - 9.0
SYS@orcl> Rem    cdialeri    09/12/00 - sp_1404195
SYS@orcl> Rem    cdialeri    04/07/00 - 1261813
SYS@orcl> Rem    cdialeri    02/16/00 - 1191805
SYS@orcl> Rem    cdialeri    01/26/00 - 1169401
SYS@orcl> Rem    cdialeri    11/01/99 - 1059172
SYS@orcl> Rem    cdialeri    08/13/99 - Created
SYS@orcl> Rem
SYS@orcl>
SYS@orcl> set echo off verify off showmode off feedback off;

Choose the PERFSTAT user's password

-----------------------------------

Not specifying a password will result in the installation FAILING
Enter value for perfstat_password: oracle

oracle
--statspack会建立一个 PERFSTAT 用户，为 PERFSTAT用户设置密码

Choose the Default tablespace for the PERFSTAT user

---------------------------------------------------

Below is the list of online tablespaces in this database which can

store user data.  Specifying the SYSTEM tablespace for the user's

default tablespace will result in the installation FAILING, as

using SYSTEM for performance data is not supported.


Choose the PERFSTAT users's default tablespace.  This is the tablespace

in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------

EXAMPLE                        PERMANENT

STATSINFO                      PERMANENT

SYSAUX                         PERMANENT *

USERS                          PERMANENT

Pressing <return> will result in STATSPACK's recommended default

tablespace (identified by *) being used.

Enter value for default_tablespace: statsinfo

--选择需要的表空间，默认保存在SYSAUX，可以输入刚刚新建的表空间名字statsinfo


Using tablespace STATSINFO as PERFSTAT default tablespace.

Choose the Temporary tablespace for the PERFSTAT user

-----------------------------------------------------

Below is the list of online tablespaces in this database which can

store temporary data (e.g. for sort workareas).  Specifying the SYSTEM

tablespace for the user's temporary tablespace will result in the

installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS  DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------

TEMP                           TEMPORARY *

--选择临时表空间

Pressing <return> will result in the database's default Temporary

tablespace (identified by *) being used.

Enter value for temporary_tablespace:

Using tablespace TEMP as PERFSTAT temporary tablespace.

... Creating PERFSTAT user
... Installing required packages

 ... Creating views

... Granting privileges
NOTE:

SPCUSR complete. Please check spcusr.lis for any errors.

……省略……

Creating Package STATSPACK...

Package created.

No errors.
Creating Package Body STATSPACK...

Package body created.

No errors.

NOTE:

SPCPKG complete. Please check spcpkg.lis for any errors.

--安装完成后会在当前目录生成spcpkg.lis  spctab.lis  spcusr.lis，如果安装出现错误，可以检查上述文件找到错误的原因。

SYS@orcl>

spcreate.sql的内容如下：

--  Create PERFSTAT user and required privileges

@@spcusr

--

--  Build the tables and synonyms

connect perfstat/&&perfstat_password

@@spctab

--  Create the statistics Package

@@spcpkg

    可以看到其实是spcreate.sql调用spcusr.sql创建用户，spctab.sql创建表和同义词，spcpkg.sql创建statspack。

二、使用statspack生成快照。

用创建的perfstat用户登录

SYS@orcl>conn perfstat/oracle

PERFSTAT@orcl>show user

USER is "PERFSTAT"

PERFSTAT@orcl>

PERFSTAT@orcl>exec statspack.snap;

PL/SQL procedure successfully completed.

--必须生成两次快照进行比较，一般每隔30分钟收集一次statpack报告

PERFSTAT@orcl>exec statspack.snap;

PL/SQL procedure successfully completed.

     以上为手工生成快照，我们也可以使用spauto.sql脚本自动生成快照。

spauto.sql脚本内容如下：

variable jobno number;
variable instno number;
begin
  select instance_number into :instno from v$instance;
  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
  commit;
end;
/

    脚本会建立一个job任务，每隔1小时执行一次statspack.snap生成快照，可以修改成30分钟或者2个小时生成一个快照，修改trunc(sysdate+1/24,'HH')的内容即可。

    为了执行自动任务，执行数据收集，job_queue_processes参数必须大于0，此参数是动态参数，可以在系统级修改。

PERFSTAT@orcl>@?/rdbms/admin/spauto.sql
PERFSTAT@orcl>Rem
PERFSTAT@orcl>Rem $Header: spauto.sql 16-feb-00.16:49:37 cdialeri Exp $
PERFSTAT@orcl>Rem
PERFSTAT@orcl>Rem spauto.sql
PERFSTAT@orcl>Rem
PERFSTAT@orcl>Rem  Copyright (c) Oracle Corporation 1999, 2000. All Rights Reserved.
PERFSTAT@orcl>Rem
PERFSTAT@orcl>Rem    NAME
PERFSTAT@orcl>Rem        spauto.sql
PERFSTAT@orcl>Rem
PERFSTAT@orcl>Rem    DESCRIPTION
PERFSTAT@orcl>Rem        SQL*PLUS command file to automate the collection of STATPACK
PERFSTAT@orcl>Rem        statistics.
PERFSTAT@orcl>Rem
PERFSTAT@orcl>Rem    NOTES
PERFSTAT@orcl>Rem        Should be run as the STATSPACK owner, PERFSTAT.
PERFSTAT@orcl>Rem        Requires job_queue_processes init.ora parameter to be
PERFSTAT@orcl>Rem        set to a number >0 before automatic statistics gathering
PERFSTAT@orcl>Rem        will run.
PERFSTAT@orcl>Rem
PERFSTAT@orcl>Rem    MODIFIED   (MM/DD/YY)
PERFSTAT@orcl>Rem    cdialeri    02/16/00 - 1191805
PERFSTAT@orcl>Rem    cdialeri    12/06/99 - 1059172, 1103031
PERFSTAT@orcl>Rem    cdialeri    08/13/99 - Created
PERFSTAT@orcl>Rem
PERFSTAT@orcl>
PERFSTAT@orcl>
PERFSTAT@orcl>spool spauto.lis
PERFSTAT@orcl>
PERFSTAT@orcl>--
PERFSTAT@orcl>--  Schedule a snapshot to be run on this instance every hour, on the hour
PERFSTAT@orcl>
PERFSTAT@orcl>variable jobno number;
PERFSTAT@orcl>variable instno number;
PERFSTAT@orcl>begin
  2    select instance_number into :instno from v$instance;
  3    dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
  4    commit;
  5  end;
  6  /

PL/SQL procedure successfully completed.


PERFSTAT@orcl>prompt

PERFSTAT@orcl>prompt    Job number for automated statistics collection for this instance

Job number for automated statistics collection for this instance

PERFSTAT@orcl>prompt    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PERFSTAT@orcl>prompt    Note that this job number is needed when modifying or removing

Note that this job number is needed when modifying or removing

PERFSTAT@orcl>prompt    the job:

the job:

PERFSTAT@orcl>print jobno

     JOBNO

----------

        21


PERFSTAT@orcl>

PERFSTAT@orcl>prompt

PERFSTAT@orcl>prompt    Job queue process

Job queue process

PERFSTAT@orcl>prompt    ~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~

PERFSTAT@orcl>prompt    Below is the current setting of the job_queue_processes init.ora

Below is the current setting of the job_queue_processes init.ora

PERFSTAT@orcl>prompt    parameter - the value for this parameter must be greater

parameter - the value for this parameter must be greater

PERFSTAT@orcl>prompt    than 0 to use automatic statistics gathering:

than 0 to use automatic statistics gathering:

PERFSTAT@orcl>show parameter job_queue_processes

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

job_queue_processes                  integer     10

PERFSTAT@orcl>prompt

PERFSTAT@orcl>

PERFSTAT@orcl>prompt

PERFSTAT@orcl>prompt    Next scheduled run

Next scheduled run

PERFSTAT@orcl>prompt    ~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~

PERFSTAT@orcl>prompt    The next scheduled run for this job is:

The next scheduled run for this job is:


PERFSTAT@orcl>select job, next_date, next_sec

  2    from user_jobs

  3   where job = :jobno;

       JOB NEXT_DATE NEXT_SEC

---------- --------- --------------------------------

        21 17-OCT-12 23:00:00

 

1 row selected.

--这是查出job号

PERFSTAT@orcl>

PERFSTAT@orcl>spool off;
 

    停止自动收集需要先知道job号，然后移出作业，执行spauto.sql会显示出job号，可以看到刚生成的job号是21。

PERFSTAT@orcl>begin

PERFSTAT@orcl>dbms_job.remove(21);

PERFSTAT@orcl>end;

PL/SQL procedure successfully completed.


三、查看报告
SYS@orcl>@?/rdbms/admin/spreport.sql
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 1320402727        1 ORCL         orcl         oraedu

Using 1320402727 for database Id

Using          1 for instance number

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completed snapshots.

Listing all Completed Snapshots

                                                       Snap

Instance     DB Name        Snap Id   Snap Started    Level Comment

------------ ------------ --------- ----------------- ----- --------------------

orcl         ORCL                 1 17 Oct 2012 22:37     5

                                  2 17 Oct 2012 22:49     5

--此为快照的id

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 1     --选择开始快照的Snap Id  

Begin Snapshot Id specified: 1
 

Enter value for end_snap: 2         --选择结束快照的Snap Id

End   Snapshot Id specified: 2

 
Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is sp_1_2.  To use this name,

press <return> to continue, otherwise enter an alternative.

 

Enter value for report_name: /home/oracle/sp12.txt

--指定保存的位置和文件名，默认在当前目录生成报告

 

四、根据报告，找出性能问题

bash$vim /home/oracle/sp12.txt
 

五、查看完报告后要注意删除job任务和旧的快照信息

PERFSTAT@orcl>select job.log_user,priv_user,last_date,next_date,interval from user_jobs;                --查出job号

PERFSTAT@orcl>begin
PERFSTAT@orcl>dbms_job.remove(job号);
PERFSTAT@orcl>end;

--移除job

 
    可以使用sptrunc.sql脚本删除所有相关的信息表，sptrunc.sql内容大致如下：

truncate table STATS$TIME_MODEL_STATNAME;
truncate table STATS$SYS_TIME_MODEL;
truncate table STATS$SESS_TIME_MODEL;
truncate table STATS$STREAMS_CAPTURE;
truncate table STATS$STREAMS_APPLY_SUM;
truncate table STATS$PROPAGATION_SENDER;
truncate table STATS$PROPAGATION_RECEIVER;
truncate table STATS$BUFFERED_QUEUES;
truncate table STATS$BUFFERED_SUBSCRIBERS;
truncate table STATS$RULE_SET;
truncate table STATS$OSSTAT;
truncate table STATS$OSSTATNAME;
truncate table STATS$PROCESS_ROLLUP;
truncate table STATS$PROCESS_MEMORY_ROLLUP;
truncate table STATS$STREAMS_POOL_ADVICE;
truncate table STATS$SGA_TARGET_ADVICE;
truncate table STATS$MUTEX_SLEEP;
truncate table STATS$DYNAMIC_REMASTER_STATS;
delete from STATS$SNAPSHOT;
delete from STATS$DATABASE_INSTANCE;
commit;

    也可以删除指定的快照：
PERFSTAT@orcl>select max(snap_id) from stats$snapshot;
MAX(SNAP_ID)
------------
           20


PERFSTAT@orcl>delete from stats$snapshot where snap_id <= 15;

删除stats$snapshot数据表中的相应数据，其他表中的数据会相应级联删除。