ORACLE_012. Understanding Excution Plan

Understanding Excution Plan                                                                   

 SQL 구문을 수행하기 위해서 오라클을 많은 단계의 절차를 거칩니다. 어떠한 방법으로든 사용자가 구문을 입력하게 되면 각각의 단계에서는 데이터베이스에서 물리적으로 데이터의 행을 가져오거나 혹은 보낼 준비를 합니다. 각각의 단계를 조합하는 일은 오라클이 실행계획(Execution Plan)이란 구문을 통해 수행하게 됩니다. 실행계획은 구문(Statement)에 의해 읽혀지는 테이블의 접근경로와 Join Method 에서 Join 을 수행할 테이블의 순서를 포함합니다.

OVERVIEW OF EXPLAIN
 EXPLAIN PLAIN 이라는 SQL 구문을 통하여 옵티마이저가 선택한 실행계획을 확인할 수 있습니다. 구문이 입력되면, 옵티마이저는 실행계획을 선택하고 데이터베이스 테이블에 계획에 관한 자료를 삽입합니다. 간단하게 EXPLAIN PLAN 구문을 입력하고 출력할 테이블을 질의(Query) 하면 됩니다.

 다음은 EXPLAIN PLAN 구문의 기본적인 사용 방법 입니다.

 - UTLXPLAN.SQL Script 를 이용하여 스키마에 PLAN_TABLE을 만듭니다. 
 - SQL 구문의 처음에 EXPLAIN PLAN FOR 구문을 포함시킵니다.
 - EXPLAIN PLAN FOR 구문을 수행한후, 오라클이 제공하는 plan table을 보여주는 스크립트를
   사용합니다.
 - EXPLAIN PLAN의 출력 순서는 가장 가까운 쪽에서 먼쪽으로 출력됩니다. 각 라인은 다음 단계의
   부모 단계 입니다. 두 단계의 들여쓰기가 같다면, 상위 라인이 먼저 수행됩니다.

---

      NOTES:
               - 여기에서 EXPLAIN PLAN의 결과 출력은 utlxpls.sql 스크립트를 사용합니다.
               - 각 단계에서의 EXPLAIN PLAN의 출력 테이블은 각 시스템의 환경설정이나 옵티마이저에 따라
                 다르게 보여질 수 있습니다. 

---

 EXAMPLE : Using EXPLAIN PLAN
 EXPLAIN PLAN FOR
 SELECT e.employee_id,  j.job_title, e.salary, d.department_name
   FROM    employees e, jobs j, departments d
   WHERE e.employee_id < 103
        AND e.job_id = j.job_id
        AND e.department_id = d.department_id;

 EXAMPLE : Result of EXPLAIN PLAN Output

 

STEPS IN EXECUTION PLAN
 다음 단계들은 위 예제에서 데이터베이스의 오브젝트에서 물리적으로 데이터를 얻었습니다.
  - Step 3 에서 employees의 모든 행을 읽어들였습니다.
  - Step 5 에서 JOB_ID_PK 인덱스로 job_id를 찾고, jobs 테이블에서 rowid로 연관된 행을 찾습니다.
  - Step 4 에서는 Step 5에서 rowid로 행을 찾아냅니다.
  - Step 7 에서 DEPT_ID_PK 인덱스로 각각의 department_id를 찾고 departments 테이블에서 행과 연관된 rowid를
    찾습니다.
  - Step 6 에서 Step 7에서 획득한 rowid로 departments 테이블에서 행을 찾아냅니다.
 다음 단계들은 위 예제에서 그 전 행의 소스로 반환된 행을 다룹니다.
  - Step2는 employees, jobs 테이블에서 job_id를 이용하여 Nested loop을 수행하고 Step 3,4에서 반환한 행
    소스를 받아들이고, Step 3 소스를 Step 4에서 반환한 행과 Join 합니다.  그리고 결과를 Step 2에 반환 합니다.
  -  Step 1은 Nested loop을 수행하고 2와 6 단계의 소스를 받습니다. Step 2에서의 행을 Step 6의 행과 Join 하고,
     Step 1에거 그 결과 행을 반환합니다.


UNDERSTANDING EXECUTION ORDER
 실행계획의 단계는 위 예제에서 보여주는 것 처럼 순서대로 수행되는 것이 아닙니다. 오른쪽으로 가장 들여쓰기가 되어진 행을 먼저 수행합니다. 각각의 단계의 결과는 부모 단계에게 값을 전달합니다.  쉽게 이하히기 위해 다음 그림을 참고하세요.

       <Pic : Graphical View of SQL Explain Plan in SQL Scratchpad/www.oracle.com>

FIN
REF) Oracle Documents (Server .920)/a96533 "Introduction to the Optimizer"