Oracle StatSpack

一、Statspack概述
Oracle Statspack工具从Oracle 8.1.6开始引入，通过Statspack可以很容易地收集数据库性能数据，并通过这些数据进而分析确定Oracle数据库的瓶颈所在。从Oracle 10g开始，Oracle推出了新的工具——AWR自动负载信息库。比较起来，Statspack需要由用户自行进行安装调度，并且其收集的信息较为有限，AWR收集的信息大大增加，从10g开始，AWR被设计用来取代Statspack工具，并且在Oracle 11g中被不断增强。但是不管Statspack还是AWR，其本质都是相同的，就是通过持续不断地收集数据库或者系统的性能信息来提炼有意义的数据库报告，以作为性能分析的基础。
二、安装Statspack
在数据库中Statspack初始并未安装，但是其安装配置脚本随软件提供，要使用这个工具首先需要通过一系列的脚本来安装和初始化这个工具。
Statspack的脚本位于$ORACLE_HOME/rdbms/admin目录下，是一组以sp开头的文件。
1、安装前数据库参数的修改
为了能够顺利安装和运行Statspack，可能需要设置以下两个系统参数。
（1）job_queue_processes
为了能够建立自动任务，执行数据收集，该参数需要大于0.
对于Oracle 10g，该参数默认值是0；
对于Oracle 11g，该参数默认值是1000.
可以使用下面命令修改：
SQL> alter system set job_queue_processes=6 scope=both;
（2）timed_statistics
该参数为True时，Oracle将实时收集操作系统的计时信息，这些信息可被用来显示时间等统计信息、优化数据库和SQL语句。
使用Statspack收集统计信息时建议将该值设置为True，在Oracle 9i之前这个参数的缺省值为False，从Oracle 9i开始这个参数缺省值为True。将timed_statistics设置为True会对数据库带来一定的性能影响，但是这个性能影响与收集信息带来的好处相比是微不足道的。
该参数使收集的时间信息存储在V$SESSTATS和V$SYSSTATS动态性能视图中。
SQL> alter system set timed_statistics=true;
2、安装Statspack
安装需要使用sys用户以sysdba身份登录到数据库安装。
安装过程通常很简单，运行spcreate.sql脚本即可。但是为了更好的规划，还有一些准备工作需要提前做好。
（1）、建立独立表空间
由于Statspack需要一定的存储空间，最好建立独立的表空间。这里创建的表空间不能太小，如果太小创建对象会失败，至少需要建立100M表空间。如果打算长期使用，可以建立稍大的表空间。如：假设每隔半小时采样一次，连续采样一周，那么Statspack产生的数据量是很大的。这里我创建一个500MB的测试表空间。
C:\Users\Administrator>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期一 12月 9 13:56:21 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select file_name from dba_data_files;
FILE_NAME
-----------------------------------------------------------------
E:\APP\ADMINISTRATOR\ORADATA\TEST\SYSTEM01.DBF
E:\APP\ADMINISTRATOR\ORADATA\TEST\SYSAUX01.DBF
E:\APP\ADMINISTRATOR\ORADATA\TEST\UNDOTBS01.DBF
E:\APP\ADMINISTRATOR\ORADATA\TEST\USERS01.DBF
SQL> create tablespace perfstat datafile 'E:\APP\ADMINISTRATOR\ORADATA\TEST\perfstat.dbf' size 500M;
表空间已创建。
（2）、执行安装脚本
E:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期一 12月 9 14:11:10 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @spcreate
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
输入 perfstat_password 的值:  system
system
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
------------------------------ --------- ----------------------------
PERFSTAT                       PERMANENT
SYSAUX                         PERMANENT *
USERS                          PERMANENT
Pressing will result in STATSPACK's recommended default
tablespace (identified by *) being used.
输入 default_tablespace 的值:  perfstat
Using tablespace PERFSTAT 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 will result in the database's default Temporary
tablespace (identified by *) being used.
输入 temporary_tablespace 的值:
Using tablespace TEMP as PERFSTAT temporary tablespace.
按照提示输入缺省表空间和临时表空间的名称即可。
在这一步，如果出现错误，可以运行spdrop.sql脚本来删除这些对象，然后重新运行spcreate.sql来创建对象。
3、测试Statspack
运行statspack.snap可以产生系统快照，运行两次，然后执行spreport.sql就可以生成一个基于两个时间点的报告，如果一切正常，说明安装成功。
SQL> execute statspack.snap
PL/SQL 过程已成功完成。
SQL> execute statspack.snap
PL/SQL 过程已成功完成。
SQL> @spreport.sql
......
三、规划自动任务
Statspack正确安装以后，就可以设置定时任务收集数据了。可以使用spauto.sql来定义自动任务。先来看看spauto.sql的关键内容：
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
这个job任务定义了收集数据的时间间隔，一天有24个小时，即1440分钟，那么：
l   1/24HH，即每小时一次；
l   1/48MI，即每半小时一次；
l   1/144MI，即每10分钟一次；
l   1/288MI，即每5分钟一次。
可以修改spauto.sql来更改执行间隔，然后执行spauto，这样就建立了数据收集计划。可以查看spauto.lis来获得输出信息：
SQL> @spauto.sql
PL/SQL 过程已成功完成。
Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:

     JOBNO
