꿈꾸는 개발자, DBA 커뮤니티 구루비

오라클에서 제공하는 Parallel 메커니즘은 대량의 데이터를 처리하는 데 효과적인 대안이다. Parallel 메커니즘은 시스템의 CPU를 과다하게 사용해 빠른 속도로 요청된 작업을 완료하게 된다.

Parallel 작업은 많은 시간이 소요되는 배치 처리 작업에 적당하며 실시간성 OLTP 업무에는 적당하지 않다. CPU의 과다 사용으로 동시간대의 다른 온라인 작업에 성능적인 영향을 미칠 수 있기 때문이다.

하지만 개발자와 DBA가 알게 모르게 시스템은 간단하고 심플한 작업에 Parallel 메커니즘을 사용하게된다. 그 사례를 세 가지 예를 들어 설명하겠다.

첫 번째 사례는 테이블에 Degree[or Parallel] 설정이 Parallel 작업을 수행하게 설정되어 있는 경우다.

특정 SQL이 /*+ Parallel n */과 같은 Parallel Hint를 명시하지 않아도 Parallel로 작업을 수행한다면 첫 번째로 이를 의심해 봐야 한다.

간단한 테스트를 해보자.

SQL> ALTER TABLE employees PARALLEL 16;


SQL> SELECT table_name, degree 
       FROM user_tables
      WHERE table_name = 'EMPLOYEES';


SQL> SELECT * FROM employees;
------------------------------------------
| Id | Operation            | Name      |
------------------------------------------
| 0 | SELECT STATEMENT      |           |
| 1 | PX COORDINATOR        |           |
| 2 | PX SEND QC (RANDOM)   | :TQ10000  |
| 3 | PX BLOCK ITERATOR     |           |
| 4 | TABLE ACCESS FULL     | EMPLOYEES |

위에서 보았듯이 PARALLEL HINT를 명시하지 않아도 테이블 설정에 의해 PARALLEL로 SQL이 수행되는 것을 알 수 있다.

두 번째 사례는 인덱스에 Degree[or Parallel] 설정이 Parallel 작업을 수행하게 설정되어 있는 경우다.

하지만 인덱스 Parallel 설정은 조금 특별하다. 인덱스의 Execute Plan이 반드시 Index Fast Full Scan으로 수행되어야 Parallel로 작업을 수행하게 된다.

그 외의 인덱스 스캔 시 Parallel로 수행되지 않기때문에 사용자는 이를 간과할 수 있다. 필자가 가장 많이 경험한 Parallel 문제는 바로 이 Index Parallel 부분이다.

대량의 데이터 마이그레이션 작업 시 인덱스를 생성할 때 빠른 작업을 위해 Parallel 옵션으로 인덱스를 생성하게 된다. 작업이 완료된 후에는 이 옵션을 원 상태로 설정해야 하지만, Parallel 옵션 상태로 방치하는 경우가 많다.

후에 시스템 성능 문제가 생겨 AWR 또는 Statspack에 의한 Report를 생성해 보면 Top 5 Event에 항상 PX Deq.. 관련 Parallel Wait Event가 감지되는 것을 확인할 수 있었다. 이 또한 테스트를 수행해 보자.

SQL> ALTER INDEX emp_emp_id_pk PARALLEL 16;
인덱스가 변경되었습니다.



SQL> SELECT table_name,index_name,degree
       FROM user_indexes 
      WHERE index_name = 'EMP_EMP_ID_PK';

 TABLE_NAME   INDEX_NAME       DEGREE
------------- ---------------- -------
EMPLOYEES     EMP_EMP_ID_PK    16



SQL> SELECT /*+ INDEX_FFS(E) */ COUNT(*) FROM employees e;
-----------------------------------------------------------
| Id | Operation            | Name          | Rows  |
-----------------------------------------------------------
| 0 | SELECT STATEMENT      |               | 1     |
| 1 | SORT AGGREGATE        |               | 1     |
| 2 | PX COORDINATOR        |               |       |
| 3 | PX SEND QC (RANDOM)   | :TQ10000      | 1     |
| 4 | SORT AGGREGATE        |               | 1     |
| 5 | PX BLOCK ITERATOR     |               | 107   |
| 6 | INDEX FAST FULL SCAN  | EMP_EMP_ID_PK | 107   |

