Simple Record

3개 이상의 주문 채널이 존재하고 각 주문 채널별 모놀리식 시스템과 단일 DB로 구성된 환경에서 특정 시간 동시 주문 발생 간 데드락이 발생하였고 이에 대한 해결 방법에 대한 사고 실험을 해보고자 한다

문제 상황

한개 이상의 상품을 포함하는 주문이 여러 판매 채널로 부터 발생하는 과정에서 상품의 재고 차감 중 데드락이 발생

발생 예시

이처럼 여러 상품을 포함하고 있는 주문 건에 대해 재고 차감 시 데드락이 발생할 수 있다

락의 종류

1. Optimistic Lock(낙관적 락)

낙관적 락은 version 컬럼을 추가로 두고 업데이트 시 버전을 조건으로 넣어 데이터를 변경하는 방식

조건절에 있는 버전 정보가 맞지 않으면 업데이트가 실패하게 됨으로써 정합성을 보장하는 방법이다

UPDATE ..... WHERE PRODUCT_ID = ? AND VERSION = ?;

락을 점유하지 않아 데드락 발생에서 자유롭다

버전 정보가 맞지 않아 업데이트가 실패하는 경우에 대한 재수행 로직이 추가로 구현될 필요가 있다

또한, 충돌이 빈번하게 발생하는 상황(주문이 대량으로 들어오는 상황)에서 반복적인 재수행에 따라 성능 저하가 발생할 수 있다

2. Pessimistic Lock(비관적 락)

비관적 락은 충돌이 발생할 수 있는 데이터에 접근할 때 해당 데이터에 대한 락을 설정하여 다른 트랜잭션에서 해당 데이터에 대한 변경을 차단하는 방식(행에 대한 락 설정)

SELECT * FROM INVENTORY WHERE PRODUCT_ID = ? FOR UPDATE;
SELECT * FROM INVENTORY WHERE PRODUCT_ID IN (?, ?, ?...) ORDER BY PRODUCT_ID FOR UPDATE;

* 여러 행에 대해 락을 설정하는 경우 ORDER  BY 설정이 중요하다. ORDER BY 설정을 하지 않은 경우 락을 설정하는 행의 순서에 따라 데드락이 발생할 수 있기 때문이다

출돌이 발생하기 전에 락을 걸어 차단하기 때문에 충돌 상황에서 안전하게 처리가 가능하다

하지만 트랜잭션 시간이 길거나 상품 수가 많은 경우 성능 저하가 발생할 수 있다

3. Distributed Lock(분산 락)

분산 락은 여러 서버들 간의 리소스에 대한 잠금이 필요한 경우 사용하는 것으로 Redis나 Zookeeper 등의 미들웨어를 통해 잠금을 수행한다

분산 시스템에 대한 락을 설정할 수 있는 장점이 있으나 미들웨어에 의존하기 때문에 미들웨어의 장애 시 락의 안정성을 보장하기 어렵다(미들웨어의 FailOver 설정 중요)

네트워크 지연이나 TTL 만료 등 고려하지 않으면 중복 실행될 수 있다

고찰

위와 같은 문제가 발생한 상황과 현재 운영 중인 시스템의 관점에서 보면 낙관적 락을 선택하는게 바람직해 보인다

1. 단일 DB이므로 분산 환경 X

2. 동시 발생하는 주문이 많지 않다

3. 데드락 회피 필요

위와 같은 이유로 현재 주어진 상황 속에서 적용할 수 있는 락을 선택하였다

!다만, 낙관적 락을 위해서는 변경 실패에 대한 재시도 로직에 대한 추가적인 구현과 기존 코드나 쿼리에 대한 변경이 필요하고, 재시도 횟수에 대한 정책 또한 수립이 필요하다

사고 실험을 하면서 서비스의 현재 상황과 시스템의 구성 등을 고려하여 가장 적합한 방법을 고민해볼 수 있었다

외부와 통신하는 기능에 대해 외부 서비스의 SSL이 2세대 인증서로 변경되면서 이에 대해 확인하는 과정을 기록한 것이다

어플리케이션에서 외부 서비스와의 통신을 하는 경우 RestTemplate를 통해 통신을 하는 등 java 단에서 통신을 하는 경우가 있다

이러한 경우 통신의 과정에서 SSL 인증서의 검증은 어떻게 이루어 지는지에 대해 알아보았다

일반적으로 웹사이트에 접속을 하게 되면 SSL 인증서에 대한 검증은 브라우저에서 수행하게 되어있는데

Java 어플리케이션 내에서 https 프로토콜로 통신 하는 경우 SSL 인증서에 대한 검증은 JVM 내에서 이루어지게 되어있다

Java의 SSL 인증서 검증 흐름

Java 어플리케이션 내에서 SSL 인증서의 검증 흐름은 다음과 같다

  HttpsURLConnection
         ↓
     SSLContext
         ↓
 TrustManagerFactory
         ↓
  X509TrustManager (실제 검증 수행)
         ↓
 checkServerTrusted

HttpsURLConnection은 네트워크 통신 레벨의 API로 SSL 핸드셰이크를 수행

SSLContext는 SSL 세션의 구성요소에 대한 초기화 및 관리르 수행하고 해당 객체에서 암호화 방식이나 TrustManager를 결정

TrustManagerFactory SSLContext에서 사용할 TrustManager 리스트를 생성하는 역할 수행한다 이 때, JVM "cacerts" 또는 사용자 keyStore를 통해 TrustManager 생성

X509TrustManager는 실제 서버 인증서 체인을 검증하는 역할을 수행하며 "checkServerTrusted" 메소드를 통해 서버가 제공한 인증서 체인을 검증

(RestTemplate도 내부적으로 HttpURLConnection을 사용하고 있어 인증 흐름은 동일)

Java 내 cacerts 확인 방법

cd $JAVA_HOME (JAVA가 설치된 디렉토리로 이동)

cd jre/lib/security (cacerts가 있는 위치로 이동)

# 아래 명령을 수행하면 해당 되는 alias에 대한 CA 인증서 정보를 볼 수 있다
keytool -list -v -keystore ./cacerts -storepass [password, 기본 "changeit"] -alias '[alias 명]'

해당 CA인증서를 확인함으로써 인증서의 세대 교체 등이 발생했을 때 신규 인증서를 어플리케이션에서 검증할 수 있는지 확인할 수 있다

추가

어플리케이션 실행할 때 jvm 옵션으로 아래 옵션을 추가하면 HTTPS 요청이 발생할 때 인증서, TLS 버전, 루트 인증서에 대한 정보를 로그로 확인할 수 있다

-Djavax.net.debug=ssl,handshake,certpath

선택도 : 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율

선택도 = 1 / NDV(Number of Distinct Value)

카디널리티 : 전체 레코드 중에서 조건절에 의해 선택되는 레코드 개수

카디널리티 = 총 로우 수 * 선택도 = 총 로우 수 / NDV

예) 상품분류 컬럼이 ‘가전’, ‘의류’, ‘식음료’, ‘생활용품’ 네 개의 값이 있을 때,

where 상품분류 = ‘가전’ 해당 조건에 대한 선택 도는 25%(1/4)

만약 전체 레코드가 10만건이면 카디널리티는 2만 5천

통계정보

오브젝트 통계

테이블 통계

테이블 통계 정보 수집 방법

테이블 통계 정보 조회

또는 all_tab_statistics 뷰를 통해 조회 가능

테이블 통계항목

인덱스 통계

인덱스 통계 수집

인덱스 조회

인덱스 통계정보는 all_ind_statistics 뷰에서 조회가 가능하다

컬럼 통계(히스토그램 포함)

컬럼통계는 테이블 통계 수집할 때 함께 수집된다

컬럼 통계 조회

컬럼 히스토그램

컬럼 값별로 데이터 비중 또는 빈도를 미리 계산해 놓은 통계정보

실제 값을 읽어서 계산해둔 값으로 데이터 분포가 많이 변하지 않는 한 거의 정확하다

해스토그램 수집 방법

히스토그램을 수집하려면, 테이블 통계 수집 시 method_opt 파라미터를 지정하면 된다

수집된 컬럼 히스토그램 조회

all_tab_histograms 뷰에서 같은 저보 조회 가능

시스템 통계

애플리케이션 및 하드웨어 성능 특성을 측정한 것으로 아래 항목을 포함 한다

• CPU 속도
• 평균적인 Single Block I/O 속도
• 평균적인 Multiblock I/O 속도
• 평균적인 Multiblock I/O 개수
• I/O 서브시스템의 최대 처리량
• 병렬 Slave의 평균적인 처리량

sys.aux_stats$ 뷰에서 조회할 수 있다

비용 계산 원리

인덱스 키값을 모두 = 조건으로 검색할 때의 비용 계산

인덱스 키값이 모두 = 조건이 아닐 때는 컬럼 통계까지 활용하여 비용 계산

비용(Cost)

I/O 비용 모델 : I/O 비용 모델을 사용할 때 실행게획에 나타나는 Cost는 ‘예상 I/O Call 횟수’를 의미

