[그림으로 배우는 오라클구조]SQL문 분석과 공유 풀

SQL문의 분석과 공유 풀을 왜 배워야 하는가?

아무리 디스크를 늘리고 클럭이 빠른 CPU를 추가하더라도 RDBMS의 처리 방법 자체가 나쁘면 처리 방법이 효율적인 더 낮은 사양의 장비에서 동작하는 RDBMS에게 질 수밖에 없다. 또한 SQL문을 처리하는 방법을 생성하는 데는 CPU를 오래 사용하므로, 처리 방법을 생성하는 횟수를 줄이면 DM 전체의 성능을 높일 수도 있다. 이번 장에서 주로 다를 분석(Parse, 파스)은 이런 처리 방법 중 하나이다. 

 

SQL문과 일반 프로그래밍 언어의 차이

일반적인 프로그래밍 언어와 SQL의 차이는 무엇일까. SQL은 처리방법(절차)을 기술하지 않는다는 점이 가장 큰 차이라고 말할 수 있다. 프로그래밍 언어는 종류에 상관없이 처리 방법을 기술해야한다. 어디서 데이터를 꺼내 와서 반복문을 수행하고 조건문으로분기한다. 와 같은 처리 방법이 이에 해당한다. 이해 비해 SQL문은 데이터의 조건이나 관계만 기술한다.

SELECT A FROM B WHERE C=1

이는 테이블 B에서 C=1이라는 조건을 만족하는 데이터에서 A라고 하는 속성의 정보를 꺼내오라는 SQL이다. '인덱스를 사용하라' 또는 '풀 스캔을 하라'와 같은 처리 방법에 대한 기술이 없다. RDBMS에서 어떤 작업을 할 때 사람이 아니라 옵티마이저(파서, parser)라고불리는 기능이 처리 방법을 생각한다. 이는 SQL문을 분석하고 실행 계획이라고 하는 처리 방법을 생성한다. 

 

서버 프로세스와 분석

서버 프로세스는 SQL문의 처리를 최우선적으로 수행하는 프로세스라고 알고 있을 것이다. SQL문을 처리하는데에 있어서 분석이란 SQL문을 분해해서 어떤 요소로 구성되어 있는지를 조사하는것뿐만 아니라 어떤 식으로 처리할지까지 생각하는 것을 의미한다. 오라클은 규칙기반(rule base)과 비용기반(cost base)이란 알고리즘을 가지고 있다. 

 

비용기반이란, 처리 시간이나 I/O 횟수가 가장 작다고 생각 되는 처리 방법이 최상이다. 라는 알고리즘이다. 처리에 필요하다고 생각되는 시간 도는 자원 사용량을 '비용'이라고 부르는데 이를 이용해 처리시간이나 I/O 횟수를 예측한다. 

 

이를 위해

'이 테이블에 데이터가 몇 로우가 존재하고, 양은 어느 정도이다. 칼럼 데이터의 최댓값과 최솟값은 이런값이다. 해당 테이블의 인덱스는 이렇다..'

와 같은 기초 수치를 사전에 파악해 둔 후 이 통계 정보를 이용해 시간을 예측한다. 이런 통계 정보를 얻는 작업을 '애널라이즈(Analyze)라고 한다. 이는 관리자가 수행하지 않더라도 오라클이 자동으로 수행한다. 

실행 계획이 최적이라는 것을 판단하기 위해

테이블 A의 ID, Value, 그리고 테이블 B의 ID, value의 네가지 칼럼에 인덱스가 생성되어 있다고 가정하고 아래 SQL문을 수행해보자. 

SELECT * FROM A,B WHERE A.ID = B.ID AND A.VALUE=1 AND B.VALUE=1;

테이블 A와 B 중 어느 곳을 검색하지에 선택지가 생긴다. 여기서,

A의 데이터가 B에 비해 훨씬 많다

A의 value 칼럼의 값이 대부분 1이다 라고 가정하자.

A를 먼저 검색하게 되면 value 값이 1인 많은 데이터를 가져오게 되고 이 데이터당 한번 씩 B의 id으 값에 접근하게 된다. 이에 비해 value 값이 모두 다른 B의 데이터에서 데이터를 가져온 후 그 하나의 값에 맞는 id값을 테이블 A에서 가져오면 작업이 훨씬 쉽게 끝난다. 이렇게 DBMS는 실행 계획의 좋고 나쁨으로 인해 매우 큰 성능 차이가 발생한다. 

 

그렇다면 무슨 수로 '어떤 처리 방법이 가장 좋은지'를 판단할까. 모든 처리 방법의 비용을 계산해서 비교하는 것 외에는 방법이 없지만, 테이블의 수가 늘어날 수록 비용을 계산해야 하는 개수가 막대하게 증가하기 때문에 이는 사실상 불가능하다. 이렇게 '선택할 수 있는 실행 계획의 수가 너무 많다는 점'과 '이런 실행계획이 예측에 지나지 않는다는 점' 때문에 간혹 DBMS가 좋지 않은 실행 계획을 선택하는 경우가 있다. 

 

공유 풀의 동작과 구조

인덱스를 사용해서 한 건의 데이터를 가져오는 간단한 SQL문을 수행했다고 가정하면, 실제 데이터를 처리하는 데 필요한 CPU자원보다 데이터를 처리하기 위한 과정을 생성하는 SQL문의 분석 단계에서 더 많은 CPU를 소모할 수도 있다. CPU자원을 분석에 사용하는 것은 상당히 아깝다고 생각할 수 있다. 때문에 실행 계획을 공유하게 되면 자원 소비를 좀 더 절약할 수 있지 않을까?라고 생각할 수 있다.  이를 공유 풀이라고 하고 공유 풀에는 최근 실행한 SQL문의 실행 계획이 캐시 되어있다. 

 

클라이언트에게 SELECT문을 요청 받았다고 하자. 서버 프로세스는 곽에 같은 요청을 받았던 적이 있다는 것을 공유 풀에서 꺼내 사용하여 더 빠른 처리를 할 수 있었다. 공유 풀 또한 프로세스 간 공유 되어야 하므로 버퍼 캐시와 같이 공유 메모리에 있다. 

 

오라클은 어떻게 SQL문을 같다고 판단할까? 오라클은 SQL문을 해시함수로 반환하여 이를 ID로 저장한다. 따라서 대소문자의 차이도 오라클은 다른 SQL문으로 인식한다. 

 

SELECT id, cust_name, tel_no FROM cust WHERE id = 1 ;
SELECT id, cust_name, tel_no FROM cust WHERE id = 2 ;

이런 id만 다른 SQL문 또한 사람이 보았을 때는 같은 실행 계획을 적용해도 된다고 생각할 수 있다. 하지만 해시함수에 대입한 결과인 id는 전혀 다르기 때문에 오라클은 같은 실행 계획을 적용하지 않는다. 이럴 땐 바인드 함수를 이용한다. 

SELECT id, cust_name, tel_no FROM cust WHERE id = :A ;
SELECT id, cust_name, tel_no FROM cust WHERE id = :A ;

:A라는 변수 안에 1이나 2 같은 값을 넣은 후 SQL문을 실행하면 id가 같기 때문에 분석 결과가 캐시에 남아 있어 분석 작업을 수행하지 않는다 

 

공유 풀에 캐시되어 있는 실행 게획을 찾아 재사용하는 경우를 소프트 파스(soft parse), 그렇지 않고 실행 계획을 생성하는 경우를 하드 파스(hard parse)라고 부른다.