[SQL 튜닝 실습 2-4] 해시 조인 튜닝 1

해시 조언으로 성능 극대화하기

 

실습 준비

테이블 생성 : TB_PRDT

CREATE TABLE TB_PRDT			
(			
    PRDT_CD VARCHAR2(6), --상품코드			
    PRDT_NM VARCHAR2(50), --상품명			
    REL_DT VARCHAR2(8), --출시일자			
    COST_AMT NUMBER(15), --원가금액			
    INST_DTM DATE, --입력시간			
    INST_ID VARCHAR2(50), --입력자ID			
    UPDT_DTM DATE, --수정시간			
    UPDT_ID VARCHAR2(5) --수정자ID			
);			

 

테이블 생성 : TB_ORD

CREATE TABLE TB_ORD			
(			
    ORD_NO VARCHAR2(10), --주문번호			
    ORD_DT VARCHAR2(8), --주문일자			
    ORD_NM VARCHAR2(150), --주문이름			
    ORD_AMT NUMBER(15), --주문금액			
    PRDT_CD VARCHAR2(6), --상품코드			
    SALE_GB VARCHAR2(2), --판매구분			
    PAY_GB VARCHAR2(2), --결제구분			
    CUST_ID VARCHAR2(10), --고객ID			
    INST_DTM DATE, --입력시간			
    INST_ID VARCHAR2(50), --입력자ID			
    UPDT_DTM DATE, --수정시간			
    UPDT_ID VARCHAR2(5) --수정자ID			
);			

 

데이터 입력 (10만 건) : TB_PRDT 테이블

INSERT INTO TB_PRDT	
SELECT	
LPAD(TO_CHAR(ROWNUM), 6, '0'),	
DBMS_RANDOM.STRING('U', 50),	
TO_CHAR(SYSDATE - TRUNC(DBMS_RANDOM.VALUE(1, 3650)), 'YYYYMMDD'),	
TRUNC(DBMS_RANDOM.VALUE(100, 100000)),	
SYSDATE,	
'KSKY2',	
NULL,	
NULL	
FROM DUAL CONNECT BY LEVEL <= 100000;	
	
COMMIT;	

 

테이블 생성 : DUAL_10

CREATE TABLE DUAL_10			
(			
    DUMMY VARCHAR2(1)			
)			
;			
INSERT INTO DUAL_10			
SELECT DUMMY 			
FROM DUAL CONNECT BY LEVEL <= 10;			
			
COMMIT;			

 

NOLOGGING 모드 설정 : TB_ORD 테이블

ALTER TABLE TB_ORD NOLOGGING;

 

데이터 입력 (500만 건) : TB_ORD 테이블 > 20분 정도 걸림!

INSERT /*+ APPEND */  INTO TB_ORD --APPEND 힌트 사용							
SELECT							
    LPAD(TO_CHAR(ROWNUM), 10, '0'),							
    TO_CHAR(SYSDATE-TRUNC(DBMS_RANDOM.VALUE(1, 3650)), 'YYYYMMDD'),	
    DBMS_RANDOM.STRING('U', 150),							
    TRUNC(DBMS_RANDOM.VALUE(1000, 100000)),							
    LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 100000))), 6, '0'),	
    LPAD(TO_CHAR(MOD(TRUNC(DBMS_RANDOM.VALUE(1, 1000)), 3)), 2, '0'),
    LPAD(TO_CHAR(MOD(TRUNC(DBMS_RANDOM.VALUE(1, 1000)), 10)), 2, '0'),
    LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 10000))), 10, '0'),
    SYSDATE,							
'KSKY2',							
    NULL,							
    NULL							
FROM DUAL_10, ( SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 500000);	
							
COMMIT;							

 

기본키 생성

ALTER TABLE TB_PRDT		
ADD CONSTRAINT TB_PRDT_PK		
PRIMARY KEY (PRDT_CD);		
		
ALTER TABLE TB_ORD		
ADD CONSTRAINT TB_ORD_PK		
PRIMARY KEY (ORD_NO);		

 

외래키 생성

