[DataBase] 고급 SQL 튜닝 - DML / DB CALL 튜닝

** 데이터 진흥원 - SQL 전문가 가이드의 [과목3-6장 : 고급 SQL 튜닝] 내용

 

(1) Sort 튜닝 (2) DML 튜닝 (3) DB CALL 튜닝 (4) 파티셔닝 (5) 대용량 배치 프로세스 튜닝 (6) 복잡 구문 간결화 - 6개 중 (2/3번)

---

2) DML 튜닝

◼︎ Index 비용 고려하기

> Index가 많을 수록 DML의 성능은 느려진다. 인덱스의 변형은 데이터 레코드 DML보다 더 많은 리소스를 요구한다.

   (Update 연산도 인덱스의 경우 Delete&Insert를 통해 변경되며, 유지를 위해 Undo 레코드도 2개씩 생성된다.)

> 대량의 데이터를 입력/수정/삭제할 때는 인덱스를 Drop하거나 Unusable상태로 변경한 후 수행하는 것도 방법이다.

 

◼︎ Insert 성능 개선방법

Oracle

SQL Server

- Direct Path Insert [배경 설명] DB의 일반적인 힙 테이블들을 IOT(index-organized table)이 아님으로 freelist로부터 할당받은 블록에 무작위로 입력을 진행한다. freelist는 HWM(high-water mark)하의 블록 중, 빈 블록들을 관리하는 자료구조 (지정된 pctfree, pctused에 의해 결정됨) [장점 설명] -> 대량의 Insert인 경우, 하나하나 freelist를 거치면 비효율이 발생 -> HWM 바깥 영역에 버퍼 캐시를 거치지 않고 디스크에 바로 입력 1) insert 문장에 /*+ append */ 옵션 사용 2) 병렬 모드로 insert 3) direct 옵션 지정 후, SQL*Loader(sqlldr) 데이터 로드 4) CTAS 문장 수행 위 4가지 경우, direct path insert가 수행됨. (해당 테이블은 Lock) + [nologging 모드] direct path insert가 수행될 때, redo로그마저도 생략하고 싶다면 alter table t nologging;을 수행한다. -> 장애 발생 시 복구가 되지 않는 제한이 있음으로      즉시 백업을 생성하거나, DW 이전처럼 손상 시에도 문제가 없는     경우 사용한다.

- 최소 로깅 (minimal nologging) 'alter database SQLPRO set recovery SIMPLE' recovery 모드가 SIMPLE 또는 Bulk-logged여야 한다. 1) BULK INSERT문의 WITH절에 TABLOCK을 추가 2) Select into 3) insert into with (TABLOCK) ~ (ver 2008이상부터) 4) 클러스터형 인덱스 테이블에도 수행 가능 --------------------- use SQLPRO go alter database SQLPRO set recovery SIMPLE DBCC TRACEON(610); insert into t_idx select * from t_source order by col1 -> t_idx의 클러스터형 인덱스 키 순 정렬 ---------------------

 

◼︎ Update 성능 개선방법

 

> 대량의 update가 필요한 경우, pk, idx 등의 변경에 의해 update 성능이 상당히 저하될 수 있음으로

   pk, idx를 지우고, 테이블을 truncate한 후에, 위에서 배운 bulk insert를 진행할 때 변경하려는 구문을 적용한다.

 

+ delete도 마찬가지

 

> 조인이 포함된 update 구문 (조인 뷰 사용)

   두 테이블이 키-보존(key-preserved Table : key에 의해 비교되었을 때, 중복 없이 unique하게 식별이 가능한 테이블)관계여야 함.

 

+ Merge문에도 조인 컨셉 적용이 가능하다.

merge into 고객 t using 고객변경분 s on (t.고객번호 = s.고객번호)

when matched then update

   set t.고객번호 = s.고객번호, t.고객명 = s.고객명, t.이메일 = s.이메일,

when not matched then insert

   (고객번호, 고객명, 이메일, 전화번호, 거주지역, 주소, 등록일시) values

   (s.고객번호, s.고객명, s.이메일, s.전화번호, s.거주지역, s.주소, s.등록일시);

 

---

3) DB CALL 튜닝