Ada
banyak cara yang dapat dan harus dilakukan agar proses server database
dapat berlangsung cepat. Dalam pengembangan aplikasi database, hal ini
biasa disebut dengan istilah performance tuning, karena berkenaan dengan
kinerja/performance database. Dalam Oracle, dikenal banyak tuning untuk
mendapatkan kinerja yang optimal. Beberapa di antaranya adalah instant
tuning, memory tuning, sql tuning, dan sebagainya.Keberhasilan
pengembangan aplikasi database yang besar acapkali menjadi terkesan
kurang dihargai, apabila ternyata database tersebut relatif lambat
memberikan data yang diperlukan. Sebenarnya hal ini dapat dimaklumi
apabila kita memahami internal database. Misalkan, memilih seratus data
tertentu dari satu juta data yang tersimpan di dalam database, tentu
saja akan membutuhkan waktu yang lama. Namun demikian, sebagian besar
user tidak memahami hal internal database. Yang mereka inginkan database
tersebut harus cepat memberikan data yang dicari apapun kriterianya.
Ada banyak cara yang dapat dan harus dilakukan agar proses server
database dapat berlangsung cepat. Dalam pengembangan aplikasi database
biasa disebut dengan istilah performance tuning. Dalam Oracle dikenal
banyak tuning untuk mendapatkan kinerja yang optimal. Beberapa di
antaranya adalah instant tuning, memory tuning, sql tuning, dan
sebagainya. Artikel ini membahas sebagian dari sql tuning.Sebagian besar
dari SQL yang digunakan di dalam pengembangan aplikasi basis data
adalah perintah SELECT yang ditujukan untuk menarik/mengambil sejumlah
baris data dari suatu tabel. Misalkan, SELECT * FROM T1 berarti "tarik
semua data - ditandai dengan * - dari tabel T1". Contoh lain: SELECT
DATA1, DATA2 FROM T2 berarti "tarik DATA1 dan DATA2 dari tabel T2".
Mengingat SELECT merupakan perintah SQL yang paling banyak digunakan,
maka artikel ini memfokuskan pada optimalisasi perintah SELECT untuk
mendapatkan kinerja yang paling optimal yang antara lain ditandai dengan
kecepatan akses/proses.
SELECT memerlukan waktu pemrosesan yang panjang dan sangat
tergantung pada klausul SELECT yang digunakan. Untuk mempercepatnya,
setiap programmer harus memperhatikan teknik-teknik optimalisasi SQL.
1. Pemilihan Metode Akses
Dalam mengambil/menarik data dari suatu table, SQL melakukan salah satu dari alternatif berikut:
- Pengaksesan secara bertahap (sequential access). Teknik ini juga
sering direferensikan sebagai full table scanning, karena SQL akan
mengambil data yang diinginkan dengan lebih dulu merunut/melacak data
satu demi satu baris, dimulai dari record/baris pertama. Apabila proses
ini melibatkan tabel dengan jumlah record yang banyak, maka metode ini
tidak akan efisien, karena membutuhkan waktu yang lama. Contoh: SELECT *
FROM T_PROFILE_ANUMBER.
- Pengaksesan secara langsung (direct access). Dalam teknik ini, SQL
hanya membaca baris/data yang diminta. Metode ini biasa juga disebut
access by index, karena untuk dapat melakukan ini diperlukan indeks pada
kolom yang digunakan untuk kriteria pencarian. Dengan pembacaan
langsung pada baris yang dibutuhkan, maka metode ini membutuhkan waktu
yang lebih cepat dari sequential access. Contoh: SELECT * FROM
T_PROFILE_ANUMBER WHERE ANUMBER = '0215215522', di mana kolom ANUMBER
merupakan kolom yang diindex.
2. Pembuatan Index
Pencarian data menggunakan SELECT biasanya dengan menggunakan
kriteria pencarian menggunakan klausa WHERE, atau HAVING. Contoh berikut
menunjukkan adanya spesifikasi pencarian dengan batasan K3 > 5:
SELECT * FROM T1 WHERE K3 > 5
Kolom yang akan digunakan dalam spesifikasi pencarian data perlu
dibuat index agar dapat menggunakan metode direct access. Dalam contoh
di atas, maka proses SELECT akan lebih cepat memberikan hasil apabila
dibuatkan index pada kolom K3 dari tabel T1.
Contoh lain, misalkan suatu tabel T1 mempunyai kolom (K1, K2, K3,
K4). Apabila dalam proses SELECT nantinya akan lebih banyak menggunakan
kolom K4 sebagai batasan/kriteria pencarian, maka disarankan untuk
membuat index dari kolom K4.
Cara pembuatan index dapat menggunakan perintah sebagai berikut:
CREATE {UNIQUE} INDEX nama_indeks
ON identifikator_tabel ((nama_kolom {ASC|DESC}), ...);
CREATE INDEX T1_K4_NDX
ON T1(K4);
Index dapat dibuat untuk satu kolom atau gabungan dari beberapa kolom.
Berikut pedoman untuk pemilihan kolom yang perlu dibuatkan index:
- Index unik pada kolom kunci utama (primary key)
- Index pada kolom kunci asing (foreign key)
- Index pada kolom yang digunakan sebagai kriteria pemilihan
- Index pada kolom yang digunakan untuk pengurutan (order by)
- Index pada kolom yang digunakan untuk pengelompokan (group by)
3. Perbaikan Pernyataan SELECT
3.1. Hindari Penggunaan SELECT *
Fokuslah hanya pada kolom yang memang benar-benar dibutuhkan.
Misalkan diasumsikan bahwa tabel T_CALLS memiliki kolom (ANUMBER,
BNUMBER, TGLJAM, DURASI, TRIN, TROUT), maka jika diinginkan informasi
panggilan dari A ke B dan durasinya maka dapat dilakukan dengan perintah
sebagai berikut:
SELECT * FROM T_CALLS;
Perintah SQL di atas akan lebih efisien jika dirubah menjadi:
SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
3.2. Gunakan Kriteria
Hindari pengaksesan seluruh data, tetapi fokuslah pada kriteria data
yang diperlukan sesuai kebutuhan. Contoh apabila dibutuhkan informasi
ke Jakarta maka:
SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS;
Perintah SQL di atas akan lebih efisien, jika dirubah menjadi:
SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE SUBSTR(BNUMBER, 1, 3) = '021';
3.3. Hindari OR, gunakan UNION
Operator OR membutuhkan waktu yang banyak. Bila memungkinkan, gunakan operator UNION yang setara.
SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE SUBSTR(BNUMBER, 1, 3) = '021' OR
SUBSTR(BNUMBER, 1, 3) = '022';
Perintah SQL di atas akan lebih efisien jika dirubah menjadi:
SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE SUBSTR(BNUMBER, 1, 3) = '021'
UNION
SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE SUBSTR(BNUMBER, 1, 3) = '022';
3.4. Hindari NOT
Hindari operator NOT dan ganti dengan operator perbandingan bila memungkinkan:
SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE NOT (DURASI > 100);
Perintah SQL di atas akan lebih efisien jika dirubah menjadi:
SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE (DURASI <= 100);
3.5. Hindari Ekspresi Matematika di Sisi Kiri Operand
Bila kriteria pencarian memerlukan ekspresi matematika, maka
posisikan ekspresi tersebut pada sebelah kanan operator jika
memungkinkan.
SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE (DURASI + 40 > 100);
Perintah SQL di atas akan lebih efisien jika dirubah menjadi:
SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE (DURASI > 60);
3.6. Hindari Operator IN
Penggunaan operator IN dalam kriteria pencarian merupakan pernyataan
SELECT yang mahal, sehingga perlu dihindari. Berikut cara
menghindarinya untuk dua bentuk penggunaan IN.
Penggunaan operator IN bentuk pertama:
SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE SUBSTR(ANUMBER, 1, 3) IN ('021', '022');
Perintah SQL di atas akan lebih efisien jika dirubah menjadi:
SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE SUBSTR(ANUMBER, 1, 3) = '021'
OR SUBSTR(ANUMBER, 1, 3) = '022';
Untuk selanjutnya, perhatikan paragraf 3.3 untuk mengindari penggunaan operator OR.
Penggunaan operator IN bentuk kedua:
SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE SUBSTR(ANUMBER, 1, 3)
IN (SELECT KODEAREA FROM T_AREACODES);
Perintah SQL di atas akan lebih efisien jika dirubah menjadi:
SELECT T1.ANUMBER, T1.BNUMBER, T1.DURASI
FROM T_CALLS T1, T_AREACODES T2
WHERE SUBSTR(T1.ANUMBER, 1, 3) = T2.KODEAREA;
3.7. Hindari Operator EXIST
Sama halnya dengan EXIST, bila memungkinkan hindari penggunaan operator ini.
SELECT A.ANUMBER, A.BNUMBER, A.DURASI FROM T_CALLS A
WHERE EXIST
(SELECT B.KODEAREA FROM T_AREACODES B
WHERE B.KODEAREA = SUBSTR(A.ANUMBER, 1, 3));
Perintah SQL di atas akan lebih efisien jika dirubah menjadi:
SELECT A.ANUMBER, A.BNUMBER, A.DURASI
FROM T_CALLS A, T_AREACODES B
WHERE SUBSTR(A.ANUMBER, 1, 3) = B.KODEAREA;
3.8. Hindari Operator LIKE
Penggunaan operator LIKE akan menyebabkan full table scanning. Oleh
karena itu, perlu dirubah dengan ekspresi yang sama bila memungkinkan.
SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE ANUMBER LIKE '021%';
Perintah SQL di atas akan lebih efisien jika dirubah menjadi:
SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE ANUMBER >= '021' AND ANUMBER < '022';
3.9. Urutan Nama Kolom
Penulisan nama kolom disarankan menggunakan urutan sebagaimana kolom
tersebut didefinisikan di dalam tabel. Misalkan tabel berikut mempunyai
kolom dengan urutan sebagaimana tertulis: T1(K1, K2, K3, K4, K5) di
mana terdapat composite index untuk kolom K4 dan K5;
SELECT K2, K5, K3 FROM T1;
Perintah SQL di atas akan lebih efisien jika dirubah menjadi:
SELECT K2, K3, K5 FROM T1;
Pada penggunaan index dari beberapa kolom (composite index)
sebutkan nama kolomnya dalam urutan yang benar sesuai dengan urutan
kolom tersebut di dalam tabelnya.
SELECT K2, K3, K5 FROM T1
WHERE K5 > 50 AND K4 = '0215215522';
Perintah SQL di atas akan lebih efisien jika dirubah menjadi:
SELECT K2, K3, K5 FROM T1
WHERE K4 = '0215215522' AND K5 > 50;
3.10. Gunakan Konsensus Penulisan SQL
SQL merupakan bahasa pemrograman tingkat tinggi generasi keempat.
Secara internal, Oracle akan mengolah lebih dulu setiap pernyataan SQL
untuk mendapatkan hasil yang diinginkan. Jika Oracle menemukan suatu
pernyataan SQL yang dimasukkan pertama kali, maka Oracle akan mengolah
pernyataan tersebut menjadi pernyataan perintah low level yang
dimengerti oleh server database untuk mendapatkan hasilnya. Perintah low
level ini akan didaftarkan oleh Oracle pada suatu area memory khusus
yang disebut LIBRARY CACHE untuk keperluan lebih lanjut. Jika suatu saat
pernyataan SQL yang sama muncul lagi, maka Oracle tidak akan merubah
SQL menjadi low level, namun menggunakan perintah low level yang sudah
ada terdaftar pada LIBRARY CACHE. Dengan demikian, mengurangi proses
translasi dari bahasa tingkat tinggi ke bahasa low level.
Untuk itu, disarankan paling tidak dua hal:
- Penggunaan pernyataan SQL yang sama. Di sinilah perlunya konsensus
cara penulisan SQL termasuk penggunaan huruf besar dan kecil.
- Penggunaan bind variable.
