DB/sqlp

[친절한 SQL 튜닝] 6장 DML

bonschicken 2023. 8. 24. 00:01
728x90

6.0 DML 성능 영향 요소

  • 기본적인 DML 튜닝을 설명하기 전 DML 성능에 영향을 미치는 요소에 대해 설명합니다.
  • 인덱스
    • 인덱스를 위한 데이터를 생성하거나 삭제하는 작업은 DML 성능에 영향을 줍니다.
    • INSERT, DELETE는 인덱스 조작을 한 번만 수행합니다.
    • UPDATE는 인덱스 조작을 두 번 수행합니다. (DELETE , INSERT)
    • 시스템마다 다르지만
      • 인덱스 1개에 100만 건 데이터를 넣을 때는 5초
      • 인덱스 3개에 100만 건 데이터를 넣을 때는 40초
    • 무결성 제약
      • PK, FK, Check, Not Null 같은 제약도 DML 성능에 영향을 줍니다.
      • 시스템마다 다르지만
        • PK가 없으면 100만건 데이터를 넣을 때는 1.3초
        • PK가 있으면 100만건 데이터를 넣을 때는 4.95초
    • 조건절
      • 조건절을 확인하기 위하여 SELECT를 합니다.
      • 따라서 SELECT 할 때와 동일하게 인덱스를 잘 활용해서 DML을 수행해야 합니다.
    • 서브쿼리
      • 조건절과 마찬가지로 SELECT 할 때와 동일하게 인덱스를 잘 활용해서 DML을 수행해야 합니다.
    • Redo 로깅
      • Redo : 모든 변경 사항을 기록하는 로그입니다.
        1. 물리적으로 디스크가 깨질 경우, 데이터베이스를 복구하기 위해 사용
        2. 정전 등으로 버퍼 캐시가 사라질 경우, 캐시를 복구하기 위해 사용
        3. 커밋은 느리므로 혹시 모를 상황을 대비해 트랜잭션에 의한 변경사항을 저장해놓습니다.
      • DML을 수행할 때마다 로그를 쌓으므로 성능에 영향을 미칩니다.
    • Undo 로깅
      • Undo : 오라클 9i부터 Rollback을 Undo로 표현
      • 변경된 블록을 이전 상태로 되돌리는 데 필요한 정보를 로깅해야 하므로 성능에 영향을 미칩니다.
    • Lock
      • Lock은 DML 성능에 아주 큰 영향을 주는 요소입니다.
      • 길게 잡으면 DML 성능이 안 좋아지고, 짧게 잡으면 데이터 품질이 안 좋아집니다.
    • 커밋
      • Lock과 함계 커밋도 DML 성능에 간접적으로 영향을 미칩니다.
      • 커밋의 내부 메커니즘은 다음과 같습니다.
        1. DML문을 수행하면 버퍼 캐시가 정전 등으로 사라질 수 있으므로 Redo 로깅을 먼저 해놓습니다.
        2. Redo 로그 파일에 기록하기 전에 파일 자체도 I/O이므로 느립니다. 따라서 로그 버퍼에 변경사항을 먼저 기록합니다.
        3. 버퍼 캐시에 변경된 블록을 기록합니다.
        4. 커밋을 하게 되면
        5. LGWR 프로세스가 Redo 로그 버퍼 내용을 Redo 로그 파일에 일괄 저장합니다.
        6. DBWR 프로세스가 버퍼 캐시에 변경된 블록을 데이터 파일에 일괄 저장합니다.

