statspack是oracle 9i时代的产物，对于监控与分析数据库性能有着跨里程碑的意义，是awr的前身。在oracle 10g后awr取代了statspac
statspack是oracle 9i时代的产物，对于监控与分析数据库性能有着跨里程碑的意义，是awr的前身。在oracle 10g后awr取代了statspack。尽管如此，awr异常或者需要调试包license的情况下statpack依旧是不错的选择。然而在rac环境中，statspack并不支持，，需要单独的进行配置以及使用job来进行管理。本文描述的则是通过在rac环境下创建service，以及job来达到各节点同时产生snapshot的效果。
一、演示环境
suse11a:oracle:orcl101 > cat /etc/issue
welcome to suse linux enterprise server 11 sp3  (x86_64) - kernel \r (\l).
suse11a:oracle:orcl101 > sqlplus -v
sql*plus: release 10.2.0.5.0 - production
suse11a:oracle:orcl101 > $ora_crs_home/bin/crsctl query crs activeversion
crs active version on the cluster is [10.2.0.5.0]
二、配置statspack
1)首先添加service
$  srvctl add service -d orcl10 -s statspack_suse11a_srvc -r orcl101 
$  srvctl add service -d orcl10 -s statspack_suse11b_srvc -r orcl102
$  srvctl start service -d orcl10 -s statspack_suse11a_srvc
$  srvctl start service -d orcl10 -s statspack_suse11b_srvc
$  srvctl status service -d orcl10
service orcl10_srv is running on instance(s) orcl102, orcl101
service statspack_suse11a_srvc is running on instance(s) orcl101
service statspack_suse11b_srvc is running on instance(s) orcl102
$  srvctl config service -d orcl10     
orcl10_srv pref: orcl102 orcl101 avail:
statspack_suse11a_srvc pref: orcl101 avail:
statspack_suse11b_srvc pref: orcl102 avail:
$ lsnrctl status
  .........
service statspack_suse11a_srvc has 1 instance(s).
  instance orcl101, status ready, has 2 handler(s) for this service...
service statspack_suse11b_srvc has 1 instance(s).
  instance orcl102, status ready, has 1 handler(s) for this service...
  .............
2)配置statspack
conn / as sysdba
create tablespace perfstat datafile '+asm_data' size 500m autoextend on;
@?/rdbms/admin/spcreate
grant execute on dbms_lock to perfstat;
grant create job to perfstat;
grant execute on sys.dbms_scheduler to perfstat;
grant execute on sys.dbms_isched to perfstat;
3)创建job class
begin
  dbms_scheduler.create_job_class (
      job_class_name  => 'statspack_suse11a_class',
      service          => 'statspack_suse11a_srvc');
  dbms_scheduler.create_job_class (
      job_class_name  => 'statspack_suse11b_class',
      service          => 'statspack_suse11b_srvc');
end;
/
sql> select job_class_name, service from dba_scheduler_job_classes;
job_class_name                service
------------------------------ -------------------------------------------------
default_job_class
auto_tasks_job_class
statspack_suse11a_class        statspack_suse11a_srvc
statspack_suse11b_class        statspack_suse11b_srvc
grant execute on sys.statspack_suse11a_class to perfstat;
grant execute on sys.statspack_suse11b_class to perfstat;
4)创建用于同步节点的过程
conn perfstat/perfstat
create or replace procedure db_proc_rac_statspack
as
  w_status      number (38);
  w_handle      varchar2 (60);
  w_snap_level  number;
begin
  w_snap_level := 7;
  sys.dbms_lock.allocate_unique (lockname    => 'synchronize statspack',
                                  lockhandle  => w_handle);
  w_status :=
      sys.dbms_lock.request (lockhandle          => w_handle,
                            lockmode            => dbms_lock.x_mode,
                            timeout            => 300, -- seconds, default is dbms_lock.maxwait
                            release_on_commit  => false -- which is the default
                                                        );
  if (w_status = 0)
  then
      dbms_output.put_line (
            to_char (sysdate, 'dd hh24:mi:ss')
        || ': acquired lock, running statspack');
      statspack.snap (w_snap_level);
      dbms_output.put_line (
        to_char (sysdate, 'dd hh24:mi:ss') || ': snapshot completed');