행위

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

DB CAFE

(현재 사용중인 테이블스페이스로 다시 이동)
(현재 사용중인 테이블스페이스로 다시 이동)
 
(같은 사용자의 중간 판 4개는 보이지 않습니다)
1번째 줄: 1번째 줄:
 +
== HWM(High Water Mark) 확인 ==
 
# HWM(High Water Mark)란 고수위 즉, 마지막까지 등록된 블록위치.  
 
# HWM(High Water Mark)란 고수위 즉, 마지막까지 등록된 블록위치.  
# 만약 데이터가 대량으로 지워지면 이전에 표시되었던 HWM은 그대로이고 실제로 사용되는 데이터는 HWM보다 훨씬 작을 것이지만
+
# 만약 데이터가 대량으로 지워지면 이전에 표시되었던 HWM은 그대로이고 실제로 사용되는 데이터는 HWM보다 훨씬 작을 것이지만 데이터를 추출시 HWM표시부분까지 읽기때문에 불필요한 DISK I/O 발생.
데이터를 추출시 HWM표시부분까지 읽기때문에 불필요한 DISK I/O 발생.
 
 
 
 
 
== HWM(High Water Mark) 확인 ==
 
 
 
 
=== 테이블 블록확인 ===
 
=== 테이블 블록확인 ===
 
<source lang=sql>
 
<source lang=sql>
62번째 줄: 58번째 줄:
 
</source>
 
</source>
  
2. 실제사용 블럭확인  
+
=== 실제사용 블럭확인 ===
 
<source lang=sql>
 
<source lang=sql>
 
SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
 
SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
71번째 줄: 67번째 줄:
 
※ 블록의 크기와 실재사용 블록의 크기가 차이가 많이 나면 HWM를 지워주는 것이 좋다.
 
※ 블록의 크기와 실재사용 블록의 크기가 차이가 많이 나면 HWM를 지워주는 것이 좋다.
  
3. 용량 확인
+
=== 용량 확인 ===
 
<source lang=sql>
 
<source lang=sql>
 
SELECT SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB  
 
SELECT SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB  
137번째 줄: 133번째 줄:
 
ALTER TABLE  [TABLE_NAME]  DEALLOCATE UNUSED;
 
ALTER TABLE  [TABLE_NAME]  DEALLOCATE UNUSED;
 
</source>
 
</source>
# SHRINK 실행
+
==== SHRINK 실행 ====
 
<source lang=sql>
 
<source lang=sql>
 
-- ROW-MOVEMENT 활성화를 먼저 해줘야 SHRINK 사용이 가능
 
-- ROW-MOVEMENT 활성화를 먼저 해줘야 SHRINK 사용이 가능
145번째 줄: 141번째 줄:
 
ALTER TABLE [TABLE_NAME] SHRINK SPACE CASCADE;
 
ALTER TABLE [TABLE_NAME] SHRINK SPACE CASCADE;
 
</source>
 
</source>
# 잦은 DEALLOCATE는 테이블 스페이스의 단편화를 유발할 수 있음
+
* 잦은 DEALLOCATE는 테이블 스페이스의 단편화를 유발할 수 있음
  
 
=== HWM제거 방법3(테이블스페이스 이동) ===
 
=== HWM제거 방법3(테이블스페이스 이동) ===
181번째 줄: 177번째 줄:
 
</source>
 
</source>
  
== 현재 사용중인 테이블스페이스로 다시 이동 ==
+
==== 현재 사용중인 테이블스페이스로 다시 이동 ====
 
* 파티션 테이블 hwm 줄이기
 
* 파티션 테이블 hwm 줄이기
* 인덱스 동시 업데이트
+
* rowid가 변경되기 때문에 인덱스도 동시 업데이트
 
* 병렬 처리
 
* 병렬 처리
 
* 12c New Feature
 
* 12c New Feature
191번째 줄: 187번째 줄:
 
</source>
 
</source>
  
== Procedure ==
+
==== Procedure ====
 
<source lang=sql>
 
<source lang=sql>
 
begin
 
begin
214번째 줄: 210번째 줄:
  
 
</source>
 
</source>
 +
{{:reorg 테스트}}
 +
 
[[Category:oracle]]
 
[[Category:oracle]]

2023년 11월 28일 (화) 16:29 기준 최신판

thumb_up 추천메뉴 바로가기


1 HWM(High Water Mark) 확인[편집]

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

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;

1.3 실제사용 블럭확인[편집]

SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
  FROM [TABLE_NAME] -- 파티셔닝 테이블일 경우 PARTITION(파티션명)
;

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

1.4 용량 확인[편집]

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

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

2.2.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;
  • 잦은 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;

2.3.1 현재 사용중인 테이블스페이스로 다시 이동[편집]

  • 파티션 테이블 hwm 줄이기
  • rowid가 변경되기 때문에 인덱스도 동시 업데이트
  • 병렬 처리
  • 12c New Feature
    • global index rebuild 불필요
ALTER TABLE OWNER.테이블명 MOVE PARTITION P2022 UPDATE INDEXES ONLINE PARALLEL 64;

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

2.4 HWM reorg 테스트[편집]

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

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

2.4.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) 실제 사용 블록 및 크기 확인

  1. 테이블 360MB , 인덱스 128MB 확인
  2. 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) 블록 수 및 용량 확인

  1. 실제 사용블록은 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) 용량 및 블록 수 다시 확인

  1. 용량과 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

2.4.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.
  • 블록수와 용량이 줄어든 것을 확인