6.1 기본 DML 튜닝

  • DB는 SQL을 수행하면 Parse, Execure, Fetch Call이 발생합니다.
    • Parse Call : 1.1장에서 설명한 파싱과 최적화를 수행하는 단계입니다.
    • Execute Call : 실제로 SQL을 실행하는 단계입니다. DML일 경우 해당 단계에서 마무리 됩니다.
    • Fetch Call : SELECT 문에서 사용자에게 결과를 전송하는 단계입니다. 정송할 데이터가 많다면 여러 번 호출됩니다
  • Call은 발생 위치에 따라 User, Recursive Call로 나뉩니다.
    • User Call : DBMS 외부로부터 인입되는 Call, WAS가 앞단에 있다면 WAS가 DBMS를 호출할 때 발생합니다.
    • Recursive Call : DBMS 내부에서 발생하는 CAll , 함수/프로시저/트리거에 내장된 SQL을 실행할 때 발생합니다.
  • Call은 성능에 영향을 주며, 네트워크를 경유하는 User Call이 성능에 미치는 영향이 큽니다.
    • 100만 건 기준으로 recurisive Call만 발생하는 for문의 insert문 짠 PL/SQL은 30초 걸립니다.
    • User Call(자바로 짠 코드)는 220초 걸립니다.
    • 하나의 SQL인 INSERT INTO select은 한번의 Call만 발생하므로 1.4초 걸립니다. 따라서 ONE SQL을 최대한 사용하는 것이 좋습니다
  • One SQL로 작성하기 힘들 때는 Array Processing을 활용
    • 100만 건 기준으로 Recurisive Call만 발생하는 for문의 insert문을 짠 PL/SQL 4초
    • User Call(자바로 짠 코드)는 12초 for(int i = 0 ; i < length; i++){ … st.addBatch(); }
    • st.executeBatch();​​
  • 인덱스 및 제약 해제를 통한 대량 DML 튜닝
    • 1000만 건의 데이터를 입력하게 되면
      • PK 인덱스 + 일반 인덱스 존재 : 약 1분 19초
      • 인덱스 및 제약사항이 없을 경우 : 약 5.8초
    • 애플리케이션이 실행 중에 자주 발생하는 테이블에 대한 인덱스 및 제약을 잠시 동안 해제하기는 어렵습니다.
    • 대량 데이터를 적재하기 위한 배치 프로그램에서는 이들 기능을 해제함으로써 DML 성능을 크게 높일 수 있습니다.
    • 해당 DML이 테이블의 데이터를 5% 이상 수정할 경우 사용하는 것을 추천합니다. – PK 제약사항 및 PK 인덱스 드랍 ALTER TABLE 테이블 MODIFY CONSTRAINT PK명 DISABLE DROP INDEX;– PK 제약사항 및 PK 인덱스 재생성 ALTER TABLE 테이블 MODIFY CONSTRAINT PK명 ENABLE NOVALIDATE;
    • – 일반 인덱스 활성화 ALTER INDEX 인덱스명 REBUILD;
    • – 일반 인덱스 비활성화 ALTER INDEX 인덱스명 UNUSABLE;
    • 뷰 : 하나 이상의 테이블이나 다른 뷰의 데이터를 볼 수 있게 하는 데이터베이스 객체, 실제 데이터는 뷰를 구성하는 테이블에 담겨 있지만 마치 테이블처럼 사용할 수 있습니다.
    • 조인 뷰 : FROM 절에 두 개 이상의 테이블을 가진 뷰 CREATE OR REPLACE VIEW EMP_DEPT_VIEW AS SELECT E.ROWID AS EMP_RID ,E.* ,D.ROW_ID AS DEPT_RID ,D.DNAME ,D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;
    • 수정 가능 조인 뷰 : 입력, 수정, 삭제가 허용되는 조인 뷰입니다. 키 보존 테이블 설정을 해야만 가능합니다.
    • – 연봉이 1500 이하인 직원의 휴대폰번호를 '1234’로 변경 – 하지만 현 상태에서는 ORA-01779 라는 에러가 발생한다. – ORA-01779는 키-보존 테이블을 알 수 없어 발생하는 에러이다. UPDATE EMP_DEPT_VIEW SET EMP_PHONE = ‘1234’ WHERE SAL <= 1500

