8장 쿼리 실행 계획

1. Optimizer(옵티마이저)

  • 사용자가 작성한 SQL문은 다양한 방식으로 실행될 수 있습니다.

  • 이 다양한 방식들을 실행계획들 이라고 부르는데 이 실행계획들 중 가장 효율이 좋은 실행계획을 선택하여 SQL문을 실행하는 오라클 객체를 Optimizer라고 부릅니다.

2. Optimizer 작동방식

옵티마이저는 사용자의 SQL문을 받은후 다음과 같은 과정을 거쳐 SQL문을 실행합니다.

  1. SQL Parsing

    1. SQL문을 해석하고, 구문과 의미를 분석합니다.

    2. 만약 잘못된 문법 사용시 에러를 반환합니다.

  2. 최적화(Optimization)

    1. 다양한 실행계획을 평가하여 그 중 가장 비용이 적은 실행계획을 선택합니다(비용기반옵티마이저)

      1. 규칙기반옵티마이저는 미리정의된 규칙에 따라 실행되는 방식으로 현재는 사용하지 않는 옵티마이저

    2. 비용이 적다는 것은, 조회해야할 레코드가 적은 것을 의미합니다.

    3. 실행계획을 선택할 때는 딕셔너리의 통계데이터(테이블의행개수,인덱스여부등 )를 이용합니다.

  3. 실행계획(Execution Plan) 전달

    1. 최종적으로 선택된 실행계획을 바탕으로 SQL실행 엔진에게 실행계획을 전달합니다.

  4. 결과값 반환

    1. SQL실행 결과를 사용자에게 반환합니다.

  • SQL Developer에서 실행계획을 확인하려면 F10버튼을 누르면 된다.

3. Optimizer의 JOIN문

  • 옵티마이저는 SQL에 JOIN문이 사용된 경우 이 JOIN문을 어떤 방식으로 처리 하는 것이 가장 효율적인지 판단합니다.

  • 옵티마이저가 SQL문 실행에 사용하는 조인문은 크게 3가지가 있으며, 각 조인문에 대한 실행 계획의 비용을 산출한 후 ,이 중 가장 효율적인 방식으로 실행됩니다.

1. Nested Loop Join(중첩 반복 조인)

  • 선행 테이블의 각 행을 기준으로 후행테이블의 JOIN 조건과 일치하는 칼럼을 찾아 나가는 방식의 JOIN문

  • 구조적으로 중첩 반복문의 구조를 가지고 있다.

EX) 코드예시

SELECT EMP_NAME, DEPT_TITLE
FROM EMPLOYE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID

  • 위 코드를 자바기준으로 코드화 한다면 다음과 같다.

for(Employee e : employeeTables){ // employee Table은 list형태의 데이터라고 가정
   for(Dparment d : deptTables){ // dept Table은 list형태의 데이터라고 가정
      if(e.dept_code = d.dept_id){
         // employee의 정보에 department의 정보를 합친 후(Result), resultSet에 추가.
         Result result = new Result(e, d);
         resultList.add(result);
         break; // 반복종료.
      }
   }    
}

  • Emplyoee테이블의 1개의 행을 기준으로 해당 행의 dept_code와 일치하는 dept_id값을 찾기 위해 Department 테이블의 행의 개수 만큼 반복문을 돌리는 것.

  • 이 때, 후행 테이블에 INDEX가 존재한다면, INDEX를 통해 더 빠르게 후행 테이블의 칼럼을 찾을 수 있으므로 NL JOIN시 인덱스가 존재한다면 성능이 올라간다.

  • 정리)

    • NL JOIN은 중첩 반복문 방식으로 선행 테이블의 JOIN조건에 사용된 칼럼의 값과 후행 테이블의 JOIN조건에 사용된 칼럼이 일치하는 경우를 찾는 JOIN문으로, 후행 테이블의 갯수가 적고 INDEX설정이 되어 있다면 JOIN성능이 향상된다.

    • JOIN문에 사용될 선행/후행 테이블은 기술한 순서가 아닌, 딕셔너리를 통해 확인한 행의 갯수가 적은 테이블이 후행 테이블로 선정된다.

2. Hash Join

  • 선행 테이블의 JOIN조건에 사용된 칼럼의 값을 hasing처리하여 hash-table에 보관한 후 , 후행 테이블의 값도 hasing하여 일치하는 행과 매칭을 시도하는 JOIN문

  • 선행 테이블은 JOIN문에서 행의 갯수가 적은 테이블이 선행 테이블로 선정된다.

  • 인덱스 없이도 효율적인 조인이 가능하지만, hash-table을 구성하기 위한 추가 메모리가 필요하다.

  • 대용량 데이터 처리에서 자주 사용되는 효율적인 방식의 JOIN문

