운영되는게 기적적인 블로그

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

오라클 Lock

공유 리소스와 사용자 데이터를 보호할 목적으로 다양한 종류의 Lock을 사용한다.

• DML Lock : 다중 트랜잭션이 동시에 액세스하는 사용자 데이터의 무결성 보호
  • 테이블 Lock
  • 로우 Lock
• DDL Lock
• 래치 : SGA에 공유된 각종 자료구조를 보호하기 위해 사용됨
• 버퍼 Lock : 버퍼 블록에 대한 액세스를 직렬화하기 위해 사용
• 라이브러리 캐시 Lock/Pin : 캐시에 공유된 SQL 커서와 PL/SQL 프로그램을 보호하기 위해 사용

DML Lock

• 로우 Lock : 두 개의 동시 트랜잭션이 같은 로우를 변경하는 것을 방지 -> 하나의 로우를 변경하려면 로우 Lock 먼저 설정
  • DML 로우 Lock 에는 베타적 모드를 사용하므로 UPDATE 또는 DELETE를 진행중인 로우를 다른 트랜잭션이 UPDATE 하거나 DELETE할 수 없다.
  • INSERT에 대한 로우 Lock 경합은 Unique 인덱스가 있을 때만 발생한다. 즉 Unique 인덱스가 있는 상황에서 두 트랜잭션이 같은 값을 입력하라고 할때 블로킹이 발생한다. 블로킹이 발생하면 후행 트랜잭션은 기다렸다가 선행 트랜잭션이 커밋하면 INSERT에 실패하고 롤백하면 성공한다. 두 트랜잭션이 서로 다른 값을 입려갛거나 Unique 인덱스가 아예 없으면 INSERT에 대한 로우 Lock 경합은 발생하지 않는다.
  • MVCC 모델을 사용하는 오라클은 SELETE 문에 로우 Lock을 사용하지 않는다. 오라클은 다른 트랜잭션이 변경한 로우를 읽을 때 복사본 블록을 만들어서 쿼리가 시작된 시점으로 되돌려서 읽는다. 변경이 진행 중인 로우를 읽을 때도 Lock이 풀릴때까지 기다리지 않고 복사본을 만들어서 읽는다. 따라서 SELECT 문에 Lock을 사용할 필요가 없다.
  • 즉 오라클에서는 DML과 SELECT는 서로 진행을 방해하지 않는다. 물론 SELECT끼리도 서로 방해하지않는다. DML끼리는 서로 방해할 수 있다.
  • MVCC 모델을 사용하지 않는 DbMS는 SELECT문에 공유 Lock을 사용한다. 공유 Lock끼리는 호환된다. 두 트랜잭션이 같은 Lock을 설정할 수 있다는 뜻이다. 공유 Lock과 배타적 Lock은 호환되지 않기 떄문에 DML과 SELECT가 서로 진행을 방해할 수 있다. 즉 다른 트랜잭션이 읽고 있는 로우를 변경하려면 다음 레코드로 이동할 때까지 기다려야하고, 다른 트랜잭션이 ㅂ녀경중인 로우를 읽으려면 커밋할때까지 기다려야한다.
  • DML 로우 Lock에 의한 성능 저하를 방지하려면 OLTP를 처리하는 주간에 Lock을 필요 이상으로 오래 유지않도록 커밋 시점을 조절해야한다. 그에 앞서 트랜잭션이 빨리 일을 마치도록 관련 SQL을 튜닝해야한다.
   
• DML 테이블 Lock (TM Lock)
  • 로우 Lock을 설정하기전에 테이블 Lock을 먼저 설정한다. 현재 트랜잭션이 갱신중인 테이블 구조를 다른 트랜잭션이 변경하지 못하게 막기 위해서다.
  • 로우 Lock은 항상 배타적 모드를 사용하지만 테이블 Lock에는 여러 가지 Lock 모드를 사용한다. 아래 표는 모드간 호환성을 정리한것이다. 

• -
  •  INSERT, UPDATE, DELETE, MERGE문을 위해 로우 Lock을 설정하려면 해당 테이블에 RX 모드 테이블 Lock을 먼저 설정해야한다.
  • SELECT FOR UPDATE문은 10gR1 -> RS, 10gR2이상 -> RX 모드 설정
  • RS, RX 간에는 어떤 조합으로도 호환되므로 SELECT FRO UPDATE나 DML문 수행시 테이블 Lock에 의한 경험은 발생하지 않는다. 같은 로우를 갱신하려고 할때만 로우 Lock에 의한 경합이 발생한다.
  • 즉 테이블 Lock은 테이블 Lock을 설정한 트랜잭션이 해당 테이블에서 현재 어떤 작업을 수행 중인지 알리는 일종의 푯말이다. 그래서 어떤 모드를 사용했는지에 따라 후행 트랜잭션이 수행할 수 있는 작업의 범위가 결정된다.

COMMIT

• 블로킹 : 선행 트랜잭션이 설정한 Lock 때문에 후행 트랜잭션이 작업을 진행하지 못하고 멈춰있는 상태 -> 해결하려면 커밋
• 교착상태 : 두 트랜잭션이 특정 리소스에 Lock을 설정한 상태에서 맞은편 트랜잭션이 Lock을 설정한 리소스에 또 Lock을 설정하려고 진행하는 상황을 말한다.

이 메시지를 받은 트랜잭션은 커밋 또는 롤백을 결정해야한다. 이것은 예외처리안하면 대기 상태 지속된다.

불필요하게 트랜잭션을 길게 정의하지 않도록 주의해야한다.

• Undo 세그먼트 고갈
• Undo 세그먼트 경합 유발

DML Lcok 때문에 동시성이 저하되지 않도록 적절한 시점에 커밋해야한다.

너무 커밋이 자주 발새앟면

• 서버 프로세스가 LGWR에게 로그 버퍼를 비우도록 요청하고 동기방식으로 기다리는 횟수 증가 -> 성능 느려짐
• 10gW2부터 제공하는 비동기식 커밋과 배치 커밋 활용 방안 검토

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

대량 데이터를 입력/수정/삭제할때는 인덱스를 Drop하거나 Unusable 상태로 변경하고서 작업하는 방법을 많이 활용한다. 손익 분기점은 5%정도로 본다. 즉 입력/수정/삭제하는 데이터 비중이 5%를 넘는다면, 인덱스를 그대로 둔 상태에서 작업하기보다 인덱스 없이 작업한 후에 재생성하는 것이 빠르다는 뜻이다.

근데 일부 건을 위해 대량 데이터를 인덱스를 재 생성하는 것은 부담된다.

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

테이블이 파티셔닝돼 있고 인덱스도 다행히 로컬 파티션이라면 수정된 값을 갖는 임시 세스먼트를 만들어 원본 파티션과 바꿔치기하는 방식이다.

1. 임시테이블을 생성한다. 할수있다면 nologging모드로 생성한다.
2. 거래 데이터를 읽어 임시 테이블에 입력하면서 상태코드 값을 수정한다.
3. 임시 테이블에 원본 테이블과 같은 구조로 인덱스를 생성한다. 할수있다면 nologging 모드로 생성한다.
4. 파티션과 임시테이블을 Exchange 한다.
5. 임시테이블을 Drop한다.
6. (nologging 모드로 작업했다면) 파티션을 logging 모드로 전환한다.

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

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

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

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

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

거래일자 조건에 해당하는 데이터를 일괄 삭제하지 않고 아래와 같이 또 다른 삭제 조건이 있는 경우. (상태코드 <> 'ㅇㅇㅇ' 상태코드 is null) 조건을 만족하는 데이터가 소수이면 DELETE문을 그대로 사용하면 된다.

