행위

오라클 HWM 확인

DB CAFE

  1. HWM(High Water Mark)란 고수위 즉, 마지막까지 등록된 블록위치.
  2. 만약 데이터가 대량으로 지워지면 이전에 표시되었던 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;
  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_기존테이블스페이스
-- 인덱스도 같이 리빌드할 경우
-- 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';