그림으로 공부하는 오라클 구조 : SQL문 분석과 공유 풀

1. SQL문의 분석과 공유 풀을 배워야 하는 이유

디스크를 늘리고 클럭이 빠른 CPU를 추가하더라도 처리 방법 자체가 나쁘면, 안좋은 장비에서 동작하지만 처리 방법이 더 효율적인 RDBMS에게 밀리게 된다. 또한, SQL문을 처리하는 방법을 생성하는 데는 CPU를 오래 사용하므로, 처리 방법을 생성하는 횟수를 줄이면 데이터베이스 전체의 성능을 높일 수 있다.

 

2. SQL문과 일반적인 프로그래밍 언어의 차이

가장 큰 차이점은 SQL은 '처리 방법(절차)'을 기술하지 않는다는 점이다.

 

프로그래밍 언어는 객체지향 언어, 스크립트 언어에 상관없이 처리 방법(어디선가 데이터를 꺼내와서 반복문을 수행하고 조건문을 사용해 분기를 시킨다)을 기술해야 한다.

 

반면에, SQL문에서는 '데이터의 조건이나 관계'만을 기술한다. 예를 들어, 'SELECT A FROM B WHERE C = 1'과 같은 SELECT문은 테이블 B에서 'C=1'이라는 조건을 만족하는 데이터에서 A라고 하는 속성(컬럼)의 정보를 꺼내오라는 SQL문이다. 어디에도 '인덱스를 사용해서'나 '풀 스캔을 하라'와 같은 처리 방법을 기술하지 않는다. RDBMS에서는 '옵티마이저(파서, parser)'라고 불리는 기능이 처리 방법을 생각한다. 옵티마이저가 SQL문을 분석하고 '실행 계획'이라고 하는 처리 방법을 생성해준다.

 

3. 서버 프로세스와 분석

오라클에서의 분석이란, SQL문을 분해해서 어떤 요소(테이블이나 컬러 등)로 구성되어 있는지를 조사하는 것뿐만 아니라 어떤 식으로 처리할지까지 생각하는 것을 의미한다. '생각한다'는 것은 알고리즘을 기반으로 처리하는 것으로, 오라클은 '규칙 기반(rule base)'와 '비용 기반(cost base)'이라는 알고리즘을 가지고 있다.

비용 기반은 '처리 시간이나 I/O 횟수가 가장 작다고 생각되는 처리 방법이 최상이다'라는 알고리즘이다. 오라클에서는 처리 시간이나 I/O 횟수를 예측하기 위해서 '비용'이라고 불리는 수치를 이용한다. 이때 비용은 처리에 필요하다고 생각되는 시간 또는 자원 사용량이다.

 

비용을 예측하기 위해서는 기초 수치(데이터양 등)가 필요하다. 비용은 '통계 정보'라 불리는 기초 수치를 기반으로 계산된다. 통계 정보는 '이 테이블에는 데이터가 몇 로우가 존재하고 있으며, 데이터는 이 정도이다. 컬럼의 데이터 최댓값과 최솟값은 이런 값이다. 해당 테이블의 인덱스는 ...'과 같은 테이블이나 인덱스에 관한 기초 수치를 말한다. 오라클에서는 통계 정보를 '애널라이즈(Analyze)'라고 불리는 작업을 통해 얻는다.

 

비용 계산에 필요한 정보

 

4. 실행 계획이 최적이라는 것을 판단

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

 

테이블 A의 ID, value 그리고 테이블 B의 ID, value의 네 가지 컬럼에 인덱스가 생성되어 있다고 가정했을 때, 테이블A에서 검색한 후 테이블 B를 검색하는 일반적인 실행으로 얻을 수 있는 데이터는 한 건이므로 처리가 바로 끝난다.

 

하지만 테이블B는 데이터가 100건만 들어가 있고, 테이블A는 1000만건의 데이터를 가지고 있다고 가정했을 때는 실행 계획에 따라 성능 차이가 발생할 수 있다.

 

테이블A에서 테이블B순으로 검색하는 경우. 1과 2번 작업이 1000만 번 발생해서 처리가 매우 무거워진다. 한 블록을 읽어오는데 10밀리초가 걸린다고 가정하면, 테이블A에서 1000만 번 읽어오게 되면 10만초가 걸리게 된다.

 

