implementando o STATSPACK – Oracle 10/11g

Tanto na versão 10g como na 11g a utilização das features de DIAGNOSTIC e TUNING (controlada a partir do 11g pelo parâmetro control_management_pack_access) exige a necessidade de uma licença a parte. Sem a mesma o Oracle não irá armazenar dados relacionados a performance do ambiente, o que pode dificultar nosso trabalho como DBA’s.

Afim de evitar que o ambiente fique sem coleta de informações relacionadas a performance, uma alternativa que não exige licenciamento é a utilização do STATAPACK.

O STATSPACK é fornecido pela Oracle desde a versão 8i e tem como objetivo auxiliar na análise e monitoramento de performance do ambiente, fornecendo um relatório detalhado, semelhante ao AWR.

Abaixo estarei demonstrando o processo para implementar o statspack. O processo de ser executado através de uma conexão sysdba.

Na primeira etapa será criada uma tablespace que será utilizada pelo statspack.

create tablespace statspack datafile '/oraprd02/oradata/dbtrn/statspack01.dbf' size 100m autoextend on next 100m maxsize 5000m;

Tablespace created.

O próximo passo consiste na execução do script spcreate.sql localizado em $ORACLE_HOME/rdbms/admin conectado como sysdba.

  • Durante a execução deste script será solicitada a definição de uma senha para o usuário perfstat (criado pelo processo). A definição de uma senha é obrigatório para possamos continuar com a instalação;
  • Após definir a senha, será solicitada a default tablespace. No exemplo abaixo estarei definindo como statspack (tablespace criada anteriormente);
  • Por último será solicitado qual a tablespace temporária a ser utilizada. Neste caso, definirei como a tablespace padrão (TEMP).
SQL> @?/rdbms/admin/spcreate.sql

Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: perfstat
perfstat

Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data.  Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
STATSPACK                      PERMANENT
SYSAUX                         PERMANENT *
USERS                          PERMANENT

Pressing  will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: STATSPACK

Using tablespace STATSPACK as PERFSTAT default tablespace.

Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas).  Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS  DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP                           TEMPORARY *

Pressing  will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

.
.
.

Creating Package STATSPACK...

Package created.

No errors.
Creating Package Body STATSPACK...

Package body created.

No errors.

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

Para que possamos gerar um relatório do ambiente, assim como no AWR, precisamos gerar snapshots da base. Um snapshot pode ser gerado manualmente ou automaticamente.

Abaixo estarei mostrando os 2 exemplos:

  • Para uma coleta automática, estarei executando o script spauto.sql. Este script criará um job que por padrão irá gerar um snapshot por hora. De acordo com a necessidade do ambiente é possível alterar o intervalo de execução deste job.
SQL> @?/rdbms/admin/spauto.sql

PL/SQL procedure successfully completed.

Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:

     JOBNO
----------
        43

Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000

Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

       JOB NEXT_DATE          NEXT_SEC
---------- ------------------ --------
        43 30/04/014 15:00:00 15:00:00

1 row selected.
  • Para uma coleta manual usamos o comando abaixo, conectados com o usuário perfstat.

SQL> EXEC STATSPACK.snap

PL/SQL procedure successfully completed.

SQL>

Ao utilizar um processo automatizado para a geração de snapshots, devemos ficar atentos e realizar periodicamente uma limpeza dos mesmos. Para o processo de limpeza usamos o script sppurge.sql.

Na execução do script será solicitado o intervalo de snapshots a serem removidos.

SQL> select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT order by 2;

   SNAP_ID SNAP_TIME
---------- ------------------
        57 29/04/014 15:00:00
        58 29/04/014 16:00:01
        59 29/04/014 17:00:02
        60 29/04/014 18:00:02
        61 29/04/014 19:00:03
        62 29/04/014 20:00:04
        63 29/04/014 21:00:00
        64 29/04/014 22:00:01
        71 29/04/014 23:00:02
        72 30/04/014 00:00:03
        73 30/04/014 01:00:04
        74 30/04/014 02:00:04
        75 30/04/014 03:00:00
        76 30/04/014 04:00:01
        77 30/04/014 05:00:01
        78 30/04/014 06:00:02
        79 30/04/014 07:00:03
        80 30/04/014 08:00:03
        81 30/04/014 09:00:04
        82 30/04/014 10:00:00
        83 30/04/014 11:00:00
        84 30/04/014 12:00:01
        85 30/04/014 13:00:02
        86 30/04/014 14:00:02

