[DB & SQL] SQL 고급활용 및 튜닝 / 아키텍처 기반 튜닝 원리

SQL 고급활용 및 튜닝 / 아키텍처 기반 튜닝 원리

 

클라이언트가 서버 프로세스와 연결하는 Oracle의 예

1. 전용 서버(Dedicated Server) 방식
2. 공유 서버(Shared Server) 방식

 

SQL Server에서는 세그먼트는 테이블, 인덱스, Undo처럼 저장공간을 필요로 하는 데이터베이스 오브젝트다.

저장공간을 필요로 한다는 것은 한 개 이상의 익스텐트를 사용함을 뜻한다.

SQL Server에서는 세그먼트라는 용어를 사용하지 않지만, 힙 구조 또는 인덱스 구조의 오브젝트가 여기에 속한다.

 

DB 버퍼 캐시에 가해지는 모든 변경사항을 기록하는 파일을 Oracle은 'Redo 로그'라고 부르며, SQL Server는 '트랜잭션 로그'라고 부른다.

 

Response Time Analysis 방법론

• Response Time Analysis 방법론은 Response Time을 정의하고, CPU Time과 Wait Time을 각각 break down 하면서 서버의 일]량과 대기 시간을 분석해 나간다. CPU Time은 파싱 작업에 소비한 시간인지 아니면 쿼리 본연의 오퍼레이션 수행을 위해 소비한 시간인지를 분석한다. Wait Time은 각각 발생한 대기 이벤트들을 분석해 가장 시간을 많이 소비한 이벤트 중심으로 해결방안을 모색한다.
• Response Time = Service Time + Wait Time = CPU Time + Queue Time

 

파싱(Parsing)

• 소프트 파싱(Sort Parsing)
  • SQL과 실행계획을 캐시에서 찾아 곧바로 실행단계로 넘어가는 경우를 말함
• 하드 파싱(Hard Parsing)
  • SQL과 실행계획을 캐시에서 찾지 못해 최적화 과정을 거치고 나서 실행단계로 넘어가는 경우를 말함

 

바인드 변수(Bind Variable)

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

 

SQL 커서에 대한 작업 요청에 따른 데이터베이스 Call의 구분

1. Parse Call : SQL파싱을 요청하는 Call
2. Execute Call : SQL 실행을 요청하는 Call
3. Fetch Call : SELECT문의 결과 데이터 전송을 요청하는 Call

 

사용자 정의 함수/프로시저는 내장함수처럼 Native 코드로 완전히 컴파일된 형태가 아니여서 가상머신(Virtual Machine) 같은 별도의 실행엔진을 통해 실행된다. 실행될 때마다 컨텍스트 스위칭(Context Switching)이 일어나며, 이 때문에 내장함수(Built-In)를 호출할 때와 비교해 성능을 상당히 떨어뜨린다.

 

Single Block I/O는 한번의 I/O Call에 하나의 데이터 블록만 읽어 메모리에 적재하는 방식이다.

MultiBlock I/O는 I/O Call이 필요한 시점에, 인접한 블록들을 같이 읽어 메모리에 적재하는 방식이다.

 

버퍼 캐시 히트율(BCHR: Buffer Cache Hit Ratio)

버퍼 캐시 히트율(Buffer Cache Hit Ratio)을 측정하는 지표로서, 전체 읽은 블록 중에서 메모리 버퍼 캐시에서 찾은 비율을 나타낸다.

물리적인 디스크 읽기를 수반하지 않고 곧바로 메모리에서 블록을 찾은 비율을 말한다.

 

I/O튜닝의 핵심 원리

• Sequential 액세스에 의한 선택 비중을 높인다.
• Random 액세스 발생량을 줄인다.

 

Write Ahead Logging

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

 

Log Force at commit

Log Force at commit은 로그 버퍼를 주기적으로 로그 파일에 기록하되 늦어도 커밋 시점에는 반드시 기록해야 함을 뜻함

 

Fast Commit

Fast commit은 사용자의 갱신내용이 메모리상의 버퍼 블록에만 기록된 채 아직 디스크에 기록되지 않았지만 Redo 로그를 믿고 빠르게 커밋을 완료하는 것을 말한다.

 

Delayed Block Cleanout

Delayed Block Cleanout은 오라클만의 독특한 매커니즘으로서, 변경된 블록을 커밋 시점에 바로 Cleanout(로우 Lock 정보 해제, 커밋 정보 기록)하지 않고 그대로 두었다가 나중에 해당 블록을 처음 읽는 세션에 의해 정리되도록 하는 것을 말한다.

 

 

 

 

 

 

 

 

 

 

 

참조:

SQL자격검정실전문제