1. SQL 처리과정
SQL 파싱
라이브러리 캐시
: 시스템 공유 메모리에서 SQL과 실행계획이 캐싱되는 영역
SQL 파싱 과정
1) 파싱 트리 생성 : SQL문을 이루는 개별 구성 요소를 분석해서 파싱 트리 생성
2) Syntax 체크 : 문법적 오류가 없는지 확인. 예를 들어, 사용할 수 없는 키워드를 사용했거나 순서가 바르지 않거나 누락된 키워드가 있는지 확인
3) Semantic 체크 : 의미상 오류가 없는지 확인. 예를 들어, 존재하지 않는 테이블 또는 컬럼을 사용했는지, 사용한 오브젝트에 대한 권한이 있는지 확인
SQL 최적화
SQL 옵티마이저는 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성해서 비교한 후 가장 효율적인 하나를 선택한다.
(SQL 옵티마이저 : 사용자가 요청한 SQL을 가장 빠르고 효율적으로 수행할 최적(최저비용)의 처리경로를 선택해주는 DBMS의 핵심 엔진)
로우 소스 생성
SQL 옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅하는 단계다.
2. 캐싱된 SQL 공유
SQL 수행 절차
- 문법적 오류와 의미상 오류가 없는지 검사한다. (SQL 파싱)
- 해시 함수로부터 반환된 해시 값으로 라이브러리 캐시 내 해시버킷을 찾아간다.
- 찾아간 해시 버킷에 체인으로 연결된 엔트리를 차례로 스캔하면서 같은 SQL 문장을 찾는다.
- SQL 문장을 찾으면 함께 저장된 실행계획을 가지고 바로 실행한다.
- 찾아간 해시버킷에서 SQL 문장을 찾지 못하면 최적화를 수행한다.
- 최적화를 거친 SQL과 실행계획을 방금 탐색한 해시버킷 체인에 연결한다.
- 방금 최적화한 실행계획을 가지고 실행한다.
캐시에서 SQL을 찾기 위해 사용되는 키 값이 SQL 문장 그 자체다. 즉, SQL 문장 중간중간에 작은 공백문자 하나만 추가되더라도 DBMS는 서로 다른 SQL 문장으로 인식하기 때문에 캐싱된 버전을 사용하지 못하게 된다.
실행 계획을 공유하지 못하는 경우
- 공백문자 또는 줄바꿈
- 대소문자 구분
- 주석
- 테이블 Owner 명시
- 옵티마이저 힌트 사용
- 조건절 비교 값 (사용자가 입력한 값을 조건적에 문자열로 붙여가며 매번 다른 SQL로 실행하는 경우 라이브러리 캐시 효율과 직접 관련이 있다.)
3. 바인드 변수 사용하기
프로시저를 만드는 역할은 옵티마이저가 하는데, 로그인처럼 매번 같은 쿼리에 아이디나 비밀번호에 해당하는 부분의 문자열만 다르게 하고 나머지는 같게 SQL을 작성하게 된다면 내부 처리 루틴이 같은 프로시저가 여러 개 생성될 것이다. 모든 프로시저의 처리 루틴이 같다면 특정 값을 파라미터로 받아 하나의 프로시저로 처리하도록 하는 것이 좋다.
(파라미터 Driven 방식으로 SQL을 작성하는 방법 = 바인드 변수)
바인드 변수를 사용하면 하나의 프로시저를 공유하면서 반복 재사용할 수 있게 되고 SQL과 실행계획을 여러 개 캐싱하지 않고 하나를 반복 재사용하므로 파싱 소요시간과 메모리 사용량을 줄여줄 수 있다.
바인드 변수를 사용하지 않아도 되는 경우
- 배치 프로그램이나 DW, OLAP 등 정보계 시스템에서 사용되는 Long Running 쿼리 (파싱 소요 시간이 쿼리 총 소요 시간에서 차지하는 비중이 매우 낮고, 수행빈도도 낮아 하드파싱에 의한 라이브러리 캐시 부하를 유발할 가능성이 낮음. 오히려 바인드 변수 대신 상수 조건절을 사용하여 옵티마이저가 칼럼 히스토그램을 활용할 수 있도록 하는 것이 유리함)
- 조건절 컬럼의 값 종류가 소수일 때
- 값 분포가 균일하지 않아 옵티마이저가 칼럼 히스토그램 정보를 활용하도록 유도하고자 할 때
주의사항
변수를 바인딩하는 시점은 최적화 이후다. 즉, 나중에 반복 수행될 때 어떤 값이 입력될지 알 수 없기 때문에 옵티마이저는 조건절 컬럼의 데이터 분포가 균일하다는 가정을 세우고 최적화를 수행한다.
-> 컬럼 분포가 균일할 때는 바인드 변수를 사용하고 그렇지 않으면 상수를 사용하는 것이 좋다.
(+ 선택도가 높은 값이 입력될 때는 Full Table Scan이 유리하고, 강원도나 제주도처럼 선택도가 낮은 값이 입력될 때는 인덱스를 경유해 테이블을 액세스하는 것이 유리하다.)
4. Static SQL과 Dynamic SQL
Static SQL
String형 변수에 담지 않고 코드 사이에 직접 기술한 SQL문
런타임 시에 절대 변하지 않으므로 PreCompile 단계에서 구문 분석, 유효 오브젝트 여부, 오브젝트 액세스 권한 등을 체크하는 것이 가능하다.
Dynamic SQL
String형 변수에 담아서 기술하는 SQL문
조건에 따라 SQL문을 동적으로 바꿀 수 있고, 런타임 시에 사용자로부터 SQL문의 일부 또는 전부를 입력 받아서 실행할 수도 있다.
PreCompile 시 Syntax, Semantics 체크가 불가능하므로 Dynamic SQL에 대해서는 PreCompiler는 내용을 확인하지 않고 그대로 DBMS에 전달한다.
(참고로 Java에서는 Static SQL을 지원하지 않는다. 생각해보니 SQL문을 무조건 String형으로 전달해야 한다.)
Static SQL을 사용하든 Dynamic SQL을 사용하든 옵티마이저는 SQL 문장 자체만 인식할 뿐이므로 성능에도 영향을 주지 않기 때문에 라이브러리 캐시 효율을 논할 때는 바인드 변수 사용 여부에 초점을 맞춰야 한다.
5. 애플리케이션 커서 캐싱
애플리케이션 커서 캐싱
: SQL 문장의 문법적, 의미적 오류가 없는지 확인하고, 해시함수로부터 반환된 캐시 값을 이용해 캐시에서 실행계획을 찾고, 수행에 필요한 메모리공간을 할당하는 등의 작업을 생략하고 빠르게 SQL을 수행하는 방법.
일반적인 방식으로 같은 SQL을 반복 수행할 때는 Parse Call 횟수가 Execute Call 횟수와 같게 나타나지만, 위와 같은 방식의 경우 Parse Call은 한 번만 발생하고 나머지 Execute Call에 대해서는 Parse Call이 발생하지 않는다.
Java에서 이 기능을 구현하려면 ((OracleConnection)conn).setImplicitCachingEnabled(true);와 같이 묵시적 캐싱 기능을 활성화하거나 Statement를 닫지 않고 재사용하면 된다.
Dynamic SQL을 사용하거나 Cursor variable을 사용할 때는 커서를 자동으로 캐싱하는 효과가 사라진다.