----------
         2
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
1000
Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

       JOB NEXT_DATE     NEXT_SEC
---------- -------------- ----------------
         2 09-12月-13     16:00:00
关于采样间隔，通常建议以1小时为时间间隔，对于有特殊需要的环境，可以设置更短的，如半小时作为采样间隔，但不推荐更短。因为Statspack的执行本身需要消耗资源，对于繁忙的生产系统，太短的采样对系统的性能会产生较大的影响（甚至会使Statspack的执行出现在采样数据中）。如果是实时的问题诊断，需要采样分析，可以根据具体需要手工执行采样。
四、生成分析报告
调用spreport.sql可以生成分析报告：
SQL> @spreport
Current Instance
~~~~~~~~~~~~~~~~
   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
2130218216 TEST                1 tsid
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
2130218216        1 TEST         tsid         ZZLK-3
Using 2130218216 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 without
specifying a number lists all completed snapshots.
Listing all Completed Snapshots
                                                       Snap
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
tsid         TEST                 1 09 12月 2013 14:32     5
                                   
                                  2 09 12月 2013 14:32     5
                                  
                                  3 09 12月 2013 15:00     5
                                  
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:  1
Begin Snapshot Id specified: 1
输入 end_snap 的值:  3
End   Snapshot Id specified: 3
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_3.  To use this name,
press to continue, otherwise enter an alternative.
输入 report_name 的值:  e:\rep1209.txt
Using the report name e:\rep1209.txt
在生成报告之前，Oracle会查询列出所有采样的Snap ID，可以按照先后顺序任选两个Snap ID生成一个报告，begin_snap就是指其实Snap号，end_snap指终止Snap号；生成报表的名称可以自己定义，也可以采用缺省的；如不指定路径，则会在当前目录下生成一个报告。
如果系统在采样过程中间停过机，那么就可能收到错误信息：
declare
*
第 1 行出现错误:
ORA-20200: The instance was shutdown between snapshots 1 and 11
ORA-06512: 在 line 48
一个Statspack的报告不能跨越一次停机，但是之前或之后的连续区间，收集的信息依然有效，可以选择之前或之后的采样生成report。
五、移除定时任务
移除一个定时任务，可以如下操作：
SQL> select job,log_user,last_date,next_date,interval from user_jobs;
       JOB LOG_USER   LAST_DATE           NEXT_DATE           INTERVAL
---------- ---------- ------------------- -------------------
         3 PERFSTAT        2013/12/09 15:00:00 2013/12/09 16:00:00 trunc(SYSDATE+1/24,'HH')
SQL> execute dbms_job.remove('3')
PL/SQL 过程已成功完成。
当完成了一个采样报告，就应该及时移除这个job任务，在生产环境中，遗漏一个无人照顾的job是非常危险的，如果Statspack连续运行一个星期或更长时间，采样的数据量可能会是非常惊人的。
六、删除历史数据
删除stats$snapshot数据表中的相应数据，其他表中的数据会相应地级联删除：
SQL> select max(snap_id) from stats$snapshot;
MAX(SNAP_ID)
------------
          11
SQL> delete from stats$snapshot where snap_id<=11;
已删除4行。
可以更改snap_id的范围以保留需要的数据。
sppurge.sql脚本可以用于删除一定范围的采样数据，运行该脚本，输入一个采样范围，会自动删除该区间之间的采样数据，但是需要注意的是，删除会产生大量的归档日志，在生产环境上要特别关注：
SQL> @sppurge
Database Instance currently connected to
========================================
                                Instance
   DB Id    DB Name    Inst Num Name
