"오라클 HWM 확인"의 두 판 사이의 차이

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';

4.1 HWM reorg 테스트[편집]

4.1.1 해결 방법 2가지[편집]

Shrink
- Online으로 테이블의 HWM을 낮추어 공간을 축소
Table Move 작업

4.1.2 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

4.1.3 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.

블록수와 용량이 줄어든 것을 확인

@@ 214번째 줄: / 214번째 줄: @@
 </source>
+{{:reorg 테스트}}
 [[Category:oracle]]