seoyyyy-dev
1회차 SQL 처리 과정과 I/O 정리 본문
1. SQL 파싱과 최적화
1.1 SQL 이란?
- Structured(구조적인) Query(질의) Language(언어) 의 줄임말
- 집합적(set-based), 선언적(declarative) 인 질의언어
- PL/SQL, SQL Server T-SQL 처럼 절차적(procedure) 프로그래밍을 구현할 수 있는 확장언어 제공
1.2 SQL 최적화
- SQL 최적화 과정
① SQL 파싱: 사용자로부터 SQL을 전달 받으면 가장 먼저 SQL 파서가 파싱을 진행
→ 파싱 트리 생성: SQL 문을 이루는 개별 구성 요소 분석 및 파싱 트리 생성
→ Syntax 체크: 문법적 오류 체크
→ Semantic 체크: 의미상 오류 체크(존재하지 않는 테이블, 컬럼, 권한 체크)
② SQL 최적화: 옵티마이저(Optimizer)가 미리 수집한 시스템 및 오브젝트 통계 정보를 바탕으로 다양한 실행 경로를 생성 및 비교한 후 효율적인 하나 선택
③ 로우 소스 생성: 로우 소스 생성기가 옵티마이저②가 선택했던 실행경로를 확인하여 실제 실행 가능한 코드 OR 프로시저 형태로 포맷팅
1.3 SQL 옵티마이저
- 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로 선택, DBMS 핵심 엔진
- 옵디마이저 최적화 단계
① 사용자에게 전달받은 쿼리를 수행하는 데 후보군이 될만한 실행계획들을 찾아냄
② 데이터 딕셔너리에 미리 수집해둔 오브젝트 통계 및 시스템 통계 정보를 이용해 각 실행계획의 예상비용 선정
③ 최저 비용의 실행계획 선택
1.4 실행계획(Execution Plan)과 비용(Cost)
- 실행계획: SQL 실행경로 미리보기
: 실행계획을 통해 작성한 SQL이 테이블 스캔인지 인덱스 스캔인지, 어떤 인덱스를 사용하는지 확인할 수 있다.
- 옵티마이저가 실행계획을 선택하는 근거 예시(더보기 클릭!)
-- 테스트를 위해 테이블 생성
CREATE TABLE T
AS
SELECT d.NO, e.*
FROM emp e, (SELECT rownum NO FROM dual CONNECT BY LEVEL <= 1000) d;
-- 옵티마이저가 최적의 경로를 탐색하는지 알기위해 인덱스 2개 생성
CREATE INDEX t_x01 ON t(deptno, no);
CREATE INDEX t_x02 ON t(deptno, job, no);
select *
from t
where deptno = 10
where no = 1;
→ 위의 select 쿼리 실행계획을 보면 옵티마이저는 아래와 같이Cost 가2로 나타나는 t_x01 인덱스를 선택
→ t_x02 인덱스를 강제로 사용하도록 힌트를 주어 실행계획을 살펴보면 Cost가 5로 표시
→ Table Full Scan 하도록 강제로 힌트를 주어 실행계획을 살펴보면 cost가 29로 제일 높음
따라서 옵티마이저가 비용을 기반으로 t_x01 인덱스를 선택한 것을 알 수 있음
- 비용(Cost) : 쿼리를 수행하는 동안 발생할 것으로 예상되는 I/O 횟수 또는 예상 소요시간을 표현한 값
: 비용도 옵티마이저가 통계정보를 활용해 계산한 예상치이므로(실측치가 아님) 실제 수행 시 발생하는 I/O 또는 시간이 많이 차이날 수 있다.
1.5 옵티마이저 힌트
- 옵티마이저의 한계로 인해 최선의 실행계획으로 선택되지 않았을 때 개발자가 직접 쿼리에 힌트를 주어 더 효율적인 액세스 경로를 찾아낼 수 있음
- 옵티마이저 사용방법
SELECT /*+ index(A 고객_PK) */
고객명, 연락처, 주소, 가입일시
FROM 고객 A
WHERE 고객 A
WHERE 고객ID = '00000008';- 주의사항
힌트안에 인자 나열 시 ','(콤마) 사용이 가능하지만 힌트와 힌트 사이에 사용하게 되면 첫번째 힌트만 유효하게 됨
/*+ index(A A_X01) INDEX (B, B_X03) */ => 유효
/*+ index(A A_X01), INDEX (B B_X03) */ => 첫 번째 힌트만 유효테이블 지정 시 스키마명까지 명시하면 안되며 ALIAS 지정 시 힌트에도 반드시 ALIAS로 사용해주어야함
SELECT /*+ FULL(SCOTT.EMP) */ * FROM EMP; => 무효 'FULL(EMP)'로 지정해야 함
SELECT /*+ FULL(EMP) FROM*/ * FROM EMP E; => 무효'FULL(E)'로 지정해야 함또한 힌트에 컬럼 일부만 지정하여 해당 컬럼이 선두인 인덱스 중에 알아서 선택되도록 할 수 있으며 이경우 조인 방식과 순서, 고객테이블 액세스 방식은 옵티마이저가 알아서 판단한다.
SELECT /*+ INDEX(A (주문일자)) */ A.주문번호, A.주문금액, B.고객명, B.연락처, B.주소
FROM 주문 A, 고객 B
WHERE A.주문일자 = :ORD_DT
AND A.고객ID = B.고객ID옵티마이저의 자율적 판단에 맡기기 힘든 중대한 시스템인 경우엔 힌트 사용시 빈틈없이 기술하는 것이 좋다.
SELECT /*+ LEADING(A) USE_NL(B) INDEX(A (주문일자)) INDEX(B 고객_PK) */
A.주문번호, A.주문금액, B.고객명, B.연락처, B.주소
FROM 주문 A, 고객 B
WHERE A.주문일자 = :ORD_DT
AND A.고객ID = B.고객ID;
자주 사용하는 힌트 목록(더보기 클릭!)
| 분류 | 힌트 | 설명 |
| 최적화목표 | ALL_ROWS | 전체 처리속도 최적화 |
| FIRST_ROWS(N) | 최초 N건 응답속도 최적화 | |
| 액세스 방식 | FULL | Table Full Scan으로 유도 |
| INDEX | Index Scan으로 유도 | |
| INDEX_DESC | Index를 역순으로 스캔하도록 유도 | |
| INDEX_FFS | Index Fast Full Scan으로 유도 | |
| INDEX_SS | Index Skip Scan으로 유도 | |
| 조인순서 | ORDERED | FROM 절에 나열된 순서대로 조인 |
| LEADING | LEADING 힌트 괄호에 기술한 순서대로 조인 (예) LEADING(T1 T2) |
|
| SWAP_JOIN_INPUTS | 해시 조인 시, BUILD INPUT을 명시적으로 선택 (예) SWAP_JOIN_INPUTS(T1) |
|
| 조인방식 | USE_NL | NL 조인으로 유도 |
| USE_MERGE | 소트 머지 조인으로 유도 | |
| USE_HASH | 해시 조인으로 유도 | |
| NL_SJ | NL 세미조인으로 유도 | |
| MERGE_SJ | 소트 머지 세미조인으로 유도 | |
| HASH_SJ | 해시 세미조인으로 유도 | |
| 서브쿼리 팩토링 |
MATERIALIZE | WITH 문으로 정의한 집합을 물리적으로 생성하도록 유도 예) WITH /*+ MATERIALIZE */ T AS (SELECT ....) |
| INLINE | WITH 문으로 정의한 집합을 물리적으로 생성하지 않고 INLINE으로 처리하도록 유도 예) WITH /*+ INLINE */ T AS (SELECT ... ) |
|
| 쿼리 변환 | MERGE | 뷰 머징 유도 |
| NO_MERGE | 뷰 머징 방지 | |
| UNNEST | 서브쿼리 Unnesting 유도 | |
| NO_UNNEST | 서브쿼리 Unnesting 방지 | |
| PUSH_PRED | 조인조건 Pushdown 유도 | |
| NO_PUSH_PRED | 조인조건 Pushdown 방지 | |
| USE_CONCAT | OR 또는 IN-List 조건을 OR-Expansion으로 유도 | |
| NO_EXPAND | OR 또는 IN-List 조건에 대한 OR-Expansion 방지 | |
| 병렬처리 | PARALLEL | 테이블 스캔 또는 DML을 병렬방식으로 처리하도록 유도 예) PARALLEL (T1 2) PARALLEL(T2 2) |
| PARALLEL_INDEX | 인덱스 스캔을 병렬방식으로 처리하도록 유도 | |
| PQ_DISTRIBUTE | 병렬 수행 시 데이터 분배 방식 결정 예) PQ_DISTRIBUTE(T1 HASH HASH) |
|
| 기타 | APPEND | Direct-Path Insert 유도 |
| DRIVING_SITE | DB Link Remote 쿼리에 대한 최적화 및 실행 주체 지정 (Local 또는 Remote) |
|
| PUSH_SUBQ | 서브쿼리를 가급적 빨리 필터링하도록 유도 | |
| NO_PUSH_SUBQ | 서브쿼리를 가급적 늦게 필터링하도록 유도 |
2. SQL 공유 및 재사용
2.1 소프트 파싱 VS 하드파싱
- 라이브러리 캐시(Library Cache): SQL 파싱, 최적화, 로우 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해두는 메모리 공간, SGA의 구성요소
- SGA (System Global Area): 서버프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어구조를 캐싱하는 메모리 공간
- 소프트파싱(Soft Parsing)
: 사용자가 SQL문을 전달하면 DBMS가 SQL을 파싱한 후 SQL이 라이브러리 캐시에 존재하는지 확인하여 캐시에 존재하면 바로 실행단계로 넘어가는 것
- 하드파싱(Hard Parsing)
: 캐시에 존재하지 않아 최적화 및 로우 소스 생성 단계까지 모두 거치게 되는 것
- 옵티마이저가 최적화하는데 사용하는 정보
● 테이블, 컬럼, 인덱스 구조에 관한 기본 정보
● 오브젝트 통계: 테이블 통계, 인덱스 통계, (히스토그램을 포함한) 컬럼 통계
● 시스템 통계: CPU 속도, Single Block I/O 속도, Multiblock I/O 속도 등
● 옵티마이저 관련 파라미터
=> 이 정보들을 사용하여 옵티마이징 할 때마다 매번 실행경로를 계산하면 CPU가 많이 소비되고 비효율적이므로 라이브러리 캐시가 필요
2.2 바인드 변수의 중요성
- 이름없는 SQL 문제
- 사용자 정의 함수(Function)/프로시저, 트리거, 패키지
: 생성 시 이름을 갖고 컴파일한 상태로 딕셔너리에 저장되며 사용자가 삭제하지 않는 한 영구 보관된다.
: 실행할 때 라이브러리 캐시에 적재함으로써 여러 사용자가 공유하면서 재사용함
- SQL
: 이름이 따로 없으며 전체 텍스트가 이름 역할을 함, 딕셔너리 저장 X
: 처음 실행 시 최적화 과정을 거쳐 동적으로 생성한 내부 프로시저를 라이브러리 캐시에 적재함으로써 여러 사용자가 공유 및 재사용
: 캐시공간 부족 시 버려졌다가 다음 실행 시 다시 최적화 과정을 거쳐 캐시에 적재
- 공유 가능 SQL
- 라이브러리 캐시에서 SQL을 찾기 위해서는 키 값이 'SQL 문 그 자체' 이므로 조건까지 똑같아야 같은 쿼리로 인식함
- 아래 쿼리는 의미적으로는 같지만 모두 다른 쿼리라고 인식해 각각 최적화되며 라이브러리 캐시에 별도로 저장
SELECT * FROM EMP WHERE EMPNO = 7900;
select * from EMP where EMPNO = 7900;
select * from emp where empno = 7900;
select * from emp where empno = 7900 ;
select * from emp where empno = 7900 ;
select * from scott.emp where empno = 7900;
select /* comment */ * from emp where empno = 7900;
select /*+ first_rows */ * from emp where empno = 7900;- JAVA에서 SQL문 작성 시 아래 쿼리와 같이 실행하게 되면 하드파싱으로 진행될 것임
Public void login(String login_id) throws Exception {
String SQLStmt = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = '" + login_id + "'";
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(SQLStmt);
if(rs.next()){
...
}
rs.close();
st.close();
}바인드 변수를 사용해주면 소프트 파싱이 가능해짐
/** 바인드 변수 사용 시 로그인 아이디가 달라져도 라이브러리 캐시에는**/
/** SELECT * FROM CUSTOMER WHERE LOGIN_ID = :1 하나만 조회된다. **/
Public void login(String login_id) throws Exception {
String SQLStmt = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = ?";
Statement st = con.createStatement();
st.setString(1, login_id);
ResultSet rs = st.executeQuery(SQLStmt);
if(rs.next()){
...
}
rs.close();
st.close();
}3. 데이터 저장 구조 및 I/O 메커니즘
3.1 SQL이 느린 이유
- 프로세스(Process) 생명 주기
생성(new) → 승인(admitted)↘ ↗ exit → 종료(terminate)
준비(ready) ← interrupted / dispatched → 실행(running)
I/O 완료or 이벤트 완료 ↖ ← 대기(waiting) ← ↙ I/O 요청 or 이벤트 대기
- 실행중인 프로세스는 interrupt에 의해 수시로 실행 준비(runnable Queue)로 전환했다가 다시 실행상태로 전환됨
- interrupt없이 열심히 일하던 프로세스도 디스크에서 데이터를 읽어야 할 땐 CPU를 OS에 반환하고 잠시 수면(waiting) 상태에서 I/O가 완료되기를 기다림(Wait Queue)에서 잠을 잠 => I/O가 많으면 성능이 느림
3.2 데이터베이스 저장구조
● 블록: 데이터를 읽고 쓰는 단위
- 한블록 및 하나의 익스텐트는 하나의 테이블이 독접한다.
● 익스텐트: 공간을 확장하는 단위, 연속된 블록 집합
- 테이블이나 인덱스에 데이터를 입력하다 공간 부족 시 테이블 스페이스로부터 익스텐트를 추가로 할당받는다.
- 익스텐트 단위로 확장하지만 실제로 데이터를 저장하는 공간은 데이터 블록이다.
● 세그먼트: 데이터 저장공간이 필요한 오브젝트(테이블, 인덱스, 파티션, LOB 등)
- 여러 익스텐트로 구성됨
- LOB 컬럼은 그 자체가 하나의 세그먼트를 구성하므로 자신이 속한 테이블과 다른 별도 공간에 값 저장
● 테이블 스페이스: 세그먼트를 담는 콘테이너
● 데이터 파일: 디스크 상의 물리적인 OS 파일
- 세그먼트에 할당된 모든 익스텐트가 같은 데이터파일에 위치하지 않을 수 있다.(파일 경합을 줄이기 위해 DBMS가 데이터를 가능한 여러 데이터파일로 분산 저장하기 때문)
- 익스텐트 내 블록은 서로 인접한 연속된 공간이지만, 익스텐트끼리는 연속된 공간이 아님
- 세그먼트에 할당된 익스텐트 목록 조회법(오라클)
SELECT SEGMENT_TYPE, TABLESPACE_NAME, EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS
FROM DBA_EXTENTS
WHERE OWNER = 'SEOYYYY'
AND SEGMENT_NAME = 'T' -- 아까 만든 테이블
ORDER BY EXTENT_ID;
● DBA(Data Block Address): 디스크 상에서 몇 번 데이터 파일의 몇 번째 블록인지 나타내는 고유한 주소
- 인덱스로 테이블 레코드를 읽을 때 인덱스 ROWID를 이용해야 하는데 ROWID는 DBA + 로우 번호(블록 내 순번)으로 구성
- 테이블 스캔 시 테이블 세그먼트 헤더에 저장된 익스텐트 맵을 이용, 익스텐트 맴을 통해 각 익스텐트의 첫번째 블록 DBA를 알 수 있다.
3.3 블록 단위 I/O
- 테이블의 데이터 I/O 단위는 블록이므로 특정 레코드를 읽고자 할 때 무조건 해당 블록을 통째로 읽어야 한다.
- 오라클은 기본 블록 하나당 8KB 이므로 1Byte만 읽고 싶어도 기본 8KB를 읽게된다.
- 테이블뿐 아니라 인덱스도 블록단위로 데이터를 읽고 쓴다.
3.4 시퀀셜 액세스 VS 랜덤 액세스
① 시퀀셜(Sequential) 액세스: 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식
: 인덱스 리프 블록에 명시된 앞뒤를 가리키는 주소를 따라서 순차적(앞 또는 뒤로)으로 스캔하는 방식
: 테이블 블록간에는 서로 논리적인 연결고리를 갖고 있지 않으며 세그먼트에 할당된 익스텐트 목록을 세그먼트 헤더에 맵으로 관리한다. 익스텐트 맵은 각 익스텐트의 첫 번째 블록 주소 값을 갖는다.(블록은 연속된 공간으로 첫번째만 알면 됨)
각 익스텐트의 첫번째 블록 뒤에 연속해서 저장된 블록을 순서대로 읽으면 이것이 Full Table Scan
② 랜덤(Random) 액세스: 논리적, 물리적 순서를 따르지 않고 레코드 하나를 읽기 위해 한 블록씩 접근(=touch)하는 방식
3.5 논리적 I/O VS 물리적 I/O
- DB 버퍼캐시
: 디스크에서 읽은 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O Call 을 줄이는데 목적이 있다.
: 라이브러리 캐시는 '코드 캐시', DB 버퍼 캐시는 '데이터 캐시'
: SGA의 가장 중요한 구성요소 중 하나
: 데이터 블록을 읽을 때 항상 버퍼 캐시부터 탐색 후 블록이 존재하면 캐시의 블록을 사용, 존재하지 않으면 데이터 파일에 물리적으로 액세스(I/O Call)를 거쳐 DB 버퍼 캐시에 적재한다.
: 버퍼캐시는 공유 메모리 영역이므로 같은 블록을 읽는 다른 프로세스도 득을 본다.
- 논리적 블록 I/O : SQL을 처리하는 과정에서 발생한 총 블록 I/O (메모리 I/O + Direct Path I/O)
: DB 버퍼 캐시에서 블록을 읽은 횟수와 일치한다.
- 물리적 블록 I/O : 디스크에서 발생한 총 블록 I/O
: SQL 처리 도중 블록을 버퍼캐시에서 못 찾았을 때 디스크를 액세스하므로 물리적 I/O가 발생한다.
: 메모리 I/O는 전기적 신호, 디스크 I/O는 액세스 암(Arm)을 통해 물리적 작용이 일어나 상당히 느리다. 디스크 경합이 심할땐 더느림
: 연속 실행 시 DB버퍼캐시에서 해당 테이블 블록의 점유율이 높아져 물리적 I/O 횟수가 줄어든다.(한참 뒤 실행하면 반대로 물리적 I/O 횟수가 증가함)
- 버퍼캐시 히트율(Buffer Cache Hit Ratio)
: 읽은 전체 블록 중 물리적인 디스크 I/O를 수반하지 않고 곧바로 메모리에서 찾은 비율
: 온라인 트랜잭션을 주로 처리하는 애플리케이션이라면 시스템 레벨에서 평균 99% 히트율을 달성해야 한다.
BCHR = ( 캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수 ) X 100
= ( ( 논리적 I/O - 물리적 I/O ) / 논리적 I/O ) X 100
= ( 1 - (물리적I/O) / (논리적 I/O) ) X 100
물리적 I/O = 논리적 I/O X (100% - BCHR)=> 물리적 I/O가 성능을 결정하지만 실제 SQL 성능 향상을 위해 논리적 I/O를 줄여야 한다.(물리적 I/O는 통제 불가능한 외생변수)
=> BCHR이 평균 70%일 때 논리적 I/O가 10,000개면 물리적 I/O는 대략 3,000개쯤 발생할 것으로 예상된다.
=> 논리적 I/O를 1,000개로 줄이면 물리적 I/O도 300개로 감소하고 성능도 열배 향상된다.
- 논리적 I/O를 줄이는 방법?
: SQL을 튜닝해서 읽는 총 블록 개수를 줄이면 된다.
SQL 트레이스를 통해 수집한 Call 통계 정보에서 BCHR 구하기
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.001 0 0 0 0
Execute 1 0.010 0.006 0 0 0 0
Fetch 2 138.680 1746.630 601458 1351677 12367 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 138.690 0.00 601458 1351677 12367 1Query + Current = 논리적 I/O(물리적 I/O 수가 포함되어있음)
1,351,677+ 12,367 = 1,364,044
disk = 물리적 I/O
601,458
BCHR = ( 1 - (Disk / ( Query + Current ) ) ) x 100
= ( 1 - (601,458 / 1,351,677 + 12,367 ) ) ) x 100
= 55.9 %
- BCHR이 SQL 성능을 좌우하지만 BCHR이 높다고 효율적인 SQL을 의미하는 건 아니다.
- 같은 블록을 비효율적으로 반복해서 읽으면 BCHR이 높아질 수 있다.
3.6 Single Block I/O VS Multiblock I/O
- Single Block I/O
: 캐시에서 찾지 못한 데이터 블록을 I/O Call을 통해 한번에 한 블록 씩 요청해 DB 버퍼캐시에 적재하는 방식
: 인덱스를 이용할 때 기본적으로 인덱스와 테이블 블록 모두 이 방식을 사용
: Single Block I/O 대상 오퍼레이션
- 인덱스 루트 블록을 읽을 때
- 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
- 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
- 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때
- Multiblock I/O
: 캐시에서 찾지 못한 특정 블록을 읽으려고 I/O Call 할 때 디스크 상에 그 블록과 '인접한' 블록들을 한꺼번에 읽어 캐시에 미리 적재하는 방식
: 같은 익스텐트 내의 '인접한' 블록만 읽기 때문에 Multiblock으로 읽더라도 다른 익스텐트까지 같이 담지 못한다.
: Multiblock I/O 대상 오퍼레이션
- 인덱스를 이용하지 않고 테이블 전체를 스캔할 때
- 대용량 테이블 Full Scan 시 유리
: 테이블이 클수록 Multiblock I/O 단위도 크면 좋다. 프로세스가 대기하며 잠자는 횟수를 줄여주기 때문
: DBMS 블록사이즈가 얼마이건 OS단에서는 보통 1MB 단위로 I/O를 수행한다. 테이블 전체 블록 읽을 때는 손수레에 한번에 담을 수 있는 만큼 최대한 많이 담아야 유리하다.
- 한번에 읽을 수 있는 multiblock 수
3.7 Table Full Scan VS Index Range Scan
- Table Full Scan: 테이블 전체를 스캔해서 읽는 방식
: 테이블에 속한 블록 '전체를' 읽어 사용자가 원하는 데이터를 찾는 방식
: 한번에 많은 데이터를 처리하는 집계용 SQL에서 유리함
: 시퀀셜 액세스(순차적으로 읽음), Multiblock I/O(한번에 인접한 여러 블록 읽음)
- Index Range Scan: 인덱스를 이용한 테이블 엑세스
: 인덱스에서 '일정량'을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식
: ROWID는 디스크상에 테이블 레코드가 어디에 저장됐는지 가리키는 위치
: 소량의 데이터를 검색할 때 유리함
: 많은 데이터를 읽으면 물리적인 블록 I/O 뿐만 아니라 논리적인 블록 I/O도 불리하다.(읽었던 블록을 반복해서 읽게될 수 있음)
: 랜덤 액세스, Single Block I/O(한번에 하나의 블록 읽음)
=> 인덱스가 항상 옳은 것은 아니다. 조회해야하는 데이터가 많으면 Table Full Scan, 소량이면 Index Range Scan!
3.8 캐시 탐색 매커니즘
- 버퍼 캐시 탐색과정을 거치는 오퍼레이션
● 인덱스 루트 블록을 읽을 때
● 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
● 인덱스 브랜치 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때
● 테이블 블록을 Full Scan 할 때
- 버퍼 캐시에서 블록을 찾을 때 해시 알고리즘으로 버퍼 헤더를 찾고 거기서 얻은 포인터(pointer)로 버퍼 블록을 액세스하는 방식으로 사용한다.
- 캐시 탐색 예시
① 20번 블록을 찾고자 할 때 Mod(20, 5) => 20 % 5 = 0 이므로 해시값 0번의 체인을 읽는다.
② 27번 블록을 찾고자 할 때 Mod(27, 5) => 27 % 5 = 2 이므로 2번 체인을 탐색했는데 없다면 디스크에서 읽어와 세번째(2번) 체인에 연결시킨 후 읽어온다.
- 해시구조의 특징
● 같은 입력 값은 항상 동일한 해시 체인(=버킷)에 연결된다.
● 다른 입력 값(예를 들어, 4와 9)이 동일한 해시 체인(=버킷)에 연결될 수 있다.
● 해시 체인 내에서는 정렬이 보장되지 않는다.
- 메모리 공유자원에 대한 액세스 직렬화
: 버퍼캐시는 SGA 구성요소이므로 버퍼캐시에 캐싱된 버퍼블록은 모두 공유자원이다. => 누구나 접근가능
: 하나의 버퍼블록을 두 개 이상의 프로세스가 '동시에' 접근하려고 할 때 문제가 발생한다. => 동시 접근 시 정합성 문제
: 이를 위해 직렬화(serialization) 메커니즘이 필요. => 래치(Latch), 버퍼 Lock으로 직렬화 매커니즘 구현
- 캐시버퍼 체인 래치
: 해시 체인을 스캔하는 동안 다른 프로세스가 체인 구조를 변경하는 일이 생기지 않도록 자물쇠(Latch)를 걸고 자물쇠를 열 수 있는 (key)를 획득한 프로세스만이 체인으로 진입할 수 있다.
- 버퍼 Lock
: 읽고자하는 블록을 찾았으면 곧바로 캐시버퍼 체인 래치를 해제해야 한다. 그래야 대기하고 있던 다른 프로세스들이 작업을 재개할 수 있다.
: 래치를 해제한 상태로 작업중인 버퍼 Lock에 후행 프로세스가 동시에 접근해서 데이터를 읽고 쓰면 정합성 문제가 발생할 수 있으므로 버퍼에도 Lock을 설정한다.(로우 lock과는 다름, 로우 lock을 설정하는 것 자체도 블록을 변경하는 작업)
데이터 작업시 직렬화 매커니즘 간단 순서
: 캐시버퍼 체인 래치를 획득 => 버퍼 Lock 설정(먼저 버퍼에 Exclusive Lock이 걸려있으면 이벤트 대기하고 래치 해제) => 캐시버퍼 체인 래치 해제 => 읽기/쓰기 작업 완료(버퍼 캐시에 없으면 디스크 I/O 발생) => 캐시버퍼 체인 래치 획득 => 버퍼 Lock 해제 => 캐시버퍼 체인 래치 해제
'개발 독서 스터디 > 친절한 SQL 튜닝' 카테고리의 다른 글
| 4회차 조인 튜닝 (0) | 2025.02.25 |
|---|---|
| 3회차 인덱스 튜닝 - 3 (0) | 2025.02.08 |
| 3회차 인덱스 튜닝 - 2 (0) | 2025.02.08 |
| 3회차 인덱스 튜닝 - 1 (1) | 2025.02.08 |
| 2회차 인덱스 기본 (0) | 2025.01.21 |
