Database Buffer Cache 구조
그림 설명.
DBA + Block class에 해시 함수를 적용하면 특정 bucket으로 할당됨.
여러 bucket을 하나의 cbc latch로 보호하기 때문에 경합이 원인이 된다.
Buffer Header는 doulble linked list로 연결되어 있다.
currnet block과 cr블럭은 같이 연결되어 있다.
latch를 획득시에 두가지 모드 shared 모드와 exclusive모드가 있다.
Bucket하나에 Header하나가 좋음.
Working Set은 버퍼의 부분집합이다. LRU list와 CKPT cue 두가지가 있다.
Working set의 갯수는 cpu의 갯수와 DBWR의 갯수에 따라 달라짐.
튜닝의 목표
1. hit ratio를 높일 것
※ hit ratio? 원하는 data block을 메모리에서 찾을 확률
계산법 : 1 - physical reads/logical reads
SQL> select name, value
from v$sysstat
where name in ('db block gets from cache',
'consistent gets from cache',
'physical reads cache');
- db block gets from cache : caching 되어 있는 data block을 읽은 횟수
- consistent gets from cache : cr블럭을 읽은 횟수
- physical reads cache : 디스크에서 data block을 읽은 횟수
hit ratio만으로는 튜닝의 대상인지 판단하기 어렵다.
예)
A SQL 99% 99만블럭 / 100만블럭
B SQL 60% 60블럭 / 100블럭
2. wait를 줄일 것 : 더 비중을 둔다.
① latch : cache buffer chains (cbc latch)
관련 view : v$latch, v$system_event
여러 유저가 같은 block을 access할 경우 cbc latch에 경합이 생긴다.
비효율적인 SQL, hot segments일 경우 이 지표가 올라간다.
hot segment를 찾는 방법
ⓐ 아래의 SQL을 실행
SQL> select *
from (select owner, object_name, object_type, statistic_name, sum(value)
from v$segment_statistics
group by owner, object_name, object_type, statistics_name
order by sum(value) desc)
where rownum < 10
ⓑ statspack을 남길 경우 level을 7이상으로 한다. -> segment 통계가 남는다.
② latch : cache buffer lru chains (cblc latch)
관련 view : v$latch, v$system_event
③ buffer busy waits
관련 view : v$system_event
: 하나의 버퍼에 여러 개의 프로세스가 동시에 사용하려고 할 때 발생하는 wait지표
buffer lock을 header에 기록
buffer busy waits를 확인하는 SQL
SQL> select class, count
from v$waitstat
where count>0
order by count desc;
block class를 확인하는 SQL
SQL> select rownum, w.* from v$waitstat w;
block class의 의미
| Class |
Description |
| 1 |
data block |
| 2 |
sort block |
| 3 |
save undo block |
| 4 |
segment header |
| 5 |
save undo header |
| 6 |
free list |
| 7 |
extent map |
| 8 |
1st level bmb |
| 9 |
2nd level bmb |
| 10 |
3rd level bmb |
| 11 |
bitmap block |
| 12 |
bitmap index block |
| 13 |
file header block |
| 14 |
unused |
| 15 + 2*r |
undo header block(r = Undo Segment 번호. 0 = System undo segment) |
| 16 + 2*r |
undo block(r = Undo Segment 번호. 0 = System undo segment) |
15 : system이라는 undo segment의 헤더 블럭
16 : system이라는 undo segment의 일반 undo 블럭
17 : 사용자가 사용할 수 있는 undo tablespace안에 있는 1번 undo segment의 헤더 블럭
18 : 사용자가 사용할 수 있는 undo tablespace안에 있는 1번 undo segment의 undo 블럭
cf) read by other session
: 한 프로세스가 데이터 블럭을 읽어서 메모리에 올리고 있는 중에 다른 프로세스가 읽으려고 할 때 발생하는 지표
-> I/O의 문제가 있는 것.
④ free buffer waits
관련 view : v$system_event
server process가 lru알고리즘을 이용해서 free buffer 를 찾는다. 그때 free buffer가 없어서 기다릴 경우 발생하는 지표
- 메모리의 크기를 늘여주거나 줄여준다.
관련 파라미터를 설정한다.(계속해서 모니터링 해야 한다.)
db_cache_size, db_keep_cache_size, db_recycle_cache_size
사전 준비사항
create index cust_idx storage (
buffer_pool keep ...)
create table customer storage (
buffer_pool recycle ...)
create index cust_name storage (
buffer_pool keep ....)
advisor를 사용하는 방법 : db_cache_advice를 on으로 설정하여야 한다.
관련 view : v$db_cache_advice
⑤ reads waits
- 관련 wait 지표
db file sequential read : single block io를 할 때 올라감.
db file parallel read : 병렬 처리로 data block을 읽어 올림.
db file scattered read
튜닝 방법
1. Database Buffer Cache의 크기를 늘이거나 줄이는 것
: ASMM, Advisor
2. SQL Tuning
3. Multiple Buffer Pool(default, keep, recycle)
교도소 : recycle
io를 줄이는 것이 목적.
4. Caching Table
사전 지식 : LRU 알고리즘.
아주 자주쓰는 작은 테이블을 caching tables로 만든다.
SQL> create table t1 .... cache;
-- select시에 hint를 이용
select /*+ cache */
* from t1;
5. DB_nK_CACHE_SIZE : multiple block size
6. DBWR 튜닝(N개 또는 Slave process(서버 프로세스가 DBWR의 역할을 대신하도록 한다.))
- DBWR의 갯수 변경 : DB_WRITER_PROCESSES파라미터를 변경시켜주면 됨. 최대 20개까지 늘일 수 있음.
- slave process : DBWR_IO_SLAVES파라미터를 변경시켜주면 됨.
7. Direct Path I/O
8. I/O system(SAME)
9. MBRC
flushing the Buffer Cache
SQL> alter system flush buffer_cache
기타 참고사항
- OWI (Oracle Wait Interface)?
: 오라클 서버 내에서 일어나는 여러가지 일들을 여러가지 지표를 통하여 확인하고
- Cache Fusion
: 한 데이터베이스에 두개 이상의 instance가 있는 환경(RAC)에서 disk에서 block을 읽어오는 것이 아니고 다른 instance의 buffer를 읽는 것.
- LRU알고리즘의 종류
1. standard lru
2. touch count lru
- SQL에서의 literal은 문자, 숫자, 날짜를 의미한다.
- 기술면접 예상질문 :
nested loop join에 대해서 말씀해보세요.(비교적 쉬운 질문)
Query Transformation에 대해서 말씀해보세요.
logical optimization, physical optimization에 대해서 말씀해보세요.
histogram에 대해서 아는데로 말씀해보세요.
- dirty buffer : 원본과 내용이 다른 버퍼
- keep의 크기는 들어갈 데이터의 크기에 조금 더 준다. - 왜? cr블럭을 위해서...
참고 :
http://blog.naver.com/orapybubu?Redirect=Log&logNo=40047392715
http://wiki.ex-em.com/index.php/Buffer_busy_waits
