Uso e gerenciamento dos ocupantes da SYXAUX

Hoje verificando um database 11GR2 constatei que a tablespace SYSAUX estava ocupando cerca de 17GB, mas porque? Quem está utilizando este espaço? 

Creio que a resposta que veio a sua cabeça é AWR! É sempre ele o culpado?… 

Vamos dar inicio identificando quem são os ocupantes da tablespace SYSAUX que mais consomem espaço, para isto vamos utilizar a view v$sysaux_occupants

SQL> set linesize 120
SQL> set pagesize 100
SQL> COLUMN "Item" FORMAT A25
SQL> COLUMN "Space Used (GB)" FORMAT 999.99
SQL> COLUMN "Schema" FORMAT A25
SQL> COLUMN "Move Procedure" FORMAT A40
SQL>
SQL> SELECT  occupant_name "Item",
  2  space_usage_kbytes/1048576 "Space Used (GB)",
  3  schema_name "Schema"
  4  FROM v$sysaux_occupants
  5  ORDER BY 1
  6  /
 
Item                      Space Used (GB) Schema
------------------------- --------------- -------------------------
AO                                    .00 SYS
AUDIT_TABLES                          .00 SYS
AUTO_TASK                             .00 SYS
EM                                    .50 SYSMAN
EM_MONITORING_USER                    .00 DBSNMP
EXPRESSION_FILTER                     .00 EXFSYS
JOB_SCHEDULER                         .00 SYS
LOGMNR                                .01 SYSTEM
LOGSTDBY                              .00 SYSTEM
ORDIM                                 .00 ORDSYS
ORDIM/ORDDATA                         .00 ORDDATA
ORDIM/ORDPLUGINS                      .00 ORDPLUGINS
ORDIM/SI_INFORMTN_SCHEMA              .00 SI_INFORMTN_SCHEMA
PL/SCOPE                              .00 SYS
SDO                                   .00 MDSYS
SM/ADVISOR                            .10 SYS
SM/AWR                               9.44 SYS
SM/OPTSTAT                           5.86 SYS
SM/OTHER                              .01 SYS
SMON_SCN_TIME                         .00 SYS
SQL_MANAGEMENT_BASE                   .00 SYS
STATSPACK                             .00 PERFSTAT
STREAMS                               .00 SYS
TEXT                                  .00 CTXSYS
TSM                                   .00 TSMSYS
ULTRASEARCH                           .00 WKSYS
ULTRASEARCH_DEMO_USER                 .00 WK_TEST
WM                                    .01 WMSYS
XDB                                   .06 XDB
XSAMD                                 .00 OLAPSYS
XSOQHIST                              .00 SYS
 
31 rows selected.

Observe que temos 2 “ocupantes” que consomem bastante espaço, o SM/AWR e o SM/OPTSTAT. 

Antes de prosseguirmos vamos lembrar que na versão 10.2.0.3 existe um BUG com a limpeza do AWR (Bug 6522103 : WRH$_SQL_PLAN AND WRH$_SQLTEXT ARE NOT PURGED WHEN PURGING AWR SNAPSHOTS), nas demais versões este BUG já foi reparado! 

Prosseguindo.. vamos entender quem são estes ocupantes e como podemos limpar estes espaços:

SM/OPTSTAT: 

Armazena versões mais velhas do otimizador de estatísticas, a partir do 10G ele permite que seja restaurada as estatísticas caso seja encontrada alguma agressão com o novo plano quando as estatísticas são atualizadas. 

Sua retenção default(padrão) é 31 dias, vale lembrar que ele não faz parte do AWR e portanto não é controlado pelo parâmetro de retenção do AWR como veremos mais a frente. 

Identificando o tempo de retenção atual:

SQL> select dbms_stats.get_stats_history_retention from dual;
 
GET_STATS_HISTORY_RETENTION
---------------------------
                         31

Agora vamos ajustar a retenção para apenas 10 dias e remover as estatisticas mais antigas que 10 dias (fazer em etapas reduzindo gradativamente a quantidade de dias, 30,25,20..):

SQL> exec dbms_stats.alter_stats_history_retention(5);
 
SQL> exec DBMS_STATS.PURGE_STATS(SYSDATE-5);
 
PL/SQL procedure successfully completed.

NOTA:Este processo consome muito recurso do database/servidor e deve ser executado fora do horário de "pico"/workload elevada (demorado…)

SM/AWR: 

O Automatic Workload Repository conhecido basicamente por AWR é uma feature introduzida no Oracle 10G que em intervalos regulares gera um snapshot(foto) de todas as estatisticas e informações vitais do database para o repositório de carga de trabalho, AWR com o objetivo de permitir análise e investigações de momentos passados o atuais. 

Vamos verificar o intervalo de snapshot e retenção:

SQL> set lines 190
SQL> col SNAP_INTERVAL for a40
SQL> col RETENTION for a40
SQL> select * from dba_hist_wr_control;
 
      DBID SNAP_INTERVAL                            RETENTION                                TOPNSQL
