SQL 전문가(SQLP) 시험 노트개념 정리16 MIN
SQL 처리 과정과 블록 I/O 메커니즘
SQLP 3과목의 둘째 자리입니다. 파싱·최적화·로우 소스 생성의 단계, 라이브러리 캐시와 공유 커서, 블록 단위 I/O, 시퀀셜·랜덤 액세스, 논리적·물리적 I/O와 버퍼 캐시 히트율, Single·Multiblock Read를 다룹니다.
앞 노트에서 SQL을 받아 실행하는 것이 서버 프로세스이고, 그 서버 프로세스가 블록을 버퍼 캐시에서 찾거나 데이터 파일에서 읽어 온다고 했습니다. 이 노트는 그 사이를 채웁니다. SQL 문자열 한 줄이 실행 가능한 절차로 바뀌는 과정, 그리고 그 절차가 블록을 읽는 방식입니다. 튜닝이 줄이려는 것은 결국 파싱 횟수와 읽는 블록 수 두 가지입니다.
SQL 처리 과정
파싱 단계
SQL은 실행되기 전에 세 단계를 거칩니다.
- 파싱 — 문장을 구성 요소로 쪼개 트리로 만들고 두 가지를 검사합니다. 문법에 맞는지 보는 Syntax 체크(
SELEC처럼 틀린 키워드를 잡는다)와, 테이블·칼럼이 실제로 있고 사용자에게 권한이 있는지 보는 Semantic 체크입니다. - 최적화 — 옵티마이저가 가능한 실행 방법 여럿을 놓고 비용을 견줘 하나를 고릅니다. 통계 정보를 읽어 조인 순서, 인덱스를 쓸지 여부를 정하는 단계이고 가장 무겁습니다.
- 로우 소스 생성 — 고른 실행계획을 실제로 실행할 수 있는 코드, 곧 로우 소스의 묶음으로 바꿉니다. 그다음이 실행입니다.
옵티마이저는 같은 결과를 내는 여러 처리 경로 가운데 가장 싸 보이는 것을 고르는 DBMS의 부품이고, 그 선택의 결과가 실행계획입니다.
하드 파싱과 소프트 파싱
1단계의 두 검사 뒤에 서버 프로세스는 공유 풀의 라이브러리 캐시에서 같은 SQL이 이미 처리된 적이 있는지 찾습니다. 라이브러리 캐시는 파싱한 SQL과 그 실행계획을 담아 두는 공유 풀의 한 영역입니다.
| 갈래 | 라이브러리 캐시에서 | 하는 일 |
|---|---|---|
| 소프트 파싱 | 같은 SQL을 찾았다 | 저장된 계획으로 곧바로 실행 |
| 하드 파싱 | 못 찾았다 | 최적화와 로우 소스 생성까지 다 하고 결과를 캐시에 올린다 |
하드 파싱은 CPU를 많이 쓰고, 캐시에 올리는 동안 공유 풀을 지키는 래치를 잡아 다른 세션을 기다리게 합니다.
라이브러리 캐시
SQL 커서
라이브러리 캐시에 올라간 SQL과 실행계획을 공유 커서라 부릅니다. 커서를 찾는 열쇠는 SQL 텍스트 그 자체입니다. 텍스트를 해시해 캐시를 찾으므로 대소문자 하나, 공백 하나, 주석 하나만 달라도 다른 SQL로 보고 새로 하드 파싱합니다.
SELECT * FROM 고객 WHERE 고객번호 = 1001;
select * from 고객 where 고객번호 = 1001;
SELECT * FROM 고객 WHERE 고객번호 = 1002;
세 문장은 결과의 모양이 같아도 텍스트가 모두 달라 공유 커서도 셋이 생깁니다. 같은 사용자가 같은 텍스트를 보내도 스키마가 달라 가리키는 테이블이 다르면 커서를 나눠 쓸 수 없습니다.
바인드 변수
조건 값만 바뀌는 SQL을 값마다 다른 텍스트로 보내면 고객 수만큼 하드 파싱이 일어납니다. 값을 텍스트에서 빼고 자리만 남긴 것이 바인드 변수입니다.
SELECT * FROM 고객 WHERE 고객번호 = :cust_no;
텍스트가 늘 같으므로 첫 실행만 하드 파싱하고 나머지는 같은 커서를 씁니다. 대신 옵티마이저가 최적화할 때 실제 값을 모르므로, 값에 따라 분포가 크게 다른 칼럼(대부분이 한 값인 상태 코드 같은 것)에서는 평균을 가정한 계획이 어떤 값에는 나쁘게 맞을 수 있습니다. 실행이 드물고 무거운 배치·분석 SQL은 상수를 쓰는 편이 나은 이유입니다.
블록 단위 I/O
블록 I/O
DBMS가 데이터를 읽고 쓰는 최소 단위는 블록입니다. 행 하나, 칼럼 하나가 필요해도 그 행이 든 블록 전체를 읽습니다. 그래서 SQL의 성능을 재는 단위는 돌려준 행 수가 아니라 읽은 블록 수입니다. 열 행을 돌려주는 쿼리가 열 행이 열 블록에 흩어져 있으면 열 블록을, 한 블록에 몰려 있으면 한 블록을 읽습니다. 인덱스도 블록에 저장되므로 인덱스를 거치는 것 역시 블록을 읽는 일입니다.
시퀀셜 액세스와 랜덤 액세스
| 기준 | 시퀀셜 액세스 | 랜덤 액세스 |
|---|---|---|
| 읽는 방식 | 논리적·물리적으로 이어진 차례대로 | 블록 하나를 찾아가 한 번에 하나 |
| 대표 예 | Full Table Scan, 인덱스 리프 블록 훑기 | 인덱스로 찾은 ROWID로 테이블 블록 찾아가기 |
| 한 번에 읽는 양 | 여러 블록을 모아 읽을 수 있다 | 한 블록 |
시퀀셜 액세스의 효율은 읽은 블록 가운데 버린 행이 얼마나 적은가(선택도)로 정해지고, 랜덤 액세스의 효율은 찾아간 횟수 자체를 줄여야 오릅니다. 인덱스 리프 블록은 앞뒤 블록을 가리키는 주소로 이어져 있어 범위를 차례로 훑을 수 있고, 거기서 얻은 ROWID로 테이블에 가는 순간 랜덤 액세스가 됩니다. 인덱스 튜닝의 큰 갈래 둘 — 인덱스 스캔 범위 줄이기와 테이블 랜덤 액세스 줄이기 — 가 이 둘과 짝을 이룹니다.
논리적 I/O와 물리적 I/O
두 I/O
논리적 I/O는 SQL이 버퍼 캐시에서 읽은 블록 수이고, 물리적 I/O는 그중 캐시에 없어 디스크에서 읽어 온 블록 수입니다. 서버 프로세스는 늘 캐시를 먼저 찾고, 없으면 디스크에서 캐시로 올린 뒤 읽으므로 물리적 I/O는 논리적 I/O 안에 듭니다. 트레이스에서 query·current 칼럼의 합이 논리적 I/O, disk 칼럼이 물리적 I/O입니다.
버퍼 캐시 히트율
읽은 블록 가운데 캐시에서 곧바로 찾은 비율이 버퍼 캐시 히트율(BCHR)입니다.
논리적 I/O가 10,000블록이고 그중 2,000블록을 디스크에서 읽었다면 %입니다. 같은 SQL을 다시 실행하면 블록이 캐시에 남아 있어 물리적 I/O가 줄고 히트율이 오릅니다.
물리적 I/O는 논리적 I/O × (100 − BCHR) ÷ 100입니다. BCHR은 시스템 상황에 따라 흔들려 SQL이 직접 정할 수 없지만, 논리적 I/O는 같은 SQL과 같은 데이터에서 늘 같습니다. 그래서 물리적 I/O를 줄이는 확실한 길은 논리적 I/O를 줄이는 것입니다.
블록을 읽는 단위
Single Block Read
인덱스 루트에서 브랜치, 리프로 내려가는 것처럼 다음에 읽을 블록이 앞 블록을 읽어야 정해지는 경우에는 한 번에 한 블록씩 읽습니다. 이것이 Single Block Read이고, ROWID로 테이블 블록을 찾아가는 랜덤 액세스와 인덱스 범위 스캔이 이 방식입니다. 오라클은 이 읽기를 기다리는 시간을 db file sequential read 대기 이벤트로 기록합니다. 이름에 sequential이 붙었지만 Single Block Read라는 점이 혼동되는 자리입니다.
Multiblock Read
디스크 I/O는 한 번 요청에 드는 고정 비용이 커서, 어차피 이어서 읽을 블록이면 한 번에 여러 개를 가져오는 편이 쌉니다. 이것이 Multiblock Read이고 Full Table Scan과 Index Fast Full Scan이 씁니다. 한 번에 읽는 블록 수는 db_file_multiblock_read_count 파라미터로 정하고, 대기 이벤트는 db file scattered read입니다. 다만 익스텐트 경계를 넘어 모아 읽지는 못합니다. 익스텐트 하나가 한 데이터 파일 안의 연속 블록이기 때문입니다.
| 기준 | Single Block Read | Multiblock Read |
|---|---|---|
| 한 번에 읽는 양 | 1블록 | 여러 블록(익스텐트 안에서) |
| 쓰는 오퍼레이션 | 인덱스 범위 스캔, ROWID 테이블 액세스 | Full Table Scan, Index Fast Full Scan |
| 대기 이벤트 | db file sequential read |
db file scattered read |
같은 블록 반복 읽기
히트율이 99%여도 느린 SQL이 있습니다. 같은 블록을 수만 번 읽으면 그 블록은 늘 캐시에 있어 히트율은 높지만, 논리적 I/O 하나하나가 래치를 잡고 블록을 찾는 CPU 비용이라 쌓이면 무겁습니다. 이런 일이 생기는 자리는 셋입니다.
- 인덱스로 찾은 행마다 테이블 블록을 찾아가는데, 인접한 인덱스 키가 서로 다른 테이블 블록을 가리켜 같은 블록을 여러 번 오가는 경우
- NL 조인에서 바깥쪽 행마다 안쪽 인덱스의 루트·브랜치 블록을 처음부터 다시 읽는 경우
SELECT절의 스칼라 서브쿼리나 사용자 정의 함수가 행마다 다시 실행되는 경우
이런 SQL은 히트율이 아니라 실행 한 번에 읽은 블록 수를 봐야 드러납니다. 히트율이 높다는 것이 SQL이 효율적이라는 뜻이 아닌 이유입니다.
연습 문제
SQL 처리 단계를 차례대로 적은 것은?
① 최적화 → 파싱 → 로우 소스 생성 → 실행
② 파싱 → 로우 소스 생성 → 최적화 → 실행
③ 파싱 → 최적화 → 로우 소스 생성 → 실행
④ 로우 소스 생성 → 파싱 → 최적화 → 실행③. 문법·의미를 검사하는 파싱 뒤에 옵티마이저가 계획을 고르고, 그 계획을 실행 가능한 로우 소스로 바꾼 다음 실행합니다.다음 중 서로 다른 공유 커서를 만드는 쌍이 아닌 것은? (같은 스키마에서 실행)
①SELECT * FROM 주문 WHERE 주문번호 = 7과SELECT * FROM 주문 WHERE 주문번호 = 8
②SELECT * FROM 주문 WHERE 주문번호 = :b1을 값 7과 8로 실행
③SELECT * FROM 주문과select * from 주문
④SELECT * FROM 주문과SELECT * FROM 주문(공백 둘)②. 바인드 변수를 쓰면 텍스트가 같아 커서 하나를 나눠 씁니다. 나머지는 상수·대소문자·공백이 달라 각각 다른 SQL로 봅니다.어떤 SQL의 트레이스에서 query 9,000, current 1,000, disk 1,500블록이 찍혔다. 버퍼 캐시 히트율은?
① 83.3%
② 85%
③ 88.9%
④ 15%②. 논리적 I/O가 블록이므로 %입니다. query만으로 나누면 ①의 83.3%가 나옵니다.논리적 I/O가 40,000블록인 SQL의 히트율이 95%다. 인덱스를 바꿔 논리적 I/O를 8,000블록으로 줄였고 히트율은 90%로 떨어졌다. 물리적 I/O는 어떻게 바뀌었나?
① 2,000에서 800으로 줄었다
② 2,000에서 4,000으로 늘었다
③ 400에서 800으로 늘었다
④ 그대로다①. 바꾸기 전 블록, 바꾼 뒤 블록입니다. 히트율이 떨어져도 논리적 I/O가 크게 줄면 물리적 I/O가 줄어듭니다.Multiblock Read를 쓰는 오퍼레이션은?
① INDEX RANGE SCAN
② TABLE ACCESS BY INDEX ROWID
③ INDEX UNIQUE SCAN
④ TABLE ACCESS FULL④. 인덱스를 따라 내려가거나 ROWID로 찾아가는 읽기는 다음 블록이 앞 블록에서 정해지므로 Single Block Read입니다.db file sequential read대기 이벤트에 대한 설명으로 옳은 것은?
① Full Table Scan에서 여러 블록을 모아 읽을 때 기록된다
② 한 번에 한 블록을 읽을 때 기록된다
③ 버퍼 캐시에서 블록을 찾을 때 기록된다
④ Redo 로그 파일을 쓸 때 기록된다②. 이름과 달리 Single Block Read의 대기입니다. 여러 블록을 모아 읽는 대기는db file scattered read입니다.

