오라클 병렬처리(Parallel Processing), Parallel DOP (Degree of Parallelism)

Oracle DB 에서 SQL문을 만들면서 빠르게 실행하기 위해서 우리는 튜닝(Tuning)을 합니다. SQL튜닝을 하다하다 마지막으로 시도하는 것이 "병렬처리(Parallel Processing)" 입니다. 마지막으로 시도하는 이유는 이게 시스템 자원을 많이 쓰기 때문입니다. 

1개의 SQL문을 처리하기 위해 1개의 프로세스를 띄워서 일해야 하는데, 빨리 실행하기 위해 8개, 16개, 50개, 100개의 프로세스를 병렬로 동시에 띄워서 내 SQL을 처리합니다. 하지만, 이렇게 병렬로 실행하도록 만들어 놓은 SQL을 한꺼번에 여러명이 동시에 실행해버리면 죽을 수 있습니다. DB가.. 시스템이 뻗을 수 있습니다. 

당연히 병렬처리 기술을 사용할 때는 SQL이 동시에 많이 실행되는 OLTP 환경에서는 자제해야 하고, 큰 SQL을 From time to time 실행하는 DW 환경에서는 많이들 사용하죠

사용하는 방법은 무지 쉽습니다. SQL문에 /*+ parallel(..) */ 힌트만 주면 끝

SQL> select /*+ parallel(8) */ ..
     from EMP;
 

parallel 힌트 사용할때 인자(argument)에다가 뭘 주는지에 따라 여러가지 활용이 있습니다만, 잘 모르는 경우는 아무 생각없이 parallel 이라고만 써도 병렬로 돌아간다

/*+ parallel */

/*+ parallel(10) */

/*+ parallel(EMP, 10) */

위와 같이 병렬프로세스 갯수를 지정하지 않을 수도 있고, 지정할 수도 있고, 테이블을 지정할 수도 있습니다. 단, 위 첫번째 경우처럼 병렬프로세스 갯수를 지정하지 않으면 병렬프로세스가 시스템디폴트 갯수만큼 기동될 수 있습니다. 이게 좀 큽니다. 따라서 왠만하면 병렬프로세스 갯수를 지정하는 것이 좋습니다. 

내가 병렬프로세스 갯수를 위와같이 10 이라고 줬다고 해서 반드시 10개가 뜨는 것은 아닙니다. Oracle 이 시스템 파라메타 설정에 따라 또는 시스템 자원 리소스 여유상황에 따라 자동으로 조정합니다. 따라서, 병렬프로세스가 얼마나 뜨는지 실제로 확인해 보는게 좋습니다. 이걸 확인해보는 가장 좋은 방법은 Trace 를 떠보거나 Realtime SQL Monitoring 을 이용하는 것입니다.

참고) 오라클 Plan, Trace 쉽게 보는 - Realtime SQL Monitoring

Realtime SQL Monitoring 에서 보면 아래와 같이 병렬처리 프로세스들이 어떻게 일했는지 정확히 확인할 수 있습니다.

병렬처리에 대해 설명된 문서들을 찾아보다 보면 자주 접하게되는 용어가 있습니다. QC, Consumer, Producer 라는 용어입니다.

(출처 : Oracle Document)

테이블에서 데이터를 읽는 작업을 하는 병렬프로세서들을 Producer 라고 하고, 이 읽은 데이터를 받아서 Sorting(정렬), DML, DDL, Join 등을 수행하는 작업을 하는 병렬프로세들을 Consumer 라고 합니다. 그리고, 작업된 결과들을 취합(통합)하는 작업을 하는 프로세서가 있는데 이를 QC(Query Coordinator) 라고 합니다.

병렬처리 힌트 쓰는데, 당장 이런걸 꼭 알 필요는 없지만, 병렬처리 관련해서 구글링하다보면 영어문서에는 이런 용어들이 빈번하게 나오기 때문에 잘 알아둘 필요가 있겠습니다.

병렬처리와 관련하여 추가적으로 아래와 같은 개념들을 알면 더 효과적으로 병렬처리를 활용할 수 있습니다.

 

DOP : Degree Of Parallelism

DOP 란 Degree Of Parallelism 의 약어로, 병렬도라고 합니다. 병렬처리할 때 병렬프로세스를 몇개 띄울 것인지를 의미합니다. DOP 가 20 이면 20개의 병렬프로세스를 띄워서 작업한다는 의미입니다.

오라클에서 병렬처리는 DB서버의 자원(CPU, Memory, Disk I/O)을 최대한 사용해서 작업을 빠르게 끝낼 수 있는 아주 유용한 기능입니다.

하지만, 조심해야 할 부분이 하나의 작업에 대해서만 병렬처리를 하면 문제없지만, 동시에 수십개의 병렬처리가 돌아가는 상황이 발생한다면? ㅋㅋ  장애로 이어질 수 있겠죠...이런 일이 종종 발생합니다.