---------- ---------------------------------------- ---------------------------------------- ----------
 847901981 +00000 01:00:00.0                        +00008 00:00:00.0                        DEFAULT

Temos 8 dias de retenção e um intervalo de coleta a cada 1 hora, vamos diminuir esta retenção visto que não é necessário esta quantidade de dias de armazenamento. A retenção do AWR de cada database deve ser analisada conforme sua necessidade. 

Alterando a retenção X intervalo:

interval = minutes
retention = seconds ( 60*24*3 dias)

SQL> exec dbms_workload_repository.modify_snapshot_settings ( interval => 60, retention => 4320);
 
PL/SQL procedure successfully completed.

Após o ajuste de retenção do AWR basta aguardarmos o database realizar o gerenciamento conforme novas políticas implementadas removendo assim os snpahosts antigos.

Dia seguinte..Confirmando se os snaphosts já estão conforme retenção ajustada:

SQL> set lines 190
SQL> select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1;
 
   SNAP_ID BEGIN_INTERVAL_TIME                                                         END_INTERVAL_TIME
---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
      7038 07-FEB-12 02.00.15.335 AM                                                   07-FEB-12 03.00.02.927 AM
      7038 07-FEB-12 02.00.15.434 AM                                                   07-FEB-12 03.00.03.023 AM
      7039 07-FEB-12 03.00.02.927 AM                                                   07-FEB-12 04.00.56.232 AM
      7039 07-FEB-12 03.00.03.023 AM                                                   07-FEB-12 04.00.56.351 AM
 
...
   SNAP_ID BEGIN_INTERVAL_TIME                                                         END_INTERVAL_TIME
---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
      7117 10-FEB-12 08.00.00.169 AM                                                   10-FEB-12 09.00.07.137 AM
      7118 10-FEB-12 09.00.07.137 AM                                                   10-FEB-12 10.00.04.696 AM
      7118 10-FEB-12 09.00.07.170 AM                                                   10-FEB-12 10.00.04.835 AM

Feito estas duas atividades sobre o AWR e OPTSTAT basta realizarmos um move e rebuild das tabelas e indices da SYSAUX que agora apresentam fragmentação devido as remoções ocorridas. 

Para verificar a fragmentação/objetos fragmentados devemos executar um segment advisor utilizando o Enterprise Manager ou via SQL conforme artigo – Segment Advisor via SQL

Agora vejamos como ficou os ocupantes da tablespace SYSAUX: Observem que liberamos praticamente 13GB de espaço.

SQL> set linesize 120
SQL> set pagesize 100
SQL> COLUMN "Item" FORMAT A25
SQL> COLUMN "Space Used (GB)" FORMAT 999.99
SQL> COLUMN "Schema" FORMAT A25
SQL> COLUMN "Move Procedure" FORMAT A40
SQL>
SQL> SELECT  occupant_name "Item",
  2  space_usage_kbytes/1048576 "Space Used (GB)",
  3  schema_name "Schema"
  4  FROM v$sysaux_occupants
  5  ORDER BY 1;
 
Item                      Space Used (GB) Schema
------------------------- --------------- -------------------------
AO                                    .00 SYS
AUDIT_TABLES                          .00 SYS
AUTO_TASK                             .00 SYS
EM                                    .50 SYSMAN
EM_MONITORING_USER                    .00 DBSNMP
EXPRESSION_FILTER                     .00 EXFSYS
JOB_SCHEDULER                         .00 SYS
LOGMNR                                .01 SYSTEM
LOGSTDBY                              .00 SYSTEM
ORDIM                                 .00 ORDSYS
ORDIM/ORDDATA                         .00 ORDDATA
ORDIM/ORDPLUGINS                      .00 ORDPLUGINS
ORDIM/SI_INFORMTN_SCHEMA              .00 SI_INFORMTN_SCHEMA
PL/SCOPE                              .00 SYS
SDO                                   .00 MDSYS
SM/ADVISOR                            .10 SYS
SM/AWR                               2.40 SYS
SM/OPTSTAT                            .21 SYS
SM/OTHER                              .01 SYS
SMON_SCN_TIME                         .00 SYS
SQL_MANAGEMENT_BASE                   .00 SYS
STATSPACK                             .00 PERFSTAT
STREAMS                               .00 SYS
TEXT                                  .00 CTXSYS
TSM                                   .00 TSMSYS
ULTRASEARCH                           .00 WKSYS
ULTRASEARCH_DEMO_USER                 .00 WK_TEST
WM                                    .01 WMSYS
XDB                                   .06 XDB
XSAMD                                 .00 OLAPSYS
XSOQHIST                              .00 SYS
 
31 rows selected.

Outras informações sobre o gerenciamento de espaço na SYSAUX podem ser encontrados no MOS (My Oracle Support):

Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER [ID 329984.1]