| # 요약 | |
| ■ Data Buffer Cache ■ Buffer Busy Wait ■ 물리적 I/O 발생 # LRU(Least Recently Used) LIST # LRUW(Least Recently Used Write) LIST _db_block_max_scan_pct _db_aging_hot_criteria |
|
■ Data Buffer Cache
- database에서 읽어 들인 block을 메모리에 올려 놓은 영역
- 물리적인 I/O를 최소화하기 위한 메모리 영역
1. parse
2. bind
3. Execute
- library cache lock 과 library cache pin shared 모드로 변환하고 SQL문을 실행한다.
- 실행계획을 통해서 요청한 블록을 DBA(Data Block Address)와 Block Class(블록 종류)에 대해 해시함수를 이용해서
해시값을 생성하고 해시값에 해당하는 hash bucket을 찾는다.
현재 date buffer cache에 올라온 블록의 정보를 shared pool에서 관리한다.
해시값에 해당하는 hash bucket을 검색하기 위해서는 latch를 획득해야 한다. 그런데 이미 누군가가 latch를 획득하고 있으면 나는 기다려야 한다. 그 때 발생한 이벤트가 latch : cache buffers chains
| select count(*) from v$latch_children where name = 'cache buffers chains'; COUNT(*) ---------- 2048 > latch가 2048개 있다 # 현재 latch 수 SELECT a.ksppinm Parameter, b.ksppstvl Session_Value, c.ksppstvl Instance_Value FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm = '_db_block_hash_latches'; PARAMETER SESSION_VALUE INSTANCE_VALUE ------------------------------ -------------------- -------------------- _db_block_hash_latches 2048 2048 # 현재 bucket 수 SELECT a.ksppinm Parameter, b.ksppstvl Session_Value, c.ksppstvl Instance_Value FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm = '_db_block_hash_buckets'; PARAMETER SESSION_VALUE INSTANCE_VALUE ------------------------------ -------------------- -------------------- _db_block_hash_buckets 65536 65536 >>> 하나의 latch가 담당하는 bucket의 수는 65536 / 2048 = 32 |
hash bucket을 보호하는 cache buffers chains latch를 획득 해야한다.
읽기 작업 : shared mode
쓰기 작업 : exclusive mode
이 과정에서 경합이 발생하면 latch : cache buffers chains 발생한다.
latch를 잡고 hash bucket 블록에 해당하는 버퍼 헤더가 존재하면(DBA + CLASS) 해당 블록이 버퍼 캐시에 올라와 있는 상태면 논리적인 I/O 발생.
■ Buffer Busy Wait
같은 블록 안에 있는 서로 다른 행을 조회하는데(shared) 다른 유저가 변경하는(exclusive) 경우 동시에 이루어져서는 안된다.
같은 블록 안에 있는 서로 다른 행을 변경하려고 하는데(exclusive) 다른 유저가 변경하는(exclusive하는) 경우 동시에 이루어져서는 안된다.
이런 상황에 buffer busy wait이 발생한다.
각 사용자는 행을 변경하기 위해서 tx lock(row level lock)을 exclusive하게 획득했다고 하더라도 현재 자신만 블록안에
있는 행을 변경해야하는 것을(exclusive) 보장 받아야 한다.
이때 블록 헤더에 exclusive lock을 설정해야 한다. 이 lock이 block lock 개념이다.
1. 변경하고자하는 행에 해당하는 블록이 data buffer cache에 있는지 실행계획을 통해서 찾아간다.
latch 못잡으면 wait event -> latch : cache buffers chains
2. block을 찾아서 block header에 block lock(shared(select),exclusive(dml))을 획득하게 되면 latch는 해제한다.
3-1. select를 수행했을 경우 active set 결과를 만들면 block lock 해제,
3-2. tx lock을 획득하고 DML 작업이 끝나면(≠tranction) block lock 해제
tx lock을 획득 못하면 대기(enq : TX - row lock contention) 하게되고 block lock 해제된다.
waiting 단계에서 해제되면 다시 block header에 exclusive를 획득하고 작업을 수행한다.
| << 예시 1 >> | |
| HR1> update hr.employees set salary = 1000 where employee_id = 100; 1 row updated. HR2> update hr.employees set salary = 2000 where employee_id = 100; > wait... SYS> select sid, blocking_session, event from v$session where username = 'HR'; SID BLOCKING_SESSION EVENT ---------- ---------------- ---------------------------------------------------------------- 23 SQL*Net message from client 32 23 enq: TX - row lock contention HR1> select last_name,salary from hr.employees where employee_id = 100; LAST_NAME SALARY ------------------------- ---------- King 1000 HR2> select * from hr.employees where employee_id = 100; > wait... HR1> rollback; HR2> 1 row updated. LAST_NAME SALARY ------------------------- ---------- King 2000 SYS> select sid, blocking_session, event from v$session where username = 'HR'; SID BLOCKING_SESSION EVENT ---------- ---------------- ---------------------------------------------------------------- 23 SQL*Net message from client 32 SQL*Net message from client |
|
■ 물리적 I/O 발생
latch를 잡고 hash bucket에 블록에 해당하는 버퍼 헤더가 있는지를 조회해보고 없으면 물리적 I/O 발생
# LRU(Least Recently Used) LIST
- 가장 최근에 사용되거나 사용하지 않은 버퍼들의 리스트
- free buffer, 사용중이거나 사용된 버퍼, 아직 LRUW리스트로 옮겨지지 않은 dirty buffer(변경된 버퍼)
- 메인 리스트 : 사용된 버퍼들의 리스트, hot region(자주 사용된 buffer), cold region(사용빈도가 낮은 buffer) 으로
구분해서 관리
- 보조 리스트 : free buffer list, 미사용된 buffer, DBRW에 기록된 버퍼들의 리스트
# LRUW(Least Recently Used Write) LIST
- 아직 디스크로 기록되지 않은 변경된 buffer(dirty buffer)들을 관리하는 리스트
- Dirty List, Write List 라고도 부른다.
- 메인 리스트 : 변경된 buffer들의 리스트
- 보조 리스트 : 현재 DBWR에 의해 기록중인 버퍼들의 리스트
물리적(physical)인 I/O가 발생하면 latch를 잡고 free buffer를 찾는다.
이때 latch를 못잡으면 wait event 발생 -> latch : cache buffers LRU chain
| select count(*) from v$latch_children where name = 'cache buffers lru chain'; COUNT(*) ---------- 32 ↓ 사용중 2개 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 2; BP_BLKSZ CHILD# BP_NAME GETS MISSES SLEEPS ---------- ---------- -------------------- ---------- ---------- ---------- 8192 1 KEEP 14 0 0 8192 3 KEEP 14 0 0 8192 5 RECYCLE 14 0 0 8192 7 RECYCLE 14 0 0 8192 9 DEFAULT 104829 15 1 8192 11 DEFAULT 107673 5 0 2048 13 DEFAULT 14 0 0 2048 15 DEFAULT 14 0 0 4096 17 DEFAULT 14 0 0 4096 19 DEFAULT 14 0 0 8192 21 DEFAULT 14 0 0 8192 23 DEFAULT 14 0 0 16384 25 DEFAULT 14 0 0 16384 27 DEFAULT 14 0 0 32768 29 DEFAULT 14 0 0 32768 31 DEFAULT 14 0 0 |
# 물리적(physical)인 I/O가 발생하면
1) LRU 리스트의 보조 리스트에서 free buffer를 찾는다.
2) 만약 보조 리스트의 버퍼가 모두 사용된 경우에는 메인 리스트의 cold region 제일 뒤에서부터 free buffer를 찾는다.
찾는 도중에 touch count 1 이하인 버퍼를 free buffer로 사용한다.
만약에 touch count 2 이상인 buffer를 만나면 hot region 앞으로(head)로 옮기고 해당 버퍼의 touch count 0으로
초기화 시킨다. (0으로 초기화 : 사용안하면 밀려나가도록)
프리 버퍼를 찾는 도중 dirty buffer가 발견되면 LRUW 리스트로 이동한다.
3) 프리 버퍼를 찾게 되면 해당 버퍼에 대해 buffer lock을 exclusive mode로 획득하고 데이터파일의 블록을 해당 버퍼로
읽어 들인다.
물리적으로 읽어 들이고 있는 중에 다른 세션에서 같은 블록을 조회하려고 하는 경우 기다리는 작업이 발생한다.
read by other session wait event 발생
4) 만약에 LRU 리스트에서 free buffer를 40% scan을 하고도 프리버퍼를 찾지 못하면 서버프로세스는 DBWR에게
dirty buffer를 파일에 기록하고 free buffer를 확보할 것을 요청한다.
DBWR free buffer가 확보 될때까지 free buffer wait event가 발생한다.
| # LRU 리스트에서 free buffer 스캔 기준 : 40% SELECT a.ksppinm Parameter, b.ksppstvl Session_Value, c.ksppstvl Instance_Value FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm = '_db_block_max_scan_pct'; PARAMETER SESSION_VALUE INSTANCE_VALUE ------------------------------ -------------------- -------------------- _db_block_max_scan_pct 40 40 # buffer를 만나면 hot region 앞으로(head)로 옮기는 touch count 기준 : 2 이상 SELECT a.ksppinm Parameter, b.ksppstvl Session_Value, c.ksppstvl Instance_Value FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm = '_db_aging_hot_criteria'; PARAMETER SESSION_VALUE INSTANCE_VALUE ------------------------------ -------------------- -------------------- _db_aging_hot_criteria 2 2 |
| << 예시 2 >> | |
| HR1> create table cbc_latch(id number, name char(100)); insert into cbc_latch(id,name) select level, 'oracle'||level from dual connect by level <= 500000 order by dbms_random.value; commit; create index cbc_latch_idx on cbc_latch(id); select index_name, uniqueness from user_indexes where index_name = 'CBC_LATCH_IDX'; INDEX_NAME UNIQUENES ------------------------------ --------- CBC_LATCH_IDX NONUNIQUE SYS> alter system flush shared_pool; alter system flush buffer_cache; HR1> exec dbms_application_info.set_client_info('sess_1') HR2> exec dbms_application_info.set_client_info('sess_2') SYS> select client_info, sid from v$session where client_info in('sess_1','sess_2'); CLIENT_INFO SID -------------------- ------- sess_2 19 sess_1 32 HR1> + HR2> 동시 수행 begin for i in (select /*+ index(c cbc_latch_idx) */ * from cbc_latch c where id >= 0) loop null; end loop; end; / - + : 힌트 - 테이블의 인덱스 범위스캔해줘 SYS> select sid, event, total_waits, time_waited from v$session_event where sid in (19,32); SID EVENT TOTAL_WAITS TIME_WAITED ------- ----------------------------------- ----------- ----------- 19 Disk file operations I/O 1 0 19 latch: cache buffers chains 5 0 19 read by other session 1503 119 19 log file sync 1 0 19 db file sequential read 4787 109 19 SQL*Net message to client 16 0 19 SQL*Net message from client 15 1318891 19 SQL*Net break/reset to client 2 0 32 Disk file operations I/O 59 7 32 Data file init write 35 1 32 control file sequential read 407 0 32 control file parallel write 77 342 32 latch: cache buffers chains 6 0 32 buffer busy waits 11 569 32 read by other session 1725 120 32 log buffer space 46 183 32 log file switch completion 4 24 32 log file sync 5 24 32 db file sequential read 13655 315 32 db file scattered read 32 6 32 db file single write 11 3 32 db file parallel read 20 0 32 direct path read 288 1 32 direct path write 288 179 32 flashback log file sync 13 24 32 enq: TX - row lock contention 1 33824 32 SQL*Net message to client 25 0 32 SQL*Net message from client 24 1894711 32 SQL*Net break/reset to client 1 0 32 events in waitclass Other 146 52 - db file sequential read : 싱글 블럭 io 발생할 때 wait event (인덱스 스캔 시) - db file scattered read : 멀티io 발생 시 (테이블 풀스캔, 인덱스 풀스캔 시) - db file parallel read : 멀티io 발생 시 select sid, prev_sql_id from v$session where sid in (19,32); SID PREV_SQL_ID ------- ------------- 19 7k4m665w2vvkh 32 7k4m665w2vvkh select * from table(dbms_xplan.display_cursor('7k4m665w2vvkh')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID 7k4m665w2vvkh, child number 0 begin for i in (select /*+ index(c cbc_latch_idx) */ * from cbc_latch c where id >= 0) loop null; end loop; end; NOTE: cannot fetch plan for SQL_ID: 7k4m665w2vvkh, CHILD_NUMBER: 0 Please verify value of SQL_ID and CHILD_NUMBER; It could also be that the plan is no longer in cursor cache (check v$sql_plan) |
|
| << 예시 3 >> | |
| SYS> alter system flush shared_pool; alter system flush buffer_cache; HR1> exec dbms_application_info.set_client_info('sess_1') HR2> exec dbms_application_info.set_client_info('sess_2') SYS> select client_info, sid from v$session where client_info in('sess_1','sess_2'); CLIENT_INFO SID -------------------- ------- sess_2 19 sess_1 32 HR1> + HR2> 동시 수행 begin for i in (select * from cbc_latch where id >= 0) loop null; end loop; end; / SYS> select sid, event, total_waits, time_waited from v$session_event where sid in (19,32); SID EVENT TOTAL_WAITS TIME_WAITED ------- ----------------------------------- ----------- ----------- 19 Disk file operations I/O 1 0 19 log file sync 1 0 19 direct path read 130 1 19 SQL*Net message to client 14 0 19 SQL*Net message from client 13 2980 32 Disk file operations I/O 3 0 32 db file sequential read 143 23 32 db file scattered read 22 0 32 direct path read 130 1 32 SQL*Net message to client 14 0 32 SQL*Net message from client 13 3720 select sid, prev_sql_id from v$session where sid in (19,32); SID PREV_SQL_ID ------- ------------- 19 384bptr7pujx1 32 384bptr7pujx1 select * from table(dbms_xplan.display_cursor('384bptr7pujx1')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID 384bptr7pujx1, child number 0 begin for i in (select * from cbc_latch where id >= 0) loop null; end loop; end; NOTE: cannot fetch plan for SQL_ID: 384bptr7pujx1, CHILD_NUMBER: 0 Please verify value of SQL_ID and CHILD_NUMBER; It could also be that the plan is no longer in cursor cache (check v$sql_plan) |
|
| << 예시 4 >> FLM(FreeList Management) 방식 | |
| SYS> alter system flush shared_pool; alter system flush shared_pool; alter system flush buffer_cache; alter system flush buffer_cache; create tablespace flm_tbs datafile '/u01/app/oracle/oradata/ora11g/flm_tbs01.dbf' size 100m autoextend on extent management local uniform size 1m segment space management manual; HR> create table flm_t (id char(1000)) storage(freelists 1) tablespace flm_tbs; exec dbms_application_info.set_client_info('sess_1') HR2> exec dbms_application_info.set_client_info('sess_2') HR1> + HR2> 동시 수행 begin for i in 1..100000 loop insert into flm_t values(' '); end loop; commit; end; / SYS> select client_info, sid from v$session where client_info in('sess_1','sess_2'); CLIENT_INFO SID -------------------- ------- sess_2 19 sess_1 20 select sid, event, total_waits, time_waited from v$session_event where sid in (19,20); SID EVENT TOTAL_WAITS TIME_WAITED ------- ----------------------------------- ----------- ----------- 19 Disk file operations I/O 29 2 19 Data file init write 15 1 19 control file sequential read 185 1 19 control file parallel write 35 44 19 latch: cache buffers chains 8 1 19 buffer busy waits 11560 1563 19 read by other session 70 2 19 log buffer space 48 645 19 log file switch (checkpoint incomplete) 4 904 19 log file switch completion 3 5 19 log file sync 4 24 19 db file sequential read 18257 149 19 db file single write 5 1 19 flashback log file sync 10 180 19 enq: HW - contention 2489 1378 19 latch: row cache objects 2 0 19 cursor: pin S 10 5 19 cursor: pin S wait on X 1 0 19 resmgr:cpu quantum 1 1 19 SQL*Net message to client 22 0 19 SQL*Net message from client 21 130034 19 events in waitclass Other 75 542 20 Disk file operations I/O 65 7 20 Data file init write 36 2 20 control file sequential read 444 0 20 control file parallel write 84 275 20 latch: cache buffers chains 3 0 20 buffer busy waits 11771 2703 20 log buffer space 65 433 20 log file switch (checkpoint incomplete) 4 744 20 log file switch completion 4 55 20 log file sync 4 209 20 db file sequential read 20332 119 20 db file scattered read 58 0 20 db file single write 12 2 20 db file parallel read 698 3 20 flashback log file sync 17 30 20 enq: HW - contention 2432 436 20 cursor: pin S 12 2 20 latch: shared pool 1 0 20 SQL*Net message to client 20 0 20 SQL*Net message from client 19 106761 20 events in waitclass Other 187 387 - log buffer space : 리두 공간 생성 시 - log file sync : 커밋이 발생 시 - log file switch completion : 로그스위치 발생 시 wait > seq# 변경되어 컨트롤 파일도 읽고 쓰고 - cursor: pin S : 쉐어모드로 커서찍으려는데 대기 시 - cursor: pin S wait on X : exclusive 하려는데 쉐어모드라 대개 중 LCU > 라이브러리 캐시/ 핀 # HWM(High-Water Mark) 이동하는 과정에 HW lock 경합이 발생할 수 있다. 이 경우 발생하는 이벤트는 enq : HW - contention select sid, prev_sql_id from v$session where sid in (19,20); SID PREV_SQL_ID ------- ------------- 19 7dzbrt8yp1bg3 20 7dzbrt8yp1bg3 select * from table(dbms_xplan.display_cursor('7dzbrt8yp1bg3')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID 7dzbrt8yp1bg3, child number 0 ------------------------------------- INSERT INTO FLM_T VALUES(' ') ------------------------------------------------- | Id | Operation | Name | Cost | ------------------------------------------------- | 0 | INSERT STATEMENT | | 1 | | 1 | LOAD TABLE CONVENTIONAL | | | ------------------------------------------------- Note ----- - cpu costing is off (consider enabling it) |
|
| << 예시 5 >> ASSM(Auto Segment Space Management) | |
| SYS> drop tablespace flm_tbs including contents and datafiles; create tablespace flm_tbs datafile '/u01/app/oracle/oradata/ora11g/flm_tbs01.dbf' size 100m autoextend on extent management local uniform size 1m segment space management auto; alter system flush shared_pool; alter system flush shared_pool; alter system flush buffer_cache; alter system flush buffer_cache; HR1> create table flm_t (id char(1000)) tablespace flm_tbs; exec dbms_application_info.set_client_info('sess_1') HR2> exec dbms_application_info.set_client_info('sess_2') SYS> select client_info, sid from v$session where client_info in('sess_1','sess_2'); CLIENT_INFO SID -------------------- ------- sess_1 19 sess_2 20 HR1> + HR2> 동시 수행 begin for i in 1..100000 loop insert into flm_t values(' '); end loop; commit; end; / SYS> select sid, event, total_waits, time_waited from v$session_event where sid in (19,20); SID EVENT TOTAL_WAITS TIME_WAITED ------- ----------------------------------- ----------- ----------- 19 Disk file operations I/O 284 63 19 Data file init write 168 3 19 control file sequential read 2072 1 19 control file parallel write 392 328 19 latch: cache buffers chains 18 0 19 buffer busy waits 2019 341 19 read by other session 17 58 19 log buffer space 33 765 19 log file switch (checkpoint incomplete) 2 403 19 log file sync 2 2 19 db file sequential read 1831 74 19 db file scattered read 20 4 19 db file single write 56 122 19 db file parallel read 3 4 19 flashback log file sync 72 48 19 enq: HW - contention 134 1088 19 cursor: pin S 3 3 19 SQL*Net message to client 15 0 19 SQL*Net message from client 14 2883 19 events in waitclass Other 909 1334 20 Disk file operations I/O 354 65 20 Data file init write 210 8 20 control file sequential read 2590 1 20 control file parallel write 490 454 20 latch: cache buffers chains 28 1 20 buffer busy waits 1934 357 20 read by other session 8 5 20 log buffer space 34 490 20 log file switch (checkpoint incomplete) 6 561 20 log file switch completion 6 98 20 log file sync 2 5 20 db file sequential read 1393 36 20 db file scattered read 16 57 20 db file single write 70 39 20 flashback log file sync 78 86 20 enq: HW - contention 153 1166 20 latch: row cache objects 1 0 20 cursor: pin S 9 4 20 cursor: pin S wait on X 1 2 20 SQL*Net message to client 14 0 20 SQL*Net message from client 13 2609 20 events in waitclass Other 1031 1181 select sid, prev_sql_id from v$session where sid in (19,20); SID PREV_SQL_ID ------- ------------- 19 fp6146x56b2g6 20 fp6146x56b2g6 select sql_id, sql_text, version_count from v$sqlarea where sql_id = 'fp6146x56b2g6'; SQL_ID SQL_TEXT VERSION_COUNT ------------- -------------------------------------------------- ------------- fp6146x56b2g6 begin for i in 1..100000 loop insert into flm_t 1 values(' '); end loop; commit; end; select * from table(dbms_xplan.display_cursor('fp6146x56b2g6')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID fp6146x56b2g6, child number 0 begin for i in 1..100000 loop insert into flm_t values(' '); end loop; commit; end; NOTE: cannot fetch plan for SQL_ID: fp6146x56b2g6, CHILD_NUMBER: 0 Please verify value of SQL_ID and CHILD_NUMBER; It could also be that the plan is no longer in cursor cache (check v$sql_plan) |
|
alter system flush shared_pool;
alter system flush buffer_cache;
select * from v$latch_children;
select * from x$ksppi;
select * from x$ksppcv;
select * from x$ksppsv;
x$kcbwbpd
x$kcbwds
from v$session
from v$session_event
select * from table(dbms_xplan.display_cursor('7k4m665w2vvkh'));
exec dbms_application_info.set_client_info('sess_1')
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/instance-tuning-using-performance-views.html#TGDBA-GUID-03401D0F-DB3E-49E5-89E0-2F2A6164A5C0
Database Performance Tuning Guide
docs.oracle.com
'Oracle 11g > Tuning' 카테고리의 다른 글
| 83 Explain Plan, AutoTrace, v$sql_plan, Buffer Pinning, SQL TRACE (0) | 2024.02.13 |
|---|---|
| 82 TX LOCK, Full Table Scan, Rowid Scan, Index Scan (1) | 2024.02.08 |
| 81 Trans, Lock, Redo, Append Hint (1) | 2024.02.07 |
| 79 Shared Pool, Library Cache (1) | 2024.02.05 |
| 78 SGA, Shared Pool, Select (0) | 2024.02.02 |