만약 소수가 아니라 대다수이면 남길 데이터만 백업했다가 재입력하는 방식이 빠르다

1. 임시 테이블을 생성하고 남길 데이터만 복제한다.
2. 삭제 대상 테이블 파티션을 Truncate한다.
3. 임시테이블에 복제해 둔 데이터를 원본 테이블에 입력한다.
4. 임시 테이블을 Drop한다.

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

비파티션 테이블일때

파티션 테이블일때

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

테이블 파티션

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

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

파티션에는 Range, 해시, 리스트가 있다.

Range 파티션

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

위와 같은 파티션 테이블에 값을 입력하면 각 레코드가 파티션 키값에 따라 분할 저장하고 읽을때도 검색 조건을 만족하는 파티션만 골라 읽을 수 있어 이력성 데이터를 Full Scan 방식으로 조회할 때 성능을 크게 향상한다. 보관주기 정책에 따라 과거 데이터가 저장된 파티션만 백업하고 삭제하는 등 데이터 관리 작업을 효율적이고 빠르게 수행할 수 있는 장점도 있다.

파티션 테이블에 대한 SQL 성능 향상 원리는 파티션 Pruning(Elimination)에 있다. prune은 쓸데없는 가지를 치다라는 뜻을 가지는데 

즉 파티션 Pruning은 SQL 하드파싱이나 실행 시점에 조건절을 분석해서 읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외하는 기능이다.

위 SQL의 조건절을 만족하는 데이터는 1200만건중 25%에 해당하는 300만건이다. 인덱스로 건건이 랜덤 액세스하면 테이블 전체를 스캔하는 것보다 느리다. 하지만 테이블 전체를 스캔하자니 사이즈가 너무 크다.

오른쪽처럼 100만건 단위로 나눠서 저장하면, Full Scan 하더라도 전체가 아닌 일부 파티션 세그먼트만 읽고 멈출 수 있어 성능이 향상된다. 파티션 과 병렬 처리가 만나면 더 효가가 좋다.

화살표로 표현한 것처럼 파티션 테이블도 인덱스로 액세스할 수 있지만, 파티션 Pruning을 이용한 테이블 스캔보다 훨씬 느린다.

파티션도 클러스터 IOT와 마찬가지로 관련 있는 데이터가 흩어지지 않고 물리적으로 인접하도록 저장하는 클러스터링 기술에 속한다. 클러스터와 다른점은 세그먼트 단위로 모아서 저장한다는 것이다.

해시 파티션

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

파티션 개수만 사용자가 결정하고 데이터를 분산하는 알고리즘은 오라클 내부 해시함수가 결정

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

검색할 때는 조건절 비교 값에 똑같은 해시 함수를 적용함으로써 읽을 파티션을 결정한다. 해시 알고리즘 특성상 등치 조건 또는 IN-LIST 조건으로 검색할 때만 파티션 Pruning이 작동한다.

리스트 파티션

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

Range 파티션에선 값의 순서에 따라 저장할 파티션이 결정되지만, 리스트 파티션에서는 순서와 상관없이 불연속적인 값의 목록에 의해 결정된다.

해시 파티션과 비교하면 해시 파티션은 오라클이 정한 해시 알고리즘에 따라 임의로 분할하는 반면, 리스트 파티션은 사용자가 정의한 논리적인 그룹에 따라 분할. 업무적인 친화도에 따라 그룹핑 기준을 정하되, 될 수 있으면 각 파티션에 값이 고르게 분산되도록 해야한다

인덱스 파티션

• 테이블 파티션
  • 비파티션 테이블
  • 파티션 테이블
• 인덱스 (파티션, 비파티션)
  • 로컬 파티션 인덱스 : 
  • 글로벌 파티션 인덱스
  • 비파티션 인덱스

로컬 파티션 인덱스는 테이블 파티션과 인덱스 파티션이 서로 1:1 대응 관계가 되도록 오라클이 자동으로 관리하는 파티션 인덱스를 말한다. 로컬이 아닌 파티션 인덱스는 '모두' 글로벌 파티션 인덱스이며, 테이블 파티션과 독립적인 구성(파티션 키, 파티션 기준값 정의)을 갖는다.

로컬 파티션 인덱스

뒤에 LOCAL 옵션을 추가하면 된다.

각 인덱스 파티션은 테이블 파티션 속성을 그대로 상속받는다. 따라서 테이블 파티션 키가 주문일자라면 인덱스 파티션 키도 주문일자가 된다. 로컬 파티션 인덱스는 테이블과 정확히 1:1 대응 관계를 갖도록 오라클이 파티션을 자동으로 관리해준다. 테이블 파티션 구성을 변경하더라도 인덱스를 재생성할 필요가 없다. 변경 작업이 순식간에 끝나므로 피크 시간대만 피하면 서비스를 중단하지 않고도 작업할 수 있다. 즉 관리 편의성이 있다.

글로벌 파티션 인덱스

파티션을 테이블과 다르게 구성한 인덱스다. 파티션 유형이 다르거나, 파티션 키가 다르거나, 파티션 기준값 정의가 다른경우이다. 비파티션 테이블이어도 인덱스는 파티셔닝 할 수 있다.

테이블 파티션 구성을 변경 (drop, exchange, split)하는 순간 Unusable 상태로 바뀌므로 곧바로 인덱스를 재생성해줘야한다. 그동안 해당 테이블을 사용하는 서비스를 중단해야한다.

테이블과 인덱스가 정확히 1:1관계가 되도록 DBA가 파티션을 직접 구성할 수 있지만 그것은 로컬 파티션이 아니다. 모양만 로컬

비파티션 인덱스

말그대로 파티셔닝하지 않은 인덱스이낟.

테이블 파티션 구성을 변경하는 순산 Unusable 상태로 바뀌므로 곧바로 인덱스를 재생성해줘야한다. 그동안 해당 테이블을 사용하는 서비스를 중단해야 한다.

Prefixed vs Nonprefixed

• Prefixed : 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치한다.
• Nonprefixed : 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치하지 않는다. 파티션 키가 인덱스 컬럼에 아예 속하지 않을 때도 여기에 속한다.

중요한 인덱스 파티션 제약

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

주문일자로 파티셔닝한 테이블이 있을때 PK = 주문일자 + 주문번호, 인덱스는 로컬 파티션이다. PK 인덱스도 주문일자로 파티셔닝한 셈이므로 파티션 키가 인덱스 구성 컬럼이다.

이때 주문 레코드를 입력하면 중복 값 확인을 위해 한 파티션 인덱스만 탐색하면 된다.

PK = 주문 번호, 단일컬럼인 경우, 테이블 파티션 키인 주문일자가 인덱스 구성 컬럼이 아니다. 파티셔닝을 허용했다고 가정하면 중복값이 있는지 확인하려고 인덱스 파티션을 모두 탐색해야한다.

이렇게 Unique 인덱스를 파티셔닝할때 파티션 키가 인덱스 컬럼에 포함돼야한다는 조건은 DML 성능 보장을 위해 단연히 있어야 할 제약조건이다. 그리고 파티션 키 조건 없이 PK 인덱스로 액세스하는 수많은 쿼리 성능을 위해서도 필요하다.

문제는, 이 제약으로 인해 PK 인덱스를 로컬 파티션하지 못하면 파티션 Drop, Truncate, Exchange, Split, Merge 같은 파티션 구조 변경 작업도 쉽지 않다는 데 있다. 이들 작업을 하는 순간, PK 인덱스가 Unusable 상태로 바뀌기 때문이다. 곧바로 인덱스를 Rebuild 하면 되지만, 그동안 해당 테이블을 사용하는 서비스를 중단해야 한다. 서비스 중단 없이 파티션 구조를 빠르게 변경하려면, PK를 포함한 모든 인덱스가 로컬 파티션 인덱스이어야 한다.

