Wednesday, June 30, 2010

Fragmentasi di Tablespace Oracle

Oleh : Achmad Faisol


Database Oracle menyarankan apabila kita mempunyai table dengan intensitas penambahan record (insert) tinggi, maka buatlah tablespace baru untuk menampung tabel tersebut. Apa yang terjadi bila kita tidak menuruti saran ini?

1. Buka SQL window I lalu login sebagai system.

SQL> connect system/inix2009
SQL> create tablespace MARKETING
datafile 'C:\oracle\product\10.1.0\oradata\marketing.dbf'
size 5M autoextend on next 5M maxsize 100M
extent management local uniform size 64k;

=>Ukuran extent di tablespace MARKETING tersebut sebesar 64 KB (8 block karena 1 block = 8 KB). Hal ini bisa dirubah sesuai kebutuhan.

SQL> alter user scott quota unlimited on MARKETING;

2. Buka SQL window II lalu login sebagai scott. Di kasus ini scott membuat 3 tabel baru, yaitu tabel tes1, tes2, dan tes3 yang diletakkan di tablespace MARKETING. Penambahan record di tabel tes1 sangat tinggi. Apa dampak yang terjadi bila sebuah tabel dengan intensitas penambahan row sangat tinggi dicampur dengan tabel-tabel lain dalam sebuah tablespace?

SQL> connect scott/tiger
SQL> create table tes1 (f1 char(300),f2 char(300))
tablespace MARKETING;
SQL> create table tes2 (f1 char(300),f2 char(300))
tablespace MARKETING;
SQL> create table tes3 (f1 char(300),f2 char(300))
tablespace MARKETING;

SQL> insert into tes1 values('a1','b1');
SQL> insert into tes2 values('a2','b2');
SQL> insert into tes3 values('a3','b3');
SQL> commit;

Catatan : tipe data yang digunakan char, bukan varchar2 dengan tujuan agar berapa pun karakter yang dimasukkan tetap akan dihitung 300 karakter oleh Oracle (dengan penambahan spasi kosong).

3. Klik SQL window I (system), lihat pemakaian masing-masing tabel.

SQL> connect system/inix2009
SQL> column segment_name format a7
SQL> column extents format 99
SQL> column blocks format 99
SQL> select segment_name,extents,blocks,bytes
from dba_segments
where owner='SCOTT'
and tablespace_name='MARKETING';

SEGMENT EXTENTS BLOCKS BYTES
------- ------- ------ ----------
TES1 1 8 65536
TES2 1 8 65536
TES3 1 8 65536

Lihat alokasi extents di tablespace MARKETING.

SQL> select segment_name,extent_id,block_id,blocks
from dba_extents
where tablespace_name='MARKETING';

SEGMENT EXTENT_ID BLOCK_ID BLOCKS
------- ---------- ---------- ------
TES1 0 17 8
TES2 0 25 8
TES3 0 41 8


Terlihat bahwa masing-masing tabel menempati 1 buah extent dengan extent_id = 0. Oracle memulai perhitungan dari 0, jadi bila menempati 2 buah extents, maka extent_id = 0 dan 1. Perlu diingat lagi bahwa Oracle memesan tempat di tablespace dalam satuan extent. Adapun 1 extent di tablespace MARKETING sebesar 64 KB (8 block).

Adapun urutan block sebagai berikut :

Tabel tes1 dimulai dari block ke-17, menempati 8 block (1 extent).
Tabel tes2 dimulai dari block ke-25, menempati 8 block. Hal ini berarti tabel tes2 menempati extent setelah tabel tes1.
Tabel tes3 dimulai dari block ke-41, menempati 8 block. Hal ini berarti tabel tes2 menempati extent setelah tabel tes2

Dengan demikian ke-3 tabel tersebut beriringan ketika menempati ruang di tablespace MARKETING (tes1 – tes2 – tes3). Kalau diibaratkan rumah, maka ke-3 tabel tersebut bertetangga. Bagaimana bila record di tabel tes1 terus bertambah? Bagaimana susunan letak penyimpanan di tablespace MARKETING?

4. Klik SQL window II (scott), lalu lakukan transaksi penambahan record di tabel tes1 dengan jumlah banyak.

SQL> set autocommit on;
SQL> begin
for i in 1..300 loop
insert into tes1 values('a1','b1');
end loop;
end;
/

SQL> select count(*) from tes1;

COUNT(*)
----------
301

5. Klik SQL window I (system), lalu lihat alokasi extent di tablespace MARKETING.

SQL> select segment_name,extent_id,block_id,blocks
from dba_extents
where tablespace_name='MARKETING';

SEGMENT EXTENT_ID BLOCK_ID BLOCKS
------- ---------- ---------- ------
TES1 0 17 8
TES1 1 49 8
TES1 2 57 8
TES1 3 65 8
TES2 0 25 8
TES3 0 41 8


Terlihat bahwa sekarang tabel tes1 menempati 4 extent dengan extent_id = 0, 1, 2 dan 3. Adapun susunan block masing-masing tabel sebagai berikut :

Block ke-17 sd 24 : tabel tes1
Block ke-25 sd 32 : tabel tes2
Block ke-41 sd 48 : tabel tes3
Block ke-49 sd 56 : tabel tes1
Block ke-57 sd 64 : tabel tes1

Terlihat bahwa terjadi fragmentasi table TES1. Adapun alokasi extent di tablespace MARKETING sebagai berikut:

table : TES1 – TES2 – TES3 – TES1 – TES1
extent_id: 0 – 0 – 1 – 2 – 3

Kondisi ini akan menjadi semakin tidak baik bila ada banyak table di tablespace MARKETING dengan tingkat penambahan record tinggi. Hal ini menyebabkan ketidakurutan posisi extent (terjadi fragmentasi).

Oleh karena itu, bila sebuah table memang sering diinsert, letakkan table tersebut di sebuah tablespace khusus (tablespace tersebut tidak dipakai untuk segment lain).

Bila sudah terlanjur terjadi seperti kasus di atas, maka table TES1 bisa dipindah ke tablespace baru, misal tablespace MKT. Adapun perintah SQL untuk memindah table TES1 ke tablespace MKT adalah:

SQL> alter table tes1 move tablespace mkt;

1 comment:

  1. waduh, lama g pake oracle...
    inixindo surabaya bisa sertifikasi vb.net g?

    ReplyDelete