🔑 SQL 튜닝 - CH.6 DML튜닝

DML 성능에 영향을 주는 다른 요소와  튜닝방법들을 모아 따로 설명하자 !

1️⃣ 기본 DML 튜닝

1 - 1 DML 성능에 미치는 요소

• 인덱스
• 무결성 제약
• 조건절 where
• 서브쿼리
• Redo 로깅
• Undo 로깅
• Lock
• 커밋

인덱스와 DML 성능

테이블에 레코드 ( 행 )를 입력하면 인덱스에도 입력을 해야한다

테이블은 Freelist를 통해 입력할 블록을 할당 받지만
인덱스는 정렬된 자료구조 이므로 수직적 탐생을 통해 입력할 블록을 찾아야 한다.

Insert, Update, Delete 중 Update의 부담이 제일 크다. ( Update = Delete + Insert )

▶ Freelist : 테이블마다 데이터 입력이 가능한 (여유 공간 있는) 블록 목록을 관리하는데 이것을 ' Freelist '

---

1 - 2 무결성 제약과 DML 성능

DB에 논리적으로 의미있는 자료만 저장되게 하는 데이터 무결성 규칙

• 개체 무결성 = PK
• 참조 무결성 = FK
• 도메인 무결성 = Check
• 사용자 정의 무결성 

PK 와 FK는 중복체크를 해야해서 부담이 상대적으로 많다
제약조건이 걸릴수록 성능은 저하된다
느릴경우 제약조건을 풀어주는 것이 좋다 (대신에 프로그램에서 관리하기)

---

 

1 - 3 Redo 로깅과 DML 성능

오라클은 데이터 파일과 컨트롤 파일에 가해지는 모든 변경사항을 Redo 로그에 기록한다 
( Redo = 재현 , Redo 로그 = 복구 할때 사용)

• Database Recovery
  물리적으로 디스크가 깨지는 등의 Media Fail이 발생 시 데이터베이스를 복구하기위해 사용
• Cache Recovery
  트랜잭션 데이터 유실에 대비하기 위함
• Fast Commit
  메모리상의 버퍼블록에만 기록된 채 아직 디스크에 기록되지 않았지만 Redo로그를 믿고 빠르게 커밋을 완료한다는 뜻

 

---

1 - 4 Undo 로깅과 DML 성능

• Transaction Rollback
  트랜잭션에 의한 변경사항을 최종 커밋하지 않고 롤백하고자 할 때
• Transaction Recovery
  시스템이 셧다운 된 시점에서 아직 커밋되지 않았던 트랜잭션들을 모두 롤백해야 할 때
• Repeatable Read (Read Consistency)
  읽기 일관성을 위해 사용

Redo - 트랜잭션을 재연함으로써 과거를 현재상태로 되돌리기
Undo - 트랜잭션을 롤백해 현재를 과거 상태로 되돌리기

예전에는 Roll back , 현재 오라클 9 부터 Undo 사용

오라클은 데이터를 입력 , 수정 , 삭제 할 때 마다 Undo 세그먼트에 기록을 남긴다
Undo 데이터를 기록한 공간은 트랜잭션이 커밋하는 순간 
다른 트랜잭션이 재사용할수 있는 상태로 바뀐다.

---

1 - 5 MVCC 모델

• 오라클은 시스템에서 마지막 커밋이 발생한 시점 정보를 SCN ( System Commit Number ) 이라는 글로벌 변수 값으로 관리 한다.
• Consistent 모드는 쿼리 SCN과 블록 SCN을 비교 함으로써 쿼리 수행 중 블록이 변경되었는지 확인하면서 읽는 방식
• 블록 SCN이 쿼리 SCN보다 더 큰 블록을 만나면 복사본 블록을 만들고 Undo 데이터를 적용해서 쿼리 시작 시점으로 되돌려 읽는다.

---

1 - 6 Transaction 의 속성 - ACID

• 원자성 (Atomicity) - 나눌 수 없는 하나의 작업으로 다뤄져야 한다. 
• 일관성 (Consistency) - Tx 수행 전과 후가 일관된 상태를 유지해야 한다.
• 격리성 (Isolation) - 각 Tx는 독립적으로 수행되어야 한다. (방해x)
• 지속성 (Durability) - 성공한 Tx의 결과는 유지되어야 한다.