데이터베이스 Call과 성능

SQL은 아래 세 단계로 나누어 실행된다.

• Parse Call : SQL 파싱과 최적화를 수행하는 단계, SQL과 실행계획을 라이브러리 캐시에서 찾으면, 최적화 단계는 생략할 수 있다.
• Execute Call : SQL을 실행하는 단계, DML은 이 단계에서 모든 과정이 끝나지만 SELECT 문은 Fetch 단계를 거친다.
• Fetch Call : 데이터를 읽어서 사용자에게 결과집합을 전송하는 과정으로, SELECT 문에서만 나타난다. 전송할 데이터가 많을 때는 Fetch Call이 여러 번 발생한다.

Call이 어디서 발생하느냐에 따라 User Call과 Recursive Call로 나눈다,

• User Call : 네트워크를 경유해 DBMA 외부로부터 인입되는 Call, User는 클라이언트 단에 위치한다. 하지만 DBMS 입장에서는 사용자는 WAS(AP서버)다. 3-Tier 아키텍처에서 "User Call은 WAS에서 발생하는 Call이다.
• Recurive Call : DBMS 내부에서 발생하는 Call이다. SQL 파싱과 최적화 과정에서 발생하는 데이터 딕셔너리 조회, PL/SQL로 작성한 사용자 정의 함수/프로시저/트리거에 내장된 SQL을 실행할 때 발생하는 Call이다.

어떤 Call이든 위에 Parse, Execute, Fetch Call단계를 거친다. 데이터베이스 Call이 많으면 성능이 느릴 수 밖에없다. 네트워크를 경유하는 User Call이 성능에 미치는 영향은 매우크다.

절차적 루프 처리

SOURCE 테이블에 레코드 100만개가 입력되어있다.

PL/SQL 프로그램에서 SOURCE 테이블을 읽어 100만번 루프를 돌면서 TARGET 테이블에 입력할때 루프를 돌면서 Call이 발생했지만. 네트워크를 경유하지 않는 Recursive Call이므로 29초가 걸렸다.

JAVA는 네트워크를 경유하는 User Call이므로 성능이 218초로 급격히 나빠졌다.

One SQL의 중요성

단 한번의 Call로 처리하니 1초가 걸렸다. 그러니 가급적 One SQL (데이터베이스에서 여러 데이터에 대한 처리를 하나의 SQL문으로 처리하는 것) 로 구현하려고 노력해야한다.

아래 구문 활용법을 잘 익혀야한다.

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

Array Processing 활용

절차적 프로그램을 One Sql로 구현하는 일은 쉽지않다. 복잡한 업무 로직을 포함하는 경우가 많기 때문이다. Array Processing 기능을 활용하면 One SQL로 구현하지 않고도 Call 부하를 획기적으로 줄일수있다.

만번에 한번씩 Insert 하도록 구현해서 백만번할 Call을 백번으로 줄였기 때문이다. Call을 단 하나로 줄이지 못해도 Array Processing을 활용해 10~100번 수준으로 줄일 수 있으면 성능효과가 좋다.

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

OLTP에서 인덱스와 무결성 제약 조건을 해제할 순없다. 하지만 동시 트랜잭션없이 대량 데이터를 적재하는 Batch 에서는 이런 기능을 해제해서 큰 성능개선 효과를 얻을 수 있다.

PK제약과 인덱스가 있는 상태에서 1분 19초 걸렸다.

PK와 인덱스를 해제하니 약 6초가 걸렸다.

PK 제약을 활성화하고 일반 인덱스를 재생성하니 8초걸렸다.

1. 첫 번째 테스트 (PK 제약과 Unique 인덱스 사용)

• SOURCE 테이블에 1,000만 건의 데이터 생성
• TARGET 테이블에 PK 제약조건과 인덱스 생성
• 1,000만 건 입력 시 약 1분 19초 소요

1. 두 번째 테스트 (PK 제약과 인덱스 해제)

• PK 제약 비활성화하고 인덱스를 Unusable 상태로 변경
• skip_unusable_indexes = true 설정
• 동일한 1,000만 건 입력 시 약 5.84초로 대폭 단축
• NOVALIDATE 옵션 사용으로 시간 추가 단축

1. 세 번째 테스트 (Non-Unique 인덱스 사용)

• PK 제약에 Non-Unique 인덱스를 사용
• 제약조건은 비활성화하되 인덱스는 유지
• 역시 빠른 성능을 보여줌

결론: 대량 데이터 입력 시 제약조건과 인덱스를 적절히 제어함으로써 성능을 크게 향상시킬 수 있음을 보여줌. 특히 제약조건 비활성화와 NOVALIDATE 옵션 사용이 효과적.

주요 성능 개선 포인트:

1. 제약조건 비활성화로 인한 검증 과정 생략
2. Unusable 인덱스 설정으로 인덱스 관리 부하 감소
3. NOVALIDATE 옵션으로 데이터 검증 과정 최소화
4. Non-Unique 인덱스 활용으로 유연한 구조 확보

실무 적용 시 고려사항:

• 데이터 무결성과 성능 사이의 균형 필요
• 대량 데이터 처리 시 임시적으로 제약조건을 해제하고 작업 후 다시 활성화하는 전략 유용
• NOVALIDATE 옵션 사용 시 데이터 정합성에 대한 별도 검증 필요할 수 있음
• 작업 완료 후 인덱스와 제약조건의 적절한 재활성화 필요

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

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

인덱스

테이블에 레코드를 입력하면, 인덱스에도 인력해야한다. 테이블은 Freelist를 통해 입력할 블록을 할당받지만, 인덱스는 정렬된 자료구조이므로 수직적 탐색을 통해 블록을 찾아야한다. (Freelist : 테이블마다 데이터 입력이 가능한 블록 목록을 관리하는 것)

이는 DELETE 할때도 마찬가지로, 레코드를 하나 삭제하면 인덱스 레코드를 모두 찾아서 삭제해야한다.

UPDATE할 때는 변경된 컬럼을 참조하는 인덱스만 찾아서 변경해주면 된다. -> 테이블은 한 건 변경할 때마다 인덱스에는 두 개 오퍼레이션이 발생한다. (ex. A->K로 변경하면 저장 위치도 달라지므로 삭제후 삽입하는 방식으로 처리한다.)

이렇게 DELETE, UPDATE할때 인덱스를 찾으므로 인덱스 개수는 DML 성능에 미치는 영향이 크다. 인덱스 늘때마다 TPS 증가

예시

create table source
as
select b.no, a.*
from (select * from emp where rownum <= 10) a
,(select rownum as no from dual connect by level <= 100000) b;

create table target
as
select * from source where 1 = 2;

alter table target add
constraint target_pk primary key(no, empno);

SOURCE 테이블에 레코드가 100만개 입력되어있고 TARGET 테이블은 비어있다.

TARGET 테이블에 PK 인덱스 하나만 생성하여 SOURCE 테이블을 읽어 레코드 100만개를 입력해보다.

그러니까 약 5분이 걸렸다.

인덱스 두개를 더 생성하고 100만건 생성하니 39초로 매우 느려졌다.

무결성 제약과 DML 성능

• 개체 무결성 : 테이블의 기본키(Primary Key)는 NULL 값을 가질 수 없으며 중복된 값을 허용하지 않음.
• 참조 무결성 : 외래키(Foreign Key)가 참조하는 테이블의 기본키와 일치하거나 NULL이어야 함
• 도메인 무결성 : 각 속성(컬럼)의 값은 해당 도메인에서 정의된 값만을 가질 수 있음
• 사용자 정의 무결성 (업무 제약 조건) : 업무 규칙이나 도메인 특성에 따라 사용자가 직접 정의하는 제약조건

