1. Oracle Block 개요
2. Oracle Data Block 상세 구조
3. PCTFREE 와 PCTUSED
4. ROW DATA 와 ROW CHAINING & ROW MIGRATION
5. Extent 와 Segment
6. Free List Management (FLM) 기법을 사용한 Extent 관리
7. Automatic Segment Space Management (ASSM) 기법을 사용한 Extent 관리
실습 Row Chaining 확인하기
SYS> select owner, table_name, tablespace_name from dba_tables where owner = 'SCOTT' and table_name = 'EMP' ;
SYS> exec dbms_stats.gather_table_stats('SCOTT', 'EMP') ;
SYS> select num_rows, chain_cnt from dba_tables where table_name = 'EMP' ;
위 결과에서 chain_cnt 부분이 row chaining 의 값이다.
만약 이 값이 높을 경우 해당 테이블에 row chaining 이 많이 발생하고 있다는 의미이므로
해당 테이블이 저장되어 있는 Tablespace 의 Block 크기를 재 조정하는 것과
해당 Table 을 Reorg 하는 것을 심각하게 고려해야 한다.
해당 Table 을 Reorg 하는 방법은 아래와 같이 Table 을 move 하면 된다.
SYS>alter table scott.emp move tablespace example ;
SYS>select owner, table_name, tablespace_name
2 from dba_tables
3 where owner = 'SCOTT'
4 and table_name = 'EMP' ;
SYS>exec dbms_stats.gather_table_stats('SCOTT', 'EMP') ;
SYS>select table_name, index_name, status
2 from dba_indexes
3 where table_name = 'EMP' ;
SYS>alter index scott.pk_emp rebuild ;
* rebuild 시 locking 현상 방지
SYS>alter index scott.pk_emp rebuild online ;
SYS>exec dbms_stats.gather_table_stats('SCOTT', 'EMP') ;
SYS>select table_name, index_name, status
2 from dba_indexes
3 where table_name = 'EMP' ;
table reorg 후 인덱스 rebuild 해야 한다는 사실을 기억하세요.
SYS>alter index scott.idx_emp_ename rebuild ;
SYS>exec dbms_stats.gather_table_stats('SCOTT', 'EMP') ;
SYS>select table_name, index_name, status
2 from dba_indexes
3 where table_name = 'EMP' ;
SYS>alter table scott.emp allocate extent (size 128k) ;
SYS>analyze table scott.emp compute statistics ;
SYS>select num_rows, blocks, empty_blocks as empty
2 from dba_tables
3 where table_name='EMP' ;
SYS>create table scott.assm
2 ( no number,
3 name varchar2(10),
4 addr varchar2(10)) ;
SYS>begin
2 for i in 1..10000 loop
3 insert into scott.assm
4 values(i, dbms_random.string('A', 9), dbms_random.string('B', 9)) ;
5 end loop ;
6 commit ;
7 end ;
8 /
SYS>select count(*) from scott.assm ;
SYS>set serveroutput on ;
SYS>declare
2 p_owner varchar2(30) := 'SCOTT' ;
3 p_segname varchar2(30) := 'ASSM' ;
4 p_segtype varchar2(30) := 'TABLE' ;
5 p_partition_name varchar2(30) := null ;
6 l_total_blocks number ;
7 l_total_bytes number ;
8 l_unused_blocks number ;
9 l_unused_bytes number ;
10 l_last_used_extent_file_id number ;
11 l_last_used_extent_block_id number ;
12 l_last_used_block number ;
13 l_unformatted_blocks number ;
14 l_unformatted_bytes number ;
15 l_fs1_blocks number ;
16 l_fs1_bytes number ;
17 l_fs2_blocks number ;
18 l_fs2_bytes number ;
19 l_fs3_blocks number ;
20 l_fs3_bytes number ;
21 l_fs4_blocks number ;
22 l_fs4_bytes number ;
23 l_full_blocks number ;
24 l_full_bytes number ;
25 BEGIN
26 /*blocks space*/
27 DBMS_SPACE.SPACE_USAGE(
28 segment_owner => p_owner,
29 segment_name => p_segname,
30 segment_type => p_segtype,
31 partition_name => p_partition_name,
32 unformatted_blocks => l_unformatted_blocks,
33 unformatted_bytes => l_unformatted_bytes,
34 fs1_blocks => l_fs1_blocks,
35 fs1_bytes => l_fs1_bytes,
36 fs2_blocks => l_fs2_blocks,
37 fs2_bytes => l_fs2_bytes,
38 fs3_blocks => l_fs3_blocks,
39 fs3_bytes => l_fs3_bytes,
40 fs4_blocks => l_fs4_blocks,
41 fs4_bytes => l_fs4_bytes,
42 full_blocks => l_full_blocks,
43 full_bytes => l_full_bytes) ;
44 DBMS_OUTPUT.PUT_LINE(RPAD('OWNER',40,'.')||p_owner) ;
45 DBMS_OUTPUT.PUT_LINE(RPAD('SEGMENT_NAME',40,'.')||p_segname) ;
46 DBMS_OUTPUT.PUT_LINE(RPAD('SEGMENT_TYPE',40,'.')||p_segtype) ;
47 DBMS_OUTPUT.PUT_LINE(RPAD('PARTITION_NAME',40,'.')||p_partition_name) ;
48 DBMS_OUTPUT.PUT_LINE(RPAD('*',0,'*')) ;
49 DBMS_OUTPUT.PUT_LINE(RPAD('unformatted_blocks',40,'.')||l_unformatted_blocks) ;
50 DBMS_OUTPUT.PUT_LINE(RPAD('unformatted_bytes',40,'.')||l_unformatted_bytes) ;
51 DBMS_OUTPUT.PUT_LINE(RPAD('fs1_blocks : 0 to 25% free space',40,'.')||l_fs1_blocks) ;
52 DBMS_OUTPUT.PUT_LINE(RPAD('fs1_bytes : 0 to 25% free space',40,'.')||l_fs1_bytes) ;
53 DBMS_OUTPUT.PUT_LINE(RPAD('fs2_blocks : 25 to 50% free space',40,'.')||l_fs2_blocks) ;
54 DBMS_OUTPUT.PUT_LINE(RPAD('fs2_bytes : 25 to 50% free space',40,'.')||l_fs2_bytes) ;
55 DBMS_OUTPUT.PUT_LINE(RPAD('fs3_blocks : 50 to 75% free space',40,'.')||l_fs3_blocks) ;
56 DBMS_OUTPUT.PUT_LINE(RPAD('fs3_bytes : 50 to 75% free space',40,'.')||l_fs3_bytes) ;
57 DBMS_OUTPUT.PUT_LINE(RPAD('fs4_blocks : 75 to 100% free space',40,'.')||l_fs4_blocks) ;
58 DBMS_OUTPUT.PUT_LINE(RPAD('fs4_bytes : 75 to 100% free space',40,'.')||l_fs4_bytes) ;
59 DBMS_OUTPUT.PUT_LINE(RPAD('full_blocks',40,'.')||l_full_blocks) ;
60 DBMS_OUTPUT.PUT_LINE(RPAD('full_bytes',40,'.')||l_full_bytes) ;
61 END ;
62 /
위 결과를 보면 fs2 상태의 블록이 1개, fs4 상태의 블록이 3개, full 상태의 블록이 39개가 있다는 것을 알 수 있다.
ASSM 방식으로 segment 를 생성할 경우에는 위의 방법으로 각 Block 들의 상태를 조회할 수 있다.