이펙티브 오라클 (2008년)
I. DMT VS LMT
DMT(Dictionary Managed Tablespace)란 ?
- 데이터 파일의 공간이 데이터 사전 테이블,
- 즉 SYS.UET$(사용된 익스텐트)와 SYS.FET$(사용 가능한 익스텐트)에 관린된다.
- 익스텐트 해제/사용 시마다 재귀 SQL이 수행된다. 대부분의 경우 이들 작업은 매우 느리다.
LMT(Locally Managed Tablespace)란 ?
- Locally managed tablespace는 자신이 보유,
- 관리하는 각 extent들에 대한 freed, used 여부를 bitmap형태로 각 구성 datafile들에 저장한다.
- Bitmap내의 각 bit들은 하나의 block 또는 block의 묶음에(extent) 대한 status를 표시한다.
- 만약 이들 extent들을 free하거나 할당받아서 사용하게 되면 각각의 extent에 해당하는 bitmap값들이 변경되게 된다.
- 이러한 변경사항들은 data dictionay table들에 update작업을 수행하지 않는 관계로 rollback정보를 생성하지 않는다.
- 이렇게 local로 관리되는 경우 인접한 각 free extent들에 대한 정보가 자동적으로 추적가능하기 때문에 free extent를 coalesce할 필요성이 없어진다.
- 객체가 얼마나 커질지 모르는 상황에서는 시스템에 의해 관리되는 LMT를 사용하라.
- Locally managed tablespace는 동일한 size의 extent들을 가지도록 설정할 수도있고, system이 알아서 그 extent의 size를 결정하도록 할 수도 있다.
- Tablespace생성시의 UNIFORM 또는 AUTOALLOCATE option을 이용하여 이를 결정한다. (autocallocate uniform size 1M)
- 이때 주의할 점은, locally managed tablespace와 이 ASSM 방식간에 혼동을 일으키는 경우가 종종있다.
- 이것은 locally managed tablespace와 ASSM이 둘다 bitmap방식을 통해 space를 관리한다는 측면때문일 것으로 보인다.
- ASSM방식을 사용하려면 미리 이야기한대로 locally managed tablespace에서만 사용가능한데, 기본적으로 locally managed tablespace는 dba_free_space에서 확인되는 할당되지 않은 space에 대한 관리이고, ASSM은 일단 segment내에 할당된 extent안에서 block내의 free space에 관한것이다.
- LMT 는 아직 할당 되지 않은 Space 에 대한 관리, ASSM 은 이미 할당된 Space 에 대한 관리
- 즉, Local Managed tablespace 는 Free Space 즉. Segment 에 할당 이전의 공간에 대한 관리 방식이고, Segment Space Management Auto는 이미 Segment 에 할당된 공간에 대한 관리 방식이다.
- 또한 Tablespace 생성시 segment space management auto 지정 하였을 경우엔(Default) PCTFREE만 적용되며, PCTUSED 를 설정하여도 무시 된다.
- 또한 Locally Managed Tablespace 의 경우 Extent 관리는 Default로 LOCAL - AUTOALLOCATE - DBMS가 적절한 Extent Size 설정 하게 됩니다.
예제) 1. Tablespace 생성 예제 및 설명
create tablespace TS_TGDOM_D datafile '/u01/ORACLE/TGDOM/TS_TGDOM_D_01.dbf' size 1000m
autoextend on next 100m maxsize 2000m
extent management local autoallocate
\--> 빈 extent 를 찾을 때 bitmap 을 활용하고, 할당시 자동으로 해주어라
segment space management auto ;
\--> allocation 된 extent 내의 free block 을 찾을때 자동으로 할당 하라
예제) 2. LMT 의 Extent 자동 관리에 대한 검증
- LMT Tablespace 생성 - 자동 Allocation Type
sys@ARTDOM> create tablespace system_managed extent management local ;
Tablespace created.
LMT Tablespace 생성 - uniformsize Allocation Type
sys@ARTDOM> create tablespace uniform_size extent management local uniform size 5M
Tablespace created.
\#LMT Tablespace 에 Table 생성
sys@ARTDOM> create table big_Table as select rownum id, a.\* from all_objects a
where 1=0
\#LMT Tablespace 에 Table 을 Nologging 으로 설정
sys@ARTDOM> alter table big_table nologging ;
\#LMT Tablespace 에 Table 에 데이타 입력
sys@ARTDOM> declare
l_cnt number ;
l_rows number :=&1
begin
insert /*+ append \*/ into big_table select rownum, a.* from all_objects a;
l_cnt := sql%rowcount ;
commit ;
while ( l_cnt < l_rows )
loop
insert /*\+ append \*/ into big_table
select rownum+l_cnt, owner, object_name, subobject_name, object_id, data_object_id,
object_type, created, last_ddl_time, timestamp, status, temporary,
generated, secondary
from big_table
where rownum <= l_rows-l_cnt;
l_cnt := l_cnt+sql%rowcount;
commit ;
end loop ;
end ;
/
Enter value for 1: 10000000
old 3: l_rows number :=&1 ;
new 3: l_rows number :=10000000 ;
PL/SQL procedure successfully completed.
\#LMT Tablespace 에 Table 에 PK 설정
sys@ARTDOM>alter table big_table add constraint big_table_pk primary key(id)
/
Table altered.
\#LMT Tablespace 에 Table 통계자료 생성
sys@ARTDOM>begin dbms_stats.gather_table_stats
( ownname =>'SYS',
tabname =>'BIG_TABLE',
method_opt => 'for all indexed columns',
cascade => TRUE) ;
end ;
/
PL/SQL procedure successfully completed.
\#LMT Tablespace 에 Table 인덱스 생성
sys@ARTDOM>create index big_Table_owner_idx on big_table(owner) compute statistics ;
Index created.
\#LMT Tablespace 에 Table 을 Copy
sys@ARTDOM>create table big_table_copy tablespace system_managed as select * from big_table ;
Table created.
sys@ARTDOM> select rownum, tablespace_name, extent_id, bytes/1024,
blocks from user_extents where segment_name ='BIG_TABLE_COPY' ;
ROWNUM TABLESPACE_NAME EXTENT_ID BYTES/1024 BLOCKS
\---------\- \-----------------------------\- \---------\- \---------\- \---------\-
1 SYSTEM_MANAGED 0 64 8
2 SYSTEM_MANAGED 1 64 8
3 SYSTEM_MANAGED 2 64 8
4 SYSTEM_MANAGED 3 64 8
5 SYSTEM_MANAGED 4 64 8
6 SYSTEM_MANAGED 5 64 8
7 SYSTEM_MANAGED 6 64 8
8 SYSTEM_MANAGED 7 64 8
9 SYSTEM_MANAGED 8 64 8
10 SYSTEM_MANAGED 9 64 8
11 SYSTEM_MANAGED 10 64 8
12 SYSTEM_MANAGED 11 64 8
13 SYSTEM_MANAGED 12 64 8
14 SYSTEM_MANAGED 13 64 8
15 SYSTEM_MANAGED 14 64 8
16 SYSTEM_MANAGED 15 64 8
17 SYSTEM_MANAGED 16 1024 128
...
79 SYSTEM_MANAGED 78 1024 128
80 SYSTEM_MANAGED 79 8192 1024
...
199 SYSTEM_MANAGED 198 8192 1024
200 SYSTEM_MANAGED 199 65536 8192
1 ~ 16(16) : 64k
16 ~ 79(73) : 1024K(1M)
80 ~ 199(121) : 8192K(8M)
200 ~ : 65536K(64M)
시스템에 의해 관리되는 접근법에서는 데이터베이스가 알아서 익스텐트의
크기를 결정하도록 한다. 시스템에 의해 관리되는 LMT는 객체의 크기가 10G
이하인 경우에 효과적으로 동작한다.
# Tables Drop
sys@ARTDOM>drop table big_table_copy ;
Table dropped.
# Uniform Size Allocation Tablespace 에 생성
sys@ARTDOM>create table big_table_copy tablespace uniform_size as select * from big_table ;
Table created.
sys@ARTDOM>select rownum, tablespace_name, extent_id, bytes/1024, blocks from user_extents
2 where segment_name ='BIG_TABLE_COPY';
ROWNUM TABLESPACE_NAME EXTENT_ID BYTES/1024 BLOCKS
\---------\- \-----------------------------\- \---------\- \---------\- \---------\-
1 UNIFORM_SIZE 0 5120 640
2 UNIFORM_SIZE 1 5120 640
3 UNIFORM_SIZE 2 5120 640
4 UNIFORM_SIZE 3 5120 640
5 UNIFORM_SIZE 4 5120 640
6 UNIFORM_SIZE 5 5120 640
7 UNIFORM_SIZE 6 5120 640
....
233 UNIFORM_SIZE 232 5120 640
234 UNIFORM_SIZE 233 5120 640
235 UNIFORM_SIZE 234 5120 640
236 UNIFORM_SIZE 235 5120 640
236 rows selected.
- 예상한 바와 같이 이 테이블 영역에 있는 기존 익스텐트나 ?으로 할당될 익스텐트의 크기는 모두 정확히 5MB이다.
- 그렇다면 이 유형의 테이블 영역은 언제 유용한가? 객체의 궁극적인 크기가 알려진 경우에 유용하다.
II. LMT 에 관한 몇 가지 주의 사항
2-1. 획일적인 크기의 익스텐트용 매직 넘버는 64KB이다.
- 즉. LMT 에서 Free Extent 를 관리 하기 위해서 사용하는 Bitmap 의 크기는 64KB 이다.
예제) 파일을 관리하는데 사용되는 비트맵의 크기에 대한 검증
sys@ARTDOM>create tablespace five_meg
datafile size 100M
uniform size 5M
/
Tablespace created.
sys@ARTDOM>select sum(bytes/1024/1024) free_space
2 from dba_free_space
3 where tablespace_name ='FIVE_MEG'
4 /
FREE_SPACE
\---------\-
95
sys@ARTDOM>col file_name new_val f
sys@ARTDOM>select file_name from dba_data_files where tablespace_name ='FIVE_MEG';
FILE_NAME
\-------------------------------------------------\-
/u02b/ORACLE/ARTDOM/OMF/ARTDOM/datafile/o1_mf_five_meg_4cqc32cd_.dbf
104857600
104857600(100M) + 65536(64K) = 104923136 ( 확대할 사이즈 = 102464k )
sys@ARTDOM>alter database datafile '&f' resize 102464k ;
old 1: alter database datafile '&f' resize 102464k
new 1: alter database datafile '/u02b/ORACLE/ARTDOM/OMF/ARTDOM/datafile/o1_mf_five_meg_4cqc32cd_.dbf' resize 102464k
Database altered.
sys@ARTDOM>select sum(bytes/1024/1024) free_space
2 from dba_free_space
3 where tablespace_name ='FIVE_MEG'
4 /
FREE_SPACE
\---------\-
100
이 64K 규칙은 시스템에 의해 관리되는 익스텐트에는 적용될 필요가 없다는 사실에
유의 하게 바란다. 'uniform size 5M' 절 없이 상기 테스트를 실행 하였다면
99.9375MB, 즉 64K 를 제외한 모든 공간이 사용 가능한 것으로 나타났을 것이다.
sys@ARTDOM>create tablespace five_meg2 datafile size 100M ;
Tablespace created.
sys@ARTDOM>select sum(bytes/1024/1024) free_space
2 from dba_free_space
3 where tablespace_name ='FIVE_MEG2'
4 /
FREE_SPACE
\---------\-
99.9375
2-2. 시스템에 의해 관리되는 LMT는 파일과 다르게 할당한다.
- LMT 와 데이타 분산에 대한 검증
-- Uniform Size Allocation Type LMT 생성
sys@ARTDOM>create tablespace uniform_extents
datafile size 2112k, size 2112k, size 2112k, size 2112k uniform size 64k
/
Tablespace created.
-- Auto Allocation Type LMT 생성
sys@ARTDOM>create tablespace system_managed
datafile size 2112k, size 2112k, size 2112k, size 2112k
/
Tablespace created.
-- Uniform Size Allocation Type LMT 에 Table 생성
sys@ARTDOM>create table uniform_size ( x int, y char(2000)) tablespace uniform_extents ;
Table created.
-- Auto Allocation Type LMT 에 Table 생성
sys@ARTDOM>create table system_size ( x int, y char(2000)) tablespace system_managed ;
Table created.
-- Tablespace Size Check
sys@ARTDOM> select TABLESPACE_NAME,SUM(BYTES)/1024/1024 from dba_data_files
where tablespace_name IN ('UNIFORM_EXTENTS','SYSTEM_MANAGED')
group by tablespace_name ;
SYSTEM_MANAGED 8.25
UNIFORM_EXTENTS 8.25
-- Tablespace Datafile Check
sys@ARTDOM> select TABLESPACE_NAME,FILE_ID ,BYTES/1024/1024 from dba_data_files
where tablespace_name IN ('UNIFORM_EXTENTS','SYSTEM_MANAGED')
ORDER BY TABLESPACE_NAME, FILE_ID ;
SYSTEM_MANAGED 12 2.0625
SYSTEM_MANAGED 21 2.0625
SYSTEM_MANAGED 22 2.0625
SYSTEM_MANAGED 23 2.0625
UNIFORM_EXTENTS 16 2.0625
UNIFORM_EXTENTS 17 2.0625
UNIFORM_EXTENTS 18 2.0625
UNIFORM_EXTENTS 19 2.0625
-- uniform_siz Table 에 Data 입력
sys@ARTDOM>begin
2 loop
3 insert into uniform_size values ( 1, 'x' ) ;
4 commit ;
5 end loop ;
6 end ;
7 /
begin
\*
ERROR at line 1:
ORA-01653: unable to extend table SYS.UNIFORM_SIZE by 8 in tablespace UNIFORM_EXTENTS
ORA-06512: at line 3
-- system_size Data 입력
sys@ARTDOM>begin
2 loop
3 insert into system_size values ( 1, 'x');
4 commit ;
5 end loop ;
6 end ;
7 /
begin
\*
ERROR at line 1:
ORA-01653: unable to extend table SYS.SYSTEM_SIZE by 128 in tablespace SYSTEM_MANAGED
ORA-06512: at line 3
DATA 분포도 조사
sys@ARTDOM>select segment_name, extent_id, blocks, file_id from dba_extents
2 where segment_name in ('UNIFORM_SIZE','SYSTEM_SIZE')
3 and owner ='SYS'
4 order by segment_name , extent_id ;
SEGMENT_NAME EXTENT_ID BLOCKS FILE_ID
\-----------------------------\- \---------\- \---------\- \---------\-
SYSTEM_SIZE 0 8 12
SYSTEM_SIZE 1 8 12
SYSTEM_SIZE 2 8 12
SYSTEM_SIZE 3 8 12
SYSTEM_SIZE 4 8 12
SYSTEM_SIZE 5 8 12
SYSTEM_SIZE 6 8 12
SYSTEM_SIZE 7 8 12
SYSTEM_SIZE 8 8 12
SYSTEM_SIZE 9 8 12
SYSTEM_SIZE 10 8 12
SYSTEM_SIZE 11 8 12
SYSTEM_SIZE 12 8 12
SYSTEM_SIZE 13 8 12
SYSTEM_SIZE 14 8 12
SYSTEM_SIZE 15 8 12
SYSTEM_SIZE 16 128 20
SYSTEM_SIZE 17 128 21
SYSTEM_SIZE 18 128 22
SYSTEM_SIZE 19 128 12
SYSTEM_SIZE 20 128 20
SYSTEM_SIZE 21 128 21
SYSTEM_SIZE 22 128 22
출력을 보면 시스템에 의해 할당된 테이블 영역의 첫 번째 16개의 익스텍트는
모두 같은 파일(FILE_ID 22)로부터 할당되었다. 오라클이 다수의 작은 익스텐트를
여러 개의 서로 다른 파일에서 할당하지 않고 이렇게 한 이유는 이 테이블 영역으로부터
개체를 삭제할 경우 발생할지도 모르는 단편화를 줄이기 위함이다.
익스템트가 8블록(64KB)에서 128블록(1MB)으로 뛸 때에만 라운드 로빈 알고리즘이
동작한다. 일단 1MB 익스텐트에 도달하면서 파일 12,23,21,22 순으로 할당된다.
UNIFORM_SIZE 0 8 16
UNIFORM_SIZE 1 8 17
UNIFORM_SIZE 2 8 18
...
UNIFORM_SIZE 118 8 18
UNIFORM_SIZE 119 8 19
UNIFORM_SIZE 120 8 16
UNIFORM_SIZE 121 8 17
UNIFORM_SIZE 122 8 18
UNIFORM_SIZE 123 8 19
UNIFORM_SIZE 124 8 16
UNIFORM_SIZE 125 8 17
UNIFORM_SIZE 126 8 18
UNIFORM_SIZE 127 8 19
151 rows selected.
- uniform size 가 지정된 경우 획일적인 크기의 익스텐트에서는 단편화가 관심사가 아니므로 처음부터 라운드 로빈 할당이 사용된다.
2-3. 데이터 파일을 자동으로 확장하는 방법
- 이것은 주의해야 할 사항이 아니고 권고 사항이다.
- 획일적인 크기의 익세튼테에서는 다음 크기를 익스텐트 크기로 설정하고 앞에서 언급한 대로 여분의 64KB를 추가하여야 한다. MAXSIZE를 설정하려면 N*익스텐트 크기+64KB로 설정한다.(필자는 항상 MAXSIZE를 설정한다.)
- 시스템에 의해 관리되는 익스텐트에서는 모든 것이 조금 까다롭다.
- 사용자는 파일이 어떤 단위(예를 들면, 64KB, 1MB, 또는 8MB)로 확장되는 게 바람직한지를 모른다.
- 필자는 한번에 8MB씩 확장하라고 권하고 싶다.
- 처음에는 여러 크기(여분의 64KB는 의미가 없다.)로 시작해서 상관없지만 8MB 간격으로 확장하도록 하는 게 좋다.
- 그리고 큰 세그먼트가 파일을 자동으로 확장되도록 한다고 가정하여야 한다.
2-4. 구식 STORAGE 절에 유의한다.
- 일부 사람들은 LMT 에서 STORAGE 절은 무의미한다고 생각하는데 이는 초기 CREATE TABLE 문 이후에는 사실이다.
- 그러나 CREATE 문이 수행되는 동안에는 STORAE 가 완벽하게 효과를 발휘하기 때문에 이들의 부작용을 염두에 두어야 한다.
TABLESPACE UNIFORM SIZE 10M 가 지정된 테이블스페이스에서
CREATE TABLE ... STORAGE ( INITAL 1M NEXT 5M MINEXTENTS 3 )
- 오라클은 적어도 1MB+5MB+5MB=11MB 의 공간을 LMT에 할당할 것이다.
- ( 초기 익스텐트가 1MB가 할당되며 MINEXTENTS 절을 만족시키기 위하여 두 개의 5M 익스텐트가 할당된다.)
- 이제 10MB 의 획일적인 익스텐트 크기로 테이블 영역에 CREATE 문을 실행하였다고 가정해 보자.
- 처음에는 20MB가 할당되지만 CREATE 문의 수행된 이후에는 INITIAL,NEXT, PCTINCREASE, MINEXTENTS, 그리고 MAXEXTENTS 가 모두 무시되고 이때부터 LMT 가 동작한다.
- 따라서 STORAGE 절이 포함된 문에는 주의를 기울여야 한다.
- 이들 문은 생각보다 많은 공간을 할당할 수 있다.
- EXP 툴을 이용할 때는 특히 주희하여야 한다.
- 기본적으로 이 툴은 현재 할당된 익스텐트 크기를 합산한 후 초기 익스텐트 할당을 이 크기로 지정하는 STORAGE 절을 생성하는 COMPRESS=Y 옵션을 사용한다.
- 이것은 바람직하지 않다.
- 예제 ) EXPORT OPTION 확인
> EXP HELP=Y
COMPRESS import into one extent (Y)
III. LMT 와 DMT 요약
- LMT 는 조각나지 않는다. DMT 에서 쉽게 발생하는 종류의 단편화가 LMT 에서는 발생할 수 없다.
- 객체의 익스텐트 수는 관련이 없다. 객체가 몇 개의 익스텐트를 가질리에 관해서 염려할 필요가 없다.
- 과거에는 성능 때문이 아니라 DDL(예를 들면, DROP 또는 TRUNCATE) 성능 때문에 염려해야 했다.
- LMT의 성능이 DMT 보다 휠씬 우수하다. 재귀 SQL 의 오버헤드가 실질적으로 완전히 사라졌기 때문이다.
- LMT 에는 고민해야 할 사항이 적다. 최적의 INITIAL, NEXT, PCTINCREASE, 그리고 MAXEXTENTS 값이 얼마인지를 알아내려고 고민할 필요가 없다.
- 왜냐하면 이들은 전혀 관련이 없기 때문이다.
IV. FREELIST 구조도
"구루비 데이터베이스 스터디모임" 에서 2008년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3543
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.