3)SQL수행구조 - 1. 데이터베이스 아키텍처

1. 데이터베이스 구조

오라클의 데이터베이스 정의

디스크에 저장된 데이터 집합(Datafiles,Redo Log File, Control File, Archived Log Files)

인스턴스)

SGA공유메모리 영역과 이를 액세스하는 프로세스 집합

 

2.프로세스

서버프로세스 : 전면에서 사용자로부터 전달받은 각종 명령 처리  (SQL Server -Worker 쓰레드)

                        SQL 파싱, 최적화.

                       커서를 열어 SQL실행, 블록을 읽어 데이터 정렬-요청한 결과 집합 만들어 전송

                       OS와 I/O서브시스템,백그라운드 프로세스가 할일 시스템 Call을 통해 요청.

(데이터 파일로부터 DB버퍼캐시로 블록 적재, Dirty 블록을 캐싱서 밀어내 Free블록 확보, Redo로그 버퍼 비우는 일)

     -전용서버방식: 처음연결을 요청받은 리스너가 서버프로세스를 생성해주고, 단 하나의 사용자 프로세스를 위해 전용 서비스 제공. 따라서 OLTP성 애플리케이션에선 Connection Pooling 기법을 필수적으로 사용. 예를 들어 50개의 서버 프로세스와 연결된 50개의 사용자 프로세스를 공유해 반복 재사용하는 방식.

     -공유서버방식:Connection Pooling기법을 DBMS내부에 구현. 미리 여러개의 서버 프로세스를 띄어놓고 이를 공유해 반복 재사용.

 

백그라운드 프로세스 : 할당받은 역할 수행

3.데이터 저장구조

1) 데이터 파일

   -블록(=페이지 8KB)

   옵티마이저가 인덱스를 이용해 테이블을 엑세스할지 아니면 Full Table Scan할지를 결정하는 데 있ㅇ ㅓ가장 중요한

   판단 기준은 읽어야 할 레코드 수가 아니라 읽어야 하는 블록 개수다. 

   -익스텐트 (SQL Server 8개익스텐트)

   테이블스페이스로부터 공간을 할당하는 단위

   테이블이나 인덱스에 데이터를 입력하닥 공간이 부족해지면 해당 오브젝트가 속한 테이블스페이스로부터 추가적인

   공간을 할당 받는다 이때 정해진 익스텐트 크기의 연속된 블록을 할당받는다.

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

Oracle은 한 익스텐트에 속한 모든 블록을 단일 오브젝트가 사용하지만 SQL Server에서는 2개 이상 오브젝트가 나누어 사용할 수도 있다. 

      *균일 익스텐트: 64KB이상의 공간을 필요로 하는 테이블이나 인덱스를 위해 사용되며, 8개 페이지 단위로 할당된 익스텐트를 단일 오브젝트가 모두 사용.

      *혼합 익스텐트: 한 익스텐트에 할당된 8개 페이지를 여러 오브젝트가 나누어 사용하는 형태. 모든 테이블이 처음에는 혼합 익스텐트로 시작하지만 64KB를 넘으면서 2번째부터 균일 익스텐트를 사용하게 된다.

   -세그먼트

    테이블,인덱스,Undo처럼 저장공간을 필요로 하는 데이터베이스 오브젝트.(한개 이상의 익스텐트를 사용함)

    테이블을 생성할때 테이블 세그먼트, 인덱스 생성때 인덱스세그먼트 생성. 파티션은 1:M관계를 갖음.

    한 세그먼트는 자신이 속한 테이블스페이스 내 여러 데이터 파일에 걸쳐 저장될 수 있다. 

   -테이블스페이스

     세그먼트를 담는 콘테이너, 여러 데이터파일로 구성. 

 

2) 임시 데이터 파일

  대량의 정렬이나 해시 작업을 수행하다가 메모리 공간이 부족해지면 중간 결과 집합을 저장하는 용되.

  임시로 저장후 삭제됨.

오라클에선 임시 테이블스페이스를 여러개 생성해 두고, 사용자마다 별도의 임시 테이블스페이스를 지정해줄 수도 있다.

 

3) 로그파일

 변경된 메모리 버퍼블록을 디스크 상의 데이터블록에 기록하는 작업은 Random I/O방식으로 느리다.

반면 로그기록은 Append방식으로 매우 빠름.

따라서 버퍼블록에 대한 변경사항을 데이터파일에 기록하기 보다 우선 로그파일에 빠르게 기록 후 

버퍼블록과 데이터 파일 간 동기화는 적절한 수단 (DBWR,Checkpoint)ㅡㄹ 이용해 나중에 배치 방식으로 일괄 처리.

