DB 옵티마이저

옵티마이져의 목표? SQL로 요구된 결과를 최소의 비용으로 처리할 수 있는 처리  경로를 결정한다.

 

SQL과 옵티마이저

• SQL은 처리 절차를 기술하는 것이 아니라, 결과에 대한 요구일 뿐입니다.
• 처리 절차는 옵티마이저가 생성합니다. 즉 진정한 프로그래머는 옵티마이저입니다.
• 없는 처리 방법을 생성해주는 것이아니라, 이미 존재하는 처리 방법을 단지 찾아줄 뿐입니다.
• 사용자가 부여한 영향 요소에 따라 논리적으로 존재하는 처리 방법이 달라집니다. (결국, 책임은 사용자에게 있습니다.)
• 동일한 결과를 얻을 수 있는 경로는 많으나 효율성의 차이는 큽니다.
• 옵티마이저는 절대 전지전능하지 않습니다. : 그래서 우리의 역할이 필요 합니다.

 

옵티마이져에 영향을 미치는 요소

•  SQL의 형태에 따라
• 인덱스 클러스터링
• 연산자의 형태( = , like, 상수, 변수)
• 통계 정보
• 힌트 사용

select * from tab1 x, tab2 y, tab3 z
where x.key =y.key
and z.key = x.key
and x.col1 = '10'
and x.col2 <> 111
and x.col3 like 'ABC%'
and y.col4 between '10' and '50'

어떤 인덱스를 쓸까? 어떤 Join 방법을 쓸까? 드라이빙을 어떤 테이블을 먼저 할까?

 

1) 규칙 기반 옵티마이저(Rule-Based Optimizer)

미리 정해 놓은 규칙에 따라 실행계획을 수립합니다. 여기서 '규칙'이란, 액세스 경로별 우선순위로서, 인덱스 구조, 연산자, 조건절 형태가 순위를 결정짓는 주요입니다.

• 인덱스 구조나 사용 연산자에 부여권 순위로 최적경로 결정
• 통계 데이터 전혀 가지지 않음
• 데이터 분포도를 고려하지 않으므로 경우에 따라 비현실적인 처리 경로가 수립될 수 있다.
• 수립될 처리 경로 예측 가능
• 사용자가 원하는 처리경로로 유도하기가 용이(수동카메라)
• 일반적인 보편타당성이 있음(생각보다 높은 신뢰성)

 

2) 비용기반 옵티마이저(Cost-Based Optimizer)

말 그대로 비용을 기반으로 실행계획을 수립합니다.  여기서 ‘비용’이란, 쿼리를 수행하는데 소요되는 일량 또는 시간을 뜻합니다. 비용을 산정할 때 사용되는 오브젝트 통계 항목으로는 레코드 개수, 블록 개수, 평균 행 길이, 칼럼 값의 수, 칼럼 값 분포, 인덱스 높이, 클러스터링 팩터 같은 것들이 있습니다. 오브젝트 통계뿐만 아니라 최근에는 하드웨어적 특성을 반영한 시스템 통계정보(CPU 속도, 디스크 I/O 속도 등)까지 이용합니다.

• 통계정보를 이용해 실질적인 비용을 계산하여 최소비용 선택
• 실제 데이터의 구성 상태에 따른 현실적인 처리경로 수립
• 이론적으로 규칙기준에 비해 훨씬 진보된 형태
• 전문지식이 부족하더라도 극단적인 악성 실행계획은 피해갈 수 있음
• 수림될 처리경로 예측이 곤란(럭비공)
• 원하는 처리경로로 유도하기가 곤란(자동 카메라)
• 생각하는 것 보다 똑똑하다

 

Oracle은 규칙 기반 옵티마이저로 시작하여 비용 기반 옵티마이저도 지원하였으나 Oracle 10g 부터는 규칙 기반 옵티마이저 지원을 중단하였습니다. SQL Server는 비용 기반 옵티마이저를 지원합니다.

규칙 기반 옵티마이저는 수동 카메라, 코스트 베이스 옵티마이저는 자동 카메라를 생각하면 됩니다.

시대의 흐름에 따라 기술이 발전함에 따라 옵티마이저도 더욱 똑똑해지고 하드웨어 성능 또한 크게 향상 되어 비용 기반 옵티마이저가 대세가 되었습니다.

아무리 옵티마이저가 똑똑해 졌더라도  옵티마이저를 이해하고 제대로 활용할 줄 알아야 합니다. 제대로 활용 하려면 데이터 모델링을 제대로 해야하고 설계할 줄 알아야 합니다.

제대된 데이터베이스 설계를 하지 않으면 SQL을 제대로 사용할 수가 없습니다.

즉, 모델링을 잘해야 하는 이유는 단순합니다. SQL을 잘 사용하기 위해서죠.

 

[데이터베이스/게임 서버 프로그래머가 알아야 할 RDBMS] - 게임 서버 프로그래머가 알아야 할 RDBMS

[데이터베이스/게임 서버 프로그래머가 알아야 할 RDBMS] - 옵티마이저 맛보기

[데이터베이스/게임 서버 프로그래머가 알아야 할 RDBMS] - 테이블 조인 하면 느리다?

[데이터베이스/게임 서버 프로그래머가 알아야 할 RDBMS] - 리커시브 모델의 활용

[데이터베이스/게임 서버 프로그래머가 알아야 할 RDBMS] - 아크 모델(exclusive or)에서의 주의점

[데이터베이스/게임 서버 프로그래머가 알아야 할 RDBMS] - 부분범위 처리와 전체 범위처리

[데이터베이스/게임 서버 프로그래머가 알아야 할 RDBMS] - 인덱스의 제대 사용하자

[데이터베이스/게임 서버 프로그래머가 알아야 할 RDBMS] - DB 옵티마이저

[데이터베이스/게임 서버 프로그래머가 알아야 할 RDBMS] - Nested Loop/Sort Merge/Hash Join

[데이터베이스/게임 서버 프로그래머가 알아야 할 RDBMS] - DB 테이블 클러스터링 팩터

[데이터베이스/게임 서버 프로그래머가 알아야 할 RDBMS] - DB 절차적 사고 VS 집합적 사고

[데이터베이스/게임 서버 프로그래머가 알아야 할 RDBMS] - SQL 집합의 가공

[데이터베이스/게임 서버 프로그래머가 알아야 할 RDBMS] - SQL IN의 특징과 IN의 활용

[데이터베이스/게임 서버 프로그래머가 알아야 할 RDBMS] - 서브 쿼리 활용

[데이터베이스/게임 서버 프로그래머가 알아야 할 RDBMS] - 인라인 뷰의 활용