논리적 I/O와 물리적 I/O

#핵심 요약

논리적 I/O는 SQL이 버퍼 캐시에 요청한 블록 수이고, 물리적 I/O는 그 가운데 캐시에 없어 디스크까지 간 읽기임
물리적 I/O는 논리적 I/O 안에 포함됨

트레이스에서 논리적 I/O = db block gets(Current) + consistent gets(Query), 물리적 I/O = physical reads(Disk)임

버퍼 캐시 히트율 BCHR = (1 − Disk / (Query + Current)) × 100
실습에서는 95% 이상을 권장 기준으로 적었음

대용량 Full Table Scan, 병렬 쿼리, PGA가 부족한 정렬, 해시 조인은 버퍼 캐시를 거치지 않고 읽음(Direct Path Read)

#개념과 동작 원리

#논리적 I/O와 물리적 I/O의 관계

실습에서는 emp 조회의 autotrace 통계에서 다음 값을 읽었음

논리 I/O  = 17 + 52 = 69
물리 I/O  = 2          (69 안에 포함)
Cache Hit = 69 - 2 = 67
BCHR      = (1 - 2 / 69) x 100 = 97.1 %

해석: emp는 블록 1개짜리 작은 테이블이라 첫 조회 때 세그먼트 헤더와 데이터 블록 2개만 디스크에서 읽었고, 다시 실행하면 physical reads가 0, BCHR이 100%가 됨
실습2의 SQL 트레이스에서는 Disk 0, Query 7, Current 0으로 BCHR 100%였음

설명: Fetch Count가 2인 이유는 14행을 arraysize(표기 기본 15) 단위로 가져와 한 번에 14행을 받고, 마지막에 끝(EOF)을 확인하는 fetch가 한 번 더 있기 때문임
SQL*Plus의 ARRAYSIZE 기본값은 15이며 세션에서 바꿀 수 있음
실습 트레이스의 집계는 Fetch Count 2를 표시하지만 각 Fetch의 내부 발생 순서는 확인 불가

#블록 이동과 LRU 목록

실습의 SQL 처리 흐름은 유저 프로세스 → 서버 프로세스 → parsing → execute → fetch 순서임
parsing은 문법 검사와 의미 분석으로 나뉘며 데이터 파일의 블록을 DB buffer cache로 읽어 오는 흐름을 설명함
블록 크기 표기는 8kb임

실습의 논리 구조는 DB → Tablespace → Segment → extent → block 순서임

버퍼 히트율 = (버퍼 캐시에서 곧바로 찾을 수 있는 블록 수 / 총 읽은 블록 수) × 100
= ((논리적 I/O − 물리적 I/O) / 논리적 I/O) × 100
= (1 − (물리적 I/O / 논리적 I/O)) × 100

#버퍼 캐시를 거치지 않는 읽기

실습에서는 "Direct Path Read를 제외하면 모든 블록은 버퍼 캐시를 거쳐 읽는다"고 정리하고, 버퍼 캐시를 거치는 쪽이 오히려 비효율적인 경우로 다음 셋을 들었음

1. Full Table Scan 중 테이블 크기가 버퍼 캐시의 일정 비율을 넘을 때
2. parallel 힌트로 병렬 쿼리를 실행할 때
3. 정렬이나 해시 조인 중 PGA가 부족할 때

세 조건을 일괄 판정할 고정 비율은 확인 불가
Full Table Scan의 캐시 처리에는 테이블 크기와 읽기 방식 등 여러 조건이 관여함(Full Table Scan과 인덱스 접근)

#버퍼 캐시가 블록을 찾는 방법

실습에서는 블록 번호를 해시 함수(예: mod(x, 5))에 넣어 해시 체인(버킷)을 정하는 예로 세 가지 특징을 설명했음

퀴즈 답: 체인을 탐색하려면 캐시 버퍼 체인 래치를 얻고, 블록을 찾은 뒤 래치를 풀기 전에 버퍼 헤더에 버퍼 Lock을 걸어 다른 프로세스가 같은 블록을 동시에 바꾸지 못하게 함
래치 경합은 버퍼와 Shared Pool 경합에서 다룸

#주요 용어와 구성 요소

#필수 명령어 또는 SQL

#EMP 조회의 autotrace 통계

실습의 기동 응답은 인스턴스 시작, 데이터베이스 마운트, 데이터베이스 열림 순서임

첫 조회의 실습 기록

14 행이 선택되었습니다.