사용자의 갱신 내용이 메모리상의 버퍼 블록에만 기록된 채 아직 디스크에 기록되지 않았더라도 Redo  로그를 믿고 빠르게 커밋을 완료한다는 의미에서 이를 'Fast Commit'메커니즘이라고 브른다.

 

   -Online Redo로그(Oracle)

트랜잭션 데이터의 유실에 대비하기 위해 사용. 마지막 체크포인트 이후부터 사고 발생직전까지 수행됐던 트랜잭션들을 Redo로그를 이용해 재현하는 것, 이를 '캐시복구'라고 한다.

최소 두 개 이상의 파일로 구성. 현재 사용중인 파일이 꽉 차면 다음 파일로 로그 스위칭이 발생하며, 계속 로그를 써 나가다가 모든 파일ㅇ ㅣ꽉 차면 다시 첫번째 파일부터 재사용하는 라운드 로빈 방식을 사용

   -트랜잭션 로그(SQL Server)

데이터베이스마다 트랜잭션 ㅗ그 파일이 하나씩 생기며 확자아느 ldf.

내부적으로 '가상로그파일'이라 불리는 더 작은 단위의 세그먼트로 나뉘며, 아 가상 로그파일의 개수가 너무 많아지지 않도록 옵션을 지정하는 게 좋다. 

   -Archived(=Offline) Redo로그

오라클에서 Online Redo로그가 재사용되기 전에 다른 위치로 백업해 둔 파일.

물리적 저장매체에 문제시 복구를 위해 사용 (SQLServer에는 대응되는 개념이 없다)

 

4. 메모리구조

시스템 공유 메모리영역(SGA / Memory Pool) 여러 프로세스가 동시에 액세스할 수 있는 메모리 영역 공유메모리를 구성하는 캐시영역은 DB버퍼캐시,공유풀,로그버퍼가 있다. 외에 Large Pool,Java Pool 등을 포함 시스템 구조와 제어 구조를 캐싱하는 영역도 포함. 여러 프로세스에 공유되기 때문에 내부적으로  Latch,버퍼Lock,라이브러리 캐시 Lock/Pin 같은 액세스 직렬화 메커니즘이 사용된다.

프로세스전용메모리영역(Process Global Area PGA) 데이터를 정렬하고 세션과 커서에 관한 상태정보를 저장 SQLServer는 없음.

액세스 직렬화 메커니즘이란?
여러 프로세스의 동시 접근을 제어하고 충돌을 방지하기 위해 사용하는 기술들입니다. 여기서 언급된 몇 가지를 설명하면:

Latch:

공유 자원을 짧은 시간 동안 보호하기 위해 사용하는 경량 락입니다.
예: 특정 메모리 구조를 수정하는 동안 다른 프로세스가 접근하지 못하도록 잠금.
버퍼 Lock:

데이터베이스에서 버퍼 캐시(디스크에서 읽어온 데이터를 담고 있는 메모리 영역)에 대한 접근을 제어하기 위해 사용.
한 프로세스가 버퍼를 수정하는 동안 다른 프로세스는 해당 버퍼를 읽거나 쓸 수 없음.
라이브러리 캐시 Lock/Pin:

데이터베이스의 SQL 문장이나 PL/SQL 코드를 캐싱하는 영역에 대한 보호.
Lock은 공유 메모리 구조 전체를 보호하고, Pin은 특정 객체(예: 특정 SQL 문장)를 보호.

<SGA>

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

 인스턴스에 접속한 모든 사용자 프로세스는 서버프로세스를 통해 DB버퍼 캐시의 버퍼 블록을 동시에(내부적으로는 버퍼 Lock을 통해 직렬화)액세슬할 수 있다.

일부 Direct Path Read매커니즘이 작동하는 경우를 제외하면, 모든 블록 읽기는 버퍼캐시를 통해 이뤄진다.

즉 읽고자 하는 블록을 먼저 버퍼캐시에서 찾아보고 없ㅇ르때 디스크에서 읽음.

번경된 블록도 마찬가지며 변경된 블록(Dirty 버퍼 블록)을 주기적으로 데이터파일에 기록하는 작업은 DBWR프로세스의 몫이다.

  1.버퍼블록의 상태

   Free버퍼: 인스턴스 기동 후 아직 데이터가 읽히지 않음.데이터가 담겼지만 데이터파일과 서로 동기화되어 언제든 덮어써도 무방. 데이터 파일로부터 새로운 데이터 블록을 로딩하려면 먼저 Free 버퍼를 확보해야 한다. Free상태인 버퍼에 변경이 발생하면 그순간 Dirty버퍼로 변경됨

   Dirty버퍼: 이 버퍼 블록들이 다른 데이터 블록을 위해 재사용되려면 디스크에 먼저 기록돼야하며 기록되는 순간 Free버퍼로 변경

   Pinned버퍼: 읽기 도는 쓰기 작업이 현재 진행 중인 버퍼 블록

 