개발자가 어플리케이션의 SQL에 /*+ parallel */ 힌트를 박아놨는데, 이 어플리케이션이 뜻하지 않게 너무 많이 실행되는 경우 ㅎㅎ 난리가 날겁니다. 또, DBA가 테이블을 옮기는 작업을 하면서 테이블을 parallel 옵션을 주어 생성해두었다가 이를 안고친 경우, 이 경우 이 테이블에 Access 하는 SQL에 대해 Parallel 처리를 하게되어, 영문도 모르게 병렬처리가 너무 많이 실행되는 사태가 발생하곤 합니다.

테이블의 경우 아래와 같이 user_tables 딕셔너리에서 degree 값을 조회하면 됩니다.

이 degree 값이 테이블의 DOP(병렬도, Degree Of Parallelism)를 나타냅니다. 지금은 1 로 세팅되어 있어 병렬처리를 하지 않습니다. 

아래와 같이 alter table ~ parallel 커맨드로 테이블의 DOP를 변경할 수 있습니다.

DOP를 4 로 세팅했습니다. user_tables 딕셔너리의 degree 값이 4 로 바껴있는 것을 확인할 수 있습니다.

여기에서는 기존 테이블을 alter table 커맨드로 변경했지만, 일반적으로는 create table .. 커맨드로 처음에 테이블 생성작업할 때 parallel 옵션을 주었다가 이게 계속 남아있는 경우가 많습니다. 이걸 모르고 그대로 놔두면 종종 장애를 경험하는 불상사가 생깁니다. 위에서는 4 로 작은값이 세팅되어 있지만, 20, 32 등 큰 값으로 세팅되어 있는 경우도 종종 봤습니다. 그럼, 세션당 한번에 병렬프로세스가 20개, 32개 씩 뜬다는 얘기입니다. 여러 세션에서 동시에 실행한다면...? 몇백개씩 프로세스가 뜬다고 생각하면 ? 

실제로 병렬처리가 일어나는지 Plan 을 체크해보겠습니다.

set autotrace on  커맨드를 실행해주면, 이후에 실행되는 모든 SQL문에 대해 실행 후, Plan 정보를 함께 보여줍니다. Execution Plan 정보를 보면 PX COORDINATOR 가 나오는 걸로 보아 병렬처리를 했음을 알 수 있습니다. 밑에 Note 섹션에 DOP 4 로 실행되었다고 적혀져 나오네요. 원래 싱글일 때와 비교해보면 확실히 다른 것을 확인할 수 있습니다.

싱글처리할 때는 PX Coordinator 얘기도 없고, Note 섹션이 아예 안나오네요. 싱글처리로 바꾸는 Table 의 degree 를 초기화하는 방법은 위에서처럼 alter table ... parallel 1; 이라고 해도 되고, alter table ... noparallel; 이라고 해도 같은 겁니다.

이번에는 Default DOP 에 대해서 체크해보겠습니다. DOP는 병렬도였죠. Default DOP 는 말그대로 디폴트로 잡히는 병렬도입니다. 아래에서 EMP 테이블에 parallel 옵션을 주어 degree 를 변경했습니다. 이때, parallel 뒤에 DOP 값을 명시하지 않았습니다. 

이렇게 DOP 값을 명시하지 않을 때, Default 로 잡히는 DOP 값을 Default DOP 라고 부릅니다. 그럼, Default DOP 는 어떻게 결정될까요?

• For a single instance, DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT
• For an Oracle RAC configuration, DOP = PARALLEL_THREADS_PER_CPU x sum(CPU_COUNT)

위와 같이 결정됩니다. 

PARALLEL_THREADS_PER_CPU 와 CPU_COUNT 는 DB 파라메타 값입니다. RAC 의 경우는 전체 노드의 CPU_COUNT 값의 합입니다.

저의 경우는 아래와 같이 디폴트로 잡혀있네요.

제 DB의 Default DOP 는 2 x 2 = 4 가 되겠습니다. 

그럼, 위에서 변경한 EMP 테이블의 DOP 가 정말 4로 돌아가는지 확인해보겠습니다.

Default DOP 4 로 동작하는 것을 확인할 수 있습니다.

 

참고문헌 : 

> 오라클 병렬처리 Parallel DOP (Degree of Parallelism)

> 오라클 Parallel 병렬처리 Default DOP (Default Degree of Parallelism)

> 오라클 Parallel DML 처리 방법 및 주의 사항 - enable parallel dml

> 오라클 Parallel DML 주의 사항 (ORA-12838 : cannot read/modify)

> 병렬처리, Parallel Join 에서 /*+ pq_distribute(,,) */ 힌트

> 병렬처리(Parallel Processing)관련 주의 사항 (degree)

> 병렬처리(Parallel Processing)관련 주의 사항 (default degree)

> PL/SQL 함수(Function) 병렬처리 옵션 (parallel_enable)

> 병렬처리 Plan 개선 (PX SELECTOR) - Oracle 12c 신기능

> 병렬처리 Union All 성능향상 (PQ_CONCURRENT_UNION) - Oracle 12c 신기능