reorg
=> DB의 최적화로 성능향상을 위한 것.
1. 단편화 해소를 통한 성능 향상
- 인덱스 rebuild
ex)
alter index scott.ix_tbx_01 rebuild tablespace ts_work;
*
인덱스 리빌딩시 nologging 전환. 리빌딩 후 logging
alter index scott.ix_tbx_01 rebuild partition p04 paralel 5 nologging;
alter index scott.ix_tab_01 logging;
- 테이블 move
ex)
alter table scott_tab_01 move tablespace ts_work;
10g 부터 제공하는 공간 재 확보 기능,
온라인 테이블 재구성,
스토리지 증가량 예측 기능 등....
=> 세그먼트 효율적으로 관리 할 수 있도록...
10g 이전 관리방법.
처음 만들어진 세그먼트는 아래와 같이 차곡차곡 쌓임.
┌──┬──┬──┬──┬──┬──┬──┐
│헤더│■■│■■│■■│■■│■■│Free│
│ │■■│■■│■■│■■│■■│ │
│블록│■■│■■│■■│■■│■■│블록│
└──┴──┴──┴──┴──┴──┴──┘
그러나 작업이 수행되면서 일부 로우가 삭제되고 나면 낭비되는 공간이 생김. 아래 참조
High Water Mark
∇
┌──┬──┬──┬──┬──┬──┬──┐
│헤더│■■│■□│□■│Free│Free │Free│
│ │■■│□■│□□│ │ │ │
│블록│■■│■□│□■│블록│블록│블록│
└──┴──┴──┴──┴──┴──┴──┘
오라클은 위와 같이 빈 영역에 대한 할당을 바로 해제하지 않고 새로운 insert 작업 및 기존 로우의 확장에 대비해
예비 공간으로활용. 이부분을 High Water Mark(고순위)
=> 이로인해 문제점 발생
1) 사용자 쿼리가 테이블 풀 스캔일 경우.
- 오라클은 데이터가 전여 존재하지 않더라도 HWM 아래쪽 모든 영역을 스캔.
=> 테이블 풀 스캔으로 인해 소요되는 시간 길어짐.
2) 로우가 direct path 정보와 함께 insert 되는 경우.
- APPEND 힌트를 사용한 insert, SQL *Loader direct path를 통해 insert 경우
- 새로 추가 되는 테이터 블록은 HWM의 위쪽 영역에 추가됨
- 빈 공간이 낭비
Reorg 란.
- 대상 table의 Data를 Physical 하게 재편성 하여 DML 작업으로 인한 Fragmentation 을 제거하는 작업.
- Reorg 명령은 조각난 데이터 및 인덱스를 조각나지 않은 실제로 연속된 페이지로 재 빌드함.
-> 액세스 되는 블록도 감소시켜 SQL쿼리의 성능 향상도 볼 수 있음.
1) 성능
- Insert/Update/Delete 발생으로 인한 Fragmentation 발생 -> ROW Chained 발생 -> Disk I/O 증가 -> 서비스 성능 저하
- 대량의 DATA 삭제 후 실제 Table Data 는 감소하였지만 DATA HWM가 기존 Table Size 와 같음 -> Full Table Scan 등 작업 성능 저하
2) DB space 관리
- 사용하지 못하는 Disk space 발생하여 스토리지 사용율 낮아짐.
- 효율적인 스토리지 관리를 위해 Data 증가량 예측하여 주기적인 관리 필요
(1) 요구되는 전체 I/O 최소화
(2) 낭비된 디스크 공간 복구
(3) 과다하게 확장된 스페이스 교정 작업
(4) 복잡, 불필요하게 분산된 데이터베이스 오브젝트를 효율적으로 물리적인 디스크 공간에 정렬
(5) 데이터, 인덱스, 클러스터 비율을 높임으로써 인덱스 조회 속도 향상
3) 수행 방법
(1) exp/imp
* [참고] export 시 수행되는 object 순서
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
* import 주의 사항
1. dba export 했다면 dba import
2. DDL, DML 을 수행하는 것이므로 Redo log, undo segment 사용함.
-> undo tablespace 크기 준비
-> error 시 rollback
=> 이것을 막기 위해 옵션 중 commit=y 변경. array 단위로 commit 을 하기 때문에
전체의 rollback 을 막을수 있음.
(2) alter table move
1. 통계정보 생성
2. 특정 테이블의 chain_cnt 값 확인 ( 테이블 재구성 필요 )
select num_rows, chain_cnt FROM dba_tables;
3. 이동할 Tablespace 생성
4. Table 이동
alter table [Table_name] move [이동할 Tablespace];
5. 인덱스 확인
Select table_name, index_name, status from dba_indexes
6. 인덱스 rebuild
alter index [Index_name] rebuild
* 인덱스 rebuild 이유
- 인덱스의 잦은 DDL, DML 으로인해 마킹 된 부분이 생김
-> 인덱스 사이즈에 비해 인덱스 세그먼트의 크기가 엄청 커짐
-> Index B-Tree 구조의 밸런스가 심하게 깨짐
(3) Create table as select
1. Table 재생성
Create Table [Table_name] as Select * from [Table_name]
2. Index 재생성
Create Index Index_name on Table_name(Column_name)
3. 인덱스 rebuild
alter index index_name rebuild
4) SHRINK
- 기존에는 테이블을 새로 작성(Reorg) 작업을 통해 커진 테이블 스페이스 크기를 복구 가능하였음.
- 10g 부터는 Shrink 기능을 이용하여 손쉽게 테이블을 줄일 수 있음.
-> HWM 줄일 수 있음
- (ASSM : Automatic Segment Space Management) 기능을 사용하는 테이블 스페이스만 가능
-> DBMS_SPACE 패키지의 SPACE_USAGE 프로시저를 사용해야함.
* 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 수행 안됨
- 대용량 테이블일 경우 속도가 느리다.
- Shrink 수행 방법
1. 웹사이트를 통해 온라인으로 접수된 예약 정보를 보관(TEST 테이블)
2. 확인 절차를 거친 후 TEST_HIST 테이블에 저장되고 해당 레코드는 TEST테이블에서 삭제됨.
3. 걸리는 시간은 고객마다 다름.
4. 레코드 삭제로 인해 남은 공간이 충분하지 않을 경우 HWM 위쪽 영역에 insert 됨.
- Shink 통해 낭비되는 공간 재 확보
1. 세그먼트에서 얼마나 많은 공간을 확보 할 수 있는지 확인.
2. ASSM 이 적용된 테이블스페이스에 위치하고 있으므로,
DBMS_SPACE 패키지의 SPACE_USAGE 프로시저를 사용해야함.
- rwo-movement 활성 여부 확인
alter table [table_name] enable row movement;
- 단편화 완료 후 disable
alter table [table_name] disable row movement;
- 형태
1. 테이블 공간만 tablespace 환원
Alter Table [Table_name] Shrink Space;
2. 모든 Object 까지 tablespace 환원
Alter Table [Table_name] Shrink Space Cascade;
3. 행만 이동
Alter Table [Table_name] Shrink Space compact;
EX) 세그먼트에서 얼마나 공간을 확보할수 있는지.
select * from table(dbms_space.asa_recommendations());
Reorg 시 작업 순서 ?
1. backup , 통계정보 DBMS_STATS.EXPORT_SCHEMA_STATS
2. 옮길 테이블 조사
select * from table(dbms_space.asa_recommendations());
type 이 long, log raw 일 경우 move 불가능
확인 방법
select owner, table_name, column_name , data_type from dba_tab_columns
where owner not in ('SYS','SYSTEM','OUTLN','WMSYS')
and ( data_type like '%LONG%')
-> 불가능한 것은 imp/exp
3. move tablespace . parallel 수행.
cpu 개수 확인 => show parameter cpu_count
테이블 무브(parallel 4, nologging)
alter table [테이블 명] move tablespace [table_space명] parallel 4 nologging;
인덱스 리빌딩(parallel 4, nologgin)
ALTER INDEX [인덱스 명] REBUILD TABLESPACE [table_space명]PARALLEL 4 NOLOGGING;
alter table [test] noparallel logging;
#lob 일 경우
ALTER table RPMMGR.ILM_SQLSET_STAGE move lob(BIND_VALUE) STORE AS SYS_LOB0000115429C00013$$ (tablespace HPC_MAIL_LOB);
[테이블명] [랍컬럼] [랍세그먼트명] [테이블스페이스명]
=> lob move 시 해당 테이블에 걸린 인덱스가 unsuable 로 빠지므로 리빌딩 필요
=> lob index 의 경우는 리빌딩 불필요
=> ALTER TABLE IM_CONTENTS MODIFY LOB(CONTENTS)(SHRINK SPACE CASCADE);
[테이블명] [컬럼명]
select OWNER,TABLE_NAME,TABLESPACE_NAME,INDEX_NAME from DBA_INDEXES
where index_name ='SYS_IL0000074793C00013$$';
-- lob
select owner, table_name, segment_name, column_name from dba_lobs
where table_name ='CONSULT';
ALTER table CHN.CONSULT move lob(CONSULT_MSG) STORE AS SYS_LOB0000074793C00011$$ (tablespace TBS_CHN);
ALTER table CHN.CONSULT move lob(NOTICE_MSG) STORE AS SYS_LOB0000074793C00012$$ (tablespace TBS_CHN);
ALTER table CHN.CONSULT move lob(AGREE_MSG) STORE AS SYS_LOB0000074793C00013$$ (tablespace TBS_CHN);
alter table CHN.CONSULT modify lob(CONSULT_MSG)(shrink space cascade);
alter table CHN.CONSULT modify lob(NOTICE_MSG)(shrink space cascade);
alter table CHN.CONSULT modify lob(AGREE_MSG)(shrink space cascade);
4. tail -f alert_sid.log 필요.
5. long type 경우 exp/imp
exp system/manager file=type.dmp tables='(scott.PLAN_TABLE,scott.TEMP_HONG)' buffer=9999999
imp system/manager fromuser=scott touser=scott file=type.dmp buffer=99999999
5. 캐릭터값 확인.
6. analyze 수행 -> 데이터 블록이 차곡차곡 쌓였으므로 에널라이즈를 통해 다시 조사.
analyze 를 해야함. < empty_blocks, avg_space, chain_cnt 등도 수집 가능
dbms_stats 사용하여 < 파티션일 경우.
execute DBMS_STATS.GATHER_SCHEMA_STATS (ownname=>'SCOTT', ESTIMATE_PERCENT=>10, block_sample=>TRUE, degree=> 8, cascade=>TRUE);
degree = parallel
cascade = ture ( index 까지 )
7. Noparallel 아닌 테이블 출력, 인덱스 출력
select * from dba_tables where degree > 1 and owner not in ('SYS','SYSTEM','OUTLN','PERFSTAT');
select * from dba_indexes
where degree > 1 and degree<>'DEFAULT' and owner not in ('SYS','SYSTEM','OUTLN','PERFSTAT');