친절한 SQL튜닝 6장(DML 튜닝) 정리
1. 기본 DML튜닝
DML 성능에 영향을 미치는 요소
1. 인덱스
2. 무결성 제약
3. 조건절
4. 서브쿼리
5. Redo 로깅
6. Undo 로깅
7. Lock
8. 커밋
인덱스는 정렬된 자료구조로 삭제 후 삽입하는 방식으로 처리된다.
데이터 무결성 규칙 4가지
1. 개체 무결성
2. 참조 무결성
3. 도메인 무결성
4. 사용자 정의 무결성
PK, FK 제약은 CHECK, NOT NULL제약보다 성능에 더 큰 영향을 미친다.
DML을 수행할 때마다 Redo로그를 생성해야 하므로 Redo 로깅은 DML 성능에 영향을 미친다.
DML이 Lock에 의해 블로킹된 경우 DML을 완료할 수 있게 Lock을 푸는 열쇠가 커밋이다.
트랜잭션 저장 과정
1. DML문을 실행하면 Redo 로그버퍼에 변경사항을 기록한다.
2. 버퍼블록에서 데이터를 변경한다.
3. 커밋한다.
4. LGWR프로세스가 Redo 로그버퍼 내용을 로그파일에 일괄 저장한다.
5. DBWR 프로세스가 변경된 버퍼블록들은 데이터파일에 일괄 저장한다.
오라클은 데이터를 변경하기 전에 항상 로그부터 기록한다. 그것을 Write Ahear Logging 이라고 부른다.
커밋 시점에는 REdo 로그버퍼 내용을 로그파일에 기록한다. 이것을 Log Force at Commit이라고 한다.
커밋은 저장버튼이고 커밋 시점에 Redo 로그를 디스크에 안전하게 기록했다면 트랜잭션의 영속성은 보장된다.
커밋을 완료할 때까지 다음작업을 진행할 수 없다. Sync 방식이고 Redo 로그를 기록하는 작업은 디스크 I/O 작업이다. 즉 커밋은 생각보다 느린 작업이다.
SQL은 세 단계로 나누어 실행된다.
1. Parse Call : SQL파싱과 최적화를 수행하는 단계
2. Execute Call : SQL을 실행하는 단계
3. Fetch Call : Select 문에서만 나타나는 단계로 결과집합을 사용자에게 전송하는 과정
Call의 분류
1. User Call : 네트워크를 경유해 DBMS외부로부터 인입되는 Call이다.
2. Recursive Call :DBMS 내부에서 발생하는 Call이다.
커밋을 자주 발행하면 원자성에 문제가 생긴다.
반대로 너무 오래 걸리는 트랜잭션을 한 번도 커밋하지 않고 진행하면 Undo 공간 부족으로 시스템에 여러 부작용을 초례할 수 있다.
적당한 주기로 커밋하는 방안을 고려해야 한다. ( 10만 번에 한 번씩 커밋 한다는 둥 )
java프로그램에서 수행하면 네트워크를 경유하는 user call이므로 성능이 급격히 나빠진다.
Redo 로그 사용 목적 3가지
1. 데이터베이스 리커버리
2. 캐시 리커버리
3. 패스트 커밋
Redo => 트랜잭션을 재현함으로써 과거를 현재 상태로 되돌리는데 사용, 트랜잭션을 재현하는 데 필요한 정보를 로깅
Undo => 트랜잭션을 롤백함으로써 현재를 과거 상태로 되돌리는데 사용, 변경된 블록을 이전 상태로 되돌리는 데 필요한 정보를 로깅
오라클은 데이터를 CUD할 때마다 Undo 세그먼트에 기록을 남긴다.
Undo 사용 목적 3가지
1. 트랜잭션 롤백
2. 트랜잭션 리커버리
3. 읽기 일관성
MVCC모델
1. Current 모드 -> 원본 블록 상태 그대로 읽는 방식
2. Consistent 모드 -> 시작된 시점으로 되돌려서 읽는 방식
Lock
1. DML 성능에 매우 크고 직접적인 영향을 미친다.
2. 필요이상으로 자주, 길게 사용하거나 레벨을 높일수록 DML성능은 느려진다.
3. 동시성 제어가 필요하다. ( 동시에 실행되는 트랜잭션 수를 최대화 하면서 CURD 시 데이터 무결성 유지하는 것 )
업무로직이 복작하면 절차적으로 처리할 수 밖에 없지만, 그렇지 않다면 가급적 one sql로 구현하려고 노력해야 한다.
one sql 3가지
1. insert into select
2. 수정가능 조인 뷰
3. merge 문
인덱스를 비활성화하고 DML을 수행하는 것이 효과적이다. ( 대용량 데이터일 경우 선호 )
1쪽 집합에 PK 제약을 설정하거나 Unique 인덱스를 생성해야 수정가능 조인뷰를 통한 입력/수정/삭제가 가능하다.
키 보존 테이블이란?
1. 조인된 결과집합을 통해서도 중복 값 없이 unique 하게 식별이 가능한 테이블을 말한다.
2. 뷰에 rowid를 제공하는 테이블을 말한다.
뷰에서 인덱스가 없을 경우 group by를 통해 ora-01779에러를 회피할 수 있다.
배치나 데이터 이행 프로그램에서 사용하는 임시 테이블에는 일일이 pk제약이나 인덱스를 생성하지 않아도 되서 은근 잘 쓸 수 있다.
조인 update를 위해 앞으로 merge문을 쓸 지 update문을 쓸 지 선택은 각자의 몫이다.
2. Direct Path I/O활용
버퍼캐시를 경유하지 않고 곧바로 데이터 블록을 읽고 쓸 수 있는 것.
Direct Path I/O가 작동하는 경우
1. 병렬 쿼리로 풀스캔 할 때
2. 병렬 DML할 때
3. 다이렉트 패스 인서트 할 때
4. TEMP세그먼트 블록들을 읽고 쓸 때
5. DIRECT 옵션을 지정하고 EXPORT를 수행할 때
6. NOCACHE 옵션을 지정한 LOB컬럼을 읽을 때
ORDER BY, GROUP BY, 해시 조인, 소트 머지 조인 등을 처리할 떄는 힌트로 지정한 병렬도보다 두 배 많은 프로세스가 사용된다.
일반적인 INSERT가 느린 이유
1. 입력 가능한 블록을 프리리스트에서 찾는다.
2. 프리리스트에서 할당받은 블록을 버퍼캐시에서 찾는다.
3. 버퍼캐시에 없으면 데이터파일에서 읽어 버퍼캐시에 적재한다.
4. INSERT내용을 UNDO세그먼트에 기록한다.
5. INSERT내용을 REDO로그에 기록한다.
Direct Path Insert 입력 방식
1. insert ... select 문에 append힌트 사용
2. parallel힌트 사용으로 병렬 insert
3. direct 옵션을 지정하고 sql*loader로 데이터 적재
4. ctas문 수행(create ... as select)
Direct Path Insert 가 빠른 이유
1. 프리리스트를 참조하지 않는다.
2. 블록을 버퍼캐시에서 탐색하지 않는다.
3. 버퍼캐시에 적재하지 않고, 데이터파일에 직접 기록한다.
4. undo 로깅을 안한다.
5. redo 로깅을 안 하게 할 수 있다. ( 일반 인서트문은 불가능 )
Direct Path Insert주의사항
1. 성능은 좋아지지만, exclusive 모드 tm lock이 걸린다.
2. 커밋하기 전까지 다른 트랜잭션은 해당 테이블에 dml을 수행할 수 없다.
3. 트랜잭션이 많은 주간에 이 옵션을 사용하면 안된다.
4. 테이블에 여유 공간이 있어도 재활용하지 않는다.
5. 주기적으로 reorg 작업을 수행해야한다.
수정과 삭제는 기본적으로 Direct Path Write 가 불가능하다. 따라서 병렬 dml처리로 해야한다.
3. 파티션을 활용한 DML 튜닝
파티션을 이용하면 대량 추가/변경/삭제 작업을 빠르게 처리할 수 있다.
파티셔닝은 테이블 또는 인덱스 데이터를 특정 칼럼값에 따라 별도 세그먼트에 나눠서 저장하는 것을 말한다.
데이터를 월별, 분기별, 반기별, 연별로 분할해서 저장해두는 것과 같다.
파티션에는 range, 해시, 리스트 3종류가 있다.
1. range파티션
이력성 데이터를 풀 스캔 방식으로 조회할 때 성능을 크게 향상한다.
2. 해시 파티션
파티션 개수만 사용자가 결정하고 데이터를 분산하는 알고리즘은 오라클 내부 해시함수가 결정한다.
변별력이 좋고 데이터 분포가 고른 컬럼을 파티션 기준으로 해야 효과적이다.
해시 알고리즘 특성상 등치(=) 조건이나 in-list 조건으로 검색할 때만 파티션 prunning이 작동한다.
3. 리스트 파티션
사용자가 정의한 그룹핑 기준에 따라 데이터를 분할 저장하는 방식이다.
순서와 상관없이 불연속적인 값의 목록에 의해 결정된다.
사용자가 정의한 논리적인 그룹에 따라 분할한다.
파티션 prunning
파티션의 불필요한 부분을 제거한다라는 뜻을 가진다.
sql 하드파싱이나 실행 시점에 조건절을 분석해서 읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외하는 기능이다.
파티션은 데이터가 흩어지지 않고 물리적으로 인접하도록 저장하는 클러스터링 기술에 속한다.
클러스터와 다른 점은 세그먼트 단위로 모아서 저장한다는 것이다.
인덱스 파티션
1. 비파티션 테이블
2. 파티션 테이블
파티션 인덱스
1. 로컬 파티션 인덱스
각 테이블 파티션과 인덱스 파티션이 서로 1:1 대응 관계가 되도록 오라클이 자동으로 관리하는 파티션 인덱스를 말한다.
계절별로 별도 색인을 만드는 것과 같다.
로컬 파티셜 인덱스 == 로컬 인덱스
테이블과 정확히 1:1 대응 관계를 갖도록 오라클이 파티션을 자동으로 관리해준다.
파티션 구성을 변경하더라도 인덱스를 재생성할 필요가 없다.
장점은 관리 편의성이다.
2. 글로벌 파티션 인덱스
로컬이 아닌 파티션 인덱스는 모두 글로벌 파티션 인덱스이다, 테이블 파티션과 독립적인 구성을 갖는다.
파티션을 테이블과 다르게 구성한 인덱스다.
파티션 유형이 다르거나, 파티션 키가 다르거나, 파티션 기준값 정의가 다른 경우다.
테이블 파티션 구성을 변경하는 순간 unusable상태로 바뀌므로 곧바로 인덱스를 재생성해줘야 한다. 재생성하는 동안 해당 테이블 서비스 중단 필수
오라클이 자동으로 관리해 주지 않는다.
3. 비파티션 인덱스
말 그대로 파티셔닝하지 않은 인덱스다.
일반 CREATE INDEX문이다.
글로벌 비파티션 인덱스라고 부르기도 한다.
테이블 파티션 구성을 변경하는 순간 unusable상태로 바뀌므로 곧바로 인덱스를 재생성해줘야 한다. 재생성하는 동안 해당 테이블 서비스 중단 필수
파티션 인덱스
1. prefixed
인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치한다.
2. nonprefixed
인섹스 파티션 키 컬럼이 인덕스 키 컬럼 왼쪽 선두에 위치하지 않는다. 인덱스 컬럼이 아예 속하지 않을 때도 여기에 속한다.
총 4가지 파티션 인덱스로 구성
1. 로컬 prefixed 파티션 인덱스
2. 로컬 nonprefixed 파티션 인덱스
3. 글로벌 prefixed 파티션 인덱스
4. 비파티션 인덱스
**Unique 인덱스를 파티셔닝하려면 파티션 키가 모두 인덱스 구성 컬럼이어야 한다. ( DML 성능 보장을 위한 제약조건이다 )
서비스 중단 없이 파티션 구조를 빠르게 변경하려면 PK를 포함한 모든 인덱스가 로컬 파티션 인덱스이어야 한다.
4. Lock과 트랜잭션 동시성 제어
Lock은 데이터베이스의 특징을 결정짓는 가장 핵심적인 매커니즘이다.
Lock의 종류
1. 래치
sga공유된 각종 자료구조를 보호하기 위해 사용
2. 버퍼 락
버퍼 블록에 대한 액세스를 직렬화하기 위해 사용
3. 라이브러리 캐시락
라이브러리 캐시에 공유된 커서와 pl/sql 프로그램을 보호하기 위해 사용
4. dml 락
다중 트랜잭션이 동시에 액세스하는 사용자 데이터의 무결성을 보호( 테이블 락과 로우 락이 있다. )
5. ddl 락
dml 로우 락 특징
1. 두 개의 동시 트랜잭션이 같은 로우를 변경하는 것을 방지
2. 하나의 로우를 변경하려면 로우 lock을 먼저 설정
3. 커밋하지 않은 로우를 다른 트랜잭션이 업데이트나 삭제 불가
4. insert에 대한 로우 lock경합은 unique인덱스가 있을 때만 발생한다. ( 선행 트랜잭션이 커믹하면 insert실패, 롤백하면 성공 )
select 문에는 lock이 필요없다. ( dml과 select는 서로 진행을 방해하지 않는다. 단 mvcc모델을 사용하지 않는 dbms는 select 문에 공유 lock을 사용한다. )
sql튜닝은 lock튜닝인 셈이다.
dml 테이블 lock
1. dml로우 lock 설정하기전에 테이블 lock을 먼저 설정한다.
2. tm lock이라고도 부른다.
3. 테이블 lock을 설정한 트랜잭션이 해당 테이블에서 현재 어떤 작업을 수행 중인지를 알리는 일종의 푯말(flag)이다.
4. lock에는 여러가지 모드가 있고 모드에 따라 후행 트랜잭션이 수행할 수 있는 작업의 범위가 결정된다.
lock을 얻고자 하는 리소스가 사용 중일 때 진로 선택
1. lock이 해제될 때까지 기다린다. ( dml 처리 시 )
2. 일정 시간만 기다리다 포기한다.
3. 기다리지 않고 작업을 포기한다. ( ddl 처리 시 )
블로킹과 교착상태
블로킹은 선행 트랜잭션이 설정한 lock때문에 후행 트랜잭션이 작업을 진행하지 못하고 멈춰 있는 상태이다.
교착상태는 두 트랜잭션이 각각 특정 리소스에 lock을 설정한 상태에서 맞은편 트랜잭션이 lock을 설정한 리소스에 또 lock을 설정하려고 진행하는 상황이다.
교착상태가 발생하면 이를 먼저 인지한 트랜잭션이 문장 수준 롤백을 진행한다. ( ora-00060 에러 )
단, 교착상태는 해소됐지만 블로킹 상태에 놓이게 되므로, ora-00060에러를 받은 트랜잭션은 커밋 또는 롤백을 해야한다. 만약 하지 않으면 대기 상태를 지속하게 되므로 주의해야 한다.
커밋 옵션
1. WAIT(기본값)
LGWR가 로그버퍼를 파일에 기록했다는 완료 메시지를 받을 때까지 기다린다. ( 동기식 커밋 )
2. NOWAIT
LGWR의 완료 메시지를 기다리지 않고 바로 다음 트랜잭션을 진행한다. ( 비동기식 커밋 )
3. IMMEDIATE
커밋 명령을 받을 때마다 LGWR가 로그 버퍼를 파일에 기록한다.
4. BATCH
세션 내부에 트랜잭션 데이터를 일정량 버퍼링했다가 일괄 처리한다.
트랜잭션 동시성 제어
1. 비관적 동시성 제어
사용자들이 같은 데이터를 동시에 수정할 것으로 가정
2. 낙관적 동시성 제어
사용자들이 같은 데이터를 동시에 수정하지 않을 것으로 가정
데이터 품질과 동시성 향상을 위해 데이터를 변경할 목적이라면 당연히 LOCK을 걸어야 한다. 코딩이 번거롭더라도 WAIT 또는 NOWAIT옵션을 활용한 예외처리도 신경써야 한다.
신규 테이블 데이터 입력에 따른 PK중복 방지 채번
1. 채번 테이블
2. 시퀀스 오브젝트
3. MAX + 1조회
채번 테이블의 장점
1. 범용성이 좋다.
2. 채번 함수만 잘 정의하면 편리하게 사용할 수 있다.
3. 결번을 방지할 수 있다.
4. PK가 복합컬럼일 때도 사용할 수 있다.
다른 채번 방식에 비해 로우 LOCK 경합을 해서 성능이 안좋다.
동시 INSERT가 많을 경우 채번 레코드와 채번 테이블까지 블록에 대한 경합이 발생한다.
시퀀스 오브젝트 장점
1. 성능이 빠르다.
2. 중복 레코드 발생 예외처리 신경 안써도 된다.
대신 시퀀스 채번 과정에 발생하는 LOCK이 있다.
오라클 내부에서 관리하는 채번 테이블이기 때문이다.
PK가 단일컬럼일 때만 사용이 가능하다. ( 복합컬럼일 때도 사용은 가능하지만 최소성 요건을 위배한다. )
따라서 캐시 사이즈를 적절히 설정하면 가장 빠른 성능을 제공한다.
시퀀스 LOCK 3가지
1. 로우 캐시 LOCK
SGA를 I/O할 때 쓰는 LOCK
2. 시퀀스 캐시 LOCK
시퀀스 캐시에서 값을 얻을 때 쓰는 락
3. SV LOCK
RAC에서 노드 간에 순서가 보장된 상태로 Sequence.nextval을 호출하는 동안 획득하는 락
RAC => DB장애를 대비해서 DB서버를 2대 이상 놓는 것, 단 각 DB의 내용은 똑같아야 한다.
MAX + 1조회 장점
1. 시퀀스 또는 별도의 채번 테이블을 관리하는 부담이 없다.
2. 동시 트랜잭션에 의한 충돌이 많지 않으면 성능이 매우 빠르다.
3. PK가 복합컬럼이여도 사용할 수 있다.
단점으로는 레코드 중복에 대한 예외처리가 필요하고 동시 채번이 심하면 시퀀스보다 성능이 나빠질 수 있다.
정리하자면,,,
동시 채번이 적다 + 관리 부담 고려 => MAX + 1
동시 채번이 많다 + PK단일컬럼 => 시퀀스
동시 채번이 많다 + PK 구분속성값이 많은 경우 => MAX + 1
동시 채번이 많다 + PK 구분 속성 값이 적을 경우 => 시퀀스 오브젝트
일련번호나 입력일시/변경일시 같은 순차적으로 값이 증가하는 단일컬럼은 항상 맨 우측 블럭에만 데이터가 입력되는데 이런 특징을 인덱스 Right Growing이라고 한다.
서로 다른 트랜잭션이 하나의 블록을 접근할 경우 성능이 매우 나빠질 수 있다 이 얘기임.
따라서 인덱스 해시 파티셔닝이나, 인덱스를 리버스 하는 방법도 고려할 수 있다.
6장 정리 끝~