꿈꾸는 개발자, DBA 커뮤니티 구루비

1장 SQL의 활용

1장 SQL의 활용

관계형 데이터베이스에서 SQL이 차지하는 비중은 그 무엇보다 크다.
SQL은 결코 쉽지 않다.
SQL은 단순 명령어가 아니라 하나의 애플리케이션이다.
SQL를 최적화해야만 최적의 효과를 얻을 수 있고 그러기 위해 개발자는 집합적 사고와 비절차형 언어에 익숙해져야 한다.

SQL과 옵티마이져

SQL의 실행계획에 영향을 미치는 요소

인덱스와 클러스터(생성 주기, 생성시점)
옵티마이져 모드, 버젼
통계정보.(생성여부, 생성시기, 생성주기)
네트워크 부하

옵티마이져의 궁극적인 목표는 어느길로가면 가장 처리범범위를 최소화 시킬 수 있는냐는 것.
옵티마이져는 새로운 최적의 길을 만들어내는 것이 아니라 이미 존재하는 여러 길 중 최적의 길을 선택하려 노력하고 그 선택에 대한 책임은 옵티마이져보다 사용자의 책임이 훨씬 크다.(인덱스, 사용자된 조건, 클러스터링 여부..)

1.1 옵티마이져와 우리의 역할

관계형 데이터베이스의 이론 물리적인 연결고리가 없이 논리적인 연결고리만 있으면 원하는 데이터 액세스 가능하다.
과거 이론적인 수준을 옵티마이져가 발전함에 따라 관계형 데이터베이스 발전에 일조하게 되었고 관계형 데이터베이스의 많은 사용으로 이어졌다.
그러나, 옵티마이져는 전지전능하지 못함.
때문에 옵티마이징 팩터와 요구서(SQL)가 최소효과로 최대 효과를 얻도록 하는 것이 개발자의 도리.

1.2 옵티마이져의 최적화 절차

옵티마이저의 입장에서 질의(query) 처리 과정

Query Process단계

ParseSyntax, Security, Semantics의 체크 및 Simple Transformation 수행
Query Rewrite 서브질의와 뷰의 병합 수행,OR Expansion작업 수행
Optimization질의에 대한 액세스 경로를 결정
QEP Generation질의를 실행하는데 필요한 상세한 정보를 만든다.
Query ExecutionQEP에 따라 SQL문장을 실행한다.

Simple transformation의 예

Example Expression(From)	Transformation(to)
ename LIKE 'WARD'	ename='WARD'
ename IN ('KING','WARD')	ename='KING' OR ename = 'WARD'
ename=ANY/SOME('KING','WARD')	ename='KING' OR ename = 'WARD'
deptno != ALL(10,20)	deptno != 10 AND deptno !=20
sal BETWEEN 2000 AND 3000	sal >= 2000 AND sal <= 3000
NOT(sal<1000 OR comm is null)	sal >= 1000 AND comm IS NOT NULL

뷰병합예

Example Expression(From)	View Merging(tO)
{code:sql} CREATE VIEW emp_d10 AS SELECT * FROM emp WHERE deptno = 10 AND empno > 11910; {code}	{code:sql} SELECT empno FROM emp WHERE deptno = 10 AND empno > 11910; {code}

서브질의 병합 예(Single Row Sub-Query)

Example Expression(From)	Sub-Query Mergin(to)
{code:sql}SELECT * FROM dept WHERE deptno = (SELECT deptno FROM emp WHERE empno < 12501 );{code}	{code:sql}SELECT * FROM dept WHERE deptno = <evaluated_value>; {code}

소프트 파싱과 하드파싱

SQL문장이 옵티마이져에 의해 처리된 후 결과물로 QEP가 생기게된다.
이 QEP는 SGA의 공유 풀(Shared pool)에 캐쉬화 관리되고 같은 SQL문장이 실행되면 재활용하게 된다.
SQL이 실행되면 문장 텍스트 스르링을 해쉬함수를 통과시켜 결과 값에 해당하는 버킷에 메달린 체인 정보에서 같은 SQL문장이 존재하는지 찾는 처리절차를 수행하게 된다.
같은 SQL문장을 찾은 후 같은버젼의 SQL이 존재하는지 찾게된다.(소프트파싱)
- 같은버젼이란, 같은 SQL문장이지만 대/소문자, 공백, 스키마(SCOTT.EMP OR SYS.EMP), 변수타입, 변수길이 등에 의해서 서로 다른 버젼이 된다.
만약 체인 정보에서 같은 SQL문장 발견하지 못하면, 해당 문장(Parsing/optimizing된)을 저장하기 위해 공유 풀로부터 메모리를 확보 받고 저장(체인생성)하게 된다.(하드파싱)
하드파싱은 소프트파싱에 비해 작업량이 많다.

