Explain Plan, 실행계획

■ Explain Plan
  - Oracle Optimizer가 SQL 문을 실행하는 데 사용하는 실행 계획을 생성하는 도구.
  - SQL 문을 실행하지 않고, 단순히 어떤 방식으로 실행할지에 대한 계획을 생성하여 확인 가능하다.
  - 실행 계획은 `PLAN_TABLE` 또는 `sys.plan_table$`에 저장되며, `utlxplan.sql` 스크립트를 사용하여 생성된 계획을 조회할 수 있다.

 

1. 실행 계획 저장

  - `EXPLAIN PLAN` 결과는 `PLAN_TABLE` 또는 `sys.plan_table$`에 저장된다.
  - 기본적으로 10g 버전부터는 DB 설치시 `sys.plan_table$` 테이블이 만들어진다.

2. 사용방법

  - `EXPLAIN PLAN` 문을 사용하여 SQL 문의 실행 계획을 확인한다.
  - 실행 계획은 `SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);` 등의 쿼리로 확인 가능.

3. 스크립트 위치

  - `utlxplan.sql` 스크립트는 `$ORACLE_HOME/rdbms/admin/` 디렉토리에 위치.
  - 해당 스크립트를 통해 `PLAN_TABLE`을 생성할 수 있다.

4. 버전별 차이

  - 10g 버전부터는 DB 설치시 `sys.plan_table$` 테이블이 자동으로 생성된다.
  - 이전 버전에서는 `PLAN_TABLE`을 생성해야 했지만, 이제는 기본으로 제공.

5. 주의 사항

  - `EXPLAIN PLAN`은 SQL 문을 실행하지 않고 실행 계획을 생성하므로, 실제 성능에 영향을 미치지 않는다.
  - 실행 계획을 통해 옵티마이저가 어떤 방식으로 쿼리를 처리할지 분석 가능하다.
  - 실행 계획 분석을 통해 SQL 튜닝이 가능하며, 성능 최적화에 도움이 된다.
  - `DBMS_XPLAN.DISPLAY` 함수를 사용하면 보다 간편하게 실행 계획을 확인할 수 있다.

 

select *
from all_synonyms
where synonym_name = 'PLAN_TABLE';

#) F10 (단축키)와 같은 쿼리문

=> 실행계획이 PLAN_TABLE$ 에 저장된다. 

explain plan for select * from hr.employees where employee_id = 100;

 

#) 실행계획 : basic 확인

select * from table(dbms_xplan.display(null,null,'basic'));

#) hr 계정에서도 가능하다.

explain plan for select * from hr.employees where employee_id = 100;

#) 실행계획 basic 으로 보기

select * from table(dbms_xplan.display(null,null,'basic'));

▷Segment은 데이터를 저장하는 논리적 단위로, Latch를 통해 공유 자원에 접근하며 Root-Branch-Leaf 구조의 블록에 커서를 사용하여 접근하는 과정에서 User Process가 상호 작용함.

 

#) 실행계획 typical 으로 보기

select * from table(dbms_xplan.display(null,null,'typical'));

#설명)
1) Id: 각 Operation 번호, Id * 이 있는 경우 Predicate Information 에 access(block 위치 알고 있는 경우), filter(block 위치 모르고 있는 겨우) 에 관한 정보
2) Operation : 실행되는 job, 아래부터 해석(숫자가 큰 수부터/ Operation의 Name이 안쪽으로 쓰임부터)
3) Name : Operation이 access 하는 테이블, 인덱스 
4) Rows : 각 Operation이 끝났을 때 return 되는 행의 수 (예상치/누적치)
5) Byte : Operation 이 수행했을때 byte 값(예상치)
6) Cost (%CPU) : 각 Operation의 cost, 누적치
7) Time : 각 Operation의 수행시간, 누적치(예상치)

#) Access Predicate 및 Filter Predicate
1) Access predicate

- 정의: 찾고자 하는 데이터 행이 있는 블록의 위치를 알고 있을 때, 해당 블록을 효율적으로 읽기 위한 방법을 결정하는 조건

- 활용: 블록의 위치 정보를 활용하여 불필요한 블록을 읽지 않고 필요한 블록만 읽음.

2) Filter predicate

- 정의: 실제 블록을 읽은 후에 데이터를 걸러내기 위해 사용되는 조건. 블록의 위치 정보를 모르는 상황에서 특정 행을 찾기 위한 조건.

