문서유형ㅣ기술정보
분야ㅣ 모니터링/점검
적용제품버전ㅣ 7FS02, 7FS02PS
문서번호ㅣTMOTI003
개요
dbms_xplan.display_cursor 는 Physical Plan Cache에 등록되어 있는 플랜을 SQL_ID 값을 통해 조회하는 함수입니다.
이 함수를 이용해 Physical Plan Cache에 등록된 SQL 실행 계획(Plan)을 확인하는 방법에 대해 설명합니다.
방법
참고
주의
1. SQL_ID, CHILD_NUMBER 조회
SQL> select sql_id, CHILD_NUMBER, SQL_TEXT from V$SQLTEXT where sql_text like '%{확인 하고자 하는 SQL문 일부 입}%';
2. 조회한 SQL_ID, CHILD_NUMBER로 실행 계획 조회
SQL> spool xplan.txt
SQL> select * from table(dbms_xplan.display_cursor('SQL_ID',CHILD_NUMBER,'ALL');
SQL> spool off
--예시
SQL> select * from table(dbms_xplan.display_cursor('84wtrjkhtuk86',44584,'ALL'));
3. 결과 파일 확인 (xplan.txt)
| 항목 | 설명 |
|---|---|
| SQL ID | 조회한 SQL ID |
| CHILD NUMBER | 조회한 CHILD NUMBER |
| EXECUTIONS | 실행 횟수 |
| LOADED AT | 캐시에 로드된 시간 |
| TOT ELAPSED TIME | 총 소요 시간 |
| AVG ELAPSED TIME | 평균 소요 시간 |
COLUMN_VALUE ------------------------------------- SQL ID : 84wtrjkhtuk86 CHILD NUMBER : 44584 HASH VALUE : 2711439622 PLAN HASH VALUE: 1515504815 EXECUTIONS : 3 FETCHES : 574 LOADED AT : 2025/03/12 15:05:58 TOT ELAPSED TIME: 00:07:03.7238 AVG ELAPSED TIME: 00:02:21.2413
| 항목 | 설명 |
|---|---|
| Operation | 수행되는 작업 종류 |
| Name | 객체명 (테이블, 인덱스 명) |
| Cost (%CPU) | 비용 예측 값 (CPU%) |
| Rows | 실제 처리된 Rows 수 |
| Elaps. Time | 각 단계별 소요 시간 |
| CR Gets | Consistent Read 수 |
| Used Mem | 사용된 메모리 크기 |
| Temp Read / Temp Write | 임시 공간 읽기/쓰기 블록 수 |
| Reads | 물리적 디스크 블록 읽기 수 |
COLUMN_VALUE ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ID | Operation | Name | Cost (%CPU) | Cards | Rows | Elaps. Time | CR Gets | Starts | Used Mem | Temp. Read | Temp. Write| Reads | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | HASH JOIN (REVERSE LEFT OUTER) | | 261M (.01)| 55511 | 14102 |00:00:00.0460 | 112 | 1 | 1438K | 0 | 0 | 0 | | 2 | GROUP BY (SORT) | | 261M (.01)| 30145 | 12502 |00:00:00.0356 | 0 | 1 | 4687K | 0 | 0 | 0 | | 3 | HASH JOIN (SEMI) | | 261M (.01)| 30145 | 29627 |00:00:00.2338 | 0 | 1 | 5248K | 0 | 0 | 0 | | 4 | UNION ALL | | 7419 (.05)| 30939 | 34201 |00:00:00.0040 | 0 | 1 | 0K | 0 | 0 | 0 | | 5 | TABLE ACCESS (ROWID) | EMPLOY_TRAINEE_TOUR | 4364 (.02)| 22022 | 26910 |00:00:00.0132 | 815 | 1 | 0K | 0 | 0 | 0 | | 6 | INDEX (SKIP SCAN) | EMPLOY_TRAINEE_TOUR_1 | 217 (0)| 22022 | 26910 |00:00:00.1525 | 274 | 1 | 0K | 0 | 0 | 233 | | 7 | INDEX JOIN | | 3055 (.1)| 8916 | 7291 |00:00:00.0022 | 0 | 1 | 0K | 0 | 0 | 0 | | 8 | TABLE ACCESS (ROWID) | INVOLVED_COM_CHECK | 489 (0)| 2480 | 4624 |00:00:00.0026 | 212 | 1 | 0K | 0 | 0 | 0 | | 9 | INDEX (SKIP SCAN) | INVOLVED_COM_CHECK_PK | 38 (0)| 2480 | 4624 |00:00:00.0003 | 57 | 1 | 0K | 0 | 0 | 0 | | 10 | TABLE ACCESS (ROWID) | INVOLVED_COM_CHK_QT | 5 (0)| 1 | 7291 |00:00:00.0609 | 8857 | 4624 | 0K | 0 | 0 | 0 | | 11 | INDEX (RANGE SCAN) | INVOLVED_COM_CHK_QT_H | 4 (0)| 1 | 247721 |00:00:00.0360 | 8951 | 4624 | 0K | 0 | 0 | 0 | | 12 | FILTER | | 261M (.01)| 1100739 | 746694 |00:00:36.4823 | 12M | 1 | 0K | 0 | 0 | 0 | | 13 | TABLE ACCESS (FULL) | EMPLOY_TRAINEE_HIS |16939 (0)| 1100739 | 1123563 |00:00:00.1265 | 39213 | 1 | 0K | 0 | 0 | 0 | | 14 | CACHE | | 237 (0)| 0 | 1123563 |00:00:00.1504 | 0 | 1123K | 0K | 0 | 0 | 0 | | 15 | TABLE ACCESS (ROWID) | EMPLOY_TRAINEE_HIS | 237 (0)| 1 | 375089 |00:00:01.4285 | 643K | 1115K | 0K | 0 | 0 | 0 | | 16 | HASH JOIN (ANTI) | | 236 (0)| 1 | 646954 |00:00:01.1923 | 0 | 1115K | 144K | 0 | 0 | 0 | | 17 | HASH JOIN | | 6 (0)| 1 | 1124029 |00:00:03.6817 | 0 | 1115K | 78K | 0 | 0 | 0 | | 18 | INDEX (RANGE SCAN) | EMPLOY_TRAIN_HIS_3 | 3 (0)| 54 | 2194366 |00:00:04.8821 | 3400K | 1115K | 0K | 0 | 0 | 0 | | 19 | INDEX (RANGE SCAN) | EMPLOY_TRAIN_HIS_5 | 3 (0)| 54 | 1196691 |00:00:04.7531 | 3388K | 1115K | 0K | 0 | 0 | 0 | | 20 | TABLE ACCESS (ROWID) | EMPLOY_RE_HIS | 230 (0)| 223 | 473668 |00:00:02.7967 | 995K | 1115K | 0K | 0 | 0 | 0 | | 21 | INDEX (RANGE SCAN) | EMPLOY_RE_HIS | 6 (0)| 223 | 995327 |00:00:06.0843 | 4493K | 1115K | 0K | 0 | 0 | 0 | | 22 | HASH JOIN (REVERSE LEFT OUTER) | | 390K (.53)| 55511 | 14102 |00:00:00.0331 | 0 | 1 | 1453K | 0 | 0 | 0 | | 23 | TABLE ACCESS (FULL) | EMPLOY_TRAIN_ENT_EDU_HIS | 655 (.61)| 8879 | 13228 |00:00:00.0115 | 1505 | 1 | 0K | 0 | 0 | 0 | | 24 | INDEX JOIN (LEFT OUTER) | | 389K (.38)| 26658 | 14102 |00:00:00.0354 | 0 | 1 | 0K | 0 | 0 | 0 | | 25 | INDEX JOIN (LEFT OUTER) | | 386K (.29)| 6946 | 14102 |00:00:21.5471 | 335K | 1 | 0K | 0 | 0 | 0 | | 26 | HASH JOIN (LEFT OUTER) | | 358K (.27)| 6946 | 14102 |00:00:00.0265 | 0 | 1 | 9323K | 0 | 0 | 0 | | 27 | INDEX JOIN (LEFT OUTER) | | 280K (.25)| 6946 | 14102 |00:00:00.0153 | 0 | 1 | 0K | 0 | 0 | 0 | | 28 | WINDOW (SIMPLE) | | 259K (.25)| 6946 | 14102 |00:00:00.0182 | 0 | 1 | 8374K | 0 | 0 | 0 | | 29 | ORDER BY (SORT) | | 259K (.24)| 6946 | 14102 |00:00:00.0383 | 0 | 1 | 9206K | 0 | 0 | 0 | | 30 | WINDOW (SIMPLE) | | 137K (.44)| 6946 | 14102 |00:00:00.0218 | 0 | 1 | 264K | 0 | 0 | 0 | ...(중략)....