SGA vs PGA

SGA는 여러 프로세스가 공유할 수 있지만 동시에 액세스 안된다. 그래서 프로세스간 액세스를 직렬화 하려고 Lock매커니즘인 래치가 존재한다. SGA에 있는 DB 버퍼캐시의 블록을 읽으려면 버퍼 Lock도 얻어야 한다.

PGA

오라클 서버 프로세스는 SGA에 공유된 데이터를 읽고 쓰면서 자신만의 공유 메모리 영역을 가지는데 그걸 PGA(Process/Program/Private Global Area) 라고 함.

할당 받은 PGA 공간이 작아 데이터를 저장할 수 없으면 Temp 테이블 스페이스를 이용한다.

PGA는 독립 메모리 공간이라 래치 매커니즘이 필요없다. 그래서 같은 양의 데이터를 읽더라도 SGA 버퍼캐시보다 훨 빠르다.

기본 메커니즘

소트 머지 조인은 두 단계로 진행한다

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

소트머지 조인은 use_merge 힌트로 유도한다.

select /*+ ordered use_merge(c) */
	e.사원번호, e.사원병, e.입사일자, 
	c.고객번호, c.고객명, c.전화번호, c.최종주문금액
from 사원 e, 고객 c
where c.관리사원번호 = e.사원번호
	and e.입사일자 >= '19960101'
	and e.부서코드 = 'Z123'
	and c.최종주문금액 >= 20000

1. 아래 조건에 해당하는 사원 데이터를 읽어 사원 번호 순으로 정렬.

   1. 정렬한 결과 집합은 PGA영역에 할당된 Sort Area에 저장. 공간 없으면 Temp 테이블 스페이스 사용함.

   select e.사원번호, e.사원병, e.입사일자
   from 사원 e
   where e.입사일자 >= '19960101'
   	and e.부서코드 = 'Z123'
   order by e.사원번호

2. 아래 조건에 해당하는 고객 데이터를 읽어 조인 컬럼인 관리사원번호 순으로 정렬

   1. 정렬한 결과 집합은 PGA영역에 할당된 Sort Area에 저장. 공간 없으면 Temp 테이블 스페이스 사용함.

   select c.고객번호, c.고객명, c.전화번호, c.최종주문금액
   from 고객 c
   where c.최종주문금액 >= 20000
   order by c.관리사원번호

3. PGA에 저장한 사원데이터를 스캔하면서 PGA에 저장한 고객 데이터 조인.

1, 2 단계는 소트단계 3은 머지 단계이다. 3번 머지 단계는 NL 조인과 다르지 않다.

소트머지 조인은 Sort Area에 저장한 데이터 자체가 인덱스 역할을 해서, 조인 컬럼에 인덱스가 없어도 사용할 수 있는 조인 방식이다.

조인컬럼에 인덱스가 있어도 NL 조인이 대량 데이터에 불리하므로 소트 머지 조인을 사용할 수 있다.

소트 머지 조인이 빠른이유

NL조인은 대량의 데이터를 조인할때 성능이 좋지 않아 소트 머지조인과 해시 조인이 개발되었다.

소트 머지 조인은 Sort Area에 미리 정렬해둔 자료구조를 사용하는 것 외 NL조인과 같다.

하지만 소트머지 조인은 양쪽 테이블로부터 조인 대상 집합을 일괄적으로 읽어 PGA에 저장후 조인한다. SGA처럼 래치 획득 과정이 없어 대량 데이터 조인에 유리하다.

소트 머지 조인도 양쪽 테이블로부터 조인 대상 집합을 읽을 때 DB 버퍼캐시를 경유하는데, 그때 인덱스를 사용한다. 그 과정에 생기는 “버퍼캐시 탐색 비용”, “랜덤 엑세스 부하”는 소트 머지 조인도 피할 수 없다.

소트 머지 조인의 주용도

소트 머지 조인은 아래와 같은 상황에서 주로 사용한다.

• 조인 조건식이 등치조건이 아닌 대량 데이터 조인
• 조인 조건식이 아예 없는 조인

소트 머지 조인 제어하기

소트머지 조인은 use_merge 힌트로 유도한다.

소트 머지 조인 특징 요약

• 실시간으로 인덱스를 생성하는 조인이다.
• NL조인과 같은 방식이지만 PGA 영역을 사용해서 소트 부하만 감수하면 NL보다 빠르다.
• NL조인은 인덱스 유무에 크게 영향받으나, 소트 머지 조인은 영향받지 않는다.
• 양쪽 집합을 개별적으로 읽고 조인을 시작한다.
• 조인컬럼에 인덱스가 없는 상황에서 두 테이블을을 각각 읽어 조인 대상 집합을 줄일 수 있을때 유리하다.
• 스캔 위주 액세스 방식을 사용한다.
• 양쪽 소스 집합으로부터 조인 대상 레코드를 찾을때는 인덱스를 이용할 수도 있다.