투씨에스지 기술 블로그

Reorg 방법 본문

Oracle/Admin

Reorg 방법

TOCSG 2014.02.18 11:40

1. Reorg 등장배경 & 세그먼트의 관리

 

Oracle Database 10g 새로 제공하는 공간 확보 기능, 온라인 테이블 재구성, 스토리지 증가량 예측 기능 등을 이용하요 세그먼트 공간을 효율적으로 관리할 있습니다.

 

오래 , Oracle Database 경쟁 RDBMS 제품을 평가해 달라는 요청을 받은 일이 있습니다. 경쟁사의 프리젠테이션이 진행되는 동안, 청중들이 가장 감탄했던 기능이 바로 온라인 재구성(online reorganization) 기능이었습니다. 제품은 (오라클로 따지면 세그먼트에 해당하는) 영역의 데이타 블록을 온라인 상태에서 재배치하는 기능을 제공했습니다.

당시 오라클이 제공하던 Oracle9i Database 이러한 기능을 제공하지 못했습니다. 이제 Oracle Database 10g 온라인 상태에서 낭비되는 공간을 확보하고 오브젝트를 보다 컴팩트(compact) 형태로 관리할 있게 하는 기능을 추가적으로 제공합니다.

기능을 자세히 살펴 보기에 앞서, 이전에는 세그먼트 관리를 어떤 방법으로 수행했는지 설명하도록 하겠습니다.

 

 

 

    1.1      기존의 관리방법

 

그림 1 같은 형태로 채워진 세그먼트를 가정해 봅시다. 작업이 수행되면서 그림 2 같이 일부 로우(row) 삭제되고 나면 낭비되는 공간이 생기게 됩니다. 낭비되는 공간은 (i) 남아있는 블록의 마지막 영역과 기존 테이블의 마지막 영역의 사이에서, 그리고 (ii) 로우가 부분적으로만 삭제된 블록 내부에서 발생합니다.

 

 

오라클이 영역에 대한 할당을 바로 해제하지 않고, 새로운 insert 작업 기존 로우의 확장에 대비한 예비 공간으로 활용합니다.

지금까지의 점유되었던 공간의 최고점을 High Water Mark(HWM)이라 부릅니다.

(그림 2 참고).

 

하지만 이와 같은 접근 방식에는 가지 문제점이 존재합니다:

- 사용자의 쿼리가 테이블 스캔을 발생시키는 경우, 오라클은 (설사 관련된 데이타가 전혀 존재하지 않는 경우라 하더라도) HWM 아래쪽의 모든 영역을 스캔합니다. 이로 인해 테이블 스캔에 소요되는 시간이 길어질 있습니다.

- 로우가 direct path 정보와 함께 insert 되는 경우 (예를 들어 APPEND 힌트를 사용한 Insert, 또는 SQL*Loader direct path 통해 insert 되는 경우) 새로 추가 되는 데이타 블록은 HWM 위쪽 영역에 추가됩니다. 따라서 HWM 아래쪽 영역은 낭비된 채로 남게 됩니다.

 

 

Oracle9i 이전 버전에서 공간을 재확보하려면, 테이블을 drop하고 다시 생성한 다음 데이터를 다시 로드하는 방식, 또는 ALTER TABLE MOVE 명령을 사용하여 테이블을 다른 테이블스페이스로 이동하는 방식을 사용해야 했습니다. 가지 방식은 모두 오프라인 상태에서 수행되어야 한다는 문제가 있습니다. 대안으로 online table reorganization 기능을 사용할 수도 있지만, 이를 위해서는 기존 테이블 크기의 배나 되는 공간이 필요했습니다.

 

10g 경우 이러한 작업은 훨씬 간소화되었습니다. 10g Automatic Segment Space Management(ASSM) 해당 테이블스페이스에 활성화되어 있는 경우, 세그먼트, 테이블, 인덱스를 shrink하고 free block 확보한 다른 용도로 할당하도록 데이터베이스로 반환됩니다. 자세한 방법을 마지막 장에서 설명해 드리겠습니다.

 

 

2. Reorg ?

 

대상 Table Data Physical하게 재편성하여 DML 작업으로 인한 Fragmentation 제거하는 작업 입니다.