ALTER TABLE TB_ORD
ADD CONSTRAINT TB_ORD_FK
FOREIGN KEY (PRDT_CD) REFERENCES TB_PRDT(PRDT_CD);

* 한 개의 제품은 0개 또는 1개 이상의 주문을 가질 수 있다.

* 하나의 주문은 반드시 한 개의 제품을 갖는다

 

인덱스 구성 : TB_ORD(ORD_DT)

CREATE INDEX TB_ORD_IDX01 ON TB_ORD(ORD_DT);

 

통계정보 생성 : 테이블과 인덱스에 대한 통계정보 생성

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

 

---

튜닝 전 상황

 

튜닝 전 SQL문

1	SELECT /*+ LEADING(A) INDEX(A TB_ORD_IDX01) USE_NL(B) */
2	A.ORD_NO, A.ORD_DT, B.PRDT_CD, B.PRDT_NM						
3	FROM TB_ORD A,						
4	    TB_PRDT B						
5	WHERE A.ORD_DT > TO_CHAR(SYSDATE-365, 'YYYYMMDD')	
6	AND A.PRDT_CD = B.PRDT_CD;						

소요 시간 : 0.008

 

[1]

오라클의 LEADING 힌트를 이용해서 TB_ORD 테이블을 Outer 테이블로 지정 
Outer 테이블 스캔 시 TB_ORD_IDX01 인덱스 이용
또한 USE_NL 힌트를 이용해서 TB_PRDT 테이블을 
Inner 테이블로 지정해서 중첩 루프 조인이 되도록 한다.

 

SQL의 문제점

* 대용량의 테이블인 TB_ORD 테이블이 Outer 테이블로 지정되서 매우 큰 성능 부하가 예상됨
* ORD_DT 컬럼 조건만으로는 대량의 행이 나오기 때문에 그에 따른 테이블 랜덤 액세스 부하가 발생

수행 순서 : 4 > 3 > 5 > 2 > 6 > 1 > 0

4 : TB_ORD_IDX01 을 인덱스 범위 스캔한다

3 : TB_ORD_IDX01의 ROWID를 이용해서 TB_ORD테이블을 테이블 랜덤 액세스한다

5 : TB_ORD 테이블의 PRDT_CD 컬럼 값을 이용해서 TB_PRDT_PK 인덱스를 인덱스 유일 스캔 한다.

2 : 3번과 5번 연산을 중첩 루프 조인 한다.

6 : TB_PRDT_PK에서 나온 ROWID를 이용해서 TB_PRDT 테이블은 테이블 랜덤 액세스 한다

1 : 2번과 6번의 연산을 중첩 루프 조인한다

0 : SELECT절의 연산 수행

 

---

튜닝

튜닝 후 SQL문

1	SELECT /*+ LEADING(B) FULL(B) USE_HASH(A)  */					
2	A.ORD_NO, A.ORD_DT, B.PRDT_CD, B.PRDT_NM					
3	FROM TB_ORD A,					
4	    TB_PRDT B					
5	WHERE A.ORD_DT > TO_CHAR(SYSDATE-365, 'YYYYMMDD')					
6	AND A.PRDT_CD = B.PRDT_CD;					

수행시간 : 0.2초

 

[1]

LEADING 힌트를 이용해서 작은 집합인 TB_PRDT 테이블을 Build Input으로 지정
또한, FULL 힌트를 이용해서 Build Input을 테이블 풀 스캔으로 유도
마지막으로 USE_HASH 힌트를 이용해서 
TB_ORD테이블을 Probe Input으로 지정하고 해시 조인 수행

 

실행 계획

수행 순서 : 2 > 3 > 1 > 0

2 : TB_PRDT 테이블을 테이블 풀 스캔한다

3 : TB_ORD 테이블을 테이블 풀 스캔한다

1 : TB_PRDT 테이블을 Build Input, TB_ORD 테이블을 Probe Input으로 해서 해시조인 수행

0 : TB_PRDT 테이블을 Build Input, TB_ORD 테이블을 Probe Input으로 해서 해시 조인 수행