[아이티윌 오라클 DBA 과정 91기] 260126 TIL
2026. 1. 29. 09:21ㆍCourses/아이티윌 오라클 DBA 과정
Database(Data) Buffer Cache(Cont.)
세그먼트 헤더 블록
- 익스텐트 맵
- HWM
물리적 I/O 발생
- access하려는 블록이 데이터 버퍼 캐시에 없을 경우 물리적 I/O 발생
- working set을 관리하는
cache buffers lru chain latch를 획득해야 함 - 이 과정에서 경합이 발생하면
latch: cache buffers lru chain대기 이벤트 발생
SYS@ora19c> select count(*) from v$latch_children where name = 'cache buffers lru chain';
COUNT(*)
----------
36
SYS@ora19c> select a.ksppinm parameter, b.ksppstvl value
from x$ksppi a, x$ksppcv b
where a.indx = b.indx
and a.ksppinm = '_db_block_lru_latches'; 2 3 4
PARAMETER VALUE
-------------------------------------------------------------------------------- ------------------------------
_db_block_lru_latches 18
SYS@ora19c> select a.bp_blksz,
c.child#,
a.bp_name,
c.gets,
c.MISSES,
c.SLEEPS
from x$kcbwbpd a, x$kcbwds b, v$latch_children c
where b.set_id between a.bp_lo_sid and a.bp_hi_sid
and c.addr = b.set_latch
order by 1,2; 2 3 4 5 6 7 8 9 10
BP_BLKSZ CHILD# BP_NAME GETS MISSES SLEEPS
---------- ---------- -------------------- ---------- ---------- ----------
2048 13 DEFAULT 16 0 0
2048 15 DEFAULT 16 0 0
4096 17 DEFAULT 16 0 0
4096 19 DEFAULT 16 0 0
8192 1 KEEP 16 0 0
8192 3 KEEP 16 0 0
8192 5 RECYCLE 16 0 0
8192 7 RECYCLE 16 0 0
8192 9 DEFAULT 90844 11 4 <<- 실제 사용하는 cache buffer lru chain latch
8192 11 DEFAULT 85581 7 0 <<- 실제 사용하는 cache buffer lru chain latch
8192 21 DEFAULT 16 0 0
8192 23 DEFAULT 16 0 0
8192 33 DEFAULT 16 0 0
8192 35 DEFAULT 16 0 0
16384 25 DEFAULT 16 0 0
16384 27 DEFAULT 16 0 0
32768 29 DEFAULT 16 0 0
32768 31 DEFAULT 16 0 0
18 rows selected.- LRU List의 보조 리스트에서 free buffer를 찾음
- 만약에 보조 리스트에 free buffer가 없을 경우 메인 리스트 cold 영역 제일 뒤에서부터 free buffer를 찾음
- 찾는 도중에 touch count가 1 이하인 버퍼가 free buffer로 사용될 수 있음
- 만약에 찾는 도중에 touch count가 2 이상인 버퍼를 만나면 hot 영역 앞으로 옮기고 해당 버퍼 touch count는 0으로 초기화시킴
# touch count가 2 이상인 buffer를 만나면 hot region 으로 옮겨지는 기준
SYS@ora19c> select a.ksppinm parameter, b.ksppstvl value
from x$ksppi a, x$ksppcv b
where a.indx = b.indx
and a.ksppinm = '_db_aging_hot_criteria'; 2 3 4
PARAMETER VALUE
-------------------------------------------------------------------------------- ------------------------------
_db_aging_hot_criteria 2- LRU List에서 free buffer를 40% scan 하고도 free buffer를 찾지 못하면 DBWR에게 dirty buffer 들을 데이터 파일에 기록하고 free buffer를 확보해 줄 것을 요청
SYS@ora19c> select a.ksppinm parameter, b.ksppstvl value
from x$ksppi a, x$ksppcv b
where a.indx = b.indx
and a.ksppinm = '_db_block_max_scan_pct'; 2 3 4
PARAMETER VALUE
-------------------------------------------------------------------------------- ------------------------------
_db_block_max_scan_pct 40- free buffer를 찾게 되면 해당 버퍼에 대해 buffer lock을 exclusive 모드로 획득하고 데이터 파일에서 블록을 해당 위치로 읽어 들임
- logical I/O 수 안에 physical I/O도 포함
- touch count는 3초 후에 다시 access 하게 되면 1씩 증가
SYS@ora19c> select a.ksppinm parameter, b.ksppstvl value
from x$ksppi a, x$ksppcv b
where a.indx = b.indx
and a.ksppinm = '_db_aging_touch_time'; 2 3 4
PARAMETER VALUE
-------------------------------------------------------------------------------- ------------------------------
_db_aging_touch_time 3working set
- 데이터 버퍼 캐시 관리
- LRU(Least Recently Used) List
- 가장 최근에 사용되거나 사용하지 않은 버퍼들의 리스트
- free buffer, 사용 중이거나 사용된 버퍼, 아직 LRUW 리스트로 옮겨지지 않은 dirty block
- 메인 리스트 : 사용된 버퍼들의 리스트
- 보조 리스트 : free buffer list, 미사용된 buffer, dbwr에 의해 기록된 버퍼들의 리스트
- LRUW(Least Recently Used Write) List, Dirty List
- 아직 데이터 파일로 기록되지 않은 변경된 버퍼(dirty buffer)들을 관리하는 리스트
- 메인 리스트 : 변경된 buffer들의 리스트
- 보조 리스트 : 현재 dbwr에 의해 기록 중인 버퍼들의 리스트(partial checkpoint 시 이 부분만 봐도 됨)
- LRU 리스트에서 free buffer를 더 이상 찾을 수 없는 경우 체크 포인트를 발생시켜 dirty buffer를 디스크에 작성해야 함
- dirty block들을 빠르게 찾기 위해 LRUW list 탐색
실습
Buffer Cache flush
alter system flush buffer_cache;buffer cache에 올라와 있는 버퍼 정보 조회
# HR 유저가 소유한 Object 블록 중 버퍼 캐시에 올라가 있는 블록 조회
select o.object_name, x.obj, x.file#, x.dbablk, x.tch, x.state
from x$bh x, dba_objects o
where o.data_object_id = x.obj
and o.owner = 'HR';
- touch count 정보는 x$bh의 TCH 컬럼을 통해 조회 가능
- state = 1은 v$bh에서 status = xcur과 동일 : 사용 중인 블록
select o.object_name, x.objd, x.file#, x.block#, x.status
from v$bh x, dba_objects o
where o.data_object_id = x.objd
and o.owner = 'HR';
select * from dba_segments where segment_name = 'EMP_EMP_ID_PK';
- root 블록은 세그먼트 헤더 블록의 다음 번호를 가짐
물리적 I/O 발생
테이블 생성
drop table hr.emp purge;
create table hr.emp(id number, name varchar2(1000)) tablespace users;
insert into hr.emp(id, name)
select level, 'oracle'||level
from dual
connect by level < = 1000000
order by dbms_random.value;
commit;
SYS@ora19c> select blocks, bytes/1024/1024 mb from dba_segments where owner = 'HR' and segment_name = 'EMP';
BLOCKS MB
---------- ----------
3328 26인덱스 생성
create index hr.emp_idx on hr.emp(id) tablespace users;
SYS@ora19c> select blocks, bytes/1024/1024 mb from dba_segments where owner = 'HR' and segment_name = 'EMP_IDX';
BLOCKS MB
---------- ----------
2304 18 세션 1, 2에서 아래 익명 블록 수행 : 인덱스 스캔
begin
for i in (select /*+ index(e emp_idx) */* from hr.emp e where id >= 0) loop
null;
end loop;
end;
/SYS@ora19c> select sid, event, total_waits, time_waited
from v$session_event
where sid in (select sid from v$session where username = 'HR'); 2 3
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ---------------------------------------------------------------- ----------- -----------
38 log file sync 1 0
38 SQL*Net message to client 8 0
38 SQL*Net message from client 7 1
38 events in waitclass Other 8 0
160 log file sync 1 1
160 SQL*Net message to client 8 0
160 SQL*Net message from client 7 0
160 events in waitclass Other 8 0
8 rows selected.
SYS@ora19c> /
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ---------------------------------------------------------------- ----------- -----------
38 log file sync 1 0
38 SQL*Net message to client 9 0
38 SQL*Net message from client 8 4552
38 events in waitclass Other 9 0
160 Disk file operations I/O 1 0
160 log file sync 1 1
160 db file sequential read 2227 7
160 db file parallel read 95 4
160 SQL*Net message to client 9 0
160 SQL*Net message from client 8 4886
160 events in waitclass Other 18 0
11 rows selected.세션 1, 2에서 아래 익명 블록 수행 : 풀 테이블 스캔
begin
for i in (select /*+ full(e) */* from hr.emp e where id >= 0) loop
null;
end loop;
end;
/SYS@ora19c> select sid, event, total_waits, time_waited
from v$session_event
where sid in (select sid from v$session where username = 'HR'); 2 3
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ---------------------------------------------------------------- ----------- -----------
162 Disk file operations I/O 1 0
162 log file sync 1 1
162 db file sequential read 1 0
162 SQL*Net message to client 8 0
162 SQL*Net message from client 7 1
162 events in waitclass Other 9 0
160 Disk file operations I/O 2 0
160 log file sync 1 3
160 db file sequential read 9 0
160 SQL*Net message to client 8 0
160 SQL*Net message from client 7 1
160 events in waitclass Other 9 0
12 rows selected.
SYS@ora19c> /
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ---------------------------------------------------------------- ----------- -----------
162 Disk file operations I/O 2 0
162 log file sync 1 1
162 db file sequential read 1 0
162 direct path read 107 1
162 SQL*Net message to client 9 0
162 SQL*Net message from client 8 1855
162 events in waitclass Other 13 0
160 Disk file operations I/O 3 0
160 log file sync 1 3
160 db file sequential read 13 0
160 db file scattered read 1 0
160 direct path read 107 1
160 SQL*Net message to client 9 0
160 SQL*Net message from client 8 2098
160 events in waitclass Other 16 0
15 rows selected.- direct path read 방식 사용
세션 1, 2에서 아래 익명 블록 수행 : direct path read X
# direct path read를 사용하지 않도록 event 설정
alter session set events '10949 trace name context forever, level 1';
begin
for i in (select /*+ full(e) */* from hr.emp e where id >= 0) loop
null;
end loop;
end;
/SYS@ora19c> select sid, event, total_waits, time_waited
from v$session_event
where sid in (select sid from v$session where username = 'HR'); 2 3
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ---------------------------------------------------------------- ----------- -----------
162 Disk file operations I/O 1 0
162 log file sync 1 1
162 db file sequential read 3 0
162 SQL*Net message to client 9 0
162 SQL*Net message from client 8 5920
162 events in waitclass Other 11 0
160 Disk file operations I/O 3 0
160 log file sync 1 1
160 SQL*Net message to client 9 0
160 SQL*Net message from client 8 4866
160 events in waitclass Other 10 0
11 rows selected.
SYS@ora19c> /
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ---------------------------------------------------------------- ----------- -----------
162 Disk file operations I/O 2 0
162 log file sync 1 1
162 db file sequential read 6 0
162 db file scattered read 91 3
162 SQL*Net message to client 10 0
162 SQL*Net message from client 9 10196
162 events in waitclass Other 11 0
160 Disk file operations I/O 3 0
160 log file sync 1 1
160 SQL*Net message to client 10 0
160 SQL*Net message from client 9 9126
160 events in waitclass Other 10 0
12 rows selected.Single Block I/O 수행 시 발생
- rowid scan 발생(root → branch → leaf → rowid 를 이용해서 테이블 스캔)
- 데이터 파일 헤더(file header)
- row chaining, row migration
db file sequential read: 데이터 파일로부터 하나의 블록을 메모리로 읽는 작업(물리적 I/O)db file parallel read- prefetch 기능이 수행될 경우
- Single Block I/O 수행 시 디스크에서 읽어야 하는 경우가 생기면 바로 디스크로 가지 않고 디스크에서 읽어야 하는 블록들을 모아 놨다가 한 번에 읽어옴
Multi Block I/O 수행 시 발생
- table full scan, index fast full scan
db file scattered read: 데이터 파일로부터 여러 개의 블록을 메모리로 읽는 작업(물리적 I/O)- 낮 시간, OLTP 시스템에서 이 이벤트가 발생하면 확인 및 조치 필요
- 밤 시간, OLAP 시스템에서는 이 이벤트가 발생하는 것이 당연
direct path read- Multi Block I/O 발생 시 데이터 버퍼 캐시에 올리지 않고 서버 프로세스에 있는 CURSOR에 직접 읽어오는 방식
Transaction 처리 순서
SYS@ora19c> select s.username, t.xidusn, t.ubafil, t.ubablk, t.used_ublk
from v$session s, v$transaction t
where s.saddr = t.ses_addr; 2 3
no rows selected
# 트랜잭션 시작
HR@ora19c> update hr.employees set salary = salary * 1.1 where employee_id = 200;
1 row updated.
SYS@ora19c> /
USERNAME XIDUSN UBAFIL UBABLK USED_UBLK
------------------------------ ---------- ---------- ---------- ----------
HR 2 4 1412 1SYS@ora19c> col object_name format a30
SYS@ora19c> select o.object_name, x.obj, x.file#, x.dbablk, x.tch, x.state
from x$bh x, dba_objects o
where o.data_object_id = x.obj
and o.owner = 'HR'
and o.object_name = 'EMPLOYEES'; 2 3 4 5
OBJECT_NAME OBJ FILE# DBABLK TCH STATE
------------------------------ ---------- ---------- ---------- ---------- ----------
EMPLOYEES 72977 3 31277 0 0
EMPLOYEES 72977 3 31274 0 0
EMPLOYEES 72977 3 31279 1 1
EMPLOYEES 72977 3 31279 0 0Undo 정보 조회
SYS@ora19c> select n.usn, n.name, s.extents, s.rssize, s.xacts, s.status
from v$rollname n, v$rollstat s
where n.usn = s.usn; 2 3
USN NAME EXTENTS RSSIZE XACTS STATUS
---------- -------------------------------------------------- ---------- ---------- ---------- ---------------
0 SYSTEM 7 450560 0 ONLINE
1 _SYSSMU1_1261223759$ 4 2220032 0 ONLINE
2 _SYSSMU2_27624015$ 4 2220032 1 ONLINE
3 _SYSSMU3_2421748942$ 8 516096 0 ONLINE
4 _SYSSMU4_625702278$ 5 3268608 0 ONLINE
5 _SYSSMU5_2101348960$ 6 3334144 0 ONLINE
6 _SYSSMU6_813816332$ 4 2220032 0 ONLINE
7 _SYSSMU7_2329891355$ 5 3268608 0 ONLINE
8 _SYSSMU8_399776867$ 4 2220032 0 ONLINE
9 _SYSSMU9_1692468413$ 5 3268608 0 ONLINE
10 _SYSSMU10_930580995$ 5 3268608 0 ONLINE
11 rows selected.DML 처리 단계
- parse
- bind
- execute
- library cache lock과 library cache pin 을 share mode로 변경
- 트랜잭션 시작
트랜잭션 처리 순서
1. undo segment 할당
- 현재 온라인 상태인 undo 세그먼트 중 하나를 사용(랜덤)
- 다른 트랜잭션이 사용 중이라면 오라클은 3번까지 재시도
- 이 과정에서 실패하게 되면 오프라인 상태의 undo 세그먼트를 온라인으로 사용
- 이 과정에서도 실패하게 되면 새로운 undo 세그먼트(10개 기본)를 생성해서 사용(undo 테이블스페이스에 속한 데이터 파일 여유 공간이 있을 경우)
- 이 과정에서도 실패하게 되면 사용 중인 undo 세그먼트 중 가장 사용량이 작은 undo 세그먼트를 같이 사용
- undo segment를 할당 받기까지
enq: US - contention대기 이벤트 발생
2. undo segment header block에 transaction table slot 생성
- 트랜잭션 정보를 저장하는 곳, txid(transaction id)를 생성
- txid = (xidusn, xidslot, xidsqn)
SYS@ora19c> select t.xidusn, xidslot, xidsqn
from v$session s, v$transaction t
where s.saddr = t.ses_addr; 2 3
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
2 5 24913. 트랜잭션 대상이 되는 블록을 데이터 버퍼 캐시에서 실행 계획을 통해 찾음
cache buffers chains latch- 인덱스 블록은 shared mode로 획득
- 대상 데이터 블록은 exclusive mode로 획득
- block(buffer) header에 block lock(shared(select), exclusive(dml))을 획득한 후 cache buffers chains latch를 해제
- buffer header에 block lock 경합이 발생하면
buffer busy waits대기 이벤트 발생
- buffer header에 block lock 경합이 발생하면
- block header에 있는 transaction layer에 transaction slot을 획득해야 함(ITL(Interested Transaction List)에 transaction entry 등록)
- 만약에 ITL entry(transaction slot)을 획득할 수 없으면
enq : TX - allocate ITL entry대기 이벤트가 발생 - 트랜잭션 대상 행에 대해서 lock 생성
- 만약 다른 세션에서 같은 행을 lock 걸고 있으면 기다려야 함
- 이 때 발생하는 대기 이벤트는
enq : TX - row lock contention
4. 변경 정보를 PGA 영역에 change vector 생성
- undo segment header 정보(change vector #1)
- undo block (change vector #2)
- transaction 대상이 되는 블록(change vector #3)
- PGA 영역 안에 있는 change vector 를 redo entry라는 이름으로 redo log buffer로 복사
- redo copy latch, redo allocation latch, redo writing latch를 획득해야 함
- 이 과정에서 latch 경합이 발생하면
latch: redo copylatch: redo allocationlatch: redo writing
5. 이전 값은 undo block에 작성하고 새로운 값으로 변경
- 변경된 블록은 dirty 상태가 됨
- 블록 header에 있는 buffer lock(exclusive)은 해지
'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글
| [아이티윌 오라클 DBA 과정 91기] 260128 TIL (0) | 2026.01.29 |
|---|---|
| [아이티윌 오라클 DBA 과정 91기] 260127 TIL (0) | 2026.01.29 |
| [아이티윌 오라클 DBA 과정 91기] 260122 TIL (0) | 2026.01.29 |
| [아이티윌 오라클 DBA 과정 91기] 260121 TIL (1) | 2026.01.21 |
| [아이티윌 오라클 DBA 과정 91기] 260120 TIL (1) | 2026.01.20 |