1 - 7 Tx의 Isolation level 격리수준 레벨

1. READ UNCOMMITED - 커밋되지 않은 데이터도 읽기 가능
2. READ COMMITED - 커밋된 데이터만 읽기 가능
3. REPEATABLE READ - Tx가 시작된 이휴 변경은 무시됨
4. SERIALIZABLE - 한번에 하나의 Tx만 독립적으로 수행

---

1 - 8 데이터 저장 과정

 

• DML문을 실행하면 Redo 로그버퍼에 변경사항을 기록한다.
• 버퍼블록에서 데이터를 변경한다. 버퍼캐시에서 블록을 찾지 못하면, 데이터파일에서 읽는 작업부터 한다.
• 커밋한다.
• LGWR 프로세스가 Redo 로그버퍼 내용을 로그파일에 일괄 저장한다.
• DBWR 프로세스가 변경된 버퍼블록들은 데이터파일에 일괄 저장한다.

---

1 - 9  Lock 과 DML 성능             

Lock을 필요이상으로 자주 , 길게 사용하거나 레벨을 높일수록 DML 성능은 느려진다.

그렇다고 Lock을 너무 적게 짧게 사용 하거나 필요 이하로 낮춘다면 데이터 품질이 나빠진다

성능과 데이터 품질이 모두 중요한데 '트레이드 오프' 관계여서 어렵다

트레이드오프 

▶ 트레이드오프란 객체의 어느 한부분의 품질을 높이거나 낮추는게, 다른 부분의 품질을 높이거나 낮추는데 영향을 끼      치는 상황

        

---

2️⃣ Direct Path I/O 활용

1 - 1 Direct Path I / O

일반적인 블록 I/O와 다르게 버퍼캐시를 거치지 않고 바로 디스크로 간다

Direct Path I/O가 작동하는 경우

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

 

---

 

3️⃣ 파티션을 활용한 DML 튜닝

파티션을 이용한다면 대량 추가 / 변경 / 삭제 작업을 빠르게 처리 할수 있다.

1 - 1 테이블 파티션

파티셔닝은 테이블 또는 인덱스 데이터를 특정 컬럼값에 따라 세그먼트에 나눠서 저장하는것

세그먼트 

테이블 스페이스 ▶ 세그먼트 ▶ 익스텐트 ▶ 블럭

세그먼트는 테이블 , 인덱스 처럼 데이터 저장공간이 필요한 오브젝트다

테이블 , 인덱스를 생성할 때 데이터를 어떤 테이블 스페이스에 저장할지 지정세그먼트는 여러 익스텐트로 구성됨

세그먼트가 파티션 구조가 아니라면 테이블 , 인덱스도 하나의 세그먼트

세그먼트가 파티션 구조라면 각 파티션이 하나의 세그먼트
(아니 그러면 파티션이든 테이블 인덱스 전부 세그먼트네 ?)

익스텐트 는 공간을 확장하는 단위

세그먼트 : https://gi-dor.tistory.com/131

🔑SQL 튜닝 - CH 1. SQL 처리과정 & I / O

 

 

---

① Range 파티션

주로 날짜 컬럼을 기준으로 파티셔닝 하는 기초적인 방식

---

② 해시 파티션

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

해시파티션은 고객 ID , 사원번호 처럼 변별력이 좋고 데이터 분포가 고른 컬럼을 파티션 기준으로 
선정해야 효과적이다

create table 사원 ( 사원번호 varchar2(10) , 사원명 varchar2(10),....)
partition by hash (사원번호) partitions 4;

-- partition by hash: 해시 방식으로 파티션을 생성. 
-- 해시 분할은 지정된 컬럼(사원번호)의 해시 값을 기준으로 데이터를 분산시키며
-- 고른 데이터 분포를 확보할 수 있습니다.
-- (사원번호): 해시 분할의 기준이 될 컬럼. 
-- 여기서는 사원번호 컬럼이 기준이 되며 해당 컬럼의 값에 따라 데이터가 다른 파티션으로 분산됨.

-- partitions 4: 테이블을 4개의 파티션으로 나눈다는 의미

 

---

③ 리스트 파티션

사용자가 정의한 그룹핑 기준에 따라 데이터를 분할 저장하는 방식

 

---

 

4️⃣ Lock과 트랜잭션 동시성 제어

