병렬 처리 튜닝

개발자를 위한 오라클 SQL 튜닝 내용에서 참고한 내용입니다.

병렬 처리 튜닝

오라클은 다중 스레드 방식을 통한 병렬 처리를 지원한다.
즉, 대용량 테이블에 대한 대량의 연산 작업을 여러 개의 스레드가 동시에 처리하여 연산 시간을 획기적으로 단축할 수 있다.
대부분 개발자가 업무를 진행할 때 단발성 또는 통계성 쿼리를 작성해야 하는 경우가 있다.
이러한 경우에 병렬 처리가 유용하게 사용될 수 있다.
병렬 스캔을 통한 튜닝과 인덱스 스캔을 병렬 처리하여 성능을 극대화하는 튜닝 방법에 대해 알아본다.

병렬과 병렬 처리

병렬이란 한 개의 작업 처리하는데 여러 개의 스레드가 실행되어 작업을 동시에 처리하는 것을 뜻한다.
반대로 직렬이란 한 개의 작업을 처리하는 데 한 개의 스레드가 실행되어 처리하는 것을 뜻한다.
오라클에서 병렬 처리란 대용량의 테이블을 검색하거나 구성하는 데 여러 개의 스레드를 실행하는 것을 말한다.
각각의 스레드가 해당 연산(Operation) 내에서 자신들이 맡은 부분을 처리하며 QC(Query Coordinator)라는 프로세스가 각각의 스레드를 호출한다.
각각의 스레드가 처리한 부분 집합을 QC가 다시 취합하여 최종 결과를 도출해낸다.
병렬 처리 기법을 사용한다면 대용량의 테이블을 검색하는데 극적인 성능 향상 효과를 가져오게 된다.
하지만 병렬 처리 기법은 DBMS의 리소스(Resource)를 많이 사용하므로 온라인 환경의 실시간 조회 쿼리에는 적합하지 않고 배치 프로그램이나 통계성 쿼리에 사용하면 큰 효과를 볼 수 있다.

병렬 스캔 튜닝

병렬 스캔 튜닝

병렬 스캔 튜닝(Parallel Scan Tuning) 이란 대용량의 큰 테이블을 검색할 때 병렬 처리 기법을 이용하여 성능을 향상하는 일련의 모든 과정을 뜻한다.
오라클에서 제공하는 PARALLEL 힌트로 구현할 수 있다.

병렬 스캔 튜닝 관련 힌트

PARALLEL
- SQL 단위로 병렬 처리할 수 있게 해주는 힌트이다.

SELECT /*+ FULL(테이블) PARALLEL(테이블, 병렬도) */

대용량의 테이블을 병렬 스캔으로 검색하기

테이블 생성

CREATE TABLE TB_TRD
(
    TRD_NO VARCHAR2 (15),  -- 거래번호
    TRD_DT VARCHAR2 (8),   -- 거래일자
    TRD_AMT NUMBER (15),   -- 거래금액
    CUST_ID VARCHAR2 (10), -- 고객ID
    PRDT_CD VARCHAR2 (6),  -- 상품코드
    INST_ID VARCHAR2 (50), -- 입력자ID
    INST_DTM DATE          -- 입력일시
);

CREATE TABLE DUAL_1000
(
    DUMMY VARCHAR2 (1)
);

데이터 입력

INSERT INTO DUAL_1000
SELECT DUMMY
FROM DUAL CONNECT BY LEVEL <= 1000;

COMMIT;

ALTER TABLE TB_TRD NOLOGGING;

INSERT /*+ APPEND */ INTO TB_TRD -- APPEND 힌트 사용
SELECT LPAD(TO_CHAR(ROWNUM), 15, '0'),
       TO_CHAR(SYSDATE - TRUNC(DBMS_RANDOM.VALUE(1, 365)), 'YYYYMMDD'),
       TRUNC(DBMS_RANDOM.VALUE(1000, 100000)),
       LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(0, 100000))), 10, '0'),
       LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(0, 10000))), 6, '0'),
       'DBMSEXPERT',
       SYSDATE
FROM DUAL_1000,
     (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 100000);

COMMIT;

제약 조건 생성

