Analizar crecimiento histórico de Tablespaces en AWR Warehouse

El manejo eficiente del almacenamiento en bases de datos Oracle es una tarea crítica para los administradores de bases de datos (DBAs). El análisis del crecimiento diario de los tablespaces permite anticipar problemas de espacio y planificar estrategias de escalabilidad.

En este artículo, exploraremos cómo AWR Warehouse nos ayuda a realizar este análisis y cómo esta herramienta puede ser un aliado para gestionar el crecimiento de los datos a lo largo del tiempo.

¿Qué es AWR Warehouse? AWR Warehouse (Automatic Workload Repository Warehouse) es una funcionalidad de Oracle Enterprise Manager que permite centralizar los datos de rendimiento histórico de múltiples bases de datos. En lugar de mantener las estadísticas del AWR en cada base de datos de manera aislada, AWR Warehouse recopila y almacena los datos en una base de datos centralizada.

Características principales:

• Retención a largo plazo: Mientras el AWR estándar mantiene datos históricos por un tiempo limitado, AWR Warehouse permite conservar esta información por años.

• Consolidación de datos: Agrupa información de rendimiento de múltiples bases de datos en un solo repositorio.

• Análisis avanzado: Permite identificar patrones de uso y crecimiento a lo largo del tiempo mediante herramientas analíticas.

• Planificación proactiva: Ayuda a realizar estimaciones para futuras necesidades de capacidad basándose en tendencias históricas.

¿Por qué usar AWR Warehouse para analizar el crecimiento de un tablespace?

• Perspectiva histórica amplia: Al consolidar datos históricos, puedes observar tendencias de uso de tablespaces durante meses o incluso años.

• Identificación de patrones de crecimiento: AWR Warehouse permite visualizar si el crecimiento de un tablespace es lineal, exponencial o irregular, lo que facilita la planificación.

• Optimización de recursos: Con datos confiables, puedes ajustar configuraciones de almacenamiento y prever la necesidad de recursos adicionales antes de que ocurran problemas.

• Análisis centralizado: Si gestionas varias bases de datos, puedes comparar el uso de tablespaces entre entornos desde un solo lugar.

Para ello he desarrollado la siguiente consulta la cual nos mostrará tanto el tamaño en un punto en el tiempo, el espacio utilizado y el crecimiento diario.

La consulta está diseñada para analizar el crecimiento diario de un tablespace en una base de datos PDB (Pluggable Database) utilizando el repositorio de datos de rendimiento de AWR (Automatic Workload Repository). Se divide en varias etapas mediante el uso de CTEs (Common Table Expressions), lo que facilita su comprensión y modularidad. Además, será explotada a través de metabase (preparada para ello), donde podremos ver gráficamente el crecimiento diario.

WITH 
pdbs as (
    select distinct dbid,pdb_name,con_dbid from  DBA_HIST_PDB_INSTANCE where pdb_name = {{pdb_name}}
),
snapshots AS (
    SELECT 
        TRUNC(s.END_INTERVAL_TIME) AS snap_date,
        p.pdb_name pdb_name,
        tsu.tablespace_id,
        MAX(tsu.tablespace_size) * df.block_size AS tablespace_size, -- Tamaño actual
        MAX(tsu.tablespace_usedsize) * df.block_size AS tablespace_usedsize, -- Tamaño usado
        df.tsname AS tablespace_name
    FROM 
        dba_hist_tbspc_space_usage tsu
        JOIN dba_hist_snapshot s ON tsu.snap_id = s.snap_id AND tsu.dbid = s.dbid
        JOIN dba_hist_datafile df ON tsu.dbid = df.dbid AND tsu.tablespace_id = df.ts#
        join dbsnmp.caw_dbid_mapping m ON s.dbid = m.new_dbid
        JOIN pdbs p ON tsu.con_dbid= p.con_dbid
    WHERE 
        s.END_INTERVAL_TIME between {{fecha_inicial}} and ({{fecha_final}} + 1)
        and m.target_name = {{target_name}}
        and tsname not in  ('UNDO1','UNDO2','TEMP')
    GROUP BY 
        TRUNC(s.END_INTERVAL_TIME), tsu.tablespace_id, df.block_size, df.tsname, p.pdb_name
),
growth AS (
    SELECT 
        snap_date,
        pdb_name,
        tablespace_name,
        tablespace_usedsize / 1024 / 1024 AS tablespace_usedsize_mb,
        tablespace_size / 1024 / 1024 AS tablespace_size_mb,
        NVL((
            tablespace_usedsize - LAG(tablespace_usedsize) OVER (PARTITION BY tablespace_name ORDER BY snap_date)
        ) / 1024 / 1024, 0) AS daily_growth_mb
    FROM 
        snapshots
)
SELECT 
    pdb_name,
    snap_date,
    tablespace_name,
    ROUND(tablespace_size_mb, 2) AS tablespace_size_mb,
    ROUND(tablespace_usedsize_mb, 2) AS tablespace_usedsize_mb,
    ROUND(daily_growth_mb, 2) AS daily_growth_mb
FROM 
    growth
WHERE daily_growth_mb > 0
ORDER BY 
    snap_date

Yo aquí veo comportamiento a analizar. Seguro que tú también verdad?

Esta consulta aprovecha el repositorio histórico de AWR para analizar el crecimiento de los tablespaces de una PDB específica. Al usar datos históricos almacenados en AWR Warehouse, puedes identificar tendencias de crecimiento, planificar ampliaciones de almacenamiento y optimizar la gestión del espacio en bases de datos Oracle. Este enfoque también es escalable, ya que puede aplicarse a múltiples bases de datos alojadas en una infraestructura consolidada.