DBMS에서 PK, FK, Check, Not Null 같은 제약을 설정하면 더 완벽하게 데이터 무결성을 지켜낼 수 있다.

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

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

이런 결과가 나온다.

조건절과 DML 성능

SELECT와 실행계획이 다르지 않다. 그래서 튜닝 원리를 그대로 적용할 수 있다.

서브쿼리와 DML 성능

SELECT 문과 실행계획이 다르지 않다.

Redo 로깅과 DML 성능

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

Redo 로그는 트랜잭션 데이터가 어떤 이유에서건 유실되었을때 트랜잭션을 재현하여 유실 이전 상태로 복수한다.

DML을 수행할 때마다 Redo 로그를 생성해야 하므로 Redo 로깅은 DML 성능에 영향을 미친다.

INSERT 작업에 대해 Redo 로깅 생략 기능을 제공하는 이유이다.

Redo 로그 용도

• Database Recovery : 온라인 Redo 로그를 백업해둔 Archived Redo 로그를 이용하여 데이터베이스 복구
• Cache Recovery (Instance Recovery 시 roll forward 단계) : 버퍼캐시는 휘발성이라 캐시에 저장된 변경사항이 비정상적으로 종료되면 작업내용을 잃는다. 이런 유실을 막기위함
• Fast Commit : 디스크 상 데이터 블록은 랜덤 액세스 방식으로 반영되서 느리다. 로그는 Append 방식으로 기록해서 빠르다. 그래서 ㅊ트랙잭션에 의한 변경사항을 우선 Append 방식으로 빠르게 로그 파일에 기록하고, 변경된 메모리 버퍼블록과 데이터파일 블록 간 동기화는 적절하게 (DBWR, Checkpoint)을 이용해 배치로 수행한다. 즉 버퍼블록에만 기록되고 디스크에 기록되지않았지만 Redo 로그를 믿고 빠르게 커밋을 완료했다는 의미

Undo 로깅과 DML 성능

트랙잭션을 롤백해서 현재상태를 과거 상태로 돌리는것

Redo는 트랜잭션을 재현하는 데 필요한 정보를 로깅하고, Undo에는 변경된 블록을 이전 상태로 되돌리는 데 필요한 정보를 로깅한다.

Undo 로깅도 DML 성능에 영향을 미친다.

• Transaction Rollback : 트랜잭션에 의한 변경사항을 최종 커밋하지 않고 롤뱃
• Transaction Recovery : 시스템이 셧다운된 시점에 아직 커밋되지 않은 트랙잭션 롤백
• Read Consistency : 읽기 일관성.

MVCC 모델

MVCC(Multi-Version Concurrency Control)는 데이터베이스의 동시성 제어를 위한 매커니즘

기본 개념:

• 데이터를 업데이트할 때 기존 데이터를 바로 변경하지 않고 새로운 버전의 데이터를 생성
• 각 트랜잭션은 데이터의 특정 시점 스냅샷을 봄
• 읽기 작업이 쓰기 작업을 차단하지 않음

• Current 모드 : 디스크에서 캐시로 적재된 원본 블록을 현재 상태 그대로 읽는 방식
• Consistent 모드 : 쿼리가 시작된 이후 다른 트랜잭션에 의해 변경된 블록을 만나면 원본 블록으로부터 복사본 블록을 만들고 거기에 Undo 데이터를 적용하므로써 쿼리가 '시작된 시점'으로 되돌려서 읽는 방식 (즉 원본 블록하나에 여러 복사본이 존재할수있음)
  • SCN : 시스템에서 마지막 커밋이 발생한 시점정보를 SCN이라는 글로벌 변수값으로 관리. 트랜잭션 커밋할 때마다 1씩증가하고 오라클 백그라운드 프로세서에 의해서도 조금씩 증가.

Lock과 DML 성능

Lock은 DML 성능에 매우 크고 직접적인 영향을 미친다. Lock을 필요 이상으로 자주, 길게 사용하거나 레벨을 높일수록 DML 성능은 느려진다. 그러나 적게 짧게 낮게 쓰면 데이터 품질이 나빠진다. 그래서 두 마리 토끼 잡으려면 동시성 제어가 필요하다.

동시성 제어는 동시에 실행되는 트랜잭션 수를 최대화하면서 데이터 무결성을 유지하기위해 노오력하는것

커밋과 DML 성능

커밋은 DML과 별개로 실행하지만, DML을 끝내려면 커밋까지 완료해야하므로 서로 밀접한 관계가 있다.

DML이 Lock에 의해 블로킹되는 경우, 커밋은 DML 성능과 직결된다. 

DML이 Lock을 푸는 열쇠가 커밋이다. 커밋은 가벼운 작업이 아니다.

 아래는 커밋의 내부 메커니즘이다.

1. DB 버퍼캐시 : 서버 프로세스는 버퍼캐시를 통해 데이터를 읽고 쓴다. 버퍼캐시에서 변경된 블록을 모아 주기적으로 데이터파일에 일괄기록하는 작업은 DBWR 프로세스가 맡는다. 배치 방식으로 처리한다.
2. Redo 로그 버퍼 : 버퍼캐시는 휘발성이므로 DBWR 프로세스가 Dirty 블록들을 데이터파일에 반영할 때까지 불안한 상태라고 생각할 수 있지만 버퍼캐시에 가한 변경사항을 Redo 로그에도 기록하므로 안정하다. 데이터가 유실되도 Redo로그를 이용해 복구가능.그런데 Redo 로그도 파일이다. Append 방식으로 기록하더라도 디스크 I/O는 느리다. Redo 로깅 성능 문제를 해결하기 위해 오라클은 로그버퍼를 이용한다. Redo 로그 파일에 기록하기 전에 먼저 로그버퍼에 기록하는 방식이다. 로그버퍼에 기록한 내용은 나중에 LGWR 프로세스가 Redo 로그 파일에 배치로 일괄 기록한다.
3. 트랙잭션 데이터 저장과정
   
   1. DML문을 실행하면 Redo 로그버퍼에 변경사항을 기록한다.
   2. 버퍼블록에서 데이터를 변경(추가/수정/삭제)한다. 버퍼캐시에서 블록을 찾지 못하면 데이터파일에서 읽는 작업
   3. 커밋
   4. LGWR 프로세스가 Redo 로그버퍼 내용을 로그파일에 일괄 저장
   5. DBWR 프로세스가 변경된 버퍼블록들은 데이터파일에 일괄 저장

  데이터를 변경하기전에 항상로그부터 기록한다. 서브 프로세스가 버퍼블록에서 데이터를 변경(2)하기 전에 Redo 로그버퍼에 로그를 먼저 기록(1)하는 이유다. DBWR 프로세스가 Dirty 블록을 디스크에 기록(5)하기 전에 LGWR 프로세스가 Redo 로그파일에 로그를 먼저 기록하는(4) 이유다. -> Write Ahead Logging

 DBWR, LGWR 프로세스는 주기적으로 깨어나 각각 Diry 블록과 Redo 로그버퍼를 파일에 기록한다. LGWR 프로세스는 서버 프로세스가 커밋을 발행했다고 신호를 보낼때도 깨어나서 활동시작한다. -> Log Force at Commit,

