Ontem publiquei um artigo falando sobre como configurar o Statspack no seu banco de dados e hoje vamos ver como alterar algumas de suas configurações, como deletar snapshots, vou mostrar também um job que faz a reciclagem de snapshots automaticamente baseado na retenção que quisermos e por fim vamos ver como remover completamente o Statspack.
Vamos ver por primeiro o que considero mais importante para podermos realizar uma boa análise de desempenho utilizando o relatório do Statspack: os níveis de coleta.
Temos os seguintes níveis de captura:
| Level 0 | This level captures general statistics, including rollback segment, row cache, SGA, system events, background events, session events, system statistics, wait statistics, lock statistics, and Latch information. |
| Level 5 | This level includes capturing high resource usage SQL Statements, along with all data captured by lower levels. |
| Level 6 | This level includes capturing SQL plan and SQL plan usage information for high resource usage SQL Statements, along with all data captured by lower levels. |
| Level 7 | This level captures segment level statistics, including logical and physical reads, row lock, itl and buffer busy waits, along with all data captured by lower levels. |
| Level 10 | This level includes capturing Child Latch statistics, along with all data captured by lower levels. |
Estas informações estão presentes na view stats$level_description do Statspack e pode ser consultada com a query:
SQL> SELECT * FROM stats$level_description ORDER BY snap_level;
Eu gosto de usar o nível 6 de coleta, pois este captura os planos de execução dos comandos SQL que mais utilizaram recursos no banco de dados no período do snapshot. O nível padrão é 5, então tenha sempre em mente que quanto maior o nível de coleta, mais dados serão coletados e armazenados.
Podemos executar um único snapshot com nível 10, por exemplo:
SQL> EXECUTE STATSPACK.SNAP(i_snap_level=>10); PL/SQL procedure successfully completed.
Ou, por exemplo, alterar todos os futuros snapshots para executar com o nível 6:
SQL> EXECUTE STATSPACK.SNAP(i_snap_level=>6, i_modify_parameter=>'true'); PL/SQL procedure successfully completed.
Vamos consultar os snapshots que já temos:
SQL> select name, snap_level, snap_id, to_char(snap_time, 'DD/MM/YYYY HH24:MI:SS') "Snapshot Time" from stats$snapshot,v$database order by snap_id; NAME SNAP_LEVEL SNAP_ID Snapshot Time --------- ---------- ---------- ------------------- LOREDB 5 1 20/07/2016 13:01:16 LOREDB 5 2 20/07/2016 13:44:18 LOREDB 5 3 20/07/2016 14:00:04 LOREDB 5 4 20/07/2016 15:00:10 LOREDB 5 5 20/07/2016 20:45:41 LOREDB 5 6 20/07/2016 21:00:07 LOREDB 6 7 20/07/2016 21:07:38 LOREDB 10 8 20/07/2016 21:07:58 LOREDB 6 9 20/07/2016 21:09:51 9 rows selected.
Agora vamos ver como remover os snapshots. Execute o script abaixo para remover um range de snapshots:
SQL> @?/rdbms/admin/sppurge.sql
Database Instance currently connected to
========================================
Instance
DB Id DB Name Inst Num Name
----------- ---------- -------- ----------
1937271711 LOREDB 1 loredb
Snapshots for this database instance
====================================
Base- Snap
Snap Id Snapshot Started line? Level Host Comment
-------- --------------------- ----- ----- --------------- --------------------
1 20 Jul 2016 13:01:16 5 loredata-srv.lo
2 20 Jul 2016 13:44:18 5 loredata-srv.lo
3 20 Jul 2016 14:00:04 5 loredata-srv.lo
4 20 Jul 2016 15:00:10 5 loredata-srv.lo
5 20 Jul 2016 20:45:41 5 loredata-srv.lo
6 20 Jul 2016 21:00:07 5 loredata-srv.lo
7 20 Jul 2016 21:07:38 6 loredata-srv.lo
8 20 Jul 2016 21:07:58 10 loredata-srv.lo
9 20 Jul 2016 21:09:51 6 loredata-srv.lo
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: 1
Using 1 for lower bound.
Enter value for hisnapid: 3
Using 3 for upper bound.
Deleting snapshots 1 - 3.
Number of Snapshots purged: 3 ~~~~~~~~~~~~~~~~~~~~~~~~~~~
Purge of specified Snapshot range complete.
Podemos observar que os snapshots de snap_id 1 a 3 foram removidos:
SQL> select name, snap_level, snap_id, to_char(snap_time, 'DD/MM/YYYY HH24:MI:SS') "Snapshot Time" from stats$snapshot,v$database order by snap_id; NAME SNAP_LEVEL SNAP_ID Snapshot Time --------- ---------- ---------- ------------------- LOREDB 5 4 20/07/2016 15:00:10 LOREDB 5 5 20/07/2016 20:45:41 LOREDB 5 6 20/07/2016 21:00:07 LOREDB 6 7 20/07/2016 21:07:38 LOREDB 10 8 20/07/2016 21:07:58 LOREDB 6 9 20/07/2016 21:09:51 6 rows selected.
Infelizmente o Statspack não possui nenhum job de purge automático e para controlar a retenção do repositório precisamos fazer isso manualmente executando o script sppurge.sql ou desenvolver algo pra isso. Eu, particularmente, uso a procedure abaixo para realizar esta atividade e crio um job para ela, onde tem “snap_time < sysdate-30” você configura sua retenção em dias, vamos à procedure:
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-30 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.
Para executá-la manualmente depois de criada faça:
SQL> exec perfstat.statspackpurge; PL/SQL procedure successfully completed.
Para criar um job que fará o purge use a package dbms_job:
SQL> declare
my_job number;
begin
dbms_job.submit(job => my_job,
what => 'perfstat.statspackpurge;',
next_date => sysdate+1,
interval => 'sysdate+1');
end;
/
PL/SQL procedure successfully completed.
Consultando a view dba_jobs podemos ver que o job está agendado:
SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
Session altered.
SQL> select job, what, LAST_DATE, NEXT_DATE, TOTAL_TIME, BROKEN, FAILURES from dba_jobs where SCHEMA_USER='PERFSTAT';
JOB WHAT LAST_DATE NEXT_DATE TOTAL_TIME B FAILURES
---------- ---------------------------------------------------------------------- ------------------- ------------------- ---------- - ----------
1 statspack.snap; 20/07/2016 21:00:07 20/07/2016 22:00:00 18 N 0
2 perfstat.statspackpurge; 21/07/2016 21:30:22 0 N
2 rows selected.
Já vimos bastante coisa sobre o Statspack, agora vamos por fim removê-lo do nosso banco de dados.
Se você quiser somente efetuar um truncate para limpar os dados das tabelas do Statspack pode executar o seguinte script:
SQL> @?/rdbms/admin/sptrunc.sql Warning ~~~~~~~ Running sptrunc.sql removes ALL data from Statspack tables. You may wish to export the data before continuing. About to Truncate Statspack Tables ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If would like to exit WITHOUT truncating the tables, enter any text at the begin_or_exit prompt (e.g. 'exit'), otherwise if you would like to begin the truncate operation, press <return> Enter value for begin_or_exit: Entered at the 'begin_or_exit' prompt ... Starting truncate operation Table truncated. . . . . 6 rows deleted. 2 rows deleted. Commit complete. Package altered. ... Truncate operation complete
Podemos ver na stats$snapshot que não há mais snapshots:
SQL> select name, snap_level, snap_id, to_char(snap_time, 'DD/MM/YYYY HH24:MI:SS') "Snapshot Time" from stats$snapshot,v$database order by snap_id; no rows selected
Porém o job de coleta continua agendado:
SQL> select job, what, LAST_DATE, NEXT_DATE, TOTAL_TIME, BROKEN, FAILURES from dba_jobs where SCHEMA_USER='PERFSTAT';
JOB WHAT LAST_DATE NEXT_DATE TOTAL_TIME B FAILURES
---------- ---------------------------------------------------------------------- ------------------- ------------------- ---------- - ----------
1 statspack.snap; 20/07/2016 21:00:07 20/07/2016 22:00:00 18 N 0
2 perfstat.statspackpurge; 21/07/2016 21:30:22 0 N
2 rows selected.
Caso queira remover todo o Statspack é só executar o script com o usuário SYS e se for no CDB do 12c lembre-se do parâmetro “_oracle_script”:
[oracle@loredata-srv ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 20 21:54:14 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options SQL> alter session set "_oracle_script"=TRUE; Session altered.
SQL> @?/rdbms/admin/spdrop.sql Dropping old versions (if any) Synonym dropped. Sequence dropped. Synonym dropped. Table dropped. . . . . SQL> -- Drop PERFSTAT user SQL> SQL> @@spdusr SQL> Rem SQL> Rem $Header: rdbms/admin/spdusr.sql /main/15 2009/02/11 10:15:23 shsong Exp $ SQL> Rem SQL> Rem spdusr.sql SQL> Rem SQL> Rem Copyright (c) 1999, 2009, Oracle and/or its affiliates. SQL> Rem All rights reserved. SQL> Rem SQL> Rem NAME SQL> Rem spdusr.sql SQL> Rem SQL> Rem DESCRIPTION SQL> Rem SQL*Plus command file to DROP user which contains the SQL> Rem STATSPACK database objects. SQL> Rem SQL> Rem NOTES SQL> Rem Must be run when connected to SYS (or internal) SQL> Rem SQL> Rem MODIFIED (MM/DD/YY) SQL> Rem shsong 02/02/09 - remove drop STATS$X_$KCFIO etc SQL> Rem shsong 07/03/08 - drop view STATS$X_$KCBFWAIT etc SQL> Rem cdgreen 08/22/05 - 4562627 SQL> Rem cdgreen 05/24/05 - 4246955 SQL> Rem cdialeri 11/07/03 - 10g - streams - rventkate SQL> Rem cdialeri 04/23/01 - 9.0 SQL> Rem cdialeri 09/12/00 - sp_1404195 SQL> Rem cdialeri 04/07/00 - 1261813 SQL> Rem cdialeri 02/16/00 - 1191805 SQL> Rem cdialeri 11/04/99 - 1059172 SQL> Rem cdialeri 08/13/99 - Created SQL> Rem SQL> SQL> set echo off; View dropped. Synonym dropped. . . . . User dropped. NOTE: SPDUSR complete. Please check spdusr.lis for any errors.
Agora é só remover a tablespace e tudo volta a ser como era antes:
SQL> drop tablespace PERFSTAT including contents and datafiles; Tablespace dropped.
É isso aí galera. Esse tenho certeza que vai ajudar muita gente por aí. Se curtiu o artigo assina o blog, assim me motivo a escrever cada vez mais.
Grande abraço e até mais,
Franky