Oracle Standard Edition

Statspack

ALTER SESSION SET time_zone = 'EUROPE/ROME';




BEGIN

DBMS_SCHEDULER.DROP_JOB('PERFSTAT.STATSPACK_SNAP_AND_PURGE');
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;