- 활용: 블록을 읽은 후에 데이터를 조건에 따라 걸러내어 최종 결과를 형성함.

3) Access + Filter 조합

- Access Predicate와 Filter Predicate가 동시에 사용되는 경우, 먼저 Access Predicate를 확인한 후, 그 결과에 따라 Filter Predicate를 확인함.

- Access Predicate가 먼저 적용되면 필요한 블록을 효율적으로 읽고, 그 후 Filter Predicate를 사용하여 데이터를 걸러냄.

※ 주의 사항 

- Access Predicate는 데이터의 위치 정보를 활용하여 블록을 선택하는데 사용되며, 효율적인 데이터 검색을 도와줌.
- Filter Predicate는 블록을 읽은 후에 데이터를 실제로 걸러내는 조건으로, 위치 정보를 모를 때 유용함.
- Access + Filter를 함께 사용할 때, Access Predicate의 결과에 따라 Filter Predicate를 적용하여 최종 결과를 형성함.
- 쿼리의 성능을 향상시키기 위해서는 적절한 Access 및 Filter Predicate를 사용하는 것이 중요함.

#) 문장 id 로 지정했을 경우, test1

explain plan set statement_id = 'demo1' for select * from hr.employees where employee_id = 100;

#) select * from table(dbms_xplan.display('플랜 테이블', '문장 아이디/null 이 보편적','typical'));

select * from table(dbms_xplan.display('PLAN_TABLE', 'demo1','typical'));

#) 'typical'을 'all'로 수정했을 경우
-> 컬럼들의 정보들도 확인 : leaf block

select * from table(dbms_xplan.display('PLAN_TABLE', 'demo1','all'));

#) 'outline' : 내부적으로 실행계획을 생성하기 위한 힌트들 보기 위함

select * from table(dbms_xplan.display('PLAN_TABLE', 'demo1','outline'));

#) 'advanced': 전체 확인 가능(힌트, 사용된 문장)

select * from table(dbms_xplan.display('PLAN_TABLE', 'demo1','advanced'));

#) 'advanced -Projection -Outline': 전체 실행계획중에 Projection, Outline 를 제외하고 싶다면?

select * from table(dbms_xplan.display('PLAN_TABLE', 'demo1','advanced -Projection -Outline'));

 

DBMS_XPLAN.DISPLAY('플랜 테이블', '문장 아이디', '매개변수');	해석
BASIC DBMS_XPLAN.DISPLAY('플랜 테이블', '문장 아이디', 'BASIC ');	- 가장 간결한 형태의 실행 계획 - 테이블과 인덱스의 액세스 방법, 조인 순서 등을 간략하게 제시
TYPICAL DBMS_XPLAN.DISPLAY('플랜 테이블', '문장 아이디', 'TYPICAL ');	- 더 많은 세부 정보를 제공하는 중간 수준의 실행 계획을 표시 - 테이블 및 인덱스 액세스 방법, 조인 순서, 통계 정보 등이 포함
ALL DBMS_XPLAN.DISPLAY('플랜 테이블', '문장 아이디', 'ALL ');	- 가장 상세한 형태의 실행 계획을 표시 - 모든 세부 정보를 제공하며, 각 단계에서의 예상 비용, 카디널리티, 통계 정보 등이 자세히 표시
ADVANCED DBMS_XPLAN.DISPLAY('플랜 테이블', '문장 아이디', 'ADVANCED');	- 추가적인 고급 정보를 제공하는 옵션. - 예를 들어, 각 단계에서의 입출력 레코드 수, 풀 스캔인 경우에 대상 테이블의 풀 스캔 통계 등이 포함
OUTLINE DBMS_XPLAN.DISPLAY('플랜 테이블', '문장 아이디', 'OUTLINE');	- 저장된 실행 계획에 관련된 정보를 표시 - OUTLINE 이름, 버전, 예상 비용, SQL 텍스트 등과 관련된 세부 정보를 제공
ROWS DBMS_XPLAN.DISPLAY('플랜 테이블', '문장 아이디', 'ROWS');	- 각 실행 단계에서의 예상 레코드 수와 통계 정보를 제시
PARTITION DBMS_XPLAN.DISPLAY('플랜 테이블', '문장 아이디', 'PARTITION');	- 파티션 키와 관련된 정보를 포함하여 파티션 관련 실행 계획을 표시