테이블이 여러 수정되어 데이터가 분할되고 액세스 성능이 현저하게 느려지게 되면 REORG TABLE 명령이 최우선적인 튜닝 요소로 지적 됩니다.

Reorg 명령은 조각난 데이터 인덱스를 조각나지 않은 실제로 연속된 페이지로 빌드 함으로써, 복잡하고 불필요하게 공간을 차지하고 있는 오브젝트(테이블 인덱스)들을 최적화 있으며, 이로 인해 액세스 되는 블록도 감소시켜 SQL 쿼리의 성능 향상도 어느 정도 있습니다.

 

 

3. Reorg 필요성_성능   

 

- 운영중인 DB 계속적인 Insert / Update / Delete 발생으로 인한 Fragmentation 발생하면, Chained Row 발생하여 Disk I/O 증가되며, 결과적으로 서비스 성능이 저하됨

 

- 대량의 Data 삭제 실제 Table Data 감소하였으나 Data High Water Mark 기존 Table Size 같으므로 Full Table Scan 등의 작업 성능이 저하됨

 

 

 

4. Reorg 필요성_DB space 관리

 

- Fragmentation으로 인하여 사용하지 못하는 Disk Space 발생하여 Storage 사용율이 낮아짐

 

- 효율적인 Storage 관리를 위해 Data 증가량을 예측하여 주기적인 관리가 필요함

 

1.   요구되는 전체 I/O 최소화

2.    낭비된 디스크 공간의 복구

3.    과다하게 확장된 스페이스의 교정 작업

4.    복잡하고 불필요하게 분산된 데이터베이스 오브젝트를 효율적으로 물리적인 디스크 공간으로 정렬

5.   데이터와 인덱스 클러스터 비율을 높임으로써 인덱스 조회 속도 향상

 

 

5. Reorg 수행 방법

 

- Fragmentation으로 인하여 사용하지 못하는 Disk Space 발생하여 Storage 사용율이 낮아짐

 - 효율적인 Storage 관리를 위해 Data 증가량을 예측하여 주기적인 관리가 필요함

 

 

5.1 exp/imp

 

Export Import 유틸리티는 DB Open 상태에서만 사용 가능합니다.
그리고 export import OS 기종이나 오라클 버전이 달라도 데이터를 이동시킬수 있기 때문에 이식성이 아주 좋습니다
.
export import 데이터가 많을 경우 물리적인 백업보다 시간이 많이 걸릴 있습니다
.
실제로 export 단점 하나가 시간이 많이 소요된다는 것입니다.

 

 

5.1.1 EXPORT

 

우리가 full export 수행하면 수많은 오브젝트를 export 하게 되는데 아래의 순서로 해당 object export 합니다.

 

1. Tablespaces

2. Profiles

3. Users

4. Roles

5. System Privilege Grants

6. Role Grants

7. Default Roles

8. Tablespace Quotas

9. Resource Costs

10. Rollback Segments

11. Database Links

12. Sequences (includes Grants)

13. Snapshots (includes grants, auiting)

14. Snapshot logs

15. Job Queues

16. Refresh Groups

17. Cluster Definitions

18. Tables(includes grants, column grants, comments, indexes, constraints, auditing)

19. Referential Integrity

20. POSTTABLES actions

21. Synonyms

22. Views

23. Stored Procedures

24. Triggers

25. Default and System Auditing

 

- Conventional Path export Direct Path export
Exports
conventional path export direct path export 구분되며 기본값은 conventional Path export입니다.

그림에서 있듯이 conventional path export export 명령어가 수행되면 Export client 메모리에 evaluation buffer라는 곳을 만들고 DB buffer cache 있는 데이터를 evaluation buffer 가져간 이곳이 차면 다시 디스크에 파일로 저장합니다.
이렇게 하는 이유는 direct path load 원본 테이블에 여러 process 동시에 접근을 못하는 단점이 있는데 conventional path load 부분을 해결할 있습니다
.
외에도 동시성 문제 등에서 direct path 문제가 있어서 오라클은 기본적으로 여러 사람이 동시에 사용하는 DB이기 때문에 conventional path 모드로 작동하게 되어 있습니다.

