오라클 힌트절과 NULL

1. 놓치면 후회할 오라클 힌트절 7가지

1.1 힌트절로 옵티마이저의 실수 차단

CBO(Cost Based Optimizer) 방식에서 옵티마이저는 주어진 환경(통계정보, SQL문) 아래서 최적의 실행계획을 제공한다. 하지만 잘못된 SQL문이나 통계정보로 인해 엉뚱한 실행계획을 제공할 때도 있다. 이때 힌트절을 통해서 잘못된 실행계획을 바로 잡을 수 있다. 옵티마이저는 개발자가 사용하는 힌트절을 무조건 사용하지는 않는다. 

SELECT /*+ [힌트절] */ ~

힌트절은 주석문 안에 표시한다. 오라클이 제공하는 힌트절은 매우 많지만 핵심적인 7가지만 알아도 쿼리 튜닝에 부족함이 없다.

1.1.1 접근 순서를 결정하는 힌트절

- ORDERED : FROM 절에 나열된 테이블 순서대로 접근한다. (SQL 문에 종속적).

- LEADING : 테이블 접근 순서를 명시적으로 표시한다.(SQL문에 독립적)

 

ORDERED 힌트절과 LEADING 힌트절은 테이블 간 접근 순서를 결정한다. 

SELECT 컬럼들, ...
FROM 고객 A, 주문 B
WHERE A.고객번호 = B.고객번호
AND A.고객명 = '홍길동'
AND B.주문일자 = '20141201'

고객, 주문 테이블 조인, 조건절 모든 위치에 인덱스가 있다면 테이블의 접근 순서는 명확하지 않다. 고객 -> 주문, 주문 -> 고객으로 접근이 가능하기 때문에, 옵티마이저가 실수할 가능성도 있다. 만약 우리가 판단하는 접근 방향과 옵티마이저가 판단하는 접근 방향이 다르다면, 우리는 힌트절을 통해 접근 방향을 변경할 수 있다.

SELECT /*+ ORDERED */  컬럼들, ...
FROM 고객 A, 주문 B
WHERE A.고객번호 = B.고객번호
AND A.고객명 = ?
AND B.주문일자 = ?

SELECT /*+ LEADING(B A) */  컬럼들, ...
FROM 고객 A, 주문 B
WHERE A.고객번호 = B.고객번호
AND A.고객명 = ?
AND B.주문일자 = ?

앞 쿼리에서 ORDERED 힌트절로 인해 옵티마이저는 고객 -> 주문 방향으로 접근을 진행할 것이다. ORDERED 힌트절은 FROM 절에 나열된 테이블 순으로 접근을 유도한다. 반면에 LEADING 힌트절은 테이블의 접근 순서를 명시적으로 나열할 수 있다. 따라서 FROM 절에 종속적이지 않다는 장점이 있다.

테이블 접근 순서에 대한 결정 기준으로 다음 3가지를 인지하는게 좋다.

1. 진입형 테이블을 결정한다. 조건 중에서 조회 범위가 작은 테이블을 우선함
2. 연결 확장형보다는 연결 축소형 테이블을 우선한다. 조회 범위가 줄어드는 JOIN 우선
3. OUTER JOIN 보다는 INNTER JOIN을 우선으로 한다. INNER JOIN이 조회 범위 축소 가능

1.1.2 접근 방법을 결정하는 힌트절

- USE_NL : NESTED LOOP JOIN 방식으로 조인하도록 유도한다. (순차적인 접근 방법)

- USE_HASH : HASH JOIN 방식으로 조인하도록 유도한다. (해시 함수를 이용한 접근 방법)

 

USE_NL과 USE_HASH는 테이블 간 접근 방법을 결정한다. USE_NL 조인을 알아보자.

SELECT /*+ USE_NL(B) */ 컬럼들, ...
FROM 고객 A, 주문 B
WHERE A.고객번호 = B.고객번호
AND A.고객명 = '홍길동'
AND B.주문일자 = '20141201'

1. 고객 테이블에서 고객명이 '홍길동'인 고객을 구한다. (선행 테이블 결정)
2. '홍길동' 고객의 수만큼 순차적으로 주문 테이블을 고객번호 컬럼으로 접근한다. (순차적 접근)
3. 주문 테이블에서 주문일자가 '20141201'인 정보만 필터한다.

