Hoy vamos a lidiar con un problema muy común en las bases de datos de Oracle que es el tamaño del tablespace SYSAUX.
El tablespace SYSAUX es uno de los tablespaces especiales de la base de datos con el que no podemos jugar alegremente, la principal característica que tiene de cara a la administración de base de datos es que ,el espacio que le demos se ha perdido, es decir, no va ha haber forma de hacerlo mas pequeño (al menos en mi caso siempre ha habido un «pero» que lo imposibilita).

Como decía al principio es junto con el SYSTEM un tablespace muy especial, y los motivos de su tamaño pueden ser varios,hoy vamos a centrarnos en como vaciar un poco el tamaño debido el AWR.

Lo que, lo primero que tendremos que hacer es ver cual es el componente que esta llenándonos el tablespace.
Para ello tenemos la consulta

set linesize 100;
column OCCUPANT_NAME format a20;
column OCCUPANT_DESC format a60;
column Mb format 999.99
SELECT occupant_name,space_usage_kbytes/1024 Mb,occupant_desc
  FROM V$SYSAUX_OCCUPANTS
  order by space_usage_kbytes  desc;

OCCUPANT_NAME             MB OCCUPANT_DESC
-------------------- ------- ------------------------------------------------------------
SM/AWR                780.06 Server Manageability - Automatic Workload Repository
SM/OPTSTAT            333.00 Server Manageability - Optimizer Statistics History
LOGMNR                 91.63 LogMiner
XDB                    85.50 XDB
SM/ADVISOR             65.56 Server Manageability - Advisor Framework
WM                      6.38 Workspace Manager
SM/OTHER                6.25 Server Manageability - Other Components
SMON_SCN_TIME           5.31 Transaction Layer - SCN to TIME mapping
EXPRESSION_FILTER       3.88 Expression Filter System
JOB_SCHEDULER           2.81 Unified Job Scheduler
EM_MONITORING_USER      2.75 Enterprise Manager Monitoring User
SQL_MANAGEMENT_BASE     1.69 SQL Management Base Schema
PL/SCOPE                1.56 PL/SQL Identifier Collection
XSOQHIST                1.38 OLAP API History Tables
AO                      1.38 Analytical Workspace Object Table
LOGSTDBY                1.38 Logical Standby
STREAMS                 1.00 Oracle Streams
AUTO_TASK                .31 Automated Maintenance Tasks
ORDIM/ORDDATA            .00 Oracle Multimedia ORDDATA Components
ORDIM/ORDPLUGINS         .00 Oracle Multimedia ORDPLUGINS Components
ORDIM/SI_INFORMTN_SC     .00 Oracle Multimedia SI_INFORMTN_SCHEMA Components
HEMA
EM                       .00 Enterprise Manager Repository
TEXT                     .00 Oracle Text
ULTRASEARCH              .00 Oracle Ultra Search
ORDIM                    .00 Oracle Multimedia ORDSYS Components
SDO                      .00 Oracle Spatial
STATSPACK                .00 Statspack Repository
TSM                      .00 Oracle Transparent Session Migration User
XSAMD                    .00 OLAP Catalog
AUDIT_TABLES             .00 DB audit tables
ULTRASEARCH_DEMO_USE     .00 Oracle Ultra Search Demo User

Como podemos ver, son muchas las cosas que se guardan en SYSAUX, pero, en nuestro caso, está bastante claro que, la mayoría de la ocupación es debida a los AWR.

Nuestro siguientes pasos van a ser:

• Determinar que es lo que nos ocupa el espacio
• Mirar si la causa esta en la retención
• Mirar si la causa está en los AWR guardados
• Mirar si la causa está en las lineas «huerfanas» de las Active Session History

Nuestro objetivo va a ser liberar espacio dentro de SYSAUX , no disminuir el tamaño de este tablespace ya que, como decía al principio esto es prácticamente una misión imposible.

Determinar que es lo que ocupa espacio

El siguiente paso será el buscar la información del AWR, para ello el propio Oracle tiene una utilidad que podemos llamar desde sqlplus

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

Este sql nos genera un informe con unos apartados muy interesantes que nos servirán para encontrar de donde liberar espacio.

Mirar si la causa esta en la retención

