Direct Path I/O 활용 및 파티션 활용

Direct Path

일반적인 블록 I/O는 DB 버퍼캐시를 경유한다. 시스템의 전반적인 성능을 높이려고 버퍼캐시를 이용하지만, 대량 데이터를 읽고 쓸 때는 건건이 버퍼캐시를 탐색한다며 개별 프로그램 성능에는 오히려 안좋다. 그래서 오라클은 버퍼캐시를 경유하지 않고 곧바로 데이터 블록을 쓸 수 있는 Direct Path I/O 기능을 제공한다. 아래는 기능이 작동하는 경우다.

• 병렬 쿼리로 Full Scan을 수행할 때
• 병렬 DML을 수행하 때
• Direct Path Insert을 수행할 때
• Temp 세그먼트 블록들을 읽고 쓸 때
• direct 옵션을 지정하고 export를 수행할 때
• nocache 옵션을 지정한 LOB 컬럼을 읽을 때

Direct Path Insert

일반적인 Insert가 느린 이유는 다음과 같다

1. 데이터를 입력할 수 있는 블록을 Freelist에서 찾는다.
2. freelist에서 할당받은 블록을 버퍼캐시에서 찾는다.
3. 버퍼캐시에 없으면, 데이터파일에서 읽어 버퍼캐시에 적재한다. 
4. insert 내용을 undo 세그먼트에 기록한다.
5. insert 내용을 redo 로그에 기록한다.

Direct path insert 방식을 사용하면 일반 insert보다 훨씬 더 빠르게 입력할 수 있다.

1. Insert ... SELECT문에 append 힌트사용
2. parallel 힌트를 이용해 병렬모드로 insert
3. direct 옵션을 지정하고 SQL *Loader(sqlldr)로 데이터 적재
4. CTAS(create table ... as select) 문 수행

Direct path insert 방식이 빠른 이유는 다음과 같다.

• Freelist를 참조하지 않고 HWM 바깥 영역에 데이터를 순차적으로 입력한다.
• 블록을 버퍼캐시에서 탐색하지 않는다.
• 버퍼캐시에 적재하지 않고, 데이터파일에 직접 기록한다.
• Undo 로깅을 안한다.
• Redo 로깅을 안 하게 할 수 있다. => "alter table t NOLOGGING"

Direct path insert 사용시 주의할 점

• 이 방식을 사용하면 성능은 비교할 수 없이 빨라지지만 Exclusive 모드 TM lock이 걸린다는 사실이다. 따라서 커밋하기 전까지 다른 트랜잭션은 해당 테이블에 DML을 수행하지 못한다.
• Freelist를 조회하지 않고 HWM 바깥 영역에 입력하므로 테이블에 여유 공간이 있어도 재활용하지 않는다.

 

파티션을 활용한 DML 튜닝

테이블 파티션

파티셔닝(Partitioning)은 테이블 또는 인덱스 데이터를 특정 컬럼값에 따라 별도 세그먼트에 나눠서 저장하는 것을 말한다.

 

파티션이 필요한 이유

• 관리적 측면 : 파티션 단위 백업, 추가, 삭제, 변경 -> 가용성 향상
• 성능적 측면 : 파티션 단위 조회 및 DML, 경합 또는 부하 분산

파티션에는 Range, 해시, 리스트 세종류가 잇따.

1) Range 파티셔닝 (주로 날짜 컬럼을 기준을로 파티셔닝한다.)

# 주문일자 기준으로 분기별 range 파티셔닝방법
create table 주문(주문번호 number, 주문일자 varchar2(8), 고객id varchar2(5)
                , 배송일자 varchar2(8, 주문금액 number, … )
partition by range(주문일자) (
      partition p2017_q1 values less than (‘20170401’)
    , partition p2017_q2 values less than (‘20170701’)
    , partition p2017_q3 values less than (‘20171001’)
    , partition p2017_q4 values less than (‘20180101’)
    , partition p2018_q1 values less than (‘20180401’)
    , partition p9999_mx values less than (maxvalue) --> 주문일자 >= '20180401' );

2) 해시 파티션 (파티션 키 값을 해시 함수에 입력해서 반환받은 값이 같은 데이터를 같은 세그먼트에 저장)

해시 알고리즘 특성상 등치(=)조건 또는 IN-List 조건으로 검색할 때만 파티션 Prunning이 작동한다.

# 해시 파티션은 고객 id처럼 변별력이 좋고 데이터 분포가 고른 컬럼을 파티션 기준으로 선정해야 효과적
create table 고객(
      고객id varchar2(5)
    , 고객명 varchar2(10)
    , … 
)
partition by hash(고객id) partitions 4;

 

