<오라클 성능 고도화 원리와 해법2> Ch07-05 병렬 처리에 관한 기타 상식

Posted Jun 2, 2024

By Mijong Go 30 min read

오라클 성능 고도화 원리와 해법2 - Ch07-05 병렬 처리에 관한 기타 상식

(1) Direct Path Read

일반적인 블록 읽기는 DB 버퍼 캐시를 경유한다. 즉, 읽고자 하는 블록을 먼저 버퍼 캐시에서 찾아보고, 찾지 못할 때만 디스크에서 읽는다. 디스크에서 읽을 때도 바로 읽지 않고 버퍼 캐시에 적재한 후에 읽는다. 그런데 버퍼 캐시 히트율이 낮은 대용량 데이터를 건건이 버퍼 캐시를 거쳐서 읽는다면 오히려 성능이 나빠지게 마련이다.

오라클은 그래서 병렬 방식으로 Full Scan 할 때는 버퍼 캐시를 거치지 않고 곧바로 PGA 영역으로 읽어들이는 Direct Path Read 방식을 사용한다. 병렬도를 2로 주면 쿼리 수행 속도가 2배보다 훨씬 더 향상되는 이유가 바로 여기에 있다.

Direct Path Read 과정에서 읽기 호출이 완료될 때까지 대기가 발생하는데, 모니터링해보면 direct path read 이벤트로 측정된다.

자주 사용되고 버퍼 캐시에 충분히 적재될 만큼의 중소형 테이블을 병렬 쿼리로 읽을 때는 오히려 성능이 나빠지는 경우가 있다. 버퍼 경합이 없는 한 디스크 I/O가 메모리 I/O보다 빠를 수 없기 때문이다. 게다가 Direct Path Read를 하려면 메모리와 디스크 간 동기화를 위한 체크포인트를 먼저 수행해야 한다. 따라서 병렬 쿼리의 Direct Path Read 효과를 극대화하려면 그만큼 테이블이 아주 커야 한다.

(2) 병렬 DML

병렬 처리가 가능해지려면 쿼리, DML, DDL을 수행하기 전에 각각 아래와 같은 명령을 먼저 수행해 주어야 한다.

  
alter session enable parallel query;
alter session enable parallel dml;
alter session enable parallel ddl;

이와 관련해 각 세션의 상태를 v$session을 통해 확인할 수 있다.

  
select pq_status, pdml_status, pddl_status from v$session;

다행히 parallel query와 parallel ddl은 기본적으로 활성화되어 있으므로 사용자가 의도적으로 비활성화하지 않는 한 신경 쓸 필요가 없다. 하지만, parallel dml은 사용자가 명시적으로 활성화해 주어야 하는데, 이 사실을 몰라 병렬 DML을 효과적으로 활용하지 못하는 개발팀을 여러 번 보았다.

오라클 9iR1까지는 한 세그먼트를 두 개 이상 프로세스가 동시에 갱신할 수 없었다. 따라서 파티션되지 않은 테이블이라면 병렬로 갱신(update, merge, delete)할 수 없었고, 파티션 테이블일 때는 병렬도를 파티션 개수 이하로만 지정할 수 있었다. 즉, 오라클 9iR1까지 병렬 DML은 파티션 기반 Granule이었다.

오라클 9iR2부터 병렬 DML이 블록 기반 Granule로 바뀌었다. 매뉴얼에는 비파티션에 대한 병렬 DML이 여전히 불가능하다고 되어 있지만 9iR2 New Features를 보면 주요 개선사항 중 하나로 기술되어 있다.

주의할 점은, 병렬 DML을 수행할 때 Exclusive 모드 테이블 Lock이 걸린다는 사실이다. 성능은 비교할 수 없을 정도로 빨라지겠지만 해당 테이블에 다른 트랜잭션이 DML을 수행하지 못하게 되므로 트랜잭션이 빈번한 주간에 이 옵션을 사용하는 것은 절대 금물이다.

(3) 병렬 인덱스 스캔

Index Fast Full Scan이 아닌 한 인덱스는 기본적으로 병렬로 스캔할 수 없다. 그림 7-12처럼 파티션된 인덱스일 때는 병렬 스캔이 가능하며, 파티션 기반 Granule이므로 당연히 병렬도는 파티션 개수 이하로만 지정할 수 있다.

(4) 병렬 NL 조인

