Oracle Block

Oracle Block

◆ Oracle Block 개요

- Oracle에서의 최소 작업 단위

- 사용자가 입력한 데이터를 하드디스크에 저장하거나 읽어들 일 때 1건씩 처리하는 구조가 아닌, 가장 작은 단위인 block 단위로 작업

- DB_BLOCK_SIZE 파라미터로 block 사이즈 조절 가능, default는 8K (BUT, 생성 시 지정하면 바꿀 수 없음)

- create database 할 때 한 번 지정되면 그 값은 database를 재 생성하기 전에는 변경할 수 없다.

- SQL> show parameter db_block_size로 조회

- 2KB부터 4KB,8KB,16KB,32KB가 제공

- 짝수 SIZE 권장

- block 사이즈가 작다면 한 번에 담을 수 있는 데이터양이 작아져 disk I/O가 많아짐

- block 사이즈가 크면 데이터가 작을 경우 공간이 낭비될 수 있고 wait time이 많이 생길 수 있음

- Tablespace 생성 시 block size를 기존과 다르게 설정할 수 있으나 먼저 database buffer cache에도

해당 block 사이즈 만큼 공간을 미리 할당해두어야 설정 가능(권고 X) * memory도 1:1로 matching 해야 함 (p.224)

 

◆ Oracle Data Block 상세구조

- 블록헤더(Header) : 일반적인 Block의 정보를 가지고 있습니다.

(Block의 위치, Segment의 형태), 85~100bytes정도

- Table Directory : 클러스터에 있는 테이블에 관한 정보를 가지고 있습니다.

- Row Directory : Block내의 Row관련 정보를 가지고 있습니다. 각 Row마다 2byte

- Free Space : New Row Insert나 Update시 사용 합니다. PCTFREE값과 PCTUSED에

의해 결정 됩니다.

* 로우 마이그레이션(ROW MIGRATION) 발생 --> 많은 디스크 I/O 발생

(인접한 공간으로 이주)

- Row Data : 테이블 데이터와 인덱스 데이터를 포함 합니다.

 

 

◆ PCTFREE

 

- 사용 가능한 Block 공간 중에서 데이터 행의 UPDATE시 block의 여유 공간이

없어 다른 block에 저장될 것에 대비하여 확보해 놓은 영역

ex) "PCTFREE 20" 으로 설정을 하면, 데이터 블록의 20%를 사용 가능한 빈 영역으로 유지하여 각 블록에 있는 행을 갱신하는데

      사용한다는 의미

- PCTFREE의 기본 값은 10%

- INDEX값은 수정이 자주 발생하지 않으므로 PCTFREE는 5%정도가 적당

- 이 공간은 데이터가 insert 되지 않고 오로지 update만을 위해 남겨놓은 공간

- PCTFREE값이 도달된 후 블록 내에 남은 공간은 기존의 ROW가 Update되어 공간이 추가로 필요할 때 사용됨 

- 이 공간을 제외한 나머지 공간이 가득 차게 되면 이 block은 더 이상 빈 공간이 없는 Dirty Block 상태로 변경

- PCTFREE와 PCTUSED의 합이 100을 초과하지 않는 범위 내에서 0에서 99까지 값을 PCTFREE값으로 사용할 수 있음

(PCTFREE+PCTUSED <= 100)

 

1) PCTFREE가 적을 경우

- 기존 테이블 행 갱신에 의한 확장을 위해 적은 공간을 확보

- update가 빈번히 일어나는 경우 Row Migration이 자주 발생

- 많은 row가 한 block에 입력 가능

- 수정이 적은 세그먼트에 적합

 

2) PCTFREE가 클 경우

- 블록 당 적은 row가 입력, 즉 같은 row를 입력하기 위해서 많은 block이 필요

- update시 다른 block으로 이동할 필요가 없으므로 수정 수행 속도가 증가

- 자주 수정되는 세그먼트에 적합

 

create table pct_test1

(a number, b varchar2(10));

 

create table pct_test2

(a number, b varchar2(10))

PCTFREE 50;

 

--테이블 별 storage 옵션 확인

select owner, table_name, pct_free, pct_used, num_rows,blocks,chain_cnt

from dba_tables

where table_name like 'PCT_TEST%';

 

◆ block내 빈 공간을 관리하는 과정

- 비어있는 block의 명단은 Free list에 다 기록되고, 빈 공간이 없는 block는 Dirty list에 기록되는데 데이터 1건 지울 때 마다