24 rows selected.

SQL> @$ORACLE_HOME/rdbms/admin/sppurge.sql

Database Instance currently connected to
========================================

                                Instance
   DB Id    DB Name    Inst Num Name
----------- ---------- -------- ----------
 3062688822 DBTST             1 dbtst

Snapshots for this database instance
====================================

                               Base-  Snap
 Snap Id   Snapshot Started    line? Level Host            Comment
-------- --------------------- ----- ----- --------------- --------------------
      57  29 Apr 2014 15:00:00           5 baseteste
      58  29 Apr 2014 16:00:01           5 baseteste
      59  29 Apr 2014 17:00:02           5 baseteste
      60  29 Apr 2014 18:00:02           5 baseteste
      61  29 Apr 2014 19:00:03           5 baseteste
      62  29 Apr 2014 20:00:04           5 baseteste
      63  29 Apr 2014 21:00:00           5 baseteste
      64  29 Apr 2014 22:00:01           5 baseteste
      71  29 Apr 2014 23:00:02           5 baseteste
      72  30 Apr 2014 00:00:03           5 baseteste
      73  30 Apr 2014 01:00:04           5 baseteste
      74  30 Apr 2014 02:00:04           5 baseteste
      75  30 Apr 2014 03:00:00           5 baseteste
      76  30 Apr 2014 04:00:01           5 baseteste
      77  30 Apr 2014 05:00:01           5 baseteste
      78  30 Apr 2014 06:00:02           5 baseteste
      79  30 Apr 2014 07:00:03           5 baseteste
      80  30 Apr 2014 08:00:03           5 baseteste
      81  30 Apr 2014 09:00:04           5 baseteste
      82  30 Apr 2014 10:00:00           5 baseteste
      83  30 Apr 2014 11:00:00           5 baseteste
      84  30 Apr 2014 12:00:01           5 baseteste
      85  30 Apr 2014 13:00:02           5 baseteste
      86  30 Apr 2014 14:00:02           5 baseteste

Warning
~~~~~~~
sppurge.sql deletes all snapshots ranging between the lower and
upper bound Snapshot Id's specified, for the database instance
you are connected to.  Snapshots identified as Baseline snapshots
which lie within the snapshot range will not be purged.

It is NOT possible to rollback changes once the purge begins.

You may wish to export this data before continuing.

Specify the Lo Snap Id and Hi Snap Id range to purge
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for losnapid: 57
Using 57 for lower bound.

Enter value for hisnapid: 60
Using 60 for upper bound.

Deleting snapshots 57 - 60.

Number of Snapshots purged: 4                                        ~~~~~~~~~~~~~~~~~~~~~~~~~~~
Purge of specified Snapshot range complete.

Afim de reduzir a necessidade de intervenção manual no processo de gerenciamento dos snapshots, estarei criando uma procedure chamada statspackpurge para limpeza dos snapshots, com uma retenção de 7 dias, porém esse valor pode ser alterado de acordo com a necessidade do ambiente.

SQL> 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 ;
begin

n_count := 0;

select count(*) into n_count from stats$snapshot where snap_time < sysdate-7; 
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.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 => false
 , i_dbid => var_db_id
 , i_instance_number => var_instance_no);

 dbms_output.Put_line('snapshot deleted'||to_char(noofsnapshot));

end if;
end;
/

Procedure created.

Após criada a procedure, estarei criando um job que executara o processo de limpeza diariamente.

SQL> declare
  my_job number;
begin
  dbms_job.submit(job => my_job,
    what => 'statspackpurge;',
    next_date => trunc(sysdate)+1,
    interval => 'trunc(sysdate)+1');
end;
                  2    3    4    5    6    7    8    9
 10  /

PL/SQL procedure successfully completed.

Para consultar os snpshots existentes, podemos usar o comando abaixo, conectados com o owner perfstat.