병렬 조인은 항상 Table Full Scan을 이용한 해시 조인 또는 소트 머지 조인으로 처리된다고 생각하기 쉽다. 하지만 아래와 같이 인덱스 스캔을 기반으로 한 병렬 NL 조인도 가능하다.

  
CREATE TABLE emp
PARTITION BY RANGE (sal) (
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (2000),
    PARTITION p3 VALUES LESS THAN (3000),
    PARTITION p4 VALUES LESS THAN (MAXVALUE)
) AS SELECT * FROM scott.emp;

CREATE INDEX emp_sal_idx ON emp(sal) LOCAL;

CREATE TABLE dept AS SELECT * FROM scott.dept;

ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY (deptno);

SET AUTOTRACE TRACEONLY EXPLAIN;

SELECT /*+ ordered use_nl(d) full(e) parallel(e 2) */ *
FROM emp e, dept d
WHERE d.deptno = e.deptno
AND e.sal >= 1000;

Id	Operation	Name	Pstart	Pstop	TQ	IN-OUT
0	SELECT STATEMENT
1	PX COORDINATOR
2	PX SEND QC (RANDOM)	:TQ10000			Q1, 00	P->S
3	NESTED LOOPS				Q1, 00	PCWP
4	PX BLOCK ITERATOR		2	4	Q1, 00	PCWC
5	TABLE ACCESS FULL	EMP	2	4	Q1, 00	PCWP
6	TABLE ACCESS BY INDEX ROWID	DEPT			Q1, 00	PCWP
7	INDEX UNIQUE SCAN	DEPT_PK			Q1, 00	PCWP

위 실행 계획을 그림으로 표시하면 그림 7-13과 같다.

Parallel Full Scan에는 블록 기반 Granule이 사용되므로 병렬도는 파티션 개수와 무관하다. 위에서는 병렬도를 2로 지정했지만 테이블 크기에 따라 자유롭게 더 큰 값을 지정할 수 있다. 물론 데이터량에 비해 병렬도를 너무 크게 지정하면 아무 일도 하지 않고 노는 프로세스가 생길 수 있다.

병렬 인덱스 스캔으로 드라이빙하는 경우

emp 테이블에 생성해 둔 emp_sal_idx 인덱스를 먼저 드라이빙해 병렬 NL 조인을 수행하는 경우를 살펴보자.

  
SELECT /*+ ordered use_nl(d) index(e emp_sal_idx)
			parallel_index(e emp_sal_idx 3) */ *
FROM emp e, dept d
WHERE d.deptno = e.deptno
AND e.sal >= 1000;

Id	Operation	Name	Pstart	Pstop	TQ	IN-OUT
0	SELECT STATEMENT
1	PX COORDINATOR
2	PX SEND QC (RANDOM)	:TQ10000			Q1, 00	P->S
3	NESTED LOOPS				Q1, 00	PCWP
4	PX PARTITION RANGE ITERATOR		2	4	Q1, 00	PCWC
5	TABLE ACCESS BY LOCAL INDEX ROWID	EMP	2	4	Q1, 00	PCWP
6	INDEX RANGE SCAN	EMP_SAL_IDX	2	4	Q1, 00	PCWP
7	TABLE ACCESS BY INDEX ROWID	DEPT			Q1, 00	PCWP
8	INDEX UNIQUE SCAN	DEPT_PK			Q1, 00	PCWP

위 실행 계획을 그림으로 표시하면 그림 7-14와 같다.

그림 7-14와 같은 방식으로 병렬 NL 조인을 수행하려면 드라이빙 인덱스(emp_sal_idx)가 반드시 파티션 인덱스여야 한다. 드라이빙 테이블(emp)과 두 번째 인덱스(dept) 및 테이블(dept)의 파티션 여부와는 상관없다.

인덱스를 드라이빙한 병렬 NL 조인에는 파티션 기반 Granule이 사용되므로 병렬도가 파티션 개수를 초과할 수 없다. 여기서는 세 개의 파티션(Pstart=1, Pstop=3)만 액세스하므로 병렬도를 3보다 크게 줄 수 없다. 크게 주더라도 실제 세 개 프로세스만 사용된다. 만약 병렬도를 2로 지정한다면 각각 하나씩 처리하다가 먼저 일을 마친 프로세스가 나머지 하나를 더 처리한다.

병렬 NL 조인의 효용성

지금 본 것처럼 NL 조인을 병렬로 수행하는 것도 가능하지만 실무적으로 활용할 기회가 많지는 않다. 그럼, 어떨 때 유용하게 쓸 수 있을까? 다음과 같은 상황을 가정해보자.