Free list와 Dirty list를 업데이트 하는 경우 부하가 매우 큼

- block에 있는 row가 삭제되어서 사용량이 줄어들어도 즉시 이 block을 재사용하지 않고 특정 양이 빌 때까지 기다렸다 재사용 함

- 지워진 후 남은 용량이 얼마가 되어야 Free block이 되는지를 결정하는 파라미터가 PCTUSED

 

◆ PCTUSED

 

- 계속해서 데이터가 지워져서 빈 공간이 생길 경우 Dirty block에서 Free block으로 변경되는 기준을 의미

- PCTUSED=40 : 데이터 블록의 사용영역이 40%보다 적어지지 않으면 새로운 행을 INSERT할 수 없음(PCTUSED의 기본 값은 40%)

- ASSM(Automatic Segment Space Management)일 경우 사용되지 않음 (default)

 

1) PCTUSED값이 적을 경우

- 블록이 재사용되는 일이 적어질 수 있으므로 처리 비용이 감소

- 데이터베이스에서 사용되지 않은 공간이 증가

2) PCTUSED값이 클 경우

- 블록이 재사용되는 일이 많아질 수 있으므로 처리 비용이 증가

- 공간 사용도를 향상

 

 

PCTFREE = 20 / PCTUSED = 40 일 경우

 

◆ Row Chaining

- Row Chaining은 DB_BLOCK_SIZE보다 너무 큰 데이터가 들어왔을 경우, 혹은 남아 있는 block size보다 큰 데이터가

들어올 경우 인접한 다른 블록까지 데이터가 쓰여지는 것

- 이 데이터 블록은 원래 블록과 연결 되어짐

- Row Chaining은 블록이 하나의 I/O 작업과 동일한 양을 수행하기 위해 두 개의 I/O를 사용해야 하므로 성능상의 부하를 줌

- Row Chaining 현상을 줄이기 위해서는 block size를 크게 생성하는 것이 좋음

 

◆ Row Migration

- UPDATE시 row에 저장된 Data의 양을 증가시켜 더 이상 그 블록에 저장될 수 없을 경우 새로운 Block으로

데이터를 완전히 옮기는데 이를 Row Migration 이라고 함

- Update시 공간이 부족할 때 발생하며, Data 행을 완전히 채울 수 있는 새로운 Block에 저장됨

- 원래 block에 신규 block의 포인터 주소를 남겨두어 이전 block으로 찾아오는 access도 신규 block을 찾아갈 수 있도록 함

- PCTFREE값을 많이 주거나 reorg 등의 작업을 통해 줄일 수 있음 ( 향후 )

* PCTFREE를 그대로 가져 갈 것이라면 해당 테이블스페이스에 move,

PCTFREE값을 바꾸려면 TableSpace를 새로 생성 후 move

 

-- PCT_FREE_TEST를 위한 TEST TABLE 생성

-- PCTFREE 10(DEFAULT)

create table pct_test1

(a number, b varchar2(10));

 

-- PCTFREE 50으로 생성

create table pct_test2

(a number, b varchar2(10))

PCTFREE 50;

 

-- table별 storage 옵션 확인

select owner, table_name, pct_free, pct_used, num_rows, -- 실제 data 건수

blocks, -- 실제 block 수

chain_cnt, -- row_chain 수

last_analyzed -- 마지막 통계정보 갱신 날짜

from dba_tables

where 1=1

and table_name like 'PCT_TEST%' ;

 

-- 대용량 data insert

begin for i in 1..100000 loop

insert

into pct_test1

values(i,'AAAA');

end loop;

commit;

end;

/

 

begin for i in 1..100000 loop

insert

into pct_test2

values(i,'AAAA');

end loop;

commit;

end;

/

 

 

-- 통계정보 갱신 후 block수 재 확인

exec dbms_stats.gather_table_stats('SCOTT','PCT_TEST1');

exec dbms_stats.gather_table_stats('SCOTT','PCT_TEST2');

 

-- 대용량의 update 발생

update pct_test1

set b='AAAAAAAA';

commit;

 

update pct_test2

set b='AAAAAAAA';

commit;

 

-- 통계정보 갱신 후 update 후 달라지는 block 수 확인

exec dbms_stats.gather_table_stats('SCOTT','PCT_TEST1');

exec dbms_stats.gather_table_stats('SCOTT','PCT_TEST2');

 