CPU 비용 모델 : Single Block I/O 를 기준으로 한 상대적 시간을 표현

옵티마이저

• 비용기반(Cost-Based) 옵티마이저(CBO) : 사용자 쿼리를 위해 후보군이 될만한 실행계획들을 도출하고, 데이터 딕셔너리에 미리 수집해 둔 통계정보를 이용해 각 실행계획의 예상비용을 산정하고, 그 중 가장 낮은 비용의 실행계획 하나를 선택하는 옵티마이저
• 규칙기반(Rule-Based) 옵티마이저(RBO) : 통계정보를 활용하지 않고 단순한 규칙에만 의존하여 대량 데이터를 처리하는데 부적합

옵티마이저 모드

• ALL_ROWS : 전체 처리속도 최적화 : 전체를 읽는 것을 전제로 시스템 리소스를 가장 적게 사용하는 실행계획을 선택
• FIRST_ROWS : 최초 응답속도 최적화 : 앞쪽 일부만 읽다가 멈추는 것을 전제로 응답 속도가 가장 빠른 실행계획을 선택
• FIRST_ROWS_N : 최초 N건 응답속도 최적화 : 앞쪽 N개 로우만 읽고 멈추는 것을 전제로 응답 속도가 가장 빠른 실행계획을 선택

옵티마이저 모드 설정

힌트 적용

옵티마이저에 영향을 미치는 요소

• SQL과 연산자 형태 : SQL 형태, 연산자(=, IN, LIKE, BETWEEN, 부등호 등)
• 인덱스, IOT, 클러스터, 파티션, MV 등 옵티마이징 팩터
• 제약 설정 : PK, FK, Check, Not Null
• 통계정보
• 옵티마이저 힌트
• 옵티마이저 관련 파라미터 : 옵티마이저 파라미터 목록 확인

옵티아미저 한계

• 옵티마이저 행동에 가장 큰 영향을 미치는 통계정보를 필요한 만큼 충분히 확보하는 것은 불가능한 일, 통계정보를 수집하고 관리하는 데 어마어마한 시간과 비용이 들기 때문
• 바인드 변수를 사용한 SQL에 컬럼 히스토그램을 활용할 수 없다는 치명적인 단점
• 최적화에 허용되는 시간이 매우 짧다

개발자의 역할

- 필요한 최소 블록만 읽도록 쿼리 작성

1) 출력 대상이 아닌 게시물에 대해서도 GET_ICON 함수 적용

2) 회원, 게시판유형, 질문유형 테이블과 조인 → 출력 대상 집합 확정 후 조인 필요

- 최적의 옵티마이징 팩터 제공

최적화는 옵티마이저가 수행하지만, 잘 할 수 있도록 적절한 수단을 제공하는 것은 사용자의 몫

• 전략적인 인덱스 구성
• DBMS가 제공하는 다양한 기능 활용
• 옵티마이저 모드 설정
• 정확하고 안정적인 통계정보

옵티마이저 모드를 포함해 각종 파라미터를 적절한 값으로 설정하고, 통계정보를 잘 수집해 주는 것이 중요

그리고 난 후에 전략적인 인덱스 구성이 필수적으로 뒷받침되어야한다

또한 DBMS가 제공하는 기능을 적극적으로 활용해 옵티마이저가 최적의 선택을 할 수 있도록 다양한 수단을 제공해 주어야한다

- 필요시, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도

데이터베이스 튜닝

SQL이 병목이나 지연 없이 빠르고 안정적으로 수행되도록 조치하는 모든 활동

• SQL 튜닝 : I/O 효율화, DB Call 최소화, SQL 파싱 최소화 등
• DB 설계 : 논리적 데이터 구조 설계, 물리적 저장 구조 설계 등
• 인스턴스 튜닝 : Lock/Latch 모니터링 및 해소, 메모리 설정, 프로세스 설정 등

모의 커머스 서비스를 개발하는 과정에서 API에 대한 동작을 확인하기 위해 Testcontainer를 활용하여 테스트를 작성하였는데,

그 과정에서 발생한 내용에 대한 기록이다

회원 모듈을 개발하였고 "회원가입, 회원수정, 회원탈퇴, 로그인, 로그아웃" 이렇게 단순한 기능을 개발하였다

로그인과 로그아웃을 위해 JWT와 Redis를 활용하여 세션을 관리하였다

개발을 마친 후 단위적인 기능에 대한 테스트가 아닌 API를 통해 실제적으로 원하는 결과가 도출되는지 확인하기 위해 통합테스트가 필요하다

개발한 기능에는 Redis라는 서드파티 서비스가 필요하였고, 테스트를 위해 Redis 컨테이너를 그 때마다 띄울 수 없었다

그래서 Testcontainer를 활용하였다

Testcontainer를 활용하면 테스트가 수행되는 동한 테스트용 컨테이너를 자동으로 띄울 수 있기 때문에 통합테스트가 편리해진다

첫번째 회원에 대한 API 테스트를 Testcontainer를 활용하여 작성하고 수행하였다

@Testcontainers
abstract class RedisTestConfig {
    companion object {
        @JvmStatic
        @Container
        private val redis = GenericContainer("redis:6.2.7-alpine")
            .withExposedPorts(6379)
            .withReuse(true)
            .apply { start() }

        @JvmStatic
        @DynamicPropertySource
        fun registerRedisProperties(registry: DynamicPropertyRegistry) {
            registry.add("spring.data.redis.host") { redis.host }
            registry.add("spring.data.redis.port") { redis.getMappedPort(6379).toString() }
        }
    }
}

Testconatiner를 각 테스트 별로 사용할 수 있도록 abstract 클래스로 작성해둔 것이다

이것을 아래와 같이 상속 받아 테스트를 만들게 되면 테스트가 수행될 때 redis에 대한 테스트 컨테이너가 생성되고 테스트가 수행되게 된다

그러나 이런 방법으로 여러 테스트를 작성하고 테스트를 일괄로 수행하게 되면 문제가 발생하게 된다

이처럼 첫번째 테스트는 잘 수행이 되지만 두번째 테스트에서는 redis container를 찾지 못하여 테스트가 정상적으로 수행되지 않는 모습을 볼 수 있다

해결 방법

이를 해결하는 방법으로는 두가지가 있다

1. redis를 static으로 선언하지 않는다

@Testcontainers
abstract class RedisTestConfig {
    companion object {
        private val redis = GenericContainer("redis:6.2.7-alpine")
            .withExposedPorts(6379)
            .withReuse(true)
            .apply { start() }

        @JvmStatic
        @DynamicPropertySource
        fun registerRedisProperties(registry: DynamicPropertyRegistry) {
            registry.add("spring.data.redis.host") { redis.host }
            registry.add("spring.data.redis.port") { redis.getMappedPort(6379).toString() }
        }
    }
}

2. @DirtiesContext(classMode = DirtiesContext.ClassMode.AFTER_CLASS) 를 테스트 클래스 상단에 작성해준다

@SpringBootTest
@AutoConfigureMockMvc
@DirtiesContext(classMode = DirtiesContext.ClassMode.AFTER_CLASS)
class AuthControllerV1Test : RedisTestConfig() {

    @Autowired
    lateinit var memberRepository: MemberJpaRepository

    @Autowired
    lateinit var redisTemplate: RedisTemplate<String, Any>

.....
}

위 두 방법을 사용하게 되면 앞서 발생한 문제는 해소가 된다

그렇다면 문제가 발생할까?

문제 원인

문제의 원인은 Spring 테스트의 특성 때문에 발생한다

통합테스트를 할 때는 Spring 컨테이너를 통해 필요한 객체를 생성하고 의존성을 주입해야한다

그리고 이를 위해 @SpringBootTest 어노테이션을 붙인다

이를 통해 테스트가 수행될 때 SpringBootTestContextBootstrapper가 동작하면서 ContextLoader를 통해 context(ApplicationContext)를 로드하는 과정에서 캐싱된 context가 있으면 해당 컨텍스트를 재사용하게 된다

- ApplicationContext를 생성하는 비용이 크기 때문에 재사용

@SpringBootTest 동작 흐름

@SpringBootTest
   ↓
SpringBootTestContextBootstrapper
   ↓
SpringBootContextLoader.loadContext()
   ↓
ApplicationContext 생성
   ↓
의존성 주입
   ↓
테스트 수행
------------------
@BeforeEach 실행
   ↓
@Test 실행
   ↓
@AfterEach 실행
------------------
   ↓
컨텍스트 캐시 유지 또는 제거

컨텍스트를 재사용하면서 컨텍스트 로드 시점에 동작해야하는 아래 설정 함수가 동작하지 못하고 이로 인해 두번째 테스트에서 redis에 연결을 시도하였으나 실패하면서 테스트가 정상 수행되지 못한 것이다

@JvmStatic
@DynamicPropertySource
fun registerRedisProperties(registry: DynamicPropertyRegistry) {
    registry.add("spring.data.redis.host") { redis.host }
    registry.add("spring.data.redis.port") { redis.getMappedPort(6379).toString() }
}

회고