Outer 테이블과 Inner 테이블이 둘 다 초대용량 테이블이다. (어느 한쪽이 작은 테이블이면 병렬 해시 조인으로 해결 가능하다.)
Outer 테이블에 사용된 특정 조건의 선택도가 매우 낮은데 그 컬럼에 대한 인덱스가 없다.
Inner 쪽 조인 컬럼에는 인덱스가 있다.
수행 빈도가 낮다. (수행 빈도가 높다면 Outer 쪽에도 인덱스를 만드는 편이 낫다.)

두 개의 초대용량 테이블을 소트머지나 해시 방식으로 조인하려면 아주 많은 리소스가 필요해 부담스러운데다, 조인 결과 집합까지 소량일 때는 큰 비효율이 아닐 수 없다. (Full Partition Wise 조인이 가능한 상황이라면 조금 낫다.)

사실, Outer 테이블을 스캔하면서 조건 필터링을 한 결과 건수가 매우 소량(낮은 선택도)이라면 원칙적으로 인덱스를 생성하는 것이 최적의 해법이다. 하지만 수행 빈도가 낮은 쿼리를 위해 대용량 테이블에 인덱스를 하나 더 두는 것은 고민스러운 문제이다.

이럴 때 병렬 NL 조인이 아주 제격인데, 아래 쿼리를 보자.

  
SELECT /*+ ordered use_nl(o) full(t) parallel(t 4) index(o 현물호가_PK) */
    t.체결번호, t.종목코드, t.체결가격, t.체결수량, t.체결유형코드,
    o.회원번호, o.지점번호, o.계좌번호, o.호가수량, o.호가가격, o.호가접수번호
FROM 현물체결 t, 현물호가 o
WHERE o.종목코드 = t.종목코드
    AND o.호가일자 = t.체결일자
    AND o.호가접수번호 = t.매도호가접수번호
    AND o.정규시간외구분코드 = t.정규시간외구분코드
    AND o.대량매매구분코드 = t.대량매매구분코드
    AND o.매도매수구분코드 = '1'
    AND t.체결일자 = '20080603'
    AND t.체결수량 >= 500000;

Id	Operation	Name	Pstart	Pstop	TQ	IN-OUT
0	SELECT STATEMENT
1	PX COORDINATOR
2	PX SEND QC (RANDOM)	:TQ10000			Q1, 00	P->S
3	NESTED LOOPS				Q1, 00	PCWP
4	PX BLOCK ITERATOR		128	128	Q1, 00	PCWC
5	TABLE ACCESS FULL	현물체결	128	128	Q1, 00	PCWP
6	PARTITION RANGE SINGLE		128	128	Q1, 00	PCWP
7	TABLE ACCESS BY INDEX ROWID	현물호가	128	128	Q1, 00	PCWP
8	INDEX UNIQUE SCAN	현물호가_PK	128	128	Q1, 00	PCWP

이럴 때 병렬 NL 조인으로 유도하면 효과적이다. 체결 테이블에 [체결일자+체결수량]으로 구성된 인덱스가 없다고 가정하였으므로, 5건을 선택하려고 250만 건의 현물체결 테이블을 Parallel Full Scan하는 비효율은 피할 수 없다. 하지만 현물호가 테이블은 600만 건을 다 읽지 않아도 된다.

(5) 병렬 쿼리와 스칼라 서브쿼리

따라서 user_dump_dest 디렉토리에 생성된 트레이스 파일을 분석해보면 전체 병렬 쿼리 수행 과정 중 QC가 처리한 오퍼레이션이 어떤 것인지 식별해낼 수 있다.

QC의 SQL 트레이스에 스칼라 서브쿼리 수행 통계가 나타나지 않았으므로 병렬 서버 프로세스들이 스칼라 서브쿼리를 수행했음을 추측할 수 있다. 즉, 병렬 서버들이 order by를 위한 정렬 처리를 하면서 동시에 스칼라 서브쿼리를 수행하고 Sort Area(또는 Temp 테이블스페이스)에 중간 결과 집합을 담는다.

병렬 쿼리는 대부분 Full Table Scan으로 처리되는데, 도중에 이처럼 인덱스를 경유한 Random 액세스 위주의 스칼라 서브쿼리까지 수행해야 한다면 수행 속도가 크게 저하된다. 따라서 병렬 쿼리에서는 스칼라 서브쿼리를 가급적 일반 조인문으로 변환하고서 Full Scan + Parallel 방식으로 처리되도록 하는 것이 매우 중요한 튜닝 기법 중 하나이다.

만약 병렬 쿼리 결과 집합 전체를 Fetch하지 않고 중간에 멈추는 상황, 즉 부분 범위 처리가 가능한 상황이라면 불필요한 스칼라 서브쿼리 수행을 최소화하는 것만으로도 쿼리 응답 속도를 크게 향상시킬 수 있다.

