Database buffer cache 전체 flush
alter system flush buffer_cache;
Database buffer cache default flush
alter system flush buffer_pool default;
Database buffer cache keep flush
alter system flush buffer_pool keep;
Database buffer cache recycle flush
alter system flush buffer_pool recycle;
buffer header 정보
- x$bh
select o.object_name, decode(state,0,'free',1,'xcur',2,'scur',3,'cr'), dbarfil, dbablk, tch
from x$bh b , dba_objects o
where b.obj = o.data_object_id
and o.owner = 'HR'
and o.object_name = 'EMPLOYEES';- v$bh - x$bh의 view
select b.*
from v$bh b , dba_objects o
where b.objd = o.data_object_id
and o.owner = 'HR'
and o.object_name = 'EMPLOYEES';0: free: 블록이 비어 있거나 사용되지 않음을 나타냅니다.1: xcur: 싱글 모드에서 현재 블록이 사용 중(select)이다. (Exclusive Current).2: scur: RAC모드에서 블록이 공유 상태임을 나타냅니다 (Shared Current).3: cr: 트랜잭션이 완료되기 전의 일관된 데이터 상태를 유지하는 블록. (Consistent Read).
- 테이블 조회
select * from hr.employees where employee_id = 100;- 처음에는 buffer cache에 block이 올라가 있지 않기 때문에 physical i/o가 발생한다.
- 처음 access 하는거기 때문에 TCH는 1이다.
- 업데이트
- 다른세션에서 테이블을 조회
- 다시 buffer header 정보를 보면 다른세션에서는 cr된 테이블을 조회했기 때문에 buffer block이 하나 더 생성되었다.
hit ratio 확인
select db_block_gets, consistent_gets, physical_reads,
case
when db_block_gets + consistent_gets <> 0
then round((1 - (physical_reads/(db_block_gets+consistent_gets))) * 100,2) end "default_hit(%)"
from v$buffer_pool_statistics
where name = 'DEFAULT';
- db_block_gets : DML작업(transaction 작업)으로 logical i/o 가 발생시 증가됨
- consistent_gets : select 작업으로 logical i/o가 발생시 증가됨
- physical_reads : physical i/o가 발생시 증가
logical i/o 수는 physical i/o수를 포함한 수 이다. 왜냐하면 physical i/o로 메모리로 올린 다음 logical i/o를 수행하기 때문이다.
Non Standard block(9i)
- 4k buffer cache 확인
show parameter db_4k_cache_size
- block size를 변경하기 전에 free 메모리 공간 확인
select * from v$sgainfo;
- db_4k_cache_size 변경
alter system set db_4k_cache_size=12m;
- name은 default로 보이지만 block size를 보고 non standard block을 확인할 수 있다.
select name,block_size, current_size from v$buffer_pool;
- 4k block을 사용하는 테이블스페이스 생성
create tablespace oltp_tbs
datafile '/u01/app/oracle/oradata/ORA19C/oltp_tbs01.dbf' size 10m autoextend on
blocksize 4k
extent management local uniform size 1m
segment space management auto;- 4k block 테이블스페이스안에 신규 테이블 생성
create table hr.oltp_emp tablespace oltp_tbs as select * from hr.employees;- block의 사이즈가 4k이기 때문에 OLTP_EMP 테이블이 더 많은 블록을 사용하고 있는걸 알 수 있다.
select segment_name, extent_id, file_id, block_id, blocks
from dba_extents
where owner = 'HR'
and segment_name in ('EMPLOYEES','OLTP_EMP');
select table_name,blocks, avg_row_len from dba_tables where owner='HR' and table_name in ('EMPLOYEES','OLTP_EMP');
- 4k buffer cache 에 대한 hit ratio 확인
- nk 공간은 이름이 DEFAULT이기 때문에 block_size로 확인해야 한다.
select name, block_size,db_block_gets, consistent_gets, physical_reads,
case
when db_block_gets + consistent_gets <> 0
then round((1 - (physical_reads/(db_block_gets+consistent_gets))) * 100,2) end "default_hit(%)"
from v$buffer_pool_statistics
where name = 'DEFAULT';
- 테이블 조회
select * from hr.oltp_emp where employee_id = 100;- hit ratio를 다시 확인
- physical i/o를 발생한 걸 알 수 있다.
- nk 공간에 대한 flush
- 오류를 발생하면 buffer_pool 뒤의 옵션을 알 수 있다.
alter system flush buffer_pool;
alter system flush buffer_pool pool_4k;
- flush 후 다시 테이블 조회 후 delta 값 확인
Transaction 처리 순서
update hr.employees
set salary = salary * 1.1
where employee_id = 100;1 parse
2 bind
3 execute
3-1) undo segment 할당
- 처음 undo tablespace를 생성할때 기본적으로 10개의 undo segment를 생성한다.
- 하지만 기존의 undo segment를 다 사용중인 경우 3가지를 확인한다.
- 오프라인으로 떨어져 있는 segment가 있는지 확인한다
- 10개를 더 추가적으로 확장이 가능한지 확인한다.
- 아니면 트랜젝션 작업중인 세그먼트중 작게 사용 하고 있는 세그먼트가 있다면 공유해서 같이 사용한다.
enq : US - contention wait event: undo segment 할당 받을 때 까지 대기하는 이벤트
3-2) undo segment header에 transaction table slot 생성
- transaction 정보를 저장하는 곳, TXID(Transaction ID)를 생성한다.
3-3) transaction의 대상이 되는 블록을 데이터 버퍼 캐시에 실행계획을 통해서 찾는다.
-
buffer header에 buffer lock을 exclusive mode로 획득하고
transaction layer(ITL(Interested Transaction List))에transaction entry(slot)등록한다. -
만약에 ITL에 transaction entry 등록할 공간이 없다면 공간이 확보될때 까지 대기 해야한다.
이때 발생하는 대기 이벤트는enq : TX - allocate ITL entry- wait event가 발생하는 이유 : transaction이 commit or rollback 하지 않아서이다.
-
transaction entry(slot)이 만들어지는 이유는 block 안에서 어떤 row가 transaction 작업이 발생되는지, 어떤 undo segment를 사용하는지에 대한 정보를 가지고 undo segment header 와 point 되어있다. 트랜잭션의 진행 상태도 알 수 있다.(commit, rollback, 진행중) -
ini_trans = transaction slot 수 확인
select pct_free,pct_used, ini_trans, max_trans, freelists from dba_tables where owner='HR' and table_name='EMPLOYEES';
3-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로 복사한다.
change vector 정보를 redo entry라는 이름으로 redo log buffer로 복사하기 위해서는 latch를 획득하고 수행한다.
이과정에서 latch 경합이 발생하면
latch : redo copy: change vector를 redo entry로 copy 하기 위한 latchlatch : redo allocation: redo log buffer의 free 공간을 체크하는 latchlatch : redo writing: redo log buffer에 free 공간이 없을 경우 LGWR에 redo entry를 current한 reo log group으로 옮겨달라는 시그널을 보내는 latch
redo log buffer에 free 공간이 없어서 redo writing latch를 잡고 LGWR에게 redo entry를 redo log file에 write가 끝날때 까지 기다려야 한다.
이때 발생하는 이벤트는 log buffer space 이벤트가 발생한다.
log file switch completion : 현재 리두로그 파일이 꽉차서 로그 스위치가 발생하는 경우 lgwr은 로그 스위치 작업이 끝날때 까지 대기 해야한다.
3-5) 이전값에 대한 정보를 undo block에 기록하고 데이터 블록을 변경한다.
- 변경된 블록은 dirty buffer 상태가 된다.
- 변경된 블록 header에는 CR(Consistent Read) 블록으로 작성해 놓는다.
- 변경하는 행에 대해서 LOCK을 생성한다.
만약에 다른 세션에서 같은 행에 대해서 이미 LOCK을 걸고 있다면 대기 해야한다.
이때 발생하는 대기 이벤트는 enq : TX - row lock contention
3-6) 데이터 블록을 변경하고 이전값을 undo block에 저장했으면 buffer lock은 null 모드로 변경한다.(아직 commit or rollback을 하지 않았다.)
commit
- SCN(System Commit Number)할당, commit 정보를 redo log buffer에 저장
- undo segment 헤더의 transaction table에 commit이 완료되었다는 정보를 저장
(그래야 다른 세션에서 해당 언두 세그먼트를 사용할 수 있다) - 트랜잭션 대상의 행이 있는 블록의 transaction entry(slot), lock정보 해지
- LGWR 작동된다. redo log buffer의 내용을 리두 로그 파일에 기록한다.
- commit이 완료되었다는 메시지는 LGWR가 기록을 끝내야 나온다.
