Tuning Query dengan SQL Trace dan tkprof


Dengan explain plan kita bisa tahu (meng-estimate) nantinya query kita itu memakai “execution plan” yang mana. Sementara dengan SQL trace kita bisa mengetahui query yang sedang berjalan ini menggunakan “execution plan” yang mana. Jadi “explain plan” adalah untuk meramalkan, sedangkan “sql trace” untuk melihat kejadian yang sesungguhnya.

Kelebihan SQL trace adalah SQL trace menampilkan informasi yang lebih banyak. Lebih detail tentang informasi yang bisa digali dari sql trace, silahkan lihat referensi di akhir tulisan ini. Berikut ini langkah-langkah (step-step) untuk mengaktifkan SQL trace:

  1. Pastikan bahwa instance parameter TIMED_STATISTICS=true.
  2. Aktifkan instance parameter sql_trace=true. Kita cukup lakukan di level session saja. alter session set sql_trace=true;
  3. Jalankan query yang akan dianalisa.
    SQL> select * from b where owner='ROHMAD'
    union select * from c where owner='ROHMAD';
  4. setelah selesai, disable sql_trace. alter session set sql_trace=false;
  5. Hasil trace ditaruh di directory udump. Untuk melihat lokasi udump, gunakan command ini (pakai user yang punya role dba)
    SQL> sho parameter user_dump_destFile trace yang dihasilkan berformat namainstance_ora_OSID.trc. Dalam contoh ini nama instance adalah ts. File yang dihasilkan adalah ts_ora_14662.trc. OSID bisa diperoleh dengan command berikut:
    SQL> select a.SPID from v$process a, v$session b
    where a.addr=b.paddr and
    b.username='nama_user_yang_menjalankan_sql_trace';
    Mungin kita tidak perlu report-report mencari OSID, kalau file-file di derektori udump tidak banyak, kita mungkin bisa langsung menemukan file trace tersebut.
  6. Untuk membaca file trace dengan format yang user fiendly, gunakan tool tkprof. Berikut ini contohnya
    cd lokasi_direkroty_user_dump_dest
    tkprof ts_ora_14662.trc output=ts_ora_14662.trc.log
    Hasil yang diformat ditaruh di file yang ditunjukkan oleh parameter output, yaitu ts_ora_14662.trc.log.

Setelah dapat trace file (hasil tkprof), lihat bagian “execution plan”. Biasanya yang paling penting adalah kalau ada “full table scan”, nah kita bisa mencoba-coba gimana sih kalau pakai index.

Pengalaman saya, sebagian besar porsi tuning query adalah:
– Menemukan bagian mana yang melakukan full table scan
– Memakai (membuat) index yang berkaitan dengan query
– OO… ternyata dengan memakai index, query jadi jauh lebih cepat

Referensi:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/sqltrace.htm

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