1 - 1 오라클 Lock

오라클은 공유 리소스와 사용자 데이터를 보호할 목적으로 DML Lock , DDL Lock , 래치, 버퍼 Lock ,

라이브러리 캐시 Lock / Pin등 다양한 종류의 Lock를 사용한다.

---

1 - 2 DML 로우 Lock 

1 ) DML 로우 Lock은 두개의 동시 트랜잭션이 같은 로우를 변경하는 것을 방지한다

2 ) 어떤 DBMS든지 DML로우 Lock 은 배타적모드를 사용 하므로 UPDATE 또는 DELETE를 할수 없다

DML
INSERT , DELETE , UPDATE 같은 연산을 포함한 SQL 명령어 집합

'배타적 모드'
DB의 잠금 메커니즘 중 하나
배타적 잠금은 특정 트랜잭션에서 리소스에(Ex. 행 , 블록 , 테이블)
액세스 할 때 다른 트랜잭션이 해당 리소스를 동시에  변경할수 없도록 하는 잠금 방식

 

3 ) MVCC 모델을 사용 하는 오라클은 SELECT 문에 로우 Lock을 사용 하지 않는다
    오라클은 다른 트랜잭션이 변경한 로우를 읽을 때 복사본 블록을 만들어서 쿼리가 '시작된 시점'으로 되돌려서 읽는다.

---

1 - 4 DML 테이블 Lock

오라클은 DML로우 Lock 설정하기 전에 테이블 Lock을 먼저 설정한다
현재의 트랜잭션이 갱신중인 테이블 구조를 다른 트랜잭션이 변경하지 못하게 막기 위해서다.

테이블 Lock을 TM Lock 이라고 부른다.

 

잠금모드 약어

• RS : row share  ▶ 행 공유 잠금
  ◽ 다른 트랜잭션에서 해당 테이블에 대한 동일한 RS 잠금 또는 RX 잠금을 획득할 수 있다
     그러나 X 잠금을 설정하려는 트랜잭션은 대기 상태가 된다
  
  
• RX : row exclusive ▶ 배타적 행 잠금 
  ◽ 다른 트랜잭션에서 해당 테이블에 대한 RS 또는 RX 잠금을 획득할 수 있다 그러나
      S, SRX, 또는 X 잠금을 설정하려는 트랜잭션은 대기 상태가 된다
  
  
• S : share ▶ 공유 잠금
  ◽ 다른 트랜잭션에서 동일한 테이블에 대해 RS, RX 또는 S 잠금을 획득할 수 있지만,
      SRX 또는 X 잠금을 설정하려는 트랜잭션은 대기 상태가 된다
  
  
• SRX : share row exclusive ▶ 배타적 행 공유 잠금 
  ◽ RS, RX, S 또는 다른 SRX 잠금을 설정하려는 트랜잭션은 대기 상태가 됩니다.
  
  
• X : exclusive ▶ 배타적 잠금
   ◽ 다른 트랜잭션이 해당 테이블에 대한 어떠한 잠금도 획득할 수 없습니다.

 

테이블 Lock이라고 하면 테이블 전체에 Lock 이 걸리는건가 ? 생각할수 있는데   오라클에서 말하는 테이블 Lock은

자신(테이블 Lock을 설정한 트랜잭션)이  해당 테이블에서 현재 어떤 작업을 수행 중인지 알리는 표시이다. 

---

1 - 5 대상 리소스가 사용중일 때의 진로선택

Lock을 얻고자 하는 리소스가 사용 중일 때  3가지 방법 중 하나를 선택한다

① Lock이 해제될 때까지 기다린다. 

select * from t for update

② 일정 시간만 기다리다 포기한다.

select * from t for update wait 3

③ 기다리지 않고 작업을 포기한다.

select * from t for update nowait

 

---

1 - 6 Lock을 푸는 열쇠 , 커밋

Lock은 커밋을 해야 풀린다

오라클은 데이터를 읽을 때 Lock을 사용하지 않으므로 다른 DBMS에 비해 상대적으로
Lock 경합이 적게 발생하는데 

읽는 트랜잭션의 진행을 막는 부담감이 없으므로 필요한 만큼 트랜잭션을 충분히 길게 가져갈수 있다.

❗ 불필요하게 트랜잭션을 길게 정의하지 말것

 

