데이터베이스 옵티마이저에 대한 간단 설명

데이터베이스를 사용하다 보면 여러 가지 인덱스를 만들어 놓는 경우가 많다. 어느 테이블에서 무엇인가 조회를 하는 방법은 그 테이블에 사용되는 다양한 인덱스 중 어느 인덱스를 사용할지, 조인을 했다면 어느 테이블부터 조회할지 등 실행 방법에는 다양한 경우가 있을 수 있다. 이러한 방법 중에 최적의 방법을 실행하는 것이 가장 효율적일 것이다.

여기서 어떤 실행에 대해서 최적의 루트를 계산해 실행하는 것이 '옵티마이저'이다.

 

옵티마이저에는 크게 CBO(Cost Based Optimizer) 즉, 비용 기반 옵티마이저와 RBO(Rule Based Optimizer) 즉, 규칙 기반 옵티마이저로 두가지로 나뉠 수 있다. 

 

RBO는 규칙 기반 옵티마이저로서 미리 정해진 우선 순위 규칙에 따라 접근 경로를 결정한다. 순위가 높은 규칙이 낮은 규칙보다 우선 적용된다. 비록 잘못된 우선순위의 규칙이 적용되더라도 예측이 가능하며, 안정적이고 실행계획의 제어가 쉽다. 대부분 CBO 방식이지만 실행계획을 확실히 제어할 수 있다는 점 때문에 DBA나 고급 개발자들은 이 방식을 선호하기도 한다. 

 

CBO는 비용 기반 옵티마이저로서 통계정보에 따른비용을 계산해 가장 최소한의 비용이 소모되는 접근 경로를 결정한다. 여기에는 I/O 비용뿐만 아니라 CPU 연산 비용 및 메모리 비용까지 포함된다. 

여기서 비용은 논리적 비용이다. 논리적 비용이란 어떻게 산출됐는지 구체적으로 알 수 없다거나 공개할 수 없다는 말과 같다. 

CBO는 통계정보가 잘못됬거나 최신 정보를 제대로 반영하지 못한다면, 잘못된 접근 경로를 선택할 것이다.

잘못된 실행계획을 세울 수가 있기에 너무 옵티마이저를 믿으면 안된다. 그럼에도 CBO를 사용하는 이유는 대용량 시스템에서는 CBO가 최선이라기보다는 RBO가 대안이 될 수 없기 때문이다. 

아래는 CBO와 RBO의 특징들을 간단히 정리한 것이다. 

 

RBO

1. 규칙 기준 최적화
2. SQL 실행 순서 규칙 우선 순위
3. 청소기(수동)
4. 개발자의 능력과 의지에 좌우
5. 능숙한 쿼리 전문가에게 유리
6. 오라클 11g부터 기술지원 안 함
7.  간단한 규칙 기준들(1. ROWID에 의한 단일행 , 8. 결합 인덱스, 9. 단일 인덱 수, 15 전체 테이블 스캔 등)

 

CBO

1. 비용 기준 최적화
2. 통계정보를 이용한 비용 우선순위
3. 청소 로봇(자동)
4. 옵티마이저 알고리즘 성능이 좌우
5. 평균적인 쿼리 품질 보증함
6. 오라클 7i부터 지원 시작
7. IO + CPU + 메모리 + 네트워크, 통계정보: 테이블 정보, 인덱스 정보, 컬럼의 분포도 등

 

위에서 언급했다시피 CBO는 잘못된 실행계획을 세울 수도 있다. 이와 같은 경우 우리는 힌트 절을 통해 잘못된 실행계획을 바로잡을 수 있다. 

옵티마이저는 최적의 실행계획을 수행하는 알고리즘일 뿐 완벽하지가 않다. 그렇기에 옵티마이저가 올바른 판단을 하도록 아래와 같은 부분을 제공하거나 제어해야 한다.

 

1. 최적의 인덱스 구성하기
2. 올바른 SQL문 작성하기
3. 주기적으로 최신의 통계정보로 갱신하기
4. 힌트절을 추가해 옵티마이저 제어하기

 

만약 하나의 테이블에 여러 가지 인덱스가 있고 다른 테이블과도 조인을 하는 경우 옵티마이저는 최적을 결과를 찾아서 실행계획을 세울 것이다. 하지만 내가 원하지 않았던 방향으로 세울 가능성도 있기에 미리미리 힌트 절을 추가해서 옵티마이저가 내가 원하는 방향으로 실행계획을 세우도록 할 수 있다. 

 

 

한 가지 더 중요한 내용을 언급하자면 쿼리를 작성하는 것에도 옵티마이저를 생각해야 한다.

대규모 프로젝트에서 어떠한 규칙 없이 각자 SQL 구문을 작성한다면 수백 개의 쿼리 패턴이 나타날 것이다. 이 점은 다른 사람(예를 들면 프로젝트를 인수인계받는 사람)이 봤을 때, 수백 개의 서로 다른 패턴과 맞다 뜨리게 되어서 익숙해지는데 오랜 시간이 걸리고 문제 발생 시 빠르게 대응할 수 없을 것이다. 

그렇기에 정해진 규칙에 따라 쿼리를 작성하는 것이 좋은 쿼리이고 좋은 성능을 보장하는 것이다.  

이것은 옵티마이저가 동일한 쿼리로 인식하도록 작성하는 것도 포함한다. 아래의 예시를 봐보자

 

SELECT * FROM 고객정보
Select * From 고객정보
select * from 고객정보
select    *   from    고객정보

 

 

위의 SQL 구문은 문법적 오류가 없으며 동일한 결과를 얻을 수 있다. 하지만 개발자의 성향에 따라서 이와 같이 서로 다른 방식으로 표현한다면 이는 비용 증가로 이어질 수 있다. 왜냐하면 사람은 동일한 쿼리로 인식하지만, 옵티마이저는 서로 다른 쿼리로 인식하기 때문이다. 

아래의 다른 예시를 봐보자.

 

SELECT * FROM 고객 WHERE 고객번호 = 1234
SELECT * FROM 고객 WHERE 고객번호 = 5678

 

 

오라클 옵티마이저는 위 SQL 구문을 서로 다른 쿼리로 인식한다. 하지만 다음과 같이 바인드 변수를 사용하면 동일한 쿼리로 인식한다. 

 

SELECT * FROM 고객 WHERE 고객번호 = :CUST_NO

 

 

사람은 SQL 구문의 내용을 보면서 쿼리의 동일성 여부를 판단하지만, 옵티마이저는 SQL 구문을 아스키 값으로 계산해 동일성 여부를 판단한다. 그렇기에 같은 결과를 얻더라도 대소문자, 띄어쓰기, 줄 넘기기, 주석 등의 표현 방식이 하나라도 다르면 다른 SQL 문으로 인식하기에 성능이 떨어진다.

그렇기에 조건값 대신에 바인드 변수를 사용하고 표준화된 작성 규칙에 따라 쿼리를 작성하는 것이 옵티마이저를 다루는 데 있어서 성능 향상을 이룰 수 있을 것이다.