Simple to complex SQL tuning with TOAD

This SQL tuning ability that make TOAD 8.6 so worthy. It help beginner, like me, to ‘just watch and wait a magic AI from TOAD, abrakadabra my SQL dummy syntax :). Saya menggunakan contoh sederhana dengan user SDE, tabel SGARDU_DISTRIBUSI dengan jumlah data 433.

select * from sgardu_distribusi

Kemudian saya mulai bagian paling menarik. Yaitu klik tombol SQL Tuning. Tombol ini aktif ketika saya menulis syntax di SQL Editor.

Jika belum menemukan tombol itu berarti anda belum menginstall TOADSQLTuning.exe yang ada di folder Tuning_Lab hasil instalasi TOAD anda.

Setelah muncul dialog SQL Tuning, saya akan melakukan optimasi syntax menggunakan fungsi Optimize. Tetapi sebelumnya set kepintaran AI di Preferences -> Optimization. Saya mencoba level 6 dari 10 level yang disediakan. Level berbanding lurus dengan waktu optimasi.

Kemudian klik Optimize . Setelah menunggu beberapa saat akan muncul dialog Compare Scenarios. Di dialog ini, TOAD memberikan saya 3 alternatif pilihan query SQL syntax, yaitu :

1. Alt #1 : select /*+ RULE */ * from sgardu_distribusi
2. Alt #2 : select /*+ INDEX(EMP) */ * from sgardu_distribusi
3. Alt #3 : select /*+ INDEX_DESC(EMP) */ * from sgardu_distribusi

Tampak query SQL original saya diberi HINT, yaitu directive yang diletakkan diantara /*+ dan */. Normalnya memang Oracle optimizer akan memilih hint ini yang diturunkan dari method eksekusinya. Ada lebih dari 40 hint di Oracle.

Hint yang umum digunakan salah satunya adalah INDEX. Seperti alternatif query nomor 2 diatas akan memberitahu bahwa Index EMP dalam tabel EMP akan digunakan dalam query. Oh ya jika kita salah menulis hint, akan diabaikan oleh Oracle. Jadi, double-check Hint yang anda gunakan dalam query supaya tidak sia-sia.

Sebelum mengetahui query mana yang lebih baik, antara yang original punya saya, atau alternatif yang digunakan TOAD, terlebih dahulu kita klik Execute All . Hasilnya :

Untuk Optimizing dengan kriteria Total Elapsed Time, query alternatif ke-3 tampak sangat mencolok, unggul 50%. Statistik komparasi hasil eksekusi yang lengkap dapat dilihat di menu Execution Statistics.

Whic one is TOAD choose as the winner?. Tentu saja query alternatif ke-3 : select /*+ INDEX_DESC(EMP) */ * from sgardu_distribusi.

Dapat dilihat pada menu Resolution.

Tapi saya tidak tahu mengapa, saya coba mengulang Execute kok hasilnya beda lagi. Kali ini seri. Antara alternatif 1 dan alternatif 3 sama-sama unggul 50%. Saya ulang Execute lagi. Malah alternatif 1 yang jadi pemenang. Ulang-ulang terus, lebih sering alternatif 1 yang menang. Jadi bingung yang mana.

Tapi jika melihat Actual Plan alternatif 1 yang menggunakan hint RULE hanya ada 1 langkah. Bandingkan alternatif 3 yang menggunakan 2 langkah, saya lebih memilih alternatif 1.

Untuk query bercabang dan banyak join yang lebih komplek saya pernah mencoba. Hasilnya saya diberi banyak sekali alternatif. Sampai 300-an. Begitu dieksekusi untuk mengetahui alternatif mana yang lebih baik, tiba-tiba di tengah jalan TOAD saya menghilang!.

Tags: SQL Tuning, TOAD