아키텍처 기반 튜닝원리

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

1) 전용서버방식

2) 공유서버방식 

 

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

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

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

 

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

 

데이터베이스 연결에 관련한 설명

-다중 사용자 환경에서 서버와 모든 클라이언트 간 연결상태를 지속하면 서버 자원을 낭비하게 된다. 

그렇다고 SQL 을 수행할 때마다 연결 요청을 반복하면 서버 프로세스(또는 쓰레드)의 생성과 해제도 반복하므로 성능에 좋지 않다. 따라서 OLTP성 애플리케이션에선 Connection Pooling 기법의 활용이 필수적이다. 

-연결요청에 대한 부하는 쓰레드(Thread)기반 아키텍처보다 프로세스 기반 아키텍처에서 심함

-전용서버(Dedicated Server) 방식으로 오라클 데이터베이스에 접속하면 사용자가 데이터베이스 서버에 연결 요청을 할 때마다 서버 프로세스가 생성된다. 

-공유서버 방식으로 오라클 데이터베이스에 접속하면 사용자 프로세스는 서버프로세스와 직접 통신하지 않고 Dispatcher 프로세스를 거친다. 

 

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

데이터를 읽고 쓰는 단위는 블록(=페이지)이다. 

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

SQL Server에서는 한 익스텐트에 속한 페이지들을 여러 오브젝트가 나누어 사용할 수 있다. 

 

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

> Write Ahead Logging

 

다음중 메모리 구조에 대한 설명 

> Table Full Scan한 데이터 블록은 LRU end에 위치하기 때문에 버퍼 캐시에 오래 머물지 않는다. 

DB 버퍼 캐시는 데이터 파일로부터 일겅들인 데이터 블록을 담는 캐시영역이다. 

/*+ append*/힌트를 사용하면 insert시 DB 버퍼 캐시를 거치지 않고 디스크에서 직접쓴다. 

클러스터링 팩터가 좋은 인덱스를 사용하면 Buffer Pinning 효과로 I/O를 줄일 수 있다. 

 

Response Time Analysis 방법론

ResponseTime을 정의하고 , CPU Time과 Wait Time을 각 break down하면서 서버의 일량과 대기시간을 분석해나간다. 

CPU Time은 파싱 작업에 소비한 시간인지 아니면 쿼리 본연의 오퍼레이션 수행을 위한 소비한 시간인지를 분석한다.

Wait Time은 각각 발생한 대기 이벤트들을 분석해 가장 시간을 많이 소비한 이벤트 중심으로 해결방안을 모색한다. 

 

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

 

소프트파싱 

: SQL과 실행계획을 캐시에서 찾아 곧바로 실행단계로 넘어가는 경우를 말함 

 

하드파싱 

: SQL과 실행계획을 캐시에서 찾지 못해 최적화 과정을 거치고 나서 실행단계로 넘어가는 경우를 말함

 

캐시에서 SQL과 실행계획을 식별하는 식별자는 SQL문장 그 자체다. 따라서 옵티마이저는 문자하나만 달라도 서로 다른 SQL로 인색해 각각 하드파싱을 일으키고 다른 캐시 공간을 사용한다. 

1~3번 SQL은 SQL이 서로 다르다. SQL Text가 달라 하드파싱은 각각일어나지만, 의미상 전혀 차이가 없으므로 실행계획은 같다. 

 

* 바인드 변수 :

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

 

OLTP환경의 시스템인 점을 고려해 가급적 바인드 변수를 사용하도록 권고 , but Literal 상수 조건을 사용하는 것이 더 낫거나 바인드 변수를 사용하려고 애쓰지 않아도 되는 경우는? 

-수행빈도가 낮고 한번 수행할 때 수십초 이상 수행되는 SQL일때 

-조건절 칼럼의 값 종류가 소수이고 값분포가 균일하지 않을때 

-사용자가 입력할 수 있는 조회 항목이 아니어서 해당 조건이 불변일때 

사용자의 입력 조건이 다양해서 조건절을 동적으로 구성하더라도 조건절 비교값 만큼은 바인드 변수를 사용하려고 노력해야함

 

Dynamic SQL 방식으로 코딩했지만, 바인드 변수를 사용했으므로 불필요한 하드파싱을 많이 일으킨다고 말하기 어려움 

바인드 변수를 사용했으므로 컬럼 히스토그램은 활용하지 못하지만, 레코드건수, 컬럼 값의 종류 , Null 값 개수 등을 활용해 실행계획을 수립한다. 

 

Static SQL이란, String형 변수에 담지 않고 코드 사이에 직접 기술한 SQL 문을 말한다.

