[ORACLE] 실행계획 보는 법 #2

ORACLE 실행계획 #2

 

1. 기본 플랜 예시

 

위 2개의 플랜에서 쿼리의 차이점은 ROWNUM <= 1 혹은 2 부분이다. 테이블을  FULL SCAN하기 위해 접근하지만 COUNT(STOPKEY)부분에서 레코드 건수가 1혹은 2가 되었을 때 SCAN을 중지하고 빠져 나옴을 알 수 있습니다. 2개의 플랜에서 ROWNUM값이 1 혹은 2에 따라서 Card값과 Bytes값이 배수가 됨을 알 수 있습니다. 그런데 비용(Cost) 값은 왜 같을까? 그것은 고객 테이블의 첫 번째 레코드와 두번째 레코드가 동일 블록에 저장이 되어 있기 때문이라고 추측 할 수 있습니다. 참고로 ORACLE은 최소 운반 단위인 블록 단위로 데이터를 운반합니다.

 

2. 복잡한 플랜을 인덱스 생성도와 비교

플랜의 해석 순서는 깊이가 다른 경우에는 안쪽에서 바깥쪽으로, 깊이가 같은 경우에는 위에서 밑으로 해석합니다.

따라서, 위 플랜은 1 -> 2 -> 3 -> 4 -> 5 -> 6 순으로 해석합니다. 그리고 플랜의 내용에서 스캔은 UNIQUE SCAN임을 알 수 있고, 두 테이블의 조인 방식은 NESTED LOOP JOIN임을 알 수 있습니다. 즉, 순차적 루프에 의한 접근 방식입니다.

 

* UNIQUE SCAN

- 수직적 탐색만으로 데이터를 찾는 스캔 방식으로서,  Unique인덱스를 통해 '=' 조건으로 탐색하는 경우에 작동합니다.

* NESTED LOOP JOIN

- 2개 이상의 테이블에서 하나의 집합을 기준으로 순차적으로 상대방 Row를 결합하여 원하는 결과를 조합하는 방식입니다.

 

플랜의 해석순서를 그림으로 변화하면 인덱스 생성도와 동일하다는 것을 알수있습니다.

(주문번호 인덱스 -> 주문 테이블 -> 고객번호 인덱스 -> 고객 테이블)

 

아래의 그림은 고객 테이블에 인덱스가 없는 경우를 가정해 보았습니다.

고객 테이블의 고객번호 컬럼에 인덱스가 없어서 고객 테이블에서 FULL SCAN이 발생하고 있습니다. 실제 리턴 결과 건수는 1건이지만, 인덱스가 존재하지 않음에 따라 고객 테이블의 전체 데이터를 FULL SCAN하고 있는 것입니다. 여기에서 우리는 Card = 1 이라는 것에 주목할 필요가 있습니다. 비록 인덱스는 없지만 고객 테이블에 통계정보가 구성되어 있음을 유추할 수 있고, 고객번호는 UNIQUE함 추정할 수 있습니다. 따라서 고객번호 컬럼을 인덱스로 생성해야 함을 알수 있습니다. 플랜에서 인덱스를 생성해야 할 컬럼을 직관적으로 바로 알기는 어려우나, 인덱스 생성도를 함께 이용하면 어떤 위치에 어떤 인덱스를 생성해야 하는지 알수 있습니다.

 

3. 조인절 양뱡향 모두에게 인덱스가 없는경우

위 쿼리의 문제점은 이런 경우 두 테이블 간 조인 방식은 Sort Merge Join 방식으로 풀리는 경우가 많습니다. 처리 순서는 다음과 같습니다.

 

* Sort Merge Join

- 조인의 대상범위가 넓을 경우 발생하는 Random Access를 줄이기 위한 경우나 연결고리에 마땅한 인덱스가 존재하지 않을 경우 해결하기 위한 조인방안

 

1. 고객테이블에서 고객명 '홍길동' 인 고객을 구한 후 고객번호 순으로 정렬한다. (SORT)

2. 주문테이블에서 주문일자 '20150112'인 주문을 구한 후 고객번호 순으로 정렬한다.(SORT)

3. 정렬된 고객정보와 주문정보를 고객번호 컬럼으로 결합한다. (MERGE)

 

하지만 Sort Merge join 방식은 성능상 문제가 많은 조인 방식입니다. 해결 방안은 컬렘에 인덱스를 생성하여 Nested Loop Join 방식으로 플랜이 풀리게 해야합니다. 만약에 고객 테이블의 고객번호를 인덱스로 생성한다면 주문 테이블에서 고객 테이블로 순차적으로 접근할 것이고, 주문 테이블의 고객번호를 인덱스로 생성하면 고객 테이블에서 주문 테이블로 순차적으로 접근할 것입니다.

 

만약, 업무 로직상 조인절에 인덱스를 생성하기 곤란한 상황이라면 힌트절을 추가해 Hash Join 방식으로 접근하는 것도 좋은 방법 입니다. 대부분 Hash Join 방식이 Sort Merge Join 방식보다 성능이 더 좋다. 그래서 요즘  Sort Merge Join 방식은 거의 볼 수 없고 Hash Join 방식을 많이 사용합니다.

 

* Hash Join

- 해싱 함수기법을 활용하여 조인을 수행하는 방식(해싱함수는 직접적인 연결을 담당하는 것이 아니라 연결될 대상을 특정 지역에 모다우는 역할만을 담당)

- 해시값을 이용하여 테이블을 조인하는 방식

 

 

4. 힌트절을 추가해 Hash Join 방식

Hash Join 방식은 해시 함수를 이용한 접근 방식인데, 대량의 데이터 처리에 효율적인 조인 방식입니다. Nsted Loop Join 방식에서 처리범위가 부담스럽거나, Sort Merge Join 방식에서 정렬(Sort)이 부담스러울 때 사용합니다. Hash Join 방식의 처리 순서는 다음과 같습니다.

 

1. 고객 테이블에서 고객명이 ‘홍길동’인 고객을 구한 후, 조인절 컬럼인 고객번호를 해시 함수로 분류해 해시 테이블을 생성합니다. (해시 함수를 이용해 해시 테이블 생성)

2. 주문 테이블에서 주문일자 ‘20150112’인 주문을 구한 후, 조인절 컬럼인 고객번호를 해시 함수로 변환해서 테이블로 순차적으로 접근합니다. (해시 함수를 통해서 해시 테이블 탐색)

 

메모리에 해시 테이블을 생성하고 해시 함수를 이용하여 연산 조인을 하기 때문에 CPU 사용이 증가할 수 있으므로 조회 빈도가 높은 온라인 프로그램에는 적합하지 않는 조인 방식입니다.

 

5. UNION ALL과 UNION 관련 플랜

쿼리에서 UNION ALL 구문을 사용하면 중복되는 데이터를 있는 그대로 모두 보여줍니다. 하지만 UNION 구문을 사용하면 중복되는 데이터를 제거하고 UNIQUE하게 보여준다는 것을 알수 있습니다. 위의 2개 플랜의 차이점은 SORT(UNIQUE)부분입니다. 이것의 의미는 데이터를 정렬한 후에 중복된 데이터를 제거하고 UNIQUE하게 보여 준다는 의미입니다.