[DB] 데이터베이스 실행계획 및 공유 풀

SQL문 분석

일반적인 프로그래밍 언어와 SQL의 차이는 무엇일까요??

 

여러가지가 있을 수 있지만 SQL은 처리 방법(절차)을 기술하지 않는다는 점이 가장 큰 차이라고 말할 수 있습니다.

 

예를들어 프로그래밍할 때는 '여기에서 데이터를 꺼내 와서 반복문을 수행하고 조건문을 사용해 분기를 시킨다'와 같이 처리 방법을 기술했을 겁니다.

이에 비해 SQL문에서는 'SELECT A FROM B WHERE C=1'과 같이 테이블 B에서 C=1이라는 조건을 만족하는 데이터에서 A라고하는 속성의 정보를 꺼내오라고만 하지 어디에도 '인덱스를 사용해라', '풀 스캔을 하라' 와 같은 처리 방법을 기술하지는 않습니다.

 

그러면 SQL은 어떻게 우리가 원하는 데이터를 가져올까요??

 

RDBMS는 옵티마이저(파서,parse)라고 불리는 기능이 처리 방법을 생각해줍니다.

옵티마이저가 SQL문을 분석하고 실행 계획이라고 하는 처리 방법을 생성해줍니다.

 

 

실행 계획

실행 계획을 세우기 위해서는 먼저 분석을 해야합니다.

여기서 분석이란 SQL문을 분해해서 어떤 요소로 구성되어 있는지, 어떤 식으로 처리할지를 생각하는 것입니다.

 

 

오라클이 더 나은 방법을 선택하는 기준은 비용 기반(cost base)입니다.

비용이란 간단하게 '처리에 필요하다고 생각되는 시간 또는 자원 사용량'입니다.

그리고 이러한 비용을 예측하기 위해서는 기초 수치 즉 '통계 정보'라는 정보가 필요합니다.

 

 

오라클은 통계정보를 애널라이즈(Analyze)라고 불리는 작업을 통해 얻습니다.

 

통계정보는 다양한 종류의 정보를 수집합니다.

• 테이블에는 데이터가 몇 로우(Row) 존재하는지
• 데이터 양은 어느정도인지
• 컬럼의 데이터 최댓값과 최솟값은 무엇인지' 등등 정보입니다.

 

 

 

 

그러면 실행 계획이 최적이 아니면 어떻게 되는지 한번 예시를 살펴보겠습니다.

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에서 검색한 후에 테이블 A를 검색하는 방법이 있을 것입니다.

 

만약 추가로 테이블 A는 1000만건의 데이터를 가지고 있고, 테이블 B는 데이터가 100건만 들어있다고 생각해봅시다.

 

그러면 테이블 A → 테이블 B순으로 검색하는 처리는 비교적 많은 비용이 들지만, 테이블 B → 테이블 A순으로 처리하는것은 적은 비용이 들게 될 것입니다.

 

이렇게 실행 계획의 좋고 나쁨으로 인해 매우 큰 성능 차이가 발생합니다.

 

공유 풀

좋은 실행계획은 찾는 방법은 모든 경우의 처리 방법 비용을 계산해서 비교하는 방법 밖에 없습니다.

 

그런데 모든 경우의 처리 방법을 계산하기 위해서는 통계정보인 애널라이즈를 가져오고 다양한 방법을 비교해봐야 합니다.

 

예를들어 테이블이 한개일때 테이블을 검색하는 방법이 인덱스 액세스와 풀 스캔 방법 2가지가 있다고 가정해보겠습니다.

 

 

그러면 테이블이 2개일때는 몇가지의 경우의 수가 있을까요??

• 테이블 A는 인덱스, 테이블 B도 인덱스
• 테이블 A는 인덱스, 테이블 B는 풀 스캔
• 테이블 A는 풀 스캔, 테이블 B는 인덱스
• 테이블 A는 풀 스캔, 테이블 B는 풀 스캔
• 테이블 B는 인덱스, 테이블 A는 인덱스
• 테이블 B는 인덱스, 테이블 A는 풀 스캔
• 테이블 B는 풀 스캔, 테이블 A는 인덱스
• 테이블 B는 풀 스캔, 테이블 A는 풀 스캔

 

 