USE_NL이 취하는 인자는 비드라이빙 테이블을 의미

다음으로 USE_HASH 힌트절에 의한 Hash Join을 알아보자.

SELECT /*+ USE_HASH(B) */ 컬럼들, ...
FROM 조직 A, 집계 B
WHERE A.조직코드 = B.조직코드
AND A.사업부 = '강원사업부'
AND B.집계년월 = '201412'
GROUP BY A.조직명, A.조직코드

1. 조직 테이블에서 사업부가 '강원사업부'인 조직들을 구한 후, 조인절 컬럼인 조직코드를 해시 함수로
분류 한 다음, 해시 테이블을 생성한다. (해시 함수를 이용해 해시 테이블 구성)
2. 집계 테이블에서 집계년월이 '201412' 자료를 구한 후, 조인절 컬럼인 조직코드를 해시 함수로 변환
후 해시 테이블로 순차적으로 접근한다.

즉, 해시 함수를 이용해 조직 테이블과 집계 테이블을 조인한다는 의미이다. 조회 조건 컬럼인 A.사업부, B.집계년월 인덱스는 사용되고 있으며 A.조직코드, B.조직코드 컬럼은 인덱스가 있더라도 사용하지 않는다.

HJ 조인은 작은 테이블을 먼저 접근하는 것이 성능 면에서 더 좋다. 해시 테이블 구성 작업에 부하가 많이 발생하기 때문이다.

위와 같은 인덱스 생성도가 있을 때, 개발자가 생각하는 실행계획과 오라클이 생각하는 실행계획이 다를 수 있는데 개발자가 생각하는 실행계획이 더 성능이 좋다면 바꿔야 한다.

• 오라클 옵티마이저 : 가 -> 다 -> 마 -> 바 -> 라 -> 나 (전구간 USE_NL)
• 개발자 옵티마이저 : 가 -> 라 -> 다 -> 마 -> 바 -> 나 (가 ~ 라 구간은 USE_HASH, 나머지는 USE_NL)

코드로 나타내면 /*+ LEADING(가 라 다 마 바 나) USE_HASH(가, 라) USE_NL(라, 다 마 바 나) */ 와 같다. 

1.1.3 자원 사용을 결정하는 힌트절

- INDEX : 인덱스를 통한 접근 경로를 유도한다.

- FULL : 테이블을 풀스캔한다.

- PARALLEL : 병렬 처리를 통해 성능을 높인다.

SELECT /*+ INDEX(주문 고객번호_IDX) */ 컬럼들, ...
FROM 주문
WHERE 고객번호 = ?
AND 주문일자 = ?


고객번호, 주문일자 모두 인덱스 존재

두 개의 조건절에 모두 인덱스가 존재할 때 옵티마이저는 통계정보에 근거해 최소한의 비용이 소요되는 인덱스를 선택할 것이지만, 잘못 선택한 경우는 우리가 원하는 인덱스를 명시적으로 지정할 수 있다. 또 다음과 같은 인덱스 관련 힌트절도 있다. 

• INDEX_SS --- 결합인덱스의 선행컬럼 조건이 입력되지 않을 때 사용(INDEX SKIP SCAN)
• INDEX_FFS --- 인덱스만을 빠르게 전체 스캔한다. (INDEX FAST FULL SCAN)
• INDEX_DESC --- 인덱스를 통해 데이터를 역순으로 스캔한다.

SELECT /*+ FULL(주문) */ 컬럼들, ...
FROM 주문
WHERE 주문일자 =BETWEEN ? AND ?
ORDER BY 주문번호


주문일자 인덱스 존재

주문일자 조건절로 구간 조회를 하고 있다. 주문 테이블의 전체 건수에 비해 구간 조회 건수가 적다면 인덱스를 통해 접근하는 것이 빠를것이다. 하지만 구간 조회 범위가 넓다면 랜덤 액세스 부하가 크므로 랜덤 액세스 보다는 풀스캔하는게 더 빠를 수도 있다. 이와 같은 경우는 대개 배치성 쿼리에서 많이 볼 수 있다. 조회 기준은 1% 범위를 기준으로 하는게 좋다. 조회 건수가 1% 미만일 때 인덱스를 사용하고, 1% 이상일 때는 풀스캔 힌트절 사용을 고려하는게 좋다.