이번 문제를 해결하면서 Testcontainer는 물론 통합 테스트를 작성함에 있어서 Spring의 테스트 동작 구조에 대해서 조금 들여다 볼 수 있었다

기본적으로 Spring의 동작 구조에 대해서는 계속적으로 접할 기회가 많고 학습의 기회가 많았는데, Spring의 테스트 메커니즘에 대해서는 다소 학습의 기회가 적지 않았나 하는 생각이 든다

1. 테이블 파티션

파티션 종류 : Range, 해시, 리스트

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

Range 파티션 : 가장 기초적인 방식으로 주로 날짜 컬럼을 기준으로 파티셔닝한다

파티션 테이블에 값을 입력하면 각 레코드를 파티션 키 값에 따라 분할 저장

검색할 때 : 조건절 비교 값(상수 또는 변수)에 똑같은 해시 함수를 적용함으로써 읽을 파티션 결정

해시 알고리즘 특성상 등치 조건 또는 IN-List 조건으로 검색할 때만 파티션 Pruning 작동

라이트 파티션

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

리스트 파티션은 순서와 상관없이 불연속적인 값의 목록에 의해 결정

사용자가 정의한 논리적인 그룹에 따라 분할한다

테이블 파티션

• 비파티션 테이블
• 파티션 테이블

인덱스 파티션

• 로컬 파티션 인덱스 : 각 테이블 파티션과 인덱스 파티션이 서로 1:1 대응 관계가 되도록 오라클이 자동으로 관리하는 파티션 인덱스
• 글로벌 파티션 인덱스
• 비파티션 인덱스

로컬이 아닌 파티션 인덱스는 모두 글로벌 파티션 인덱스이며, 테이블 파티션과 독립적인 구성(파티션키, 파티션 기준값 정의)을 갖는다

테이블과 인덱스 파티션 조합

로컬 파티션 인덱스

CREATE INDEX 마지막에 LOCAL옵션을 추가하면 로컬 파티션 인덱스를 생성할 수 있다

로컬 파티션 인덱스는 테이블과 1:1 대응 관계를 갖도록 오라클이 파티션을 자동으로 관리해준다

테이블 파티션 구성 변경(add, drop, exchange 등)하더라도 인덱스를 재생성할 필요가 없다

글로벌 파티션 인덱스

파티션을 테이블과 다르게 구성한 인덱스이다

구체적으로, 파티션 유형이 다르거나, 파티션 키가 다르거나, 파티션 기준값 정의가 다른 경우 비파티션 테이블이어도 인덱스는 파티셔닝할 수 있다

CREATE INDEX 마지막에 GLOBAL 키워드를 추가하고 파티션을 정의하면 된다

글로벌 파티션 인덱스는 테이블 파티션 구성(drop, exchange, split 등)하는 순간 Unusable 상태로 바뀌므로 재생성해줘야한다

로컬 인덱스 처럼 보이도록 인덱스를 구성해도 로컬 파티션이 아니므로 오라클이 인덱스를 자동으로 관리해주지 않는다

비파티션 인덱스

파티셔닝하지 않은 인덱스이다

일반 CREATE INDEX문으로 생성하면 비파티션 인덱스가 생성된다

비파티션 인덱스는 테이블 파티션 구성을 변경(drop, exchange, split 등)하는 순간 Unusable상태로 바뀌어서 인덱스를 재생성해줘야 한다

파티션 인덱스는 Prefixed, Nonprefixed로 나눌 수 있다

Prefixed : 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치

Nonprefixed : 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치하지 않음, 파티션 키가 인덱스 컬럼에 아예속하지 않은 때도 포함

로컬과 글로벌의 Prefixed, Nonprefixed 조합

• 로컬 Prefixed 파티션 인덱스
• 로컬 Nonprefixed 파티션 인덱스
• 글로벌 Prefixed 파티션 인덱스
• 비파티션 인덱스

중요한 인덱스 파티션 제약

Unique 인덱스를 파티셔닝하려면, 파티션 키가 모두 인덱스 구성 컬럼이어야 한다

파티션 키가 인덱스 구성 컬럼인 상태

주문 번호로 중복값이 있는지 확인하려면 인덱스 파티션을 모두 탐색해야한다. 주문일자로 나누어진 파티션 테이블 모두에 주문번호가 들어갈 수 있기 때문

다른 트랜잭션이 같은 주문번호로 다른 파티션에 입력하는 현상까지 막으려면 추가적인 Lock 매커니즘이 필요하다

Unique 인덱스를 파티셔닝할 때 파티션 키가 인덱스 컬럼에 포함되어야 DML 성능 보장하기 위한 필수 조건 이다

서비스 중단 없이 파티션 구조를 빠르게 변경하려면, PK를 포함한 모든 인덱스가 로컬 파티션 인덱스이어야한다

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

입력/수정/삭제하는 데이터 비중이 5%를 넘는다면, 인덱스를 그대로 둔 상태에서 작업하기 보다는 인덱스 없이 작업한 후 재생성하는 게 더 빠르다

다만 대량 데이터의 경우 인덱스를 재생성하는데 걸리는 시간이 만만치 않으므로 이에 대해 고민하고 수행해야한다

파티션 Exchange를 이용한 대량 데이터 변경

테이블이 파티셔닝돼 있고 인덱스가 로컬 파티션인 경우, 수정된 값을 갖는 임시 세그먼트를 만들어 원본 파티션과 바꿔치기하는 방법이 있다

1. 임시 테이블을 생성한다. 가능하면 nologging 모드로 생성

2. 거래 데이터를 읽어 임시 테이블에 입력하면서 상태코드 값을 수정

3. 임시 테이블에 원본 테이블과 같은 구조로 인덱스 생성. 가능하면 nologging 모드로 생성

4. 2014년 12월 파티션과 임시 테이블을 Exchange

5. 임시 테이블을 Drop한다

6. 파티션을 logging 모드로 전환(nologging으로 작업한 경우)

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

인덱스를 실시간으로 관리하려면 어마어마한 시간이 소요

초대용량 테이블 인덱스를 모두 Drop 했다가 다시 생성하기도 쉽지 않다

Delete 가 느린 이유

1. 테이블 레코드 삭제
2. 테이블 레코드 삭제에 대한 Undo Logging
3. 테이블 레코드 삭제에 대한 Redo Logging
4. 인덱스 레코드 삭제
5. 인덱스 레코드 삭제에 대한 Undo Logging
6. 인덱스 레코드 삭제에 대한 Redo Logging
7. Undo에 대한 Redo Logging

파티션 Drop을 이용한 대량 데이터 삭제

테이블이 삭제 조건절 컬럼 기준으로 파티셔닝돼 있고 인덱스도 로컬 파티션이라면 삭제가 간단하다

파티션 Truncate를 이용한 대량 데이터 삭제

거래일자 조건에 해당하는 데이터를 일괄 삭제하지 않고 아래와 같이 또 다른 삭제 조건이 있는 경우

만약 조건을 만족하는 데이터가 소수이면, Delete 문을 그대로 수행하면 된다

조건을 만족하는 데이터가 다수라고 하면, 남길 데이터만 백업했다가 재입력하는 방식이 빠른다

1. 임시 테이블을 생성하고 남길 데이터만 복제

2. 삭제 대상 테이블 파티션을 Truncate

3. 임시 테이블에 복제해 둔 데이터를 원본 테이블에 입력

4. 임시 테이블 Drop

서비스 중단 없이 파티션 Drop 또는 Truncate를 위해서는 아래 조건을 만족해야한다

1. 파티션 키와 커팅 기준 컬럼이 일치해야 함 ex) 파티션 키와 커팅 기준 컬럼이 모두 ‘신청일자’
2. 파티션 단위와 커팅 주기가 일치해야 함 ex) 월 단위 파티션을 월 주기로 커팅
3. 모든 인덱스가 로컬 파티션 인덱스이어야 함 ex) 파티션 키는 ‘신청일자’, PK는 ‘신청일자 + 신청순번’ PK 인덱스는 삭제 기준(파티션 키) 컬럼이 인덱스 구성 컬럼이어야 로컬 파티셔닝 가능

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

비파티션 테이블일 때

비파티션 테이블에 손익분기점을 넘는 대량 데이터를 INSERT 하려면, 인덱스를 Unusable 시켰다가 재생성하는 방식이 더 빠를 수 있다

1. 가능하다면 테이블을 nologging 모드로 전환

2. 인덱스를 Unusable 상태로 전황

3. 가능하다면 Direct Path Insert 방식으로 대량 데이터를 입력

4. 가능하다면 nologging 모드로 인덱스를 재생성

5. nologging 모드로 작업했다면 logging 모드로 전황

파티션 테이블일 때

초대용량 테이블 인덱스 재생성이 부담되기 때문에 인덱스를 그대로 둔 상태로 INSERT 한다

테이블이 파티셔닝돼 있고, 인덱스도 로컬 파티션이라면 파티션 단위로 인덱스를 재생성할 수 있어 좋다

1. 가능하다면 테이블의 파티션을 nologging 모드로 전환

2. 작업 대상 테이블 파티션과 매칭되는 인덱스 파티션을 Unusable로 전환