Dynamic SQL이란, String형 변수에 담아서 실행하는 SQL문을 말한다. 

Static SQL은 PreCompile 과정을 거치므로 런타임시 안정적인 프로그램 build가 가능하다. 

그리고 Dynamic SQL을 사용하면 애플리케이션 커서 캐싱이 작동하지 않는 경우가 있다. 따라서 Static SQL을 지원하는 개발환경에선 가급적 이 방식을 사용하는게 좋다. 

 

Parse Call : sql 파싱을 요청하는 call

, Excute : sql 실행을 요청하는 call

, Fetch : select문의 결과 데이터 전송을 요청하는 call 

 

SELECT 문장을 수행할 땐 Parse, Excute, Fetch 순으로 Call이 발생한다. 

Group by  결과집합을 만드는 과정에서의 I/O는 첫번째 Fetch Call 단계에서 일어난다. 

INSERT UPDATE DELETE 문장에선 Fetch Call이 전혀 발생하지 않는다. 

 

제시된 Call Statics 만드로는 Orderby 또는 Group By 연산의 포함 여부를 판단할 수 없다. 

 

부분 범위 처리에 대한 설명 

: 모든 데이터 처리가 서버 내에서 이루어지는 프로그램에선 부분범위처리에 의한 성능 개선 효과가 나타나지 않음 

부분범위처리가 가능하도록 SQL을 작성하면 출력 대상 레코드가 많을수록 쿼리 응답 속도도 그만큼 빨라진다. 

Array 크기를 증가시키면 데이터베이스 Call 횟수가 감소한다.

Array 크기를 증가시키면 블록 I/O 횟수가 감소한다. 

 

SQL을 포함하는 형태의 사용자 정의 함수라면, 대용량 쿼리에 그것을 사용하는 순간 성능이 크게 저하됨 

성능이 중요하다면, 함수 안에서 또다른 함수를 호출하는 형태를 지양해야함

SQL을 포함하지 않는 형태의 사용자 정의 함수라도 문맥전환에 의한 부하가 발생하므로 성능저하가 발생한다. 

작은 코드 테이블로부터 코드명을 가져오는 경우 사용자 저으이 함수보다 스칼라 서브쿼리를 사용하여 캐싱효과를 누리는 것이 성능상 유리하다. 

 

오라클에서 DB저장형 함수를 사용할 때 성능이 저하되는 원인 

> 함수를 실행할때마다 컴파일 하진않음 

>가상머신상에서 실행되므로 매번 바이트 코드를 해석하는 부하 

>쿼리문자의 조회 건수만큼 함수를 밙복적으로 호출하는 부하

>함수에 내장된 쿼리가 있다면, 해당 문장을 Recusive하게 반복 수행하는 부하 

 

I/O효율화 튜닝방안 

> 변경이 거의 없는 테이블까지 매일 통계정보를 수집할 필요는 없다 .

>필요한 최소 블록만 읽도록 쿼리를 작성한다. 

>전략적인 인덱스 구성은 물론 DBMS가 제공하는 다양한 기능을 활용한다. 

>필요하다면 , 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도한다. 

 

Single Block I/O

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

MultiBlock I/O는 

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

 

Direct Path I/O는 일반적으로 병렬 쿼리로 Full Scan을 수행할 때 발생한다. 

Random I/O는 인덱스를 통해 테이블을 액세스할 때 주로 발생한다. 

Single Block I/O는 인덱스를 통해 테이블을 액세스 할 때 주로 발생한다. 

Multiblock I/O는 인덱스를 이용하지 않고 테이블 전체를 스캔할 때 주로 발생한다. 

 

Multiblock I/O 방식으로 읽더라도 Extent 범위를 넘어서까지 읽지는 않는다. 따라서 작은 Extent로 구성된 테이블을 Full Table Scan 하면 I/O Call이 더 많이 발생한다. 

반면, 인덱스를 통한 테이블 액세스 시에는 Single Block I/O방식을 사용해서 Extent 크기가 I/O call 횟수에 영향을 미치지 않는다. 

 

I/O 튜닝의 핵심 원리 

Sequential 액세스에 의한 선택 비중을 높힌다. 

Random 액세스 발생량을 줄인다. 

 

데이터베이스 I/O원리 

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

 

Sequential I/O방식은 테이블이나 인덱스를 스캔할 때 사용한다. Random I/O방식은 인덱스를 스캔하면서 테이블을 액세스할 때 사용한다. 

요즘은 NAS서버나 SAN가 보편적으로 사용되기 때문에 네트워크 속도가 I/O성능에 큰 영향을 미친다. 

RAC 같은 클리스터링 데이터베이스 환경에선 메모리도 I/O성능에 영향을 미친다.