2. LRU 알고리즘 (Leas recently Use)

 : 버퍼캐시는 유한하다. 따라서 사용빈도가 높은 데이터 위주로 구성되도록 알고리즘 사용.

 

 

-공유 풀: 딕셔너리 캐시와 라이브러리캐시로 구성 LRU알고리즘 사용 (SQLServer-프로시저캐시)

딕셔너리 캐시 테이블,인덱스 같은 오브젝트와  테이블스페이스,데이터파일,세그먼트,사용자,제약등 메타정보 저장. ex)주문데이터는 데이터 파일에 저장됐다가 버퍼 캐시를 경유해 읽히지만,테이블 메타 정보는 딕셔너리에 저장됐다가 딕셔너리 캐시를 경유해 읽힘.

라이브러리 캐시 사용자가 수행한 SQL문과 실행계획, 저장프로시저를 저장해두는 캐시영역. 사용자가 SQL 명령어를 통해 결과 집합을 요청하면 이를 최적으로 수행하기 위한 처리 루틴을 생성해야 하는데 -실행계획이라한다. (빠른 쿼리 수행을 위해 내부적으로 생성한 일종의 프로시저와 같은것) 하드파싱>>쿼리 구문을 분석해서 문법 오류 및 실행 권한 등의 체크, 최적화 과정을 거쳐 실행계획생성, sQL실행엔진이 이해할 수 있는 형태로 포맷팅 등의 전 과정 같은 SQL에 대한 반복적 하드파싱을 최소화하기 위해 캐시공간을 따로두게 되었는데 이게바로 라이브러리 캐시.

 

-로그버퍼:DB버퍼 캐시에 가해지는 모든변경파일 기록

서버프로세스가 데이터 블록 버퍼에 변경을 가하기 전 Redo로그 버퍼에 먼저 기록.

>>주기적으로 LGWR프로세스가 Redo로그파일에 기록.(SQLServer - 트랜잭션로그,로그캐시)

변경이 가해진 더티버퍼를 데이터 파일에 기록하기 전에 항상 로그 버퍼를 먼저 로그 파일에 기록해야만하는데

 

-PGA

:각 서버 프로세스는 자신만의 PGA메모리 영역을 할당받고 이를 프로세스에 종속적인 고유 데이터를 저장하는 용도로 사용. 다른 프로세스와 공유되지 않는 독립적인 메모리 공간으로서 래치 메커니즘이 필요 없어 똑같은 개수의 블록을 읽더라도 SGA버퍼캐시에서 읽는것보다 빠르다.

UserGlobalArea(UGA) 세션이 프로세스 개수보다 많아질 수 있는 구조로서, 하나의 프로세스가 여러 개 세션을 위해 일한다. 따라서 각 세션을 위한 독립적인 메모리 공간이 필요한데, 이를 UGA UGA는 전용서버 방식으로 연결할때는 PGA할당되고, 공유 서버 바식으로 연결할 때는 SGA에 할당됨.

Call Global Area(CGA) 하나의 데이터베이스 Call을 넘어 다음 Call가지 계속 참조해야 하는 정보는 UGA에 Call이 진행되는 동안에만 필요하면 CGA에 담음. (Parse Call, Execute Call,Fetch Call)마다 매번 할당받음. Call이 진행되는 동안 Recursive Call이 발생하면 단계별로 CGA가 추가로 할당. 하나의 cAll이끝나면 PGA에 반환됨.

Sort Area 데이터 정렬을 위해사용. 소트 오퍼레이션이 진행되는동안 부족해질 때마다 청크 단위로 조금씩 할당. 할당위치는 SQL문 종류와 소트 수행 단계에 딸 ㅏ다르다. DML문장은 하나의 Execute Call 내에서 모든 데이터 처리를 완료하므로 Sort Area가 CGA에 할당된다. SELECT 문장의 경우 수행 중간 단계에 필요할때 CGA, 최종 결과 집합 출력 직전에는 UGA에 할당.

**스레드 기반 아키텍처를 사용하는 SQL Server는 프로세스 전용 메모리 영역을 갖지 않는다.

대신 데이터 정렬은 Memory Pool안에 있는 버퍼캐시에서 수행.

세션관련정보는 Memory Pool안에 있는 Connection Context영역에 저장.