병렬 처리 튜닝

병렬 처리 튜닝

개발자를 위한 오라클 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 인덱스를 병렬 인덱스 스캔하도록 한다.

  • 인덱스 스캔을 병렬로 함으로써 성능이 향상된다.