Tuning Query dengan SQL Trace dan tkprof

Beberapa waktu yang lalu saya membahas tuning query dengan explain plan. 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

This entry was posted in Performance Tuning and tagged , , , , , , , , . Bookmark the permalink.

13 Responses to Tuning Query dengan SQL Trace dan tkprof

  1. nubie says:

    Pak Rohmad, saya kurang sependapat dengan bapak soal tuning menggunakan index. Beberapa kasus memang membutuhkan index agar query berjalan dengan cepat, namun kadang tidak selamanya index mempercepat query. kadang query tersebut ada yg kurang baik walaupun sudah di index tidak ada pengaruhnya. misalnya salah datatype, query kurang mengikat dsb.
    index kadang berpengaruh ke performance dari sistem, terutama jika datanya besar. karena setahu saya sebelum proses inserting data akan ada waktu tambahan untuk menyesuaikan dengan index yg ada.
    saran saya index merupakan alternatif terakhir dari tuning.
    Tidak selamanya full table scan itu jelek. untuk table yg kecil lebih baik full table scan dari pada dia harus mencari berdasarkan index.
    Dan juga kadang cost dari sebuah query tidak selamanya mencerminkan keefektifan dari sebuah query. harus dilihat juga buffer gets, banyak row yg di proses, cpu time, dsb
    maaf kalau kurang berkenan.
    blog bapak sangat bermanfaat untuk saya yang masih pemula.

  2. rohmadne says:

    Iya, index tidak selamanya bermanfaat. Dengan “explain plan” kita bisa ngecek kok, index yang kita buat ini bermanfaat apa tidak. Kalau bermanfaat ya sebaiknya dipakai, kalau tidak ya buat apa dipakai.

    Melihat cost dari query adalah salah satu alternatif tuning. Tentu saja aspek tuning tidak cuma itu; seperti yang anda sebutkan.

  3. HARRY says:

    MAS SAYA MAU TANYA MENGENAI OPTIMASI QUERY PAKE COST BASED OPTIMIZATION UTK DB MY SQL.TOLONG DONK JLASIN TTG TEKNIK ITU

  4. harry says:

    mas,,saya mo tanya mengenai optimasi query pake teknik cost based optimization,,
    bisa g jelasin ttg teknik itu n kasih contoh sekalian,,,trus kira2 dimana aku bisa dapetin referensi ttg teknik itu??? trus cara ngeliat execution plan pada mysql gimn??oke itu aja thanks

  5. rohmadne says:

    Hallo Mas Harry,
    silahkan lihat komentar tanggapan saya di sini
    http://rohmad.net/2008/05/29/tuning-query-dengan-explain-plan/

  6. HARRY says:

    MAS ROHMAD, SAYA MAU TANYA LG NIH! sy punya 2 tabel ,tbl berita dan komentar dng primary key berita_id. tbl berita memiliki atribut berita_id.tanggal.judul.dan berita dng 4000 record! tbl komentar memiliki atribut komentar_id,pengirim.email,komentar.dan berita_id dng 8000 record! gmn syntak nya di oracle utk menampilkan informasi berita dng jumlah komentar pd stiap berita dng menggunakan left join dan subquery? trus gmn cara melihat cost dari masing2 query tersebut? tolong dijawab mas ya.,soal nya skripsi ku bhs ttg optimasi query!

  7. rohmadne says:

    Untuk join
    ————-
    select berita.*, komentar.* from berita, komentar
    where berita.berita_id=komentar.berita_id

    Untuk left join
    —————–
    select berita.*, komentar.* from berita, komentar
    where berita.berita_id=komentar.berita_id(+)

    Untuk right join
    ——————-
    select berita.*, komentar.* from berita, komentar
    where berita.berita_id(+)=komentar.berita_id

    Tolong saya dikoreksi kalau salah (tertukar) antara left dan right.

    Untuk melihat cost, gunakan explain plan seperti yang saya bahas di sini
    http://rohmad.net/2008/05/29/tuning-query-dengan-explain-plan/

  8. HARRY says:

    mas.sya pnya query utk mysql:
    utk left join:
    select a.berita_id,a.judul,a.berita, coalesce?count?b.komentar_id?,0?as total_komentar from berita a left join komentar b using ?berita_id?group by a.berita_id order by berita_id desc
    dengan subquery:
    select a.berita_id,a.judul,a.berita coalesce ?b.total_komentar,0?as total_komentar from berita a left join ?select berita_id, count ?komentar_id?as total_komentar from komentar group by berita_id?b using ?berita_id?group by a.berita_id order by berita_id desc
    setelah ke 2 query trbt saya coba di oracle teryata error! kr2 syntak yg bnr utk oracle gmna ya?

  9. rohmadne says:

    Di My SQL:
    ————-
    select a.berita_id,a.judul,a.berita, coalesce (count(b.komentar_id),0)as total_komentar
    from berita a left join komentar b using (berita_id)
    group by a.berita_id order by berita_id desc

    Di Oracle
    ———-
    select a.berita_id,a.judul,a.berita, coalesce (count(b.komentar_id),0)as total_komentar
    from berita a, komentar b
    where a.berita_id=b.berita_id(+)
    group by a.berita_id
    order by berita_id desc

    ***

    Kuncinya adalah mengganti:
    ———————————-
    from berita a left join komentar b using (berita_id)

    Menjadi
    ———
    from berita a, komentar b where a.berita_id=b.berita_id(+)

    ***

    Namun saya agak miss dengan istilah left dan right, saya belum ngecek. Silahkan bandingkan hasilnya

    antara
    ——–
    from berita a, komentar b where a.berita_id=b.berita_id(+)
    dan
    —-
    from berita a, komentar b where a.berita_id(+)=b.berita_id

  10. HARRY says:

    MAS SAYA SDH COBA QUERY UTK YG DI ORACLE TETAPI MSH ERROR…
    NOT A GROUP BY EXPRESSION
    KR2 APANYA YG SALAH YA?

  11. rohmadne says:

    Clausa “group by” harus di-ikuti dengan nama-nama kolom acuan query.

    select a.berita_id,a.judul,a.berita, coalesce (count(b.komentar_id),0)as total_komentar
    from berita a, komentar b
    where a.berita_id=b.berita_id(+)
    group by a.berita_id,a.judul,a.berita
    order by berita_id desc;

  12. Rahmat says:

    mas..mau nanya neh..aku mengunakan database oracle 9i dan owb 10g r1..pada saat aku akan mendelete suatu table, table tersebut selalu locking dan selalu hanya table itu. dengan query yang sama ,saya coba lakukan ke table lain,hasilnya ndak locking. ini query nya :

    delete from [nama table]
    where tahun in (select tahun from[nama table]) and jenis=’2′;

    pls help me mas..thanks ya

  13. michael says:

    mas, saya mau tanya tentang sql trace di oracle 10g.
    saat ini saya membutuhkan trace sebuah proses di server (sun solaris).
    ada sebuah program yang berjalan tiap pagi (dijalankan lewat crontab). program ini mengakses database oraclenya.
    nah,berhubung waktunya tidak memungkinkan untuk ditongkrongin, saya berencana untuk menjalankan skrip (untuk menjalankan oracle trace-nya) bersamaan dengan jalannya program tersebut.
    kira2 isi skripnya seperti apa ya mas?
    terima kasih sudah boleh posting disini.. 😀

Leave a Reply

Your email address will not be published. Required fields are marked *