EX) 코드예시

  • 수백만건의 주문정보가 저장된 ORDERS와 고객정보가 담긴 MEMBER데이터를 조인하는 경우

SELECT ORDER_ID, MEMBER_NAME
FROM ORDERS
JOIN MEMBER(MEMBER_ID)

  • 위 코드를 자바방식으로 해석한다면 다음과 같음

// 1.Member 테이블을 해시맵으로 메모리에 올림(더 적은 메모리기준으로 hash-table이 만드어짐)
Map<Integer, Member> memberHashTable = new HashMap<>();
for (Member m : memberTable) { // meberTable은 list형태의 데이터라고 가정
    memberHashTable.put(m.getMemberId(), m); // JOIN컬럼기준
}

// 2. Orders 테이블을 순회하면서 해시맵에서 매칭되는 회원정보를 찾음
for (Order o : ordersTable) { // ordersTable은 list형태의 데이터라고 가정
    Member matchedMember = memberHashTable.get(o.getMemberId()); // MemberId의 해쉬함수호출
    if (matchedMember != null) {
        // 주문과 회원 정보를 조합하여 결과 생성흐 RESULTSET에 추가.
        Result result = new Result(o.getOrderId(), matchedMember.getName());
        resultList.add(result);
    }
}

3. Sort Merge Join

  • 조인 대상의 양쪽 테이블을 JOIN의 칼럼값을 기준으로 정렬(Sort) 한 후 , 각 테이블의 행을 하나로 합친 후 JOIN하는 방식.

  • 조인 대상의 양쪽 테이블이 이미 정렬(Sort)되어 있는 경우 사용하면 매우 빠르게 조인이 가능하다.

  • 양쪽 테이블의 데이터가 JOIN 칼럼을 기준으로 정렬된 상태를 유지하고 있는 경우 사용하면 좋다.

예시코드) 학번 기준으로 정렬된 학생 테이블과 성적테이블이 존재하는 경우

SELECT STUDENT_NAME, SCORE
FROM STUDENT
JOIN GRADE ON STUDENT_ID = STUDENT_ID;

  • 위 코드를 자바방식으로 해석한다면

int i = 0, j = 0;
// student, grade는 list형태의 데이터라고 가정.
student.sort();
grade.sort(); // STUDENT_ID기준.
while (i < student.size() && j < grade.size()) { // i,j의 값이 테이블의 끝에 도달할때까지 반복
    Student s = student.get(i);
    Grade g = grade.get(j);
    
    // 두 테이블에서 추출한 현재 id값이 동일한 경우 하나로 묶어 준다.
    if(s.getId() == g.getStudentId(){
        resultList.add(new Result(s.getName(), g.getScore()));
        j++; // Grade테이블에는 학생 1명당 n개의 정보가 담겨있으므로 j값을 증가하여 계속 찾음
    }
    else if(s.getId() < g.getStudentId()){
        i++;
    }else if(s.getId() > g.getStudentId()) {
        j++;
    }
}

4. Oracle 실행계획 해석 방법

1번 예시) NL JOIN문 실행계획

  • OPERATION : 수행내용

  • OBJECT_NAME : 객체이름

  • OPTIONS : 옵션명

  • CARDINALITY : 예상 결과 행 수

  • COST : 예상 실행 비용

해석순서

  • nl join실행계획은 가장 안쪽의 코드부터 먼저 실행되며 위에서부터 아래로 해석하면 된다.

  1. NESTED LOOPS (TB_GRADE) 수행(외부반복문) - 5036회 수행

  2. TABLE ACCESS FULL SCAN 수행

    1. TB_GRADE테이블은 인덱스를 사용하지 않고 모든 데이터를 순차적으로 접근함을 의미(FULL SCAN)

  3. INDEX UNIQUE SCAN 수행

    1. PK_STUENT 인덱스에서 현재 행의 STUDENT_NO와 일치하는 INDEX를 찾음

  4. NESTED_LOOPS(TB_STUDENT) 수행(내부반복문) - 1회 수행

  5. TABLE ACCESS BY INDEX ROWID

    1. 3번 단계에서 추출한 INDEX의 ROWID를 통해 TB_STUDENT의 행에 접근

    2. UNIQUE한 값이기 때문에 접근은 1회로 끝나고 종료된다.

  6. 다시 1번 부터 반복.

2번 예시 ) Hash join

  • TB_STDUENT를 FULL_SCAN하여 HASH_TABLE구성

  • TB_GRADE를 FULL_SCAN하여 HASH_TABLE과 매칭되는지 확인

  • 매칭되는 행간의 HASH JOIN 수행

3번 예시) MERGE JOIN

  • TB_GRADE SORT(STUDENT_NO 기준)

  • TB_STUDENT SORT(STUDENT_NO 기준)

  • 정렬된 결과를 MERGE JOIN

Previous7장 PL/SQLNextJDBC

Last updated