이렇게 Redo 로그가 휘발성 로그버퍼에 기록해도 트랜잭션 데이터를 지킬수있다.

      4. 커밋 = 저장버튼

         커밋은 문서 작업 도중 저장 버튼을 누르는 것과 같다. 서버 프로세스가 그때까지 했던 작업을 디스크에 기록하라는 명령어이다. 그래서 저장을 완료할때까지 서버프로세스는 다음 작업을 진행할 수 없다. 작업이 완료했다는 신호를 받아야 진행한다. Sync 방식

LGWR 프로세스가 Redo 로그에 기록하는 작업은 디스크 I/O작업이다. 그래서 커밋은 느리다.

트랜잭션을 논리적으로 잘 정의함으로써 불필요한 커밋이 발생하지 않도록 구현해야한다.

양이 너무 많은거시에요

소트 연산이 불가피하다면 메모리 내에서 처리를 완료할 수 있도록 노력해야한다.

Sort Area를 늘리는 것도 좋지만. Sort Area를 적게 사용할 방법부터 찾아야한다.

소트 데이터 줄이기

1번은 가공한 결과집합을 Sort Area에 담지만 2번은 가공하지 않는 상태로 정렬을 완료한다. 2번이 적게 든다

소트한 데이터량이 다르므로 2번이 훨씬 적게 했다.

Top N 쿼리의 소트 부하 경감 원리

인덱스로 소트 연산을 생략할 수 없을때, Top N 쿼리가 어떻게 작동할까

실행계획에 Sort Order By 가 있다. Table Full Scan 대신 종목코드가 선두인 인덱스를 사용할 수도 있지만, 바로 뒤 컬럼이 거래일시가 아니면 소트 연산을 생략할 수없으므로 Sort Order By가 나타난다.

Stopkey가 있는걸 보아 Top N 소트 알고리즘이 작동한다. -> 소트 연산 횟수와 Sort Area 사용량을 최소화한다.

1. 처음 10개 레코드를 거래일시 오름차순으로 정렬해서 배열에 다믄ㄴ다
2. 이후 읽는 레코드에 대해서는 배열 맨 끝에 있는 값과 비교해서 그보다 작은 값이 나타날때만 배열에서 다시 정렬한다. 기준에 맨 끝에 있던 값은 버린다.

이렇게 하면 오름차순으로 최소값을 갖는 열 개 레코드를 찾아낼 수 있다.

Top N 쿼리가 아닐때 발생하는 소트 부하

실행계획에서 Stopkey가 사라졌다. 알고리즘이 작동하지않고 pr과 pw도 발생했다.

분석함수에서의 Top N 소트

윈도우 함수 중 rank나 row_number 함수는 max함수보다 소트 부하가 적다. 알고리즘이 발동하기 때문

pr pw발생

max대신 rank를 사용하면

적게 발생한다.

인덱스는 항상 키 컬럼 순으로 정렬된 상태를 유지한다. -> 이것을 활용하면 SQL에 Order By 또는 Group By 절이 있어도 소트 연산을 생략할 수 있다. 여기서 Top N 쿼리 특성을 결합하면, 온라인 트랜잭션 처리 시스템에서 대량 데이터를 조회할 때 매우 빠른 응답 속도를 낼 수 있다. 최소값 또는 최대값도 빨리 찾을 수 있다.

Sort Order By 생략

인덱스 선두 컬럼을 '종목코드 + 거래일시' 순으로 구성하지 않으면 소트 연산을 생략할 수 없다.

종목 코드를 만족하는 레코드를 인덱스에서 모두 읽어야하며, 그만큼 랜덤 액세스 발생

부분범위 처리를 활용한 튜닝 기법

요즘 DB 어플리케이션은 대부분 3-Tier 환경 (WAS, AP 서버)에서 작동한다. -> 서버 리소스를 수많은 클라이언트가 공유하는 구조이므로 클라이언트가 특정 DB 커넥션을 독점할 수 없다. 단위 작업을 마치면 DB 커넥션을 바로 커넥션 풀을 반환해야 하므로 그 전에 쿼리 조회 결과를 클라이언트에게 모두 전송하고 커서를 닫아야한다. 부분범위 처리를 할 수 없다.

부분범위 처리는 쿼리 수행 결과중 앞쪽 일부를 우선 전송하고 멈추었다가 클라이언트가 추가 전송을 요청할때마다 남은 데이터를 조금씩 나눠 전송하는 방식이므로 3Tier환경에서는 Top N 쿼리로 인해 유효하다.

Top N 쿼리

전체 결과집합 중 상위 N개 레코드만 선택하는 쿼리

인라인 뷰로 정의한 집합을 모두 읽어 거래일시 순으로 정렬한 중간 집합을 우선 만들고 거기서 상위 열개 레코드를 취하는 형태로 보기엔 인덱스를 구성해 주더라도 중간집합을 만들어야 하므로 부분범위 처리는 불가능해 보인다.

 하지만 종목코드 + 거래일시 순으로 인덱스를 이용하면 옵티마이저는 소트연산을 생략하며, 인덱스를 스캔하다가 열개 레코드를 읽는 순간 멈춘다.

Sort Order By 대신 Count(STOPKEY)가 생긴다. 이는 조건절에 부합하는 레코드가 아무리 많아도 그 중 ROWNUM으로 지정한 건수만큼 결과 레코드를 얻으면 거기서 바로 멈춘다는 뜻이다. 이것을 Top N StopKey 알고리즘이라 한다.

페이징 처리

3 Tier 환경에서 대량의 결과집합을 조회할 때 페이징 처리 기법을 활용한다.

Top N 쿼리이므로 ROWNUM으로 지정한 건수만큼 결과 레코드를 얻으면 거기서 바로 멈춘다.

1. 부분범취 처리 가능하도록 SQL을 작성한다 (인덱스 사용 가능하도록 조건절을 구성하고, 조인은 NL 조인 위주로 처리 [룩업을 위한 작은 테이블은 해시 조인 Build Input으로 처리해도됨]하고, Order By절이 있어도 생략할 수 있도록 인덱스 구성)
2. 작성한 SQL 문을 페이징 처리용 표준 패턴 SQL Body 부분에 넣는다

페이징 처리 ANTI 패턴

위의 SQL 문은 ROWNUM 조건절이 불 필요해보인다.

그래서 이렇게 처리한다.

그런데 Order By 아래쪽 ROWNUM은 단순한 조건절이 아니다. Top N Stopkey알고리즘을 작동하게 하는 열쇠이다.

sql을 바꾸니 Stopkey가 없어졌다. 전체범위를 결국 처리했다.

최소값/최대값 구하기

최소값이나 최댓값 구하는 SQL에서 Sort Aggregate 오퍼레이션이 나타난다.

인덱스는 정렬돼 있으므로 이를 이용하면 전체데이터를 안읽어도 최소 최대를 찾을 수 있다.

전체 데이터를 읽지 않고 인덱스를 이용해 최소 또는 최대값을 구하려면, 조건절 컬럼과 MIN/MAX 함수 인자 컬럼이 모두 인덱스에 포함되어있어야한다. 즉 테이블 액세스가 발생하면 안된다.

인덱스를 DEPTNO + MGR + SAL 순으로 구성하면 이렇게 된다.

조건절 컬럼과 MAX 컬럼이 모두 인덱스에 포함되어있고 인덱스 선두 컬럼이 모두 =이다. FIRST ROW는 조건을 만족하는 레코드 하나를 찾았을 때 바로 멈춘다는 의미

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

ROWNUM <= 1 조건을 이용해 Top 1 레코드를 찾는다.

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

이력조회

일반 테이블은 각 컬럼의 현재 값만 저장하므로 변경되기 이전 값을 알 수 없다.

이력 테이블을 따로 관리해야한다.

