1. SQL(Structured Query Language)
SQL은 기본적으로 구조적이고 집합적이고 선언적인 질의 언어
그러나 원하는 결과집합을 만드는 과적은 절차적임 → 프로시저가 필요함
→ 그런 프로시저를 만들어 내는 DBMS 내부 엔진이 SQL 옵티마이저(프로그래밍을 대신함)
SQL 최적화란 DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한상태로 만드는 전 과정
2. SQL 최적화
SQL을 실행하기 전 최적화 과정
① SQL 파싱
사용자로부터 SQL을 전달받으면 가장 먼저 SQL 파서가 파싱을 진행
- 파싱 트리 생성 : SQL 문을 이루는 개별 구성요소를 분석해서 파싱 트리 생성
- Syntax 체크 : 문법적 오류가 없는지 확인(사용 불가한 키워드, 순서가 바르지 않거나 누락된 키워드 확인 등)
- Semantic 체크 : 의미상 오류가 없는지 확인(존재하지 않는 테이블 또는 컬럼 사용, 오브젝트 권한이 있는지 확인 등)
② SQL 최적화
옵티마이저가 실행(DB 성능을 결정하는 가장 핵심적인 엔진)
→ SQL 옵티마이저는 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성해서 비교한 후 가장 효율적인 선택
③ 로우 소스 생성
로우 소스 생성기가 실행
SQL 옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅 하는 단계
3. SQL 옵티마이저
SQL 옵티마이저는 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로를 선택해 주는 DBMS 핵심 엔진
옵티마이저의 최적화 단계
- 사용자로부터 전달받은 쿼리를 수행하는 데 후보군이 될만한 실행계획들을 찾아냄
- 데이터 딕셔너리에 미리 수집해 둔 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정
- 최저 비용을 나태내는 실행계획을 선택
4. 실행계획과 비용
DBMS 내 SQL 실행경로 미리보기 기능 = 실행계획
실행계획이란 SQL 옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있게 트리 구조로 표현한 것
미리보기 기능을 통해 작성한 SQL이 테이블을 스캔하는지 인덱스를 스캔하는지, 인덱스를 스캔한다면 어떤 인덱스인지 확인할 수 있고,
예상과 다른 방식으로 처리된다면 실행 경로 변경 가능
옵티마이저가 특정 실행계획을 선택하는 근거는?
비용(Cost)이 가장 작은 실행계획 선택(비용은 쿼리를 수행하는 동안 발생할 것으로 예상되는 I/O 횟수 또는 예상 소요시간을 표현한 값)
그러나 SQL 실행계획에 표시되는 Cost는 예상치에 불과(실행경로를 선택하기 위해 옵티마이저가 여러 통계정보를 활용해 계산한 값)
실측치가 아니므로 실제 수행할 때 발생하는 I/O 또는 시간과 많은 차이가 남
5. 옵티마이저 힌트
옵티마이저 힌트를 이용해 데이터 액세스 경로 변경 가능
어떤 방식이 옳은지는 애플리케이션 환경에 따라 다름
→ 통계정보나 실행 환경 변화로 인해 옵티마이저가 가끔 실수하더라도 별문제가 없는 시스템이 있는가 하면,
옵티마이저의 작은 실수가 기업에 큰 손실을 끼치는 시스템도 존재
※ MSSQL 힌트 사용법 참고 : https://devjino.tistory.com/95
6. 소프트 파싱 vs 하드 파싱
SGA(System Global Area) : 서버 프로세스와 백그라운드 프로세스가 공통 액세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간
라이브러리 캐시(Library Cache) : SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간(라이브러리 캐시는 SGA 구성요소)
사용자가 SQL문을 전달하면 DBMS는 SQL을 파싱한 후 해당 SQL이 라이브러리 캐시에 존재하는지부터 확인
소프트 파싱 : SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것
하드 파싱 : 캐시에서 찾는 데 실패해 최적화 및 로우 소스 생성 단계까지 모두 거치는 것
SQL 최적화 과정이 하드한 이유
옵티마이저가 순식간에 엄청나게 많은 연산을 하는 과정에서(SQL을 최적화할 때) 사용하는 정보
- 테이블, 컬럼, 인덱스 구조에 관한 기본 정보
- 오브젝트 통계 : 테이블 통계, 인덱스 통계, 컬럼 통계
- 시스템 통계 : CPU 속도, Single Block I/O 속도, Multiblock I/O 속도 등
- 옵티마이저 관련 파라미터
데이터베이스에서 이루어지는 처리 과정은 대부분 I/O 작업에 집중되는 반면, 하드 파싱은 CPU를 많이 소비하는 작업
이렇게 어려운 작업을 거쳐 생성한 내부 프로시저를 한번만 사용하고 버린다면 매우 비효율적 = 라이브러리 캐시가 필요한 이유
7. 바인드 변수의 중요성
1) 이름 없는 SQL 문제
- 사용자 정의 함수/프로시저, 트리거, 패키지 등은 생성할 때부터 이름을 가짐(컴파일한 상태로 딕셔너리에 저장되며, 사용자가 삭제하지 않는 한 영구 보관 + 실행할 때 라이브러리 캐시에 적재함)
- SQL은 이름이 따로 없음 → 전체 SQL 텍스트가 이름 역할(딕셔너리에 저장되지 않음 + 처음 실행할 때 최적화 과정을 거쳐 동적으로 생성한 내부 프로시저를 라이브러리 캐시에 적재함(캐시 공간이 부족하면 버려졌다가 다음에 다시 실행 시 똑같은 최적화 과정 거침))
- SQL도 사용자 정의 함수/프로시저처럼 영구 저장 불가한 이유
DBMS에서 수행되는 SQL이 모두 완성된 SQL은 아니며, 특히 개발 과정에서는 수시로 변경이 일어나고 일회성 SQL도 많음
일회성 또는 무효화된 SQL까지 모두 저정하려면 많은 공간이 필요하고, 그만큼 SQL을 찾는 속도도 느려짐
2) 공유 가능 SQL
String SQLStmt = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = '" + login_id + "'";대량의 로그인 접속 인원이 발생할 경우 각 실행별 SQL 하드파싱이 일어남 → CPU 사용률 증가
create procedure LOGIN (login_id in varchar2) { ... }
--사용 예시
String SQLStmt = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = ?";로그인ID를 파라미터로 받는 프로시저 하나를 공유하면서 재사용 → SQL에 대한 하드파싱은 최초 한번만 일어남