[자격증] SQLP 준비 - 3과목 SQL 고급활용 및 튜닝 (개정 전)

SQL 고급활용 및 튜닝

아키텍처 기반 튜닝 원리

- 서버간 연결상태를 유지하면 서버 자원을 낭비하지만 SQL을 수행할 때 마다 연결 요청을 반복하면 서버 생성과 해제를 반복하므로 성능에 좋지 않아 OLTP성 애플리케이션에서는 Connection Pooling 기법 활용

- 요청에 대한 부하는 쓰레드 기반보다 프로세스 기반이 더 심함

- 전용 서버 방식 (Dedicated Server) : 연결 요청을 할 때마다 서버 생성

- 공유 서버 방식 (Shared Server) : 직접 통신하지 않고 Dispatcher 프로세스를 거침

 

- 블록(페이지) : 데이터를 읽고 쓰는 단위

- 익스텐트 : 데이터파일에 공간을 할당하는 단위

- 익스텐트 내 블록들은 서로 인접하지만, 익스텐트끼리는 서로 인접하지 않음

- 한 익스텐트에 속한 페이지들을 여러 오브젝트가 나누어 사용할 수 있음 (SQL Server)

 

- Redo(Transaction) 로그 : DB 버퍼 캐시에 가해지는 모든 변경사항을 기록하는 파일

- Write Ahead Logging : 버퍼 캐시 블록을 갱신하기 전에 변경사항을 먼저 로그 버퍼에 기록해야하며, Dirty 버퍼를 디스크에 기록하기 전에 해당 로그 엔트리를 먼저 로그 파일에 기록해야 함

- Log Force at commit : 로그 버퍼를 주기적으로 로그 파일에 기록하되 늦어도 커밋 시점에는 반드시 기록

- Fast Commit : 갱신 내용이 메모리상의 버퍼 블록에만 기록된 채 디스크에 기록되지 않았지만 Redo 로그를 빋고 빠르게 커밋을 완료

- Delayed Block Cleanout : 변경된 블록을 커밋 시점에 바로 Cleanout하지 않고 나중에 해당 블록을 처음 읽는 세션에 정리 (오라클)

 

- DB 버퍼 캐시 : 데이터 파일로부터 읽어 들인 데이터 블록을 담는 캐시 영역

- /*+ append*/ 힌트 : Insert 시 DB 버퍼 캐시를 거치지 앟고 디스크에 직접 씀

- 클러스트링 팩터가 좋은 인덱스를 사용하면 Buffer Pinning 효과로 I/O를 줄임

- LRU(Least Recently Used) 알고리즘으로 Table Full Scan한 데이터 블록이 Index Range Scan한 데이터 블록보다 버퍼 캐시에 적게 머뭄

 

- Response Time = Service Time + Wait Time = CPU Time + Queue Time

 

- 소프트 파싱 (Soft Parsing) : SQL과 실행계획을 캐시에서 찾아 곧바로 실행

- 하드 파싱 (Hard Parsing) : SQL과 실행계획을 캐시에서 찾지 못해 최작화 과정을 거치고 나서 실행

- SQL Text가 서로 다르면 각각 하드 파싱은 일어나지만, 실행계획은 같을 수 있음

 

- 바인드 변수 (Bind Variable) : 파라미터 Driven 방식으로 SQL을 작성하는 방법이 제공되는데 SQL과 실행계획을 여러 개 캐싱하지 않고 하나를 반복 재사용하여 파싱 소요시간과 메모리 사용량을 줄임

- 입력 조건이 다양하여 조건절이 동적으로 바뀌는 경우 비교 값만큼은 바인드 변수를 사용하려 노력해야 함

- 바인드 변수를 사용하면 불필요한 하드파싱을 많이 일으키지 않음

- 바인드 변수를 사용해도 레코드 건수, 컬럼 값을 종류(NDV), Null 값 개수 등으로 테이블 통계정보를 활용함

 

- Static SQL : String형 변수에 담지 않고 코드 사이에 직접 기술한 SQL문

- Dynamic SQL : String형 변수에 담아서 실행하는 SQL문, 공유 메모리에 캐싱도니 SQL 공유 가능

 

