Oracle Standard Edition

Statspack

Job schedulazione statspack

ALTER SESSION SET time_zone = 'EUROPE/ROME';

select * from dba_scheduler_jobs where job_name='STATSPACK_SNAP_AND_PURGE';

BEGIN
 DBMS_SCHEDULER.DROP_JOB('PERFSTAT.STATSPACK_SNAP_AND_PURGE');
END;
/

BEGIN
 DBMS_SCHEDULER.CREATE_JOB (
  job_name => 'PERFSTAT.STATSPACK_SNAP_AND_PURGE',
  job_type => 'PLSQL_BLOCK',
  job_action => 'BEGIN
    statspack.snap();
    if extract(hour from systimestamp) = 1 and extract(minute from systimestamp) < 30 
    then
     statspack.purge(i_num_days=>180,i_extended_purge=>TRUE);
    end if;
   END;',
  repeat_interval => 'FREQ=MINUTELY; INTERVAL=30;',
  auto_drop => FALSE,
  enabled => TRUE,
  comments => 'Statspack automated snap and purge');
END;
/

Tuning Checkpoint

checkpoint con SE

Checkpoint ad ogni switch di log:

  select max(BYTES/BLOCKSIZE) from v$log;

   MAX(BYTES/BLOCKSIZE)
   --------------------
	         262144


  alter system set log_checkpoint_interval=262144 scope=both;

Checkpoint almeno ogni 5 minuti

 alter system set log_checkpoint_timeout=600 scope=both;