by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

Demo Scripts




스터디 의문사항

1. 스터디 진행 중 Index ROW 를 삭제 후 INSERT 했을때 현상

스터디 진행 중 Index ROW 를 삭제 후 INSERT 했을때 현상은
로그는 정상적이 었습니다.
저희가 미처 체크 못하는 이유가 숨어 있는듯..
아래는 욱짜님 블로그 링크 http://sites.google.com/site/ukja/oracle-is-madr/index-space-reuse
[ 오타가 많네요ㅡ_ㅡ; ]

SQL> drop table t_index purge;
SQL> create table t_index(id int, name varchar(100));
SQL> create index t_index_idx on t_index(id);
SQL> insert into t_index select rownum, object_name from all_objects where rownum <= 1000 ;
SQL> commit;

SQL> exec tree_dump2(v_owner=>user, v_name=>'t_index_idx');
PL/SQL procedure successfully completed.

----- begin tree dump
branch: 0x40fb82 4258690 (0: nrow: 2, level: 1)
leaf: 0x40fb83 4258691 (-1: nrow: 538 rrow: 538)
leaf: 0x40fb84 4258692 (0: nrow: 462 rrow: 462)
----- end tree dump

– delete 2 row and get index tree dump
SQL> delete from t_index where id <= 2;

2 rows deleted.

SQL> commit ;

Commit complete.

SQL> exec tree_dump2(v_owner=>user, v_name=>'t_index_idx');

PL/SQL procedure successfully completed.

----- begin tree dump
branch: 0x40fb82 4258690 (0: nrow: 2, level: 1)
leaf: 0x40fb83 4258691 (-1: nrow: 538 rrow: 536) <== rrow 가 538 에서 536 으로 변경됨
leaf: 0x40fb84 4258692 (0: nrow: 462 rrow: 462)
----- end tree dump

– insert additional big value and get index tree dump
SQL> insert into t_index values(400, 'name');
1 row created.

SQL> commit ;
Commit complete.

SQL> exec tree_dump2(v_owner=>user, v_name=>'t_index_idx');

PL/SQL procedure successfully completed.

----- begin tree dump
branch: 0x40fb82 4258690 (0: nrow: 2, level: 1)
leaf: 0x40fb83 4258691 (-1: nrow: 537 rrow: 537) <=== 특이하게 신규로 1row 가 insert 되면서
nrow 가 538 => 537, rrow 536 => 537 변경됨
leaf: 0x40fb84 4258692 (0: nrow: 462 rrow: 462)
----- end tree dump

– insert additional small value and get index tree dump
SQL> insert into t_index values(-1, 'name');

1 row created.

SQL> commit ;

Commit complete.

SQL> exec tree_dump2(v_owner=>user, v_name=>'t_index_idx');

PL/SQL procedure successfully completed.

----- begin tree dump
branch: 0x40fb82 4258690 (0: nrow: 2, level: 1)
leaf: 0x40fb83 4258691 (-1: nrow: 538 rrow: 538)
leaf: 0x40fb84 4258692 (0: nrow: 462 rrow: 462)
----- end tree dump

음 여기서 반대로 한번 해보자 우선 -1 을 입력 했을때 400 을 입력 했을 때처럼
아래 현상이 발생 되는지 보자
==> 특이하게 신규로 1row 가 insert 되면서 nrow 가 538 => 537, rrow 536 => 537 변경됨
마찬가지 이다.
즉 2 rows 를 삭제 한 상태에서 400 을 입력 하던지 -을 입력 하던지
leaf: 0x40fb83 4258691 (-1: nrow: 538 rrow: 536)
===> 아래처럼 되는것을 확인 하였다.
leaf: 0x40fb83 4258691 (-1: nrow: 537 rrow: 537)
왜 그런지는 모르겠음 ㅡ_ㅡ;

2. dba_tables.blocks vs dba_segments.blocks

차이를 못찾겠네요 ㅡ_ㅡ;
더 찾아보구 Update 하겠습니다.

2.1 dba_Tables 에서 blocks

BLOCKS* NUMBER Number of used data blocks in the table
즉, Table 내에서 사용된 데이타 블록의 갯수

SQL> @gather T_INDEX

PL/SQL procedure successfully completed.

SQL> select table_name, blocks from dba_tables where table_name ='T_INDEX';

TABLE_NAME BLOCKS
------------------------------ ----------
T_INDEX 4
==> 4개의 Block 으로 구성되었다.

2.2 dba_segments 에서 blocks

BLOCKS NUMBER Size, in Oracle blocks, of the segment
즉, 세그먼크의 사이즈

SQL> select segment_name, blocks, EXTENTS, bytes from dba_segments
where segment_name ='T_INDEX';

SEGMENT_NAME BLOCKS EXTENTS BYTES
------------------------------ ---------- ---------- ----------
T_INDEX 8 1 65536

==> 8k size Block 8개로 이루어지 Extent 하나로 구성된 Table

