오라클 HWM 확인
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
- HWM(High Water Mark)란 고수위 즉, 마지막까지 등록된 블록위치.
- 만약 데이터가 대량으로 지워지면 이전에 표시되었던 HWM은 그대로이고 실제로 사용되는 데이터는 HWM보다 훨씬 작을 것이지만
데이터를 추출시 HWM표시부분까지 읽기때문에 불필요한 DISK I/O 발생.
목차
1 HWM(High Water Mark) 확인[편집]
1.1 테이블 블록확인[편집]
SELECT TABLE_NAME
, NUM_ROWS
, BLOCKS
, EMPTY_BLOCKS
FROM DBA_TABLES
WHERE TABLE_NAME = '테이블명';
1.2 파티셔닝 테이블 블럭[편집]
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;
2. 실제사용 블럭확인
SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM [TABLE_NAME] -- 파티셔닝 테이블일 경우 PARTITION(파티션명)
;
※ 블록의 크기와 실재사용 블록의 크기가 차이가 많이 나면 HWM를 지워주는 것이 좋다.
3. 용량 확인
SELECT SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN ('[TABLE_NAME]','IDX_HWM_COLB');
1.3 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;
/
2 HWM 제거[편집]
2.1 HWM제거 방법1[편집]
해당 테이블을 익스포트 한 후에 TRUNCATE TABLE후 인포트 수행
-- 테이블 백업 후 TRUNCATE
1) CTAS 백업;
2) TRUNCATE TABLE [TABLE_NAME];
3) 데이터 입력
4) 통계정보 생성
2.2 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는 테이블 스페이스의 단편화를 유발할 수 있음
2.3 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;
3 현재 사용중인 테이블스페이스로 다시 이동[편집]
- 파티션 테이블 hwm 줄이기
- 인덱스 동시 업데이트
- 병렬 처리
- 12c New Feature
- global index rebuild 불필요
ALTER TABLE OWNER.테이블명 MOVE PARTITION P2022 UPDATE INDEXES ONLINE PARALLEL 64;
4 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';