6.2 Direct Path I/O 활용

  • 버퍼 캐시의 장점
    • 일반적으로 SQL문을 실행하면 1장에서 설명한 버퍼 캐시를 확인해보고 작업을 진행합니다.
    • 반복적으로 동일한 블록을 찾는 경우에 버퍼 캐시는 성능을 높여주는 아주 좋은 기능입니다.
  • 버퍼 캐시의 단점
    • 버퍼 캐시를 탐색하는 것도 락에 의해서 느릴 수 있습니다.
    • 반복적으로 동일한 블록을 찾을 경우가 없을 경우에은 오히려 버퍼 캐시를 한번 찾아보는 행위는 성능에 나쁜 영향을 미칩니다.
  • 오라클은 버퍼 캐시를 사용할 필요가 없을 경우를 위해 버퍼 캐시를 경우하지 않고 곧바로 데이터 블록을 일고 쓸 수 있는 Direct Path I/O 기능을 제공합니다.
  • 버퍼 캐시가 동작하도록 할 수 있는 방법에는 총 6가지 존재합니다.
  1. Direct Path Insert를 수행할 때
    • 일반적인 Insert와 Direct Path Insert의 순서는 다음과 같습니다.순서일반적인 InsertDirect Path Insert 
      1 FreeList에서 데이터를 입력할 수 있는 블록을 찾음 FreeList를 찾아보지 않고, 맨 뒤에 순차적으로 쌓습니다
      2 FreeList에서 할당받은 블록을 버퍼 캐시에서 찾음 블록을 버퍼 캐시에서 탐색하지 않습니다.
      3 버퍼 캐시에 없으면 데이터 파일에서 읽어 버퍼 캐시에 적재합니다. 버퍼 캐시에 적재하지 않고, 데이터 파일에 직접 기록합니다.
      4 Undo 기록 Undo 기록하지 않음
      5 Redo 기록 Redo를 하지 않도록 할 수 있음
    • 버퍼 캐시를 참조하지 않고, Undo, Redo를 로깅하지 않을 수 있어 매우 빠릅니다.
    • Direct Path Insert를 유도할 수 있는 방법은 네 가지가 존재합니다.
      1. INSERT /*+ append */INTO SELECT … 처럼 append 힌트로 유도한 경우
      2. INSERT /*+ parallel(C 4) */INTO 고객 C 처럼 parallel 힌트로 유도한 경우
      3. CREATE TABLE … AS SELECT 문을 사용할 경우
      4. 데이터를 적재할 수 있는 툴인 SQL*LOADER를 사용할 때 direct 옵션을 true로 준 경우
    • Direct Path Insert를 사용할 때 주의할 점은 두 가지가 존재합니다.
      1. Exclusive 모드 TM Lock이 걸려 다른 트랜잭션은 해당 테이블에 의DML을수행할 수 없습니다. 따라서 해당 테이블에 대해 DML 수행이 없을 때 작업을 진행해야 합니다.
      2. FreeList 참조하면 삭제된 여유공간을 재활용할 수 있지만, Direct Path Insert는 항상 맨 뒤에 Insert를 하므로 사이즈가 줄지 않고 계속 늘어납니다.
  2. 병령 DML을 수행할 때
    • 위에서 설명한 Direct Path Insert를 INSERT에서 사용하는 방법이므로 UPDATE와 DELETE는 사용할 수 없습니다.
    • 다만 병령 DML로 UPDATE와 DELETE를 수행할 경우 Direct Path Insert 방식을 사용할 수 있습니다.
    • 병령 DML을 사용하기 위해서는 아래와 같이 병렬 DML을 활성화해야 합니다. ALTER SESSION SET ENABLE PARALLEL DML;​
    • parallel 힌트를 사용하여 병령 DML을 사용하면 됩니다. UPDATE /*+ full© parallel(c 4) */ 고객 c ㄴㄸㅆ 고객상태 = ‘휴먼’ WHERE 최종거래일시 < ‘20200101’;
    • DELETE /*+ full© parallel(c 4) */ FROM 고객 c WHERE 탈퇴일시 < ‘20200101’;​
    • 병렬 DML을 수행할 경우 Direct PAth Insert 단점과 동일하게 Exclusive 모드 TM Lock이 걸리므로 잘 사용해야 합니다.
  3. parallel 또는 parallel_index 힌트를 사용하여 병렬 쿼리로 Full Scan을 수행할 때
    • 병렬 DML처럼 SELECT를 병렬 쿼리로 Full Scan 할 경우 발생 SELECT /*+ full(t) parallel(C 4) */ * FROM 고객 C;​
  4. Temp 세그먼트 블록들을 읽고 쓸 때
  5. direct 옵션을 true로 활성화하고 export 할 때
  6. nocache 옵션을 지정한 LOB 컬럼을 읽을 때

