1. 힌트란?
SQL의 실행을 위해 옵티마이저가 최적의 실행 계획을 선택한다고 설명했다. 하지만 옵티마이저가 최적의 실행 계획을 선택하는데 영향을 주는 요소들이 최적화되어 있지 않거나 DBMS의 버전이 낮아 옵티마이저의 분석 기능이 떨어지는 경우는 옵티마이저가 선택하는 실행 계획 또한 최적화되어 있다고 판단하기 어렵다.
이렇듯 옵티마이저가 성능에 최적화된 실행 계획을 선택하지 못했을 때, 옵티마이저로 하여금 올바른 실행 계획을 수립할 수 있도록 직접 방향을 제시할 수 있는데, 가장 대표적으로 사용할 수 있는 방법이 힌트이다.
힌트를 사용하면 옵티마이저의 모드는 물론, 사용할 인덱스, 조인 방식, 스캔 방식까지 제어하는 것이 가능하다. 따라서 힌트는 성능 개선을 위해 꼭 필요한 수단이다. 힌트를 사용하기에 앞서, DBMS의 통계 정보가 정확한지, 필요한 인덱스들이 만들어져 있는지를 우선 확인하는 습관을 길러야 한다.
잘못된 힌트 사용은 빠르게 실행될 수 있는 SQL에게 걸림돌로 작용할 수 있다...!
힌트를 사용한 경우에는 SQL을 프로그램에 적용하기 전에 실행 계획을 확인하여 쓰고자 한 힌트가 제대로 반영되었는지 확인해야 한다. 그리고 제시한 힌트대로 실행 계획이 반영되지 않았을 때는 자신이 작성한 힌트에 잘못이 있는지부터 찾아보기 바란다.
2. 힌트의 문법 및 종류
2.1 문법
1) SELECT /+ 힌트 / ...
2) SELECT --+ 힌트 ...
위와 같이 힌트의 문법에는 2가지가 있는데, 주로 1)을 사용한다.
2.2 힌트의 종류
힌트의 개수는 엄청 많은데 튜닝을 할 때 없어서는 안될 힌트로 10개 정도 알고 있으면 좋다.
효율적으로 작성한 SQL일수록 힌트를 사용하여 튜닝할 필요가 없다. 힌트는 SQL 자체의 개선으로는 도저히 방법이 없는 경우에 쓰는 최후의 수단이다.
2.3 힌트의 사용
(1) ALL_ROWS
비용 기반 옵티마이저의 모드 가운데 ALL_ROWS 방식으로 옵티마이저를 설정하는 힌트이다. 이 모드로 SQL을 실행하도록 지시하면 마지막으로 출력될 행까지 최소한의 자원을 사용하여 최대한 빨리 가져오게 하는 실행 계획을 세운다.
SELECT /*+ ALL_ROWS */
e.name
FROM emp e
WHERE e.dept = '영업팀';(참고로 이 회사 사원의 반 이상은 영업팀에서 근무한다고 가정하자.)
실행 계획에 emp 테이블을 TABLE ACCESS FULL 하였는데, 이는 IX_부서 라는 인덱스가 존재함에도 불구하고 INDEX RANGE SCAN을 하지 않고 FULL TABLE SCAN 을 하는 것이 유리하다고 CBO가 판단하였기 때문이다.
(2) RULE
규칙 기반 옵티마이저의 접근 방식을 채택하게 하는 힌트이다.
아래는 규칙 기반 옵티마이저의 우선순위 15단계이다.
- ROWID에 의한 단일 행 실행
- 클러스터 조인에 의한 단일 행 실행
- HASH CLUSTER KEY에 의한 단일 행 실행
- UNIQUE KEY 또는 PRIMARY KEY에 의한 단일 행 실행
- 클러스터 조인
- HASH CLUSTER KEY
- INDEXED CLUSTER KEY
- 결합 인덱스
- 단일 컬럼 인덱스
- 인덱스에 의한 컬럼의 BOUNDED RANGE
- 인덱스에 의한 컬럼의 UNBOUNDED RANGE
- SORT MERGE JOIN
- 인덱스로 구성된 컬럼의 MAX 또는 MIN 처리
- 인덱스로 구성된 컬럼의 ORDER BY
- FULL TABLE SCAN
위 예시에서는 FULL TABLE SCAN(15번)보다 IX_부서 단일 컬럼 인덱스를 이용한 INDEX RANGE SCAN(9번)이 우선 순위에 있기 때문에 해당 실행 계획을 택하게 된다.
(3) FIRST_ROWS
FIRST_ROWS(n)
인수로 사용할 숫자만큼의 행이 빠르게 반환되는 실행 계획을 옵티마이저가 선택하도록 설정하는 힌트다.
이 힌트를 사용하면, 옵티마이전느 FULL TABLE SCAN 보다는 INDEX SCAN을 선호하며 Sort Merge Join 보다는 NL Join을 선호하게 된다. 개발자가 직접 사용하는 경우는 거의 드물다.
(4) INDEX
INDEX(table_name, index_name)
특정 인덱스를 사용하도록 강제로 제어하는 힌트로, SQL 튜닝을 할 때 많이 쓰는 힌트 중 하나다.
/+ INDEX(table_name, column_name) / 와 같이 특정 인덱스를 명확하게 지정하지 않고 특정 컬럼에 생성된 인덱스를 사용할 때 사용되는 힌트인데, 해당 컬럼을 선두에 둔 인덱스가 여러 개 있을 수 있기 때문에 가급적이면 인덱스명을 명확하게 지정해서 사용하는 것이 좋다. 단 From 절에서 테이블 이름의 별칭을 선언한 경우에는 table_name 대신 별칭을 사용해야 한다.
예시
SELECT login_id,
user_id,
...
FROM tb_org_user
WHERE login_id = :0
AND group_id = :1;실행계획
| Id | Operation | Name |
0 SELECT STATEMENT
1 TABLE ACCESS BY INDEX ROWID TB_ORG_USER
2 INDEX RANGE SCAN IX_ORG_USR1
인덱스 구조
| 테이블 이름 | 인덱스 | 구성 컬럼
TB_ORG_USR IX_ORG_USR1 GROUP_ID
IX_ORG_USR3 LOGIN_ID
LOGIN_ID 가 변별력이 훨씬 높은 컬럼임에도 불구하고, GROUP_ID 컬럼에 생성한 IX_ORG_USR1 인덱스를 사용하여 검색하고 있다. 이러한 상황이 발생하는 경우는 두 가지를 원인으로 추정할 수 있다.
1. 해당 테이블의 통계 정보가 잘못된 경우
2. 컬럼의 데이터 분포 편차가 큰 경우
SELECT group_id, count(*)
FROM tb_org_usr
GROUP BY group_id;GROUP_ID COUNT
-2 2
49 3192941
44 29255
47 2322
50 55271
...
2 20
위 결과에서 볼 수 있는 것처럼 GROUP_ID 값 가운데 일부 값이 지나치게 적어 이 값들을 토대로 실행 계획을 만들다 보니 GROUP_ID 에 만든 인덱스를 사용하는 것이 유리하다고 옵티마이저가 판단한 것이다. '-2', '2' 와 같은 값이 입력되는 경우에는 검색 속도가 빠를 수 있으나 그 외의 값들이 입력되는 경우는 검색 속도가 상대적으로 느려질 것이다. 따라서 GROUP_ID 보다 변별력이 높은 LOGIN_ID 컬럼에 생성된 인덱스 IX_ORG_USR3를 사용하도록 다음과 같이 힌트를 사용해야한다.
SELECT /*+ INDEX(TB_ORG_USR IX_ORG_USR3) */
login_id,
user_id,
...
FROM tb_org_user
WHERE login_id = :0
AND group_id = :1;물론 위와 같은 상황에서 IX_ORG_USR1 인덱스를 GROUP_ID + LOGIN_ID 의 결합 인덱스로 수정할 수 있겠지만 LOGIN_ID 값이 내부적으로 GROUP_ID 를 포함하는 값이었기 때문에 결합 인덱스를 고려하지 않았다. 이처럼 결합 인덱스의 생성을 고려할 때 컬럼들의 내부적인 의미와 포함 관계에도 주의해야한다.
(5) FULL
FULL(table_name)
특정 테이블에 대해 FULL TABLE SCAN 을 하고자 할 때 설정하는 힌트다. INDEX SCAN 이 적합하지 않음에도 불구하고 실행 계획에서 INDEX SCAN 을 채택하고 있는 경우 주로 사용한다.
(6) INDEX_ASC, INDEX_DESC
INDEX_ASC(table_name index_name)
INDEX_DESC(table_name index_name)
특정 인덱스를 사용하도록 지정함과 동시에, 인덱스를 읽는 방식을 오름차순 또는 내림차순 형태로 지정할 때 사용하는 힌트들이다.
일반적인 INDEX 힌트가 오름차순을 사용하므로 INDEX_ASC는 거의 사용하지 않는다. 하지만 그와 반대 기능을 가지고 있는 INDEX_DESC 힌트는 MAX 함수 등의 기능을 대신하여 종종 쓰인다.
SELECT /*+ INDEX_DESC(EMP PK_EMP) */
empno
FROM emp
WHERE ROWNUM = 1;이처럼 최대값 또는 최대값에 인접한 값들을 추출하고자 할 때, ORDER BY 를 사용하지 않고 INDEX_DESC 함수를 사용해서도 같은 결과를 얻을 수 있다.
(7) INDEX_FFS
INDEX_FFS(table_name index_name)
SQL문에서 사용하고 있는 컬럼들이 모두 인덱스에 있어서 테이블을 검색하지 않고도 인덱스만으로 검색이 가능한 경우 이 힌트를 사용할 수 있다.
SELECT /*+ INDEX_FFS(t1 IX_T1) */
FROM t1
WHERE c1 IS NOT NULL;위 SQL를 보면 WHERE절에서 비교하는 컬림이 c1 하나이면서, SELECT 절에서 가져오고자 하는 컬럼도 c1 하나로, 이 값은 IX_T1 이라는 인덱스에 저장된 값이다. 이 경우, 테이블을 검색할 필요없이 인덱스만 검색해도 원하는 결과를 가져올 수 있으므로 INDEX_FFS 힌트를 쓰는 것이 가능하다.
(8) ORDERED
조인 순서를 지정하는 대표적인 힌트다. 이 힌트는 FROM 절에 기술된 테이블의 순서대로 조인을 수행하도록 한다.
(9) LEADING
LEADING(table_name table_name table_name ...)
조인 순서를 지정하는 또 다른 힌트다. ORDERED는 FROM절에 기술된 테이블의 순서대로 조인하는 반면, LEADING 같은 경우는 사용자가 직접 조인 순서를 지정할 수 있다. 주로 드라이빙 테이블을 변경하고자 할 때 많이 사용한다.
(10) USE_NL
USE_NL(table_name table_name ...)
조인 방식 중 NL Join의 사용을 지정하는 힌트다. 주로 ORDERED나 LEADING과 같은 조인 순서를 지정하는 힌트와 함께 사용하는 것이 일반적이다.
(11) USE_HASH
USE_HASH(table_name table_name ...)
조인 방식 중 Hash Join의 사용을 지정하는 힌트다.
드라이빙 테이블의 검색 결과가 많은 경우, INNER TABLE과 조인을 수행할 때, NL Join보다 Hash Join을 수행하는 것이 유리하다.
