PGA 아키텍처 (by dolphhong) [2013.01.18]
PGA 아키텍쳐
- PGA ( Program Global Area, Process Global Area )
: 서버 프로세스 마다 각각 부여되어 독점으로 사용하는 메모리 공간.
- PGA영역
| 변수 저장 영역 | SQL문장에서 바인드 변수를 사용할 경우, 바인드 변수 값 저장 영역 | 세션 정보 | 서버 프로세스에 의해 추출 결과 값을 유저 프로세스로 전달을 위해 유저 프로세스의 세션 정보 저장 영역 | 커서 상태 정보 | 각 프로세스가 사용하는 SQL 파스 정보 저장되어있는 공유 풀의 물리적 위치 주소 저장영역 | SQL작업공간 | SQL이 요청한 작업 수행 영역 |
전용 서버와 공유 서버
- 전용 서버 (Dedicate Server) : 유저 프로세스 : 서버 프로세스 = 1 : 1
- 공유 서버 (MTS : Multi Thread Server, Shared Server) : 유저 프로세스 : 서버 프로세스 = N : 1
- 여러 개의 유저 프로세스가 하나의 서버 프로세스를 공유 하므로, 직접통신은 하지않음
- 유저 프로세스 (SGA의 요청 큐) -> 디스패쳐 프로세스(Dispatcher Process) -> 서버 프로세스 로 전달
- 서버 프로세스 (SGA의 응답 큐) -> 디스패쳐 프로세스(Dispatcher Process) -> 유저 프로세스로 전달
- PGA영역 중 SQL작업 공간에 가장 많은 공간을 할당
- 정렬 작업 이 주로 사용되어 정렬 공간(Sort Area)라고 함
정렬 작업 관련 쿼리
- ORDER BY, GROUP BY, ROLLUP 절 사용
- DISTINCT, UNION, MINUS, INSERSECT 구문 사용
- 분석 함수 사용
- 인덱스 생성
- 비트맵 인덱스 사용 시 비트맵 연산 수행
- 소트 머지 조인 , 해시 조인 수행
- 통계정보 생성
- *_AREA_SIZE 파라미터로 지정
- WORKAREA_SIZE_POLICY, PGA_AGGREGATE_TARGET 파라미터 사용
PGA관련 초기화 파라미터
- BITMAP_MERGE_AREA_SIZE : 비트맵 인덱스 사용 시 비트맵 연산을 위해 사용하는 최대 메모리 크기를 지정한다. 바이트 단위로 지정하며, 기본 값은 1MB.
- * CREATE_BITMAP_AREA_SIZE* : 비트맵 인덱스 생성 시 사용하는 최대 메모리 크기 지정. 바이트 단위로 지정하며 기본값은 8MB.
- HASH_AREA_SIZE : 해시 조인 수행 시 사용하는 최대 메모리 크기를 바이트 단위로 지정.
- PGA_AGGREGATE_TARGET : 오라클에서 PGA영역을 자동으로 관리하는 경우, 전체 세션이 사용할 수 있는 PGA의 총량 지정. 10MB ~ 4,095MB 까지 지정 가능. 기본 값은 SGA의 20% 이나 20MB 중 큰 값을 사용.
- SORT_AREA_SIZE : 정렬 작업 수행 시 사용할 수 있는 최대 메모리 크기를 바이트 단위로 지정. 정렬작업이 완료되면 SORT_AREA_RETAINED_SIZE 에서 지정한 크기를 제외하고 정렬 작업에 사용한 메모리 반환함.
- SORT_AREA_RETAINED_SIZE : 정렬 작업 완료 후, 결과 값을 유저 프로세스로 전달하는 패치 작업이 수행 완료될 때까지 유지할 메모리의 크기를 바이트 단위로 지정. 패치 작업이 완료되면 사용한 메모리 반환. 기본 값은 0 이며, 0으로 지정된 경우 패치 작업이 완료되기 전 까지 SORT_AREA_SIZE에 지정한 크기를 유지.
- WORKAREA_SIZE_POLICY : PGA 관리를 오라클에서 자동으로 관리, 관련 파라미터로 관리할지 지정하는 파라미터.
- AUTO : 자동으로 PGA_AGGREGATE_TARGET 에서 지정된 크기 이내에서 세션별 PGA사용 영역 분배 하여 관리. (Default)
- MANUAL : 사용자가 *_AREA_SIZE 파라미터 값을 직접 지정하여 각 세션에서 사용할 PGA 크기 지정
- SQL 작업 영역 사용 방식
- SQL작업 메모리 부족 시 Temp tablespace 에 위치한 임시 세그먼트를 사용 (디스크I/O발생)
- 인덱스 생성 시 PGA의 SQL 작업공간 사용 예제
- 인덱스 생성 명령 실행, 지정한 테이블 스페이스에 임시 세그먼트 생성.
- 고객 테이블을 읽으면서 순서대로 PGA의 SQL작업 공간에 고객번호 정렬(sort)
- 할당받은 SQL공간을 2단계에서 모두 사용하여, 임시 테이블스페이스에 위치한 임시 세그먼트로 내려 쓰면서 작업 공간 확보,
새로운 데이터를 읽어서 메모리 정렬
- 2,3단계를 반복하며 테이블을 끝까지 읽어, 임시 테이블스페이스의 임시 세그먼트에 저장.
SQL 작업 공간 단위의 정렬만 수행되었음. (아직 전체 데이터에 대한 정렬 발생하지 않은 상태)
- 테이블의 데이터를 모두 읽어서, 임시 테이블스페이스의 임시 세그먼트에 정렬하였다면 소트 머지를 수행하여 전체 데이터를 정렬
- 임시 테이블스페이스에 전체 정렬 완료된 임시 세그먼트는 1단계에 생성된 테이블 스페이스의 임시 세그먼트로 복사.
이후 고객 인덱스 (고객_I1) 이름을 부여받는다.
- 고객_I1 인덱스로 데이터 복사 완료 후 임시 테이블스페이스의 임시 세그먼트는 초기화 된다.
- 임시 세그먼트 사용 횟수에 따라 작업 공간 사용 유형 분류
- 최적 크기 처리 : SQL이 요청한 작업을 작업 공간 메모리 내에서 처리하는 경우
- 단일 패스 처리 : SQL이 요청한 작업 처리 시 임시 세그먼트를 1회 사용한 경우
- 다중 패스 처리 : SQL이 요청한 작업 처리 시 임시 세그먼트를 1회 이상 사용한 경우
- 작업 공간 메모리 사용 적중률
- 100 * ( 최적 크기 처리 / (최적 크기 처리 + 단일 패스 처리 + 다중 패스 처리)
- SQL작업 공간 적중률이 100%에 가까울수록 개별 SQL이 작업 수행 시 임시 세그먼트 사용으로 별도 디스크 I/O발생하지 않았다는 의미로, 수행 속도 향상.