Daily Snippet, Developer to developer

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.

1

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.

2

Kemudian klik Optimize 3 . 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 :
6
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.
7
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.
8
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!.
Standard

3 thoughts on “Simple to complex SQL tuning with TOAD

  1. eN Bi says:

    wa,,, tuning query
    masih lom mudenk neh maz… ^^

    eh maz,, boleh minta YMna atau apalah? email jg gpp ^^
    bisa nanya2 kan?

    thx bgt ya maz

    -NB-

    anangbakti wrote :
    you can contact me at nicholkas[di]yahoo.com.sg

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