행위

오라클 HWM 확인

DB CAFE

HWM(High Water Mark)란 고수위 즉, 마지막까지 등록된 블록위치. 만약 데이터가 대량으로 지워지면 이전에 표시되었던 HWM은 그대로이고 실제로 사용되는 데이터는 HWM보다 훨씬 작을 것이지만 데이터를 추출시 HWM표시부분까지 읽기때문에 불필요한 DISK I/O 발생.


1 HWM(High Water Mark) 확인

1. 블록확인

SELECT TABLE_NAME
     , NUM_ROWS
     , BLOCKS
     , EMPTY_BLOCKS
  FROM DBA_TABLES 
WHERE TABLE_NAME = '테이블명';
SELECT * FROM   TABLE(dbms_space.asa_recommendations());

2. 실제사용 블럭확인

SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
  FROM [TABLE_NAME];

※ 블록의 크기와 실재사용 블록의 크기가 차이가 많이 나면 HWM를 지워주는 것이 좋다.

3. 용량 확인

SELECT SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB 
  FROM DBA_SEGMENTS 
 WHERE SEGMENT_NAME IN ('[TABLE_NAME]','IDX_HWM_COLB');

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;
  1. 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;
  1. 잦은 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_기존테이블스페이스;
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;