Row Migration

Row Migration(행마이그레이션)이란 하나의 레코드는 하나의 블록안에 들어가야하는데 
update가 발생하여 레코드값이 커지면서 블록안에 남은 여유공간이 없어 그 레코드가 다른 블록으로 옮겨지게 되는 현상입니다.

 

행 마이그레이션을 방지하기 위해서 블록에 여유공간을 남겨 놓는데 이것을 PCTFREE 라 합니다.

PCTFREE의 필요성을 한번 테스트 해봅시다.

 

먼저 2개의 테이블 생성.

create table aaa (a number ,b varchar2(20) ,c date default sysdate) tablespace users pctfree 0 pctused 30 storage (freelists 2); ======================================= aaa 테이블에 대해 2만건 데이터 insert. begin for z in 1..20000 loop insert into aaa values (z,'abcdef',default); end loop; end;/ 후 commit; ======================================= aaa 테이블의 통계정보를 수집. anaylze table aaa compute statistics;

create table bbb (a number ,b varchar2(20) ,c date default sysdate) tablespace users pctfree 20 pctused 30 storage (freelists 2); ==================================== bbb 테이블에 대해 2만건 데이터 insert. begin for z in 1..20000 loop insert into bbb values (z,'abcdef',default); end loop; end;/  후 commit; ==================================== aaa 테이블의 통계정보를 수집. anaylze table aaa compute statistics;

 

cf.

PCTUSED : 블록에 있던 데이터가 삭제가 되었을 때 어느 정도의 공간이 남게 된다면 그 블록은 다시 재사용이 가능한데 이것을 PCTUSED라고 하고 기준치를 퍼센트 단위로 설정하게 된다.

기준치를 높게 잡는다면 블록의 재사용률이 낮아질 것이고, 낮게 잡으면 행마이그레이션 일어날 확률이 높아질 것입니다.

 

SQL> select table_name, num_rows, chain_cnt, blocks from user_tables
  2  where table_name in ('AAA','BBB');

TABLE_NAME     NUM_ROWS  CHAIN_CNT     BLOCKS
-------------- -------------- ----------- -----------
AAA                             20000                0               65
BBB                             20000                0               80

 

현재 사용되어진 블록은 각각 65,80개가 사용되어지고 CHAIN_CNT를 확인하여보니 행마이그레이션은 일어나지 않았습니다.

여기서 각 테이블의 B 컬럼을 업데이트를 해보겠습니다.

 

SQL> update aaa set b='abcdefghijk';

20000 행이 갱신되었습니다.

SQL> update bbb set b='abcdefghijk';

20000 행이 갱신되었습니다.

SQL> commit;

커밋이 완료되었습니다.

SQL> analyze table aaa compute statistics;

테이블이 분석되었습니다.

SQL> analyze table bbb compute statistics;

테이블이 분석되었습니다.

SQL> select table_name, num_rows, chain_cnt, blocks from user_tables
  2  where table_name in ('AAA','BBB');

TABLE_NAME     NUM_ROWS  CHAIN_CNT     BLOCKS
-------------- -------------- ----------- -----------
AAA                             20000            5539             103
BBB                             20000                0               80

SQL>

BBB 테이블은 사용되어진 블록의 개수가 변하지 않고 그대로 80개의 블록이 사용되었지만, AAA 테이블은 사용되는 블록이 103개로 늘어나면서 행마이그레이션이 발생했다는 것을 알수있습니.  

결과적으로 PCTFREE 를 설정해줌으로써 큰 데이터 값으로 업데이트시에도 행마이그레이션이 발생하지 않았습니다.

행마이그레이션이 발생하지 않기 위해서는 PCTFREE, PCTUSED 에 대한 비율도 잘 맞춰주어서 블록의 빈 공간에 대해서도 효율적으로 관리를 해주어야 할 것 입니다.

이러한 번거로움 때문에 9i부터는 PCTUSED만 자동관리가 된다.