SQL자격검정실전문제 - 아키텍쳐 기반 튜닝 원리 ( 데이터베이스 아키텍쳐, SQL 파싱부하, 데이터베이스 call과 네트워크 부하, 데이터베이스 I/O원리)

데이터베이스 아키택쳐에 대해 서술하시오

데이터베이스 아키택쳐는 크게 프로세스 기반과 스레드 기반으로 나눌 수 있습니다. 프로세스 기반 아키텍쳐는 크게 서버 프로세스와 백스라운드 프로세스로 구성됩니다. 서버프로세스는 클라이언트로 부터 전달받은 각종 명령을 처리하고(SQL 파싱, 최적화) 백그라운드 프로세스(데이터 처리, 로그 적재 등)는 명령을 처리하기 위한 작업을 실행합니다.

 

프로세스기반 아키텍쳐에서 클라이언트가 서버프로세스에 연갈하는 방식은 크게 전용 서버 방식과 공유서버 방식이 있습니다. 전용서버방식은 리스너에 의해 생성된 서버 프로세스가 클라이언트와 직접 연결됩니다. 클라이언트가 서버프로세스에 접속할때마다 매번 연결을 열고 닫으면 부하가 가중되므로 CONNECTION POOLING 기법을 사용한것이 좋습니다.

 

공유서버방식은 하나의 서버 프로세스를 여러 클라이언트가 공유하는 방식입니다. 클라이언트는 사버 프로세스와 직접 연결하지 않고 ditpatcher를 거칩니다. dispather가 전용서버방식의 connection pooling의 역할을 합니다.

 

스레드 기반 아키택쳐는 크게 포그라운드 스레드와 백그라운드 스레드로 구분됩니다. 포그라운드 스레드는 최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리합니다.

 

다음 작업들을 백그라운드 스레드가 처리합니다.

 

- 인서트 버퍼를 병합하는 스레드

- 로그를 디스크로 기록하는 스레드

- DB 버퍼 풀의 데이터를 디스크에 기록하는 스레드

- 데이터를 버퍼로 읽어오는 스레드

- 잠금이나 데드락을 모니터링하는 스레드

 

DBMS의 데이터 저장구조에 대해 서술하시오.

 

기본적으로 데이터를 읽고 쓰는 단위는 블록입니다. 데이터파일에 공간을 할당하는 단위는 익스텐트로 익스텐트는 여러 개의 블록으로 구성됩니다. 익스텐트 내부의 블록은 물리적으로 서로 인접해있습니다. 세그먼트는 저장공간을 필요로하는 데이터베이스 오브젝트 입니다. 세그먼트는 여러개의 익스텐트로 구성되는데 논리적 단위기 때문에 물리적으로는 인접하지 않습니다. SQL SERVER에서 한 익스텐트에 속한 페이지(블록)은 여러 오브젝트나 나누어 사용할 수 있습니다.

 

ORACLE의 REDO 로그/ SQL SERVER의 트랜잭션 로그 메커니즘 중 WRITE AHEAD LOG에 대해 서술하시오.

 

버퍼 캐시 블록을 갱신하기 전에 변경사항을 먼저 로그 버퍼에 기록해야 합니다. DIRTY 버퍼를 디스크에 기록하기 전에 해당 로그 엔트리를 먼저 로그 파일에 기록합니다.

 

DBMS 메모리 구조에 대해 서술하시오.

 

ORACLE DBMS의 메모리는 크게 SGA와 PGA로 구성됩니다. 그중 SGA는 데이터베이스 버퍼캐시, REDO LOG BUFFER, SHARED POOL로 구성됩니다. DB 버퍼캐시는 데이터 파일로 부터 읽어 들인 데이터 블록을 저장하는 캐시 영역입니다. REDO LOG BUFFER는 DB 버퍼 캐시에 가해지는 모든 변경사항을 기록합니다. Shared Pool은 SQL의 실행 계획들과 시스템을 운영하는데 필요한 메타 데이터(Data Dictionary)가 상주하는 영역으로 SQL 처리의 첫 단계인 Parsing을 담당하는 부분입니다.

 

RESPONSE TIME ANALYSIS 성능관리 방법론에 대해 서술하시오.

 

RESPONSE TIME을 정의하고, CPU TIME과 WAIT TIME을 각각 Break down 하면서 서버의 일량과 대기 시간을 분석합니다. CPU TIME은 파싱 작업에 소비한 시간인지 아니면 쿼리 본연의 오퍼레이션 수행을 위한 시간인지 분석합니다. WAIT TIME은 각각 발생한 대기 이벤트를 분석해 가장 많이 소비한 이벤트를 중심으로 해결방안을 모색합합니다. 수식으로 표현하면 다음과 같습니다.

 

RESPONSE TIME = CPU TIME + WAIT(QUEUE) TIME

 

SQL 커서의 파싱에 대해 서술하시오.

 