----------- ---------- -------- ----------
2130218216 TEST              1 tsid
Snapshots for this database instance
====================================
                               Base-  Snap
Snap Id   Snapshot Started    line? Level Host            Comment
-------- --------------------- ----- ----- ---------------
       1  09 12月 2013 14:32:34           5 ZZLK-3
       
       2  09 12月 2013 14:32:57           5 ZZLK-3
      
       3  09 12月 2013 15:00:00           5 ZZLK-3
       
      11  09 12月 2013 15:18:20           5 ZZLK-3
       
      12  09 12月 2013 15:48:31           5 ZZLK-3
       
      13  09 12月 2013 15:48:34           5 ZZLK-3
       
      14  09 12月 2013 15:48:36           5 ZZLK-3
       
      15  09 12月 2013 15:48:37           5 ZZLK-3
       
      16  09 12月 2013 15:48:38           5 ZZLK-3
       
Warning
~~~~~~~
sppurge.sql deletes all snapshots ranging between the lower and
upper bound Snapshot Id's specified, for the database instance
you are connected to.  Snapshots identified as Baseline snapshots
which lie within the snapshot range will not be purged.
It is NOT possible to rollback changes once the purge begins.
You may wish to export this data before continuing.
Specify the Lo Snap Id and Hi Snap Id range to purge
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 losnapid 的值:1
Using 1 for lower bound.
输入 hisnapid 的值:  16
Using 16 for upper bound.
Deleting snapshots 1 - 16.
Number of Snapshots purged: 9
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Purge of specified Snapshot range complete.
Oracle还提供了系统脚本用于Truncate这些统计信息表，这个脚本名字是sptrunc.sql，脚本里面的表就是和Statspack相关的所有系统表，如果采样了大量的数据，直接Delete是非常缓慢的，可以考虑使用该脚本截断所有表。
七、调整Statspack的收集门限
Statspack有两种类型的收集选项：级别（level）和门限（threshold），其中级别用于控制收集数据的类型，门限用于设置收集的数据的阈值。
Statspack共有以下5类快照级别（Level），默认级别是Level 5.
l   Level 0：包含一般性能的统计数据，如等待事件、系统事件、系统统计、回滚段统计、行缓存、SGA、会话、锁、缓冲池统计等。
l   Level 5：较之前级别增加了SQL语句信息，除了包括Level 0的所有内容，还包括SQL语句的收集，收集结果记录在stats$sql_summary中。
l   Level 6：从Oracle 9i开始引入，除包含Level 5的全部信息外，增加了SQL执行计划部分信息。
l   Level 7：从Oracle 9i开始引入，除包含低级别的所有信息之外，还增加段统计信息（Segment Level Statistics）收集。
l   Level 10：增加Parent/Children Latch信息统计。这一级别包括低级别收集得所有信息，并且还会将附加的Parent/Children Latch信息记入stats$latch_children字典表中。使用这个级别收集信息时可能会耗费大量的CPU时间，所以使用时需要慎重。
可以通过Statspack包修改缺省的级别设置：
SQL> execute statspack.snap(i_snap_level=>0,i_modify_parameter=>’true’);
通过这样的设置，以后的收集级别都将是0级。如果只是想本次改变收集级别，可以忽略i_modify_parameter参数。
SQL> execute statspack.snap(i_snap_level=>10);
而快照门限只应用于stats$sql_summary表中获取的SQL语句。因为每一个快照都会收集很多数据，每一行都代表获取快照时数据库中的一个SQL语句，所以stats$sql_summary很快就会成为Statspack中最大的表。门限信息存储在stats$statspack_parameter表中，各种门限说明如下：
l   executions_th：这是SQL语句执行的数量（默认100）。
l   disk_reads_th：这是SQL语句执行的磁盘读入数量（默认1000）。
l   parse_calls_th：这是SQL语句执行的解析调用的数量（默认1000）。
l   buffer_gets_th：这是SQL语句执行的缓冲区获取的数量（默认10000）。
任何一个门限值超过以上参数就会产生一条记录。
通过调用statspack.modify_statspack_parameter函数就可以改变门限的默认值。例如：
SQL>execute statspack.modify_statspack_parameter(i_buffer_gets_th=>100000,i_disk_reads_th=>100000);