SQL 최적화 과정과 옵티마이저 소개 및 역할안내

SQL 최적화 과정

오라클을 기준으로 SQL의 최적화 과정은 다음과 같다.

1. SQL 파싱

-> 파싱트리 생성

-> 문법적 오류 확인

-> 의미상 오류 확인 (없는 컬럼, 테이블 접근)

2. SQL 최적화

-> 옵티마이저가 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 가장 최적의 실행경로로 선정

3. 로우 소스 생성

-> 선정된 실행경로를 실제 실행 가능 코드로 변경 (Row-Source Generator)가 역할 수행

그럼 이런 최적화를 진행하는 옵티마이저는 어떤것이고 또 어떻게 진행이되는가?

SQL 옵티마이저란?

- 옵티마이저는 사용자의 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로를 선택해주는 DBMS 엔진을 말한다.

최적화 단계

- 쿼리를 수행할 실행계획 찾기

- Data Dictionary에 수집한 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예산비용 산정.

- 최저 비용 선택

그럼 옵티마이저를 활용하여 SQL을 튜닝해보자.

먼저 튜닝에 사용할 테이블을 만들어보자.

create table t as select d.no, e.* from scott.emp e, (select rownum no from dual connect by level <= 1000) d;

그리고 옵티마이저에 사용할 인덱스도 만들어보자.

create index t_x01 on t (deptno, no);
create index t_x02 on t (deptno ,job, no);
 

마지막으로 옵티마이저가 참고할 통계정보도 만들자.

exec dbms_stats.gather_table_stats(user, 't');

그럼 이를 이용해서 쿼리의 실행계획을 확인해보자. 실행할 쿼리와 화면은 아래와같다.

select * from t where deptno = 10 and no = 1;

결과를 보면 인덱스01이 선택된것을 확인할 수 있다. 왜 인덱스 01이 선택되었는가?

COST 컬럼을 보면 인덱스 01의 경우 비용이 2이다. 그러면 인덱스 02는 비용이 어떨까? 힌트를 줘서 인덱스 02를 사용하도록 지정해보자.

select /*+ index(t t_x02) */ * from t where deptno = 10 and no = 1;
 

비용이 11인것을 확인할 수 있다. 그래서 인덱스 01이 선택된 것이다.

비용은 쿼리를 수행하면서 발생하는 I/O횟수와 소요시간을 표현한 것이다.

-> 정확한것은 아니라 예상치이다. 그렇기 때문에 옵티마이저는 자신이 알고있는 통계정보를 이용해서 예상치를 만든다. 그렇기 때문에 정확치 않을 수 있기에 그때는 위에서 사용한 방법으로 힌트를 줘서 수행한다.

그럼 Full Scan도 힌트를 줘서 확인해보자.

select /*+ full(t) */ * from t where deptno = 10 and no = 1;

이번시간은 SQL 옵티마이저에 대한 개념과 옵티마이저를 이용한 간단한 튜닝을 진행했다.

다음시간에는 우선 옵티마이저에게 힌트를 주는법을 알아보고 하드파싱과 소프트 파싱에 대해 알아보자.

.