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 perfstat.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;
/
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 => 'perfstat.statspackpurge;',
next_date => trunc(sysdate)+1,
interval => 'trunc(sysdate)+1');
end;
/
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. Para executar o statspack deve executar o script spreport.sql, e informar os dados que serão solicitados para a geração do relatório.
SQL> @?/rdbms/admin/spreport.sql
Trecho final do statspack:
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.sqlReferências:http://docs.oracle.com/cd/B10500_01/server.920/a96533/statspac.htm
Para ambientes RAC apenas é necessário a criação dos jobs nas 2 instancias, tanto o job para coleta, quanto o job para limpeza.
ResponderExcluir