미래를 꿈꾸고 성공을 희망하며

Shrink를 이용하기 전에, 검토해야 할것과 Shrink의 기능이 왜 생겨났는지를 알아야 한다.

우선, 여러분이 Delete 를 해서 Table에 수천만건이 있다가 1건으로 줄이더라도 실제 데이터를 검색하기 위해서는 이미 할당된 공간을 full scan하거나, High Warter Mark 까지 체크하는 것을 이해해야 한다.

그리고, Archive log에 변경이력이 쌓이는 걸 알아야 한다. Shrink는 데이터의 저장된 구조를 변경하는 것이다.
(블록에 적재된 데이터의 량이 들쑥날쑥하고, row-chaining 등이 많다면, 동일한 데이터라도 table을 읽을 때 시간이 많이 걸린다).

그래서 triger로 인한 추가적인 DB변경은 발생하지 않지만, 실제 row를 delete/insert 작업을 통해 재정렬하는 것이기에 Archive log 파일이 대량으로 생성될 수 있다. 그래서 데이터가 많은 테이블을 Shrink할 경우에는 Archive log 의 디렉토리를 확인하여 백업이 된 log 파일은 삭제할 것을 권고한다.

제일 좋은 것은, 운영 중, 불필요한 데이터를 이관하거나 삭제하는 것이다.
(개인정보보호법에 따라, 불필요한 정보는 즉시(5일이내) 삭제해야 하며, Log 테이블은 partition되어 있고, 매일 증가분에 대해 이관하였다면, drop partition을 통해 정리하는 것이 좋다.)

Shrink의 자세한 이론과 테스트는 아래의 내용을 참고하기 바란다.

어떤 세그먼트를 위해 공간이 크게 할당된 경우 High Water Mark 이후의 공간은 사용되지 않은 채로 남아 있게 될 수 있다. 또한 High Water Mark 이전의 영역에도 누적된 delete 연산의 결과로 빈 공간이 존재할 수 있다. 이 경우 다음과 같은 문제점이 있음을 알 수 있다:

• 데이터가 보다 많은 블록들에 걸쳐 흩어져 있으므로 스캔시 보다 많은 I/O가 필요하다.
• 내부 단편화로 인해 row-chaining/row migration이 일어날 가능성이 높다.
• 전체적인 공간 낭비가 발생한다.

Oracle9i에서 이러한 문제점들을 해결하는 방안은 해당 오브젝트를 이동하거나 재생성하는 것이었다. Oracle Database 10g에서는 이 문제를 위해 Segment Shrink 기능을 추가적으로 제공한다.

한편 Oracle Database 10g의 Advisory framework는 Segment의 공간 사용에 관하여 각종 통계 보고 및 Segment Shrink를 포함한 권고안 제시를 위해 Segment Advisor를 제공한다.

Segment Shrink의 원리

Segment Shrink는 다음의 두 단계에 걸쳐 이루어진다.

• 데이터의 compact
  • 다만, 실제 데이터가 바뀌는 것은 아니기 때문에 DML 트리거가 정의되어 있다 하더라도 발생하지 않는다.
  • Segment Shrink가 row-chaining을 완전히 제거하는 것을 보장하는 것은 아니다.
  • 보통의 DML과 같은 방식으로 이루어지므로 인덱스 dependency는 자동으로 처리된다. 다만 IOT에 대한 2차 인덱스는 shrink 직후 재생성하는 것을 권장한다.
• 이 단계는 HWM 아래 영역에 있는 hole들을 채우는 작업이다. 이는 내부적으로 INSERT/DELETE 연산에 의해 이루어진다: HWM에 가까이 있는 행을 안쪽의 빈 공간을 찾아 INSERT하고, 그것이 끝나면 해당 행을 DELETE함으로써 행을 옮기는 것이다.
• HWM의 push down
• 1번 단계에 의해 데이터는 HWM에서 먼 쪽에 촘촘히 채워져 있을 것이고, 반대로 HWM에서 가까운 쪽의 공간은 비어 있는 상태가 된다. 이제 HWM를 내리고, 새롭게 설정된 HWM 이후의 모든 공간을 해당 테이블스페이스에 반납함으로써 segment shrink가 완료된다.

Segment Shrink는 online이자 in-place 연산이다. 1번 단계에서는 통상의 row-level lock이 필요할 뿐, 다른 세션의 DML을 불허하는 것은 아니기 때문에 오브젝트의 가용성은 제한되지 않는다. 또한 2번 단계에서 HWM를 내리는 데 필요한 exclusive 테이블 lock은 매우 짧은 시간 동안만 필요하다. 한편 Segment Shrink가 Oracle9i의 online redefinition과 다른 점은 별도의 임시 공간이 없이 바로 그 오브젝트에 대해서 (in-place) 수행될 수 있다는 점이다.

Segment Shrink의 조건

1. 오직 Automatic Segment Space Management(ASSM)를 사용하는 테이블스페이스 내의 세그먼트만이 shrink될 수 있다. 데이터의 compaction 정보는 세그먼트 헤더의 bitmap block을 이용하기 때문이다. 다만 다음의 세그먼트들은 ASSM 테이블스페이스 내에 있더라도 shrink될 수 없는 제한이 있다:

• 임시 세그먼트 및 Undo 세그먼트
• 클러스터 내의 테이블
• LONG 컬럼을 가진 테이블
• ROWID 기반의 materialized view가 정의된 테이블
• LOB 인덱스
• IOT mapping 테이블
• IOT overflow 세그먼트
• 공유된 LOB 세그먼트

2. 데이터 compaction 단계에서 rowid가 변경되므로 해당 세그먼트에 대해 미리 ROW MOVEMENT가 enable되어 있어야 한다.

SHRINK SPACE 명령

테이블 세그먼트에 대한 segment shrink 명령은 다음과 같다:

• 물론 테이블 뿐만 아니라 segment shrink를 지원하는 모든 오브젝트들에 대해 위와 같은 명령을 사용할 수 있다.
• COMPACT 옵션이 지정된 경우 segment shrink는 1단계인 데이터 compaction까지만 수행되게 된다.
• CASCADE 옵션이 지정된 경우 segment shrink는 dependent한 오브젝트들에 대해서도 자동으로 수행되게 된다. 예를 들면 테이블을 shrink하면서, 그 테이블에 대해 정의된 인덱스들 또한 자동으로 동시에 shrink할 수 있다.

Segment Advisor

Segment Advisor는 Segment Shrink를 포함한 Segment 관리를 위해 EM에서 제공하는 GUI이다. 이는 EM의 테이블스페이스 페이지, 스키마 오브젝트 페이지, 그리고 Advisor Central 페이지 중 하나로부터 access할 수 있다.

Segment Advisor의 기능은 다음과 같으며 필요한 자료들은 AWR로부터 제공받는다.

• Segment Shrink
• Segment Advisor는 어떤 오브젝트가 shrink 연산을 위한 좋은 후보인지를 권고한다. 이러한 결정은 그 세그먼트에 할당되었으나 사용되지 않는 공간의 크기, 세그먼트의 공간 사용 경향 등에 근거한다.
• Growth Trend Report
• 세그먼트의 공간 사용 경향을 보고함으로써 DBA의 용량 계획을 돕는다. 이는 나아가 Proactive Tablespace Monitoring 기능과 연동된다.
• New Segment Resource Estimation
• EM의 새로운 세그먼트 생성 페이지에는 "크기 예측" 버튼이 있어 이를 이용하면 필요한 디스크 크기를 예측할 수 있다. 이는 세그먼트의 구조, 예상되는 행의 수 등에 근거한다.

테스트

테스트 1: Segment Shrink

1. 테스트를 위해 테이블을 하나 생성하고 데이터를 입력해둔다.

SQL> conn scott/tiger
연결되었습니다.

SQL> create table employees as select * from hr.employees;

테이블이 생성되었습니다.

SQL> insert into employees select * from employees;

107 개의 행이 만들어졌습니다.

SQL> /

. . .

SQL> /

27392 개의 행이 만들어졌습니다.

SQL> commit;

커밋이 완료되었습니다.

2. 이제 employees 테이블이 얼마만큼의 공간을 차지하고 있는지를 확인해보자.

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks from employees;
BLOCKS

-

556

SQL> select blocks, extents from user_segments where segment_name = ‘EMPLOYEES’;

BLOCKS EXTENTS

- -

640 20

첫번째 조회에서 얻는 blocks는 실제로 사용하고 있는 block의 개수를 나타낸다. 반면에 두번째 조회에서 얻은 blocks는 할당된 block의 개수, 즉 HWM를 나타낸다.

3. 이제 이 테이블에 "hole"을 만들자.

SQL> delete employees where department_id = 50;
23040 행이 삭제되었습니다.

SQL> commit;

커밋이 완료되었습니다.

4. 이제 HWM 아래에 많은 빈 공간이 생긴 employee 테이블을 shrink해보자.

SQL> alter table employees shrink space compact;
alter table employees shrink space compact

*

1행에 오류:

ORA-10636: ROW MOVEMENT is not enabled

5. ROW MOVEMENT는 default로 disable되어 있다. 이것을 수정하고 재시도 해보자.

6. Shrink space 명령에 compact 옵션을 주었다. 이는 데이터의 compaction만 일으키고, HWM는 그대로 두는 것을 의미한다. 이는 다음의 조회로 확인해볼 수 있다.

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks from employees;
BLOCKS

-

304

SQL> select blocks, extents from user_segments where segment_name = ‘EMPLOYEES’;

BLOCKS EXTENTS

- -

640 20

실제 사용하는 block의 개수가 556개에서 304개로 늘었다. 다만 HWM는 640으로 변한 게 없다.

7. 이제 HWM까지 조정한 후 다시 결과를 확인해보자.

SQL> alter table employees shrink space;
테이블이 변경되었습니다.

SQL> select blocks, extents from user_segments where segment_name = ‘EMPLOYEES’;

BLOCKS EXTENTS

- -

320 18

HWM가 640 에서 320 으로 내려갔음을 확인할 수 있다.

테스트 2: Segment Advisor