3. 가능하다면 Direct Path Insert 방식으로 대량 데이터 입력

4. 가능하다면 nologging 모드로 인덱스 파티션을 재생성

5. nologging 모드로 작업했다면, 작업 파티션을 logging 모드로 전환

Lock과 트랜잭션 동시성 제어

Lock은 데이터베이스의 특징을 결정짓는 가장 핵심적인 매커니즘이다

오라클 Lock

오라클은 공유 리소스와 사용자 데이터를 보호할 목적으로 DML Lock, DDL Lock, 래치, 버퍼 Lock, 라이러리 캐시 Lock/Pin 등 다양한 종류의 Lock을 사용

DML 로우 Lock

두 개의 동시 트랜잭션이 같은 로우를 변경하는 것을 방지

하나의 로우를 변경하려면 로우 Lock을 먼저 실행해야 한다

DML 로우 Lock에는 배타적 모드를 사용하므로 UPDATE 또는 DELETE를 진행중인 로우를 다른 트랜잭션이 UPDATE나 DELETE 할 수 없다

INSERT 에 대한 로우 Lock 경합은 Unique 인덱스가 있을 때만 발생(Unique 인덱스가 있는 상황에서 두 트랜잭션이 같은 값을 입력할 때 발생)

MVCC 모델을 사용하는 오라클은 SELECT 문에 로우 Lock을 사용하지 않는다

따라서 DML 과 SELECT는 서로 진행을 방해하지 않고, SELECT 끼리도 서로 방해하지 않는다

DML 테이블 Lock

현재 트랜잭션이 갱신 중인 테이블 구조를 다른 트랜잭션이 변경하지 못하게 막기 위해 테이블 Lock이 DML Lock보다 먼저 설정된다

RS: row share(또는 SS : sub share)

RX: row exclusive (또는 SX: sub exclusive)

S: share

SRX: share row exclusive (또는 SSX: share/sub exclusive)

X: exclusive

INSERT, UPDATE, DELETE, MERGE 문을 위해 로우 Lock을 설정하려면 해당 테이블에 RX(=SX) 모드 테이블 Lock을 먼저 설정해야 한다

오라클에서 테이블 Lock은 해당 테이블에서 현재 어떤 작업을 수행중인지 알리는 일종의 Flag이다

대상 리소스가 사용중일 때

• Lock이 해제될 때 까지 기다린다.(select * from t for update)
• 일정 시간만 기다리다 포기한다.(select * from t for update wait 3)
• 기다리지 않고 작업을 포기한다.(select * from t for update nowait)

DML을 수행할 때 묵시적으로 테이블 Lock을 설정

설정을 통해서 NOWAIT 옵션 지정 가능

Lock을 푸는 열쇠는 커밋이다

블록킹 : 선행 트랜잭션이 설정한 Lock 때문에 후행 트랜잭션이 작업을 진행하지 못하고 멈춰있는 상태 ⇒ 커밋으로 해소 가능

교착상태 : 두 트랜잭션이 각각 특정 리소스에 Lock을 설정한 상태에서 맞은편 트랜잭션이 Lock을 설정한 리소스에 또 Lock을 설정하려고 진행하는 상황

WAIT : LGWR가 로그버퍼를 파일에 기록했다는 완료 메시지를 받을 때까지 기다린다.(동기식 커밋)

NOWAIT : LGWR의 완료 메시지를 기다리지 않고 바로 다음 트랜잭션을 진행(비동기식 커밋)

IMMEDIATE : 커밋 명령을 받을 때마다 LGWR가 로그 버퍼를 파일에 기록

BATCH : 세션 내부에 트랜잭션 데이터를 일정량 버퍼링했다가 일괄 처리

트랜잭션 동시성 제어

비관적 동시성 제어 : 사용자들이 같은 데이터를 동시에 수정할 것으로 가정, Lock은 첫 번째 사용자가 트랜잭션을 완료하기 전까지 다른 사용자들이 같은 데이터를 수정할 수 없다

적립포인트를 계산하는 동안 고객의 실적정보를 변경하면 문제가 될 수 있다

비관적 동시성 제어로 데이터 잘못 갱신되는 문제 방지

해당 방식을 통해 Lock을 무한정 기다리지 않을 수 있다

낙관적 동시성 제어 : 사용자들이 같은 데이터를 동시에 수정하지 않을 것으로 가정, 데이터를 읽을 때 Lock을 설정하지 않는다

그렇기 때문에 데이터 갱신 여부를 판단하는 로직이 필요하다

낙관적 동시성 제어에서도 UPDATE 전 for update를 수행함으로 써 Lock에 대한 예외처리를 하면, 다른 트랜잭션이 설정한 Lock을 기다리지 않게 구현 가능

* for update 사용을 두려워하지 말자

두 테이블에 대해 로우 Lock 발생

주문수량이 있는 테이블에 대해서만 로우 Lock 발생

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

채번 테이블 : 각 테이블 식별자의 단일 컬럼 일련번호 또는 구분 속성별 순번을 채번하기 위해 별도 테이블을 관리하는 방식. 채번 레코드를 읽어서 1을 더한 값으로 변경하고, 그 값을 새로운 레코드를 입력하는 데 사용

장점:

- 범용성이 좋다

- INSERT 과정에 중복 레코드 발생에 대비한 예외(Exception) 처리에 크게 신경쓰지 앟낭도 되므로 채번 함수만 잘 정의하면 편리하게 사용할 수 있다

- INSERT 과정에 결변을 방지할 수 있다

- PK가 복합컬럼일 때도 사용할 수 있다

단점:

- 다른 채번방식에 비해 성능이 않 좋다(채번 레코드를 변경하기 위한 로우 Lock 경합 때문)

PL/SQL의 자율 트랜잭션 기능을 이용하면 메인 트랜잭션에 영향을 주지 않고 서브 트랜잭션에서 일부 자원만 Lock을 해제할 수 있다

자율 트랜잭션으로 선언하면, 그 내부에서 커밋을 수행해도 메인 트랜잭션은 커밋하지 않은 상태로 남는다

시퀀스 오브젝트

장점:

- 성능이 빠르다

- INSERT 과정에 중복 레코드 발생에 대비한 예외처리에 크게 신경 쓰지 않아도 된다

시퀀스는 오라클 내부에서 관리하는 채번 테이블이다

따라서 채번 과정에서 Lock이 발생한다

캐시에 의해 빠른 성능을 제공

시퀀스 Lock

- 로우 캐시 Lock : 로우 캐시는 공유 캐시의 구성요소이므로 액세스 시 직렬화 필요

- 시퀀스 캐시 Lock : SQ Lock 이라고 부르며, 시퀀스 캐시는 공유 캐시에 위치하기 때문에 Lock 필요

- SV Lock : RAC 환경에서 시퀀스 캐시에 대한 직렬화(ORDER 옵션 사용), 단, 성능상 손실 순환 옵션을 사용하면 최대값에 도달하면 1부터 다시 시작하도록 할 수 있다

MAX + 1

최대 값을 구하고 거기에 1을 더해서 사용

장점:

- 시퀀스 또는 채번 테이블을 관리하는 부담이 없다

- 동시 트랜잭션에 의한 충돌이 많지 않으면, 성능이 매우 빠르다

- PK가 복합 컬럼인 경우, 즉 구분 속성별 순천을 채번할 때도 사용할 수 있다

단점:

- 레코드 중복에 대비한 세밀한 예외처리 필요

- 다중 트랜잭션에 의한 동시 채번이 심하면 시퀀스보다 성능이 많이 나빠질 수 있다(레코드 중복에 의한 로우 Lock 때문)

채번 방식에 발행하는 Lock 경합 요소

Lock 경합 요소를 고려한 채번 방식 선택 기준

1. 다중 트랜잭션에 의한 동시 채번이 많지 않으면 아무거나 사용해도 된다. 다만, 채번 테이블이나 시퀀스 오브젝트 관리 부담을 고려하면 MAX + 1 을 선택하는게 좋다

2. 다중 트랜잭션에 의한 동시 채번이 많고 PK가 단일컬럼 일련번호라면, 시퀀스 방식

3. 다중 트랜잭션에 의한 동시 채번이 많고 PK 구분 속성에 값 종류 개수가 많으면, 중복에 의한 로우 Lock 경합 및 재실행 가능성이 낮다 시퀀스 보다 MAX + 1 방식이 구조적으로 좋다

4. 다중 트랜잭션에 의한 동시 채번이 많고 PK 구분 속성에 값 종류 개수가 적으면, 순환 옵션을 가진 시퀀스 오브젝트 활용 고려

시퀀스 보다 좋은 솔루션

한 개 이상의 구분 속성과 함께 뒤쪽에 순번 대시 입력일시 를 두는 방식으로 PK 구조 설계

⇒ 채번의 과정에서 생기는 Lock 이슈를 거의 해소할 수 있다

중복 가능성이 매우 희박하지만 예외처리는 필요하다

인덱스 블록 경합

INSERT 성능이 너무 빠른 경우 문제, Right Growing 인덱스에서 가장 흔히 볼 수 있다