SELECT /*+ FULL(주문) PARALLEL(주문 4) */ 컬럼들, ...
FROM 주문
WHERE 주문일자 =BETWEEN ? AND ?
ORDER BY 주문번호


주문일자 인덱스 존재

PARALLEL 힌트절은 FULL 힌트절과 같이 사용된다. 병렬 처리를 위한 힌트절이므로 처리 성능은 좋으나, 자원을 독점적으로 사용하므로 멀티 유저 환경에서는 주의해야 한다. 만약 수치값을 1로 주면, FULL 힌트절만 동작한다. 또한 수치값을 주지 않는다면 사용 가능한 모두를 사용하므로 주의해야 한다. 대개 4 정도가 적당하다.

1.1.4 배치 튜닝의 마법사 같은 힌트절 삼총사 : USE_HASH, FULL, PARALLEL

대용량 데이터 처리와 조회에 빈번하게 사용하는 힌트절로 3가지를 잘 조합해 사용한다면 배치 튜닝에 어려움이 없다. 힌트절을 사용함에 있어서 다음의 3단계로 나누어서 사용 여부를 검토하는게 좋다.

• USE_HASH 힌트절만 사용해서 조회 가능한지 검토 : 적당히 무거운 쿼리에 사용
• 조회 범위가 크다면 FULL 힌트절 추가 사용 : 대개 이 단계에서 튜닝 완료
• 대용량 데이터의 빠른 처리가 요구 될 때 PARALLEL 힌트절 사용 : 제한적으로 사용 

특히 PARALLEL 힌트절은 병렬 처리를 위한 힌트절이기 때문에 신중하게 결정해야 한다.

2. 개발자들의 영웡한 숙제 NULL

오라클에서 NULL은 길이가 0인 문자열과 동일한 의미를 갖거나 알 수 없는 미지의 값을 의미한다. 

2.1 사칙연산에서의 NULL

SELECT NULL + 3 FROM DUAL -> 결과 : NULL
SELECT NULL - 3 FROM DUAL -> 결과 : NULL
SELECT NULL * 3 FROM DUAL -> 결과 : NULL
SELECT NULL / 3 FROM DUAL -> 결과 : NULL

2.2 비교 연산에서의 NULL

번호 기준금액 목표금액
1      100      200
2      100      NULL
3      NULL     200
4      NULL     NULL


SELECT 번호 FROM 실적 WHERE 기준금액 = 목표금액 -> 결과 : 없음
SELECT 번호 FROM 실적 WHERE 기준금액 > 목표금액 -> 결과 : 없음
SELECT 번호 FROM 실적 WHERE 기준금액 < 목표금액 -> 결과 : 1
SELECT 번호 FROM 실적 WHERE 기준금액 <> 목표금액 -> 결과 : 1

NULL과의 비교는 무의미함을 알 수 있다.

2.3 집계함수에서의 NULL

번호 주문금액
1    200
2    400
3    NULL


SELECT SUM(주문금액) FROM 주문 결과 -> 600
SELECT AVG(주문금액) FROM 주문 결과 -> 300
SELECT MAX(주문금액) FROM 주문 결과 -> 400
SELECT MIN(주문금액) FROM 주문 결과 -> 200

2.4 문자열 집합에서의 NULL

SELECT NULL || 'ABC' 결과 -> 'ABC'
SELECT NULL || 3 결과 -> 3
SELECT NULL || 'ABC' || 3 결과 -> 'ABC3'

표준 SQL에서는 문자열과 NULL의 결합은 NULL이다. 하지만 오라클에서는 NULL은 길이가 0인 문자열과 동일하게 인식한다.

2.5 논리연산에서의 NULL

NULL AND TRUE 결과 : NULL
NULL AND FALSE 결과 : NULL
NULL OR TRUE 결과 : TRUE
NULL OR FALSE 결과 : FALSE
NOT(NULL) 결과 : NULL 

책 오류 확인 필요

2.6 인덱스에서 NULL

