다른 명령
HWM(High Water Mark) 확인
- HWM(High Water Mark)란 고수위 즉, 마지막까지 등록된 블록위치.
- 만약 데이터가 대량으로 지워지면 이전에 표시되었던 HWM은 그대로이고 실제로 사용되는 데이터는 HWM보다 훨씬 작을 것이지만 데이터를 추출시 HWM표시부분까지 읽기때문에 불필요한 DISK I/O 발생.
테이블 블록확인
SELECT TABLE_NAME , NUM_ROWS , BLOCKS , EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME = '테이블명';
파티셔닝 테이블 블럭
SELECT TABLE_NAME , NUM_ROWS , BLOCKS , EMPTY_BLOCKS FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = '테이블명';
SELECT * FROM TABLE(dbms_space.asa_recommendations());
op1 NUMBER; op2 NUMBER; op3 NUMBER; op4 NUMBER; op5 NUMBER; op6 NUMBER; op7 NUMBER; BEGIN Dbms_Output.Disable; Dbms_Output.Enable(1000000); Dbms_Output.Put_Line('TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK'); Dbms_Output.Put_Line('------------------------------ --------------- --------------- ---------------'); FOR cur_rec IN cu_tables LOOP Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7); Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') || LPad(op3,15,' ') || LPad(op1,15,' ') || LPad(Trunc(op1-op3-1),15,' ')); END LOOP; END;
실제사용 블럭확인
SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED" FROM [TABLE_NAME] -- 파티셔닝 테이블일 경우 PARTITION(파티션명) ;
※ 블록의 크기와 실재사용 블록의 크기가 차이가 많이 나면 HWM를 지워주는 것이 좋다.
용량 확인
SELECT SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('[TABLE_NAME]','IDX_HWM_COLB');
dbms_space.space_usage 프로시져
- FS1 (free space - pctfree 공간) , Full block 이 아닌 블럭들이 많은 경우 블럭 내부에 빈공간이 많다는 의미
- FS1 , Full 이외의 블럭이 차지하는 빈도가 높은 경우는 reorg 대상임.
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 => upper(:owner), -- 1. Owner 입력 segment_name => upper(:segment_name), -- 2. 테이블 이름 입력 segment_type => upper(:segment_type), -- 3. 세그먼트 종류 입력 TABLE,TABLE PARTITION,TABLE SUBPATITION,INDEX,INDEX PARTITION,INDEX SUBPATITION,CLUSTER,LOB,LOB PARTITION,LOB SUBPATITION 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; /
HWM 제거
HWM제거 방법1
해당 테이블을 익스포트 한 후에 TRUNCATE TABLE후 인포트 수행
-- 테이블 백업 후 TRUNCATE 1) CTAS 백업; 2) TRUNCATE TABLE [TABLE_NAME]; 3) 데이터 입력 4) 통계정보 생성
HWM제거 방법2(HWM상위영역을 해제)
ALTER TABLE [TABLE_NAME] DEALLOCATE UNUSED;
SHRINK 실행
-- ROW-MOVEMENT 활성화를 먼저 해줘야 SHRINK 사용이 가능 ALTER TABLE [TABLE_NAME] ENABLE ROW MOVEMENT; ALTER TABLE [TABLE_NAME] SHRINK SPACE; -- 인덱스포함 할경우 CASCADE ALTER TABLE [TABLE_NAME] SHRINK SPACE CASCADE;
- 잦은 DEALLOCATE는 테이블 스페이스의 단편화를 유발할 수 있음
HWM제거 방법3(테이블스페이스 이동)
-- MOVE용 임시 테이블 스페이스 생성
-- 1.TS_IMSI 테이블 스페이스 생성 SQL> CREATE TABLESPACE TS_IMSI DATAFILE '/oracle/app/oracle/oradata/movets01.dbf' SIZE 1G; -- 2.임시테이블스페이스로 테이블 MOVE SQL> ALTER TABLE [TABLE_NAME] MOVE TABLESPACE TS_IMSI; Table altered. -- 3.테이블 기존 테이블 스페이스로 이동 SQL> ALTER TABLE [TABLE_NAME] MOVE TABLESPACE TS_기존테이블스페이스 -- 인덱스도 같이 리빌드할 경우 -- UPDATE INDEXES ONLINE PARALLEL 64 ; Table altered.
-- 인덱스 상태 조회 (테이블스페이스 이동으로 UNUSABLE 상태로 변경됨)
SQL> SELECT TABLE_NAME, INDEX_NAME, STATUS FROM DBA_INDEXES WHERE TABLE_NAME = '[TABLE_NAME]' ; -- 인덱스 리빌드 SQL> ALTER INDEX IDX_HWM_COLB REBUILD;
현재 사용중인 테이블스페이스로 다시 이동
- 파티션 테이블 hwm 줄이기
- rowid가 변경되기 때문에 인덱스도 동시 업데이트
- 병렬 처리
- 12c New Feature
- global index rebuild 불필요
ALTER TABLE OWNER.테이블명 MOVE PARTITION P2022 UPDATE INDEXES ONLINE PARALLEL 64;
Procedure
begin DBMS_REDEFINITION.REDEF_TABLE( uname=>'SDE' , tname=>'MY_DATA_TABLE' , table_part_tablespace=>'SDEBUS_DT' , index_tablespace=>'SDEBUS_IX' ); end; /
-- omit the "execute" if you want to run the following output -- in a BEGIN/END block instead of running each line, one at a time. select 'execute DBMS_REDEFINITION.REDEF_TABLE(uname=>''' || owner || ''', tname=>' || '''' || table_name || '''' || ', table_part_tablespace=>''SDEBUS_DT'' , index_tablespace=>''SDEBUS_IX'');' as cmd from dba_tables where owner='MARK.STEWART';
HWM reorg 테스트
해결 방법 2가지
- Shrink
- - Online으로 테이블의 HWM을 낮추어 공간을 축소
- Table Move 작업
Shrink
1) 테이블과 인덱스 생성
CREATE TABLE HWMTEST(A VARCHAR2(20), B NUMBER, C NUMBER, D VARCHAR2(30), E VARCHAR2(30)); Table created.
CREATE INDEX IDX_HWM_B ON HWMTEST(B); Index created.
2) 데이터 삽입 (총 6000000건의 데이터 삽입)
DECLARE TYPE tbl_ins IS TABLE OF HWMTEST%ROWTYPE INDEX BY BINARY_INTEGER; w_ins tbl_ins; BEGIN FOR i IN 1..1000000 LOOP w_ins(i).A :=dbms_random.string('x',10); w_ins(i).B :=i; w_ins(i).C :=99; w_ins(i).D :='ABC'||dbms_random.string('x',10); w_ins(i).E :='EEEEEEEEEEEEEEEE'; END LOOP; FORALL i in 1..1000000 INSERT INTO HWMTEST VALUES w_ins(i); COMMIT; FORALL i in 1..1000000 INSERT INTO HWMTEST VALUES w_ins(i); COMMIT; FORALL i in 1..1000000 INSERT INTO HWMTEST VALUES w_ins(i); COMMIT; FORALL i in 1..1000000 INSERT INTO HWMTEST VALUES w_ins(i); COMMIT; FORALL i in 1..1000000 INSERT INTO HWMTEST VALUES w_ins(i); COMMIT; FORALL i in 1..1000000 INSERT INTO HWMTEST VALUES w_ins(i); COMMIT; END; /
3) 통계정보 수집
exec dbms_stats.gather_table_stats('TEST','HWMTEST'); PL/SQL procedure successfully completed.
4) 실제 사용 블록 및 크기 확인
- 테이블 360MB , 인덱스 128MB 확인
- dba_segments에서 블록수와 dbms_rowid로 조회한 실제 사용 블록수의 차이는 적게나타난것 확인
select segment_name,blocks, bytes/1024/1024 MB from dba_segments where segment_name in ('HWMTEST','IDX_HWM_B'); SEGMENT_NAME BLOCKS MB ---------------- ---------- ---------- HWMTEST 46080 360 IDX_HWM_B 16384 128
SELECT TABLE_NAME,NUM_ROWS,BLOCKS FROM DBA_TABLES WHERE TABLE_NAME = 'HWMTEST'; TABLE_NAME NUM_ROWS BLOCKS ---------------- ---------- ---------- HWMTEST 6000000 45797
- 실제사용한 블록
SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)|| DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED" FROM HWMTEST; USED ---------- 45448
5) 데이터 삭제
delete from hwmtest where rownum <= 4000000; 4000000 rows deleted.
6) 삭제 후 통계정보 재 수집
exec dbms_stats.gather_table_stats('TEST','HWMTEST'); PL/SQL procedure successfully completed.
7) 블록 수 및 용량 확인
- 실제 사용블록은 45448 -> 15151로 줄었지만 용량과 HWM가 찍힌 블록수는 차이가 없는 것을 확인
SELECT TABLE_NAME,NUM_ROWS,BLOCKS FROM DBA_TABLES WHERE TABLE_NAME = 'HWMTEST'; TABLE_NAME NUM_ROWS BLOCKS ---------------- ---------- ---------- HWMTEST 2000000 45797
select segment_name,blocks, bytes/1024/1024 MB from dba_segments where segment_name in ('HWMTEST','IDX_HWM_B'); SEGMENT_NAME BLOCKS MB ---------------- ---------- ---------- HWMTEST 46080 360 IDX_HWM_B 16384 128
SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)|| DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED" FROM HWMTEST; USED ---------- 15151
8) Shrink 수행
- ORA-10636: ROW MOVEMENT is not enabled 에러 발생시 권한 부여
alter table hwmtest enable row movement; Table altered.
alter table hwmtest shrink space cascade; Table altered.
- row movement 다시 disable
alter table hwmtest disable row movement;
9) 용량 및 블록 수 다시 확인
- 용량과 HWM 블록수 모두 줄어든 것 확인
exec dbms_stats.gather_table_stats('TEST','HWMTEST'); PL/SQL procedure successfully completed.
select segment_name,blocks, bytes/1024/1024 MB from dba_segments where segment_name in ('HWMTEST','IDX_HWM_B') ; SEGMENT_NAME BLOCKS MB ---------------- ---------- ---------- HWMTEST 15320 119.6875 IDX_HWM_B 4600 35.9375 2 rows selected.
SELECT TABLE_NAME,NUM_ROWS,BLOCKS FROM DBA_TABLES WHERE TABLE_NAME = 'HWMTEST'; TABLE_NAME NUM_ROWS BLOCKS ---------------- ---------- ---------- HWMTEST 2000000 15151
SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)|| DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED" FROM HWMTEST; USED ---------- 15151
Table Move
상동 1) ~ 7)
8) Table Move작업 수행
- Table Move작업을 수행하면 Rowid가 변경되어서 기존의 Index가 Unusable상태로 변경되어 인덱스 사용불가
- 반드시 rebuild 해주어야 함
alter table hwmtest move tablespace test; Table altered.
select index_name,status from user_indexes; INDEX_NAME STATUS ------------------------------ IDX_HWM_B UNUSABLE
alter index IDX_HWM_B rebuild tablespace test; Index altered.
select index_name,status from user_indexes; INDEX_NAME STATUS ------------------------------ IDX_HWM_B VALID
8) 통계정보재수집 및 축소 확인
exec dbms_stats.gather_table_stats('TEST','HWMTEST'); PL/SQL procedure successfully completed.
SELECT TABLE_NAME,NUM_ROWS, BLOCKS FROM DBA_TABLES WHERE TABLE_NAME = 'HWMTEST'; TABLE_NAME NUM_ROWS BLOCKS ---------------- ---------- ---------- HWMTEST 1000000 7764 1 row selected.
select segment_name,blocks, bytes/1024/1024 MB from dba_segments where segment_name in ('HWMTEST','IDX_HWM_B') ; SEGMENT_NAME BLOCKS MB ---------------- ---------- ---------- HWMTEST 7808 61 IDX_HWM_B 2304 18
SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)|| DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED" FROM HWMTEST; USED ---------- 7634 1 row selected.
- 블록수와 용량이 줄어든 것을 확인