1. 테스트를 위해 테이블스페이스 하나를 새로 생성한다.

SQL> create tablespace segment_test
2 datafile ‘D:\SW\oracle\oradata\cook\segment_test.dbf’ size 5m

3 logging

4 extent management local

5 segment space management auto;

테이블 영역이 생성되었습니다.

2. 테이블스페이스 임계값을 확인해보자.

SQL> select warning_value,
2 critical_value,

3 object_name

4 from dba_thresholds

5 where metrics_name = ‘Tablespace Space Usage’;

WARNING_VALUE CRITICAL_VALUE OBJECT_NAME

– – –

85 97

기본값을 그대로 쓰고 있음을 알 수 있다. 이제 1번에서 생성한 테이블스페이스에 대해 warning 및 critical value를 각각 50, 60으로 수정하자.

SQL> exec dbms_server_alert.set_threshold(9000, dbms_server_alert.OPERATOR_GT, 50, dbms_server_alert.OPERATOR_GT, 60, 1, 1, null, dbms_server_alert.OBJECT_TYPE_TABLESPACE, ‘SEGMENT_TEST’);
PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select warning_value,

2 critical_value,

3 object_name

4 from dba_thresholds

5 where metrics_name = ‘Tablespace Space Usage’;

WARNING_VALUE CRITICAL_VALUE OBJECT_NAME

– – –

50 60 SEGMENT_TEST

85 97

3. 이제 이 테이블스페이스 내에 테이블을 하나 생성하여 데이터를 충분히 입력한다. 그리고 DELETE 연산을 통해 hole을 만든다.

SQL> conn scott/tiger
연결되었습니다.

SQL>

SQL> create table employees1 tablespace segment_test as select * from hr.employees;

테이블이 생성되었습니다.

SQL> create table employees2 tablespace segment_test as select * from hr.employees;

테이블이 생성되었습니다.

SQL> create table employees3 tablespace segment_test as select * from hr.employees;

테이블이 생성되었습니다.

SQL> create table employees4 tablespace segment_test as select * from hr.employees;

테이블이 생성되었습니다.

SQL> create table employees5 tablespace segment_test as select * from hr.employees;

테이블이 생성되었습니다.

SQL> begin

2 for i in 1..5 loop

3 insert into employees1 select * from employees1;

4 insert into employees2 select * from employees2;

5 insert into employees3 select * from employees3;

6 insert into employees4 select * from employees4;

7 insert into employees5 select * from employees5;

8

9 commit;

10 end loop;

11 end;

12 /

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> insert into employees1 select * from employees1;

3424 개의 행이 만들어졌습니다.

SQL> insert into employees2 select * from employees2;

3424 개의 행이 만들어졌습니다.

SQL> insert into employees3 select * from employees3;

3424 개의 행이 만들어졌습니다.

SQL> commit;

커밋이 완료되었습니다.

4. 많은 행이 입력되었으므로 잠시 후 다음과 같은 alert가 생성되었음을 확인할 수 있다.

SQL> select reason from dba_outstanding_alerts;
REASON



Tablespace [SEGMENT_TEST] is [52 percent] full

5. 여기서 미리 3번에서 다루었던 테이블들 중 일부에 대해 ROW MOVEMENT를 enable시켜 둔다.

SQL> alter table employees1 enable row movement;
테이블이 변경되었습니다.

SQL> alter table employees2 enable row movement;

테이블이 변경되었습니다.

SQL> alter table employees3 enable row movement;

테이블이 변경되었습니다.

6. 이제 EM의 Advisor Central 링크를 이용하여 Segment Advisor에 접속하자.

대상으로 테이블스페이스를 선택하고, 분석은 Comprehensive 모드를 선택한 후 "계속"을 클릭한다.

Add를 클릭한다.

테스트 대상인 segment_test를 선택하고 확인을 클릭한다.

다음을 클릭한다.

다음을 클릭한다.

다음을 클릭한다.

Submit을 클릭한다.

잠시 후 Advisor Central 페이지에서 다음과 같이 방금 Submit했던 분석 작업이 완료되었음을 확인할 수 있다.

결과 보기를 클릭하면 Segment Advisor가 Employees1, 2, 3 테이블에 대해 Segment Shrink를 할 것을 권고하는 내용을 확인할 수 있다.

7. 이제 employees1, 2, 3 세개 테이블에 대해 shrink를 수행하자.

SQL> alter table employees1 shrink space;
테이블이
변경되었습니다.

SQL> alter table employees2 shrink space;

테이블이
변경되었습니다.

SQL> alter table employees3 shrink space;

테이블이
변경되었습니다.

8. 잠시 후 아까 발생하였던 alert가 해제되었음을 알 수 있다.

SQL> select reason from dba_outstanding_alerts;
선택된
레코드가
없습니다.

Oracle10g 이후 버전에서 Segment Shrink 기능을 사용하면 효율적인 방법으로 테이블스페이스 내의 공간 낭비를 줄이고 그에 따라 성능을 높일 수가 있다.

또한 이를 Segment Advisor가 제공하는 GUI를 통해 사용할 수 있으므로 더욱 간편하다.