SQL 커서는 두가지 방식으로 SQL을 파싱하고 실행계획을 도출합니다. 소프트 파싱은 SQL과 실행계획을 캐시에서 찾아 바로 실행단계로 넘어갑니다. 하드 파싱은 SQL과 실행계획을 캐시에서 찾지 못해 최적화 과정을 거치고 실행단계로 넘어갑니다. 캐시에서 SQL과 실행계획을 식별하는 식별자는 SQL문장입니다. 따라서 문자가 하나만 달라도 다른 SQL로 인식해 각각 하드파싱을 일으키고 다른 캐시 공간을 사용합니다.

 

SQL 바인드 변수에 대해 서술하시오.

 

바인드 변수는 파라미터 driven 방식으로 SQL을 작성한느 방법입니다. SQL과 실행계획을 여러 개 캐싱하지 않고 하나를 반복 재사용하므로 파싱 소요시간과 메모리 사용량이 줄어듭니다. 바인드변수를 사용하면 칼럼히스토그램을 사용하지못하는 단점이 있습니다.

 

다음과 같은 경우 상수조건을 사용하는 편이 좋습니다.

 

- 수행빈도가 낮고 한 번 수행할때 수십초 이상 수행되는 SQL
- 조건절 칼럼의 값 종류가 소수이고 값 분포가 균일하지 않을때
- 사용자가 입력할 수 잇는 조회항목이 아니어서 해당 조건절이 불변일때

 

SQL 작성방식에 대해 서술하시오

 

SQL의 작성방식은 크게 STATIC SQL과 DYNAMIC SQL로 구분됩니다. STATIC SQL은 바인드 변수를 사용하지 않고 코드 사이에 직접 기술한 SQL을 말합니다. DYNAMIC SQL이란 변수를 사용해 서술한 SQL을 말합니다. STATIC SQL은 PRECOMPILE 과정을 거치므로 런타임시 안정적인 프로그램 BUILD가 가능합니다. DYNAMIC SQL을 사용하면 애플리케이션 커서 캐싱이 작동하지 않는 경우가 있습니다. 고로 STATIC SQL을 지원하는 개발환경에선 가급적 STATIC SQL로 작성하는 것이 좋습니다.

 

데이터 베이스 CALL에 대해 서술하시오

 

SQL 커서에 대한 작업 요청에 따른 데이터베이스 CALL 구분은 다음과 같습니다.

 

PARSE CALL : SQL 파싱을 요청하는 Call
EXECUTE CALL : SQL 실행을 요청하는 Call
FETCH CALL : SELECT문의 결과 데이터 전송을 요청하는 Call

 

SELECT 문장을 수행할 땐 PARSE, EXECUTE, FETCH 순으로 CALL이 발생합니다. 대부분의 CALL은 FETCH CALL 단계에서 일어납니다. GROUP BY를 포함한 SELECT 문장에서 GROUP BY 결과집합을 만들기까지의 I/O는 FETCH CALL 단계에서 발생합니다.
INSERT, UPDATE, DELETE 문장에서는 FETCH CALL이 발생하지 않습니다.

 

부분범위처리에 대해 서술하시오.

 

부분범위처리가 가능하도록 SQL을 작성하면 출력 대상 레코드가 많을수록 쿼리 응답속도도 빨라집니다. ARRAY 크기가 증가하면 데이터베이스 FETCH CALL의 횟수가 감소하고 블록 I/O 횟수가 감소합니다.

 

사용자정의함수에 대해 정의하시오

 

사용자 정의함수의는 가상머신과 같은 실행엔진을 통해 실행됩니다. 실행될 때마다 컨텍스트 스위칭이 발생하기 때문에 성능이 크게 저하됩니다.

 

블록 I/O에 대해 서술하시오.

 

SINGLE BLOCK I/O는 한번의 i/o call에 하나의 데이터 블록만을 읽어 메모리에 적제하는 방식입니다. MULTIBLOCK I/O는 I/O CALL이 필요한 시점에 인접한 블록을 같이 읽어 메모리에 적제하는 방식입니다. MULTIBLOCK I/O 방식으로 읽더라도 EXTENT 범위를 넘어서까지 읽지 않습니다. 따라서 작은 EXTENT로 구성된 테이블을 Full Table Scan 하면 I/O CALL이 더 많이 발생합니다.

 

인덱스를 통해 테이블을 엑세스 하면 SINGLE BLOCK I/O 방식을 사용하므로 extent 크기가 I/O CALL의 힛수에 영향을 주지 않습니다.

SEQUANTIAL I/O는 인덱스나 테이블을 full scan할때 사용되고 RANDOM i/o는 인덱스를통해 테이블을 엑세스할때 사용됩니다.

 

버퍼캐시히트율에 대해 서술하시오.

 

버퍼캐시효율을 측정하는 지표로서, 전체 읽은 블록 중에서 메모리 버퍼 캐시에서 찾은 비율을 나타냅니다. 물리적인 디스크 읽기를 수반하지 않고 곧바로 메모리에서 블록을 찾은 비율입니다. 하나의 쿼리에서 같은 블록을 반복적으로 엑세스하면 버퍼 캐시 히트율은 높아집니다.