ALTER TABLE TB_TRD
    ADD CONSTRAINT TB_TRD_PK
        PRIMARY KEY (TRD_NO);

통계 정보 생성

ANALYZE TABLE TB_TRD COMPUTE STATISTICS
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;

대용량의 테이블을 병렬 스캔 튜닝 전

SELECT *
FROM TB_TRD
WHERE TRD_DT BETWEEN TO_CHAR(SYSDATE - 365, 'YYYYMMDD') AND TO_CHAR(SYSDATE, 'YYYYMMDD')
  AND CUST_ID = '0000000001';

SQL 분석
- TRD_DT에 범위 조건을 준다.
- CUST_ID에 '=' 조건을 준다.
SQL 문제점
- TB_TRD 테이블에 조건절에 대한 인덱스가 존재하지 않으며 향후 인덱스를 생성할 계획도 없다.
- 그로 인해 대용량 테이블을 테이블 풀 스캔해야 하는 부하가 존재한다.

대용량의 테이블을 병렬 스캔 튜닝 후

SELECT /*+ FULL(TB_TRD) PARALLEL(TB_TRD 16) */ *
FROM TB_TRD
WHERE TRD_DT BETWEEN TO_CHAR(SYSDATE - 365, 'YYYYMMDD') AND TO_CHAR(SYSDATE, 'YYYYMMDD')
  AND CUST_ID = '0000000001';

SQL 분석
- PARALLEL 힌트를 사용하여 TB_TRD 테이블을 병렬도 16으로 병렬 처리

병렬 인덱스 스캔 튜닝

병렬 인덱스 스캔

대용량의 파티셔닝 테이블에 파티션 인덱스를 생성 후 해당 인덱스를 병렬 스캔(Parallel Scan)할 수 있다.
이것을 병렬 인덱스 스캔(Parallel Index Scan) 이라 한다.
해당 파티션 인덱스를 읽을 때 여러 개의 스레드가 실행 되어 병렬 처리로 스캔할 수 있는데, 이때의 병렬도는 파티션의 개수를 넘을 수 없다.
주의사항
- 파티션 테이블을 병렬 처리 스캔 시 병렬도는 테이블의 파티션 개수를 넘을 수 없다.
- 파티션 인덱스를 병렬 처리 스캔 시 병렬도는 인덱스의 파티션 개수를 넘을 수 없다.
16개의 파티션으로 나누어진 테이블 또는 인덱스를 병렬 스캔 시 병렬도는 최대 16개까지 가능하다.
또한, 파티션 컬럼의 조건이 들어가서 파티션 프루닝되어 16개의 파티션 중 4개의 파티션만 읽게 되는 경우 병렬도는 최대 4개까지 가능하다.

병렬 인덱스 스캔 튜닝

병렬 인덱스 스캔 튜닝(Parallel Index Scan Tuning)은 파티션 인덱스를 병렬 처리로 스캔하여 인덱스를 읽는 시간을 단축하는 기법

병렬 인덱스 스캔 튜닝 관련 힌트

PARALLEL_INDEX
- 파티션 인덱스에 대한 인덱스 범위 스캔을 병렬로 처리할 수 있게 한다.

SELECT /*+ INDEX(EMP EMP_IDX01) PARALLEL_INDEX(EMP, EMP_IDX01, 4) */
    *
FROM EMP
WHERE DEPTNO >= 10;

인덱스 병렬 스캔을 유도하여 성능 극대화하기

테이블 생성