************************************                                                                         
(2) Size estimates for AWR snapshots                                                                          
*************************************                                                                                                                                                                                    
| Estimates based on 60 mins snapshot INTERVAL:                                                               
|    AWR size/day                           31.0 MB (1,324 K/snap * 24 snaps/day)                             
|    AWR size/wk                           217.2 MB (size_per_day * 7) per instance                                                                                                                                     
| Estimates based on 24 snaps in past 24 hours:                                                               
|    AWR size/day                           31.0 MB (1,324 K/snap and 24 snaps in past 24 hours)              
|    AWR size/wk                           217.2 MB (size_per_day * 7) per instance                           
|

Aqui podemos ver el tamaño estimado que ocupa nuestra información de los AWR, en función del periodo de retencion que tengamos de los AWR tendremos la ocupación en la base de datos.
Cuando sabemos cuanto ocupa cada AWR y cuanto hacemos al día, tenemos que saber cuantos dias guardamos para ver si la ocupación es correcta, para ello haremos :

SQL> SELECT retention FROM dba_hist_wr_control;

RETENTION
---------------------------------------------------------------------------
+00007 00:00:00.0

En mi caso por ejemplo, la retencion es de 7 días.

Mirar si la causa está en los AWR guardados

Otro de los problemas que podemos tener es que el número de AWR que mantenemos guardados por razones históricas en la instancia, en el informe que acabamos de obtener también aparece un apartado con las últimas 50 snapshots de la base de datos, el eliminar imágenes antiguas también nos ayudará a liberar espacio.

Algunas veces el Active Session History (ASH) guarda filas que no pertenecen a ningún AWR,esto hace que el ASH ocupe gran parte del espacio dedicado al AWR, la forma de saber si esto nos está sucediendo es mirar nuestro informe en el apartado

**********************************
(3a) Space usage by AWR components (per database)
**********************************

COMPONENT        MB  % AWR  KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX%                                
--------- --------- ------ ------------ ---------- ----------- ----------------                               
FIXED         131.5   50.6          670       15.7       109.9    52% : 48%                                   
EVENTS         37.9   14.6          193        4.5        31.7    43% : 57%                                   
SQLPLAN        25.0    9.6          127        3.0        20.9    72% : 28%                                   
ASH            18.3    7.0           93        2.2        15.3    89% : 11%                                   
SQL            11.0    4.2           56        1.3         9.2    60% : 40%                                   
SPACE          10.3    4.0           53        1.2         8.6    61% : 39%                                   
SQLTEXT         5.2    2.0           26        0.6         4.3    96% : 4%                                    
SQLBIND         0.8    0.3            4        0.1         0.6    50% : 50%                                   
RAC             0.6    0.2            3        0.1         0.5    50% : 50%                                   

Si la línea de ASH ocupa mas de un 1% probablemente sea la causa de nuestra ocupación.
Mediante la siguiente consulta podemos saber si es nuestro caso.

SELECT COUNT(*) huerfanas
FROM wrh$_active_session_history a
WHERE NOT EXISTS
  (SELECT *
  FROM wrm$_snapshot
  WHERE snap_id       = a.snap_id
  AND dbid            = a.dbid
  AND instance_number = a.instance_number
  );


 HUERFANAS
----------
       116

Si aparece alguna línea podemos borrarlas con

delete  
FROM wrh$_active_session_history a
WHERE NOT EXISTS
  (SELECT *
  FROM wrm$_snapshot
  WHERE snap_id       = a.snap_id
  AND dbid            = a.dbid
  AND instance_number = a.instance_number
  );

Con estas tres acciones, si elproblema de laocupación del SYSAUX es debido a el AWR tendremos que haberpodido liberar el espacio suficiente para que la base de datos pueda seguir trabajando sin problemas de espacio.

Aún así, el tema de la ocupación del SYSAUX es un tema bastante complejo, con lo que, como siempre, el mejor sitio para buscar soluciones es soporte de Oracle, aquí tenéis un pequeño listado de algunas de las notas que puede que os aplique y cuyo contenido tenéis en la página de soporte

• AWR Data Uses Significant Space in the SYSAUX Tablespace [ID 287679.1]
• Suggestions if your SYSAUX Tablespace grows rapidly or too large [Document 1292724.1]
• General Guidelines for SYSAUX Space Issues [Document 552880.1]
• SYSAUX Grows Because Optimizer Stats History is Not Purged [Document 1055547.1]
• Space issue in Sysaux tablespace due to Unexpected AWR size [Document 1218413.1]
• Space Management In Sysaux Tablespace with AWR in Use [Document 287679.1]
• SYSAUX Tablespace Grows Heavily Due To AWR [Document 852028.1]

O alguna entrada muy interesante en algunos blogs como es

Why is my SYSAUX Tablespace so Big? Statistics_level=ALL