그러나 conventional path direct path 비해 과정이 다소 복잡하기 때문에 속도가 저하 됩니다.

 

-      conventional Path Full export 받기(기본모드)
# time exp system/admin full=y log=/export/home/oracle/data/backup/dmp/full_log.log file=/export/home/oracle/data/backup/dmp/full_test01.dmp


 Direct Path Database Full export 받기
# time exp system/admin full=y log=/export/home/oracle/data/backup/dmp/full_log.log file=/export/home/oracle/data/backup/dmp/full_test02.dmp direct=y

 

 

 

5.1.2 IMPORT

 

Import export 수행해서 만든 파일을 다시 데이터베이스로 넣는 작업을 수행합니다.
그래서 export 거의 옵션이 비슷합니다
.


- import 수행
Import
작업은 DDL DML 수행하는 것이므로 Redo log undo segment 사용하게 됩니다.
따라서 대량의 데이터를 import 때는 반드신 undo tablespace 준비해서 작업하여야 합니다
.
만약 undo tablespace 용량이 부족할 경우에는 마지막에 에러가 나면서 전부 rollback 수도 있습니다
.
이런 위험을 줄이려면 import commit=y 변경하고 import 수행하면 array 단위로 commit 수행하기 때문에 전체가 rollback되는 현상을 막을 있습니다
.
또한 DBA export 받은 파일은 반드시 DBA import 해야 합니다.

 

- 특정 사용자의 데이터만 import 수행
# imp system/admin file=/export/home/oracle/data/backup/dmp/scott.dmp fromuser=scott tables=emp ignore=y

 

 

 

5.2  Alter table move

 

 

Analyze Table Table_Name compute statistics à통계 정보를 생성

 

Select num_rows, chain_cnt From dba_tables Where Table_Name = Table_Name  à chain_cnt값이 경우 table 구성 해준다.

 

Create Tablespace Table_Name datafile ‘경로/파일명’ Size nM; à Tablespace 생성

 

Alter Table Table_Name move Tablespace [이동할 Tablespace] à Table 이동

 

Alter Table Table_Name move Tablespace [원래 Tablespacce]

 

Select table_name, index_name, status from dba_indexes à 인덱스 상태확인

 

Alter index index_Name rebuild à 인덱스 rebuild

 

 

Index rebuild 이유

Table Index 컬럼에 update 작은 delete 발생 Index data 지워지지 않으며 오히려 오라클 내부적으로 현재 인덱스 데이터는 삭제된 것이다라는 마킹만 해놓는다.

Update시에도 Update전의 Index데이터는 지워졌다 마킹해 놓고 Update Index 데이터를 Insert하는 방식으로 동작한다. 삭제되었다 마킹된 부분은 공간을 재활용할 필요가 있을 경우 오라클에 의해 정리된다.

인덱스 컬럼에 대향의 Update, Delete 빈번히 발생할 경우 인덱스 사이즈에 비해 인덱스 세그먼크의 덩치는 엄청 커지게 된다. 이러한 현상에 의해 Index B-Tree 구조의 밸런스가 심하게 깨지게 되며 단편화가 발생해서 일반적으로 Index rebuild 해줍니다.

 

 

 

5.3  create table as select

 

Create Table Table_Name as Select * From Table_Name; à Table 재생성

Create Index Index_Name on Table_Name(Column_Name); à Index 재생성

Alter index index_Name rebuild à 인덱스 rebuild

 

 

 

6. SHRINK 설명

 

기존에는 테이블을 새로 작성(reorg) 작업을 통해 커진 테이블 스페이스 크기를 복구 가능하였으나, 10g 부터 Shrink 기능을 이용하여 손쉽게 테이블을 줄일 있게 되었습니다.

Shrink 기능은 HWM(High Water Mark) 줄일 있는 기능입니다.

ASSM(Automatic Segment Space Management) 테이블 스페이스만 가능

 

Ex) 테이블 조회 방법      

            

SELECTOBJ.OWNER

, OBJ.TABLE_NAME, OBJ.TABLESPACE_NAME

,(CASE WHEN NVL(IDX.CNT,0)<1 THEN 'YES' ELSE 'NO' END)SHRINKABLE

FROM DBA_TABLES OBJ