인덱스는 키순으로 정렬된 상태를 유지하며 값이 입력된다

따라서 순차적으로 값이 증가하는 단일컬럼 인덱스를 Right Growing 인덱스라고 부르는데, 이 인덱스는 입력하는 값이 달라도 같은 블록을 갱신하려는 프로세스 간 버퍼 Lock 경합이 발생할 수 있다

구분 속성의 값 종류 개수가 적을 수록 경합도 심하다

일반적인 해소 방법은 인덱스를 해시 파티셔닝 하는 것

해시 파티셔닝하면 값이 순차적으로 증가하더라도 해시 함수에 의해 리턴 값이 결정 되므로 경합을 줄일 수 있다

인덱스를 리버스 키 인덱스로 전환하는 방법도 고려될 수 있다

글로벌 시퀀스 + 세션 시퀀스

세션시퀀스는 INSERT를 수행할 때마다 호출된다

글로번 시퀀스와 세션 시퀀스를 조합해서 사용하면 프로세스가 서로 다른 리프 블록에 값을 입력하므로 인덱스 경합이 발생하지 않는다

Scaleable 시퀀스(18C ^)

시퀀스를 생성할 때 SCALE 또는 SCALE EXTEND 옵션을 지정하면 사용할 수 있다

Scaleable 시퀀스에서 nextval을 호출하면, 인스턴스 번호, 세션 ID, 시퀀스 번호를 조합한 번호를 반환한다

EXTEND 생략 시 VAL3의 리딩 제로 없는 숫자를 반환

EXTEND : 0001, 0002, 0003

no EXTEND : 1, 2, 3

DML 성능에 영향을 미치는 요소

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

테이블에 레코드를 입력 시 인덱스에도 입력 필요.

테이블의 경우 Freelist(테이블별 여유공간 블록 목록) 통해 블록 할당

인덱스의 경우 정렬된 자료구조로 수직적 탐색을 통해 입력할 블록을 찾아야함

DELETE 도 마찬가지

UPDATE의 경우 해당되는 인덱스만 찾아서 변경해주면 된다. 다만, 테이블에서 한건을 변경할 때 마다 인덱스에는 두개의 오퍼레이션이 발생(삭제 후 삽입)

DML에서 인덱스 개수가 미치는 영향이 크다.

핵심 트랜잭션 테이블에서 인덱스의 개수를 하나라도 줄이면 TPS(Transcation Per Second)는 그만큼 향상된다.

인덱스가 존재할 때와 그러지 않을 때의 insert 시간 비교

왼쪽 인덱스가 없을때, 가운데 인덱스가 2개 존재할 때, 오른쪽 일반 인덱스와 PK 제약을 모두 제거한 상태

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

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

PK, FK 제약은 Check, Not Null 제약보다 성능에 더 큰 영향을 미친다

Check, Not Null은 정의한 제약 조건을 준수하지는만 확인하면 되지만, PK, FK 제약은 실제 데이터를 조회해 봐야 하기 때문

Update, Delete 문의 실행 계획이 Select 문의 실행 계획과 다르지 않으므로 인덱스 튜닝 원리를 그대로 적용할 수 있다. 조인 튜닝 또한 동일하게 적용 가능하다

인덱스도 select와 같이 적용된다

Redo 로깅과 DML 성능

데이터파일과 컨트롤 파일에 가해지는 모든 변경사항을 Redo 로그에 기록한다

Redo 로그는 트랜잭션 데이터가 어떤 이유에서건 유실되었을 때, 트랜잭션을 재현함으로써 유실 이전 상태로 복구하는데 사용된다

DML이 수행될 때마다 Redo 로그를 생성하므로 Redo 로깅은 DML 성능에 영향을 미친다

Redo 로그 목적

• Database Recovery : Media Fail 발생 시 데이터베이스를 복구하기 위해 사용(온라인 Redo 로그를 백업해 둔 Archived Redo 로그 이용) → Media Recovery
• Cache Recovery (Instnace Recovery 시 roll forward 단계) : 캐시에 저장된 변경사항이 디스크 상의 데이터 블록에 기록되지 않은 상태로 인스턴스가 비정상적으로 종료되면, 그때까지의 작업내용을 모두 잃게 된다.(버퍼캐시는 휘발성) 트랜잭션 데이터 유실에 대비하기 위해 Redo 로그를 남긴다
• Fast Commit : 변경된 메모리 버퍼 블록을 디스크 상의 데이터 블록에 반영하는 작업은 랜덤 액세스 방식으로 이루어지므로 매우 느리지만 로그는 Append 방식으로 기록하므로 상대적으로 빠르다. 따라서 변경사항을 Append 방식으로 빠르게 로그 파일에 기록하고 변경된 메모리 버퍼 블록과 데이터 파일 블록 간 동기화는 DBWR을 이용해 나중에 배치 방식으로 일괄 수행한다. 아직 디스크에 기록되지 않았지만 Redo 로그를 믿고 커밋을 완료하는 것을 Fast Commit 이라고 한다

Undo 로깅과 DML 성능

Redo는 트랜잭션을 재현함으로써 과거를 현재 상태로 되돌리는 데 사용 Undo는 트랜잭션을 롤백함으로써 현재를 과거로 돌리는데 사용

Undo 데이터 사용 조건

• Transaction Rollback : 트랜잭션에 의한 변경사항을 최종 커밋하지 않고 롤백할 때 Undo 데이터 사용
• Transaction Recovery (Instance Recovery 시 rollback 단계) : Instance Crash 발생 후 Redo를 이용해 roll forward 단계가 완료되면 최종 커밋되지 않은 변경사항까지 모두 복구되므로 Undo 데이터를 사용하여 아직 커밋되지 않았던 트랜잭션들을 모두 롤백해야 한다
• Read Consistency : 읽기 일관성에 사용

MVCC(Multi Version Concurrency Control) 모델

current 모드: 디스크에서 캐시로 적재된 원본 블록을 현재 상태 그대로 읽는 방식

consistent 모드: 쿼리가 시작된 이후에 다른 트랜잭션에 의해 변경된 블록을 만나면 원본 블록으로부터 복사본(CR Copy) 블록을 만들고 거기에 Undo 데이터를 적용함으로써 쿼리가 시작된 시점으로 되돌려 읽는 방식

SCN(System Commit Number) 마지막 커밋이 발생한 시점정보

블록 SCN: 각 블록이 마지막으로 변경된 시점을 관리하기 위해 블록 헤더에 기록됨

쿼리 SCN: 모든 쿼리는 Global 변수인 SCN 값을 먼저 확인하고 읽기 작업 시작, 이를 쿼리 SCN이라고 한다

데이더를 읽다가 블록 SCN이 쿼리 SCN보다 더 큰 블록을 만나면 복사본 블록을 만들고 Undo 데이터를 적용함으로써 쿼리가 시작된 시점으로 되돌려서 읽는다

Undo 데이터가 다른 트랜잭션에 의해 재사용됨으로써 쿼리 시작 시점으로 되돌리는 작업에 실패할 때 Snapshot too old 에러 발생

Lock과 DML 성능

Lock은 DML 성능에 매우 크고 직접적인 영향을 미친다. Lock을 필요 이상으로 자주, 길게 사용하거나 레벨이 높을수록 DML 성능은 느려진다

트랜잭션 격리 수준

• Read Uncommited
• Read Commited (기본)
• Reapeatable Read
• Serializable

트랜잭션의 저장 과정 (Write Ahead Logging)

1. DML 문을 실행하면 Redo 로그버퍼에 변경사항 기록
2. 버퍼블록에서 데이터를 변경(추가,수정,삭제), 버퍼캐시에서 블록을 찾지 못하면 데이터파일에서 읽는 작업부터 수행
3. 커밋
4. LGWR 프로세스가 Redo 로그버퍼 내용을 로그파일에 일괄 저장
5. DBWR 프로세스가 변경된 버퍼블록들을 데이터파일에 일괄 저장

Log Force at Commit : 서버 프로세스가 커밋을 발행했다고 신보를 보낼때 LGWR 프로세스가 로그파일에 저장하므로 적어도 커밋시점에는 Redo 로그버퍼 내용이 로그파일에 기록됨

데이터베이스 Call

Parse Call : SQL 파싱과 최적화를 수행하는 단계. SQL과 실행계획을 라이브러리 캐시에서 찾으면, 최적화 단계 생각 가능

Execute Call: SQL을 실행하는 단계. DML은 이 단계에서 모든 과정이 종료. SELET 문은 Fetch 단계를 거친다

Fetch Call: 데이터를 읽어서 사용자에게 결과집합을 전송하는 과정. SELECT문에서만 나타나며 전송할 데이터가 많으면 Fetch Call 여러번 발생

User Call, Recursive Call

Call 이 발생하는 위치에 따라 User Call과 Recursive Call로 나뉜다

User Call: 네트워크를 통해 DBMS 외부로부터 인입되는 Call 3-Tier 아키텍처에서 User Call은 WAS(또는 AP서버) 서버에서 발생하는 Call

Recursive Call: DBMS 내부에서 발생하는 Call