이처럼 스칼라 서브쿼리를 기술하는 위치에 따라 QC가 수행하기도 하고 병렬 서버가 수행하기도 하며, 이는 병렬 쿼리 수행 속도에 지대한 영향을 미친다. 병렬 처리 효과를 높이려면 부분 범위 처리, 전체 범위 처리 여부에 따라 스칼라 서브쿼리 위치를 옮기거나 아예 일반 조인문으로 바꾸는 등의 튜닝을 통해 큰 효과를 얻을 수 있다.

(6) 병렬 쿼리와 사용자 정의 함수

세션 변수를 참조하지 않는다면 이 키워드를 지정하든 안 하든 병렬 수행이 가능하다. 위의 getDname 함수는 세션 변수를 사용하지 않았으므로 parallel_enable 선언은 사실상 불필요하다. 세션 변수를 참조하는 함수일 때는 parallel_enable을 선언하느냐에 따라 함수의 병렬 수행 여부가 결정된다.

parallel_enable 키워드 역할

parallel_enable 키워드가 어떤 역할을 하는지 살펴보자.

SQL 수행 결과는 병렬로 수행했는지 여부와 상관없이 항상 일관된 상태여야 한다. 그런데 함수가 패키지 변수 같은 세션 변수를 참조한다면 병렬 수행 여부에 따라 결과가 달라질 수 있다. 원인은 병렬 쿼리 시 각 병렬 서버가 개별적인 세션 ID를 부여받고 실행된다는 데 있다.

패키지 변수는 세션 레벨에서만 유효하다는 특징을 갖는다. 즉, 세션이 수립될 때 초기화되어 같은 세션 내에서는 지속적으로 참조할 수 있지만 다른 세션과는 값을 공유하지 못한다. 따라서 패키지 변수를 가진 함수를 한 세션이 직렬로 호출할 때와 여러 세션이 병렬로 호출할 때의 결과는 다를 수 있다.

다시 말하지만, SQL 수행 결과는 병렬로 수행하는지 여부와 상관없이 항상 일관된 결과를 반환해야 한다. 그런데 패키지 변수 같은 세션 변수를 참조하는 함수는 병렬로 실행했을 때 일관성이 보장되지 않기 때문에 오라클은 기본적으로 병렬 수행을 거부한다.

그럼에도 사용자가 병렬 수행을 원할 때 사용하는 키워드가 parallel_enable이다. 1권 5장 8절, 그리고 부록 3에서 자세히 설명한 Deterministic 키워드의 의미를 되새겨보기 바란다.

Deterministic 함수로 선언했다고 해서 오라클이 함수의 일관성을 보장하는 것이 아니라, 함수가 일관되지 않은 값을 반환하더라도 함수를 구현한 개발자의 책임임을 선언하는 것이라고 설명한 것을 기억하는가?

parallel_enable도 같은 역할을 한다. 즉, 직렬로 수행할 때와 비교해 함수 결과가 달라질 수 있음에도 사용자가 parallel_enable 키워드를 선언하면 오라클은 사용자 지시에 따라 함수를 병렬로 실행할 수 있도록 허용한다. 하지만 결과에 대한 책임은 사용자의 몫이다.

이런 원리를 잘 숙지하고 parallel_enable을 불필요하게 남용하지 않도록 주의하기 바란다. 다시 말하지만, 세션 변수를 참조하지 않은 함수라면 굳이 이 키워드를 사용하지 않더라도 병렬 수행이 가능하다.

(7) 병렬 쿼리와 ROWNUM

SQL에 rownum을 포함하면 쿼리문을 병렬로 실행하는 데 제약을 받게 되므로 주의해야 한다.

마찬가지로 병렬 DML 문장에도 rownum을 사용하는 순간 병렬 처리에 제약을 받게 되므로 주의가 필요하다.

(8) 병렬 처리 시 주의사항

어느 책에서 보고 메모해 둔 아래 문장을 소개한다.

"Parallelism has potential to paralyze your system."

실제로 병렬 쿼리를 과도하게 사용하면 시스템을 마비시킬 수 있다. 적절한 사용 기준이 필요하다는 얘기인데, 그럼 언제 병렬 처리 기법을 사용하는 것이 바람직한가?

동시 사용자가 적은 애플리케이션 환경(야간 배치 프로그램, DW, OLAP 등)에서 직렬로 처리할 때보다 성능 개선 효과가 확실할 때 (이 기준에 따르면 작은 테이블은 병렬 처리 대상에서 제외됨)
OLTP성 시스템 환경이더라도 작업을 빨리 완료함으로써 직렬로 처리할 때보다 오히려 전체적인 시스템 리소스(CPU, 메모리 등) 사용률을 감소시킬 수 있을 때 (수행 빈도가 높지 않음을 전제로)

