row chaining, row migration

■ ROW chaining

1) 정의: 한 블록에 저장할 수 있는 한 행의 크기가 블록 크기를 초과하여 해당 행이 여러 블록에 걸쳐 저장되는 현상.

2) 

원인: 모델링 오류 또는 블록 크기를 작게 설정한 경우.

3) 문제점: 성능 저하 및 i/o 비용 증가.

4) 해결 방법: 모델링을 재고, 블록 크기를 조절하여 적절한 크기로 설정.

 

# 테이블 row, block, 평균 바이트값 조회

select num_rows, blocks, avg_row_len 
from dba_tables 
where owner='HR' and table_name='EMP';

-- num_rows : row의 수

-- blocks : block의 수

-- avg_row_len : 한 행에 평균 바이트값 (체인화를 예방하기위해 확인해볼 수 있음)

 

■ row migration

1) 정의: update시에 증가분의 free 공간이 부족하여 해당 행이 다른 블록으로 이전되는 현상. 

2) 원인: update로 인한 행 크기의 증가 및 해당 블록의 free 공간 부족.

3) 문제점: rowid 접근 성능 저하 및 공간의 비효율성

4) 해결방법: pctfree 값 조정 및 증가분에 대한 충분한 free 공간 확보.

---

1. 마이그레이션 실습

<hr sess>

1) 테이블 생성

create table hr.mig_table(id number, l_name varchar2(2000), f_name varchar2(2000));

 

2) insert 

INSERT INTO hr.mig_table ( id, l_name, f_name)
    SELECT level, 
        decode(mod(level, 3), 1, NULL, rpad('x', 2000, 'x')),
        decode(mod(level, 3), 1, NULL, rpad('x', 1000, 'x'))
    FROM dual
    CONNECT BY level <= 1000;

 

#) 저장

commit;

 

3) 통계정보 확인

- 통계 수집 전

select num_rows, blocks, chain_cnt 
from user_tables 
where table_name = 'MIG_TABLE';

 

- 통계 수집

execute dbms_stats.gather_table_stats('HR','MIG_TABLE');

 

- 통계 수집 후

select num_rows, blocks, chain_cnt from user_tables where table_name = 'MIG_TABLE';

chain_cnt : row migration이 발생한 것을 보여주고, row chain이 발생한 것도 보여준다.

하지만, 0인 이유는? 위 통계수집방식은 반영이 안된다고 한다. 따라서 14) 단계에서 다른 수집방법을 사용할 것이다.

 

4) index 생성

create index mig_table_idx on mig_table(id);

 

5) row migration 발생

update hr.mig_table 
set l_name = rpad('x',2000,'x'), f_name = rpad('x',1000,'x') 
where mod(id,3)=1;

commit;

 

--> row migration 334건이 발생

 

6) 조회

execute dbms_stats.gather_table_stats('HR','MIG_TABLE');

 

select num_rows, blocks, chain_cnt from user_tables where table_name = 'MIG_TABLE';

 

7) system level 통계정보 조회 (row migration화 조회)

<sys sess>

select * from v$sysstat where name = 'table fetch continued row';

- VALUE : migration화가 발생된 row를 access한 순간 value가 1증가한다

 

8) table fetch continued row 조회 (session level의 통계정보)

select to_char(sysdate,'yyyy-mm-dd hh24:ss:ss') day, a.sid, vss.username, a.name, a.value
from ( select vst.sid,vst.value,vsn.name,vsn.statistic#
       from v$statname vsn, v$sesstat vst 
       where vsn.statistic# = vst.statistic# 
       order by vst.value desc) a, v$session vss
where a.sid = vss.sid
and vss.username = 'HR'
and a.name =  'table fetch continued row';

 

9) 인덱스힌트를 통해 mig_table count하기 (random i/o 많이 발생)

<hr sess>

select /*+ index(t mig_table_idx) */ count(l_name) from hr.mig_table t where id > 0;

10) system 통계정보, session 통계정보

<sys sess> 

select * from v$sysstat where name = 'table fetch continued row';

--1457에서 1964로 증가

 

select to_char(sysdate,'yyyy-mm-dd hh24:ss:ss') day, a.sid, vss.username, a.name, a.value
from ( select vst.sid,vst.value,vsn.name,vsn.statistic#
       from v$statname vsn, v$sesstat vst
       where vsn.statistic# = vst.statistic#
       order by vst.value desc) a, v$session vss
where a.sid = vss.sid
and vss.username = 'HR'
and a.name =  'table fetch continued row';

 

-- row의 migration이 발생한 수만큼 증가

-- 308 -> 642 로 증가

 

11) 문제가되는 SQL문장 찾아보기

<sys sess>

select s.prev_sql_id, s.prev_child_number, v.sql_text
from v$session s, v$sql v
where s.prev_sql_id = v.sql_id
and s.prev_child_number = v.child_number
and s.username = 'HR';

-- ROW의 MIGRATION이 발생한 친구

 

12) PLAN정보 확인

select * from table(dbms_xplan.display_cursor('4y2m5q8hhv0r4'));

 

13) 

select num_rows, blocks, chain_cnt from dba_tables where table_name = 'MIG_TABLE';

-- 아직 수집안됨 (gather로 안됨)

 

14) row migration에 대한 통계수집은 기존에 사용하는 방법을 사용해야함 ★

analyze table 테이블명 compute statistics;

<sys sess> 

analyze table hr.mig_table compute statistics;

 

select num_rows, blocks, chain_cnt from dba_tables where table_name = 'MIG_TABLE';

-- 334개가 row migration이 발생

 

