DBMS와 실행 계획_2

실행 계획이 SQL 구문의 성능을 결정

실행계획이 만들어지면 DBMS는 그것을 바탕으로 데이터 접근을 수행한다.

하지만, 데이터 양이 많은 경우에는 반응 지연이 발생하는데

첫번 째로, 앞에서 살펴본 통계정보가 부족한 경우

두번 째로는, SQL 구문이 복잡하여 옵티마이저가 최적의 경로를 선택하지 못할 경우 발생한다.

=> (이미 최적의 경로(방법)가 설정되어 있는데도 느린 경우)

 

실행계획 확인 방법

SQL 구문의 지연이 발생했을 경우 제일 먼저 실행 계획을 살펴본다.

Oracle : set autotrace traceonly

 

3개의 기본적인 SQL 구문의 실행 계획 살펴보기

• 테이블 풀 스캔(Full Scan)의 실행 계획
• 인덱스 스캔의 실행 계획
• 간단한 테이블 결합의 실행 계획(조인)

 

(Sql developer 사용)

 

테이블 풀 스캔의 실행 계획

실행 계획의 출력 포맷이 완전히 같진 않지만 공통적으로 나타나는 부분

• 조작 대상 객체
• 객체에 대한 조작의 종류
• 조작 대상이 되는 레코드 수

 

조작 대상 객체

• OBJECT_NAME 에 나오는 객체이름(테이블 이름)
• 여러 개의 테이블을 사용하는 SQL구문에서는 어떤 객체를 조작하는지 혼돈하지 않게 주의해야 한다.
• 테이블 이외에도 인덱스, 파티션, 시퀀스처럼 SQL 구문으로 조작할 수 있는 객체라면 무엇이라도 올 수 있음

 

객체에 대한 조작의 종류

• Operation필드와 Options필드를 보면
• FULL(FULL SCAN) => 테이블의 데이터를 전부 읽어들인다는 뜻이다.

 

조작 대상이 되는 레코드 수

• Cardinality => Access된 row 수를 의미

 

인덱스 스캔의 실행 계획

ex) SELECT * FROM CONTRACT WHERE CNTR_NO = '11'

 

조작 대상이 되는 레코드 수

• Cardinality가 1로 바뀌었다. WHERE구에서 기본 키를 지정했으므로, 접근 대상은 반드시 레코드 하나이기 때문에 당연한 결과이다.

 

접근 대상 객체와 조작

• Index Unique Scan이라는 조작을 볼 수 있다. 이는 인덱스를 사용해 스캔을 수행한다는 것을 말한다.
• Table Access Full -> Table Access By Index RowId로 바뀌었고 그 밑 줄에 'Index Unique Scan'에 _PK1 (PK 인덱스 이름)이 출력된다.

 

일반적으로 스캔하는 모집합 레코드 수에서 선택되는 레코드 수가 적다면 테이블 풀 스캔보다 인덱스 스캔이 빠르게 접근을 수행한다.

풀 스캔이 모집합의 데이터양에 비례해서 처리 비용이 늘어나는 것에 비해, 인덱스를 수용할 때는 B-tree가 모집합의 데이터양에 따라 대수 함수적으로 처리 비용이 늘어나기 때문이다.

간단히 말해서 데이터양을 n이라 하고, 데이터 양이 늘어날수록 풀스캔(sequential scan)은 O(n)으로 비례하고, 인덱스 스캔은 O(logn)으로 비례하게 된다.

 

간단한 테이블 결합의 실행 계획(Join)

ex)

SELECT *

FROM CONTRACT A

INNER JOIN CONTRACTOR B

ON(A.SYS_ID = B.SYS_ID AND A.CNTR_NO = B.CNTR_NO AND A.CNTR_REV = B.CNTR_REV)

 

결합, 조인이라고도 하는데 조인을 사용하면 실행계획이 상당히 복잡해지므로, 옵티마이저도 최적의 실행 계획을 세우기 어렵다.

따라서 조인 시점의 실행 계획 특성을 공부하는 것은 굉장히 중요한 의미가 있다.

조인을 할 때는 세가지 종류의 알고리즘(포스팅 참고)

NESTED LOOPS

• 한쪽 테이블을 읽으면서 레코드 하나마다 조인 조건에 맞는 레코드를 다른 쪽 테이블에서 찾는 방식 (이중 반복이므로 중첩반복이라는 이름이 지어짐)

SORT MERGE

• 결합키(CNTR_NO, CNTR_REV)로 레코드를 정렬하고, 순차적으로 두 개의 테이블을 결합하는 방법
• 결합 전에 전처리로 정렬을 수행해야 하는데, 이 때 작업용 메모리(워킹 메모리)를 사용하게 된다.

HASH

• 결합 키값을 해시값으로 매핑하는 방법, 해시 테이블을 만들어야 하므로 마찬가지로 워킹메모리 사용

 

객체에 대한 조작의 종류

• Hash Join으로 어떤 알고리즘을 사용했는지 알 수 있음
• 실행 계획은 일반적으로 트리구조이고, 중첩 단계가 깊을수록 먼저 실행이 된다.
• 결합전에 테이블 접근이 먼저 수행된다는 것을 알 수 있다.
• 결합의 경우 어떤 테이블에 먼저 접근하는지가 굉장히 중요한데 먼저 접근하는 테이블을 구동 테이블(driving table)이라고 부른다.
• 같은 중첩단계에서는 위에서 아래로 실행한다는 뜻이다. Table Access에서 위에 있는 테이블을 먼저 접근한다는 뜻