SQL 처리 과정과 I/O

1.1  SQL 파싱과 최적화

SQL : Structed Query Language

SQL은 기본적으로 구조적(Structured)이고, 집합적(set-based)이고 선언적(declarative)인 언어

SQL 최적화 : DBMS 내부에서 프로시저 작성하고, 컴파일해서 실행 가능한 상태로 만드는 것

 

- SQL 파싱

• 파싱 트리 생성 : SQL 문을 이루는 개별 구성요소를 분석해서 파싱 트리 생성
• Syntax 체크 :  문법 오류 없는지 확인
• Semantic 체크 : 의미상 오류가 없는지 확인

- SQL 최적화 -> 옵티마이저

• 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행 경로 생성 후 비교한다. -> 성능 결정의 핵심 엔진

- 로우 소스 생성 -> 로우 소스 생성기

• SQL 옵티마이저가 선택한 실행 경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅

SQL 옵티마이저

1)    사용자로부터 전달받은 쿼리를 수행하는데 후보군이 될만한 실행 계획 찾아냄

2)    데이터 딕셔너리에 미리 수집해 둔 오브젝트 통계 및 시스템 통계 정보를 이용해 각 실행 계획의 예상 비용 산정

3)    최저 비용을 나타내는 실행 계획 선택

 set autotrace traceonly exp;

사용 후에 쿼리 실행하면, 실행계획 볼 수 있다.

 

옵티마이저 힌트

select /*+ index(A 고객_PK) */고객명, 연락처, 주소, 가입일시
FROM 고객 A
WHERE 고객ID  = ‘00000008’

 

주의사항

1. 힌트 안에 인자를 나열할 땐 콤마를 사용할 수 있지만, 힌트와 힌트 사이에 사용하면 안된다.
2. 테이블을 지정할 때, 스키마명을 명시하면 안된다
3. FROM 절에서 ALIAS를 지정했다면, 힌트에도 반드시 ALIAS를 사용해야 한다

 ※자주 사용하는 힌트 목록

ALL_ROWS : 전체 처리속도 최적화

FULL : Table Full Scan 으로 유도

ORDERED :  FROM 절에 나열된 순서대로 조인

 

1.2  SQL 공유 및 재사용

 라이브러리 캐시(Library Cache) :

• SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간
• SGA(System Global Area) 구성요소 : 서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터아 제어 구조를 캐싱하는 메모리 공간

소프트 파싱 : 사용자 sql문 전달 -> DBMS에서 SQL 파싱 -> 라이브러리 캐시에 존재 -> 실행

하드파싱 : 사용자 sql문 전달 -> DBMS에서 SQL 파싱 -> 라이브러리 캐시에 존재 x ->최적화 -> 로우 소스 생성 -> 실행

 

SQL 옵티마시저가 SQL을 최적화 할때 사용하는 정보

• 테이블, 컬럼, 인덱스, 구조에 관한 기본 정보
• 오브젝트 통계 : 테이블 통계, 인덱스 통계, (히스토그램을 포함한) 컬럼 통계
• 시스템 통계 : CPU 속도, Single Block I/O 속도, Multiblock I/O 속도 등
• 옵티마이저 관련 파라미터

하나의 쿼리를 수행할 때, 무수히 많은 실행 경로 도출, 딕셔너리와 통계 정보를 읽어 각각에 대한 효율성 판단

데이터베이스에서 이루어지는 처리 과정은 대부분 I/O 작업에 집중되는 반면, 하드파싱은 CPU를 많이 소비

-> hard 한 작업을 거쳐 생성한 내부 프로시저를 한 번만 사용하고 버릴 수 x : 비효율

-> 라이브러리 캐시가 필요한 이유