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 já descrita aqui pelo Leandro, AWR Ocupando espaço na SYSAUX e que pode ajudar caso seu database esteja nesta versão, 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 comercial (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
Observem que 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 databae realizar o gerencimaneto 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 Metalink:
Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER [ID 329984.1]
Autor: Anderson Graf
Bacharel em Sistemas de Informação e MBA em Gestão de Banco de Dados Oracle. Entusiasta da tecnologia Oracle, ACE Associate ♠, autor em vários blogs e OTN. Consultor Oracle Senior na Exímio Soluções em TI
Envolvido em soluções de:
– Implementação, migração, gerenciamento e suporte a produtos Oracle, multiplataforma
– Monitoramento de ambientes 24×7
– Backup e Recovery
– Performance e Tuning
– Alta disponibilidade (HA) – RAC, Data Guard
– EM database/grid/cloud control
– Particionamento & Advanced Compression
– Oracle Engineered Systems – ODA, Exadata
Blog pessoal: http://www.andersondba.com.br
Articulista na Oracle Technology Network (OTN) – https://goo.gl/99R6yW
ACE Associate – https://goo.gl/MBB51b
Articulista GPO – http://profissionaloracle.com.br
Ao realizar o ajuste de retenção do AWR pode apresentar o seguinte erro:
ERROR at line 1:
ORA-13541: system moving window baseline size (691200) greater than retention (259200)
ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 174
ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 222
ORA-06512: at line 1
Para corrigi-lo baste ajustar modify_baseline_window_size. No caso do exemplo acima
SQL> exec dbms_workload_repository.modify_snapshot_settings ( interval => 60, retention => 4320);
BEGIN dbms_workload_repository.modify_snapshot_settings ( interval => 60, retention => 4320); END;
*
ERROR at line 1:
ORA-13541: system moving window baseline size (691200) greater than retention (259200)
ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 174
ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 222
ORA-06512: at line 1
SQL> exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size( window_size =>3);
PL/SQL procedure successfully completed.
SQL> exec dbms_workload_repository.modify_snapshot_settings ( interval => 60, retention => 4320);
PL/SQL procedure successfully completed.