위와 같이 8가지의 선택지가 나옵니다. 그런데 실제로는 검색에 여러가지의 조인방법도 있으니 훨씬 많은 경우를 비교해야 합니다.

 

그러면 SQL문을 수행할때 데이터를 처리하는 과정보다 SQL문 분석 단계에서 더 많은 CPU를 소모할 수도 있습니다.

배보다 배꼽이 더 커지는 경우인것이죠.

 

그래서 공유 풀은 실행 계획을 재사용하여 분석 작업을 줄이기 위한 존재라고 봐도 좋습니다.

캐시와 같은 원리라고 생각하면 좀 더 쉬울것입니다.

 

 

공유 풀에 실행 계획이 있을 때의 동작을 살펴보겠습니다.

1. 클라이언트로 부터 데이터 요청인 SQL문을 요청합니다.
2. SQL문을 과거의 요청이랑 비교하여 과거에 요청이 있었는지 판단합니다.
3. (과거 기록이 있는경우) 공유 풀에서 그때 실행 계획을 꺼내서 사용합니다.

 

공유 풀도 프로세스 간에 공유되어야 하므로 버퍼 캐시와 마찬가지로 공유 메모리에 있습니다.

공유 메모리에 많은 부분이 버퍼 캐시로 사용되고, 거기서 남은 일부가 공유 풀로 사용됩니다.

 

 

그러면 오라클은 어떻게 SQL문이 같다고 판단할까요??

 

 

오라클은 해시 알고리즘을 사용해서 SQL문마다 ID를 생성합니다.

좀 더 자세히 살펴보자면 SQL문을 문자열로 해시 함수에 입력하고 함수에서 출력된 해시 값을 SQL문의 ID로 사용합니다.

 

그런데 해시 함수에서는 대문자와 소문자는 다른 문자이므로 쿼리의 대문자와 소문자가 다르면 해시 값도 달라져 다른 SQL문으로 판단합니다.

 

SELECT A FROM B WHERE C=1;
select A from B where C=1;

위의 경우 의미상으론 같은 SQL문이지만, 오라클은 대문자와 소문자를 구분하기 때문에 다른 SQL문이라 판단합니다.

 

 

 

바인드 변수

최대한 공유 풀에 있는 실행 계획을 재사용하는 것이 성능상 좋기때문에 사용하는것이 바인드 변수입니다.

다른것은 같고 검색 조건의 값만 다른 SQL문은 바인드 변수를 사용하면 오라클은 같은 SQL문으로 판단하여 공유 풀을 사용할 수 있습니다.

 

 

값을 이용해서 SQL문을 실행하는 경우

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

위처럼 검색 조건인 id=1과 id=2 인 부분만 다르기 때문에 실행 계획이 같아도 상관없지만, 오라클은 다른 SQL로 인식하기 때문에 매번 분석 작업을 수행해줘야 합니다.

 

 

 

바인드 변수를 활용하는 경우

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

:A라는 바인드 변수를 활용하여 A에 1or2 값을 넣은 후에 SQL문을 실행하면 위의 값을 이용한 SQL문과 같은 작업을 수행하지만 오라클은 같은 SQL이라고 인식하기 때문에 이전에 사용했던 실행계획을 사용합니다.

그렇기 때문에 성능을 향상시키기 위해서는 최대한 바인드 변수를 사용하는 것이 좋습니다.

 

 

 

참고자료

• <그림으로 공부하는 오라클 구조> http://www.kyobobook.co.kr/product/detailViewKor.laf?mallGb=KOR&ejkGb=KOR&barcode=9791185890302 

그림으로 공부하는 오라클 구조 - 교보문고