Collecter les statistiques en attente (pending), les tester et les publier

Commande

-- 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');

Ce que fait la commande

Avec la préférence PUBLISH=FALSE, les statistiques collectées sont écrites dans une zone d'attente au lieu du dictionnaire, donc les autres sessions ne sont pas affectées. Les plans sont validés dans une session de test avec optimizer_use_pending_statistics, puis publiés ou abandonnés en une commande.

Quand l'utiliser

Pour tester sans risque en production l'impact sur les plans de statistiques rafraîchies sur des tables OLTP critiques, et pour une bascule contrôlée après un gros chargement de données.

Attention

Aucune licence requise. Tant que PUBLISH=FALSE reste en place, les statistiques collectées par la tâche automatique s'accumulent aussi en attente et ne sont jamais publiées ; pensez à remettre la préférence à TRUE. EXPLAIN PLAN est une estimation ; si possible, exécutez la requête et vérifiez DISPLAY_CURSOR.