Back to IF3140 Sistem Basis Data
Topic
Questions/Cues
Mengapa sebuah query perlu di-tuning?
Apa itu Optimizer Hints dan Set Orientation?
Apa masalah dari transaksi yang berjalan lama?
Bagaimana cara menangani transaksi yang panjang?
Query Tuning: Membantu Sang Optimizer
Terkadang, query optimizer tidak memilih rencana eksekusi (execution plan) yang terbaik. Ini bisa terjadi karena statistik database sudah usang atau query-nya terlalu kompleks (misalnya, mengandung subquery bersarang). Untuk mengatasi ini, kita bisa:
Melihat Rencana Eksekusi: Menggunakan perintah
EXPLAINuntuk melihat rencana yang dipilih oleh optimizer.Memperbarui Statistik: Menjalankan perintah
ANALYZEuntuk menghitung ulang statistik agar optimizer memiliki data yang akurat.Menulis Ulang Query: Mengubah query kompleks, misalnya dengan mengganti subquery menjadi
JOIN, agar lebih mudah dioptimalkan.Menggunakan Optimizer Hints: Menyisipkan instruksi khusus di dalam teks SQL (seperti
/*+ INDEX(...) */) untuk “memaksa” optimizer menggunakan indeks atau metode tertentu.
Teknik Set Orientation
Set Orientation adalah prinsip untuk meminimalkan jumlah panggilan ke database dari aplikasi. Daripada menjalankan satu query sederhana berulang kali di dalam sebuah loop dengan parameter berbeda, lebih baik menulis satu query yang lebih kompleks (misal, dengan
GROUP BY) yang dapat mengambil semua data yang dibutuhkan dalam satu kali panggilan.Transaction Tuning: Menangani Transaksi Panjang
Transaksi yang berjalan sangat lama, baik read-only maupun update, dapat menyebabkan masalah serius:
Transaksi Read-Only Panjang: Menyebabkan kontensi kunci (lock contention), di mana ia “mengunci” data sehingga menghalangi transaksi update lain yang ingin mengubah data yang sama.
Transaksi Update Panjang: Dapat menghabiskan ruang untuk lock dan log, serta secara drastis memperlama waktu pemulihan (recovery) jika sistem crash.
Solusi untuk Transaksi Panjang
Untuk Read Panjang: Gunakan multi-version concurrency control (MVCC) seperti “snapshots” di Oracle yang tidak memerlukan lock, atau gunakan level konsistensi yang lebih rendah (meskipun hasilnya bisa tidak presisi).
Untuk Update Panjang: Gunakan teknik mini-batch transactions, yaitu memecah satu transaksi update besar menjadi beberapa transaksi kecil yang dieksekusi secara berurutan. Ini mengurangi durasi lock dan dampak jika terjadi kegagalan.
Tuning pada level aplikasi fokus pada perbaikan kode yang berinteraksi dengan database. Query Tuning melibatkan analisis dan perbaikan query melalui
EXPLAIN,ANALYZE, penulisan ulang, dan penggunaan hints untuk membantu optimizer, serta menerapkan set orientation untuk mengurangi panggilan ke database. Transaction Tuning bertujuan mengatasi masalah transaksi yang berjalan lama, dengan menggunakan MVCC untuk transaksi baca dan mini-batches untuk transaksi update guna mengurangi lock contention dan risiko kegagalan sistem.
Additional Information (Optional)
Loop di Aplikasi: “Musuh” Performa Database
Salah satu anti-pattern paling umum yang dilakukan oleh programmer aplikasi adalah melakukan query di dalam sebuah loop. Contoh: mengambil daftar ID produk, lalu melakukan looping untuk mengambil detail setiap produk satu per satu. Setiap iterasi loop adalah satu kali perjalanan pulang-pergi (round trip) ke database, yang sangat tidak efisien. Teknik set orientation memperbaiki ini dengan mengambil semua detail produk yang dibutuhkan hanya dalam satu query menggunakan klausa
WHERE product_id IN (...)atauJOIN.Idempotency dalam Mini-Batches
Saat mengimplementasikan mini-batch transactions, penting untuk merancang setiap batch agar bersifat idempoten. Artinya, jika sebuah batch dijalankan lebih dari satu kali karena proses recovery setelah kegagalan, hasilnya akan tetap sama seperti jika dijalankan sekali. Ini mencegah masalah seperti penambahan stok barang yang sama berulang kali jika sistem crash di tengah-tengah proses batch.