Tuning dan Optimasi Performance Oracle


Performansi adalah parameter yang penting bagi aplikasi apapun. Karena tidak ada pengguna yang bersedia kompromi terhadap kinerja sistem-nya, kita harustunduk untuk mengembangkan sebuah sistem dengan sedikit waktu respon dan lebih besar throughput. Seperti semua sistem terdistribusi menggunakan database untuk mengimplementasikan logika bisnis, performa berbanding lurus dengan jumlah query yang dituning dan pengoptimalan obyek PL / SQL di database. Tuning bukan merupakan proses terpisah, ini harus dilakukan sejajar dengan SDLC (Sysem Development Life Cycle).
Oracle adalah salah satu database yang digunakan secara luas di dunia. Beberapa teknik optimasi yang dapat diterapkan untuk mengembangkan sistem yang lebih baik adalah sebagai berikut:

Hindari mismatch tipe data untuk pengindekan kolom
Kebanyakan orang menggunakan tanda kutip tunggal (dalam kondisi filter) terlepas dari tipe data yang mereka queri. Ini membuat oracle untuk melakukan internal typecast ke tipe data yang dibutuhkan.

Sebelum Optimasi Setelah Optimasi
select name,age,city,state
from employee
where employee_id=’1000′;
select name,age,city,state
from employee
where employee_id=1000;
Waktu yang dibutuhkan : 2.3 sec Waktu yang dibutuhkan : 0.3 sec

Hindari fungsi pada kolom yang diindek
Biasanya, identifikasi kolom yang paling sering di query kemudiaan dibuat index pada kolom tersebut. Tapi query kita menggunakan fungsi pada kolom yang terindeks. Ini akhirnya akan membatalkan tujuan menciptakan indeks pada kolom tersebut.

Sebelum Optimasi Setelah Optimasi
select name,age,city
from employee
where substr(employee_name,1,3)=’kar’;
select name,age,city
from employee
where employee_name like ‘kar%’;
Waktu yang dibutuhkan : 2.8 sec Waktu yang dibutuhkan : 0.3 sec

Jika kita terpaksa harus mengunakan fungsi pada query tersebut maka kita bisa membuat function based index pada kolom tersebut.

Menentukan kondisi pada WHERE bukan pada HAVING

Sebelum Optimasi Setelah Optimasi
select name,
count(1)
from employee
group by name
having name=’karthi’;
select name,
count(1)
from employee
where name=’karthi’
group by name;
Waktu yang dibutuhkan = 2.2 sec Waktu yang dibutuhkan = 0.3 sec

Ini bukan error. Jika penyaringan sebelum pengelompokan, maka semua data yang tidak perlu akan dikelompokkan dan akhirnya data yang dibutuhkan akan disaring. Menerapkan filter sebelum pengelompokan akan menghindari sortasi dan pengelompokkan yang tidak perlu.

Penggunaan join untuk mengganti inner query

Sebelum Optimasi Setelah Optimasi
select employee_name
from employee where employee_id in ( select employee_id from defaulters)
select employee_name
from employee e,
defaulters d
where e.employee_id=d.employee_id
Waktu yang dibutuhkan : 14.1 sec Waktu yang dibutuhkan : 5.5 sec

Hal ini sebenarnya dianggap sebagai praktek yang buruk pada penulisan SQL, menulis hasil inner query pada tiap-tiap baris hasil query tabel utama.

Menentukan tabel dengan ukuran paling kecil pada urutan terakhir pada query join.
Seperti yang kita lihat, menggunakan join menghasilkan hasil yang lebih baik daripada inner query, kita harus mengurutkan tabel sedemikian rupa sehingga tabel terkecil akan ditentukan pada akhir di SQL, sehingga waktu oracle untuk membandingkan baris akan berkurang.

Sebelum Optimasi Setelah Optimasi
select so.documnet_number
count(1)
from activation a,
serv_ord so,
task t
where
t.documnet_number=
so.document_number
and  so.serv_item_id=a.serv_item_id

group by so.document_number

select so.documnet_number
count(1)
from task t,
serv_ord so,
activation a,
where
t.documnet_number=
so.document_number
and  so.serv_item_id=a.serv_item_id

group by so.document_number

Waktu yang dibutuhkan : 10 Sec Waktu yang dibutuhkan : 2.1 Sec

Mengganti NOT IN dengan NOT EXISTS

Ini sangat mirip dengan menghindari sub query

Sebelum Optimasi Setelah Optimasi
Select count(1)
from task t
where t.document_number not in (
select tt.document_number from task_bkp)
select count(1)
from task t
where not exists
(select tt.document_number from task_bkp)
Waktu yang dibutuhkan : 500 Sec Waktu yang dibutuhkan : 6 Sec

Menggunkan FORALL sebagai pengganti FOR

Ini adalah pilihan yang berguna yang tersedia di oracle untuk memasukkan bulk record.

Sebelum Optimasi Setelah Optimasi
DECLARE
TYPE NumTab IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER;
TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
pnums NumTab;
pnames NameTab;
BEGIN
FOR j IN 1..20000 LOOP — load index-by tables
pnums(j) := j;
pnames(j) := ‘Part No. ‘ || TO_CHAR(j);
END LOOP;
FOR i IN 1..20000 LOOP — use FOR loop
INSERT INTO parts VALUES (pnums(i), pnames(i));
END LOOP;
END;
DECLARE
TYPE NumTab IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER;
TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
pnums NumTab;
pnames NameTab;
BEGIN
FOR j IN 1..20000 LOOP — load index-by tables
pnums(j) := j;
pnames(j) := ‘Part No. ‘ || TO_CHAR(j);
END LOOP;
FORALL I in 1 .. 20000 — use FORALL
INSERT INTO parts VALUES (pnums(i), pnames(i));
END;
Waktu yang dibutuhkan: 11.0 Sec Waktu yang dibutuhkan: 0.5 sec

FORALL akan mengurangi waktu pengulangan pada PL/SQL dan SQL.

Penggunaan BULK COLLECT

BULK COLLECT adalah suatu pilihan yang disediakan oleh Oracle untuk menghindari penggunaan loop dalam pengumpulan data dari table. Untuk aplikasi pengolahan data berat BULK COLLECT akan akan sangat berguna. Untuk contoh, kita perlu memilih 1000 baris dari tabel dan memproses baris dan masukkan ke tabel lain, maka kita dapat menggunakan BULK COLLECT.

Sebelum Optimasi Setelah Optimasi
Declare
Type bcode is table of products.barcode%TYPE;
i int;
barc bcode;
cursor cur_seq is
select barcode from products where rownum<100001;
begin
i:=0;
for cur_dta in cur_seq loop
i:=i+1;
barc:=cur_dta.barcode;
end loop;
end;
Declare
Type bcode is table of products.barcode%TYPE;
i int;
barc bcode;
begin
select barcode BULK COLLECT into barc from products where rownum<100001;
end;
Waktu yang dibutuhkan : 17sec Waktu yang dibutuhkan : 1.41 sec

Ringkasan Optimasi

  • Gunakan kode seragam di seluruh aplikasi standar
  • Hindari ketidakcocokan jenis data untuk indeks kolom
  • Hindari fungsi pada kolom indeks
  • Pindahkan kondisi dari klausa HAVING ke klausa WHERE
  • Gunakan joins bukan nested selects, jika memungkinkan
  • Mengganti Not IN dengan Not EXISTS atau OUTER JOIN
  • Gunakan bulk inserts pada insert banyak records
  • Gunkan klausa BULK COLLECT pada fetching records

2 thoughts on “Tuning dan Optimasi Performance Oracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s