SQL> select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;

 Snap Id SNAP_TIME
-------- ------------------
      63 29/04/014 21:00:00
      74 30/04/014 02:00:04
      82 30/04/014 10:00:00
      61 29/04/014 19:00:03
      62 29/04/014 20:00:04
      75 30/04/014 03:00:00
      76 30/04/014 04:00:01
      77 30/04/014 05:00:01
      80 30/04/014 08:00:03
      81 30/04/014 09:00:04
      83 30/04/014 11:00:00
      84 30/04/014 12:00:01
      86 30/04/014 14:00:02
      71 29/04/014 23:00:02
      72 30/04/014 00:00:03
      73 30/04/014 01:00:04
      78 30/04/014 06:00:02
      79 30/04/014 07:00:03
      85 30/04/014 13:00:02
      64 29/04/014 22:00:01

20 rows selected.

Após gerado o snapshot, estarei gerando um relatório com a análise do ambiente. Assim como o AWR, para gerar o relatório do statspack, devemos selecionar um snapshot inicial e um final e posteriormente definimos o nome do arquivo a ser gerado, que pode ser precedido pelo local de destino. Neste exemplo usei o destino /tmp.


SQL> SQL> @?/rdbms/admin/spreport.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 3062688822 DBTST               1 dbtst

1 row selected.

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 3062688822        1 DBTST        dbtst        baseteste

Using 3062688822 for database Id
Using          1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.

Listing all Completed Snapshots

                                                       Snap
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
dbtst        DBTST               61 29 Apr 2014 19:00     5
                                 62 29 Apr 2014 20:00     5
                                 63 29 Apr 2014 21:00     5
                                 64 29 Apr 2014 22:00     5
                                 71 29 Apr 2014 23:00     5
                                 72 30 Apr 2014 00:00     5
                                 73 30 Apr 2014 01:00     5
                                 74 30 Apr 2014 02:00     5
                                 75 30 Apr 2014 03:00     5
                                 76 30 Apr 2014 04:00     5
                                 77 30 Apr 2014 05:00     5
                                 78 30 Apr 2014 06:00     5
                                 79 30 Apr 2014 07:00     5
                                 80 30 Apr 2014 08:00     5
                                 81 30 Apr 2014 09:00     5
                                 82 30 Apr 2014 10:00     5
                                 83 30 Apr 2014 11:00     5
                                 84 30 Apr 2014 12:00     5
                                 85 30 Apr 2014 13:00     5
                                 86 30 Apr 2014 14:00     5
                                 87 30 Apr 2014 14:31     5

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 61
Begin Snapshot Id specified: 61

Enter value for end_snap: 64
End   Snapshot Id specified: 64

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_61_64.  To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name: /tmp/estatistica_dbtst.lst

Após a execução, foi criado o report  /tmp/statistica_dbtst.lst. Devido a quantidade de informações geradas no arquivo, o mesmo não será mostrado por completo.


STATSPACK report for

Database    DB Id    Instance     Inst Num  Startup Time   Release     RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
          3442772195 dbtrn               1 16-Apr-14 16:48 11.2.0.4.0  NO

Host Name             Platform                CPUs Cores Sockets   Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
     cce              Linux IA (32-bit)          1     0       0          2.0

Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- ------------------
Begin Snap:          1 16-Apr-14 21:35:37       33       2.0
  End Snap:          3 16-Apr-14 21:44:06       35       2.0
   Elapsed:       8.48 (mins) Av Act Sess:       0.0
   DB time:       0.34 (mins)      DB CPU:       0.05 (mins)

Cache Sizes            Begin        End
~~~~~~~~~~~       ---------- ----------
    Buffer Cache:       752M              Std Block Size:         8K
     Shared Pool:       240M                  Log Buffer:    15,360K

Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
      DB time(s):                0.0                0.3        0.01        0.09
       DB CPU(s):                0.0                0.0        0.00        0.01
       Redo size:            4,975.9           34,694.7
   Logical reads:               32.5              226.7
   Block changes:               13.8               96.4
  Physical reads:                0.0                0.1
 Physical writes:                3.7               25.6
      User calls:                0.4                3.0
          Parses:                3.2               22.1
     Hard parses:                0.2                1.4