6.3 파티션을 활요한 DML 튜닝

  • 파티셔닝 : 테이블 또는 인덱스 데이터를 특정 컬럼 값에 따라 별도 세그먼트에 나워서 저장하는 것
    • 관리적 측면 : 파티션 단위 백업, 추가, 삭제, 변경 -> 가용성 향상
    • 성능적 측면 : 파티션 단위 조회 및 DML, 경합 또는 부하분산
  • 파티션에는 Range, 리스트 , 해시 방식이 있습니다.
    • Range 방식 : 값의 범위에 따라 파티셔닝 reate table …
      • partition by range(주문일자) ( partition P2017_Q1 values less than (‘20170401’) , partition P2017_Q2 values less than (‘20170701’) , partition P2017_Q3 values less than (‘20170101’) , partition P2017_Q4 values less than (‘20180101’) , partition P2018_Q1 values less than (‘20180401’) , partition P9999_MX values less than (MAXVALUE) );
    •  
    • hash 방식 : 파티션 기 값을 해시 함수에 입력, 파티션 개수를 사용자가 결정
      • partition by hash(고객ID) partitions 4;​
      • create table …
    • List 방식 : 그룹핑 기준에 따라 데이터 분할 저장하는 방식
      • partition by list(지역분류) ( partition P_지역1 values (‘서울’) , partition P_지역2 values (‘경기’, ‘인천’) , partition P_기타 values (DEFALUT) );​
      • create table …
    • 테이블 파티션 : 테이블을 파티셔닝
      • 비 파티션 테이블 : 파티셔닝 하지 않은 테이블로 일반적인 테이블입니다.
      • 파티션 테이블 : 파티셔닝 한 테이블
    • 인덱스 파티션 : 인덱스를 파티셔닝 , 테이블 파티션과 인덱스 파티션을 혼동하지 마세요
      • 비 파티션 인덱스
        • 파티셔닝 하지 않은 인덱스로 일반적인 인덱스 입니다.
        • 만약 파티션 된 테이블의 파티션 구성을 변경하게 되면 비 파티션 인덱스는 재생성되어야 하며 그동안 서비스가 중단되므로 조심해야 합니다.
        • 아래는 파티션 테이블일 때 비 파티션 인덱스의 예시입니다.
      • 로컬 파티션 인덱스 – 인덱스 생성문 마지막에 LOCAL 옵션을 주면 로컬 인덱스로 생성된다. create index … LOCAL;
        • 테이블 파티션과 1:1로 매핑되는 인덱스 파티션, 즉 테이블 파티션 개수만큼 인덱스 파티션이 존재합니다.
        • 테이블 파티션의 키가 동일하게 로컬 인덱스에 상속됩니다. 따라서 테이블 파티션의 키와 인덱스 파티션의 키는 동일합니다.
          • 테이블 파티션의 키가 '계절’이라면 인덱스 파티션의 키도 "계절"입니다
        • 오라클에서 관리해주므로 테이블 파티션 구성을 변경하더라도 서비스에 영향을 거의 주지 않습니다.
        • 아래는 파티션 테이블일 때 로컬 인덱스의 예시입니다.
    • PREFIXED VS Nonprefixed
      • Prefixed : 인덱스 파티션 키 컬럼이 인덱스 컬럼 구성에서 왼쪽 선두에 위치할 경우
      • Nonprefixed : 인덱스 파티션 키 컬럼이 인덱스 컬럼 구성에서 왼쪽 선두에 위치하지 않거나 인덱스 컬럼에 아예 속하지 않는 경우
      • 로우 Prefixed
        • 테이블 파티션의 키가 '계절’이라면 인덱스 파티션의 키도 ‘계절’ 입니다.
        • 인덱스의 컬럼 구성을 ‘계절’ + '온도’로 한 로컬 파티션 인덱스를 만들었다면, 파티션 키인 '계절’이 인덱스 컬럼에 아예 속하지 않으므로 이를 로컬 Nonprefixed라고 합니다.
      • 로컬 Nonprefixed
        • 테이블 파티션의 키가 "계절"이라면 인덱스 파티션의 키도 "계절"입니다.
        • 인덱스의 컬럼 구성을 "온도"로 한 로컬 파티션 인덱스를 만들었다면, 파티션 키인 "계절"이 인덱스 컬럼에 아예 속하지 않으므로 이를 로컬 Nonprefixed라고 합니다.
      • 글로벌 Prefixed
        • 로컬 Prefixed와 설명 동일
    • Unique 인덱스를 파티셔닝 하려면, 파티션 키(=테이블 파티션의 키 = 인덱스 파티션의 키)가 모두 인덱스 구성 컬럼이어야 합니다. 이는 당연한 제약 사항입니다.
      • 예를 들면 Unique 인덱스가 '주문번호’이고 파티션의 키가 '주문일자’인 로컬 파티션이 존재한다고 가정합니다.
      • 인덱스 파티션의 키가 '주문일자’이므로 테이블 파티션도 '주문일자’를 기준으로 파티셔닝이 되어있어야 합니다.
      • '주문번호’가 1234인 데이터가 들어오게 되면 중복 값이 있는지 확인하기 위해 모든 인덱스의 파티션을 다 확인해야 합니다. 왜냐하면 인덱스 파티션의 키는 ‘주문일자’ 이므로 '주문번호’가 1234인 값이 어디에 있는지 알 수가 없기 때문입니다.
      • 이렇게 되면 데이터를 insert 할 때마다 모든 인덱스 파티션을 다 찾아봐야 한다는 게 말이 안되기 때문에 DBMS 자체에서 당연히 존재하는 제약사항입니다.

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

  • 6.1장에서 설명한 '인덱스 및 제약 해제를 통한 대량 DML 튜닝’은 해당 DML이 테이블의 데이터를 5% 이상 수정할 경우 사용하는 것을 추천하지만 , 테이블의 데이터가 엄청 많다면, 인덱스를 삭제하고 재생성하는 시간도 무시할 수 없으며 약간의 부담이 존재합니다.
  • 만약에 테이블이 파티셔닝이 돼 있고, 인덱스 파티션도 로컬 파티션이라면 ‘인덱스 및 제약 해제를 통한 대량 DML 튜닝’ 보다 더 좋은 방법이 있습니다. 특정 파티션에 대한 임시 세그먼트를 만들어 원본 파티션과 바꿔치기하는 방식입니다.
  • – 1. 임시 테이블을 만든다. nologging 모드로 생성하면 더 빠르다. CREATE TABLE 임시 nologging AS SELECT * FROM 실제테이블 WHERE 1 = 2;
  • – 2. 데이터를 수정하고자 하는 데이터를 insert
  • – append 힌트로 Direct Path Insert 유도하여 빠르게 insert 하면 좋다. INSERT /*+ append */ INTO 임시 SELECT 컬럼1, 컬럼2, 컬럼3, (CASE WHEN 데이터변경컬럼 = ‘1’ THEN ‘2’ ELSE ‘3’ END) 데이터변경컬럼 FROM 실제테이블 WHERE 거래일자 < ‘20210101’; – 특정 테이블 파티션의 조건으로
  • – 3. 임시 테이블에 실제 테이블과 동일하게 인덱스 생성. nologging 모드로 생성하면 더 빠르다. – 인덱스를 나중에 만드는 이유는 6.0에서 설명했듯이 DML 성능에 영향을 주기 때문에 나중에 만든다. CREATE INDEX …
  • – 4. 실제 테이블의 변경하려고 하는 파티션과 임시 테이블을 교체한다.
  • – 5. 임시 테이블 드랍 DROP TABLE 임시;
  • – 6. nologging으로 인덱스를 만들었다면 logging으로 전환한다. 수정하고자 하는 데이터를 insert
  • 파티션을 활용한 대량 DELETE 튜닝
    • 전제조건 : 테이블이 파티셔닝이 돼 있고, 인덱스 파티션도 로컬 파티션
    • 파티션에 할당된 데이터를 모두 삭제할 때 ALTER TABLE 테이블명 DROP PARTITION p201412;​
    • 파티션에 할당된 데이터를 소수만 삭제할 때
    • DELETE FROM 테이블명 WHERE 거래일자 < ‘20210101’ – 파티션 조건 AND 상태 = ‘1’ – 이외 조건
    • 파티션에 할당된 데이터를 대다수 삭제할 때– 2. 삭제 대상 테이블 파티션을 truncate 한다. ALTER TABLE 실제테이블 TRUNCATE PARTITION p202012;​– 4. 임시 테이블 드랍 DROP TABLE 임시;
    • – 3. 임시 테이블의 데이터를 실제 테이블에 Insert 한다. INSERT INTO 실제테이블 SELECT * FROM 임시;
    • – 1. 임시 테이블을 만드는데 삭제하지 않을 데이터만 남긴다. – nologging 모드로 생성하면 더 빠르다. CREATE TABLE 임시 nologging AS SELECT * FROM 실제테이블 WHERE 거래일자 < ‘20210101’ AND 상태 = ‘1’;
    • 파티션을 활용한 대량 INSERT 튜닝
      • 전제조건 : 테이블이 파티셔닝이 돼 있고, 인덱스 파티션도 로컬 파티션
      • '인덱스 및 제약 해제를 통한 대량 DML 튜닝’과 마찬가지로 파티션 단위로 인덱스 및 제약을 해제하면 됩니다. – 파티션의 인덱스 비활성화 ALTER INDEX 인덱스명 MODIFY PARTITION P202012 UNUSABLE;
      • – 파티션의 인덱스 활성화 ALTER INDEX 인덱스명 REBUILD PARTITION P202012;

6.4 Lock과 트랜잭션 동시성 제어(오라클 기준)

  • 오라클은 DML, DDL, 래치, 버퍼 라이브러리 캐시 등 다양한 종류의 Lock을 사용합니다.
  • 이 중 애플리케이션 개발 측면에서는 DML Lock이 중요합니다.
  • DML Lock : 다중 트랜잭션이 동시에 액세스 하는 사용자 데이터의 무결성을 보호하기 위한 Lock
    • DML 로우 Lock : 두 개의 동시 트랜잭션이 같은 로우를 변경하는 것을 방지SelectINSERTUPDATEDELETE
      로우 Lock X 인덱스 존재 시 O O O
    • DML 테이블 Lock (TM Lock) : 테이블 구조를 다른 트랜잭션이 변경하는 것을 방지
      • 테이블 Lock이라고 해서 테이블 전체에 Lock이 걸리는 의미가 아님
      • 테이블 Lock에는 여러 가지 모드가 있고, 어떤 모드를 사용했는지에 따라 후행 트랜잭션이 수행할 수 있는 작업의 범위가 결정됩니다.