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 인덱스를 병렬 인덱스 스캔하도록 한다.