과거 변경이력을 관리하기 위해 이력 테이블을 두지만, 일반적으로 이 테이블에는 현재 데이터도 저장한다. 그렇게 구현해야 변경 이력을 완벽히 재생할 수 있다.

특정 장비의 최종 상태코드가 'A2'인데 이 값으로 바뀐 날짜를 알고 싶다면, 이력 테이블에서 확인해야한다. 장비 테이블에도 최종 변경일자가 있지만 이 값을 이용할 순 없다. 이 값은 상태코드 이외의 컬럼, 즉 장비명 또는 장비구분코드가 바뀔 때도 갱신된다.

가장 단순한 이력 조회

이력 데이터 조회할때 First Row Stopkey 또는 Top N Stopkey 알고리즘이 작동될 수 있게 설계, 구현해야한다

.

위 SQL은 상태코드가 현재 값으로 변경된 날짜는 상태변경이력에서 조회하고 있다.

실행계획에서 First Row Stopkey알고리즘이 작동하고있다.

PK 인덱스가 장비번호 + 변경일자 + 변경순번으로 되어있기 때문

위 SQL은 인덱스 컬럼을 가공해서 First Row Stopkey가 작동하지 않는다.

이렇게 변경하면 코드가 복잡하고 3번조회하지만 효율은 좋다.

근데 상태코드 말고도 이력 테이블에서 읽어야할 컬럼이 더 많다면?

-> INDEX_DESC 힌트

인덱스를 역순으로 읽는 힌트 index_desc , 그리고 첫번째 레코드에서 바로 멈추도록 rownum <= 1

문제는 인덱스 구성이 완벽해야 쿼리가 잘 작동한다. 구성이 바뀌면 결과 집합에 문제가 생길수있다.

11c부터는

이렇게 작성가능

Predicate Pushing이라는 쿼리 변환 작동하였다.

상황에 따라 달라져야하는 이력 조회 패턴

일부 장비가 아닌 전체 장비, 최종이력이 아닌 직전 이력, 특정 산태로 변경한 최종 이력 등 상황에 따라 패턴도 달라져야한다.

특히, 전체 장비의 이력을 조회할 때는 인덱스를 이용한 Stopkey 기능 작동 여부가 튜닝 핵심요소가 아니다. 인덱스 활용 패턴은 랜덤 I/O 발생량 만큼 성능도 비례해서 느려지므로 대량 데이터를 조회할 때 결코 좋은 솔루션이 되지 못한다.

전체 장비의 이력을 조회할 때는 아래와 같이 윈도우 함수를 이용한다.

Full Scan과 해시 조인을 이용하기 때문에 인덱스보다 빠르다.

KEEP 절을 활용할 수 있다.

선분이력 모델

선분 이력 모델은 간단한 쿼리로 쉽게 이력을 조회할 수 있고 성능 측면 이점도 있다.

Sort Group By

그룹핑 연산에도 인덱스를 활용할 수 있다. 아래는 region이 선두인 인덱스를 이용했다.

Union, Minus, Distinct 연산자는 중복 레코드를 제거하기 위한 소트 연산을 발생시키므로 꼭 필요한 경우에만 사용해야하며 조인 방식도 잘 선택해야한다.

Union Vs Union All

Union을 사용하면 옵티마이저는 상단과 하단 두 집합 간 중복을 제거하려고 소트 작업을 수행한다.

Union All은 중복을 확인하지 않고 두 집합을 단순히 결합한다. (소트작업 없음) --> 고로 Union All을 사용해야한다.

* Union을 all로 바꾸려다 결과 집합이 달라질 수 있으므로 조심

위 SQL은 조건절에서 인스턴스 중복이 없으니까 Union All을 대신 써도 된다.. (상호베타적)

위 SQL은 조건절이 겹친다. 바꾸면 결제일자와 주문일자가 같은 결제 데이터가 중복해서 출력된다. (결제일자와 주문일자가 같을수도있음)

아래와 같이 변경하면 Union All 사용 가능

결제 일자가 Null 허용컬럼이면 'or 결제일자 is null' 추가

LNNVL 함수 써도된다.

Exists 활용

중복 레코드를 제거할 목적으로 Distinct 연산자를 종종 사용하는데, 이 연산자를 사용하면 조건에 해당하는 데이터를 모두 읽어서 중복을 제거해야한다.

부분 범위 처리 불가하고 많은 I/O 발생

이런 쿼리를 아래로 바꿔보자

Exists 서브쿼리는 데이터 존재 여부만 확인하면 되기 때문에 조건절을 만족하는 데이터를 모두 읽지않는다.

Distinct, Minus 연산자를 사용한 쿼리는 대부분 Exists 서브쿼리로 변환 가능

Minus를 Not Exists로 변환한 쿼리

조인 방식 변경

해쉬 조인이라서 Sort Order By가 나타났다.

NL 조인으로 변경하면 인덱스로 조인하므로 소트 연산을 생략할 수 있다. 지점ID 조건을 만족하는 데이터가 많고 부분범위 처리 가능한 상황에서 큰 성능 개선 효과를 얻는다.

정렬 기준이 조인 키 컬럼이면 소트 머지 조인도 Sort Order By 연산 생략할 수 있다.

SQL 수행 도중 가공된 데이터 집합이 필요할때, 오라클은 PGA, Temp를 활용한다. (소트 머지, 해시, 데이터 소트, 그룹핑)

소트 수행 과정

Sort Area에서 작업을 완료할 수 있는지에 따라 소트를 두 가지 유형을 나눈다

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

그림은 소트할 대상 집합을 SGA 버퍼캐시를 통해 읽어들이고, 일차적으로 Sort Area에서 정렬을 시도한다. 여기서 끝나면 최적이지만 양이 많으면 Temp 테이블스페이스에서 임시 세그먼트를 만들어 저장한다.(Sort Run : Temp 영역에 저장해 둔 중간 단계의 집합)

정렬된 최종 결과집합을 얻으려면 다시 Merge한다. Sort Run 내에서는 이미 정렬된 상태이므로 Merge는 어렵지 않다. 오름차순 정렬이면 각각에서 가장 작은 값부터 PGA로 읽어 들이다가 PGA가 찰 때마다 쿼리 수행 다음 단계로 전달하거나 클라이언트에게 전송

소트 연산은 메모리 집약적, CPU 집약적(데이터량이 많을때)이다.

디스크 I/O가 발생하는 것도 문제지만, 부분범위 처리를 불가능하게 함으로써 OLTP 환경에서 애플리케이션 성능을 저하시키는 주 요인이다. 될수있으면 소트를 발생시키지 않게, 불가피하다면 메모리내에서.

소트 오퍼레이션

• Sort Aggregate 
• Sort Order By
• Sort Group By
• Sort UniQue
• Sort Join
• Window Sort

Sort Aggregate

전체 로우를 대상으로 집계를 수행할 때 발생

실제로 데이터를 정렬하진 않고, Sort Area를 사용한다.

1. Sort Area 에 SUM, MAX, MIN, COUNT 값을 위한 변수를 하나씩 할당한다
2. 첫번째 레코드에서 읽은 SAL 값을 SUM, MAX, MIN 변수에 저장하고, Count 변수에는 1을 저장한다.
3. 레코드를 하나씩 읽어 내려가며
   1. SUM 변수에는 값을 누적하고
   2. MAX 변수에는 기존보다 큰 값이 나타날 때마다 값을 대체
   3. MIN 변수에는 기존보다 작은 값이 나타날때 마다 값을 대체
   4. COUNT 변수에는 NULL이 아닌 레코드를 만날때마다 1씩 증가
   5. 다 읽었으면 그 값을 그대로 출력하고 AVG는 SUM 값을 COUNT 값으로 나눈 값 출력
    