- SELECT 문장을 수행할 땐 Parse, Execute, Fetch 순으로 Call이 발생 (각각 파싱 요청, 실행 요청, 결과 데이터 전송 요청)

- 대부분 I/O가 Fetch Call 단계에서 일어남

- Group By 결과집합을 만드는 과정에서의 I/O는 첫 번째 Fetch Call에서 발생

- INSERT, UPDATE, DELETE 문장에선 Fetch Call이 전혀 발생하지 않음

 

- Call Statistics 만으로는 Order By, Group By 연산의 포함 여부는 알 수 없음

 

- 부분범위처리가 가능하면 출력 대상 레코드가 많을 수록 쿼리 응답 속도도 그만큼 빨라짐

- Array 크기를 증가시키면 데이터베이스 Call 횟수 감소, 블록 I/O 횟수 감소

 

- 사용자 정의 함수 = User Defined Function = DB 저장형 함수

- 대용량 쿼리에 사용자 정의 함수를 사용하면 성능에 영향을 줌

- 그냥 거의 항상 성능을 떨어트림

- 함수를 실행할 때마다 컴파일하지는 않음

- 가상머신(VM) 상에서 실행되어 매번 바이트 코드를 해석하는 부하, 쿼리 문장의 조회 건수만큼 함수를 반복적으로 호출하는 부하, Recursive하게 반복 수행하게 되는 부하

 

- I/O 효율화 튜닝 방안

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

    - 전략적인 인덱스 구성

    - DBMS가 제공하는 다양한 기능 활용

    - 옵티마이저 힌트를 사용해 최적의 엑세스 경로로 유도

- Single Block I/O (Random I/O) : 한번의 I/O Call에 하나의 데이터 블록만 읽어 적재

- Multiblock I/O (Direct Path I/O, Sequential I/O) : I/O Call이 필요한 시점에 인접한 불록들을 같이 읽어 메모리에 적제

- 버퍼 캐시 히트율 : 전체 읽은 블록 중에서 메모리 버퍼 캐시에서 찾은 비율

- 한 쿼리 내에서 같은 블록을 반복적으로 엑세스하면 버퍼 캐시 히트율(BCHR)은 높아짐

- Full Scan할 때 (Multiblock I/O), 테이블이 작은 Extent로 구성되어 있을수록 더 많은 I/O Call

- 인덱스를 통한 엑세스시 (Single Block I/O), Extent 크기가 I/O Call 횟수에 영향을 미치지 않음

- 하나의 레코드를 읽더라도 해당 레코드가 속한 블록을 통째로 읽음

- Random I/O는 인덱스를 스캔하면서 테이블을 엑세스할 때만 사용

 

Lock과 트랜잭션 동시성제어

- Lock 경합에 의한 성능 저하 최소화 방안

    - 트랜잭션의 원자성을 훼손하지 않는 선에서 가능한 짧게 정의

    - 같은 데이터를 갱신하는 트랙잭션이 동시에 수행되지 않도록 설계

 

- 블로킹 : Lock 경합이 발생해 특정 세션이 작업을 진행하지 못하는 상태

- FOR UPDATE 구문을 대신 : HOLDLOCK, UPDLOCK

- 테이블 Lock : Row Share(RS), Row Exclusive(RX), Share(S), Share Row Exclusive(SRX), Exclusive(X)

- Exclusive 모드는 Append 모드로 입력한 SQL 실행 후 Lock 발생현황

- Update문 TM Lock은 Row-X(SX), TX Lock은 Exclusive

 

- 트랜잭션 ACID

    - 원자성(Atomicity) : 더 이상 분해가 불가능한 업무의 최소단위임

    - 일관성(Consistency) : 트랜잭션 실행으 ㅣ결과로 데이터베이스 상태가 모순되지 않음

    - 격리성(Isolation) : 트랜잭션이 실행 중 중간 결과는 다른 트랜잭션이 접근 불가

    - 영속성(Durability) : 트랜잭션이 실행을 성공적으로 완료하면 그 결과는 데이터베이스에 영속적으로 저장

 

- Dirty Read : 다른 트랜잭션에 의해 수정됐지만 아직 커밋되지 않은 데이터를 읽는 것

