Recopilar estadísticas como pending, probarlas y publicarlas

Comando

-- 1) Disable publishing for the table (new statistics go to pending):
EXEC DBMS_STATS.SET_TABLE_PREFS('CUSTOMER_APP', 'ORDERS', 'PUBLISH', 'FALSE');
EXEC DBMS_STATS.GATHER_TABLE_STATS('CUSTOMER_APP', 'ORDERS');

-- 2) Compare pending and published values:
SELECT owner, table_name, partition_name, num_rows,
       TO_CHAR(last_analyzed,'YYYY-MM-DD HH24:MI') AS last_analyzed
FROM   dba_tab_pending_stats
WHERE  owner = 'CUSTOMER_APP';
SELECT num_rows, last_analyzed FROM dba_tables
WHERE  owner = 'CUSTOMER_APP' AND table_name = 'ORDERS';

-- 3) Try plans with pending statistics in this session only:
ALTER SESSION SET optimizer_use_pending_statistics = TRUE;
EXPLAIN PLAN FOR SELECT COUNT(*) FROM customer_app.orders WHERE status = 7;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'BASIC +ROWS'));
ALTER SESSION SET optimizer_use_pending_statistics = FALSE;

-- 4a) If good, publish:
EXEC DBMS_STATS.PUBLISH_PENDING_STATS('CUSTOMER_APP', 'ORDERS', no_invalidate => FALSE);
-- 4b) If bad, discard:
-- EXEC DBMS_STATS.DELETE_PENDING_STATS('CUSTOMER_APP', 'ORDERS');

-- 5) Return to normal behavior:
EXEC DBMS_STATS.SET_TABLE_PREFS('CUSTOMER_APP', 'ORDERS', 'PUBLISH', 'TRUE');

Qué hace

Con la preferencia PUBLISH=FALSE, las estadísticas recopiladas se escriben en un área pending en lugar del diccionario, por lo que las demás sesiones no se ven afectadas. Los planes se validan en una sesión de prueba con optimizer_use_pending_statistics y luego se publican o descartan con un solo comando.

Cuándo usarlo

Para probar sin riesgo en producción el impacto en los planes de estadísticas actualizadas en tablas OLTP críticas, y para un cambio controlado tras una gran carga de datos.

Precaución

No requiere licencia. Mientras PUBLISH=FALSE se mantenga, las estadísticas recopiladas por la tarea automática también se acumulan como pending y nunca se publican; recuerde volver a poner la preferencia en TRUE. EXPLAIN PLAN es una estimación; cuando sea posible ejecute la consulta y revise DISPLAY_CURSOR.