3) 리스트 파티션 (사용자가 정의한 그룹핑 기준에 따라 데이터를 분할 저장하는 방식)

range파티션에선 값의 순서에 따라 저장할 파티션이 결정되지만, 리스트 파티션에서는 순서와 상관없이 불연속적인 값의 목록에 의해 결정된다.

create table 인터넷매물(
      물건코드 varchar2(5)
    , 지역분류 varchar2(4)
    , …
)
partition by list(지역분류) (
      partition p_지역1 values (‘서울’)
    , partition p_지역2 values (‘경기’. ‘인천’)
    , partition p_지역3 values (‘부산’, ‘대구’, ‘대전’, ‘광주’)
    , partition p_기타 values (default) -> 기타 지역
);

 

인덱스 파티션

파티션 인덱스는 각 파티션이 커버하는 테이블 파티션 범위 따라 다음과 같이 구분된다.

• 로컬 파티션 인덱스 : 각 테이블 파티션과 인덱스 파티션이 서로 1:1 대응 관계가 되도록 오라클이 자동으로 관리하는 파티션 인덱스를 말한다.
• 글로벌 파티션 인덱스 :  로컬이 아닌 파티션 인덱스는 모두 글로벌 파티션 인덱스다.
• 비파티션 인덱스 : 파티셔닝하지 않은 인덱스다.

#로컬 파티션 인덱스 만드는 법 (뒤에 LOCAL을 붙힌다)
create index 주문_x01 on 주문 (주문일자, 주문금액) LOCAL;

#글로벌 파티션 인덱스 만드는 법 (뒤에 GLOBAL 추가하고, 파티션을 정의한다.)
create index 주문_x03 on 주문 (주문금액, 주문일자) GLOBAL
partition by range(주문금액) (
	partition P_01 values less than (100000)
	partition P_MX values less than (MAXVALUE) --> 주문금액 >= 100000
);

 

파티션을 활용한 대량 UPDATE 튜닝

# 1.임시테이블 생성한다. 할 수 있다면 nologging 모드로 생성
create table 거래_t
nologging 
as
select * from 거래 where 1 = 2;

# 2.거래 데이터를 읽어 임시 테이블에 입력하면서 상태코드 값을 수정한다.
insert /*+ append */ into 거래_t
select 고객번호, 거래일자, 거래순번 ... 
	,(case when 상태코드 <> 'ZZZ' then 'ZZZ' else 상태코드 end) 상태코드
from 거래
where 거래일자 < '20150101';

# 3.임시 테이블에 원본 테이블과 같은 구조로 인덱스를 생성한다. 할 수 있다면 nologging 모드로 생성
create unique index 거래_t_pk on 거래_t (고개번호, 거래일자, 거래순번) nologging;
create index 거래_t_x1 on 거래_t(거래일자, 고객번호) nologging
create index 거래_t_x2 on 거래_t(상태코드, 거래일자) nologging

# 4.임시 테이블과 exchange한다
alter table 거래
exchange partition p201412 with table 거래_t
including indexes without validation;

# 5.임시 테이블을 drop한다.
# 6.파티션을 logging모드로 전환한다.

 

파티션을 활용한 대량 DELETE 튜닝

테이블 삭제 조건절(거래일자 < '20150101') 컬럼 기준으로 파티셔닝돼 있고 인덱스도 다행히 로컬 파티션이라면 아래와 같이 삭제할 수 있다.

ALTER table 거래 drop partition p201412;

#오라클 11g부터는 아래와 같이 할 수 있다.
ALTER table 거래 drop partition for('20141201');

 

파티션을 활용한 대량 INSERT 튜닝

# 1. 작업 대상 테이블 파티션을 nologging 모드로 전환한다
alter table target_t modify partition p_201712 nologging

# 2. 작업대상테이블 파티션과 매칭되는 인덱스 파티션을 Unusable 상태로 전환한다.
alter index target_t_x01 modify partition p_201712 unusable;

# 3. 대량 데이터를 입력한다.
insert /*+ append */ into target_t
select * from source_t where dt between '20171201' and '20171231';

# 4. 인덱스 파티션 재생성
alter index target_t_x01 rebuild partition p_201712 nologging;

# 5. 작업 파티션을 logging모드로 전환
alter table target_t modify partition p_201712 logging;
alter index target_t_x01 modify partition p_201712 logging;

 

 

 

출처: 개발자를 위한 SQL 튜닝 입문서 친절한 SQL 튜닝