오라클자바커뮤니티에서 설립한 오엔제이프로그래밍 실무교육센터
(신입사원채용무료교육, 오라클SQL, 튜닝, 힌트,자바프레임워크, 안드로이드, 아이폰, 닷넷)
Optimizer는 SQL문장을 해석하여 실행 계획(가장 적절한 데이터의 추출 경로)을 만들어 주는 내부 구조를 이야기 합니다. SQL 문장의 실행을 위해 요구되는 최소한의 프로세싱 시간과 I/O를 위하여 참조된 데이타의 효과적인 경로를 결정하기 위하여 사용 합니다. 즉 실행계획(execution plan)을 세우고 문장을 실행하기 위한 가장 효과적인 방법을 선택 하는 것 입니다.
오라클은 RBO(rule-based optimization, 규칙기반)와 CBO(cost-based optimization, 비용기반) 두가지를 지원 하는데 CBO는 Oracle7 부터 도입이 되었습니다. RBO는 간단히 정해진 규칙으로만 실행 경로를 찾아 데이터를 추출 하는 것(15가지 정도의 실행 순서 중 가장 빠른 순서의 방법으로 추출, SQL문의 문법에 의해 주로 실행 순서가 결정)으로 CBO를 사용 가기를 권장 합니다. CBO는 개발자가 구사한 SQL문장의 테이블에 인덱스가 있는지 어떤 종류의 인덱스가 있는지, 데이터 건수와 분포도등을 이용해서 어떤 경우엔 인덱스를 이용해서, 어떤 경우엔 인덱스를 이용하지 않고 데이터를 추출 합니다. 즉 가장 빠른 결과를 내는(비용이 작게 드는) 순서에 따라 데이터를 꺼낸다는 의미 입니다. 당연히 RBO 보다는 나은 방법이죠^^;
옵티마이저의 구조
사용자가 SQL문장을 오라클 서버에 보내게 되면 이 SQL문장은 SQL문법, 테이블에 사용자가 권한이 있는지, 테이블이 있는지 등에 대한 파싱 단계를 거치면 Oracle Optimizer가 RBO인지 CBO인지를 판단하여 SQL문을 최적화 합니다. 이렇게 최적화된 SQL문이 실행되어 결과를 사용자에게 보여 주는 것 입니다.
1. 파서
파서는 구문분석(Syntax Analysis)과 의미분석(Semantic Analysis) 2가지 기능을 수행 하는데 구문 분석은 단순히 SQL문장이 문법에만 맞게 쓰여 졌는지를 확인하며, 의미분석의 경우 객체에 대한 권한과 속성 등이 정확한지 검사하는 역할을 합니다. 이 단계가 끝나면 SQL문은 Parsing Tree 형태로 변형되어 옵티마이저에게 넘어 가게 되는 것 입니다.
2. 옵티마이저(Optimizer)
Optimizer는 받은 파싱 트리를 이용해 SQL문의 실행 결과를 만드는 가장 효율적인 방법을 구하기 위해 RBO 또는 CBO 둘중 하나의 방법을 택하게 됩니다.옵티마이저의 결과물은 최적의 실행 방법을 위한 실행 계획이며 앞에 있는 것처럼 RBO아니면 CBO를 택하게 되는 것입니다.
3. 로우 소스 생성기
옵티마이저로부터 받은 실행 계획을 내부적으로 처리 하기 위하여 자세한 방법을 생성 하는 단계 입니다. 로우 소스(Row Source)란 각 단계별 실행 계획을 실제로 구현 하는 각각의 인터페이스 이며 실제로 테이블 엑세스 방법, 조인 방법, 정렬등에 위한 다양한 로우 소스가 생성 됩니다. 따라서 이 단계에서는 실행 계획에 해당 하는 트리 구조의 로우 소스들이 생성 됩니다.
4. SQL 실행 엔진
SQL의 실행 계획을 수행 하는 컴포넌트로 로우 소스를 가지고 실행을 해서 결과를 만들어 냅니다.
옵티마이저의 SQL문 처리 단계
SQL문의 실행 방법은 엑세스 하는 테이블, 인덱스의 순서에 따라 여러가지가 있을 수 있습니다. 옵티마이저는 참조된 테이블, 인덱스와 같은 것들과 지정된 조건들을 고려 하게 되는데 이것은 RBO, CBO에 모두 해당이 되는 것 입니다.
SQL문의 실행 순서를 자세히 보도록 하겠습니다. 우선 SQL문장에 대해 표현식과 조건을 평가 합니다. 상관 쿼리등과 같이 복잡한 SQL문에 대해 옵티마이저는 조인으로 변형을 하며 비용기반(CBO), 규칙기반(RBO) 중 하나를 선택 합니다. 그런 다음 옵티마이저는 가능한 엑세스 경로를 하나 이상 선택 하며 만약 SQL문장에 의해 두 개 이상의 테이블이 조인 된다면 먼저 어떤 테이블을 조인하며 그 결과를 또 어떤 테이블과 조인 할지 등을 결정 합니다. 또는 조인 문장 등에 위해서 사용 할 조인 방법(해시조인,NL조인,소트 머지 조인)에 대해 결정을 하기도 합니다.
다음 SQL문장의 실행 계획을 참고로 보겠습니다.
SQL> select ename, job, sal, dname
2 from emp, dept
3 where emp.deptno = dept.deptno
4 and not exists (select * from salgrade
5 where emp.sal between losal and hisal);
선택된 레코드가 없습니다.
아래는 autotrace를 한 경웁니다.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=14 Bytes=588)
1 0 MERGE JOIN (ANTI) (Cost=10 Card=14 Bytes=588)
2 1 SORT (JOIN) (Cost=7 Card=14 Bytes=476)
3 2 HASH JOIN (Cost=5 Card=14 Bytes=476)
4 3 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=52)
5 3 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=294)
6 1 FILTER
7 6 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'SALGRADE' (Cost=2 Card=5 Bytes=40)
해석을 해보면 오라클은 4,5단계에서 DEPT와 EMP를 FULL SACN 해서 결과를 2단계에 반환 합니다. 즉 EMP 14건과 DEPT 4건을 HASH JOIN한 후 SORT 시킨다는 의미 입니다. 그런 다음 SALGRADE 테이블을 FULL SCAN 해서 SORT 한 후 조건 비교를 합니다. 즉 EMP와 DEPT에서 조인한 한건의 데이터의 SAL가 SALGRADE의 LOSAL과 HISAL에 포함 안되는 것이 있는지 말입니다. 포함 안되는 데이터가 있다면 1단계로 반환하고 아니면 이전 단계를 계속 반복 하게되는 겁니다.
댓글 없음:
댓글 쓰기