01 SQL 과 옵티마이저
DBMS 에 명령을 날릴 때 SQL 이라고 하는 구조화된 질의언어를 통해 원하는 결과집합을 요구할 뿐
그 결과집합을 얻기 위한 처리절차를 개발자가 직접 기술하지는 않기 때문에 4세대 언어라고 부른다.
SQL 옵티마이저는 최소비용, 최적의 경로를 선택해서 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 프로시저를
자동으로 생성해주는 DBMS 의 핵심기능이다.
* 옵티마이저의 최적화 수행단계
1. 사용자가 던진 쿼리수행을 위해, 후보군이 될만한 실행계획들을 찾아낸다.
2. 데이터 딕셔너리에 미리 수집해 놓은 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.
3. 각 실행계획의 비용을 비교해서 최소비용을 갖는 하나를 선택한다.
SQL 을 파싱하고, 필요하면 최적화를 수행하며, 커서를 열어 SQL 을 실행하면서 블록을 읽고,
읽은 데이터를 정렬해서 클라이언트가 요청한 결과집합을 만들어
네트워크를 통해 전송하는 일련의 작업들을 모두 서버 프로세스가 처리해준다.
02 SQL 처리 과정
Parser parsed Optimizer Execution Row-Source
SQL Plan Generator Row-Source SQL Engine
SQL Parsing → Optimization → Row-Source → Execution
1. Query Transformer Generation
2. Estimator
3. Plan Generator
SQL 커서를 메모리에서 찾아 곧바로 실행단계로 넘어가는 것을 Soft Parsing
찾는데 실패해 최적화 및 Row-source 생성 단계를 거치는 것을 Hard Parsing
Misses in library cache during parse: 1 <- Hard Parsing
(1) SQL 파싱
사용자가 던진 SQL을 가장 먼저 받아서 처리하는 엔진이 SQL 파서
문법검사 -> 의미검사 ->
해싱 알고리즘을 이용해 SQL 커서가 Shared Pool 에 캐싱돼 있는지 확인
(2) SQL 최적화
가장 효율적인 실행계획을 선택해주는 DBMS의 핵심 엔진
옵티마이저가 최적화를 수행할 때 세부적으로 아래 3개의 서브 엔진 사용
- Query Transformer: 사용자가 던진 SQL 을 최적화되기 쉬운 형태로 변환 시도
- Plan Generator: 하나의 쿼리를 수행하는데 있어 후보군이 될만한 실행 계획 생성
- Estimator: 쿼리 오퍼레이션 각 단계의 선택도, 카디널리티, 비용을 계산하고,
궁극적으로 실행계획 전체에 대한 총 비용 계산
(3) Row-Source Generation
옵티마이저 최적화 과정을 거쳐 실행 계획이 만들어지면,
실행 가능한 코드 또는 프로시저 형태로 포맷팅하는 작업을 담당
03 라이브러리 캐시 구조
라이브러리 캐시는 Shared Pool 내에 위치하며, SQL 공유 커서 및 데이터베이스오브젝트 에 대한 정보를 관리한다.
여기 저장되는 정보의 단위를 LCO(Library Cache Object) 라 부른다.
라이브러리 캐시에 캐싱되는 정보를
1. 생성 후 Drop 하기 전까지 데이터베이스에 영구적으로 보관되는 오브젝트 정보
(테이블, 인덱스, 클러스터 ,뷰, 트리커 패키지, 사용자 정의 함수, 프로시저 등)
2. 실행시점에 생성돼서 인스턴스가 떠있는 동안에만 존재하는 일시적인 오브젝트 정보
(커서와 Anonymous PL/SQL 문이 대표적)
Shared Pool도 DB버퍼 캐시처럼 LRU 알고리즘에 의해 관리되며,
재사용 빈도가 낮은 SQL은 캐시에서 밀어냄으로써 새로운 SQL을 캐싱할 수 있도록 공간을 확보한다.
shared pool 래치: Shared Pool 에서 특정 오브젝트 정보 또는 SQL 커서를 위한 Free Chunk를 할당받으려 할 때 필요한 래치
library cache 래치: 라이브러리 캐시 체인을 탐색하고 변경하려 할 때 필요한 래치
라이브러리 캐시 최적화를 위한 데이터베이스 관리자 측변에서의 튜닝 기법
1. 커서를 공유할 수 있는 형태로 SQL 작성
2. 세션 커서 캐싱 기능을 이용해 라이브러리 캐시에서 SQL 찾는 비용을 줄인다.
3. 애플리케이션 커서 캐싱을 이용해 Parse Call 발생량을 줄인다.
04 커서 공유
(1) 커서란?
공유커서 (shared cursor): 라이브러리 캐시에 공유돼 있는 Shared SQL Area
세션커서 (session cursor): Private SQL Area 에 저장된 커서
애플리케이션 커서 (application cursor): 세션 커서를 가리키는 핸들
(2) 커서 공유
여기서 말하는 커서는 라이브러리 캐시의 공유 커서를 말한다.
세션커서, 애플리케이션 커서는 다른 프로세스와 공유할 수 없다.
커서가 공유되려면 커서를 식별하는 키 값이 같아야 하는데,
라이브러리 캐시에서 커서를 식별하기 위해 사용되는 키 값은 'SQL 문장 그 자체'다.
(3) Child 커서를 공유하지 못하는 경우
SQL 문장이 100% 동일한대도 SQL 커서를 공유하지 못하고 별도의 SQL 커서를 생성해야 할 때
오라클은 별도의 Child 커서를 생성한다.
SQL 마다 하나의 Parent 커서를 가지며, Child 커서는 여러 개일 수 있다.
이유는
1. SQL 에서 참조하는 오브젝트명이 같지만 SQL 실행한 사용자에 따라 다른 오브젝트를 가리킬 때
2. 참조 오브젝트가 변경돼 커서가 무효화되면 이후 그 커서를 처음 사용하려는 세션에 의해
다시 하드파싱돼야 하는데, 특정 세션이 아직 기존 커서를 사용 중 (Pin) 일 때
3. 옵티마이저 모드를 비롯해 옵티마이저 관련 파라미터가 다를 때
4. 입력된 바인드 값의 길이가 크게 다를 때
5. NLS 파라미터를 다르게 설정했을 때
6. SQL 트레이스를 활성화했을 때
(4) Parent 커서를 공유하지 못하는 경우
1. 공백문자 또는 줄바꿈
2. 대소문자 구분
3. 테이블 Owner 명시
4. 주석 (Comment)
5. 옵티마이져 힌트 사용
6. 조건절 비교 값
개발 초기에 SQL작성 표준을 정해 준수하도록 해야 한다.
하지만 1~4번처럼 SQL을 작성한다고 해서 라이브러리 캐시 효율이 우려할만큼 크게 나빠지지 않는다.
5번은 의도적으로 실행계획을 달리 가져가려는 것이고,
OLTP 성 업무를 처리하는 애플리케이션에서 6번과 같은 패턴으로 SQL 을 개발한다면 결코 좋은 성능을 보장받을 수 없다.
개별 쿼리 성능으로 보면 잘 느끼지 못하지만
동시 트랜잭션이 몰리는 peak 시간대에 시스템을 장애 상황으로 몰고 가는 주범이다.
05 바인드 변수의 중요성
바인드 변수 사용에 따른 효과는 아주 분명하다.
커서를 많이 생성하지 않고 하나를 반복 재사용하므로 메모리 사용량과 파싱 소요시간을 줄여준다.
궁극적으로, 시스템 전반의 메모리와 CPU 사용률을 낮춰 데이터베이스 성능과 확장성을 높이는데 기여하고,
특히 동시 사용자 접속이 많을 때는 그 영향력이 절대적이다.
06 바인드 변수의 부작용과 해법
변수를 바인딩하는 시점이 (최적화 시점보다 나중인) 실행시점이라는 사실을 아는 것이 중요하다.
즉, SQL 을 최적화하는 시점에 조건절 컬럼의 데이터 분포도를 활용하지 못하는 문제점을 갖는다.
(바인드 변수를 사용하면 통계정보를 사용하지 못한다고 흔히 말하는데, 정확히 표현하면
컬럼 히스토그램 정보를 사용하지 못하는 것이다. 히스토그램을 제외한 다른 통계정보들은 충분히 활용한다.)
카디널리티 = 선택도 X 전체 레코드 수
바인드 변수를 사용할 때는 정확한 히스토그램에 근거하지 않고
카디널리티를 구하는 정해진 계산식에 기초해 비용을 계산하므로
최적이 아닌 실행계획을 수립할 가능성이 높다.
(1) 바인드 변수 Peeking
SQL 이 첫번째 수행되면서 하드파싱될때 함께 딸려 온 바인드 변수 값을 살짝 훔쳐보고,
그 값에 대한 컬럼 분포를 이용해 실행계획을 결정하는 것이다.
*SQLServer 에서는 Parameter Sniffing 이라고 부른다.
10g부터는 dbms_stats의 기본 설정이, 히스토그램을 생성할지 여부를 오라클이 판단하는 쪽으로 바뀌었다.
안타까운 일이지만 대부분 아래처럼 비활성화해서 운영 중이다.
alter system set "_optim_peek_user_binds" = FALSE;
(2) 적응적 커서 공유
입력된 바인드 변수 값의 분포에 따라 다른 실행계획이 사용되도록 하는 것
v$sql_cs_statistics
v$sql_cs_histogram
v$sql_cs_selectivity
이 기능이 작동하려면 기본적으로 조건절 컬럼에 히스토그램이 생성돼 있어야 한다.
옵티마이저가 바인드 변수 값에 따라 실행계획을 달리 가져갈 필요가 있다고 판단되는 SQL 커서에 대해서 이 기능이 활성화 된다.
그런 커서를 Bind Sensitive 커서라고 부르며, v$sql 의 is_bind_sensitive 컬럼이 'Y'로 표시돼있다.
(3) 입력 값에 따라 SQL 분리
인덱스 액세스 경로로서 중요하고 조건절 컬럼의 데이터 분포가 균일하지 않은 상황에서
바인드 변수 사용에 따른 부작용을 피하려면
바인딩 되는 값에 따라 실행계획을 분리 하는 방안을 고려해야 한다.
OLTP 시스템에서는 union all 보다 애플리케이션 단에서 조건에 따라 (IF ELSE) SQL을 분기하는 것이 바람직하다.
(4) 예외적으로, Literal 상수값 사용
조건절 컬럼 값 종류가 소수일 때는 바인드 변수 보다 오히려 Literal 상수를 사용하는게 나은 선택일 수 있다.
종류가 적다면 하드 파싱 부하가 미미할테고, 옵티마이저가 더 나은 선택을 할 가능성이 커지기 때문이다.
DW, OLAP 등 정보계 시스템에서는 일자 조건이 빠지지 않고, 대부분 범위 검색 조건이므로,
배치 프로그램이나 대부분 SQL 이 Long Running 쿼리이므로 파싱 소요 시간이 쿼리 총 소요시간에서 차지하는 비중이 낮고, 사용 빈도도 낮다.
OLTP환경이라면 특별한 몇몇 경우를 제외하고는 반드시 바인드 변수를 사용해야만 한다.
07 세션 커서 캐싱
Shared Pool 에 위치한 공유커서를 실행하려고 PGA 로 인스턴트화 한 것이 세션커서
자주 수행하는 SQL 에 대한 세션 커서를 세션 커서 캐시에 저장할 수 있는 기능을 세션 커서 캐싱이라 한다.
이 기능을 활성화하면, 커서를 닫는 순간 해당 커서의 Parse Call 횟수를 확인해보고 그 값이 3 이상이면 세션 커서를 세션 커서 캐시로 옮긴다.
v$sql
users_opening : 공유 커서를 참조하고 있는 세션 커서의 수
users_executing : 해당 SQL을 현재 실행 중인, 즉 커서가 열려있는 세션 커서의 수
톰 카이트는 라이브러리 캐시에 공유된 커서를 반복 재사용하는 소프트 파스 보다 더 소프트 하다고 표현해주고 있다.
세션 커서 캐싱 기능은 Parse Call을 대체하기보다 Parse Call 부하를 감소시키는 기능으로 이해해야 한다.
08 애플리케이션 커서 캐싱
세션 커서 캐시에 있는 SQL 을 수행하더라도 공유 커서 힙을 Pin 하고
실행에 필요한 메모리 공간을 PGA 에 할당하는 작업마저 생략하고 SQL을 수행하는 방법
Java에서 이를 구현하려면 묵시적 캐싱 (Implicit Caching) 옵션을 사용하거나 Statement 를 닫지 않고 재사용하면 된다.
(1) 바인드 변수를 사용하지 않을 때
(2) 바인드 변수를 사용하지만, 커서를 캐싱하지 않을 때
(3) 커서를 닫지 않고 재사용할 때
(4) 묵시적 캐싱 기능을 사용할 때
5141
2860
1375
1375
PL/SQL 에서는 위와 같은 옵션을 별도로 적용하지 않더라도 자동적으로 커서를 캐싱한다.
단, Static SQL 을 사용할 때만 그렇다.
Dynamic SQL을 사용하거나 Cursor Variable(=Ref Cursor)을 사용할 때는 커서를 자동으로 캐싱하는 효과가 사라진다는 사실을 명심하기 바란다.
09 Static vs. Dynamic SQL
(1) Static SQL
String 형 변수에 담지 않고 코드 사이에 직접 기술한 SQL 문을 말한다. Embedded SQL 이라고도 한다.
(2) Dynamic SQL
String 형 변수에 담아서 기술하는 SQL 문을 말한다. String 변수를 사용하므로 조건에 따라 SQL 문을 동적으로 바꿀 수 있고,
또는 런타임 시에 사용자로부터 SQL 문의 일부 또는 전부를 입력 받아서 실행할 수도 있다.
(3) 일반 프로그램 언어에서 SQL 작성법
Java, Delphi, Visual Basic 등 개발 언어 Dynamic SQL
Toad, Orange, SQL*Plus 와 같은 Ad-hoc 쿼리 툴에서도 Dynamic SQL
Static SQL 을 지원하는 개발 언어로는 PowerBuilder, PL/SQL, Pro*C, SQLJ 정도가 있다.
(4) 문제의 본질은 바인드 변수 사용 여부
애플리케이션 커서 캐싱 기능을 사용하지 않는다면 Dynamic, Static 구분은 라이브러리 캐시 효율과도 전혀 무고나하다.
라이브러리 캐시 효율을 논할 때 초점은 바인드 변수 사용 여부에 맞춰줘야 한다.
Dynamic SQL 을 사용해 문제가 되는 것이 아니라 바인드 변수를 사용하지 않았을 때 문제가 되는 것이다.
바인드 변수 사용 여부로 Static 과 Dynamic 을 구분하는 것은 잘못된 것이다.
10 Dynamic SQL 사용 기준
(1) Dynamic SQL 사용에 관한 기본 원칙
1. Static SQL을 지원하는 개발환경이라면 Static SQL로 작성하는 것을 원칙으로 한다.
2. PreCompile 과정에서 컴파일 에러가 나는 구문을 사용할 때, 예를 들어 Pro*C에서 스칼라 서브쿼리, 분석함수, ANSI 조인 등,
상황과 조건에 따라 생성될 수 있는 SQL개수가 많아 개발 생산성이 저하되고 유지보수 비용이 매우 커질 때는 Dynamic SQL 사용
3. 2번 경우라도 조건절에는 반드시 바인드 변수를 사용하는 것을 원칙으로 한다. 특히 사용빈도가 높고 조건절 컬럼의 값 종류가 많을 때 반드시 준수
4. 3번 바인드 변수 사용 원칙을 준수하되 아래 경우는 예외적으로 인정
배치프로그램이나 DW, Olap 등 정보계 시스템에서 사용되는 Long Running 쿼리
OLTP성 애플리케이션이라도 사용빈도가 매우 낮아 하드파싱에 의한 라이브러리 캐시 부하를 유발할 가능성이 없을 때
조건절 컬럼의 값 종류가 소수일 때
(2) 기본원칙이 잘 지켜지지 않는 첫번째 이유, 선택적 검색 조건
검색 조건이 다양해 사용자 선택에 따라 조건절이 동적으로 바뀌는 경우
개발 기간 내내 SQL마다 최적의 인덱스 구성 전략을 고민하면서 개발한다는 것이 쉬운 일은 아니다.
Union all 로 분기하는 기법은, 튜닝 차원에서 접근하고 필요에 따라 적절히 활용하는 것이 타당하다.
(3) 선택적 검색 조건에 대한 현실적인 대안
Static SQL 사용을 원칙으로 하되 사용자 입력 조건에 따라 생성될 수 있는 SQL 최대 개수가 너무 많을 때는 Dynamic SQL 사용을 허용하는 것
바인드 변수 사용 원칙만큼은 준수하도록 해야 한다.
(4) 선택적 검색 조건에 사용할 수 있는 기법 성능 비교
A. OR 조건을 사용하는 경우
B. LIKE 연산자를 사용하는 경우
C. NVL 함수를 사용하는 경우
D. DECODE 함수를 사용하는 경우
E. UNION ALL 을 사용하는 경우
정리
1. not null 컬럼일 때는 nvl, decode 사용하는 것이 편하다.
2. null 값을 허용하고 인덱스 액세스 조건으로 의미있는 컬럼이라면
union all 을 사용해 명시적으로 분기해야 한다.
3. 인덱스 액세스 조건으로 참여하지 않는 경우, 즉 인덱스 필터 또는 테이블 필터 조건으로만 사용되는 컬럼이라면
(:c is null or col = :c) 또는 (c like :c|| '%') 어떤 방식을 사용해도 무방하다.
11 Static SQL 구현을 위한 기법들
(1) IN-List 항목이 가변적이지만 최대 경우 수가 적은 경우
in (:a, :b, :C)
in (decode(:a, 'all', '01', :b), decode(:a, 'all', '02', :c), decode(:a, 'all', '03', :d))
(2) IN-List 항목이 가변적이고 최대 경우 수가 아주 많은 경우
컬럼과 변수 위치를 바꾸기
:inlist := '01,03,08,13,17,24,33,46,48,53'
where INSTR(:inlist, 분류코드) > 0
(3) 체크 조건 적용이 가변적이 경우
(4) select-list 가 동적으로 바뀌는 경우
decode, case로 바꾸기
(5) 연산자가 바뀌는 경우
Between 시작값, 종료값 바인딩