1.3 옵티마이져의 형태

1.3.1 규칙기준의 옵티마이져

현실을 감안해 관계형 데이터베이스를 상품화하려는 사람들에 의해 만들어진 임시형

규칙기준의 옵티마이져는 우선순위

규칙기준의 옵티마이져의 우선순위

ROWID로 1로우 액세스
클러스터 조인에 의한 1로우 액세스
Unique HASH Cluster에 의한 1로우 액세스
Unique INDEX에 의한 1로우 액세스
CLUSTER 조인
Non Unique HASH Cluster Key
Non Unique Cluster Key
Non Unique 결합 인덱스
Non Unique 1컬럼 인덱스
인덱스에 의한 범위처리
인덱스에 의한 무범위처리
SORT MERGE 조인
인덱스 컬럼의 MIN, MAX 처리
인덱스 컬럼의 ORDER BY
전체 테이블 스캔

예

emp테이블에 A인덱스가 deptno로 구성
B인덱스가 deptno + empno로 구성되어 있다면
옆 SQL 문장은A인덱스를 사용 하게 된다.
조건이 Bounded Range Search(Between)로 왔기 때문에
SQL 문장에서 (A)와 (B)의 랭킹은 (A) ==> Rank 9 , (B) ==> Rank 10 조건이 되므로 싱글 칼럼 인덱스를 사용하게 된다.

단점

1,000,000로우를 가진 TAB1과 100로우를 가진 TAB2의 로우 수를 알지 못 해 어느쪽을 전체 테이블 스캔하는 것이 유리한지 구별하지 못한다.

장점

매우 규칙적이고 분명하여 사용자가 정확히 예측이 가능하며 이는 사용자가 미리 예측하여 원하는 방법으로 실행계획을 정확히 제어가 가능하다는 것을 의미한다.
보편타당성(현실적으로 키큰 사람이 작은 사람에 비해 농구를 잘 할 수 있는 확률)

변화

오늘날의 규칙기준 옵티마이져는 많이 변해있음.
명확한 우선순위에 의존하지 않는 원칙을 알 수 없는 경우가 발생.(테이블의 크기등에 따른 다른 가중치 반영 예상)
최대장점인 명확한 기준이 흔들려 이도 저도 아닌 상황이 되었음.

실습

규칙기준 옵티마이져(RBO)

1.3.2 비용기준 옵티마이져

관계형 데이터베이스가 추구하는 이상형
처리방법들에 대한 비용을 산정(통계정보를 참조)해 보고 그 중에서 가장 적은 비용이 들어가는 방법을 선택

장점

현실을 감안한 판단을 할 수 있다는 것
사용자의 실수로 인해 매우 나빠지는 실행계획은 어느정도 피할 수 있다.

단점.

실행계획을 예측하기 너무 어렵다.
인덱스 재생성, 통계정보 생성 누락, 통계정보 생성 주기에 따라 실행계획이 변해 버릴지도 모름.(사용자가 예측하기 힘듬)

실습

비용기준 옵티마이져(CBO)

비용기준 옵티마이져도 규칙기준 옵티마이져와 마찬가지로 전략적인 인덱스 구성과 효과적인 SQL을 사용하는 것이 우선적이다

DBMS_STATS패키지

해당 시스템만의 독특한 성능특성을 실제적으로 측정함으로써 옵티마이져에게 더 정확한 정보를 제공할 수 있게 한다.
Oracle 8.1.5이상부터 통계정보 수집에 DBMS_STATS 패키지를 권장
참고 2006년6월3일스터디자료

1.4 옵티마이져의 한계

통계정보를 토대로한 정확한 처리범위를 예측할 수 있느냐에 대한 한계.
모든 값들에 대한 정확한 분포도를 알 수 있다면 정확한 실행계획 수립이 가능하지만, 일일이 모든 분포도를 보유한다는 것은 불가능.
결합된 컬럼에 대해 일일이 분포도를 보유할 수 없다.

1.5 개발자의 역할

개발자는 SQL을 잘 활용하는 방법을 익혀 두는 것이 필요하다.

SQL을 잘 활용한다는 것

지금까지 구현해왔던 거의 모든 처리 방법의 변화
처리를 위한 접근방법의 변화

사고의 혁신

비절차형 처리
집합적 사고에의한 처리

SQL은 명령어가 아니다 하나의 애플리케이션이다.

참고자료

오라클클럽
운쓰님의 블로그
Oracle Optimizer의 원리 이해 및 SQL & 애플리케이션의 튜닝 / 최세훈 | 한국오라클 상 하