Sort Order By

데이터를 정렬할때 나타난다.

Sort Group By

소팅 알고리즘을 위해 그룹별 집계를 수행할때 나타남.

Hash Group By : 10gR2버전부터 도입
Group By 절을 뒤에 Order By 절을 명시하지 않으면 대부분 Hash Group By 방식으로 처리
읽는 레코드마다 Group By 컬럼의 해시 값으로 해시 버킷을 찾아 그룹별로 집계항목을 갱신



* 그룹핑 결과가 정렬 순서를 보장하지 않는다. 소팅 알고리즘을 사용해 그룹핑한 결과 집합은 논리적인 정렬 순서를 갖는 연결 리스트 구조이다. 정렬된 그룹핑 결과를 얻고자 한다면 실행계획에 Sort Group By라고 표시해도 반드시 Order By를 명시해야한다.

Order By 절을 추가한다고 해서 그룹핑과 정렬 작업을 각각 수행하지 않는다.

Sort UniQue

옵티마이저가 서브쿼리를 풀어 일반 조인문으로 변환하는 것을 서브쿼리 Unnesting이라고 한다. Unnesting된 서브쿼리가 M쪽 집합이라면 (1쪽 집합이라도 조인컬럼에 Unique 인덱스가 없으면) 메인쿼리와 조인하기 전에 중복 레코드부터 제거해야한다.

만약 PK/Unique 제약 또는 Unique 인덱스를 통해 Unnesting된 서브쿼리의 유일성이 보장된다면 Sort Unique 오퍼레이션은 생략된다.

---> 그러니까 일대다에서는 M쪽 집합이라면 M크기만큼 테이블을 여러번 나올수 있다는 거지

Union, Minus, Intersect 같은 집합 연산자나 Distinct를 사용할 때도 Sort Unique오퍼레이션이 나타난다.

10gR2부터는 Distinct연산에도 Hash Unique방식을 사용한다.

Sort Join

소트 머지 조인을 수행할 때 나타난다.

Window Sort

윈도우 함수를 수행할때 나타난다.

서브쿼리 변환이 필요한 이유

옵티마이저는 비용을 평가하고 실행계획을 생성하므로, 전달받은 SQL을 최적화에 유리한 형태로 변환하는 쿼리 변환부터 진행한다.

-> 얘땜에 옵티마이저 엔진이 점점 무거워지고 최적화에 소요되는 시간도 점점 늘고있다.

쿼리 변환 : 옵티마이저가 SQL을 분석해 같은 결과 집합을 생성하면서도 더 나은 성능이 기대되는 형태로 재작성하는 것

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

서브쿼리와 조인

메인쿼리와 서브쿼리 간에는 부모-자식이라는 중속적으로 계층적인 관계가 존재

서브쿼리는 메인쿼리에 종속되므로 단독으로 실행할 수 없다 -> 메인쿼리 건수만큼 값을 받아 반복적으로 필터링

• 필터 오퍼레이션 : 서브쿼리를 필터 방식으로 처리
• 서브쿼리 Unnesting : 중첩된 서브쿼리를 조인 형태로 변환하는 최적화 기법
• 서브쿼리 Pushing : 서브쿼리를 더 효율적인 위치로 이동시키는 방법

필터 오퍼레이션 (unnest, no_unnest)

no_unnest : 서브쿼리를 풀어내지말고 그대로 수행하라고 처리하게 지시하는 옵티마이저

옵티마이저는 기본적으로 서브쿼리를 조인으로 변환 (Unnesting)하여 처리하지만 no_unnest 사용시 필터 형태로 처리

메인 쿼리는 고객 테이블에서 한달 전보다 오래된 데이터를 조회

서브 쿼리는 거래 테이블에서 현재날짜 기준으로 거래가 있는 고객 확인

필터 (Filter) 오퍼레이션은 기본적으로 NL 조인과 처리 루틴이 같다. 실행계획에서 NESTED LOOPS로 치환하고 처리 루틴을 해석하면된다.

필터와 NL조인차이?

• 필터 : 조건이 맞는 데이터 하나만 찾으면 바로 다음으로 넘어감, 이전에 찾았던 같은 값은 캐시에 저장해두고 재사용, 항상 메인 쿼리 먼저 실행
• NL 조인 : 조건이 맞는 모든 데이터를 찾음, 캐싱안하고 매번 새로 찾음, 순어 변경 가능

고객 테이블: 홍길동, 김철수, 이영희
거래 테이블: 각 고객당 여러 건의 거래 있음

필터 사용시:
홍길동 -> 거래 있음 확인되면 바로 다음 고객으로
김철수 -> 거래 있음 확인되면 바로 다음 고객으로
이영희 -> 거래 있음 확인되면 끝

NL 조인 사용시:
홍길동 -> 모든 거래 다 찾음 
김철수 -> 모든 거래 다 찾음 
이영희 -> 모든 거래 다 찾음 

첫번째는 필터는 메인쿼리(고객)의 한 로우가 서브쿼리(거래)의 한 로우와 조인에 성공하는 순간 진행을 멈추고 메인쿼리의 다음 로우를 처리한다. -> 메인쿼리 결과집합이 서브쿼리 M쪽 집합 수준으로 확장되는 형상 (고객번호 중복)을 막을 수 있다.

두번째는 필터는 캐싱기능을 갖는다는 점. 이는 필터 처리한 결과, 즉 서브쿼리 입력 값에 따른 반환 값(true,false)을 캐싱하는 기능. 이 기능이 작동하므로 서브쿼리를 수행하기 전에 항상 캐시부터 확인한다. 캐시에서 true/false 여부를 확인 할 수 있다면 서브쿼리를 수행하지 않아도 되므로 성능을 높이는데 큰 도움이 된다.

마지막은 필터 서브쿼리는 일반 NL조인과 달리 메인 쿼리에 종속되므로 조인 순서가 고정된다. 항상 메인 쿼리가 드라이빙 집합이다.

캐싱은 쿼리 단위로 이루어지며 쿼리를 시작할 때 PGA 메모리에 공간을 할당하고 쿼리를 수행하면서 공간을 채워나가며, 쿼리를 마치는 순간 공간을 반환한다.

서브쿼리 Unnesting

unnest는 중첩된 상태를 풀어내라 -> 서브쿼리 unnesting은 메인과 서브쿼리 간의 계층구조를 풀어 서로 같은 레벨로 만들어준다는 의미에서 서브쿼리 Flattening이라고 한다.

서브쿼리를 그대로 두면 필터 방식을 사용할 수 밖에없지만 Unnesting하면 일반 조인문처럼 다양한 최적화 기법을 사용할 수 있다.

NL 세미 조인 : NL 조인과 같은 프로세스 -> 조인에 성공하는 순간 진행을 멈추고 메인 쿼리의 다음 로우를 계속 처리한다는 점, 10g부터 캐싱기능도 갖게되었음

그럼 그냥 필터인데? -> 필터는 항상 메인 쿼리가 드라이빙 집합이지만, Unnesting된 서브쿼리는 조인 순서 바꿀수있다.

서브 쿼리를 그대로 풀어서 조인하면 메인쿼리 결과집합(고객)이 서브쿼리 M(일대다)쪽 집합(거래) 수준으로 확장 [고객당 여러 거래가 있으므로 거래 테이블이 M쪽 집합, 조인 결과가 거래 건수만큼 중복되어 나온다는 의미]될 수 있으므로 서브쿼리 집합에 대한 Sort Unique 오퍼레이션부터 수행했음을 확인할 수 있다. 서브쿼리 집합에서 고객번호 중복을 제거하기 위해 쿼리를 아래와 같이 변환하였다.

