[DB] 1장.SQL 처리과정과 I/O

1. SQL파싱과 최적화 

1. 옵티마이저가 SQL을 어떻게 처리하는지
2. 서버프로세스는 데이터를 어떻게 읽고 저장하는지 

 

🔎 SQL (Structured Query Language)

• 구조적 질의 언어
• 구조적이고, 집합적이고, 선언적인 질의 언어
• 구조적(Structured) : 데이터를 테이블 형태로 정리하고 다루는 언어
• 집합적(set-based) : 데이터를 한꺼번에 처리가 가능, 집합적인 언어 수행
• 선언적(declarative) : SQL이 내부적으로 데이터를 어떻게 찾고, 처리할지는 DBMS가 처리하기 때문에, 사용자는 결과만 요청 

🔎 옵티마이저 (Optimizer)

• SQL 쿼리의 성능을 최적화하기위한 DBMS 핵심 엔진
• 즉, 어떻게 하면 쿼리를 더 빠르게 실행할 수 있을까?를 자동으로 고민해주는 역할
• 개발자가 작성한 SQL을 실행하면 DBMS에서는 옵티마이저를 기반으로 실행계획을 세워서 쿼리를 실행

🔎 SQL 최적화

• DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행가능한 상태
• SQL을 실행하기위해서는 SQL 최적화과정을 거침
• SQL 최적화는 SQL을 효율적으로 작성하고, 옵티마이저가 좋은 실행계획을 선택할 수 있도록 돕는 것

1. SQL 파싱 사용자로부터 SQL을 전달 받음 →  파서(Parser)가 파싱 진행 파싱 트리 생성 → Syntax체크 → Semantic체크 Syntax체크 : 문법적오류가 없는지 확인 ex)SQL문 사용순서, 누락된 키워드 Semantic체크 : 의미상 오류가 없는지 확인 ex) - 테이블 or 컬럼존재여부, 오브젝트 권한여부  2. SQL최적화 SQL옵티마이저 → 가장 효율적이고 성능이 좋은 것을 선택  3. 로우소스 생성 옵티마이저가 선택한 실행경로를 실행가능한 코드 또는 프로시저 형태로 포멧팅

 

예시) 학생 중 20살이상인 사람의 이름을 찾기 

1. 사용자가 SQL을 작성

select name from students where age >= 20;

2. 옵티마이저가 실행계획을 세움 

• 해당 테이블에 인덱스가 있는지
• 인덱스를 사용할것인지, 테이블 전체를 스캔할지 결정
• 데이터 크기와 조건을 고려하여 가장 빠른 방법을 선택 

---

🔎SQL 옵티마이저의 최적화 단계

1. 사용자로부터 전달받은 쿼리를 수행하는데 후보군이 될만한 실행계획들을 찾음.
2. 데이터 딕셔너리에 미리 수집해 둔 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정함.
3. 최저비용을 나타내는 실행계획을 선택.

⚙️비용 (Cost) 데이터베이스가 SQL을 실행하는데 필요한 리소스 예측치 DBMS는 옵티마이저를 통해 SQL 문장을 실행하기 전에 여러 실행계획을 만들어보고, 그중 비용이 가장 낮은 실행계획을 선택 → 비용이 작은 것이  더 효율적인 계획을 의미  비용이 작을 수록 SQL이 더 빠르게 실행될 가능성이 큼 비용을 낮추는 방법 : 인덱스 추가,불필요한 데이터 조회 제거, 조인 순서 최적화

🔎 실행계획 (Execution Plan)

• SQL 실행경로 미리보기
• SQL 옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있게 트리구조로 표현한 것 
• 본인이 작성한 SQL이 테이블을 스캔하는지, 인덱스를 스캔하는지등 확인가능 
• Cost(비용)는 쿼리를 수행하는 동안 발생할 것으로 예상하는 I/O횟수 또는 예상 소요시간을 표현한 값

🔎 옵티마이저 힌트 (Hint)

• 쿼리 성능최적화, 비효율적인 실행계획 수정
•  주석기호에 주석기호에 '+'를 붙임

/*+ 힌트명 */
/*+ INDEX(name)*/ : 특정 인덱스를 사용하도록 지정
/*+ FULL(name) */: 해당 테이블에서 인덱스를 무시하고 전체 테이블 스캔(Table Scan)
/*+ NO_INDEX(name) */: 인덱스를 사용하지 않도록 지정

/*+ INDEX(nameA), INDEX(nameB) */ : 유효
/*+ INDEX(name), FULL(nameB)*/ :힌트 안에 인자를 나열할땐 콤마(,)를 사용할 수 있지만, 힌트와 힌트사이에 사용X
/*+ INDEX(nameA), FULL(nameB) */ : 첫번째 힌트만 유효
/*+ FULL(TABLE.nameA)*/ :  테이블 스키마명 명시 X

SELECT /*+ INDEX(EMP.name)*/ 
FROM EMP E : 테이블명에 alias 사용했을 경우 힌트에서도 반드시 alias 사용

 

