Configuring Statspack in the Oracle RAC environment

Statspack is the product of the Oracle 9i ERA, and is the predecessor of AWR, which has a cross-milestone significance for monitoring and analyzing database performance. After the Oracle 10g, AWR replaced the statspack. Nonetheless, Statpack is still a good choice if the AWR exception or the need to debug the package license. In a RAC environment, however, Statspack is not supported and requires a separate configuration and management using the job. This article describes the effect of creating a service in a RAC environment, as well as a job to achieve snapshot at the same time for each node.

First, the demonstration environment

suse11a:oracle:orcl101 > Cat/etc/issuewelcome to SUSE Linux Enterprise Server one SP3  (x86_64)-Kernel \ R (\l). SUS e11a:oracle:orcl101 > Sqlplus-vsql*plus:release 10.2.0.5.0-productionsuse11a:oracle:orcl101 > $ORA _crs_home/ Bin/crsctl Query CRS Activeversioncrs active version on the cluster is [10.2.0.5.0]

Second, the configuration Statspack

1) First add service$ srvctl add service-d orcl10-s statspack_suse11a_srvc-r orcl101 $ srvctl Add service-d orcl10-s STA Tspack_suse11b_srvc-r orcl102 $ srvctl start service-d orcl10-s statspack_suse11a_srvc$ srvctl start service-d ORCL1 0-s statspack_suse11b_srvc$ srvctl Status service-d Orcl10service orcl10_srv is running on instance (s) orcl102, orcl101 Service STATSPACK_SUSE11A_SRVC is running on instance (s) Orcl101service STATSPACK_SUSE11B_SRVC are running on instance (s) O  rcl102$ 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, have 2 handler (s) for the This service ...  Service "STATSPACK_SUSE11B_SRVC" has 1 instance (s).  Instance "orcl102", status ready, have 1 handler (s) for the This service ..... 2) Configure Statspackconn/as sysdbacreate tablespacePerfstat datafile ' +asm_data ' size 500m autoextend on; @?/rdbms/admin/spcreategrant 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) create Job Classbegin dbms_scheduler.create_job_class (job_class_name = ' Statspack_suse   11a_class ', service = ' statspack_suse11a_srvc '); Dbms_scheduler.create_job_class (job_class_name = ' statspack_suse11b_class ', service = ' stat SPACK_SUSE11B_SRVC '); End;/sql> Select Job_class_name, service from dba_scheduler_job_classes; Job_class_name SERVICE-------------------------------------------------------------------------------DEFA Ult_job_classauto_tasks_job_classstatspack_suse11a_class Statspack_suse11a_srvcstatspack_suse11b_class Stats Pack_suse11b_srvcgrant EXECUTE on sys.     Statspack_suse11a_class to Perfstat; GRANT EXECUTE on sys. Statspack_suse11b_class to PerfSTAT;4) Create a procedure for synchronizing nodes Conn perfstat/perfstatcreate OR REPLACE PROCEDURE db_proc_rac_statspackas 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_m                             Ode, timeout =--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 If 1 Then ': Lock wait timed out ' when 2 Then ': Deadlock detected ' When 3 Then ': Parameter error ' when 4 Then ': Already holding lock ' while 5 Then ': Illega   L lock handle ' ELSE ': Unknown error ' END '; END IF; END;/5) Create a process for job scheduling begin Dbms_scheduler.create _program (program_name = ' Proc_rac_statspack ', Program_type = ' stored_                                  PROCEDURE ', program_action = ' Db_proc_rac_statspack ',                  Enabled = TRUE);                                                                    END;      /6) Clear the job with the same name (if present) 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) Create a job--author:leshami that generates snapshot and clears the history snapshot--blog:h      Ttp://blog.csdn.net/leshamibegin 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 would run on suse11a ', ENABLED = TRUE); Dbms_scheduler.create_job (job_name = ' orcl_perfstat_purge_n1 ', Job_type = ' Plsql_blo CK ', 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'll run on s    Use11b ', 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;/

third, check results

1) Verify created jobsql> Select OWNER, Job_name, State, Start_date, ENABLED from Dba_scheduler_jobs 2 where owner= ' Perfsta  T '; OWNER job_name State start_date enabl-------------- ------------------------------------------------------------------------------------------perfstat Orcl_perfsta       T_purge_n1 scheduled 22-aug-14 02.42.37.295650 PM +08:00 trueperfstat orcl_perfstat_collect_n1       Scheduled 22-aug-14 02.42.37.269292 PM +08:00 trueperfstat orcl_perfstat_collect_n2 SCHEDULED 22-aug-14 02.43.17.414613 PM +08:00 trueperfstat orcl_perfstat_purge_n2 scheduled 22-aug-14                                                        02.43.17.438804 PM +08:00 TRUE2) manually execute jobsql> 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% '                   ORDER by Actual_start_date DESC)                                                                                                                                                                                                           One 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

Iv. references
http://www.oracle-class.com/?p=2384
http://jonathanlewis.wordpress.com/2011/01/14/statspack-on-rac/

Configuring Statspack in the Oracle RAC environment