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으로 이동할 필요가 없으므로 수정 수행 속도가 증가
- 자주 수정되는 세그먼트에 적합
(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 할당 확인
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)"
===> 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 발생
-- -> 테이블 삭제 후 시노님도 함께 삭제 필요!
'Study Note > Database' 카테고리의 다른 글
| DBMS JOB & Scheduler (0) | 2016.02.02 |
|---|---|
| User 관리하기 (0) | 2016.02.02 |
| Tablespace & Datafiles (0) | 2016.02.02 |
| Redo Log files (0) | 2016.02.02 |
| SQL 문장의 실행 원리 & 백그라운드프로세스 & Startup & Shutdown (0) | 2016.01.27 |
