실행계획

실행 계획이란?

• Sql Statement가 요구한 데이터를 추출하기 위해 DBMS가 차례로 수행하는 Access 과정
• 통계정보를 기반으로 다양한 Access Path를 수립하고 그 중 Cost가 가장 낮은 Access Path를 결정
• 옵티마이저에 의한 Plan이 잘못 수립될 가능성이 있기때문에 이해하고 제어하는 것이 필요

 

실행 계획 확인하는 방법

• EXPLAIN PLAN : 쿼리 실행 계획을 생성하는 데 사용되는 SQL문으로, 쿼리를 실제로 실행하지 않고 실행 계획을 생성할 수 있다. 생성된 실행 계획은 'PLAN_TABLE'이라는 테이블에 저장된다.
• SET AUTUTRACE : 쿼리를 실행한 후 자동으로 실행 계획을 출력하는 기능을 활성화하는 명령어로, 쿼리 실행 결과와 실행 계획을 함께 확인할 수 있다.
• SQL Trace : 오라클 세션에서 실행되는 모든 SQL 문의 세부 정보와 실행 계획을 추적하는데 사용되는 방법으로, 수행한 SQL의 통계 정보, 수행 시간, 결과 등을 TRACE로 기록하여 이를 파일 형태로 저장한다.

 

실행 계획 보는 법

1. 위에서 아래로 읽어 내려가면서 제일 먼저 읽을 스텝 찾기

2. 내려가는 과정에서 같은 들여쓰기가 존재하면, 무조건 위에서 아래 순으로 읽기

3. 읽고자 하는 스텝보다 들여쓰기가 된 하위 스텝이 존재한다면, 가장 안쪽으로 들여쓰기 된 스텝을 시작으로 하여 한 단계씩 상위 스텝으로 읽어 나오기

 

실행 계획 해석

5번 : PK_EMP 인덱스를 사용하여 INDEX RANGE SCAN을 하면서 조건에 만족하는 인덱스 블록과 키 값을 검색한 결과를 반환.
4번 : 5번에서 읽은 ROWID를 기반으로 EMP 테이블로 이동하여 조건에 부합하는 결과를 반환.
6번 : PK_DEPTNO 인덱스에서 INDEX UNIQUE SCAN 방식으로 검색한 결과의 ROWID를 반환.
3번 : 4번과 6번에서 반환된 데이터들을 기준으로 NESTED LOOP JOIN 방식으로 4번에서 반환된 데이터의 숫자만큼 반복하여 조인한 결과를 반환.
7번 : DEPT 테이블도 4번과 같이 조건에 부합하는 결과를 반환.
2번 : NESTED LOOP JOIN 방식으로 3번과 같이 JOIN의 결과를 만듦.

8번 : SALGRADE 서브쿼리를 실행
1번 : 서브쿼리를 통해 해당 조건을 만족하는 데이터를 필터링하여 반환.

 

[예시]

 

실행 계획이 제공하는 정보

• Operation : 데이터베이스에서 수행되는 작업을 나타냄.(ex. TABLE ACCESS FULL, HASH JOIN 등)
• Options : 작업에 관련된 추가 옵션 정보를 제공함.(ex. TABLE ACCESS의 경우 FULL, BY INDEX ROWID 등의 옵션이 존재)
• Optimizer : 쿼리 최적화기가 사용한 최적화 방법을 나타냄. (ex. CHOOSE, ANALYZED 등)
• Object Name : 작업에 관련된 데이터베이스 객체(테이블, 인덱스 등)의 이름을 나타냄.
• Object Type : 작업에 관련된 데이터베이스 객체의 유형을 나타냄.(ex. TABLE, INDEX, CLUSTER 등)
• Depth : 실행 계획 트리에서 작업의 깊이를 나타냄.
• Cost : 각 작업에 대한 최적화기가 예측한 비용을 나타냄. 비용은 일반적으로 I/O 작업 수를 기준으로 산정되며, 낮은 비용이 더 효율적인 쿼리를 의미함.
• Cpu Cost : 쿼리 실행 계획에서 각 작업에 대한 예상 CPU 사용량을 나타냄.
• Search Columns : 인덱스에서 검색되는 열의 수를 나타냄.
• Bytes : 각 작업에서 처리되는 예상 데이터 크기를 나타냄.
• Rows : 각 작업에서 처리되는 예상 행 수를 나타냄.
• Partition Id : 해당 작업이 실행되는 파티션의 ID를 나타냄.
• Partition Start : 범위 파티션에서 작업이 시작되는 파티션의 시작 값을 나타냄.
• Partition Stop : 범위 파티션에서 작업이 종료되는 파티션의 종료 값을 나타냄.

• Access Predicate : 데이터베이스 객체(ex. 테이블, 인덱스 등)에 대한 액세스 조건을 제공함. 해당 조건은 데이터베이스에서 읽어야 할 행을 줄이기 위해 적용되며, 일반적으로 인덱스 액세스에서 사용됨.
• Filter Predicate : Access Predicate에 의해 검색된 행을 추가로 필터링하는 조건을 나타냄. Access Predicate로 걸러진 후 적용되며 결과 집합을 축소함.
• Join Predicate : 두 개 이상의 테이블이 결합될 때 적용되는 조건을 나타냄, 이 조건은 보통 두 테이블 간의 공통 열을 기준으로 결합을 수행함.
• Start/Stop Predicate : Index Range Scan에서 사용되는 시작 및 종료 조건을 나타냄. 이 조건은 인덱스를 통한 데이터 엑세스 범위를 지정하는데 사용됨.

 

실행 계획을 확인하는 것은 쿼리 성능 최적화, 문제 진단, 통계 정보 확인, 옵티마이저 동작 이해 등의 이유로 중요하다. 이를 통해 쿼리 성능을 향상시키고, 데이터베이스의 전체적인 성능 및 안정성을 개선할 수 있다.

 

 

 

 

참고

https://coding-factory.tistory.com/744