Explain Plan

• SQL을 수행하기 전 데이터를 어떻게 가져올 건지에 대한 실행계획을 의미한다.
• Explain Plan For 명령어를 사용한다. '$ORACLE_HOME'

1. Explain Plan 테이블 생성하기

SQL> @?/rdbms/admin/utlxplan.sql

• 10g 이전엔 @?/rdbms/admin/utlxplan.sql 을 실행하여 테이블을 생성했다.
  • ? 는 $ ORACLE_HOME 디렉토리를 대체하는 기호이다.
• Oracle 10g 부터는 설치 시 기본적으로 sys.plan_table$를 제공하므로 별도의 Plan Table을 생성하지 않아도 된다.
• sql server에서는 showplan_text 명령어를 수행을 통해 Plan_Table에 실행계획을 저장 할 수 있다.

2. Explain Plan 활용 이점

• Explain Plan For 명령어를 수행을 통해 Plan_Table에 실행계획을 저장 할 수 있다.
• Explain Plan 테이블을 통해 실행계획을 별도로 저장해 둔다면 이를 활용해 안정적인 시스템 운영 및 성능관리가 가능하다.
• Explain Plan 테이블을 통해 통계정보 변경 등으로 인한 이유로 갑자기 성능이 나빠질 경우 이전 실행계획을 확인하고 예전과 같은 방식으로 수행되도록 할 수 있다.
• 인덱스 구조 변경 시 사용하는 SQL을 뽑아 사전점검이 가능하다.

3. Explain Plan 테이블 조회

SELECT owner, synonym_name, table_owner, table_name
FROM all_synonyms
WHERE synonym_name = 'PLAN_TABLE';

4. Explain Plan 실행

• utlxpls 싱글 실행 계획 / utlxplp 병렬 실행 계획

1) 쿼리 수행

• explan plan for 명령을 통해 수행하며 수행 결과는 plan_table에 저장된다.

-- set statement_id ='query1' 는 생략 가능하다.
Explain plan set statement_id ='query1' for 
select * from emp where empno = 7900; 

2) 실행 계획 포맷팅

• plan_table에 저장된 정보를 쉽게 보기 위해(포맷팅하기 위해) 9i부터는 오라클이 제공해주는 utlxpls.sql 또는 utlxplp.sql 스크립트를 사용한다.
  • utlxpls는 싱글 실행 계획이다.

  SQL> SET LINESIZE 200
  select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

  • utlxplp는 병렬 실행 계획이다.

    select * from table(dbms_xplan.display());

3) 실행 계획 보기

• linesize 를 작게 설정하면 일부 정보가 다음 행으로 밀려 출력되니 여유롭게 설정하는 게 좋다.

SQL> SET LINESIZE 200
SQL> @?/rdbms/admin/utlxpls;

PLAN_TABLE_OUTPUT
 ----------------------------------------------------------------------------- 
Plan hash value: 4024650034

------------------------------------------------------------------------------ 
|ID | Operation                         | Name | Rows | Bytes | Cost (%CPU)  |
------------------------------------------------------------------------------ 
|  0| SELECT STATEMENT                  |      |      |     32|     1     (0)|
|  1|  TABLE ACCESS BY INDEX ROWID      |EMP   |     1|     32|     1     (0)|
|* 2|   INDEX UNIQUE SCAN               |EMP_PK|     1|       |     0     (0)|
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------------------------------------------
  2 - access("EMPNO"=7900)

✅ Predicate Information의 세 가지 유형

1. 인덱스 Access Predicate

• 인덱스를 통해 스캔의 범위를 결정하는데 영향을 미치는 조건절이다.

2. 인덱스 Filter Predicate

• 인덱스를 통했으나 스캔의 범위를 결정하는 영향을 미치지 못하는 조건절이다.

3. 테이블 Access Predicate

• NL 조인을 제외한 조인에서 발생하며 결과 값의 범위를 결정하는데 영향을 미치는 조건절이다.

4. 테이블 Filter Predicate

• 테이블 스캔 후 최종 결과 집합 포함 여부를 결정하는데 영향을 미치는 조건절이다.

5. Explain Plan 구성

🔆 SQLP 기출 뽀인뜨 : Explain Plan과 AutoTrace와 SQL 트레이스 비교

SQLP 기출 문제를 풀다보면 Explain Plan과 AutoTrace와 SQL 트레이스에서
각각 볼 수 있는 항목과 볼 수 없는 항목을 물어보는데 이게 굉~~~장히 헷갈린다.😂

⭕️ Explain Plan에서 볼 수 있는 정보

1) 예상 cardinality

2) 예상 cost

3) 예상 CPU TIME

4) 예상 데이터 크기 (byte)

5) 조건절 정보 (Predicate Information)

❌ Explain Plan에서 볼 수 없는 정보

1) 실제 실행시간