오늘은 간단히 SQL_ID 만 있으면 실행계획을 확인할 수 있는 쿼리를 공유드릴까 합니다.
운영 중인 시스템에서 성능 이슈가 발생했을 때 가장 먼저 확인해보면 좋은 부분은 실행계획(Execution Plan) 이라고 생각합니다.
- 실행계획을 빠르게 보고 싶지만 sql에 친숙하지 않은 실무자
- SQL Tuning 을 시도해보고자 하는 개발자
위 대상에 포함되는 분들은 한번씩 사용해보고 업무에 활용하거나 학습에 참고하시면 좋습니다.
-- 최종 실행계획 성능 분석 리포트 쿼리 (Tibero / Oracle 호환 + 실제 실행통계 결합) -- SQL_ID만 교체하면 바로 사용 가능
-- [1] 실행계획 조회 (V$SQL_PLAN 기준, 향후 DBA_HIST_SQL_PLAN 확장 가능)
WITH PLAN_RAW AS ( SELECT * FROM V$SQL_PLAN WHERE SQL_ID = '여기에_SQL_ID_입력' ),
-- [2] 성능 평가 룰셋 정의
RULES AS ( SELECT 'FULL' PATTERN, '❌ 비효율' GRADE, '전체 테이블 스캔 발생' AS MSG, 'WHERE 조건 보완 및 인덱스 검토' ACTION FROM DUAL UNION ALL SELECT 'INDEX FULL SCAN', '⚠️ 보통', '전체 인덱스를 스캔함', '조건절/통계 확인' FROM DUAL UNION ALL SELECT 'INDEX RANGE SCAN', '✅ 우수', '조건절이 인덱스를 적절히 활용', '적절한 인덱스 유지' FROM DUAL UNION ALL SELECT 'INDEX UNIQUE SCAN', '✅ 우수', '고유 인덱스를 활용한 단건 접근', '튜닝 불필요' FROM DUAL UNION ALL SELECT 'NESTED LOOPS', '⚠️ 주의', '조인 대상이 많을수록 성능 저하 우려', 'HASH JOIN 대체 고려' FROM DUAL UNION ALL SELECT 'HASH JOIN', '✅ 우수', '대용량 조인에 효과적', '병렬 처리와 함께 사용 시 최적' FROM DUAL UNION ALL SELECT 'CARTESIAN', '❌ 심각', '조인 조건 누락', '조인 조건 추가 필수' FROM DUAL UNION ALL SELECT 'SORT', '⚠️ 주의', '정렬 연산은 메모리 및 TEMP 사용 증가 우려', '인덱스 사용 또는 정렬 제거 검토' FROM DUAL UNION ALL SELECT 'CONNECT BY', '❌ 비효율', '계층형 재귀로 리소스 과다 소모', 'START WITH + NOCYCLE 최적화 필요' FROM DUAL UNION ALL SELECT 'MATERIALIZE', '⚠️ 보통', '서브쿼리 결과 캐시', 'REWRITE 또는 INLINE 여부 검토' FROM DUAL UNION ALL SELECT 'TABLE ACCESS (BY INDEX ROWID)', '✅ 우수', '인덱스를 통해 테이블 레코드 접근', '필요시 힌트로 유지' FROM DUAL ),
-- [3] 실제 실행 정보 결합 (가능한 경우)
PLAN_STATS AS ( SELECT SQL_ID, PLAN_HASH_VALUE, ID, CARDINALITY, COST, IO_COST, LAST_OUTPUT_ROWS AS ACTUAL_ROWS, ROUND(LAST_ELAPSED_TIME / 1000, 2) AS LAST_MS FROM V$SQL_PLAN_MONITOR WHERE SQL_ID = '여기에_SQL_ID_입력' ),
-- [4] 실행계획 분석
PLAN_ANALYZED AS ( SELECT LPAD(' ', LEVEL * 2) || TO_CHAR(P.ID, 'FM000') || '. ' || P.OPERATION || NVL2(P.OBJECT_NAME, ' [' || P.OBJECT_NAME || ']', '') AS PLAN_STEP, P.CARDINALITY, P.COST, P.IO_COST, CASE WHEN P.COST > 0 THEN ROUND((P.COST - P.IO_COST) / P.COST * 100) ELSE 0 END AS CPU, P.OPERATION, P.OBJECT_NAME, P.ACCESS_PREDICATES, P.FILTER_PREDICATES, R.GRADE, R.MSG, R.ACTION, S.ACTUAL_ROWS, S.LAST_MS FROM PLAN_RAW P LEFT JOIN RULES R ON UPPER(P.OPERATION) LIKE '%' || R.PATTERN || '%' LEFT JOIN PLAN_STATS S ON P.ID = S.ID AND P.SQL_ID = S.SQL_ID START WITH P.DEPTH = 1 CONNECT BY PRIOR P.ID = P.PARENT_ID AND PRIOR P.SQL_ID = P.SQL_ID ORDER SIBLINGS BY P.POSITION ),
-- [5] 요약 정보
SUMMARY AS ( SELECT COUNT(*) AS STEP_CNT, MAX(COST) AS MAX_COST, SUM(CARDINALITY) AS TOTAL_ROWS, SUM(ACTUAL_ROWS) AS ACTUAL_TOTAL_ROWS, SUM(LAST_MS) AS TOTAL_ELAPSED_MS FROM PLAN_ANALYZED )
-- [6] 최종 리포트 출력
SELECT '▶ 실행계획 요약: 총 단계 ' || STEP_CNT || '개, 최대 비용 ' || MAX_COST || ', 예상 Row 수 ' || TOTAL_ROWS || ', 실제 처리량 ' || NVL(ACTUAL_TOTAL_ROWS,0) || '건, 총 실행시간 ' || NVL(TOTAL_ELAPSED_MS,0) || 'ms' AS "Execution Report" FROM SUMMARY UNION ALL SELECT PLAN_STEP || ' | 예상 Rows: ' || CARDINALITY || ', 실제 Rows: ' || NVL(ACTUAL_ROWS,'-') || ', Cost: ' || COST || ', CPU: ' || CPU || '% ' || NVL(GRADE, '❔ 미분류') || CHR(10) || ' ▶ ' || NVL(MSG, '기준 없음') || CHR(10) || ' ▷ 조치: ' || NVL(ACTION, '없음') || CASE WHEN LAST_MS IS NOT NULL THEN CHR(10) || ' ⏱ 실제 실행시간: ' || LAST_MS || ' ms' ELSE '' END FROM PLAN_ANALYZED;
아래는 결과 예시입니다.
▶ 실행계획 요약: 총 단계 12개, 최대 비용 184, 예상 Row 수 13, 실제 처리량 61건, 총 실행시간 27.21ms
004. TABLE ACCESS (FULL) [EMPLOYEE] | 예상 Rows: 1000, 실제 Rows: 891, Cost: 84, CPU: 10% ❌ 비효율
▶ 전체 테이블 스캔 발생
▷ 조치: WHERE 조건 보완 및 인덱스 검토
⏱ 실제 실행시간: 5.67 ms