서브쿼리 Pushing (push_subq, no_push_subq)

Unnesting되지 않은 서브쿼리는 항상 필터 방식으로 처리되며, 대게 실행계획 상에서 맨 마지막 단계에서 처리된다.

상품으로부터 주문 테이블로 1000번(3)의 조인 액세스가 있었고, 조인에 성공한 주문 데이터는 60000개(2)다. 조인 과정에서 38097(5) 블록을 읽었다. 60000개 조인 결과집합은 서브쿼리 필터링을 수행하고 나서 3000(1)개로 줄었다. 총 읽은 블록수는 38103(4)다. 대부분 I/O가 조인에서 발생 -> 서브쿼리 필터링을 먼저 처리해서 조인 단계로 넘어가는 로우 수를 크게 줄일수있다면 성능은 향상된다.

서브 쿼리를 필터링한 결과가 150건(1)이므로 주문 테이블과의 조인 횟수도 150번으로 줄었고 주문 데이터도 3000개(2)만 읽었다. 총 읽은 블록수도 1903(3)로 줄었다.

즉,

Pushing 서브쿼리는 서브쿼리 필터링을 가능한 앞 단계에서 처리하도록 강제한다. 이 기능은 Unnesting 되지않은 서브쿼리에만 작동한다. -> push_subq 힌트는 no_unnest 힌트와 같이 기술한다.

뷰와 조인

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

이 쿼리에서 뷰를 독립적으로 최적화하련니 당월 거래 전체를 읽어 고객 번호 수준으로 Group by하는 실행계획을 수립하고 고개테이블과 조인은 그 다음에 했다.

문제는 고객 테이블에서 '전월 이후 가입한 고객'을 필터링하는 조건이 인라인 뷰 바깥에 있다 -> 인라인 뷰 안에서는 당월 거래한 '모든' 고객의 거래 데이터를 일겅야한다.

실행계획을 보니, 고객 테이블을 먼저 읽고 인덱스를 이용해 전월 이후 가입만 고객만 읽고 거래 테이블과 조인할 때는 해당 고객들에 대한 당월 거래만 읽는다.

단점은 조인에 성공한 전테 집합을 Group By하고나서야 데이터를 출력할 수 잇으므로 부분범위 처리가 불가능하다 -> 전월 이후 가입한 고객이 매우 많고 당월 거래도 매우 많다면 NL조인이 별로 안좋다.

조인 조건 Pushdown : 11g 이후 조인 조건 Pushdown이 생김. 메인 쿼리를 실행하면서 조인 조건절 값을 건건이 뷰안으로 밀어 넣는 기능

이 방식을 사용하면 전월 이후 가입한 고객을 대상으로 '건건이' 당월 거래 데이터만 읽어서 조인하고 Group By를 수행한다. 중간에 멈출수도있다. 즉 부분 범위 처리가 가능한다. 뷰를 독립적으로 실행할 때처럼 당월 거래를 모두 읽지 않아도 되고, 뷰를 머징할 때 처럼 조인에 성공한 전체 집합을 Group. By 하지 않아도 된다.

push_pred는 no_merge힌트와 함께 써야한다.

스칼라 서브쿼리 조인

GET_DNAME 함수를 만들고 실행시키면 함수 안에 있는 SELECT 쿼리를 메인쿼리 건수만큼 '재귀적으로' 반복 실행한다.

이 스칼라 서브쿼리는 메인쿼리 레코드마다 정확히 하나의 값만 반환한다. 메인쿼리 건수만큼 DEPT 테이블을 반복해서 읽는다는 측면에서 함수와 비슷해 보이지만 함수처럼 재귀적으로 실행하지 않고 컨텍스트 스위칭없이 메인 쿼리와 서브쿼리를 한몸체처럼 실행한다.

스칼라 서브쿼리는 처리 과정에서 캐싱작용이 발생한다.

캐싱효과

스칼라 서브쿼리로 조인하면 오라클은 조인 횟수를 최소화하려고 입력 값과 출력 값을 내부캐시에 저장한다. 조인할 때마다 일단 캐시에서 일력값을 찾고 찾으면 저장된 출력 값을 반환한다. 캐시를 찾지못할때 조인 수행하고 결과는 버리지않고 캐시에 저장

조인할 데이터를 캐시에서 찾으면 조인 수행횟수 최소화

캐싱은 쿼리 단위로 이루어진다. 쿼리를 시작할 때 PGA메모리에 공간을 할당하고, 쿼리를 수행하면서 공간을 채워나가며, 쿼리를 마치는 순간 공간을 반환한다.

부작용

캐시 공간은 늘 부족한다. (8,9i기준 256개 엔트리 캐싱, 10g이후로는 입력과 출력 값 크기, _query_execution_cache_max_size 파라미터에의해 결정)

스칼라 서브쿼리 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 작을때 효과가 있다. 반대면 캐시를 매번 확인하는 비용때문에 오히려 성능이 나빠지고 CPU, 메모리만 더 쓴다.

거래 구분 코드로 20개 값이 존재한다 -> 캐시에 충분히 저장 -> 50000개 거래 읽는 동안 조인 액세스는 최초 한번만 읽어남 -> 성능 굳

고객 (100만명) -> 캐시 너무 많음 -> 메인쿼리에서 50000개 거래 읽는 동안 캐시 매번 탐색 하지만 거의 못찾음 -> 결국 조인 -> 오히려 캐시 탐색으로 성능 안좋아짐)

해결방안 -> 최근 3개월간 수백명 이내 일부 고객만 거래 발생시켜야함

매도계좌번호, 매수계좌번호가 무수히 많다면 스칼라 서브쿼리 캐싱효과 전혀 기대할수없고 오히려 성능 떨어뜨림

또한 메인 쿼리 집합이 매우 작을때 : 캐싱은 쿼리 단위로 이루어진다는 것은 쿼리 단위로 쓰고 버린다는 것. 메인쿼리 집합이 클수록 재사용성이 높아 효과가 좋지만 메인쿼리 집합이 작으면 캐시 재사용성이 낮다.

고객당 계좌가 많지 않기때문에 쓰지도 않을 캐시를 할당해서 값을 채웠다가 바로 버리는 비효율

2개 이상 값 반환

메인 쿼리가 실행계획 아래쪽에 있고 스칼라 서브쿼리 부분은 위쪽에 있다. 프로세싱은 NL조인과 같다. NL조인처럼 부분범위 처리도 가능한다. 다른점은 캐싱 효과가 있다.

근데 이렇게 하면 안된다.

이럴수도 없고

구하는 값을 문자열로 결합하고, 바깥쪽 액세스 쿼리에서 substr로 분리하는 방식도 있고 TYPE은 불편해서 잘 안씀

그럼 그냥 인라인 뷰를 사용하면 편하긴한데. 당월 거래 전체를 읽어야하거나 Group By 때문에 부분범위 처리가 안되는 문제가 있다.

11g이후로 조인 조건 Pushdown 기능이 있어 인라인 뷰를 잘 사용한다.

스칼라 서브쿼리 Unnesting

스칼라 서브쿼리도 NL 방식으로 조인하므로 캐싱 효과가 크지않으면 랜덤 I/O부담이 있다. 스칼라 서브쿼리를 일반 조인문으로 변환하거나 병렬 쿼리에선 될수있으면 서브쿼리를 사용하지 말아야한다. 병렬쿼리는 대량 데이터를 처리해야하므로 해시 조인으로 처리해야 효과적

_optimizer_unnest_scalar_sq

true : 스칼라 서브쿼리를 Unnesting할지를 옵티마이저가 결정

false : 사용자가 unnest 힌트로 유도