커밋과 성능

: 커밋을 자주하는 경우 트랜잭션 원자성에 문제가 생긴다

: 매우 오래 걸리는 트랜잭션을 한 번도 커미하지 않고 진행하면 Undo 공간 부족으로 인해 시스템에 부작용을 초래할 수 있다

: 적당한 주기로 커밋을 하는것이 좋다

커밋을 자주하면 수행 시간이 오래걸리게 되는데, 이는 User Call에서는 더 많은 시간이 걸리게 된다

One SQL

• Insert Into Select
• 수정가능 조인 뷰
• Merge 문

실무에서 One SQL로 구현이 쉽지 않다

Array Processing 기능을 활용하면 One SQL을 쓰지 않고도 Call 부하를 획기적으로 줄일 수 있다

PL/SQL

Java

인덱스 및 제약 해제를 통한 대량 DML 튜닝

동시 트랜잭션 없이 대량 데이터를 적재하는 배치 프로그램에서는 인덱스 및 제약 조건을 해제함으로써 큰 성능 개선 효과를 얻을 수 있다

PK 제약 및 인덱스 생성 (총 인덱스 2개)

PK제약과 인덱스 해제

1. PK제약에 Unique 인덱스를 사용한 경우

PK 제약 해제 및 인덱스 삭제

인덱스 비활성화

PK 제약 설정 및 인덱스 활성화

2.PK제약에 Non-Unique 인덱스 사용

PK제약을 Unusable한 상태에서는 데이터를 입력할 수 없다

PK 인덱스를 Drop 하지 않고 Unusable 상태에서 데이터를 입력하려면 PK 제약에 Non-Unique 인덱스를 사용하면 된다

Non-Unique 인덱스 생성 및 PK 제약으로 지정

PK 제약 해제 및 인덱스 비활성화

PK 제약 설정 및 인덱스 활성화

수정가능 조인 뷰

전통적인 UPDATE

전통적인 UPDATE문은 다른 테이블과 조인이 필요할 때 비율을 완전히 해소할 수 없다

수정가능 조인 뷰

수정가능 조인 뷰를 활용하면 참조 테이블과 두 번 조인하는 비효율을 없앨 수 있다

수정가능 조인 뷰에서는 1쪽 집합에 PK 제약을 설정하거나 Unique 인덱스를 생성해야 수정가능 조인뷰를 통한 입력/수정/삭제가 가능하다

키 보존 테이블

: 조인된 결과집합을 통해서도 중복 값 없이 Unique 하게 식별이 가능한 테이블 Unique한 1쪽 집합과 조인되는 테이블이어야 조인된 결과집합을 통한 식별이 가능

ORA-01779 오류 회피 : Group By 한 집합과 조인한 테이블은 키가 보존되므로 해당 문제를 해결할 수 있다

Merge 문

DW Merge 예시

Merge문은 Source 테이블 기준으로 Target 테이블과 Left Outer 방식으로 조인해서 조인 성공하면 Update, 실패하면 Insert 한다

Update 와 Insert를 선택적으로 처리 가능

where 절을 통한 추가 조건절 기술 가능

이미 저장된 데이터를 조건에 따라 지우는 기능 제공

Delete의 경우

Merge문을 수행한 결과가 Null 이면 삭제하지 않는다

조인에 성공한 데이터만 삭제할 수 있다

Direct Path I/O

DW, 배치 프로그램에서는 대량 데이터를 처리하기 때문에 버퍼캐시를 경유하는 I/O 메커니즘이 오히려 성능을 떨어뜨릴 수 있다

그래서 버퍼캐시를 경유하지 않고 곧바로 데이터 블록을 읽고 쓸 수 있는 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 … SELECT 문에 append 힌트 적용
• parallel 힌트를 이용해 병렬 모드로 INSERT
• direct 옵션을 지정하고 SQL Loader(sqlldr)로 데이터 적재
• CTAS 문 수행

Direct Path Insert 방식이 빠른 이유

1. Freelist를 참조하지 않고 HWM(High-Water Mark) 바깥 영역에 데이터를 순차적으로 입력한다
2. 블록을 버퍼캐시에서 탐색하지 않는다
3. 버퍼캐시에 적재하지 않고, 데이터파일에 직접 기록한다
4. Undo 로깅을 하지 않는다
5. Redo 로깅을 안하게 할 수 있다(nologging 모드 상태일 때)

Direct Path Insert 주의점

• exclusive 모드 TM Lock 발생
• Freelist를 조회하지 않고 HWM 바깥 영역을 입력하므로 테이블에 여유공간이 있어도 재사용하지 않는다

병렬 DML

INSERT는 append 힌트로 Direct Path Write 방식을 유도할 수 있지만, UPDATE, DELETE 는 불가능하다

병렬 DML로 Direct Path Write 방식 사용가능

병렬 DML 활성화 방법

아래 힌트 적용 시 대상 레코드 찾는 작업(insert는 select 쿼리, update/delete 는 조건절 검색)과 데이터 추가/변경/삭제 병렬로 진행

병렬 DML을 활성화 하지 않고 수행하면 대상 레코드를 찾는 작업만 병렬로 진행되어 추가/변경/삭제 는 QC(Query Coordinator)가 혼자 담당하므로 병목 발생

병렬 INSERT는 append 힌트를 적용하지 않아도 Direct Path Insert 방식을 사용한다

하지만 병렬 DML이 작동하지 않을 경우를 대비해 apeend 힌트를 같이 사용하는 것이 좋다

병렬 DML이 작동하지 않더라도 QC 가 Direct Path Insert를 사용하면 어느정도 만족할 만한 성능을 낼 수 있기 때문

enable_parallel_dml 힌트를 통해 활성화 가능(12c이후)

* 병렬 DML도 Direct Path Write 방식을 사용하므로 데이터 입력/수정/삭제 때 Exclusive 모드 TM Lock 발생

병렬 DML 동작 확인 방법

UPDATE 가 PX COORDINATOR 아래 쪽애 나타나면 UPDATE를 각 병렬 프로세스가 처리

UPDATE 가 PX COORDINATOR 위쪽에 나타나면 UPDATE를 QC가 처리

소트 수행 과정

기본적으로 PGA에 할당한 Sort Area에서 이루어진다. 다만 Sort Area가 다 차면 디스크 Temp 테이블스페이스를 활용한다

1. 메모리 소트 : 전체 데이터의 정렬 작업을 메모리 내에서 완료(Internal Sort)
2. 디스크 소트 : 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우(External Sort)

소트 대상 집합을 SGA 버퍼캐시를 통해 읽어들이고, 일차적으로 Sort Area에사 정렬 시도

양이 많으면 템프페이블 스페이스에 임시 세그먼트 만들어 저장

Sort Area가 찰 때 마다 Temp영역에 저장해 두는 단계 집할을 Sort Run이라고 부른다

소트 연산은 메모리 집약적일 뿐 아니라 CPU 집약적

소트 오퍼레이션

1. Sort Aggregate : 실제 데이터 정렬을 하지는 않지만 Sort Area 사용

2. Sort Order By

3. Sort Group By

Hash Group By

해싱 알고리즘을 사용해 버킷을 찾아 그룹별로 집계 항목 갱신

4. Sort Unique

5. Sort Join 소트 머지 조인 시

6. Window Sort

Union, Union All

Union은 중복 제거를 하므로 Union All보다 수행시간이 길다 다만 Union 하는 두 집합이 서로 상호 배타적이라고하면 Union 대신 Union All를 사용하는게 더 낫다

Exists

중복 레코드를 제거할 목적으로 Distinct를 사용하는데 Exists를 사용하게 되면 서브쿼리의 데이터 존재여부만 확인하게 되므로 부분 범위 처리가 가능해진다

인덱스를 이용한 소트 연산 생략

인덱스는 항상 키 컬럼 순으로 청렬된 상태를 유지한다

이를 활용하면 Order By, Group By 절이 있어도 소트 연산을 생략 할 수 있다

여기에 Top N 쿼리 특성을 결합하면 온라인 트랜잭션 처리 시스템에서 매우 빠른 응답 속도를 낼 수 있다

인덱스 선두 컬럼이 “종목코드 + 거래일시” 순으로 구성되지 않은 경우 소트 연산 생략 불가

인덱스 선두 컬럼이 “종목코드 + 거래일시” 순으로 구성 된 경우

실행 계획에 Sort Order By 항목이 없어졌다

최소/최대 값 구하기

인덱스는 정렬돼 있으므로 이를 이용하면 전체 데이터를 읽지 않고도 최소 또는 최대값을 쉽게 찾을 수 있다

인덱스 맨 왼쪽으로 내려가서 첫 번째 읽는 값이 최소값, 맨 오른쪽으로 내려가 읽는 첫 번째 값이 최대값이다

1. 조건절 컬럼과 MAX 컬럼이 모두 인덱스에 포함돼 있고, 인덱스 선두 컬럼이 모두 = 조건인 경우

인덱스가 DEPTNO + MGR + SAL 구성된 경우 이다