위에서 보았듯이 PARALLEL HINT를 명시하지 않았음에도 불구하고 PARALLEL로 SQL이 수행되는 것을 확인할 수 있다.

위 SQL은 필자가 INDEX FAST FULL SCAN을 수행하기 위해 HINT를 명시했다. 하지만 대량의 데이터를 운용 중인 시스템에서는 HINT를 명시하지 않아도 옵티마이저의 판단에 의해 많은 SQL이 INDEX FAST FULL SCAN으로 실행될 수 있다.

세 번째 경우는 테이블 또는 인덱스 설정이 아닌 오라클 시스템 초기화 파라미터 설정에 의한 PARALLEL 수행으로 조금 특별한 경우이고, 테이블이나 인덱스의 DEGREE 설정이 DEFAULT로 설정되어 있을 때 이러한 문제가 발생한다.

설마 시스템 설정으로 이런 일이 일어날까라고 생각할 수도 있지만, 이러한 설정 때문에 시스템이 힘들어 하는 경우도 분명히 있고 필자는 이를 경험해 보았다. 자세히 한번 알아보도록 하자.

오라클 초기화 파라미터 중에 PARALLEL_MIN_SERVERS 항목이 있다.

이는 최소 PARALLEL 프로세스 수를 설정하는 항목으로 기본 값은 0이다. 하지만 잘못된 설정으로 이 값이 0 또는 1 이상의 값으로 설정되는 경우가 있다.

이때 특정 테이블 또는 인덱스의 Parallel 옵션이 DEFAULT로 설정되어 있다면, 해당 테이블 및 인덱스는 PARALLEL_MIN_SERVERS의 설정값에 영향을 받아 해당 테이블을 질의하는 SQL은 PARALLEL 로 작업을 수행하게 된다.

이 또한 간단한 테스트를 통해 증명해보자.

SQL> CONN /AS SYSDBA
연결되었습니다.


SQL> ALTER SYSTEM SET PARALLEL_MIN_SERVERS = 16;


SQL> SHOW PARAMETER PARALLEL_MIN_SERVERS
NAME TYPE VALUE
------------------------------------
parallel_min_servers integer 16



SQL> CONN HR/HR
연결되었습니다.



SQL> ALTER TABLE EMPLOYEES PARALLEL;
테이블이 변경되었습니다.



SQL> SELECT TABLE_NAME,DEGREE FROM USER_TABLES WHERE TABLE_NAME = 'EMPLOYEES';
TABLE_NAME DEGREE
------------------------------ --------------------
EMPLOYEES DEFAULT



SQL> SELECT * FROM EMPLOYEES;
--------------------------------------------------
| Id | Operation            | Name      | Rows  |
--------------------------------------------------
| 0 | SELECT STATEMENT      |           | 107   |
| 1 | PX COORDINATOR        |           |       |
| 2 | PX SEND QC (RANDOM)   | :TQ10000  | 107   |
| 3 | PX BLOCK ITERATOR     |           | 107   |
| 4 | TABLE ACCESS FULL     | EMPLOYEES | 107   |

위와 같이 테이블 또는 인덱스 Parallel 설정 값이 Default로 설정되어 있어도 오라클은 Parallel로 SQL을 수행할 수 있고, 대부분 관리자가 인지하지도 못한 사이 오라클에서 수행하게 된다.

또한 오라클이 Parallel 작업을 남용하게 되면, 이 또한 시스템에 큰 부하를 주게 된다. 그리고 이런 Parallel 메커니즘의 부하는 데이터베이스 관리자도 모르는 사이에 발생하고 있다.

지금당장 여러분이 관리하는 데이터베이스가 Parallel로 SQL을 수행하는지 확인해 보길 바란다. 그리고 시스템 설정, 테이블 설정, 인덱스 설정도 확인해 보길 바란다.