Statistics
----------------------------------------------------------
        43  recursive calls
        17  db block gets
        52  consistent gets
         2  physical reads
      3096  redo size
      1027  bytes sent via SQL*Net to client
       394  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         2  sorts (memory)
         0  sorts (disk)
        14  rows processed

결과 해석: db block gets 17, consistent gets 52, physical reads 2이며 처리 행 수는 14임

재실행의 실습 기록

14 행이 선택되었습니다.

Statistics
----------------------------------------------------------
         0  recursive calls
         0  db block gets
         7  consistent gets
         0  physical reads
         0  redo size
      1027  bytes sent via SQL*Net to client
       394  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
        14  rows processed

결과 해석: 재실행에서는 consistent gets 7, physical reads 0이며 처리 행 수는 14임

#EMP 조회의 SQL 트레이스

트레이스 방법을 선택하기 전의 응답

Select Trace Method first.
Menu : Option -> Trace Method

결과 해석: Trace Method를 먼저 선택하라는 응답임

Option → Trace Method에 Local File과 FTP가 표시됨
실습의 FTP Login Information에서는 SFTP를 선택했음

EMP 조회의 실습 기록

Call      Count  CPU Time  Elapsed Time  Disk  Query  Current  Rows
Parse         1     0.000         0.000     0      0        0     0
Execute       1     0.000         0.000     0      0        0     0
Fetch         2     0.000         0.000     0      7        0    14
Total         4     0.000         0.000     0      7        0    14

결과 해석: Total의 Disk 0, Query 7, Current 0, Rows 14이며 Fetch Count는 2임

#문제: 조인 SQL의 BCHR

SELECT e.ename, d.loc
  FROM emp e, dept d
 WHERE e.deptno = d.deptno
   AND e.job = 'SALESMAN';

결과 해석:

EMP, DEPT 조인 SQL의 실제 통계와 BCHR 답은 확인 불가
실습 답 식에는 BCHR = ((논리 I/O − 물리 I/O) / 논리 I/O) × 100 = (41998 − 37001) / 41998 × 100이 적혀 있으나 이 수치의 측정 대상은 아래의 seoul_2025 전체 조회임
해당 식의 수치는 EMP, DEPT 조인이 아닌 다른 SQL의 측정값임
계산 결과는 기재 없음

#seoul_2025 전체 조회의 SQL 트레이스

Call      Count  CPU Time  Elapsed Time   Disk  Query  Current    Rows
Parse         1     0.000         0.000      0      0        0       0
Execute       1     0.000         0.000      0      0        0       0
Fetch      5376     2.146         3.770  37001  41998        0  537489
Total      5378     2.146         3.770  37001  41998        0  537489

Misses in library cache during parse : 0
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=107)

결과 해석: Total의 Query는 41998, Disk는 37001, Rows는 537489임
Fetch Count는 5376, CPU Time은 2.146, Elapsed Time은 3.770으로 표시됨
Row Source 연산의 상세 출력은 확인 불가

#구문과 옵션 설명

캐시 적중률을 계산할 때는 같은 관측 구간의 통계 증분을 사용함
기준식은 1 − Δphysical reads cache / (Δconsistent gets from cache + Δdb block gets from cache)이며 백분율은 100을 곱함
분모가 0이면 비율을 계산하지 않음

SQL*Plus의 ARRAYSIZE는 한 번에 가져오는 행 수이며 유효 범위는 1~5000임
블록 캐시 크기나 결과 행 수를 바꾸는 옵션은 아님

#사용 조건과 주의사항

BCHR이 높다고 SQL이 효율적이라는 뜻은 아님
실습의 버퍼 비교 실습(실행계획 읽기)처럼 튜닝은 읽은 버퍼 수 자체를 줄이는 것이 목적임
BCHR 95%는 실습의 기준이며 모든 업무에 공통된 성능 통과 기준으로 사용하지 않음

기존 실습 계산은 그 실습의 단순화된 캐시 읽기 모델임
Direct Path를 포함하는 전체 physical reads를 항상 논리적 캐시 요청의 부분집합으로 볼 수는 없음
물리 읽기도 OS 파일 캐시에서 처리될 수 있어 물리 디스크 장치 접근 횟수와 같지 않음

병렬 스캔이 항상 캐시를 우회하는 것은 아니며 캐시를 사용하는 병렬 읽기도 가능함
캐시 적중률 하나 또는 95%라는 고정 기준만으로 SQL 효율이나 메모리 부족을 판정하지 않음