-
SQL 공유 및 재사용데이터베이스 2021. 6. 22. 22:08
소프트파싱/하드파싱
바인드 변수 사용
애플리케이션 커서 캐싱
Static SQL 과 Dynamic SQL
1. 소프트 파싱 vs. 하드파싱
시스템 공유 메모리에서 SQL 과 실행계획이 캐싱되는 영역을 오라클에선 라이브러리 캐시, SQL 서버에서는 프로시져 캐시라도 부른다.
사용자가 SQL 을 실행하면 제일 먼저 SQL 파서가 SQL 문장에 문법적 오류가 없는지를 검사한다.(Syntax검사)
문법적으로 오류가 없으면 의미상 오류가 없는지는 검사한다.(Semantic 검사)
예를 들어 존재하지 않거나 권한이 없는 객체를 사용했는지, 또는 존재하지 않는 컬럼을 사용했는지 등을 검사한다.
이런 검사를 마치면 사용자가 발행한 SQL 과 그 실행계획이 라이브러리 캐시에 캐싱됐는지를 확인한다. 만약 캐싱돼 있다면, 무거운 최적화 과정을 거치지 않고 곧바로 실행할 수 있다.
* 소프트 파싱 : SQL과 실행계획을 캐시에서 찾아 곧바로 실행단계로 넘어가는 경우를 말함
* 하드 파싱: SQL과 실행계획을 캐시에서 찾지 못해 최적화 과정을 거치고 나서 실행단계로 넘어가는 경우를 말함
라이브러리 캐시는 해시구조로 관리되기 때문에 SQL 마다 해시 값에 따라 여러 해시버킷으로 나누어 저장된다.
SQL 을 찾을 때는 SQL 문장을 해시 함수에 입력해서 반환된 해시 값을 이용해 해당 해시버킷을 탐색하면 된다.
가. SQL 공유 및 재사용의 필요성
앞서 옵티마이저의 최적화 과정을 거치는 경우 하드 파싱이라고 표현했는데, 최적화 과정은 그만큼 무거운 작업을 수반한다. 예를 들어 5개의 테이블을 조인하려면 조인 순서만 고려해도 5개의 실행계획을 평가해야한다. 120가지 실행계획에 포함된 각 조인 단계별로 NL 조인, 소트 머지 조인, 해시 조인 등 다양한 조인 방식까지 고려하면 경우의 수는 기하급수적으로 늘어난다. 여기에 각 테이블을 Full Scan 할지 인덱스를 사용할지, 인덱스를 사용한다면 어떤 인덱스를 어떤 방식으로 스캔할지까지 모두 고려해야 하므로 여간 무거운 작업이 아니다.
옵티마이저가 SQL 최적화 과정에 사용하는 정보는 다음과 같다.
테이블, 컬럼, 인덱스 구조에 관한 기본 정보
오브젝트 통계 : 테이블 통계 , 인덱스 통계, (히스토그램을 포함한) 컬럼 통계
시스템 통계 : CPU 속도, SIngle Block I/O 속도, Multiblock I/O 속도 등
옵티마이저 관련 파라미터
하나의 쿼리를 수행하는 데 있어 후보군이 될만한 무수히 많은 실행경로를 도출하고, 짧은 순간에 딕셔너리와 통계정보를 읽어 각각에 대한 효율성을 판단하는 과정은 결코 가벼울 수 없다.
이렇게 어려운 작업을 거쳐 생성한 내부 프로시저를 한 번만 사용하고 버린다면 이만저만한 비효율이 아니다.
파싱과 최적화 과정을 거친 SQL 과 실행계획을 여러 사용자가 공유하면서 재사용할 수 있도록 공유 메모리에 캐싱해 두는 이유가 여기에 있다.
나. 실행계획 공유 조건
SQL 수행 절차를 정리하면 다음과 같다.
1. 문법적 오류와 의미상 오류가 있는지 검사
2. 해시함수로부터 반환된 해시값으로 라이브러리 캐시 내 해시버킷을 찾는다.
3. 찾아간 해시버킷에 체인으로 연결된 엔트리를 차례로 스캔하면서 같은 SQL 문장을 찾는다.
4. SQL 문장을 찾으면 함께 저장된 실행계획을 가지고 바로 실행한다.
5. 찾아간 해시버킷에서 SQL 문장을 찾지 못하면 최적화를 수행한다.
6. 최적화를 거친 SQL 과 실행계획을 방금 탐색한 해시버킷 체인에 연결한다.
7. 방금 최적화한 실행계획을 가지고 실행한다.
위 과정을 통해 캐시에서 SQL 을 찾기 위한 키 값이 SQL 문장 그 자체라는 사실을 알 수 있다.
이것은 SQL 파싱 부하 해소 원리를 이해하는데 있어 매우 중요한 의미를 갖는다.
즉 SQL 문장 중간에 작은 공백문자 하나가 추가되더라도 DBMS 는 서로 다른 SQL 문장으로 인식하기 때문에 캐싱된 버전을 사용하지 못하게 된다.
다. 실행계획을 공유하지 못하는 경우
예를 들어 아래 6가지 경우에 옵티마이저는 각각 다른 SQL 로 인식해 별도의 실행계획을 수립한다.
1. 공백 문자 혹은 줄바꿈
2. 대소문자 구분
3. 주석
4. 테이블 Owner 명시
5. 옵티마이저 힌트 사용 -- 실행계획을 다르게 할 의도로 힌트를 사용했으므로 논외다.
6. 조건절 비교 값
1~4번처럼 SQL 을 작성했다고 우려할 만큼 라이브러리 캐시 효율이 나빠지지는 않는다. 같은 내용의 쿼리를 여러 개발자가 각각 다르게 작성할 가능성은 매우 낮기 때문이다.
결론적으로 6번이 라이브러리 캐시 효율와 직접 관련이 있는 패턴이다. 사용자의 입력값을 조건절에 붙이며 매번 다른 SQL 을 수행하는 경우다. 이런 패턴의 SQL 을 리터럴 SQL 이라고 부르기로 한다. 사용자가 몰리는 피크 시간대에 이런 리터럴 SQL 이 몰리면 시스템을 장애 상황으로 몰아갈 수 있다.
2. 바인드 변수 사용
가. 바인드 변수의 중요성
동일한 일을 하는 프로시져라면 procedure Login_usrA , procedure Login_usrB,procedure Login_usrC.. 이런식이 아니라
procedure Login(usr_id in varchar2) 와 같이 파라미터를 이용하는 식의 하나의 프로시저만을 생성해야한다.
이런 파라미터 드리븐 방식으로 SQL 을 작성하는 방법이 제공되는데, 이것이 바인드 변수이다.
select * from user where login_id = : login_id바인드 변수를 이용하면 SQL 과 실행계획을 여러개 캐싱하지 않고 하나를 재사용해 파싱 소요시간과 메모리 사용량을 줄여준다. 궁극적으로 시스템 전반의 CPU 와 메모리 사용률을 낮춰 데이터베이스 성능과 확장성을 높이고, 특시 동시 사용자 접속이 많을 때 그 효과가 좋다.
다음과 같은 경우는 바인드 변수를 사용하지 않아도 무방하다.
* 배치 프로그램, DW, OLAP 등 정보계 시스템에서 사용되는 long running 쿼리
- 이 쿼리들은 파싱 소요시간이 쿼리 총 소요시간에 차지하는 비중이 매우 낮고, 수행빈도도 낮아 하드파싱에 의한 라이브러리 캐시 부하를 유발할 가능성이 낮다. 그러므로 상수 바인드 변수 대신 상수 조건절을 사용해 옵티마이저가 칼럼 히스토그램을 활용할 수 있게 하는 것이 유리하다.
*조건절 컬럼의 값 종류가 소수일 때
- 특히 값 분포가 균일하지 않아 옵티마이저가 컬럼 히스토그램 정보를 활용하도록 유도하고자 할 때
무분별하게 리터럴 SQL 위주로 캐발하면 라이브러리 캐시 경합으로 시스템 정상 가동이 어려운 상황에 직면할 수 있다.
이를 대비해 DBMS 는 조건절 비교값이 리터럴 상수일 때 이를 자동으로 변수화해주는 기능을 제공한다.
SQL 서버에서는 단순 매개 변수화(simple parameterization) 이라고 기본적으로 활성화되며,
오라클에서는 cursor_sharing 파라미터를 시스템 또는 세션 레벨에서 FORCE 나 SIMILAR 로 (기본은 EXACT)설정하면 된다.
리터럴 쿼리에 의한 파싱 부하가 극심한 상황에서 이 기능이 시스템 부하를 줄이는데 도움을 주는 것은 사실이지만 부작용도 만만치 않다/ 이 옵션을 적용하는 순간 실행계획이 갑자기 바뀌어 기존에 잘 수행되던 SQL 이 갑자기 느려길 수 있고, 사용자가 의도적으로 사용한 상수도 변수화되며 문제가 되기도 한다.
오라클 응급처방용으로 사용할 수 있지만 절대 영구 적용을 목적으로 해서는 안된다. SQL 서버에서는 기본적으로 활성화되어있지만 가급적 작동하는 경우를 최소화해야한다.
나. 바인드 변수 사용 시 주의사항
사용자가 입력한 값을 변수에 바인딩하는 시점은 최적화 이후이다.
즉 나중에 반복 수행 시 값을 미리 알 수 없기 때문에 옵티마이저는 조건절 컬럼의 데이터 분포가 균일하다는 가정을 세우고 최적화를 수행한다. 컬럼에 대한 히스토그램 정보가 딕셔너리에 있어도 활용하지 못하는 것이다.
컬럼 분포가 균일하지 않으면 실행시점에 바인딩되는 값에 따라 쿼리 성능이 다르게 나타날 수 있다. 이럴 땐 바인드 변수보다 상수 값을 사용하는 것이 더 나을 수도 있다.
다. 바인드 변수 부작용을 극복하기 위한 노력
바인드 변수 사용의 부작용을 극복하려고 오라클 9i 에서 바인드 변수 peeking 기능을 도입했다. SQL 서버에서는 parameter sniffing 이라고 같은 기능을 제공한다.
SQL 이 첫번째 수행될때 바인드 변수의 값을 살짝 훔쳐보고, 그 값에 대한 컬럼 분포를 이용해 실행계획을 결정하는 기능이다.
하지만 처음 실행될 때 값과 전혀 다른 분포를 갖는 값이 나중에 오면 쿼리 성능이 갑자기 느려질 수 있어 위험한 기능이다.
3. 애플리케이션 커서 캐싱
같은 SQL 을 아주 여러 번 반복해서 수행해야 할 때, 첫번째는 하드파싱이 일어나겠지만 이후부터는 라이브러리 캐시에 공유된 버전을 찾아 가볍게 실행할 수 있다. 그렇더라도 SQL 문장의 문법적/의미적 오류가 없는지 확인하고, 해시함수로부터 반환된 해시 값을 이용해 캐시에서 실행계획을 찾고, 수행에 필요한 메모리공간(Persistent Area 와 Runtime Area) 을 할당하는 등의 작업을 매번 반복한느 것은 비효율적이다. 이런 과정을 생략하고 빠르게 SQL 을 수행하는 방법이 있는데 , 이를 '애플리케이션 커서 캐싱'이라고 부르기로 한다.
4. Static SQL 과 Dynamic SQL
가. Static SQL
Static SQL 이란 String 형 변수에 담지 않고 코드 사이에 직접 기술한 SQL 문을 말한다. 다른 말로 Embeded SQL 이라고 한다.
Pro*C, SQLJ 같은 PreCompile 언어로 소스코드를 작성해서 PreCompile 하면 순수 c,c++ 코드가 만들어진다. 이를 다시 c/c++ compiler 로 컴파일해 실행파일이 만들어지면 그것을 실행한다.
PreCompile 가 PreCompile 과정에서 Static(=Embeded) SQL 을 발견하면 이를 SQL 런타임 라이브러리에 포함된 함수를 호출하는 코드로 변환한다. 이 과정에서 결국은 String 형 변수에 담긴다. Static SQL 이든 Dynamic SQL 이든 Precompile 단계를 거치고 나면 String 변수에 담기기는 마찬가지지만 Static SQL 은 런타임 시에 절대 변하지 않으므로 PreComplie 단계에서 구문 분석, 유효 오브젝트 여부, 오브젝트 엑세스 권한 등을 체크하는 것이 가능하다.
나. Dynamic SQL
String 형 변수에 담아서 기술하는 SQL 문을 말한다. String 변수를 사용하므로 조건에 따라 SQL 문을 동적으로 바꿀 수 있고, 또는 런타임 시에 사용자로부터 SQL 문의 일부 또는 전부를 입력 받아서 실행할 수 있다. 따라서 PreComplie 시 Syntax, Semantics 체크가 불가능하므로 Dynamic SQL 에 대하선 PreCompiler는 내용을 확인하지 않고 그대로 DBMS 에 전달한다.
Static SQL 을 지원하는 개발언어는 많지 않으며, PowerBuilder, PL/SQL, Pro*c,SQLJ 정도가 있다. 외에는 SQL 을 모두 String 변수에 담아 사용해, 런타임 시 사용자로부터 받은 SQL 을 그대로 DBMS 에 던진다.
다. 바인드 변수의 중요성 재강조
Static SQL 인지 Dynamic SQL 인지 데이터베이스 입장에선 차이가 없다. 라이브러리캐시 효율을 논할 땐 바인드 변수 사용 여부에 초점을 맞춰야한다.
출처
SQL 전문가 가이드 - 한국데이터산업진흥원
반응형