통계를 pending으로 수집하여 테스트한 후 publish

명령어

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

하는 일

PUBLISH=FALSE 선호 설정이 있으면 수집된 통계는 딕셔너리가 아닌 pending 영역에 기록되어 다른 세션에 영향을 주지 않는다. optimizer_use_pending_statistics를 사용하는 테스트 세션에서 계획을 검증한 후 한 명령으로 publish하거나 폐기한다.

사용 시점

운영 환경의 중요 OLTP 테이블에서 갱신된 통계가 계획에 미치는 영향을 위험 없이 테스트하고, 대량 데이터 적재 후 통제된 전환을 위해.

주의

라이선스가 필요 없다. PUBLISH=FALSE가 남아 있는 동안 자동 작업이 수집한 통계도 pending으로 쌓이고 publish되지 않으므로 선호 설정을 TRUE로 되돌리는 것을 잊지 않는다. EXPLAIN PLAN은 추정치이므로 가능하면 쿼리를 실행하고 DISPLAY_CURSOR로 확인한다.