반대로 테이블B에서 테이블A 순으로 검색하는 경우, 1과 2번의 작업을 한 번씩만 하면 되므로 처리가 매우 가벼워진다. 블록 한 개를 읽어오는 데 10밀리초가 걸린하고 가정하면, 수십밀리초 정도로 처리할 수 있게 된다.

 

'어떤 처리 방법이 가장 좋은지(비용이 가장 적을 것 같은지)'를 판단하는 방법은 기본적으로 모든 처리 방법의 비용을 계산해서 비교하는 것이다. 하지만 실제로 모든 처리 방법의 비용을 계산할 수는 없다. 테이블의 개수가 늘어날수록 비용을 계산해야 하는 개수가 막대하기 때문이다.

 

테이블이 한 개인 경우

 

테이블이 두 개인 경우

 

테이블이 10개인 경우

 

5. 공유 풀의 동작과 구조

인덱스를 사용해서 한 건의 데이터를 가져오는 간단한 SQL문을 수행한다고 했을 때, 실제 데이터를 처리하는 데 필요한 CPU자원보다 데이터를 처리하기 위한 과정을 생성하는 SQL문의 분석 단계에서 더 많은 CPU를 소모할 수도 있다. 이러한 CPU 자원의 사용을 줄이기 위해 공유 풀을 활용할 수 있는데, 여기서 공유 풀은 실행 계획을 재사용하여 분석 작업을 줄이기 위한 존재이다.

 

 

서버 프로세스는 SQL문 요청이 왔을 때, 이전에 동일한 SQL문을 수행했던 적이 있으면, 공유 풀에서 이전에 처리했던 방법(실행 계획)을 찾아서 사용한다.

 

공유 풀은 프로세스 간에 공유되어야 하기 때문에 버퍼 캐시와 마찬가지로 공유 메모리에 존재한다. 공유 메모리의 많은 부분이 버퍼 캐시로 사용되고, 거기서 남은 일부가 공유 풀로 사용된다. 공유 풀은 라이브버리 캐시(실행 계획이 존재)나 딕셔너리 캐시 같은 영역으로 나눠진다.

 

 

- 동일한 SQL문 판단

 

오라클은 해시 알고리즘을 사용해서 SQL문마다 ID를 생성한다. SQL문을 문자열(데이터)로 해시 함수에 입력하고 함수에서 출력된 해시 값을 SQL문의 ID로 사용하는 것이다. 해시 함수에서 대문자와 소문자는 다른 문자이므로 쿼리의 대문자와 소문자가 다르면 해시 값도 달라지게 된다.

Select id, cust_name, tel_no from cust from id = 1;
select id, cust_name, tel_no from cust from id = 1;

또한 검색 조건의 값이 다른 SQL문에 바인드 변수를 사용하면 같은 SQL문이라고 판단한다.

select id, cust_name, tel_no from cust from id = 1;
select id, cust_name, tel_no from cust from id = 2;

select id, cust_name, tel_no from cust from id = :A;
select id, cust_name, tel_no from cust from id = :A;

 

- 하드 파스(hard parse)와 소프트 파스(soft parse)

 

하드 파스 : 공유 풀에 실행 계획이 없을 때 실행 계획을 생성하는 경우

소프트 파스 : 해시 값을 요청한 결과, 공유 풀에 캐시되어 있는 실행 계획을 찾아서 재사용하는 경우

 

- 분석과 공유 풀의 수치 정보는 statspack(오라클 성능 진단 도구)의 보고서를 통해 확인할 수 있다.

 

7. 요약

• SQL문에는 처리 방법이 적혀 있지 않기 때문에 오라클이 처리 방법(실행 계획)을 생성할 필요가 있다.
• 실행 계획의 좋고 나쁨에 따라 성능이 크게 변할 수 있다.
• 실행 계획을 생성하기 위해서는 많은 양의 CPU를 사용하기 때문에 공유 풀(라이브러리 캐시)에 실행 계획을 캐시해두고 재활용한다.

 

이전장 : 캐시와 공유 메모리

다음장 : 오라클의 기동과 정지

 

출처 : 스키타 아츠시 외 4명, 『그림으로 공부하는 오라클 구조』, 이민재, 제이펍(2022), p62-p78