DBMS 1
DA, SQL, DB보안 등 실무자를 위한 위한 DB기술 바이블!
Oracle Database 10g가 새로 제공하는 공간 재확보 기능, 온라인 테이블 재구성, 스토리지 증가량 예측 기능 등을 이용하여 세그먼트의 공간을 효율적으로 관리할 수 있습니다. 오래 전, Oracle Database의 경쟁 RDBMS 제품을 평가해 달라는 요청을 받은 일이 있습니다. 경쟁사의 프리젠테이션이 진행되는 동안, 청중들이 가장 감탄했던 기능이 바로 온라인 재구성(online reorganization) 기능이었습니다. 이 제품은 (오라클로 따지면 세그먼트에 해당하는) 영역의 데이타 블록을 온라인 상태에서 재배치하는 기능을 제공했습니다. 당시 오라클이 제공하던 Oracle9i Database는 이러한 기능을 제공하지 못했습니다. 이제 Oracle Database 10g는 온라인 상태에서 낭비되는 공간을 재확보하고 오브젝트를 보다 컴팩트(compact)한 형태로 관리할 수 있게 하는 기능을 추가적으로 제공합니다. 이 기능을 자세히 살펴 보기에 앞서, 이전에는 세그먼트 관리를 어떤 방법으로 수행했는지 설명하도록 하겠습니다. 그림 1과 같은 형태로 채워진 세그먼트를 가정해 봅시다. 작업이 수행되면서 그림 2와 같이 일부 로우(row)가 삭제되고 나면 낭비되는 공간이 생기게 됩니다. 낭비되는 공간은 (i) 남아있는 블록의 마지막 영역과 기존 테이블의 마지막 영역의 사이에서, 그리고 (ii) 로우가 부분적으로만 삭제된 블록 내부에서 발생합니다. 오라클이 이 영역에 대한 할당을 바로 해제하지 않고, 새로운 insert 작업 및 기존 로우의 확장에 대비한 예비 공간으로 활용합니다. 지금까지의 점유되었던 공간의 최고점을 High Water Mark(HWM)이라 부릅니다 (그림 2 참고). 하지만 이와 같은 접근 방식에는 두 가지 문제점이 존재합니다: Oracle9i와 그 이전 버전에서 공간을 재확보하려면, 테이블을 drop하고 다시 생성한 다음 데이타를 다시 로드하는 방식, 또는 ALTER TABLE MOVE 명령을 사용하여 테이블을 다른 테이블스페이스로 이동하는 방식을 사용해야 했습니다. 이 두 가지 방식은 모두 오프라인 상태에서 수행되어야 한다는 문제가 있습니다. 그 대안으로 online table reorganization 기능을 사용할 수도 있지만, 이를 위해서는 기존 테이블 크기의 두 배나 되는 공간이 필요했습니다. 10g의 경우 이러한 작업은 훨씬 간소화되었습니다. 10g의 Automatic Segment Space Management(ASSM)이 해당 테이블스페이스에 활성화되어 있는 경우, 세그먼트, 테이블, 인덱스를 shrink하고 free block을 재확보한 뒤 다른 용도로 할당하도록 데이터베이스로 반환됩니다. 그 자세한 방법을 알아보기로 합시다. 웹사이트를 통해 온라인으로 접수된 예약 정보를 보관하는 BOOKINGS라는 이름의 테이블이 존재한다고 가정해 봅시다. 확인 절차를 거친 예약은 BOOKINGS_HIST 테이블에 저장되고 해당 레코드는 BOOKINGS 테이블에서 삭제됩니다. 예약에서 확인까지 걸리는 시간은 고객에 따라 다릅니다. 이 경우 레코드 삭제로 인해 남은 공간이 충분하지 않은 경우에는 레코드가 테이블 HWM의 위쪽 영역에 insert 됩니다. 이제 낭비되는 공간을 재확보할 차례입니다. 먼저 해당 세그먼트에서 얼마나 많은 공간을 확보할 수 있는지 확인해야 합니다. 이 테이블은 ASSM이 적용된 테이블스페이스에 위치하고 있으므로, 아래와 같이 DBMS_SPACE 패키지의 SPACE_USAGE 프로시저를 사용해야 합니다: 실행 결과를 통해 4,148개의 블록이 75-100%의 free space(FS4)를 포함하고 있으며, 이를 제외하고는 free space가 전혀 존재하지 않음을 확인할 수 있습니다. Full block은 단 2개에 불과합니다. 따라서 4,148 개의 블록에서 공간을 확보할 수 있습니다. 이제 테이블에 row-movement가 활성화되어 있는지 점검해야 합니다. row-movement를 활성화 하기 위해서는 아래와 같이 입력합니다: 또는 Enterprise Manager 10g의 Administration 페이지에서 작업할 수도 있습니다. 또, 테이블의 모든 rowid 기반 트리거가 비활성화되어 있는지 점검해야 합니다. (로우가 이동되면서 rowid가 변경될 수 있기 때문입니다.) 마지막으로, 아래 명령을 사용하여 테이블의 기존 로우를 재구성합니다. 이 명령은 그림 3과 같은 형태가 되도록 블록 내부의 로우를 재배치하고, HWM 아래쪽 영역에 free block을 확보합니다. (하지만 HWM 자체는 변경되지 않습니다.) 작업이 완료된 후 공간 사용률에 변화가 있는지 확인해 봅시다. 앞에서 소개한 PL/SQL 코드를 사용하여 얼마나 많은 블록이 재구성되었는지 확인할 수 있습니다: 이제 매우 중요한 변화가 있었음을 확인할 수 있습니다. FS4 블록 (75-100%의 여유 공간을 갖는 블록)의 수가 4,148에서 0으로 바뀌었습니다. 또 FS3 블록(50-75%의 여유 공간을 갖는 블록)의 수가 0에서 1로 증가했습니다. 반면 HWM은 변경되지 않았으며, 전체 공간사용률에도 아무런 변화가 없었습니다. 사용중인 전체 공간은 아래와 같이 확인할 수 있습니다: 테이블이 점유중인 블록의 수(4,224)는 변경되지 않았으며, HWM도 기존 위치를 그대로 유지하고 있습니다. 다음과 같은 명령을 사용하면 HWM의 위치를 아래쪽을 이동하고 상위 영역을 재확보할 수 있습니다: 여기서 COMPACT 키워드가 사용되지 않은 점을 주목하시기 바랍니다. 위 구문을 실행하면 테이블이 사용되지 않은 블록을 반환하고 HWM을 재설정합니다. 아래와 같이 테이블에 할당된 공간을 확인하고 그 결과를 점검할 수 있습니다: 블록의 수가 4,224 개에서 8개로 줄었습니다. 그림 4에서 보여지는 것처럼 테이블 내에서 사용되지 않던 모든 공간이 반납되어 다른 세그먼트에서 활용할 수 있게 되었습니다. Shrink 작업은 온라인 상태에서 수행되며 사용자에게 아무런 영향을 미치지 않습니다. 테이블 인덱스에 대한 shrink 작업도 아래와 같이 수행할 수 있습니다: 온라인 shrink 명령은 낭비되는 공간을 재확보하고 HWM을 재설정하는 매우 강력한 기능입니다. 필자는 개인적으로 HWM 재설정 기능의 유용성을 높이 평가합니다. HWM을 재설정함으로써 풀 테이블 스캔의 성능을 향상시킬 수 있기 때문입니다. 온라인 shrink 작업을 수행하기 전에, 압축율을 비약적으로 향상시킬 수 있는 대상 세그먼트를 찾아내는 작업을 수행해야 할 수도 있습니다. dbms_space 패키지에 내장된 verify_shrink_candidate 함수를 사용하여 이 작업드는 대상 세그먼트가 1,300,000 바이트로 shrink 될 수 있는지 테스트합니다: 이 경우 변수 x의 값은 ‘F’로 반환되었습니다. 이는 테이블이 3,000 바이트로 shrink 될 수 없음을 의미합니다. 이번에는 특정 테이블, 또는 여러 개의 테이블에 대해 인덱스를 생성해야 하는 경우를 가정해 봅시다. 컬럼, uniqueness 등의 구조에 관련한 일반적인 고려사항을 제외하고 가장 중요한 작업을 들라면, 인덱스의 크기를 예상하는 일을 꼽을 수 있을 것입니다. 테이블스페이스의 공간이 새로운 인덱스를 수용할 수 있을 만큼 충분한지 확인해야 합니다. Oracle9i Database와 그 이전 버전의 경우, DBA들은 스프레드시트 또는 별개의 프로그램의 사용하여 인덱스의 크기를 예측하곤 했습니다. 10g에서는 새로 추가된 DBMS_SPACE 패키지를 이용해서 이 작업을 간단하게 마무리할 수 있습니다. 그렇다면 그 실제 활용 사례를 알아봅시다. BOOKINGS 테이블의 booking_id 컬럼과 cust_name 컬럼을 대상으로 하는 새로운 인덱스를 추가해야 합니다. 새로운 인덱스가 얼마나 많은 공간을 사용하게 될까요 아래와 같은 PL/SQL 스크립트를 실행하면 간단하게 확인할 수 있습니다: 실행 결과가 아래와 같습니다: 실행 결과는 아래와 같습니다: INITRANS 매개변수의 값을 높인 결과 Allocated Bytes가 훨씬 증가했음을 확인할 수 있습니다. 이와 같은 방법으로 인덱스가 사용하게 될 공간의 크기를 쉽게 예측할 수 있습니다. 하지만 두 가지 주의해야 할 점이 있습니다. 먼저, 이 프로세스는 “SEGMENT SPACE MANAGEMENT AUTO”가 활성화된 테이블스페이스에만 적용 가능합니다. 두 번째로, 패키지는 테이블 통계를 근거로 인덱스의 크기를 예측합니다. 따라서 테이블의 통계가 최신 상태를 유지하고 있는지 점검하는 것이 중요합니다. 가장 주의할 점은, 테이블에 통계가 존재하지 않는 경우 패키지가 에러를 발생시키는 대신 엉뚱한 계산 결과를 제시한다는 사실입니다. 이번에는 BOOKING_HIST 테이블이 평균 30,000의 row length를 가진 로우로 구성되어 있고 테이블의 PCTFREE 매개변수가 20으로 설정된 경우를 가정해 보겠습니다. PCT_FREE를 30으로 올리는 경우 테이블의 크기가 얼마나 증가하게 될까요 PCT_FREE가 10% 증가한 만큼, 테이블의 크기도 10% 증가하게 될까요 DBMS_SPACE 패키지의 CREATE_TABLE_COST 프로시저를 사용하면 간단하게 확인할 수 있습니다. 테이블의 크기를 예측하기 위한 코드가 아래와 같습니다: 실행 결과는 다음과 같습니다: 테이블의 PCT_FREE 매개변수를 30에서 20으로 아래와 같이 조정한 후 다시 실행합니다: 사용된 공간의 크기가 1,261,568에서 1,441,792로 증가했습니다. 이는 PCT_FREE 매개변수가 데이타 블록에 더 많은 여유 공간을 할당하기 대문입니다. 증가된 비율은 예상대로 10%가 아닌 14%로 확인되었습니다. 이처럼 DBMS_SPACE 패키지를 사용하여 PCT_FREE와 같은 매개변수를 변경하는 경우 또는 테이블을 다른 테이블스페이스로 이동하는 경우의 테이블 크기를 예측할 수 있습니다. Acme Hotel은 주말을 맞아 수요가 급증할 것을 예상하고 있습니다. DBA는 증가하 확인하려 합니다. 테이블의 크기가 얼마나 증가할지 어떻게 예측할 수 있을까요 10g가 제공하는 예측 기능의 정확성은 우리를 놀라게 하기에 충분합니다. 결과를 얻기 위해서는 아래와 같은 쿼리를 실행하기만 하면 됩니다. dbms_space.object_growth_trend() 함수는 PIPELINEd 포맷으로 레코드를 반환습니다. 출력된 결과가 아래와 같습니다: 출력된 결과는 시간(TIMEPOINT 컬럼)별로 BOOKINGS 테이블 크기의 증가 추이를 보여주고 있습니다. SPACE_ALLOC 컬럼은 테이블에 할당된 바이트 수를 의미하며 SPACE_USAGE 컬럼은 그 중 몇 바이트가 실제로 사용되고 있는지를 나타내고 있습니다. 이 정보는 Automatic Workload Repository(AWR, 본 연재 제 6 주 참고)에 의해 수집된 데이타를 기반으로 합니다. 위 데이타 중 실제로 데이타가 수집된 것은 2004년 3월 9일입니다 (QUALITY 컬럼의 값이 “GOOD”인 것으로 확인합니다). 따라서 해당 시점의 할당 공간 및 사용 공간의 수치는 정확하다고 판단할 수 있습니다. 반면, 이후 모든 데이타의 QUALITY 컬럼은 “PROJECTED”의 값을 가지며, 이는 데이타가 AWR에 의해 수집된 데이타를 근거로 추정된 것임을 의미합니다. 3월 9일 이전 데이타의 경우 QUALITY 컬럼의 값이 “INTERPOLATED”로 표시되어 있습니다. 이 데이타는 수집되거나 추정된 것이 아니며, 단순히 수집된 데이타의 패턴에 대한 interpolation을 통해 얻어진 것입니다. 이처럼 데이타가 수집되지 않은 과거 시점이 존재하는 경우, 그 값은 interpolation을 통해 계산됩니다. 세그먼트 단위의 관리 기능을 이용하여 세그먼트 내부의 공간에 대한 설정을 변경하고, 테이블 내부의 여유 공간을 재확보하거나 온라인 테이블 재구성 작업을 통해 성능을 향상시킬 수 있습니다. 10g의 새로운 기능은 테이블 재구성에 관련된 반복적인 업무를 절감하는 효과를 제공합니다. 특히 온라인 세그먼트에 대한 shrink 기능은, 내부 fragmentation을 제거하고 high water mark를 조정함으로써 풀 테이블 스캔의 성능을 극적으로 향상시키는 효과가 있습니다. Shrink 작업에 관한 자세한 정보는 Oracle Database SQL Reference의 관련 항목을 참고하시기 바랍니다. DBMS_PACKAGE는 PL/SQL Packages and Types Reference의 Chapter 88에서 설명되고 있습니다. 기술백서 The Self-Managing Database: Proactive Space & Schema Object Management는Oracle Database 10g의 공간 관리에 관련한 새로운 기능을 종합적으로 설명하고 있습니다. Oracle Database 10g의 온라인 데모 또한 OTN을 통해 제공되고 있습니다.세그먼트의 관리
세그먼트의 관리
열다섯번째. 세그먼트의 관리
기존의 관리 방법
10g의 세그먼트 관리 기능
declare l_fs1_bytes number;
l_fs2_bytes number;
l_fs3_bytes number;
l_fs4_bytes number;
l_fs1_blocks number;
l_fs2_blocks number;
l_fs3_blocks number;
l_fs4_blocks number;
l_full_bytes number;
l_full_blocks number;
l_unformatted_bytes number;
l_unformatted_blocks number;
begin
dbms_space.space_usage(
segment_owner => user,
segment_name => 'BOOKINGS',
segment_type => 'TABLE',
fs1_bytes => l_fs1_bytes,
fs1_blocks => l_fs1_blocks,
fs2_bytes => l_fs2_bytes,
fs2_blocks => l_fs2_blocks,
fs3_bytes => l_fs3_bytes,
fs3_blocks => l_fs3_blocks,
fs4_bytes => l_fs4_bytes,
fs4_blocks => l_fs4_blocks,
full_bytes => l_full_bytes,
full_blocks => l_full_blocks,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes => l_unformatted_bytes
);
dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks||' Bytes = '||l_fs1_bytes);
dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks||' Bytes = '||l_fs2_bytes);
dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' Bytes = '||l_fs3_bytes);
dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks||' Bytes = '||l_fs4_bytes);
dbms_output.put_line('Full Blocks = '||l_full_blocks||'
Bytes = '||l_full_bytes);
end;
/
The output is:
FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 0 Bytes = 0
FS3 Blocks = 0 Bytes = 0
FS4 Blocks = 4148 Bytes = 0
Full Blocks = 2 Bytes = 16384
alter table bookings enable row movement;alter table bookings shrink space compact;
FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 0 Bytes = 0
FS3 Blocks = 1 Bytes = 0
FS4 Blocks = 0 Bytes = 0
Full Blocks = 2 Bytes = 16384
SQL> select blocks from user_segments where segment_name = 'BOOKINGS'; BLOCKS
---------
4224
alter table bookings shrink space;SQL> select blocks from user_segments where segment_name = 'BOOKINGS'; BLOCKS
----------
8
alter table bookings shrink space cascade;Shrinking 작업 대상 세그먼트 찾기
begin
if (dbms_space.verify_shrink_candidate
('ARUP','BOOKINGS','TABLE',1300000)
) then
:x := 'T';
else
:x := 'F';
end if;
end;
/PL/SQL procedure successfully completed.SQL> print xX
--------------------------------
T
If you use a low number for the target shrinkage, say 3,000:
begin
if (dbms_space.verify_shrink_candidate
('ARUP','BOOKINGS','TABLE',30000)
) then
:x := 'T';
else
:x := 'F';
end if;
end;
인덱스 크기의 예측
declare
l_used_bytes number;
l_alloc_bytes number;
begin
dbms_space.create_index_cost (
ddl => 'create index in_bookings_hist_01 on bookings_hist '||
'(booking_id, cust_name) tablespace users',
used_bytes => l_used_bytes,
alloc_bytes => l_alloc_bytes
);
dbms_output.put_line ('Used Bytes = '||l_used_bytes);
dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
end;
/
Used Bytes = 7501128
Allocated Bytes = 12582912
인덱스의 크기를 증가시킬 수 있는 매개변수(INITRANS 등)를 사용한 경우를 가정해
봅시다.
declare
l_used_bytes number;
l_alloc_bytes number;
begin
dbms_space.create_index_cost (
ddl => 'create index in_bookings_hist_01 on bookings_hist '||
'(booking_id, cust_name) tablespace users initrans 10',
used_bytes => l_used_bytes,
alloc_bytes => l_alloc_bytes
);
dbms_output.put_line ('Used Bytes = '||l_used_bytes);
dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
end;
/
Used Bytes = 7501128
Allocated Bytes = 13631488
테이블 크기의 예측
declare
l_used_bytes number;
l_alloc_bytes number;
begin
dbms_space.create_table_cost (
tablespace_name => 'USERS',
avg_row_size => 30,
row_count => 30000,
pct_free => 20,
used_bytes => l_used_bytes,
alloc_bytes => l_alloc_bytes
);
dbms_output.put_line('Used: '||l_used_bytes);
dbms_output.put_line('Allocated: '||l_alloc_bytes);
end;
/
Used: 1261568
Allocated: 2097152
pct_free => 30we get the output:
Used: 1441792
Allocated: 2097152
세그먼트의 크기 예측
select * from
table(dbms_space.OBJECT_GROWTH_TREND
('ARUP','BOOKINGS','TABLE'));
TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY
------------------------------ ----------- ----------- ------------
05-MAR-04 08.51.24.421081 PM 8586959 39124992 INTERPOLATED
06-MAR-04 08.51.24.421081 PM 8586959 39124992 INTERPOLATED
07-MAR-04 08.51.24.421081 PM 8586959 39124992 INTERPOLATED
08-MAR-04 08.51.24.421081 PM 126190859 1033483971 INTERPOLATED
09-MAR-04 08.51.24.421081 PM 4517094 4587520 GOOD
10-MAR-04 08.51.24.421081 PM 127469413 1044292813 PROJECTED
11-MAR-04 08.51.24.421081 PM 128108689 1049697234 PROJECTED
12-MAR-04 08.51.24.421081 PM 128747966 1055101654 PROJECTED
13-MAR-04 08.51.24.421081 PM 129387243 1060506075 PROJECTED
14-MAR-04 08.51.24.421081 PM 130026520 1065910496 PROJECTED
결론