,(SELECT TABLE_NAME, COUNT(ROWNUM)CNT

FROM DBA_INDEXES

WHERE INDEX_TYPE LIKE 'FUN%'

GROUP BY TABLE_NAME) IDX

WHEREOBJ.TABLE_NAME=IDX.TABLE_NAME(+)

ANDOBJ.TABLESPACE_NAME=:TABLESPACE_NAME

;

쿼리를통해(테이블스페이스명을인자값으로)확인가능하다.

 

 

6.1 SHRINK 제약사항

 

- UNDO SEGMENTS

- TEMPORARY SEGMENTS

- CLUSTERED TABLE

- TABLE WITH A COLUMN OF DATATYPE LONG

- LOB INDEXES

- IOT MAPPING TABLES AND IOT OVERFLOW SEGMENTS

- TABLES WITH MVIEWS WITH ON COMMIT

- TABLES WITH MVIEWS ARE BASED ON ROWIDS

- Function Base Index

- nologging, parallel 수행 안됨

- 대용량 테이블일 경우 속도가 느리다.

 

 

 

6.2 SHRINK 수행 방법

 

웹사이트를 통해 온라인으로 접수된 예약 정보를 보관하는 BOOKINGS라는 이름의 테이블이 존재한다고 가정해 봅시다. 확인 절차를 거친 예약은 BOOKINGS_HIST 테이블에 저장되고 해당 레코드는 BOOKINGS 테이블에서 삭제됩니다. 예약에서 확인까지 걸리는 시간은 고객에 따라 다릅니다. 경우 레코드 삭제로 인해 남은 공간이 충분하지 않은 경우에는 레코드가 테이블 HWM 위쪽 영역에 insert 됩니다.

 

 

6.2.1 낭비되는 공간 재 확보

 

낭비되는 공간을 확보할 차례입니다. 먼저 해당 세그먼트에서 얼마나 많은 공간을 확보할 있는지 확인해야 합니다.

테이블은 ASSM 적용된 테이블스페이스에 위치하고 있으므로, 아래와 같이 DBMS_SPACE 패키지의 SPACE_USAGE 프로시저를 사용해야 합니다:

 

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       => 'TABLE_NAME',

      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;

 

 

 

6.2.2 row-movement 활성화 점검

 

이제 테이블에 row-movement 활성화되어 있는지 점검해야 합니다. row-movement 활성화 하기 위해서는 아래와 같이 입력합니다:

 

Alter Table Table_Name Enable Row Movement; 

 

 

6.2.3 SHRINK 형태

 

Shrink 기능을 수행할수 있으며, 다음 3가지 형태로 가능하다.

 

- Alter Table Table_Name Shrink Space;          à 테이블 공간만 Tablespace 환원

- Alter Table Table_Name Shrink Space Cascade;  à 모든 관련 OBJECT까지

                                            Tablespace 공간 환원

- Alter Table Table_Name Shrink Space compact;     à 행들만 이동시킴

 

 

 

 

 

6.2.4 row-movement 상태 변경

 

Alter Table Table_Name Disable Row Movement; 

--> 단편화 완료 row movement disable 변경

 

 

신고

'Oracle > Admin' 카테고리의 다른 글

Tablespace 관리  (0) 2014.03.17
DB 계정 관리  (0) 2014.03.17
Reorg 방법  (2) 2014.02.18
통계정보 관리  (0) 2014.02.18
백업 방안  (0) 2014.02.17
Listener 관리방안  (0) 2014.02.17
2 Comments
  • MUSICOVERY 2015.01.01 00:17 신고 하나의 예시를 만들기 위해 하나의 테이블을 생성하고 1000개의 데이터를 집어넣었는데요. dba_tables에서 chain_cnt 갯수를 인위적으로 늘리기 위해 데이터를 중간중간에 삭제를 했어요. 그런데 chain_cnt가 안늘어납니다. 어떻게 하면 늘어나나요?
  • Tocsg 2015.02.10 11:34 신고 목록중에 Row Chaining & Row Migration 의 내용을 보시면 chaining count 가 늘어나는 것을 Test 하실 수 있습니다.
댓글쓰기 폼
Prev 1 ... 30 31 32 33 34 35 36 37 38 ... 54 Next