15) migration 해결방안 (=> 테이블 재배치)

(1) Object_id 조회

select object_id, data_object_id from dba_objects where object_name = 'MIG_TABLE';

(2) move ★

alter table hr.mig_table move;

-- MOVE작업을 하기 전에 대상 TBS에 free공간이 있는지 확인해야함. storage 확인 먼저 !

SELECT tablespace_name, file_id, file_name, bytes, maxbytes, autoextensible
FROM dba_data_files
WHERE tablespace_name = '대상_TBS_이름';

-- 이 순간 DML이 진행되어 다른 dml 막힘 -> redo발생량 많아짐

-- 내부적으로 CTAS가 돌아감. 즉, TEMP TBS에 만들어놓고 RENAME함. 그래서 OBJECT번호가 바뀌고 ROWID가 바뀜

----------------------------------------------------

tip) storage 공간이 없을때 다른 tbs로 이동해서 move하기

alter table hr.mig_table move tablespace example;

----------------------------------------------------

 

(3) object_id조회

select object_id, data_object_id from dba_objects where object_name = 'MIG_TABLE';

 

-- Object번호가 바뀜

-- data_object_id

 

(4) index 조회

select index_name, status from dba_indexes where table_name = 'MIG_TABLE';

 

-- Index가 unusable됨

 

(5) index rebuild (인덱스 재구성) ★

<sys sess>

alter index hr.mig_table_idx rebuild online;

-- online : 운영 중에 rebuild 가능하도록 하기

 

(6) index 조회

select index_name, status from dba_indexes where table_name = 'MIG_TABLE';

 

(7) 다시 마이그레이션 통계수집

analyze table hr.mig_table compute statistics;

 

(8) 통계수집 조회

select num_rows, blocks, chain_cnt from dba_tables where table_name = 'MIG_TABLE';

16) 통계정보 조회 (구간확인을 위한 검색)

(1) system level

<sys sess> 

select * from v$sysstat where name = 'table fetch continued row';

 

(2) session level

select to_char(sysdate,'yyyy-mm-dd hh24:ss:ss') day, a.sid, vss.username, a.name, a.value
from ( select vst.sid,vst.value,vsn.name,vsn.statistic#
       from v$statname vsn, v$sesstat vst 
       where vsn.statistic# = vst.statistic# 
       order by vst.value desc) a, v$session vss
where a.sid = vss.sid
and vss.username = 'HR'
and a.name =  'table fetch continued row';

 

17) 인덱스힌트를 통해 mig_table count하기 (random i/o 많이 발생)

<hr sess>

select /*+ index(t mig_table_idx) */ count(l_name) from hr.mig_table t where id > 0;

18) 재통계정보 조회 (구간확인)

(1) system level

<sys sess> 

select * from v$sysstat where name = 'table fetch continued row';

 

(2) session level

select to_char(sysdate,'yyyy-mm-dd hh24:ss:ss') day, a.sid, vss.username, a.name, a.value
from ( select vst.sid,vst.value,vsn.name,vsn.statistic#
       from v$statname vsn, v$sesstat vst 
       where vsn.statistic# = vst.statistic# 
       order by vst.value desc) a, v$session vss
where a.sid = vss.sid
and vss.username = 'HR'
and a.name =  'table fetch continued row';

 --> value값이 늘어나지 않음을 확인

 

---

 

# 테이블 재배치 시 처리과정

 

1. 테이블 위치 및 관련 object 용량 및 블록 등 정보 조회

<sys sess>

select owner, segment_name, segment_type, tablespace_name, bytes, blocks
from dba_segments
where segment_name in ('MIG_TABLE', 'MIG_TABLE_IDX');

-- BLOCKS : HW 뒤에 있는 BLOCK들도 집계

-- extent안에 있는 블록 수를 체크 (HW무시)

 

2. 해당 테이블스페이스의 Free공간 조회 ★

select round(sum(bytes)/1024/1024)mb 
from dba_free_space 
where tablespace_name = 'EXAMPLE';

3. 테이블을 다른 테이블 스페이스로 이동 ★

alter table hr.mig_table move tablespace example;

 

-- 이동방법이 다름 --

 

4. 인덱스를 다른 테이블 스페이스로 이동 ★

alter index hr.mig_table_IDX rebuild online tablespace example;

 

5. 테이블 및 관련 object 용량 및 블록 등 정보 조회

select owner, segment_name, segment_type, tablespace_name, bytes, blocks
from dba_segments
where segment_name in ('MIG_TABLE', 'MIG_TABLE_IDX');

-- tbs : example로 변경

 

6. 오브젝트 번호조회

select object_id, data_object_id from dba_objects where object_name = 'MIG_TABLE';

--> data_object_id가 move를 통해서 변경된 모습

 

7. 인덱스상태정보 조회

select index_name, status, blevel from dba_indexes where table_name = 'MIG_TABLE';

---

 

# 테이블 통계수집

exec dbms_stats.gather_table_stats('HR', 'MIG_TABLE');

또는

analyze table hr.mig_table compute statistics;

--> row chaining, row migration정보도 함께 수집

 

select tablespace_name, num_rows, blocks, avg_row_len, chain_cnt, to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss')
from dba_tables
where table_name = 'MIG_TABLE';

 

--> 통계 수집 후 블록 수를 확인하면 BLOCK = HW까지 사용한 블록 수

--> last_analyzed = 최근 통계 시점

--> ★ last_analyzed 기준으로 실행계획을 세우기 때문에 저 시점과 현재의 row가 많이 차이난다면 통계수집을 해주어야한다