SQL
DA, SQL, DB보안 등 실무자를 위한 위한 DB기술 바이블!
과거에 파일 시스템이나 dBase Ⅲ+, FoxPro, Clipper 같은 xBase 계열에서 데이터베이스 프로그래밍할 때는 데이터 처리 절차를 프로그래머가 직접 작성해야 했다. 하지만 지금은 구조화된 질의언어(SQL, Structured Query Language)를 통해 사용자가 원하는 결과집합만 정의하지, 그것을 얻는 데 필요한 처리절차를 직접 기술하진 않는다. 우리 대신 프로그래밍 해 주는 엔진이 DBMS에 내장돼 있기 때문이며, SQL 옵티마이저가 바로 그런 역할을 해 준다. 옵티마이저에 의해 생성된 처리절차를 실행계획(Execution Plan)이라고 부르며, 각 DBMS가 제공하는 인터페이스를 통해 아래와 같은 형태로 출력해 볼 수 있다.(실행계획 확인방법을 모른다면 부록 B를 참조하기 바란다.) 위 실행계획이 실제 실행 가능한 형태는 아니므로 코드(또는 프로시저) 형태로 변환하는 과정을 거치고 나서 SQL 엔진에 의해 수행된다. 실행계획이 만들어지기까지의 과정을 좀 더 자세히 들여다보면, SQL 파싱과 최적화 과정으로 나누어 설명할 수 있다. 시스템 공유 메모리에서 SQL과 실행계획이 캐싱되는 영역을 Oracle에선 ‘라이브러리 캐시(Library Cache)’, SQL Server에선 ‘프로시저 캐시(Procedure Cache)’라고 부른다고 1절에서 설명하였는데, 지금부터는 설명의 편의를 위해 ‘라이브러리 캐시’로 부르기로 하겠다. 사용자가 SQL을 실행하면 제일 먼저 SQL 파서(Parser)가 SQL 문장에 문법적 오류가 없는지를 검사(→Syntax 검사)한다. 문법적으로 오류가 없으면 의미상 오류가 없는지를 검사(→Semantic 검사)하는데, 예를 들어 존재하지 않거나 권한이 없는 객체를 사용했는지, 또는 존재하지 않는 칼럼을 사용했는지 등을 검사한다. 이런 검사를 마치면 사용자가 발행한 SQL과 그 실행계획이 라이브러리 캐시에 캐싱됐는지를 확인한다. 만약 캐싱돼 있다면, 무거운 최적화 과정을 거치지 않고 곧바로 실행할 수 있다. 라이브러리 캐시는 해시구조로 관리되기 때문에 SQL마다 해시 값에 따라 여러 해시 버킷으로 나누어 저장된다. SQL을 찾을 때는 SQL 문장을 해시 함수에 입력해서 반환된 해시 값을 이용해 해당 해시버킷을 탐색하면 된다. 옵티마이저에 대해서는 3장에서 자세히 설명하지만, 여기서 간단히 살펴보고자 한다. SQL 최적화를 담당하는 엔진이 옵티마이저(Optimizer)다. 옵티마이저를 한마디로 요약하면, 사용자가 요청한 SQL을 가장 빠르고 효율적으로 수행할 최적(최저비용)의 처리경로를 선택해 주는 DBMS의 핵심엔진이라고 할 수 있다. 앞서 옵티마이저의 최적화 과정을 거치는 경우를 ‘하드(Hard) 파싱’이라고 표현했는데, 최적화 과정은 그만큼 무거운 작업을 수반한다. 예를 들어, 5개의 테이블을 조인하려면 조인 순서만 고려해도 5!(=120)개의 실행계획을 평가해야 한다. 120가지 실행계획에 포함된 각 조인 단계별로 NL JOIN, Sort Merge Join, Hash Join 등 다양한 조인 방식까지 고려하면 경우의 수는 기하급수적으로 늘어난다. 여기에 각 테이블을 Full Scan할지 인덱스를 사용할지, 인덱스를 사용한다면 어떤 인덱스를 어떤 방식으로 스캔할지까지 모두 고려해야 하므로 여간 무거운 작업이 아니다. 이렇게 힘든 과정을 거쳐 최적화된 SQL 실행계획을 한 번만 쓰고 버린다면 이만저만한 낭비가 아니다. 파싱 과정을 거친 SQL과 실행계획을 여러 사용자가 공유하면서 재사용할 수 있도록 공유 메모리에 캐싱해 두는 이유가 여기에 있다. 방금 설명한 SQL 수행 절차를 정리해 보면 다음과 같다. 방금 설명한 SQL 수행 절차에서 중요한 사실 하나를 발견할 수 있다. 하드 파싱을 반복하지 않고 캐싱된 버전을 찾아 재사용하려면 캐시에서 SQL을 먼저 찾아야 하는데, 캐시에서 SQL을 찾기 위해 사용되는 키 값이 “SQL 문장 그 자체”라는 사실이다. SQL 문을 구성하는 전체 문자열이 이름 역할을 한다는 뜻이다. 물론 DBMS나 버전에 따라 별도의 SQL ID를 부여하기도 하지만 이 SQL ID가 SQL 전체 문장과 1:1로 대응되기 때문에 SQL 문장 자체가 식별자로 이용된다는 사실에는 변함이 없다. 이것은 SQL 파싱 부하 해소 원리를 이해하는 데 있어 매우 중요한 의미를 갖는다. 즉, SQL 문장 중간에 작은 공백문자 하나만 추가되더라도 DBMS는 서로 다른 SQL 문장으로 인식하기 때문에 캐싱된 버전을 사용하지 못하게 된다. 예를 들어, 아래 6가지 경우에 옵티마이저는 각각 다른 SQL로 인식해 별도의 실행계획을 수립한다. 공백 문자 또는 줄바꿈 대소문자 구분 주석(Comment) 테이블 Owner 명시 옵티마이져 힌트 사용 조건절 비교 값 5번은 실행계획을 다르게 할 의도로 힌트를 사용했으므로 논외다. 1~3번은 실행계획이 다를 아무런 이유가 없고, 4번도 서로 같은 테이블이면 실행계획은 같아야 마땅하다. 그럼에도, 문자열을 조금 다르게 기술하는 바람에 서로 다른 SQL로 인식돼 각각 하드파싱을 일으키고 서로 다른 메모리 공간을 차지하게 된다. 이런 비효율을 줄이고 공유 가능한 형태로 SQL을 작성하려면 개발 초기에 SQL 작성 표준을 정해 이를 준수하도록 해야 한다. 하지만 1~4번처럼 SQL을 작성한다고 해서 라이브러리 캐시 효율이 우려할 만큼 나빠지지는 않는다. 100% 같은 내용의 SQL을 두 명의 개발자가 각각 다르게 작성할 가능성은 매우 낮기 때문이다. 쿼리 툴에서 수행되는 임의질의(Ad-Hoc Query)는 수행빈도가 낮아 문제 될 것이 없다. 결론적으로, 라이브러리 캐시 효율과 직접 관련 있는 패턴은 6번뿐이다. 즉, 사용자가 입력한 값을 조건절에 문자열로 붙여가며 매번 다른 SQL로 실행하는 경우다. 이런 패턴의 SQL을 ‘리터럴(Literal) SQL’이라고 부르기로 하자. 만약 하루 1,000만 번 로그인이 발생하는 애플리케이션에서 사용자 로그인을 6번처럼 리터럴 SQL로 개발했다면 어떤 일이 발생할까. 시스템이 한가한 시간대에 개별 쿼리 성능으로 보면 잘 느끼지 못할 수 있지만 사용자가 동시에 몰리는 peak 시간대에 시스템을 장애 상황으로 몰고 갈 수도 있다. 사용자 로그인을 처리하는 프로그램에 SQL을 위 6번과 같은 식으로 작성하면, 아래 처럼 프로시저가 로그인 사용자마다 하나씩 만들어지게 된다. 이들 프로시저를 만들어 주는 역할을 옵티마이저가 담당한다고 했다. 위 프로시저의 내부 처리 루틴은 모두 같을 것이다. 그것이 가장 큰 문제인데, 모든 프로시저의 처리 루틴이 같다면 여러 개 생성하기보다 아래 처럼 로그인 ID를 파라미터로 받아 하나의 프로시저로 처리하도록 하는 것이 마땅하다. 이처럼 파라미터 Driven 방식으로 SQL을 작성하는 방법이 제공되는데, 그것이 곧 바인드 변수(Bind Variable)다. 바인드 변수를 사용하면 하나의 프로시저를 공유하면서 반복 재사용할 수 있게 된다. 위 SQL과 같이 바인드 변수를 사용하면 이를 처음 수행한 세션이 하드파싱을 통해 실행계획을 생성한다. 그 실행계획을 한번 사용하고 버리는 것이 아니라 라이브러리에 캐싱해 둠으로써 같은 SQL을 수행하는 다른 세션들이 반복 재사용할 수 있도록 한다. 즉, 이후 세션들은 캐시에서 실행계획을 얻어 입력 값만 새롭게 바인딩하면서 바로 실행하게 된다. 아래는 바인드 변수를 사용한 SQL을 20,000번 수행할 때의 SQL 트레이스 결과다.(SQL 트레이스를 처음 접한 독자는 부록 B를 참조하기 바란다.) Parse Call은 SQL 문장을 캐시에서 찾으려고 시도한 횟수를 말하는데, Execute Call 횟수만큼 Parse Call이 반복된 것을 볼 수 있다. 최초 Parse Call이 발생한 시점에 라이브러리 캐시에서 커서를 찾지 못해 하드 파싱을 수행한 사실도 아래쪽 라이브러리 캐시 Miss 항목(굵은 글꼴)을 보고 알 수 있다. 만약 처음 수행할 때부터 캐시에서 찾아진다면 이 항목은 0으로 표시된다. 바인드 변수를 사용했을 때의 효과는 아주 분명하다. SQL과 실행계획을 여러 개 캐싱하지 않고 하나를 반복 재사용하므로 파싱 소요시간과 메모리 사용량을 줄여준다. 궁극적으로, 시스템 전반의 CPU와 메모리 사용률을 낮춰 데이터베이스 성능과 확장성을 높이고, 특히 동시 사용자 접속이 많을 때는 그 효과성이 절대적이다. 아래와 같은 경우에는 바인드 변수를 쓰지 않아도 무방하다. 위 경우가 아니라면, 특히 OLTP 환경에선 반드시 바인드 변수를 사용할 것을 권고한다. 위와 같은 권고에도 불구하고 무분별하게 리터럴 SQL 위주로 애플리케이션을 개발하면 라이브러리 캐시 경합 때문에 시스템 정상 가동이 어려운 상황에 직면할 수 있다. 이에 대비해 각 DBMS는 조건절 비교 값이 리터럴 상수일 때 이를 자동으로 변수화 시켜주는 기능을 제공한다. SQL Server에선 이 기능을 ‘단순 매개 변수화(simple parameterization)’라고 부르며(2000 버전까지는 ‘자동 매개 변수화(auto-parameterization)’라고 불렀음) 기본적으로 활성화된다. Oracle에선 cursor_sharing 파라미터를 시스템 또는 세션 레벨에서 FORCE나 SIMILAR로 설정(기본 값은 EXACT)하면 된다. 리터럴 쿼리에 의한 파싱 부하가 극심한 상황에서 이 기능이 시스템 부하를도 만만치 않다. 무엇보다, 이 옵션을 적용하는 순간 실행계획이 갑자기 바뀌어 기존에 잘 수행되던 SQL이 갑자기 느려질 수 있다. 사용자가 의도적으로 사용한 상수까지 변수화되면서 문제를 일으키기도 한다. Oracle의 경우, 이 기능은 응급처방으로 사용해야지 절대 영구 적용할 목적으로 사용해서는 안 된다. SQL Server에선 기본적으로 활성화 돼 있긴 하지만 가급적 바인드 변수를 사용함으로써 이 기능이 작동하는 경우를 최소화해야 한다. 바인드 변수를 사용하면 SQL이 최초 수행될 때 최적화를 거친 실행계획을 캐시에 저장하고, 실행시점에는 그것을 그대로 가져와 값만 다르게 바인딩하면서 반복 재사용한다고 설명했다. 여기서, 변수를 바인딩하는 시점이 최적화 이후라는 사실을 상기하기 바란다. 즉, 나중에 반복 수행될 때 어떤 값이 입력될지 알 수 없기 때문에 옵티마이저는 조건절 칼럼의 데이터 분포가 균일하다는 가정을 세우고 최적화를 수행한다. 칼럼에 대한 히스토그램 정보가 딕셔너리에 저장돼 있어도 이를 활용하지 못하는 것이다. 칼럼 분포가 균일할 때는 이렇게 처리해도 나쁘지 않지만, 그렇지 않을 때는 실행 시점에 바인딩되는 값에 따라 쿼리 성능이 다르게 나타날 수 있다. 이럴 때는 바인드 변수를 사용하는 것보다 상수 값을 사용하는 것이 나을 수 있는데, 그 값에 대한 칼럼 히스토그램 정보를 이용해 좀 더 최적의 실행계획을 수립할 가능성이 높기 때문이다. 바인드 변수 사용에 따른 부작용을 극복하려고 Oracle 9i부터 ‘바인드 변수 Peeking’ 기능을 도입하였다. SQL Server도 같은 기능을 제공하며 ‘Parameter Sniffing’이라고 부른다. ‘Peeking’이나 ‘Sniffing’이라는 단어가 의미하듯이 이것은 SQL이 첫 번째 수행될 때의 바인드 변수 값을 살짝 훔쳐 보고, 그 값에 대한 칼럼 분포를 이용해 실행계획을 결정하는 기능이다. 그런데 이것은 매우 위험한 기능이 아닐 수 없다. 처음 실행될 때 입력된 값과 전혀 다른 분포를 갖는 값이 나중에 입력되면 쿼리 성능이 갑자기 느려지는 현상이 발생할 수 있기 때문이다. 아침에 업무가 시작되면서 사용자가 처음 입력한 값이 무엇이냐에 따라 실행계획이 결정되고, 그것에 의해 그날 하루 종일 프로그램의 수행 성능이 결정된다면 시스템 관리자 입장에서는 불안하지 않을 수 없다. 물론, 해당 쿼리의 수행빈도가 매우 높아 캐시에서 절대 밀려나지 않을 때 그렇다. 쿼리 수행빈도가 낮아 캐시에서 자주 밀려나도 문제다. 하루 중에 실행계획이 수시로 바뀔 수 있기 때문이며, 이 또한 관리자를 불안하게 만드는 요인이다. 쿼리 수행 전에 확인하는 실행계획은 바인드 변수 Peeking 기능이 적용되지 않은 실행계획이라는 사실도 기억하기 바란다. 사용자가 쿼리 수행 전에 실행계획을 확인할 때는 변수에 값을 바인딩하지 않으므로 옵티마이저는 변수 값을 Peeking 할 수 없다. 따라서 사용자는 평균 분포에 의한 실행계획을 확인하고 프로그램을 배포하게 되는데, 그 SQL이 실제 실행될 때는 바인드 변수 Peeking을 일으켜 다른 방식으로 수행될 수 있다. 이런 이유로, 현재 대부분의 운영 시스템에서는 아래 처럼 바인드 변수 Peeking 기능을 비활성화시킨 상태에서 운영 중이다. 바인드 변수 Peeking 같은 기능의 불완전성을 해소하기 위해 DBMS 벤더들이 계속 노력 중이다. Oracle의 경우는 11g에 와서 ‘적응적 커서 공유(Adaptive Cursor Sharing)’라는 기능을 도입함으로써 입력된 변수 값의 분포에 따라 다른 실행계획이 사용되도록 처리하고 있다. 하지만 이 기능도 아직 완전하지 못하기 때문에 부작용이 완전히 해소될 때까진 개발자의 노력이 필요하다. 예를 들어, 아래 쿼리로 아파트매물 테이블을 읽을 때 서울시와 경기도처럼 선택도(Selectivity, 3장 1절 4항 참조)가 높은 값이 입력될 때는 Full Table Scan이 유리하고, 강원도나 제주도처럼 선택도가 낮은 값이 입력될 때는 인덱스를 경유해 테이블을 액세스하는 것이 유리하다. 그럴 때 위 쿼리에서 바인딩 되는 값에 따라 실행계획을 아래와 같이 분리하는 방안을 고려할 수 있다. 지금까지 하드파싱에 의한 라이브러리 캐시 부하를 최소화하기 위한 방안으로서 바인드 변수 사용의 중요성을 강조하였다. 이어서 애플리케이션 커서 캐싱 기능을 소개하기에 앞서 Static SQL과 Dynamic SQL의 의미를 명확히 하고자 한다. Static SQL이란, String형 변수에 담지 않고 코드 사이에 직접 기술한 SQL문을 말한다. 다른 말로 ‘Embedded SQL’이라고도 한다. 아래는 Pro*C 구문으로 Static SQL을 작성한 예시다. SQL문을 String 변수에 담지 않고 마치 예약된 키워드처럼 C/C++ 코드 사이에 섞어서 기술한 것을 볼 수 있다. Pro*C, SQLJ와 같은 PreCompile 언어를 잘 모르는 독자를 위해 간단히 설명하면, Pro*C에서 소스 프로그램(.pc)을 작성해서 PreCompiler로 PreCompile하면 순수 C/C++ 코드가 만들어진다. 이를 다시 C/C++ Compiler로 Compile해 실행파일이 만들어지면 그것을 실행한다. PreCompiler가 PreCompile 과정에서 Static(=Embedded) SQL을 발견하면 이를 SQL 런타임 라이브러리에 포함된 함수를 호출하는 코드로 변환한다. 이 과정에서 결국은 String형 변수에 담긴다. Static SQL이든 Dynamic SQL이든 PreCompile 단계를 거치고 나면 String 변수에 담기기는 마찬가지지만 Static SQL은 런타임 시에 절대 변하지 않으므로 PreCompile 단계에서 구문 분석, 유효 오브젝트 여부, 오브젝트 액세스 권한 등을 체크하는 것이 가능하다. Dynamic SQL이란, String형 변수에 담아서 기술하는 SQL문을 말한다. String 변수를 사용하므로 조건에 따라 SQL문을 동적으로 바꿀 수 있고, 또는 런타임 시에 사용자로부터 SQL문의 일부 또는 전부를 입력 받아서 실행할 수도 있다. 따라서 PreCompile 시 Syntax, Semantics 체크가 불가능하므로 Dynamic SQL에 대해선 PreCompiler는 내용을 확인하지 않고 그대로 DBMS에 전달한다. 아래는 Pro*C에서 Dynamic SQL을 작성한 사례다. SQL을 String형 변수에 담아 실행하는 것에 주목하기 바란다. 바로 아래 주석 처리한 부분은 SQL을 런타임 시 입력 받는 방법을 예시한다. Static(=Embedded) SQL을 지원하는 개발 언어는 많지 않으며, PowerBuilder, PL/SQL, Pro*C, SQLJ 정도가 있다. 그 외 개발 언어에선 SQL을 모두 String 변수에 담아서 실행한다. 따라서 이들 언어에서 작성된 SQL은 모두 Dynamic SQL이다. 또한 Toad, Orange, SQL*Plus, 그리고 SQL Server의 쿼리 분석기 같은 Ad-hoc 쿼리 툴에서 작성하는 SQL도 모두 Dynamic SQL이다. 이들 툴은 앞으로 어떤 SQL이 실행될지 모르는 상태에서 빌드(Build)되며, 런타임 시에 사용자로부터 입력받은 SQL을 그대로 DBMS에 던지는 역할만 할 뿐이다. 지금까지 설명한 Static, Dynamic SQL은 애플리케이션 개발 측면에서의 구분일 뿐이며, 데이터베이스 입장에선 차이가 없다. Static SQL을 사용하든 Dynamic SQL을 사용하든 옵티마이저는 SQL 문장 자체만 인식할 뿐이므로 성능에도 영향을 주지 않는다. (단, Static SQL일 때만 애플리케이션 커서 캐싱 기능이 작동하는 개발 언어도 있으므로 그때는 성능에 영향을 줄 수 있다.) 따라서 라이브러리 캐시 효율을 논할 때 Static이냐 Dynamic이냐의 차이보다는 바인드 변수 사용 여부에 초점을 맞춰야 한다. Dynamic으로 개발하더라도 바인드 변수만 잘 사용했다면 라이브러리 캐시 효율을 떨어뜨리지 않는다는 뜻이다. 바인드 변수를 사용하지 않고 Literal 값을 SQL 문자열에 결합하는 방식으로 개발했을 때, 반복적인 하드파싱으로 성능이 얼마나 저하되는지, 그리고 그 때문에 라이브러리 캐시에 얼마나 심한 경합이 발생하는지는 앞에서 충분히 설명하였다. 같은 SQL을 아주 여러 번 반복해서 수행해야 할 때, 첫 번째는 하드파싱이 일어나겠지만 이후부터는 라이브러리 캐시에 공유된 버전을 찾아 가볍게 실행할 수 있다. 그렇더라도 SQL 문장의 문법적, 의미적 오류가 없는지 확인하고, 해시함수로부터 반환된 해시 값을 이용해 캐시에서 실행계획을 찾고, 수행에 필요한 메모리 공간(Persistent Area와 Runtime Area)을 할당하는 등의 작업을 매번 반복하는 것은 비효율적이다. 이런 과정을 생략하고 빠르게 SQL을 수행하는 방법이 있는데, 이를 ‘애플리케이션 커서 캐싱’이라고 부르기로 하자. 개발 언어마다 구현방식이 다르므로 이 기능을 활용하려면 API를 잘 살펴봐야 한다. Pro*C를 예로 들면, SQL을 수행하는 부분을 아래 처럼 두 개 옵션으로 감싸면 된다. 그러면 커서를 해제하지 않고 루프 내에서 반복 재사용한다. 아래는 애플리케이션에서 커서를 캐싱한 상태에서 같은 SQL을 5,000번 반복 수행했을 때의 SQL 트레이스 결과다. 일반적인 방식으로 같은 SQL을 반복 수행할 때는 Parse Call 횟수가 Execute Call 횟수와 같게 나타난다고 앞서 설명하였다. 반면, 위 트레이스 결과에선 Parse Call이 한 번만 발생했고, 이후 4,999번 수행할 때는 Parse Call이 전혀 발생하지 않았음을 알 수 있다. 최초 Parse Call이 발생한 시점에 하드 파싱을 수행한 사실도 아래쪽 라이브러리 캐시 Miss 항목을 보고 알 수 있다. JAVA에서 이 기능을 구현하려면 아래와 같이 묵시적 캐싱(Implicit Caching) 옵션을 사용하면 된다. 또는 아래처럼 Statement를 닫지 않고 재사용해도 같은 효과를 얻을 수 있다.SQL 파싱 부하
2. 데이터 모델의 기본 개념의 이해
1. SQL 처리과정
가. SQL 파싱(Parsing)
나. SQL 최적화(Optimization)
2. 캐싱된 SQL 공유
가. 실행계획 공유 조건
나. 실행계획을 공유하지 못하는 경우
3. 바인드 변수 사용하기
가. 바인드 변수의 중요성
이들 쿼리는 파싱 소요시간이 쿼리 총 소요시간에서 차지하는 비중이 매우 낮고, 수행빈도도 낮아 하드파싱에 의한 라이브러리 캐시 부하를 유발할 가능성이 낮음. 그러므로 바인드 변수 대신 상수 조건절을 사용함으로써 옵티마이저가 칼럼 히스토그램을 활용할 수 있도록 하는 것이 유리
특히 값 분포가 균일하지 않아 옵티마이저가 칼럼 히스토그램 정보를 활용하도록 유도하고자 할 때
나. 바인드 변수 사용 시 주의사항
다. 바인드 변수 부작용을 극복하기 위한 노력
4. Static SQL과 Dynamic SQL
가. Static SQL
나. Dynamic SQL
다. 바인드 변수의 중요성 재강조
5. 애플리케이션 커서 캐싱