인덱스가 있는 컬럼이라도 조건절에서 IS NULL 혹은 IS NOT NULL 구문 사용 시 인덱스를 사용하지 못한다. 해당 테이블은 풀스캔하게 된다. 왜냐하면 인덱스는 기본적으로 NULL 정보를 보관하지 않기 때문이다.  따라서 NULL이 꼭 필요한 경우는 NULL 값을 사용하지 않고 의미 있는 다른 특별한 값을 사용하는 것이 좋다. 예를들어 날짜에 NULL을 넣기보다는 전혀 없는 미래의 날짜를 사용해 저장하는 것이 좋다. 공백을 사용하는 경우도 있지만, TO_DATE를 사용할 경우 오라클 에러가 발생하기 때문에 권고하지는 않는다. 

두 번째는 함수 기반 인덱스를 이용하는 것이다. 컬럼에 함수까지 포함시켜서 인덱스를 생성하면 된다. 인덱스는 NULL 값을 보관하지는 않지만, 함수 기반 인덱스는 함수를 이용해 변환된 값을 보관하기 때문에 가능하다. 이 경우 컬럼에 NULL이 들어가 있어도 되므로 유용하다. 

SELECT * FROM 주문 WHERE NVL2(배송일자, 배송일자, '99991231') = '99991231' 

위 쿼리에서 색상 처리된 부분만 함수기반 인덱스로 생성해 사용하면 된다.

2.7 검색에서의 NULL

WHERE 컬럼 IS NULL 결과 : 올바른 NULL 검색 조건
WHERE 컬럼 IS NOT NULL 결과 : 올바른 NULL 검색 조건
WHERE 컬럼 = NULL 결과 : 틀린 NULL 검색 조건
WHERE 컬럼 <> NULL 결과 : 틀린 NULL 검색 조건
WHERE 컬럼 = 'NULL' 결과 : 문자열 NULL 검색 조건
WHERE 컬럼 LIKE '%NULL%' 결과 : 문자열 'NULL' 검색 조건
WHERE 컬럼 IN (NULL) 결과 : 틀린 NULL 검색 조건

2.8 함수에서의 NULL

NVL 함수와 NVL2 함수의 비교
- NVL(컬럼, NULL이면 치환할 값)
- NVL2(컬럼, NULL이 아니면 치환할 값, NULL이면 치환할 값)


번호   주문금액
1      200
1      400
1      NULL

1. SELECT SUM(주문금액) FROM 주문 : 결과 600
2. SELECT SUM(NVL(주문금액, 0)) FROM 주문 : 결과 600
3. SELECT NVL(SUM(주문금액, 0)) FROM 주문 : 결과 600

NVL 함수는 주의할 점이 있는데, 1번 쿼리같은 경우 집계할 레코드가 없는 경우 NULL을 리턴한다. 2번 SQL문은 집계함수 SUM이 NULL을 제외하고 계산하기 때문에 NVL이 의미가 없다. 따라서 3번째 SQL문이 올바른 사용법이다. AVG도 동일하게 사용하면 된다.

2.9 조인절에서 NULL

OUTER JOIN에서 연결되지 않는 레코드 컬럼 값은 NULL이다.

1. SELECT * FROM 주문 A, 고객 B WHERE A.고객번호 = B.고객번호(+)
2. SELECT * FROM 주문 A LEFT OUTER JOIN 고객 B ON A.고객번호 = B.고객번호

1번 SQL은 오라클의 OUTER JOJN이고, 2번 SQL문은 표준 SQL의 OUTER JOIN 형식이다. 어떤 형식을 사용하든지 연결되지 않는 레코드의 컬럼 값은 NULL이다. 일부 소규모 소핑몰에서는 고객의 회원 가입 없이 주문 가능한 경우도 있으므로 연결되지 않는 고객 레코드의 컬럼값은 NULL이다.

3. 개발자를 위한 유용한 선물 GATHER_PLAN_STATISTICS 힌트절

오라클 10g부터 GATHER_PLAN_STATISTICS 힌트를 이용하면 SQL Trace를 수행하지 않고도 쿼리의 실행계획 단계별로 Get Block을 알 수 있다. 

SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM 인사
WHERE 사용자명 = '이슬기'

앞의 대상 쿼리를 실행 한 후에 다음 분석 쿼리를 바로 수행해야 한다.

