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‘);
w_status := sys.DBMS_LOCK.release (lockhandle => w_handle);
ELSE
DBMS_OUTPUT.put_line (
TO_CHAR (SYSDATE, ‘dd hh24:mi:ss‘)
|| CASE w_status
WHEN 1 THEN ‘: Lock wait timed out‘
WHEN 2 THEN ‘: deadlock detected‘
WHEN 3 THEN ‘: parameter error‘
WHEN 4 THEN ‘: already holding lock‘
WHEN 5 THEN ‘: illegal lock handle‘
ELSE ‘: unknown error‘
END);
END IF;
END;
/
5) 创建用于job调度的过程
BEGIN
DBMS_SCHEDULER.create_program (program_name => ‘PROC_RAC_STATSPACK‘,
program_type => ‘STORED_PROCEDURE‘,
program_action => ‘db_proc_rac_statspack‘,
enabled => TRUE);
END;
/
6) 清除同名job(如果存在)
BEGIN
DBMS_SCHEDULER.drop_job (‘ORCL10_PERFSTAT_COLLECT_N1‘,force=>true);
DBMS_SCHEDULER.drop_job (‘ORCL10_PERFSTAT_COLLECT_N2‘,force=>true);
DBMS_SCHEDULER.drop_job (‘ORCL10_PERFSTAT_PURGE_N1‘,force=>true);
DBMS_SCHEDULER.drop_job (‘ORCL10_PERFSTAT_PURGE_N2‘,force=>true);
END;
/
7) 创建产生snapshot以及清除历史snapshot的job --Author ：Leshami --Blog ：http://blog.csdn.net/leshami
BEGIN
DBMS_SCHEDULER.create_job (
job_name => ‘ORCL_PERFSTAT_COLLECT_N1‘,
program_name => ‘PROC_RAC_STATSPACK‘,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=hourly; INTERVAL=1; BYMINUTE=30‘,
job_class => ‘statspack_suse11a_class‘,
comments => ‘This job will run on suse11a‘,
ENABLED => TRUE);
DBMS_SCHEDULER.create_job (
job_name => ‘ORCL_PERFSTAT_PURGE_N1‘,
job_type => ‘PLSQL_BLOCK‘,
job_action => ‘begin STATSPACK.PURGE(31); end;‘,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=DAILY; BYHOUR=23; BYMINUTE=30‘,
job_class => ‘statspack_suse11a_class‘,
enabled => TRUE);
END;
/
--- create the job for Node 2:
BEGIN
DBMS_SCHEDULER.create_job (
job_name => ‘ORCL_PERFSTAT_COLLECT_N2‘,
program_name => ‘PROC_RAC_STATSPACK‘,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=hourly; INTERVAL=1; BYMINUTE=30‘,
job_class => ‘statspack_suse11b_class‘,
comments => ‘This job will run on suse11b‘,
enabled => TRUE);
DBMS_SCHEDULER.create_job (
job_name => ‘ORCL_PERFSTAT_PURGE_N2‘,
job_type => ‘PLSQL_BLOCK‘,
job_action => ‘begin STATSPACK.PURGE(31); end;‘,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=DAILY; BYHOUR=23; BYMINUTE=30‘,
job_class => ‘statspack_suse11b_class‘,
enabled => TRUE);
END;
/
三、校验结果
1) 验证创建的Job
SQL> select OWNER, JOB_NAME, STATE, START_DATE, ENABLED from dba_scheduler_jobs
2 where owner= ‘PERFSTAT‘;
OWNER JOB_NAME STATE START_DATE ENABL
--------------- ------------------------------ --------------- --------------------------------------- -----
PERFSTAT ORCL_PERFSTAT_PURGE_N1 SCHEDULED 22-AUG-14 02.42.37.295650 PM +08:00 TRUE
PERFSTAT ORCL_PERFSTAT_COLLECT_N1 SCHEDULED 22-AUG-14 02.42.37.269292 PM +08:00 TRUE
PERFSTAT ORCL_PERFSTAT_COLLECT_N2 SCHEDULED 22-AUG-14 02.43.17.414613 PM +08:00 TRUE
PERFSTAT ORCL_PERFSTAT_PURGE_N2 SCHEDULED 22-AUG-14 02.43.17.438804 PM +08:00 TRUE
2) 手工执行Job
SQL> exec dbms_scheduler.run_job(‘ORCL_PERFSTAT_COLLECT_N1‘);
SQL> exec dbms_scheduler.run_job(‘ORCL_PERFSTAT_COLLECT_N2‘);
SQL> SELECT *
2 FROM ( SELECT log_id,
3 job_name,
4 job_subname,
5 status,
6 actual_start_date,
7 run_duration
8 FROM dba_scheduler_job_run_details
9 WHERE job_name like ‘%ORCL_PERFSTAT%‘
10 ORDER BY actual_start_date DESC)
11 WHERE ROWNUM < 15;
LOG_ID JOB_NAME JOB_SUBNAME STATUS ACTUAL_START_DATE RUN_DURATION
---------- ------------------------- ------------ ----------- ------------------------------------- ----------------
156 ORCL_PERFSTAT_COLLECT_N2 SUCCEEDED 22-AUG-14 02.43.32.470484 PM +08:00 +000 00:00:04
155 ORCL_PERFSTAT_COLLECT_N1 SUCCEEDED 22-AUG-14 02.43.00.288887 PM +08:00 +000 00:00:04
四、参考
http://www.oracle-class.com/?p=2384
http://jonathanlewis.wordpress.com/2011/01/14/statspack-on-rac/