2. SQL공유 및 재사용

소프트 파싱과 하드 파싱의 차이점

 

🔎 라이브러리 캐시 (Library Cache)

• SQL 파싱, 최적화, 로우 소스 생성과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해두는 메모리 공간

🔎 SGA  (System Global Area)

• 서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간

🔎 소프트파싱 vs 하드파싱

• 사용자가 SQL문을 전달하면 DBMS는 SQL을 파싱한 후 해당 SQL이 라이브러리 캐싱에 존재하는지 확인
• 캐시에 존재한다면 실행단계로 넘어감 = 소프트 파싱 Soft Parsing
• 캐시에 존재하지 않는다면 최적화 단계로 넘어감 = 하드 파싱 Hard Parsing

🔎 SQL최적화 과정은 왜 하드한가? 

• 복잡하고 경우의 수도 많기에 빠르게 통계정보를 읽어 효율성을 판단하는 과정이 하드하기 때문 

• 테이블, 컬럼, 인덱스 구조에 관한 기본 정보
• 오브젝트 통계 : 테이블 통계, 인덱스 통계, 컬럼 통계
• 시스템 통계 : CPU 속도, Single Block I/O 속도, Multiblock I/O 속도등
• 옵티마이저 관련 파라미터

🔎바인드 변수 ( Bind variable )

• 바인드 변수의 목적은 Hard Parse 를 줄이기 위함
• 바인드 변수를 이용할 때 변수의 값이 달라져도 같은 문장으로 인식하여 불필요한 hard parse 를 없앰.
• Parse : 동일문장찾기(여기까지 Soft Parse) →  syntax(문장오류) 점검 →  semantic(의미) 점검 →  실행계획 결정
• Excute : Read or Write
• Fetch : 결과 반환

즉, Parse 단계에서 동일문장찾기에서 끝나고 바로 Excute 로 넘어가면 Soft Parsing
SQL 문의 경우 Hard parsing 이 많으면 자원소모가 많아지며 실행이 느려짐.

 

3. 데이터 저장 구조 및 I/O 메커니즘

• SQL이 느린이유 → 디스크 I/O 때문
• 디스크 I/O 작업동안 프로세스는 대기 →  I/O 가 많으면 성능 저하 → 디스크 I/O 가 SQL 성능을 좌우
• 여러 프로세스가 하나의 CPU를 공유하고, 특정 순간에는 하나의 프로세스만 CPU를 사용하기 때문에 준비시간이    필요 → I/O콜이 많아지면 디스크 경합이 많아지고 대기시간이 늘어나고 성능이 느릴수밖에 없음.
• I/O : OS 또는 I/O 서브시스템이 I/O를 처리하는 동안 프로세스는 일을 하지 않는다.

🔎 데이터 베이스 저장 구조

1. 테이블스페이스 ( Table Space ) 생성

• 세그먼트를 담는 콘테이너, 여러개의 데이터 파일로 구성 , 디스크 상의 물리적인 OS 파일

2. 세그먼트( Segment ) 생성

• 인덱스, 파티션, LOB 처럼 데이터 저장공간이 필요한 오브젝트
• 익스텐트 ( Extent ) : 공간을 확장하는 단위 , 테이블이나 인덱스에 데이터를 입력하다가 공간이 부족해지면 해당 오브젝트가 속한 테이블스페이스로부터 익스텐트를 추가로 할당받음
• 파티션이 아닐 경우 → 하나의 테이블/인덱스는 하나의 Segment
• 파티션 구조일 경우 → 하나의 파티션이 하나의 Segment

3. 테이블 생성

4. 데이터 저장 

🔎블록 단위 I/O

• Database가 데이터를 읽고 쓰는 단위
• 특정 레코드 하나를 읽고 싶어도 레코드가 포함된 블록을 통째로 읽음
• 오라클 기준으로 Block의 크기는 8KB이다.
• Index도 Block단위로 데이터를 읽고 씀

🔎 시퀀셜 액세스( Sequential Access ) vs 랜덤 액세스 ( Random Access )

• 시퀀셜 액세스는 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식
• 랜덤 액세스는 논리적,물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식

🔎 DB버퍼캐시

• SGA의 가장 중요한 구성요소 중 하나 
• 디스크에서 읽은 데이터 블록을 캐싱하여 같은 블록에 대한 반복적인 I/O Call을 줄임

🔎 논리적 I/O vs 물리적 I/O

• 논리적 I/O는 SQL문을 처리하는 과정에서  메모리 버퍼캐시에서 발생한 총 블록 I/O, 메모리에 접근하여 데이터를 가져온 블록 수
  물리적 I/O가 일어나더라도 메모리 버퍼캐시에 올라가서 처리되므로 SQL 문장을 처리하는 과정에서 발생한 모든 블록 개수로도 볼수 있음.

---