-- 결론

-- pctfree가 10%인 경우 대량 update 발생 후 block 수가 현저히 증가

-- pctfree가 50%인 경우 update를 pctfree로 할당한 공간에서

-- 충분히 수행했기때문에 block수가 그대로인 것을 확인할 수 있음

 

◆ Extent

- Extent는 일정한 수의 연속된 oracle block

- 일정한 수라는 의미는 사용자가 지정한 값을 의미

- create table 옵션에 INITIAL EXTENT로 설정

- INITIAL EXTENT = 64K이면 DB_BLOCK_SIZE=8K일 경우 연속해서 8개의 block이 1개의 extent를 구성

- 10g 버전부터는 ASSM(Automatic Segment Space Management)이 기본이며, 이때 extent size는 64K임

- Extent 값이 크면 대량 insert에 유용하며, 블록데이터들을 인접하게 생성할 수 있음 ( But, 공간을 많이 차지함 )

- create table로 생성시 64k로 생성됐으나 10g부터 default creation이 적용 되 0k로 생성됨

◆ Extent 특징

- Object는 현재 할당된 모든 Extent가 이미 채워진 경우에만 새로운 Extent를 할당

- 새로운 Extent는 Next Extent 크기만큼 할당(10g 이상부터는 64KB가 기본)

- Next Extent = Next * (1+Pctincrease/100)

- Free Extent를 할당하고 관리하는 방식은 DMT와 LMT로 나뉘게 된다

●DMT(DINCTIONARY MANAGED TABLESPACE)

- 전통적인 방법으로 STORAGE절을 사용하여 EXTENT를 할당

- Oracle 9i 버전 이후 DICTIONARY MANAGED 방식의 사용을 권장하지 않음

● (LOCALLY MANAGED TABLESPACE)

- CREATE TABLE절에 STORAGE를 모두 무시

- TABLESPACE 정한 UNIFORM SIZE 대로 EXTENT 할당

- Segment를 관리하는 방식에 따라 FLM과 ASSM으로 나뉨

◆ Extent 사용 이유

- 해당 테이블이 사용할 extent를 미리 할당함으로써 data file내 곳곳에 분산되어 저장되는 것을 피하기 위함

- 동일 table의 data가 곳곳의 block에 저장되어 있다면 이를 찾는 시간이 오래 걸려 전체적인 성능 저하 발생

 

create tablespace extent1

datafile '/home/oracle/oradata/testdb/extent01.dbf' SIZE 50M

EXTENT MANAGEMENT LOCAL -- 생략시 default

UNIFORM SIZE 1M; ====> LOCAL 이므로 테이블 생성시 STORAGE를 무시함

 

create table extent_test (a number, b varchar2(10)) tablespace extent1;

create table extent_test2 (a number, b varchar2(10)) tablespace extent1 storage ( INITIAL 64K NEXT 1M );

 

-- extent 할당 확인

select tablespace_name ,

segment_name ,

extent_id,

blocks,

bytes/1024 "extent_size(KB)"

from dba_extents

where segment_name like 'EXTENT%';

 

-- table 별 storage 옵션 확인(PCT_FREE, PCT_USED)

select table_name, initial_extent/1024 "initial_extent(KB)",next_extent/1024 "next_extent(KB)"

from dba_tables

where table_name like 'EXTENT%';

   

===> LMT일땐 next_extent의 의미는 무의미 ( dba_tables View만 보고 판단 X )

 

-- table별 reorg *CTAS로 reorg시

alter table emp move tablespace example;

 

-- table reorg시 index unusable 상태로 변경되므로 rebuild 필요 ==> Rowid구조가 바뀌므로

select 'alter index '||owner||'.'||index_name||' rebuild;'

from dba_indexes

where status='UNUSABLE';

-- 결론

-- LMT 환경에서는 uniform size로 지정한 크기만큼

-- extent가 할당되고 따라서, 생성한 test1과 2의 extent size가 똑같다

-- 또한 initial extent size와 next extent size가 동일하게 할당된다

 

◆ FLM(Free List Management)

- Oracle 8i까지의 Segment 관리 기법

- Segment의 Free Block들은 항상 freelist를 통해 관리 됨

- PCTUSED 아래로 채워진 block들이 freelist로 연결되어 있어서, insert가 필요하면

이 freelist를 segment header에서부터 뒤지면서 블록내의 빈 공간에 insert를 하게 됨

