Instalando o STATSPACK no Oracle Database.

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';