인덱스가 DEPTNO + SAL + MGR 인경우 DEPTNO 가 액세스 조건 MGR이 필터 조건으로 조건을 만족하는 레코드 한개를 찾았을 때 멈추게 된다

인덱스가 SAL + DEPTNO + MGR 인경우 DEPTNO, MGR이 필터 조건으로 인덱스 가장 우측에서 스캔 시작하여 조건을 만족하는 레코드를 하나 찾으면 멈춘다

인덱스가 DEPTNO + SAL인 경우 인덱스를 통해 MGR이 필터링 되지 않으므로 테이블에서 필터링이 되어야하므로 DEPTNO = 30 인 모든 레코드를 읽어 필터링 후 MAX값을 구한다. First Row StopKey 알고리즘이 동작하지 않는다

Top N 쿼리 이용 최소/최대 값 구하기

Top N 쿼리에 동작하는 Top N Stopkey 알고리즘은 모든 컬럼이 인덱스에 포함돼 있지 않아도 잘 동작한다

DEPTNO = 30을 만족하는 가장 오른쪽 레코드 부터 역순으로 스캔하면서 테이블 엑세스 하다가 MGR 조건이 일치하는 레코드를 찾았을 때 멈춘다

이력조회

복합한 이력 조회

해당 방법은 인덱스 컬럼을 가공했으므로 First Row Stopkey 알고리즘이 동작하지 않는다

해당 방법은 상태변경이력을 여러번 조회하는 비효율은 맀으나 First Row Stopkey 알고리즘이 잘 작동한다. 다만 컬럼이 많이진다면 SQL 문은 위 처럼 복잡해진다

Index_des 힌트 활용

인덱스를 역순으로 읽도록 index_desc 힌트를 사용하고, 첫번째 레코드에서 바로 멈추도록 rownum≤1조건절 사용

해당 쿼리의 경우 predicate pushing 쿼리 변환이 동작하여 인라인 뷰 안쪽으로 장비번호 조건절이 들어가면서 동작한다. Top N Stopkey 알고리즘이 작동하지 않아 성능이 느려질 수 는 있지만 쿼리 결과 집합은 보장된다

인덱스 활용 패턴은 랜덤 I/O 발생량 만큼 성능도 비례해서 느려지므로 대량 데이터 조회 시에는 윈도우 함수를 활용하는 것이 full scan과 해시조인을 이용하기 때문에 과거 이력까지 읽지만 인덱스를 이용하는 방식보다 빠르다

이처럼 KEEP 절을 활용할 수 도 있다

Sort Group By 생략

region이 선두 컬럼인 인덱스를 이용하면 Sort Group By 연산을 생략할 수 있다

인덱스가 정렬되어있으므로 순서대로 읽어 처리하다 다른 값을 만나게 되면 운반단위에 집계값을 저장하는 식으로 처리한다

인덱스를 이용해 Nosort 방식으로 Group By를 처리하면 부분 범위 처리가 가능해진다

소트 데이터 줄이기

1번의 경우 lpad에 의해 가동된 결과 집합을 sort area에 저장하므로 2번의 가공하지 않는 상태로 정렬을 하는 sql이 sort area를 더 적게 사용한다

1번의 경우 예수금원장의 모든 컬럼을 sort area에 저장하므로 계좌번호, 총예수금만 저장하는 2번 sql이 더 적응 sort area를 사용한다

Top N 쿼리를 사용하게 되면 N개의 sort area 공간을 사용하게 되므로 소트 부하가 적다

sort order by 오퍼레이션이 나타났지만 stopkey로 표시되면 top n 소트 알고리즘이 동작하였다

이 알고리즘이 동작하면서 sort area 사용량을 최소화 해준다

그 결과 sorts (disk) 항목을 사용하지 않았다

Top N 쿼리 아닐 시 소트 부하

해당 쿼리를 사용하면서 쿼리는 조금 간결해졌으나 실행계획에 stopkey가 사라지고 pr, pw가 증가한 것을 확인할 수 있다

이를 통해 Top N 소트 알고리즘이 동작하지 않았고, 정렬을 수행하는데 disk를 사용하였다

정렬을 위해 temp 테이블스페이스를 이용했다

분석함수에서 Top N 소트

윈도우 함수 중 rank나 row_number함수는 max 함수보다 소트 부하가 적다

Top N 소트 알고리즘이 동작하기 때문이다

max를 이용하는 경우 pr 13456, pw 8960 이 발생하였다

rank함수를 사용하는 경우 pr 40, pw 40으로 physical read, write가 발생하긴했으나 max 함수 사용시 보다 줄었고, 수행시간도 8초 가량 차이가 발생했다

NL(Nested Loop) 조인

NL 조인은 중첩 루프문과 같은 수행 구조를 사용

NL 조인은 Outer와 Inner 양쪽 테이블 모두 인덱스를 사용

Outer 쪽 테이블은 사이즈가 크지 않으면 인덱스를 이용하지 않을 수 있다(Table Full Scan 하더라도 한 번에 그치기 때문)

단, Inner 테이블은 인덱스를 사용해야 한다

Outer 에서 읽은 건수 만큼 Table Full Scan을 반복하기 때문이다

위 NL 조인 실행 계획을 보면 윗쪽 테이블 기준 아래쪽 테이블과 NL 조인 한다고 생각하면 된다

use_nl(alias) : alias를 Inner 테이블로 해서 NL 조인 하라는 hint

이 처럼 여러 테이블의 alias를 사용하면, 네개의 테이블을 NL 조인하는데 순서는 옵티마이저가 알아서 정하도록 하는 것이다

Leading 등을 활용해서 순서를 제어하는 것이 힌트 사용시 도움이 될 것 같다

위 쿼리의 경우 비교 순서는 2 ,3 1, 4 이다

ordered 로 사원, 고객 순서로 수행되고 index(e)로 인덱스를 이용한다

현재 조건문에 존재하는 컬럼이 포함된 인덱스는 사원_x1이므로 입사일자 조건이 먼저 수행되고 이어서 부서코드로 filter 하게된다

그리고 조인 컬럼을 포함하는 인덱스가 고객_x1이므로 해당 인덱스를 사용하여 NL 조인한 후 최종주문금액 조건으로 filter 하게 된다

NL 조인 튜닝 포인트

• 조인 액세스 횟수가 많을 수록 성능은 느려진다. 따라서, 조인 액세스 횟수가 Outer 테이블을 읽고 필터링한 결과 건수에 의해 결정

온라인 트랜잭션 처리(OLTP) 시스템에서 튜닝할 때는 일차적으로 NL 조인부터 고려하는 것이 올바른 순서

NL 조인 특징

• 랜덤 액세스 위주
• 한 레코드씩 순차적으로 진행
• 인덱스 구성 전략이 특히 중요

이런 특징 때문에 NL 조인은 소량 데이터를 주로 처리하거나 부분범위 처리가 가능한 온라인 트랜잭션 처리 시스템에 적합

NL 조인 확장 메커니즘

1. 전통적인 실행계획

2. 테이블 Prefetch 실행계획

nlj_prefetch, no_nlj_prefetch 힌트로 사용, 비사용 지정 가능

3. 배치 I/O 실행계획

: 배치 I/O 실행계획이 나타날 때는 결과 집합의 정렬 순서도 다를 수 있으므로 주의 필요

소트 머지 조인

조인 컬럼에 인덱스가 없을 때, 대량 데이터 조인이어서 인덱스가 효율적이지 않을 때 사용

SGA

공유 메모리 태시된 데이터를 여러 프로세스가 공유해서 사용. 직렬화 매커니즘 존재(Lock, 래치)

PGA

프로세스에 할당된 메모리 영역으로 프로세스만 독립적으로 사용하는 공간으로 래치 메커니즘 불필요

- SGA 버퍼캐시에서 읽을 때 보다 속도가 더 빠르다

- PGA 공간이 작아 데이터를 모두 저장할 수 없을 때는 Temp 테이블 스페이스를 이용한다

소트머지 기본 메커니즘

1. 소트 단계
   양쪽 집합을 조인 컬럼 기준으로 정렬
   소트 시 PGA의 Sort Area 에 저장, 공간 부족 시 Temp 테이블스페이스에 저장
2. 머지 단계
   정렬한 양쪽 집합을 서로 머지 NL 조인과 방식이 다르지 않음
   다만 소트 단계에 의해 정렬되어 있으므로 중간에 멈출 수 있음

use_merge(alias) 힌트로 소트머지 조인 유도 가능

ordered에 의해 순서대로 조인하고 고객 테이블 조인 시 소트 머지 조인 사용하도록 하는 힌트

소트 머지가 사용되는 상황

• 조인 조건식이 등치(=) 조건이 아닌 대량 데이터 조인
• 조인 조건식이 아예 없는 조인(Cross Join, 카테시안 곱)

소트 머지 조인 특징

• 조인 전 양쪽 집합 정렬
• NL 조인과 같은 방식으로 진행
• 실시간으로 인덱스 생성하는 것과 같음 (정렬)
• PGA 사용으로 직렬화 과정 없어 SGA에 비해 속도 우위

해시 조인

해시 조인 기본 메커니즘

