Statspack Setup (Parte I)

O Statspack é um processo do Oracle database que tem a capacidade de coletar e armazenar as informações referente a performance da instância de banco de dados seja ela single ou RAC. Você pode usar o Statspack como uma alternativa ao relatório Automatic Workload Repository, mais conhecido como AWR (que é uma option do Oracle e precisa ser licenciado à parte no Oracle Diagnostic Pack).

Diferentemente do AWR que já vem instalado e 100% funcional, o Statspack precisa ser configurado manualmente para que funcione de forma correta. Esta etapa é muito simples, basta executar um script sql que fica na $ORACLE_HOME/rdbms/admin. Nesta pasta existem vários scripts sql que são usados na administração da instância, destes scripts todos aqueles que começarem com spXXXX.sql são scripts inerentes ao Statspack, neste post vamos falar apenas da instalação do Statspack, nos demais posts vamos explicar como utilizar as principais funcionalidades.

Veja na tabela abaixo os principais scripts que estamos falando e suas respectivas funcionalidades:

Script	Função	Executar com
SQL> @spcreate.sql	Instalação do Statspack	SYSDBA
SQL> @spauto.sql	Cria um job para coleta dos snapshots	Usuário com Role de DBA
SQL> @spreport.sql	Gerar o relatório do Statspack	Usuário com Role de DBA
SQL> @sppurge.sql	Elimina os snapashots antigos	Usuário com Role de DBA
SQL> @sptrunc.sql	Trunca as tabelas dos snapshots	PERFSTAT
SQL> @spdrop.sql	Desinstala o Statspack	Usuário com Role de DBA

Para configurar o Statspack siga as instruções a seguir:

#1 – Crie uma tablespace chamada PERFSTAT:

SQL> create tablespace PERFSTAT datafile '+DATA/xxxx/perfstat_01.dbf' size 1G;

#2 – Se estiver utilizando arquitetura multitenant execute o script abaixo. Caso contrário, vá direto para o passo 3.

SQL> alter session set "_oracle_script"=true;

#3 – Execute o script para criação dos objetos do Statspack:

SQ> @?/rdbms/admin/spcreate.sql

Nota: Este script vai te pedir algumas informações como:

Nome do usuário que será criado (informe PERFSTAT)
Senha do referido usuário: informe uma senha a seu critério
Tablespace para o usuário perfstat: informe a tablespace PERFSTAT
Tablespace temporária: informe a tablespace temporária da sua instância

#4 – Colete as estátisticas do usuário statspack (opcional, porém recomendável):

SQL> exec dbms_stats.gather_schema_stats('PERFSTAT');

#5 – A patir de agora, você já pode se conectar no banco de dados com o usuário statspack e executar a primeira coleta de snapshots:

SQL> conn perfstat/passwd
SQL> exec statspack.snap;

#6 – A coleta das informações através dos snapshots deve ser feita de maneira automática, então o que você pode fazer é criar um job para que a coleta seja executa em um intervalo regular, para isso podemos usar a package DBMS_SCHEDULER. No exemplo abaixo vamos criar um job para fazer a coleta a cada 15 minutos:

BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
    job_name           =>  'coleta_snapshot',
    job_type           =>  'STORED_PROCEDURE',
    job_action         =>  'perfstat.statspack.snap',
    start_date         =>  '25-JAN-22 07.00.00 AM America/Sao_Paulo',
    repeat_interval    =>  'FREQ=MINUTELY;INTERVAL=20',
    comments           =>  'Coleta de snapshots Statspack');
 END;
 /

#7 – Para monitorar o JOB que fora criado, você pode executar o select abaixo:

SELECT owner, job_name, enabled, state, job_action, next_run_date, run_count, failure_count
FROM dba_scheduler_jobs
WHERE owner = 'PERFSTAT';

#8 – Como última etapa, para garantirmos que tudo está OK, execute o select abaixo e veja se existem registros na view STATS$SNAPSHOT. Caso haja registros nesta view significa que os snapshots estão sendo coletados normalmente, por enquanto essa informação é suficiente no próximo post vamos entender em detalhes o que essas informações querem dizer.

SELECT snap_id,to_char(snap_time, 'dd/mm/rr hh24:mi') snap_time,snapshot_exec_time_s
 FROM perfstat.stats$snapshot
 ORDER BY
 1;

Se você chegou até aqui, seu Statspack está pronto. No próximo post vamos mostrar como gerar o relarório do Statspack e também como explorar algumas das views que ele disponibiliza para coletarmos dados de performace do banco de dados.

Se você quiser ver na prática o conteúdo deste artigo, tem um vídeo á respeito em nosso canal do YouTube.

Te vejo no próximo post, tchau!