CREATE TABLE TB_TRD
(
    TRD_NO VARCHAR2 (15),  -- 거래번호
    TRD_DT VARCHAR2 (8),   -- 거래일자
    TRD_AMT NUMBER (15),   -- 거래금액
    CUST_ID VARCHAR2 (10), -- 고객ID
    PRDT_CD VARCHAR2 (6),  -- 상품코드
    INST_ID VARCHAR2 (50), -- 입력자ID
    INST_DTM DATE          -- 입력일시
)
    PARTITION BY RANGE (TRD_DT)
        (
        PARTITION P_TB_TRD_202009 VALUES LESS THAN ('20201001'),
        PARTITION P_TB_TRD_202010 VALUES LESS THAN ('20201101'),
        PARTITION P_TB_TRD_202011 VALUES LESS THAN ('20201201'),
        PARTITION P_TB_TRD_202012 VALUES LESS THAN ('20200101'),
        PARTITION P_TB_TRD_202101 VALUES LESS THAN ('20210201'),
        PARTITION P_TB_TRD_202102 VALUES LESS THAN ('20210301'),
        PARTITION P_TB_TRD_202103 VALUES LESS THAN ('20210401'),
        PARTITION P_TB_TRD_202104 VALUES LESS THAN ('20210501'),
        PARTITION P_TB_TRD_202105 VALUES LESS THAN ('20210601'),
        PARTITION P_TB_TRD_202106 VALUES LESS THAN ('20210701'),
        PARTITION P_TB_TRD_202107 VALUES LESS THAN ('20210801'),
        PARTITION P_TB_TRD_202108 VALUES LESS THAN ('20210901'),
        PARTITION P_TB_TRD_202109 VALUES LESS THAN ('20211001'),
        PARTITION P_TB_TRD_MAX VALUES LESS THAN (MAXVALUE)
        );

CREATE TABLE DUAL_100
(
    DUMMY VARCHAR2 (1)
);

더미 데이터 생성

INSERT INTO DUAL_100
SELECT DUMMY
FROM DUAL CONNECT BY LEVEL <= 100;

COMMIT;

ALTER TABLE TB_TRD NOLOGGING;

INSERT /*+ APPEND */ INTO TB_TRD -- APPEND 힌트 사용
SELECT LPAD(TO_CHAR(ROWNUM), 15, '0'),
       TO_CHAR(SYSDATE - TRUNC(DBMS_RANDOM.VALUE(1, 365)), 'YYYYMMDD'),
       TRUNC(DBMS_RANDOM.VALUE(1000, 100000)),
       LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(0, 100000))), 10, '0'),
       LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(0, 10000))), 6, '0'),
       'DBMSEXPERT',
       SYSDATE
FROM DUAL_100,
     (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 100000);

COMMIT;

제약 조건 생성

ALTER TABLE TB_TRD
    ADD CONSTRAINT TB_TRD_PK
        PRIMARY KEY (TRD_NO);

통계 정보 생성

ANALYZE TABLE TB_TRD COMPUTE STATISTICS
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;

인덱스 병렬 스캔 튜닝 전

SELECT *
FROM TB_TRD
WHERE TRD_DT BETWEEN TO_CHAR(SYSDATE - 365, 'YYYYMMDD') AND TO_CHAR(SYSDATE, 'YYYYMMDD')
  AND CUST_ID = '0000000001';

SQL 분석
- TRD_DT에 범위 조건을 준다.
- CUST_ID에 '=' 조건을 준다.
SQL의 문제점
- TB_TRD 테이블에 인덱스가 존재하지 않습니다.
- 이로 인해 대용량 테이블을 테이블 풀 스캔해야 하는 부하가 존재한다.

인덱스 병렬 스캔 튜닝 후

파티션 인덱스 구성
- 파티션 테이블의 파티션 컬럼을 인덱스 선두에 두지 않는 로컬 논 프리픽스 파티션 인덱스

CREATE INDEX TB_TRD_IDX01 ON TB_TRD (CUST_ID, TRD_DT, TRD_AMT) 
    LOCAL;
ANALYZE
INDEX TB_TRD_IDX01 COMPUTE STATISTICS;

SELECT /*+ INDEX(TB_TRD, TB_TRD_IDX01) 
         PARALLEL_INDEX(TB_TRD, TB_TRD_IDX01, 4 ) */
    *
FROM TB_TRD
WHERE TRD_DT BETWEEN TO_CHAR(SYSDATE - 365, 'YYYYMMDD') AND TO_CHAR(SYSDATE, 'YYYYMMDD')
  AND CUST_ID = '0000000001';

SQL 분석
- PARALLEL_INDEX 힌트를 이용하여 TB_TRD_IDX01 인덱스를 병렬 인덱스 스캔하도록 한다.
- 인덱스 스캔을 병렬로 함으로써 성능이 향상된다.

Previous파티션 인덱스 튜닝 NextDesign Pattern

Last updated 4 years ago

Was this helpful?