• 물리적 I/O는 SQL 문장을 처리하는 과정에서 디스크에서 발생한 총 블록 I/O
• SQL처리 도중 읽어야 할 블록을 버퍼캐시에서 찾지 못할때만 디스크를 액세스하므로 논리적 I/O 중 일부를 물리적으로 I/O함.
• 물리적  I/O는 처리되는 시간이 느림

🔎 버퍼캐시 히트 (Buffer Cache Hit Ratio, 'BCHR')

• 버퍼캐시가 히트되는 정도를 의미

BHCR = ( 캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수 ) × 100        
= ( ( 논리적 I/O - 물리적 I/O ) / 논리적 I/O ) × 100        
= ( 1 - ( 물리적 I/O ) / (논리적 I/O ) ) × 100

• BCHR이 SQL 성능을 좌우하지만, BCHR이 높다고 해서 효율적인 SQL을 의미하지 않음.그 이유는 같은 블록을 비효율적으로 반복해서 읽는 것도 BCHR이 높아지기 때문

🔎 Single Block I/O vs. Multiblock I/O

• 메모리 캐시가 클수록 좋지만, 비용적인 한계, 기술적인 한계 때문에 데이터를 모두 캐시에 적재할 수는 없음
• 따라서 전체 데이터 중 일부만 캐시에 적재해서 읽을 수 있음 → 캐시에서 찾지 못한 데이터 블록은 I/O Call을 통해 디스크에서 DB 버퍼캐시로 적재하고서 읽음.
  
  
• Single Block I/O :  I/O Call 할 때, 한 번에 한 블록씩 요청하기도 하고, 여러 블록씩 요청하기도 한다. 한 번에 한 블록씩 요청 해서 메모리에 적재하는 방식  (소량 데이터 검색)
• Multiblock I/O : 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식 , 테이블 전체 스캔할 때, 테이블이 클 때 MultiBlock IO 방식

Single Block I/O 가 나타나는 오퍼레이션

- 인덱스 루트 블록을 읽을 때
- 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
- 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
- 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때

 

🔎 Table Full Scan vs Index Range Scan

• Table Full Scan : 테이블에 저장된 데이터를 읽는 방식으로 테이블 전체를 스캔해서 읽는 방식
• 즉, 테이블에 속한 블록 '전체'를 읽어서 사용자가 원하는 데이터를 찾는 방식
• Sequential Access
• Multiblock I/O
• 대량 데이터 검색 시 유리

---

• Index Range Scan : 인덱스를 이용해서 읽는 방식
• 즉, 인덱스에서 '일정량'을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식 (ROWID : 테이블레코드가 디스크 상에 어디 저장됐는지를 가리키는 위치 정보)
• Random Access
• Singleblock I/O
• 소량 데이터 검색 시 유리

🔎 캐시 탐색 매커니즘

• Direct Path I/O를 제외한 모든 블록 I/O는 메모리 버퍼캐시를 경유

버퍼캐시 탐색 과정을 거치는 오퍼레이션

- 인덱스 루트 블록을 읽을 때
- 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
- 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
- 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때
- 테이블 블록을 Full Scan 할 때

• DBMS에서는 버퍼캐시를 해시 구조로 관리.
• 버퍼캐시에서 블록을 찾을 때 해시 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터(Pointer)로 버퍼 블록을 액세스하는 방식을 사용

해시 구조의 특징

-같은 입력 값은 항상 동일한 해시 체인(=버킷)에 연결됨
- 다른 입력 값(예를 들어, 4와 9)이 동일한 해시 체인(=버킷)에 연결될 수 있음
- 해시 체인 내에서는 정렬이 보장되지 않음

🔎 메모리 공유자원에 대한 엑세스 직렬화

• 버퍼캐시는 SGA 구성요소

버퍼캐시에 캐싱된 버퍼블록은 모두 공유자원 → 모두에게 권한이 있기 때문에 누구나 접근가능
( 문제는 하나의 버퍼블록을 두 개 이상 프로세스가 ‘동시에’ 접근하려고 할 때 발생함. 동시에 접근하면 블록 정합성에 문제가 생길 수 있기 때문)

• 하나의 블록을 두 개 이상의 프로세스가 동시에 접근하려고 할때의 문제를 방지하기 위해 직렬화(Serialization) 메커니즘을 사용 
• 래치 ( Latch ) 직렬화 메커니즘을 지원하는 것
• 캐시버퍼 체인뿐만 아니라 버퍼블록 자체에도 직렬화 메커니즘이 존재한다. 바로 ‘버퍼 Lock’이다. 이런 직렬화 메커니즘에 의한 캐시 경합을 줄이려면, SQL 튜닝을 통해 쿼리 일량(논리적 I/O) 자체를 줄여햐 한다.
• 버퍼 Lock : 캐시버퍼 체인 뿐만 아니라 버퍼 블록 자체에도 직렬화 메커니즘이 필요
• 직렬화 메커니즘에 의한 캐시 경합을 줄이려면, SQL 튜닝을 통해 쿼리 일량(논리적 I/O) 자체를 줄여야 함.