- Undo, Redo 같은 파일들은 제외

 

◆ ASSM(Automatic Segment Space Management)

- Oracle 9i부터는 PCTFREE, PCTUSED를 직접 지정하는 기존의 FLM 방법 대신 자동으로 관리하는 방법을 권장

- segment에 할당된 space를 freelist 대신 bitmap으로 관리

- ASSM 방법을 이용하여 space를 관리하게 되면 freelist를 타고 다음다음 block을 access하는 대신 Tree구조의

bitmap 정보를 참고로 적당한 block들을 선택하기 때문에 space에 관한 성능이 훨씬 좋아짐

- ASSM 방식을 이용하려면 반드시 LOCALLY MANAGED TABLESPACE여야 함

 

DMT - table별 extent 별도 관리

- extent size(initial, next) 다르게 지정 가능

LMT - table 별 동일 extent size

- initial,next extent size 동일 (FLM : PCTUSED O , ASSM : PCTUSED X)

 

◆ SEGMENT

- Oracle Object 중에서 독자적인 저장 공간을 가지는 것

- 데이터가 증가되면 실제 증가되는 영역

- Table,Index,Undo,Temp 등이 대표적인 Segment의 종류

- 여러 개의 Extent로 구성

-- nologging 테스트 시나리오

-- nologging_test1 : 일반테이블, 아무 옵션 없이 insert

-- nologging_test2 : table nologging 옵션

-- nologging_test3 : table은 그대로, insert append

 

create table nologging_test5

tablespace haksa

as

select * from nologging_test3;

 

create table nologging_test6

tablespace haksa

nologging

as

select * from nologging_test1;

 

-- data insert

begin for i in 1..5000000 loop

insert

into nologging_test1

values(i,'AAAA');

end loop;

commit;

end;

/

 

truncate table nologging_test2;

 

begin for i in 1..5000000 loop

insert

into nologging_test2

values(i,'AAAA');

end loop;

commit;

end;

/

 

truncate table nologging_test3

 

begin for i in 1..5000000 loop

insert /*+ append */

into nologging_test3

values(i,'AAAA');

end loop;

commit;

end;

/

 

begin for i in 1..5000000 loop

insert /*+ append */

into nologging_test4

values(i,'AAAA');

end loop;

commit;

end;

/

 

-- 날짜별 redo log 사용량

select to_char(first_time, 'YYYY-MM-DD') "Date"

, count(*)

from v$log_history

group by to_char(first_time

, 'YYYY-MM-DD')

order by 1;

-- 실제 redo log 사용 현황

SELECT name,

value

FROM v$sysstat

WHERE name in ('redo size',

'redo writes' ,

'redo blocks written');

-- 결론

-- nologging일 경우에는 변경된 내용을 redo, undo에 쌓지 않아서 빠르다.

-- 실제 nologging이 적용되려면 CTAS, create index여야 한다

-- 일반 itas일 경우에는 nologging의 효력이 거의 없다

-- 특히 redo writes보다 redo blocks written의 차이가 더 큰 이유는

-- nologging일 경우 실제 redo buffer에 기록되는 것 보다 --> 실제 버퍼에는 입력하나 리두 로그 파일엔 내리지 않음

-- redo log file에 기록을 훨씬 덜 하게 하면서 성능 상 좋아지기 때문

-- synonym test

select * from dba_tables

where table_name like '%EXTENT_TEST%'

 

SELECT * FROM DBA_INDEXES

WHERE 1=1

-- AND TABLE_NAME='TEMP_TEST'

AND INDEX_NAME='TEMP_TEST_IND'

 

create or replace public synonym EXTENT_TEST for system.EXTENT_TEST;

 

drop table EXTENT_TEST;

 

select * from dba_synonyms

where SYNONYM_NAME='EXTENT_TEST'

 

select * from EXTENT_TEST;

 

create table EXTENT_TEST

(a number, b varchar2(10));

 

-- 결론

-- 테이블(EXTENT_TEST)을 삭제했다가 같은 이름의 테이블을 재생성해도

-- 이미 설정해둔 SYNONYM은 사라지지 않고,

-- 새로 생성된 같은이름의 테이블에 적용된다

-- 테이블 삭제 후 시노님이 그대로 있을 경우에 테이블 조회 시

-- table does not exist error가 아닌 loop synonym error 발생

-- -> 테이블 삭제 후 시노님도 함께 삭제 필요!