---

1 - 7 트랜잭션 동시성 제어

동시성 제어는 비관적 동시성 제어와 낙관적 동시성 제어로 나뉜다

① 비관적 동시성 제어 

사용자들이 같은 데이터를 동시에 수정을 하고있다면 한사용자가 데이터를 읽는 시점에 Lock을 걸고
조회 또는 갱신처리가 완료될 때 까지 유지한다

Lock을 먼저 획득한 후에 작업

/* select문에 for update를 사용하면 고객 레코드에 Lock을 설정하므로 데이터가 잘못 갱신되는 문제를 방지할 수 있다. */
select 적립포인트, 방문횟수, 최근방문일시, 구매실적
from   고객
where  고객번호 = :cust_num for update

 

② 낙관적 동시성 제어

Lock 을 확인하지 않고 작업 ▶ 만약 Lock이 걸려있다면 기다려야 한다.

select 적립포인트, 방문횟수, 최근방문일시, 구매실적, 변경일시
into   :a, :b, :c, :d, :mod_dt
from   고객
where  고객번호 = :cust_num;

-- 새로운 적립포인트 계산

update 고객 set 적립포인트 = :적립포인트, 변경일시 = SYSDATE
where  고객번호 = :cust_num
and    변경일시 = :mod_dt;   -- 최종 변경일시가 앞서 읽은 값과 같은지 비교

if sql%rowcount = 0 then
   alert('다른 사용자에 의해 변경되었습니다.')
end if;

select 고객번호
from 고객
where 고객번호 = :coust_num
and 변경일시 = :mod_dt
for update nowait;

 

✔ 충돌이 없다면 낙관적이 더 좋다

---

1 - 8 채번방식에 따른 INSERT 성능비교

INSERT , UPDATE , DELETE ,MERGE중 가장 중요하고 튜닝요소가 많은것은 INSERT다

신규데이터를 입력하면 PK중복을 방지하기 위한 채번이 선행 되야한다

• 채번 테이블
• 시퀀스 오브젝트
• MAX + 1 조회

채번 ?
데이터베이스에서 고유한 식별자 또는 일련번호를 생성하는 방법.
다양한 시스템에서 데이터를 추가할 때 고유한 키 값을 생성하기 위해 사용된다고 한다.

 

① 채번 테이블

각 테이블 식별자의 단일컬럼 일련번호 혹은 구분 속성별 순번을 채번하기 위해 별도 테이블을 관리하는 방식

장점

• 범용성이 좋다
• INSERT 과정에 중복 레코드 발생에 대비한 예외(Exception) 처리에 크게 신경쓰지 않아도된다
• PK가 복합 컬럼일 때도 사용 가능하다
• INSERT 과정에 결번을 방지할수 있다.

단점  : 채번방식에 비해 성능이 안 좋다.

 

② 시퀀스 오브젝트

시퀀스의 가장 큰 장점은 성능이 빠르다.
중복 레코드 발생에 대비한 예외처리에 크게 신경쓰지 않아도 된다.

가장큰 장점이 성능이지만 성능 이슈가 없는 것은 아니다.
또한 PK가 단일 컬럼일 때만 사용 가능하다.

각 레코드를 유일하게 식별하는 최소 컬럼으로 PK를 구성해야 한다는 최소성 요건을 위배 하게된다

③  MAX + 1 조회

대상 테이블의 최종 일련번호를 조회하고 , 거기에 1을 더해서 INSERT하는 방식이다

insert into 상품거래 (거래일련번호 , 계좌번호 , 거래일시 , 상품코드 , 거래가격 , 거래수량)
    values (	( select max(거래일련번호)+1 from 상품거래) ,
             	:acnt_no , sysdate , :prod_cd , :trd_price ,:trd_qty );

 

장점

• 시퀀스 또는 별도의 채번 테이블을 관리하는 부담이 없다
• 동시 트랜잭션에 의한 충돌이 많지 않으면 , 성능이 매우 빠르다
• PK가 복합컬럼 인경우 , 즉 구분 속성별 순번을 채번할 때도 사용 할수 있다.
  
  

단점

• 레코드 중복에 대비한 세밀한 예외처리가 필요하다.
• 다중 트랜잭션에 의한 동시 채번이 심하면 시퀀스보다 성능이 많이 나빠질 수 있다.