9. 친절한 SQL 튜닝

DB의 메모리 구조

SGA vs PGA

1.SGA 란?

• System Global Area의 약자로써, 공유 메모리 영역을 의미한다. SGA에 캐시된 데이터는 여러 프로세스가 공유할 수 있지만 동시에 액세스할 수는 없다. 동시에 액세스하려는 프로세스 간 액세스를 직렬화하기 위한 Lock 매커니즘으로서 래치(Latch)가 존재한다.

2. PGA란?

• Private Global Area의 약자로써, 각 오라클 서버 프로세스에 할당된 메모리 영역을 PGA라고 부르며, 프로세스에 종속적인 고유 데이터를 저장하는 용도로 사용한다. (PGA 공간이 작아 데이터를 모두 저장할 수 없는 경우에는 Temp 테이블스페이스를 이용한다.)

소트머지 조인의 단계

• 소트 단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬한다.
• 머지 단계 : 정렬한 양쪽 집합을 서로 머지(Merge)한다.

기본적으로 소트 머지 조인은 use_merge(Alias) 힌트를 사용한다.

   SELECT /*+ ordered use_merge(c) */
           e.사원번호
          ,e.사원명
          ,e.입사일자
          ,c.고객번호
          ,c.고객명
     FROM 사원 e
         ,고객 c
     WHERE c.관리사원번호 = e.사원번호
       AND e.입사일자 >= '19960101'

 

위 SQL 수행 과정을 풀어서 설명하면 아래와 같다.

• WHERE 조건절에 해당하는 사원 데이터를 읽어 조인컬럼인 사원번호 순으로 정렬한다.
• 정렬한 결과집합은 PGA 영역에 할당된 Sort Area에 저장한다.
• 고객 데이터 또한 WHERE 조건절에 해당하는 고객 데이터를 읽어 조인조건인 관리사원번호 순으로 정렬하여 PGA에 저장한다.
• PGA에 저장한 사원 데이터를 스캔하면서 PGA에 저장한 고객데이터와 조인한다.

위 과정 중, PGA에 담는 과정이 소트 단계, PGA에 저장돼 있는 데이터를 조인하는 과정이 머지단계이다.

Sort Area에 저장한 데이터 자체가 인덱스 역할을 하므로 소트 머지 조인은 조인 컬럼에 인덱스가 없어도 사용할 수 있는 조인 방식이다. 조인 컬럼에 인덱스가 있어도 NL조인은 대량 데이터 조인할 때 불리하므로 소트 머지 조인을 사용할 수 있다.

NL조인 VS 소트머지 조인

NL조인은 보통 모든 DBMS가 공통으로 제공하는 가장 전통적인 조인방식이다. 그렇다면, 대량 데이터를 처리할 때, 왜 NL조인이 더욱 비효율적일까???

• 결론부터 얘기하자면 버퍼캐시에서 찾지 못한 블록을 '건건이' 디스크에서 읽어 들인다.
• 반면 소트 머지 조인은 양쪽 테이블로부터 조인 대상 집합을 '일괄적으로' 읽어 PGA에 저장한 후 조인한다.
• 이것이 바로 소트 머지 조인이 대량 데이터 조인에 유리한 이유다.
  ※ 물론 소트 머지 조인도 DB 버퍼 캐시를 경유한다. 이 과정에 있어서 버퍼캐시 탐색 비용과 랜덤 액세스 부하는 소트 머지 조인도 피할 수 없다.