• Build 단계 : 작은 쪽 테이블을 읽어 해시 테이블(해시 맵) 생성
• Probe 단계 : 큰 쪽 테이블을 읽어 해시 테이블을 탐색하면서 조인

use_hash(alias) 힌트로 유도

해시 테이블은 PGA Hash Area 에 저장 된다

해시 테이블이 너무 커서 PGA 에 담을 수 없으면, Temp 테이블 스페이스에 저장

Build와 Probe 단계에서 같은 해시 함수를 사용하므로 같은 값을 입력하면 같은 해시 값을 반환한다

따라서 해시 함수가 반환한 값에 해당하는 해시 체인만 스캔

해시 조인이 빠른 이유

PGA 영역을 사용하므로 래치 획득 과정이 없어 빠르게 탐색 가능하기 때문

!중요

해시 테이블에는 ROWID 만 있는 것이 아닌 SQL에 사용한 컬럼을 모두 저장 하고 있다

안그러면 ROWID로 다시 테이블 블록을 액세스해야 하기 때문

소트 머지 조인 보다 해시 조인이 빠른 이유는 사전 작업(정렬)의 과정과 해시 테이블(해시 맵)을 만드는 과정의 차이에서 발생하는 것

대용량 Build Input 처리

해시 조인하려는 두 테이블이 모두 대용량인 경우 인메모리 해시 조인이 불가능 하다

이럴 경우 분할 정복 방식으로진행된다

• 파티션 단계
  조인하는 양쪽 집합의 조인 컬럼에 해시 함수를 적용하고, 반환되는 해시 값에 따라 동적으로 파티셔닝
  독립적으로 처리할 수 있느 여러 개의 작은 서브 집합으로 분할함으로써 파티션 짝 생성
  Temp 테이블 스페이스를 사용하므로 인메모리 보다 성능이 많이 떨어짐
• 파티션 짝에 대해 하나씩 조인 수행
  파티션 하기 전 테이블 크기와 상관 없이, 파티션 짝 별로 작은 쪽은 Build Input으로 선택하여 해시 테이블(해시 맵) 생성

해당 sql의 경우 use_hash(e, c) 힌트로 해시 조인을 유도 했다

이때 use_hash만 사용했으므로 Build Input은 옵티마이저에 의해 결정된다

일반적으로 각 테이블에 대한 카디널리티를 비교하고 작은 테이블을 선택한다

Build Input을 직접 선택하고자 한다면 ordered 나 reading 힌트를 사용하면 된다

3개 이상의 테이블을 해시 조인 하는 경우

Build Input을 선택한 상황에서 순서를 바꾸고 싶을 때는 swap_join_inputs 힌트를 이용하여 순서를 바꿀 수 있다

swap_join_inputs : Build Input으로 지정하는 힌트

no_swap_join_inputs : Probe로 지정하는 힌트

조인 메소드 선택 기준

소량 데이터 조인 ⇒ NL 조인(use_nl)

대량 데이터 조인 ⇒ 해시 조인(use_hash)

대량 데이터 조인이지만 해시 조인으로 처리 불가능 할 때(조건식이 등치 조건이 아닐 때) ⇒ 소트 머지 조인(use_merge)

수행 빈도가 매우 높은 쿼리에 대한 기준

• (최적화된) NL 조인과 해시 조인 성능이 같으면 ⇒ NL 조인
• 해시 조인이 약간 더 빨라도 NL 조인
• NL 조인보다 해시 조인이 매우 빠른 경우 ⇒ 해시 조인

NL 조인을 먼저 고려해야 하는 이유

NL 조인에서 사용하는 인덱스는 영구적으로 유지되면서 다양한 쿼리에 공유 및 재사용하는 자료구조. 하지만 해시 테이블은 단 하나의 쿼리를 위해 생성 되고 조인이 끝나면 곧바로 소명하는 자료구조이기 때문

해시 조인 사용 조건

• 수행 빈도가 낮고
• 쿼리 수행 시간이 오리 걸리는
• 대량 데이터 조인할 때

⇒ 배치, DW, OLAP성 쿼리 특징

서브쿼리 조인

서브쿼리 변환

쿼리 변환은 옵티마이저가 SQL을 분석해 의미적으로 동일하면서 더 나은 성능이 기대되는 형태로 재작성하는 것

서브쿼리란

하나의 SQL문 안에 괄호로 묶은 별도의 쿼리 블록

• 스칼라 서브쿼리 : 한 레코드당 하나의 값을 반환하는 서브쿼리. 주로 SELECT-LIST에서 사용하지만 몇 가지 예외 사항을 제외하면 컬럼이 올 수 있는 대부분의 위치에 사용 가능
• 인라인 뷰 : FROM 절에 사용한 서브쿼리
• 중첩된 서브쿼리 : 결과집합을 한정하기 위해 WHERE 절에 사용한 서브쿼리. 특히, 서브쿼리가 메인쿼리 컬럼을 참조하는 형태를 “상관관계 있는 서브쿼리” 라고 한다.

오라클에서는 3가지로 분류 된다.

필터 오퍼레이션

서브쿼리를 필터 방식으로 처리

no_unnest 힌트를 통해 서브쿼리를 풀지말고 수행하도록 힌트 적용

필터 오퍼레이션은 기본적으로 NL 조인과 처리 루틴이 같다.

부분 범위 처리도 가능!

NL 조인과의 차이점은 필터는 캐싱 기능을 가진다는 것

필터 처리한 결과, 서브쿼리 입력 값에 따른 반환 값을 캐싱하는 기능

이 기능이 작동하므로 서브쿼리 수행 전 캐시부터 확인 한다

필터 서브쿼리는 메인쿼리에 종속적이므로 항상 메인쿼리가 드라이빙 집합이다

서브쿼리를 unnesting 하면 필터 오퍼레이션보다 더 좋은 실행 방법을 찾은 가능성이 크다

이때 서브쿼리에 rownum을 사용했다면 unnesting 이 되지 않는다

rownum은 강력한 unnesting 방지 공식

Pusing 서브쿼리

서브쿼리 필터링을 가능한 한 앞 단계에서 처리되도록 강제하는 기능

unnesting 되지 않은 서브쿼리에서만 동작

push_subq 힌트를 통해 유도 할 수 있으며, unnesting 되지 않은 서브쿼리에서만 동작하므로 no_unnest 힌트와 함께 쓰는 것 바람직하다

서브쿼리 필터링을 가능한 한 나중에 처리하고 싶으면, no_push_subq 힌트를 사용할 수 있다

뷰와 조인

최적화 단위가 쿼리 블록이므로 옵티마이저가 뷰 쿼리를 변환하지 않으면 뷰 쿼리 블록을 독립적으로 최적화 한다

인라인 뷰 안에 있는 당원에 거래한 모든 고객의 거래 데이터를 읽는다

merge 힌트로 인라인 뷰를 머징한 결과

해당 쿼리와 같이 변환되면서 인덱스를 이용하여 전월 이후 가입 고객에 대해서만 읽을 수 있게 변환 되었다

단점으로는 group by를 하고 나서야 데이터를 출력할 수 있으므로 부분 범위 처리가 불가능 하다

조인 조건 Pushdown

push_pred 힌트를 이용하여 유도할 수 있으며,

push_pred 힌트가 적용되면 불가능한 문법이지만 아래와 같은 쿼리로 옵티마이저가 최적화

따라서 가입일시 조건에 맞는 고객에 대해 당월 거래 내역만 읽어 group by를 수행하게 되고, 부분 범위 처리가 가능해진다

push_pred 힌트를 쓸 때는 merge로 유도되지 않도록 no_merge 힌트도 같이 쓰는 습관을 들이자

인라인 뷰에서 메인쿼리 테이블 컬럼을 참조하면 에러가 발생하는데, Lateral 로 인라인 뷰를 선언하게 되면 인라인 뷰에서 메인쿼리 테이블의 컬럼을 참조할 수 있다

다만, 기존 방법으로도 원하는 실행계획을 제어할 수 있으므로, 모종의 이유로 Pushdown이 안될 때 만 활용 하자

스칼라 서브쿼리의 특징

• 함수처럼 재귀적으로 실행되는 구조가 아니다
• 컨텍스트 스위칭 없이 메인쿼리와 서브쿼리를 한 몸체처럼 실행
• 처리과정에서 캐싱 작용이 일어난다(캐싱은 쿼리 단위로 이루어진다)
• PGA 메모리 공간을 사용한다

부작용

• 입력 값의 종류가 소수여야 효과적이다. 많으면 각 종류마다 캐싱이 일어나게되고 cpu, 메모리도 더 사용한다

스칼라 서브쿼리는 두 개 이상의 값 반환할 수 없다

스칼라 서브쿼리 unnesting

스칼라 서브쿼리는 병렬 쿼리에서는 사용될 수 없다

대량 데이터를처리하는 병렬 쿼리는 해시 조인으로 처리해야 효과적이다

스칼라 서브쿼리를 unnesting 하면 스칼라 서브쿼리 인데도 해시 조인 이 가능한 형태로 풀릴 수 있다