[ SQL 파싱과 최적화 ]
- SQL은 기본적으로 구조적(Structured)이고 집합적(set-base)이고 선언적(declarative)인 질의언어
- 옵티마이저(Optimizer) : 프로시저를 만들어내는 DBMS 내부 엔진.쿼리를 분석해서 어떤 명령들(프로시저)로 바꿈 -> "최적화". 클래스를 생성하는 자바의 컴파일러와 같은 역할이라고 볼 수 있다.
- SQL 최적화 : DBMS내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정.
- SQL 최적화 단계
- SQL 파싱 : 토큰 단위로 쪼개고->분석. 사용자로부터 SQL을 전달받아 SQL 파서(parser)가 파싱을 진행. -> 파싱트리생성, Syntax 체크(문법에 맞는가?), Semantic 체크(의미가 맞는가?)진행
- SQL 최적화 : 옵티마이저가 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성해서 비교 후 가장 효율적인 하나를 선택.
- 로우 소스 생성 : SQL 옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅.
- SQL 옵티마이저
- 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로를 선택해주는 DBMS 핵심 엔진. 만약 옵티마이저가 자동으로 선택해주는 경로가 최선이 아니라고 판단되면 사용자가 수동으로 설정 가능 (오라클 힌트)
- 옵티마이저의 최적화 단계
- 사용자로부터 전달받은 쿼리를 수행하는 데 후보군이 될만한 실행계획들을 찾아냄
- 데이터 딕셔너리에 미리 수집해둔 오브젝트 통계 및 시스템 통계정보로 각 실행계획의 예방비용을 산정.
- 최저비용을 나타내는 실행 계획 선택.
- 실행계획과 비용 : SQL옵티마이저는 선택한 경로가 마음에 들지 않으면 검색모드를 변경하거나 특정 옵션을 추가하여 원하는 경로로 바꿀 수 있다. SQL 실행계획에 표시되는 Cost도 예상치이며 실행경로를 선택하기 위해 옵티마이저가 여러 통계정보를 활용해 계산해낸 값. 즉 실제 수행할 때 발생하는 I/O또는 시간과 차이가 날 수 있다.
- 옵티마이저 힌트 : SQL옵티마이저도 대부분 좋은 선택을 하지만 완벽하지 않고, SQL이 복잡할수록 실수할 가능성도 발생한다. 따라서 개발자가 직접 더 효율적인 액세스 경로를 찾을 수 있는 경우 옵티마이저 힌트를 이용해 데이터 액세스 경로를 바꿀 수 있다. (옵티마이저 힌트 사용은 주석기호에 +를 붙임)
[ SQL 공유 및 재사용 ]
- 소프트파싱 VS 하드파싱
- 라이브러리 캐시(Library Chache) : SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용 할 수 있도록 캐싱해 두는 메모리 공간
- 소프트 파싱(Soft Parsing) : SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것.
- 하드 파싱(Hard Parsing) : SQL을 캐시에서 찾는데 실패해 최적화 및 로우 소스 생성 단계까지 모두 거치는 것.
- SQL 최적화 과정이 하드(Hard)한 이유
- 하나의 쿼리를 수행하는 데 있어 후보군이 될만한 무수히 많은 실행경로를 도출하고 짧은 순간에 딕셔너리와 통계정보를 읽어 각각에 대한 효율성을 판단하는 과정이 가벼울(Soft) 수 없음.
- DB에서 이루어지는 처리 과정은 대부분 I/O작업에 집중되는 반면 하드파싱은 CPU를 많이 소비하는 작업.
- 어려운 작업을 거쳐 생성한 내부 프로시저를 한번만 사용후 버린다면 비효율적이므로 라이브러리 캐시에 임시저장하여 히트수(Hit Rate)를 높임.
- 바인드 변수의 중요성 : SQL은 대소문자만 달라도(변수가 다르거나, 공백이 더 있거나... 등) 옵티마이저는 다른 SQL로 인식함. => 다른 경우는 어쩔 수 없지만 변수만 다른경우(ex. 모든 쿼리가 똑같고 WHERE절의 변수만 다른경우. => WHERE NAME = '최길동' SQL문과 WHERE NAME = '홍길동'인 SQL문)는 변수를 바인드변수[?]로 치환하여 둘이 같은 SQL문으로 인식할 수 있도록 함.
- 공유가능 SQL
- 라이브러리 캐시에서 SQL을 찾기 위해 사용하는 키 값은 'SQL문 그 자체'이므로 의미적으로 같은 SQL이라고 해도 실행할 때는 각각 최적화를 진행하고 라이브러리 캐시에 별도 공간을 사용한다.
- 같은 의미 있는 SQL이 실행할 때 각각의 쿼리에 대한 SQL의 파싱은 CPU 사용률을 급격하게 올리고 캐시공간을 부족하게 만드는 원인.
- 의미는 같지만 같은 프로시저를 여러 생성을 막기 위해 프로시저 하나를 공유하며 재사용을 하는 것은 효율성을 높게 함.
댓글