야간 배치 프로그램에는 병렬 처리가 자주 사용되기 마련인데, 야간 배치 프로그램은 전체 목표 시간을 달성하는 것을 목표로 해야지 개별 프로그램의 수행 속도를 단축하려고 필요 이상의 병렬도를 지정해선 안 된다. 업무적으로 10분 이내 수행이 목표인 프로그램을 5분으로 단축하려고 병렬 처리 기법을 남용하지 말라는 뜻이다.

야간이라도 여러 팀에서 작성한 배치 프로그램이 동시에 수행되는 상황에서 특정 소수 배치 작업이 과도한 병렬 처리를 시도한다면 CPU, 메모리, 디스크 등 자원에 대한 경합 때문에 오히려 전체 배치 수행 시간이 늘어날 수 있음을 기억하자. 그리고 병렬도를 높인다고 성능이 선형적으로 좋아지는 것도 아니다. 결론적으로, 성능 개선 효과가 확실한 최소한의 병렬도를 지정하려는 노력이 필요하다.

물론 시스템 리소스를 최대한 사용해야 할 때도 있는데, 데이터 이행(migration)이 대표적이다. 이때는 모든 애플리케이션을 중지시키고 이행 프로그램이 시스템을 독점적으로 사용하기 때문에 가능한 모든 리소스를 활용해 이행 시간을 최대한 단축하는 것을 목표로 삼는 것이 당연하다.

병렬 쿼리와 관련해 기타 주의해야 할 사항들이 많지만 생각나는 대로 몇 가지를 적어보면 아래와 같다.

workarea_size_policy를 manual로 설정한다면, 사용자가 지정한 sort_area_size가 모든 병렬 서버에 적용된다. 따라서 sort_area_size를 크게(최대 2GB) 설정한 상태에서 지나치게 큰 병렬도를 지정하면 OS 레벨에서 페이징(paging)이 발생하고 심할 경우 시스템을 마비시킬 수 있다.
병렬도를 지정하지 않으면 CPU 개수 x parallel_threads_per_cpu만큼의 병렬 프로세스가 할당된다. adaptive_multiuser 기능을 사용하려는 경우가 아니라면 반드시 병렬도를 지정하자.
실행 계획에 PX가 나타날 때는 지정한 병렬도의 2배수만큼 병렬 프로세스가 필요하다는 사실을 기억하자.
쿼리 블록마다 병렬도를 다르게 지정한 경우, 여러 가지 우선순위와 규칙에 따라 최종 병렬도가 결정된다. 하지만 이런 규칙들을 외우려는 노력보다는 쿼리 작성 시 병렬도를 모두 같게 지정하는 것이 바람직하다.
parallel 힌트를 사용할 때는 반드시 full 힌트도 함께 사용하는 습관이 필요하다. 옵티마이저에 의해 인덱스 스캔이 선택될 경우 parallel 힌트가 무시되기 때문이다. parallel_index 힌트를 사용할 때는 반드시 index 또는 index_ffs 힌트를 함께 사용하는 습관이 필요하다. 옵티마이저에 의해 Full Table Scan이 선택될 경우 parallel_index 힌트가 무시되기 때문이다.
병렬 DML 수행 시 Exclusive 모드 테이블 Lock이 걸리므로 업무 트랜잭션이 발생하는 주간에는 삼가야 한다.
테이블이나 인덱스를 빠르게 생성하려고 parallel 옵션을 사용했다면 작업을 완료하자마자 noparallel로 돌려놓는 것을 잊지 말자.
부분 범위 처리 방식으로 조회하면서 병렬 쿼리를 사용한 때에는 필요한 만큼 데이터를 Fetch하고 나서 곧바로 커서를 닫아주어야 한다.

Toad나 Orange처럼 부분 범위 처리를 지원하는 쿼리 툴에서는 EOF(End of Fetch)에 도달하기 전까지 커서를 오픈한 채로 유지하기 때문에 오라클은 병렬 서버들을 해제하지 못하고 대기 상태에 머물도록 한다. 이는 불필요한 리소스를 낭비하는 결과를 초래하므로 조회가 끝나자마자 SELECT * FROM DUAL 같은 문장을 수행해 병렬 쿼리의 커서를 닫아주어야 한다.

Database, SQL Tuning

SQLP

This post is licensed under CC BY 4.0 by the author.