SELECT * FROM TABLE(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

왜냐하면 GATHER_PLAN_STATSTICS 힌트절을 추가해 실행한, 가장 최근의 쿼리에 대한 수행 정보를 보여주는 분석 쿼리이기 때문이다. 실제 쿼리를 실행하고 분석 쿼리를 수행한 내용을 자세히 살펴보자.

3.1 Id, Operation, Name

자원에 대한 접근 순서와ㅣ 접근 방법을 나타낸다. 

3.2 Starts

오퍼레이션을 수행한 횟수를 의미한다. (Starts * E-Rows)의 값이 A-Rows 값과 비슷하다면, 통계정보의 예측 로우 수와 실제 실행 결과에 따른 로우 수가 유사함을 알 수 있다. 만약 값에 큰 차이가 있다면 통계정보가 실제의 정보를 제대로 반영하지 못했다고 생각할 수 있다.

3.3 E-rows (Estimated Rows)

통계정보에 근거한 예측 로우 수를 의미한다. 통계정보를 갱신할수록 값이 매번 다를 수 있으며, 대부분의 DB 운영에서는 통계정보를 수시로 갱신하지 않으므로 해당 값에 큰 의미를 둘 필요는 없다. 하지만 E-Rows 값과 A-Rows 값이 크게 차이난다면 오라클이 잘못된 실행계획을 가질 수 있음을 인지해야 한다.

3.4 A-rows(Actual Rows)

쿼리 실행 결과에 따른 실제 로우 수를 의미한다. 우리는 A-Rows에서 중요한 여러 정보를 수정할 수 있다.

3.5 A-Time(Actual Elapsed Time)

쿼리 실행 결과에 따른 실제 수행 시간을 의미한다. 하지만 실행 시점의 여러 상황이 늘 가변적이고 메모리에 올라온 블록 수에 따라서 수행 시간이 달라지므로 해당 값에 큰 의미를 둘 필요는 없다.

3.6 Buffers(Logical Reads)

논리적인 Get Block 수를 의미한다. 해당 값은 오라클 옵티마이저가 일한 총량을 의미하므로 튜닝을 진행할 때 가장 중요한 요소 중 하나이다.

3.7 Reads(Physical Reads)

물리적인 Get Block 수를 의미한다. 동일한 쿼리를 여러 번 수행할 때 처음에는 값이 있으나, 처음이 아닌 경우에는 값이 0인 것을 보면 알 수 있듯이 메모리에서 읽어온 블록은 제외된다. 해당 값에 큰 의미를 둘 필요는 없다. 

 

앞 헤더에서 중요하게 사용되는 부분은 Buffers와 A-Rows이다. Buffers 값을 통해 Get Block의 총량을 알 수 있고, A-Rows를 통해 실행계획 단계별로 실제 로우 수를 알 수 있기 때문이다.

SELECT /*+ GATHER_PLAN_STATISTICS LEADING(A B) */ * 
FROM 인사 A, 실적 B
WHERE A.인사번호 = B.영업자번호
AND A.사용자명 = ‘이슬기’
AND B.영업일자 = ‘20150223’;

SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

SELECT /*+ GATHER_PLAN_STATISTICS LEADING(A B) */ * 
FROM 인사 A, 실적 B
WHERE A.인사번호 = B.영업자번호
AND A.사용자명 = ‘이슬기’
AND B.영업일자 = ‘20150223’;

SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

SELECT /*+ GATHER_PLAN_STATISTICS LEADING(A B) USE_HASH(B) */ * 
FROM 인사 A, 실적 B
WHERE A.인사번호 = B.영업자번호
AND A.사용자명 = ‘이슬기’
AND B.영업일자 = ‘20150223’;

SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

접근순서 : 인사 -> 실적, 접근방법 : For문 형식의 순차적 접근 : 14 buffers

접근순서 : 실적 -> 인사, 접근방법 : For문 형식의 순차적 접근 : 393 buffers

접근순서 : 인사 -> 실적, 접근방법 : Hash 함수를 이용한 접근 : 29 buffers

 

따라서 GATHER_PLAN_STATSTICS 힌트절 덕분에 우리는 예측정보 및 실제 정보를 바탕으로 성능 이슈에 대해 효과적으로 대응할 수 있게 되었다.

---

참조

http://www.yes24.com/Product/Goods/61583758

개발자를 위한 인덱스 생성과 SQL 작성 노하우 - YES24