- Non-Repeatable Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 두 쿼리 결과가 다르게 나타나는 현상

- Phantom Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 없던 유령 레코드가 두 번째 쿼리에서 나타나는 현상

 

- Read Uncommitted : 트랜잭션에서 처리 중인 아직 커밋되지 않음 데이터를 다른 트랜잭션이 읽는 것을 허용, Dirty Read 발생

- Read Committed : 기본 트랜잭션 격리성 수준, 트랜잭션이 커밋되어 확정된 데이터만 다른 트랜잭션이 읽도럭 허용,  Non-Repeatable Read 발생

- Repeatable Read : 트랜잭션 내에서 쿼리를 두 번 이상 수행할 때, 첫 번째 쿼리에 있던 레코드가 사라지거나 값이 바뀌는 현상을 방지, Phantom Read 발생

- Serializable Read : 트랜잭션 내에서 쿼리를 두 번 이상 수행할 때, 첫 번째 쿼리에 있던 레코드가 사라지거나 값이 바뀌지 않음은 물론 새로운 레코드가 나타나지도 않음

 

- 비관적 동시성 제어 : 두 트랜잭션이 같은 데이터를 동시에 수정할 것이라고 가정하고 데이터를 읽는 시점에 Lock 설정

- 낙관정 동시성 제어 : 두 트랜잭션이 같은 데이터를 동시에 수정하지 않을 것이라고 가정하고 데이터를 읽는 시점에 Lock을 설정하지 않음

 

- Oracle은 Update 문장이 시작되는 시작을 기준으로 갱신 대상 레코드를 식별

- SQL Server는 이전 트랜잭션이 완료될 때까지 기다림

 

- 다중버전 동시성 제어(MVCC)

    - 읽기 일관성을 위해 Undo 세그먼트(버전 저장소)에 저장된 Undo(Snapshot) 데이터를 활용

    - 기본적으로 완벽한 문장 수준 읽기 일관성을 보장

    - 완벽한 트랜잭션 수준 읽기 일관성을 보장하진 않음

 

옵티마이저 원리

- 비용기반 옵티마이저(CBO) : 비용이란 SQL 수행 과정에 수반될 것으로 예상되는 I/O 일량, CPU 속도, 디스크 I/O 속도 등도 고려, 데이터베이스 Call은 수행 과정에 발생, 인덱스 종류 개수(NDV)는 CBO가 사용하는 통계정보

- 규칙기반 옵티마이저(RBO) : 휴리스틱 옵티마이저, 엑세스 경로를 평가하고 실행계획을 선택

 

- 전체범위 최적화(ALL_ROWS) : 쿼리의 최종 결과 집합을 끝까지 Fetch하는 전제로, 시스템 리소스를 가장 적게 사용하는 실행계획 선택, Index Scan보다 Table Full Scan하는 실행계획을 더 많이 생성, DML은 항상 전체범위 최적화, Response Time보다 Throughput 중심으로 최적화

- 부분범위 최적화(FIRST_ROWS)

 

- 선택도(Selectivity) = 1 / NDV

- 카디널리티(Cardinality) = 총 로우 수 / NDV = num_rows / num_distinct

 

- 통계정보 수집

    - 시간/주기 : 부하가 없는 시간대에 가능한 한 빠르게 수집을 완료해야 함

    - 표본(Sample) 크기와 정확성 : 가능한 한 적은 양의 데이터를 읽고도 전수 검사할 때의 통계치에 근접하도록 해야 함

    - 안정성 : 데이터에 큰 변화가 없는데도 매번 통계치가 바뀌지 않아야 함

 

- 뷰(View)안에 ROWNUM을 이용해 View Merging 방지하여 성능 개선

- 이외에도 집합(set 연산자), connect by 절, select-list에 집계 함수 사용, 분석 함수, 윈도우 함수(?)로 방지

- Group By를 포함한 뷰는 자주 Merging 발생

 

- 조인을 먼저 처리하면 Cartesian Product가 발생해 결과가 틀릴 수 있음

 

(진행중)

- 인덱스와 조인

- 고급 SQL 튜닝