DBAtlas · Atlas de operaciones de bases de datos

Encontrar estadísticas obsoletas y definir preferencias de estadísticas por tabla (prefs)

OracleEstadísticas

Comando

-- 1) Flush DML counters to the dictionary and list stale objects:
EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

SELECT owner, table_name, partition_name, num_rows,
       TO_CHAR(last_analyzed,'YYYY-MM-DD HH24:MI') AS last_analyzed,
       stale_stats, stattype_locked
FROM   dba_tab_statistics
WHERE  owner = 'CUSTOMER_APP'
  AND  (stale_stats = 'YES' OR last_analyzed IS NULL)
ORDER  BY table_name, partition_name;

SELECT table_name, partition_name, inserts, updates, deletes, truncated
FROM   dba_tab_modifications
WHERE  table_owner = 'CUSTOMER_APP';

-- 2) Per-table preferences (used by the auto stats job and manual GATHER calls):
EXEC DBMS_STATS.SET_TABLE_PREFS('CUSTOMER_APP', 'SALES', 'STALE_PERCENT', '5');
EXEC DBMS_STATS.SET_TABLE_PREFS('CUSTOMER_APP', 'SALES', 'METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 254 CUSTOMER_ID');
EXEC DBMS_STATS.SET_TABLE_PREFS('CUSTOMER_APP', 'SALES', 'DEGREE', '4');

SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', 'CUSTOMER_APP', 'SALES') AS stale_pct FROM dual;
SELECT table_name, preference_name, preference_value
FROM   dba_tab_stat_prefs WHERE owner = 'CUSTOMER_APP';

-- 3) Gather only stale objects:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'CUSTOMER_APP', options => 'GATHER STALE');
-- Revert a preference: EXEC DBMS_STATS.DELETE_TABLE_PREFS('CUSTOMER_APP', 'SALES', 'STALE_PERCENT');

Qué hace

Las estadísticas se vuelven obsoletas (stale) cuando la proporción de cambios supera STALE_PERCENT (10% por defecto). Las preferencias guardan por tabla el muestreo, los histogramas, el paralelismo y los umbrales de obsolescencia para que la tarea automática nocturna de estadísticas aplique las mismas reglas.

Cuándo usarlo

Cuando el umbral del 10% se dispara demasiado tarde en tablas grandes, cuando determinadas columnas deben tener siempre histograma, y para definir la estrategia de recopilación en el diccionario en lugar de en scripts.

Precaución

No requiere licencia. Las preferencias se anulan con parámetros explícitos en una llamada GATHER manual. Cambiar METHOD_OPT añade o quita histogramas y puede cambiar planes. DBA_TAB_MODIFICATIONS solo está actualizada tras un flush (flush automático aproximadamente cada 15 minutos).

Abrir en DBAtlas →

Relacionado