Block 할당 실습

▣ Block 할당

S SYS> select count(*),sum(bytes) from dba_extents

2 where owner='SCOTT' and segment_name='X';

   

COUNT(*) SUM(BYTES)

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

1 65536

   

S SCOTT> insert into x select * from x;

   

1 row created.

   

S SCOTT> /

   

2 rows created.

   

S SCOTT> /

   

4 rows created.

…. 중 략 …..

S SCOTT> /

   

2048 rows created.

   

   

S SYS> select count(*),sum(bytes) from dba_extents

2 where owner='SCOTT' and segment_name='X';

   

COUNT(*) SUM(BYTES)

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

2 131072

   

S SYS> select blocks,bytes from dba_extents

2 where owner='SCOTT' and segment_name='X';

   

BLOCKS BYTES

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

8 65536

8 65536 => block이 부족하기 때문에 하나 더 할당 받음

   

….. 중 략 …..

   

S SCOTT> /

   

32768 rows created.

   

S SCOTT> /

   

65536 rows created.

   

S SYS> select count(*),sum(bytes) from dba_extents

2 where owner='SCOTT' and segment_name='X';

   

COUNT(*) SUM(BYTES)

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

18 3145728 => 18번 할당 받음

   

S SYS> select blocks,bytes from dba_extents

2 where owner='SCOTT' and segment_name='X';

   

BLOCKS BYTES

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

8 65536

8 65536

8 65536

8 65536

8 65536

8 65536

8 65536

8 65536

8 65536

8 65536

8 65536

   

BLOCKS BYTES

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

8 65536

8 65536

8 65536

8 65536

8 65536

128 1048576

128 1048576

※ Block을 8개씩 할당 받다가 지속적으로 할당 받아야 할 경우 128개씩 미리 할당 받기 시작한다.

   

▣ Block 증가 (심화)
 

S SCOTT> select count(*) from x;

   

COUNT(*)

----------

131072

   

S SYS> select count(*),sum(bytes) from dba_extents

2 where owner='SCOTT' and segment_name='X';

   

COUNT(*) SUM(BYTES)

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

18 3145728

S SCOTT> select distinct x from x;

   

X

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

1234567890

   

S SCOTT> update x set x=x||'a';

   

131072 rows updated.

   

S SCOTT> select distinct x from x;

   

X

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

1234567890a

   

S SYS> select count(*),sum(bytes) from dba_extents

2 where owner='SCOTT' and segment_name='X';

   

COUNT(*) SUM(BYTES)

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

18 3145728 => Free Space를 사용하고 있기 때문에 용량 변화 없음

   

S SCOTT> update x set x=x||'abcdefgh';

   

131072 rows updated.

   

S SCOTT> select distinct x from x;

   

X

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

1234567890aabcdefgh

   

S SYS> select count(*),sum(bytes) from dba_extents

2 where owner='SCOTT' and segment_name='X';

   

COUNT(*) SUM(BYTES)

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

22 7340032 => Block 할당량 증가

Block 할당 받은 이유 : update시 공간이 부족해서 migration이나 Channing이 발생

   

   

▣ X 테이블이 사용하고 있는 Block?

S SCOTT> select count(distinct substr(rowid,1,15)) from x;

   

COUNT(DISTINCTSUBSTR(ROWID,1,15))

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

290

   

▣ X 테이블의 용량

S SCOTT> select count(distinct substr(rowid,1,15))*8192 from x;

   

COUNT(DISTINCTSUBSTR(ROWID,1,15))*8192

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

2375680

   

▣ 할당된 Block

S SYS> select count(*),sum(bytes) from dba_extents

2 where owner='SCOTT' and segment_name='X';

   

COUNT(*) SUM(BYTES)

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

22 7340032

   

   

S SCOTT> truncate table x;

   

Table truncated.

   

S SCOTT> select count(distinct substr(rowid,1,15)) from x;

   

COUNT(DISTINCTSUBSTR(ROWID,1,15))

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

0

   

S SCOTT> select count(distinct substr(rowid,1,15))*8192 from x;

   

COUNT(DISTINCTSUBSTR(ROWID,1,15))*8192

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

0

   

S SYS> select count(*),sum(blocks),sum(bytes)

2 from dba_extents

3 where owner='SCOTT' and segment_name='X';

   

COUNT(*) SUM(BLOCKS) SUM(BYTES)

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

1 8 65536

   

   

S SCOTT> insert into x values('123456789');

   

1 row created.

   

S SCOTT> commit;

   

Commit complete.

   

S SCOTT> select count(distinct substr(rowid,1,15)) from x;

   

COUNT(DISTINCTSUBSTR(ROWID,1,15))

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

1

   

S SYS> select count(*),sum(blocks),sum(bytes)

2 from dba_extents

3 where owner='SCOTT' and segment_name='X';

   

COUNT(*) SUM(BLOCKS) SUM(BYTES)

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

1 8 65536 => 1/8 사용중