Post

<오라클 성능 고도화 원리와 해법2> Ch04-12 기타 쿼리 변환

Posted
By Mijong Go 9 min read

오라클 성능 고도화 원리와 해법2 - Ch04-12 기타 쿼리 변환

지금까지 설명한 것 외에도 이름이 밝혀지지 않은 많은 쿼리 변환들이 작동하고 있다. 그 중 몇 가지 중요한 쿼리 변환만을 소개하고자 한다.

(1) 조인 컬럼에 IS NOT NULL 조건 추가하기

1
2
3
4

SELECT COUNT(e.empno), COUNT(d.dname)
FROM emp e, dept d
WHERE d.deptno = e.deptno
AND sal <= 2900;

위와 같은 조인 문을 처리할 때, 조인 컬럼 deptno가 NULL인데 이때는 조인 액세스가 불필요하다. 어차피 조인에 실패하기 때문이다. 따라서 아래와 같이 필터 조건을 추가해주면 불필요한 테이블 액세스 및 조인 시도를 줄일 수 있어 쿼리 성능 향상에 도움이 된다.

1
2
3
4
5
6

SELECT COUNT(e.emono), COUNT(d.dname)
FROM emp e, dept d
WHERE d.deptno = e.deptno
AND sal <= 2900
AND e.deptno IS NOT NULL
AND d.deptno IS NOT NULL;

여기서, t_emp 테이블에서 10,000개 레코드를 읽었지만 dept 테이블과의 조인 액세스가 전혀 발생하지 않은 것(c=)에 주목하자. IS NULL 조건을 따로 기술하지 않더라도 읽은 값이 NULL일 때는 조인 액세스를 하지 않는다는 뜻이며, 이는 매우 중요한 사실이 아닐 수 없다. (만약 버퍼 Pinning 효과 때문이라면 적어도 dept의 PK 인덱스를 두 번은 읽었을 것이다.)

드라이빙 테이블에서 읽은 값이 NULL일 때도 상황에 따라 조인 액세스가 일어날 수 있다는 뜻인데, 아예 emp의 deptno가 NOT NULL 조건을 명시적으로 추가해준다면 염려할 필요가 없다.

다행히도, 컬럼 통계를 수집하고 나면 옵티마이저가 그런 조건은 자동으로 추가해준다. 단, 조인 컬럼의 NULL 값 비중이 5% 이상일 때만 이 기능이 작동한다.

이처럼 조인 컬럼에 IS NOT NULL 조건을 추가해주면 NL 조인 뿐만 아니라 해시 조인, 소트 머지 조인 시에도 효과를 발휘한다. 우선, 해시 조인을 위해 BuildInput을 읽어 해시맵을 만들 때 더 적은 메모리를 사용한다. Probe Input을 읽을 때도 NULL 값인 레코드를 제외함으로써 해시맵 탐색 횟수를 줄일 수 있다. 양쪽 모두 NULL 값 비중이 클수록 효과도 커진다.

소트 머지 조인할 때도 양쪽 테이블에서 조인 컬럼이 NULL인 레코드를 제외한다면 소트 및 비교 연산 횟수를 줄일 수 있다.

이런 여러 가지 사실에 비추어볼 때, 조인 컬럼에 대한 IS NOT NULL 조건을 추가한다고 손해볼 일은 전혀 없다. 그러나 옵티마이저는 NULL 값 비중이 5%를 넘을 때만 이런 쿼리 변환을 시행한다. 따라서 필요하다면 옵티마이저 기능에 의존하지 말고 사용자가 직접 위와 같은 조건을 추가해 줌으로써 불필요한 액세스를 줄일 수 있다.

그리고 조인 컬럼의 NULL 값 비중이 많을 때 임의의 Default 값(0, x 등)으로 채우는 방식으로 설계하면 조인 성능을 떨어뜨릴 수 있다는 사실도 기억하기 바란다.

(2) 필터 조건 추가

아래와 같이 바인드 변수로 between 검색하는 쿼리가 있다고 하자(스크립트 Ch428. 참조). 쿼리를 수행할 때 사용자가 : mx보다 : mn 변수에 더 큰 값이 입력된다면 쿼리 결과는 공집합이다.

1
2

SELECT * FROM emp
WHERE sal BETWEEN :mn AND :mx

사전에 두 값을 비교해 알 수 있음에도 쿼리를 실제 수행하고서야 공집합을 출력한다면 매우 비합리적이다. 잦은 일은 아니겠지만 초대용량 테이블을 조회하면서 사용자가 값이 거꾸로 입력하는 경우를 상상해보라.

그럴 경우 8i까지는 사용자가 한참을 기다려야만 했다. 9i부터는 이를 방지하기 위해 옵티마이저가 임의로 필터 조건식을 추가한다.

(3) 조건절 비교 순서

위 데이터를 아래 SQL문으로 검색하면 B 컬럼에 대한 조건식을 먼저 평가하는 것이 유리하다. 왜냐하면, 대부분 레코드가 B=1000 조건을 만족하지 않아 A 컬럼에 대한 비교 연산을 수행하지 않아도 되기 때문이다.

1
2
3

SELECT * FROM T
WHERE A = 1
AND B = 1000;

반대로 A=1 조건식을 먼저 평가한다면, A 컬럼이 대부분 1이어서 B 컬럼에 대한 비교 연산까지 그만큼 수행해야 하므로 CPU 사용량이 늘어날 것이다.

아래와 같은 조건절을 처리할 때도 부등호(>) 조건을 먼저 평가하느냐 like 조건을 먼저 평가하느냐에 따라 일량에 차이가 생긴다.

1
2
3
4

SELECT /*+ full(도서) */ 도서번호, 도서명, 가격, 저자, 출판사, isbn
FROM 도서
WHERE 도서명 > :last_book_om
AND 도서명 LIKE :book_nm || '%';

이에 옵티마이저는, 테이블 전체를 스캔하거나 인덱스를 수평적으로 스캔할 때의 Filter 조건식(Filter Predicates)을 평가할 때 선택도가 낮은 컬럼을 먼저 처리하도록 순서를 조정한다! 인덱스 수직적 탐색을 위한 조건절에는 영향없음).

이런 쿼리 변환이 작동하려면 9i, 10g를 불문하고 옵티마이저에게 시스템 통계를 제공함으로써 CPU Costing 모델을 활성화해야 한다. I/O Costing 모델에서는 where 절에 기술된 순서대로 조건식 비교가 일어난다. RBO 모드에서는 Where 절에 기술된 반대 순서로 조건식 비교가 일어난다. 정리하면 아래표와 같다.

ordered_predicates 힌트의 또 다른 용도

10g에서 OR 또는 IN- List 조건에 대한 OR- Expansion이 일어날 때 실행 순서를 제어할 목적으로 ordered_predicates 힌트를 사용할 수도 있다. 예를 들어, 91까지는 I/O 비용 모델, CPU 비용 모델을 불문하고 IN- List를 OR- Expansion (= Concatenation) 방식으로 처리할 때 뒤쪽에 있는 값을 먼저 실행한다. 하지만 10g CPU 비용 모델 하에서는 계산된 카디널리티가 낮은 쪽을 먼저 실행한다.

Database, SQL Tuning
SQLP
This post is licensed under CC BY 4.0 by the author.