W/A MB processed:                0.1                0.9
          Logons:                0.1                0.5
        Executes:                5.5               38.4
       Rollbacks:                0.0                0.0
    Transactions:                0.1

Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.99       Redo NoWait %:  100.00
            Buffer  Hit   %:   99.96  Optimal W/A Exec %:  100.00
            Library Hit   %:   93.92        Soft Parse %:   93.49
         Execute to Parse %:   42.43         Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:   39.29     % Non-Parse CPU:   96.27

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   84.57   85.56
    % SQL with executions>1:   48.16   51.64
  % Memory for SQL w/exec>1:   53.34   57.77

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
control file parallel write                        208          14     67   35.2
log file parallel write                             94           8     90   21.4
db file async I/O submit                           366           6     16   15.3
log file sync                                       37           5    131   12.3
CPU time                                                         3           7.5
          -------------------------------------------------------------
Host CPU  (CPUs: 1  Cores: 0  Sockets: 0)
~~~~~~~~              Load Average
                      Begin     End      User  System    Idle     WIO     WCPU
                    ------- -------   ------- ------- ------- ------- --------
                       0.44    0.55      1.09    0.96   97.94   11.98

Note: There is a 12% discrepancy between the OS Stat total CPU time and
      the total CPU time estimated by Statspack
          OS Stat CPU time: 570(s) (BUSY_TIME + IDLE_TIME)
        Statspack CPU time: 509(s) (Elapsed time * num CPUs in end snap)

Para remover o statspack, baste executar o comando spdrop.sql.

@?/rdbms/admin/spdrop.sql

Referências:

http://docs.oracle.com/cd/B10500_01/server.920/a96533/statspac.htm

%name implementando o STATSPACK   Oracle 10/11g

Autor: Jhonata Lamim

MBA em Banco de Dados Oracle, formado pelo Centro Universitário de Araraquara (UNIARA), graduado em Sistemas de Informação pelo Centro Universitário de Brusque (UNIFEBE).

Atua com Banco de Dados Oracle desde Junho de 2010. Atualmente é DBA Senior na Exímio Soluções em TI (www.eximioti.com.br)

Principais atividade:

Implementação, migração, gerenciamento e suporte a produtos Oracle (10g, 11g, 12c, RAC), multiplataforma;
Monitoramento de ambientes 24×7;
Backup e Recovery;
Performance e Tuning;
Alta disponibilidade (HA);
EM database/grid/cloud control 12c/13c;
Conversão de databases;
Standby database / Oracle Data Guard;
Migração de dados para Oracle;

Experiência:

DBA Oracle Teiko Soluções em TI – Jun/2010 – Abr/2018
DBA Oracle, Outsourcing – Marfrig Group – Set/2013 – Abr/2018
DBA Oracle, Outsourcing – Grupo Notre Dame – Intermédica – Mar/2017 – Abr/2018
DBA Oralce, Outsourcing – Hospital Beneficiência Portuguesa de São Paulo – Set/2015 – Abr/2018
DBA Oracle, Outsourcing – Fundação São Francisco Xavier –  Set/2015 – Fev/2017
DBA Oracle, Outsourcing – Unimed Grande Florianopolis – Set/2014 – Jul/2016
DBA Oracle, Outsourcing – Hospital Moinhos de Vento – Set/2014 – Set/2015
DBA Oracle, Outsourcing – Santa Casa de Misericórdia de Porto Alegre – 2013

Certificações:

OCS 12C – Oracle Real Application Clusters 12c Certified Implementation Specialist
OCS 12C – Oracle Database 12c Certified Implementation Specialist
OCE 11G – Oracle Database 11g: Performance Tuning
OCA 11G – Oracle Certified Associate Administrator
OCP 11G/12C – Oracle Certified Professional Administrator
OCS 11G – Oracle Certified Specialist
OPNCS 11G – Oracle Partner Network Certified Specialist
OCS – Oracle Linux 6 Implementation Essentials

Linkedin: https://www.linkedin.com/in/jhonata-lamim-dba-oracle-61366484/