Собрать статистику как отложенную (pending), протестировать и опубликовать

Команда

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

Что делает

При preference PUBLISH=FALSE собранная статистика записывается в отложенную область, а не в словарь, поэтому другие сессии не затрагиваются. Планы проверяются в тестовой сессии с optimizer_use_pending_statistics, затем статистика публикуется или отбрасывается одной командой.

Когда использовать

Чтобы без риска проверить влияние обновлённой статистики на планы критичных OLTP-таблиц в production, и для контролируемого переключения после крупной загрузки данных.

Внимание

Лицензия не требуется. Пока остаётся PUBLISH=FALSE, статистика, собранная автоматической задачей, тоже накапливается как отложенная и никогда не публикуется; не забудьте вернуть preference в TRUE. EXPLAIN PLAN — это оценка; по возможности выполните запрос и проверьте DISPLAY_CURSOR.