Oracle 실제 실행통계로 실행계획 보기

Oracle에서 EXPLAIN PLAN이나 AUTOTRACE로 보는 실행계획은 옵티마이저가 예측한 추정 행수만 보여줍니다. 추정이 실제와 얼마나 어긋났는지 모르면 어느 조인에서 카디널리티가 틀어졌는지 짚기 어렵습니다. 쿼리에 GATHER_PLAN_STATISTICS 힌트를 달아 실행하고, DBMS_XPLAN.DISPLAY_CURSOR를 +ALLSTATS LAST 포맷으로 호출하면 추정 행수(E-Rows)와 실제 행수(A-Rows)를 한 줄에 나란히 볼 수 있습니다.

-- 힌트로 통계 수집 (세션 전체면 STATISTICS_LEVEL=ALL)
SELECT /*+ GATHER_PLAN_STATISTICS */ p.product_name
FROM   order_items o, product_information p
WHERE  o.unit_price = 15 AND o.quantity > 1
AND    p.product_id = o.product_id;

-- 방금 실행한 커서의 실제 통계 출력
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => '+ALLSTATS LAST'));

출력의 E-Rows와 A-Rows 차이가 크면 옵티마이저 통계가 오래됐거나 추정 가정이 틀린 지점입니다. A-Time은 실제 경과시간, Buffers는 논리적 I/O라 어느 단계가 비싼지 바로 드러납니다. 조회에는 V$SQL_PLAN_STATISTICS_ALL 등에 대한 SELECT 권한이 필요합니다. 통계 수집은 약간의 오버헤드가 있으니 세션이나 문장 단위로만 켜고, 운영 전체에 상시로 걸지 않는 편이 안전해 보입니다.

🔗 https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/generating-and-displaying-execution-plans.html

🔗 https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_XPLAN.html