💡 Table Full Scan : 시퀀셜 액세스, Multiblock I/O >> 한번의 I/O call로 수백개 블록 읽음 💡
💡 Index Range Scan: 랜덤 액세스, Singleblock I/O >>큰테이블에서 소량 읽을때 사용 ! 💡
1.1 SQL 파싱과 최적화
SQL 구조적 언어
PL/SQL 절차적 언어, 프로그래밍구현
🟩 SQL 최적화
:DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행가능한 상태로 만드는 과정
SQL 파싱 → SQL 최적화 → 로우소스 생성
- SQL 입력받으면 SQL 파서가 파싱을 진행
- 파싱트리생성, Syntax체크, Sementic체크
- SQL 최적화 : 옵티마이저가 실행 경로를 생성하고 하나를 선택
- 로우소스 생성 :옵티마이저가 선택한 실행 결로를 실행 가능한 코드로 포맷팅
🟨 SQL 옵티마이저 : 최적의 데이터 엑세스 경로를 선택하는 핵심 엔진
- 후보군들의 실행계획을 찾고
- 데이터 딕셔너리에 미리 수집해둔 오브젝트 통계및 시스템 통계정보를 통해 실행계획의 예상비용 산정
- 최저비용 실행계획 선택! >> I/O횟수 or 소요시간
- 힌트 사용 /+INDEX(A 고객_PK)/
- 1. 콤마 사용 안됨
- 2. 스키마명 명시 안됨
- 3. ALIAS로 사용
1.2 SQL 공유 및 재사용
🟩 소프트 파싱 VS 하드파싱
SQL은 반복 재사용 할수 있도록 **라이브러리 캐시**에 캐싱해 둠.
- SQL은 라이브러리 캐시에 있는지 확인한 후 있으면 바로 소프트 파싱
- 없으면 실행계획 생성, 로우 소스 생성까지 하드파싱 진행
→ 하드파싱은 CPU리소스를 많이 사용함
: 무수히 많은 실행경로를 도출하고, 딕셔너리와 통계정보까지 읽어와서 하드함.
→ 그래서 바인드 변수 중요함!
파라미터 Driven 방식
- 함수, 프로시저, 트리거 ,패키지는 이름을 갖고 있어**라이브러리 캐시** 적재하여 영구히 재사용함
- SQL은 이름이 없기에 **라이브러리 캐시**에 저장하지만 캐시공간이 부족하면 버려졌다가 다음에 다시 최적화 과정후 캐싱됨 (SQL문 자체가 이름)
1-3 데이터 저장구조 및 I/O 메커니즘
🟩 SQL 이 느린 이유는 I/O 때문이다.
:여러 프로세스가 CPU를 공유하지만, 특정순간에는 하나의 프로세스만 CPU를 사용할수 있음.
:os함수가 I/O call 하고 cpu반환하채 sleep 잠을잠. I/O가 완료되기를 기다림.
: I/O가 많으면 느려질수 밖에 없음
:디스크 I/O로 인해 SQL이 느려짐
🟩 데이터베이스 저장구조
데이터 파일: 디스크 상의 물리적인 OS 파일
테이블 스페이스: 세그먼트를 담는 콘테이너
세그먼트: 데이터 저장공간이 필요한 오브젝트 → 테이블, 인덱스, 파티션등
익스텐트: 공간을 확장하는 단위, 연속된 블록 (익스텐트끼리는 연속되지않음)
블록: 데이터를 읽고 쓰는 단위
오라클 8KB블록사용 , 1 byte - 8KB읽음
show parameter block_size
select value from v$parameter where name=’db_block_size’;
🟩 시퀀셜 액세스 / 랜덤 엑세스
시퀀셜 액세스 : 논리적, 물리적 연결된 순서에 따라 순차적으로 블록 읽음 >> index
랜덤 액세스: 레코드 하나를 읽기위해 한 블록씩 접근하는 방식
🟩 논리적 I/O ,물리적 I/O
디스크 I/O가 SQL 성능을 결정한다.
- 자주 읽는 블록을 매번 디스크에서 읽는것은 매우 비효율적
- DBMS 데이터 캐싱 메커니즘 필수
라이브러리 캐시 SQL실행계획, 함수, 프로시저가 있는 코드캐시
DB버퍼캐시는 데이터가 있는 데이터 캐시
❗블록에 대한 반복적인 I/O call을 줄이는데 목적!
논리적 I/O : SQL을 처리하는 과정에서 발생하는 총 블록 I/O
물리적I/O : 디스크에서 발생한 총블록 I/O
- 논리적 I/O 일부는 물리적I/O가 될수 있음
모든 블록은 DB버퍼 캐시를 경유해서 읽는다.
버퍼캐시에 없으면 디스크에서 읽고 버퍼캐시에 적재한 후 읽는다.
🟨 버퍼캐시 히트율 bhcr
: 물리적인 디스크 I/O를 수반하지 않고 곧바로 메모리에서 찾은 비율
: 온라인 트랜잭션 어플이라면 평균 99% 히트율을 달성해야함.
🟨 논리적인 I/O를 줄여야 성능이 좋아짐
→ 논리적 I/O를 줄임으로써 물리적 I/O를 줄이는것이 튜닝
🟩 single block I/O, multi block I/O
I/O call 할때,
Single block I/O : 한블록씩 읽어 메모리에 적재, 인덱스 처럼 소량데이터 읽을떄 사용
Multi block I/O: 인접한 여러블록을 읽어 메모리에 적재 , Fullscan 처럼 대용량 테이블 읽을떄 사용
- 인접한 =같은 익스텐트에 속한 블록만 읽음
🟩 Table Full Scan , Index Range Scan
Table Full Scan : 테이블 전체를 읽는 것
- 집계함수에 유리
Index Range Scan : 인덱스에서 일정량을 스캔하여 얻은 rowid로 테이블 레코드를 찾는 방식
🟩 버퍼캐시 탐색 메커니즘 (해시구조로 관리)
❗ 버퍼캐시에서 블록을 찾을때,
먼저 해시 체인을 찾고 연결되어 있는 버퍼헤더를 찾고 거기서 얻은 포인터로 버퍼블록을 엑세스 하는 방식
- 이때 없으면 디스크로부터 읽어서 해시 체인에 연결함
- 버퍼 블록에 두개이상의 프로세스가 동시 접근하려고 할떄, 경합 발생
- 그래서 블록에 프로세스들이 순차적으로 접근하도록 직렬화 메커니즘이 래치 Latch
→ 읽고자하는 블록을 찾았으면 바로 래치를 해제해야함
→ 해제한 상태로 읽고 쓰는 도중, 다른 프로세스가 접근하는것을 방지하기 위해 버퍼 LOCK사용
→ 캐시 래치를 해제하기 전에 버퍼 헤더에 LOCK