systemdba

Detección de discrepancias en estadísticas de Oracle Database

En Oracle Database, el Optimizador Basado en Costos (CBO) utiliza estadísticas para generar planes de ejecución eficientes. Si estas estadísticas no representan el estado actual de los datos, el rendimiento de las consultas SQL puede disminuir significativamente.

El problema principal ocurre cuando la columna NUM_ROWS en las vistas DBA_TABLES o DBA_TAB_PARTITIONS no coincide con el número real de registros. Esta diferencia se conoce como discrepancia de cardinalidad.

¿Por qué ocurre la diferencia?

Según la documentación oficial (Oracle Database Reference), la columna NUM_ROWS no es un contador en tiempo real. Este valor solo se actualiza cuando se ejecuta el paquete DBMS_STATS. Si una tabla recibe muchas operaciones de inserción o borrado, la información en el diccionario de datos se vuelve obsoleta (stale).

Diagnóstico mediante PL/SQL

Para identificar estos objetos, he desarrollado un bloque PL/SQL. Este script compara el valor del diccionario contra un conteo real (COUNT(*)) y determina si es necesario actualizar las estadísticas basándose en un umbral porcentual.

SET SERVEROUTPUT ON;
DECLARE
    -- Configure las variables de búsqueda aquí
    v_owner          VARCHAR2(30) := 'MY_SCHEMA';   -- Propietario del esquema
    v_table_name     VARCHAR2(30) := 'MY_TABLE';    -- Nombre de la tabla
    v_threshold_pct  NUMBER       := 10;            -- Umbral de diferencia (10%)
    v_dict_rows      NUMBER;
    v_actual_rows    NUMBER;
    v_diff_pct       NUMBER;
    v_sql            VARCHAR2(1000);
BEGIN
    -- Obtener el número de filas desde el diccionario de datos
    SELECT num_rows 
    INTO v_dict_rows
    FROM dba_tables 
    WHERE owner = UPPER(v_owner) 
      AND table_name = UPPER(v_table_name);
    -- Ejecutar el conteo real de la tabla
    v_sql := 'SELECT count(*) FROM ' || v_owner || '.' || v_table_name;
    EXECUTE IMMEDIATE v_sql INTO v_actual_rows;
    -- Calcular el porcentaje de diferencia
    IF v_actual_rows > 0 THEN
        v_diff_pct := ABS(v_dict_rows - v_actual_rows) / v_actual_rows * 100;
    ELSE
        v_diff_pct := CASE WHEN v_dict_rows > 0 THEN 100 ELSE 0 END;
    END IF;
    -- Mostrar resultados
    DBMS_OUTPUT.PUT_LINE('Rows in Dictionary: ' || v_dict_rows);
    DBMS_OUTPUT.PUT_LINE('Actual Rows (Count): ' || v_actual_rows);
    DBMS_OUTPUT.PUT_LINE('Difference Percent: ' || ROUND(v_diff_pct, 2) || '%');
    -- Generar recomendación
    IF v_diff_pct > v_threshold_pct THEN
        DBMS_OUTPUT.PUT_LINE('Status: Re-analyze required.');
        DBMS_OUTPUT.PUT_LINE('Command: EXEC DBMS_STATS.GATHER_TABLE_STATS(''' || v_owner || ''',''' || v_table_name || ''');');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Status: Statistics are up to date.');
    END IF;
END;
/

Comparar el diccionario con la realidad es una práctica esencial de mantenimiento. Al automatizar esta validación, el administrador de base de datos (DBA) puede garantizar que el optimizador siempre tenga datos precisos para elegir el mejor plan de ejecución.

Dejar un comentario