André Spadini | MultiCloud | DBA

Oracle: Statspack

Oracle: Statspack

Andre Spadini's photo
Andre Spadini
Jul 20, 2021·

3 min read

Uma das maiores dificuldades de quem trabalha com banco de dados Oracle Standard Edition One ou Standard Edition é a geração de relatórios de performance, pois todas as features de performance são disponibilizadas somente para a versão Enterprise Edition. Gostaria de compartilhar uma forma fácil e rápida de configurar o relatório de performance através do pacote STATSPACK. Nesse artigo iremos realizar as seguintes atividades:

  • Instalação do pacote STATSPACK
  • Coleta manual de estatísticas
  • Coleta automática de estatísticas
  • Gerando relatório
  • Instalação do pacote STATSPACK

Uma vez instalado o utilitário STATSPACK, é criado o usuário perfstat, o qual é dono de todos os códigos PL/SQL e os objetos de banco de dados criados.

Em banco de dados com CDB/PDBs, se faz necessário habilitar opção _oracle_script, pois não é possível criar common user no CDB.

Instalação do pacote STATSPACK

/* Ambiente com CDB*/

alter session set "_oracle_script"=true;

/* CRIAR TABLESPACE */

create tablespace tools datafile size 200m autoextend on next 100m;

/* Conecte usando “sys/manager as sysdba” para instalar o STATSPACK usando o script spcreate.sql localizado no diretório $ORACLE_HOME/rdbms/admin. Use as configurações acima quando solicitado pelo programa. */

@?/rdbms/admin/spcreate

Quando executarmos o script spcreate, precisaremos responder 3 perguntas:

  • Enter value for perfstat_password: [senha do usuário perfstat]
  • Enter value for default_tablespace: TOOLS
  • Enter value for temporary_tablespace: TEMP

Se aparecerem as mensagens abaixo, quer dizer que o pacote foi instalado com sucesso!

Package created.

No errors.
Creating Package Body STATSPACK...

Package body created.

No errors.

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

Usuário Statspack

Apos a instalação do pacote, todas as atividades serão realizadas com o usuário perfstat.

sqlplus perfstat

Coleta manual de estatísticas

execute statspack.snap;

Coleta automática de estatísticas

O script spauto.sql faz uso do pacote DBMS_JOB, a forma automatizada para coleta de estatísticas. O script fornecido pela Oracle programa fotografias de hora em hora. Esse processo pode ser alterado para se adequar a um determinado sistema. 

@?/rdbms/admin/spauto

Consultando os relatórios gerados.

Após a espera de algumas horas consulte a view stats$snapshot para listar quais snapshots foram coletados.

select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')
     "Date/Time" from stats$snapshot,v$database
     order by 1, 2;

Gerando relatório

Para examinar as mudanças nas estatísticas entre dois períodos de tempo, execute o arquivo spreport.sql enquanto conectado com o usuário perfstat.

@?/rdbms/admin/spreport

Adicional

Criando job para coletar relatórios a cada 15 minutos. 

BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'PERFSTAT.statspack_snap_15min',
repeat_interval => 'FREQ=MINUTELY;BYMINUTE=00,15,30,45' );
DBMS_SCHEDULER.CREATE_JOB( 
job_name => 'PERFSTAT.sp_snapshot', 
job_type => 'STORED_PROCEDURE', 
job_action => 'PERFSTAT.statspack.snap', 
schedule_name => 'PERFSTAT.statspack_snap_15min', 
auto_drop => FALSE, 
comments => 'Statspack collection' ); 
DBMS_SCHEDULER.ENABLE('perfstat.sp_snapshot');
END;
/

Criando Job para deletar statspack apos 30d 

BEGIN DBMS_SCHEDULER.CREATE_JOB (
job_name => '"PERFSTAT"."JOB_CLEAN_SNAPSHOTS"',
job_type => 'PLSQL_BLOCK',
job_action => 'delete from perfstat.stats$snapshot where snap_time; (sysdate - 30);', 
number_of_arguments => 0,
start_date => NULL,
repeat_interval => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SAT;BYHOUR=23;BYMINUTE=0;BYSECOND=0',
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => 'Eliminacao de snapshots antigos do STASPACK.');
DBMS_SCHEDULER.enable( name => '"PERFSTAT"."JOB_CLEAN_SNAPSHOTS"');
END;
/

Abs!!!!!

Oracle