O STATSPACK é um ferramenta de performance tuning fornecida pela Oracle, o fato de não ser necessário uma licença a parte para o uso faz dela uma ótima opção.
O processo para instalação do Oracle STATSPACK é bem simples, vamos começar criando uma tablespace para armazenar os dados.
create tablespace STATSPACK datafile '/u01/app/oracle/oradata/dbteste/statspack_data01.dbf' size 100m autoextend on next 100m maxsize 5000m force logging;
Conectado como sysdba, vamos executar o script de criação localizado em $ORACLE_HOME/rdbms/admin.
@?/rdbms/admin/spcreate.sql
Após a execução, informamos uma senha para o user, a tablespace default e a tablespace temporária, conforme imagem abaixo:
Agora vamos conectar com o usuário perfstat.
conn perfstat/1234
E executar o script a baixo para criar um job para coleta de snapshoot automaticamente.
@?/rdbms/admin/spauto.sql
Para efetuar a manutenção dos snapshoot, será criada uma procedure para aplicar retenção de 7 dias.
CREATE OR REPLACE PROCEDURE statspackpurge
IS
var_lo_snap NUMBER;
var_hi_snap NUMBER;
var_db_id NUMBER;
var_instance_no NUMBER;
noofsnapshot NUMBER;
n_count NUMBER ;
CURSOR cursor_inst
IS
SELECT
instance_number
FROM
gv$instance;
BEGIN
n_count := 0;
FOR cur_inst IN cursor_inst
LOOP
SELECT
COUNT(*)
INTO
n_count
FROM
stats$snapshot
WHERE
snap_time < sysdate-7
AND instance_number=cur_inst.instance_number;
IF n_count > 0 THEN
SELECT
MIN(s.snap_id) ,
MAX(s.snap_id),
MAX(di.dbid),
MAX(di.instance_number)
INTO
var_lo_snap,
var_hi_snap,
var_db_id,
var_instance_no
FROM
stats$snapshot s ,
stats$database_instance di
WHERE
s.dbid = di.dbid
AND s.instance_number = di.instance_number
AND di.startup_time = s.startup_time
AND s.instance_number = cur_inst.instance_number
AND s.snap_time < sysdate-7;
noofsnapshot := statspack.purge( i_begin_snap => var_lo_snap ,
i_end_snap => var_hi_snap , i_snap_range => true , i_extended_purge =>
true , i_dbid => var_db_id , i_instance_number => var_instance_no);
dbms_output.Put_line('snapshot deleted'||TO_CHAR(noofsnapshot));
END IF;
END LOOP;
END;
E o job abaixo para executar o processo de limpeza diariamente.
declare my_job number; begin dbms_job.submit(job => my_job, what => 'statspackpurge;', next_date => trunc(sysdate)+1, interval => 'trunc(sysdate)+1'); end; /
Para conferir se os jobs estão criados e agendados, executamos este select:
set lines 190 set pages 200 col WHAT for a50 col SCHEMA_USER for a15 select job,SCHEMA_USER,WHAT,NEXT_DATE,NEXT_SEC from dba_jobs where SCHEMA_USER = 'PERFSTAT';