2.1 과 2.2 가 다른데.. 왜 이런 차이가 나오는지.. 개념적으로 차이가 있는것 같은데..

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. 4월 30, 2009

    김종원 says:

    ALL_TABLES BLOCKS* NUMBER Number of used data blocks in the table ==> Optimiz...

    ALL_TABLES
    BLOCKS* NUMBER Number of used data blocks in the table
    ==> Optimizer 가 사용하는 정보, 실제 사용하고 있는 Block Count [ User Block Count ]
    통계정보 수집 시에 Update 된다.
    DBA_SEGMENTS
    BLOCKS NUMBER Size, in Oracle blocks, of the segment
    ==> extent 할당에 대한 메타 정보 [ Allocated Block Count ]
    실제 할당 되고, 반환 될때 (Truncate) Update 된다.

    • TEST -

    SYS>create table check_blocks ( a1 number ,a2 number );

    Table created.

    SYS>select table_name, blocks from user_tables where table_name ='CHECK_BLOCKS';

    TABLE_NAME BLOCKS
    ------------------------------ ----------
    CHECK_BLOCKS

    SYS >exec dbms_stats.gather_Table_stats(ownname=>user, tabname=>'check_blocks');

    PL/SQL procedure successfully completed.

    SYS> select table_name, blocks from user_tables where table_name ='CHECK_BLOCKS';

    TABLE_NAME BLOCKS
    ------------------------------ ----------
    CHECK_BLOCKS 0

    SYS> select segment_name, blocks from user_segments where segment_name ='CHECK_BLOCKS';

    SEGMENT_NAME BLOCKS
    -------------------------- ----------
    CHECK_BLOCKS 8

    SYS>begin
    2 for i in 1 .. 1000 loop
    3 insert into CHECK_BLOCKS values ( i, i) ;
    4 end loop ;
    5 commit ;
    6 end ;
    7 /

    PL/SQL procedure successfully completed.

    SYS>exec dbms_stats.gather_Table_stats(ownname=>user, tabname=>'check_blocks');

    PL/SQL procedure successfully completed.

    SYS> select table_name, blocks from user_tables where table_name ='CHECK_BLOCKS';

    TABLE_NAME BLOCKS
    ------------------------------ ----------
    CHECK_BLOCKS 2
    ==> 현재 사용하는 block 은 "2"
    SYS>select segment_name, blocks from user_segments where segment_name ='CHECK_BLOCKS';

    SEGMENT_NAME BLOCKS
    --------------------------------------------------------------------------------- ----------
    CHECK_BLOCKS 8

    ==> 현재까지 할당된 Block Size 는 8 그대로 유지 이 부분은

    SYS>begin
    for i in 1001 .. 4000 loop
    insert into CHECK_BLOCKS values ( i, i) ;
    end loop ;
    commit ;
    end ;
    /

    PL/SQL procedure successfully completed.

    SYS>exec dbms_stats.gather_Table_stats(ownname=>user, tabname=>'check_blocks');

    PL/SQL procedure successfully completed.

    SYS>select table_name, blocks from user_tables where table_name ='CHECK_BLOCKS';

    TABLE_NAME BLOCKS
    ------------------------------ ----------
    CHECK_BLOCKS 12
    ==> 사용하고 있는 Block 수가 증가

    SYS>select segment_name, blocks from user_segments where segment_name ='CHECK_BLOCKS';

    SEGMENT_NAME BLOCKS
    --------------------------------------------------------------------------------- ----------
    CHECK_BLOCKS 16
    ==> 기존에 할당된 Extent 로 수용이 불가능하여, 신규로 Extent 할당된 (16 Block )

    Extent Management local Autoallocate 시에 규칙
    Extent ID [ Extent 가 가지는 Block Count ]
    1 ~ 16 [ 8 Blocks ]
    17 ~ 79 [ 128 Blocks ]
    80 ~ 199 [ 1024 Blocks ]
    200 ~ [ 8192 Blocks ]
    SYS>truncate table check_blocks ;
    Table truncated.

    SYS>select table_name, blocks from user_tables where table_name ='CHECK_BLOCKS';
    TABLE_NAME BLOCKS
    ------------------------------ ----------
    CHECK_BLOCKS 15
    ==> 통계정보를 수집 하지 않으면, USR_TABLES.BLOCKS 는 변경되지 않는다.

    SYS>select segment_name, blocks from user_segments where segment_name ='CHECK_BLOCKS';

    SEGMENT_NAME BLOCKS
    --------------------------------------------------------------------------------- ----------
    CHECK_BLOCKS 8

    SYS>exec dbms_stats.gather_Table_stats(ownname=>user, tabname=>'check_blocks');
    PL/SQL procedure successfully completed.

    SYS>select table_name, blocks from user_tables where table_name ='CHECK_BLOCKS';

    TABLE_NAME BLOCKS
    ------------------------------ ----------
    CHECK_BLOCKS 0