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:
- Pastikan bahwa instance parameter TIMED_STATISTICS=true.
- Aktifkan instance parameter sql_trace=true. Kita cukup lakukan di level session saja.
alter session set sql_trace=true; - Jalankan query yang akan dianalisa.
SQL> select * from b where owner='ROHMAD'
union select * from c where owner='ROHMAD'; - setelah selesai, disable sql_trace.
alter session set sql_trace=false; - 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 bMungin kita tidak perlu report-report mencari OSID, kalau file-file di derektori udump tidak banyak, kita mungkin bisa langsung menemukan file trace tersebut.
where a.addr=b.paddr and
b.username='nama_user_yang_menjalankan_sql_trace'; - Untuk membaca file trace dengan format yang user fiendly, gunakan tool tkprof. Berikut ini contohnya
cd lokasi_direkroty_user_dump_destHasil yang diformat ditaruh di file yang ditunjukkan oleh parameter output, yaitu ts_ora_14662.trc.log.
tkprof ts_ora_14662.trc output=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
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.
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.
MAS SAYA MAU TANYA MENGENAI OPTIMASI QUERY PAKE COST BASED OPTIMIZATION UTK DB MY SQL.TOLONG DONK JLASIN TTG TEKNIK ITU
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
Hallo Mas Harry,
silahkan lihat komentar tanggapan saya di sini
http://rohmad.net/2008/05/29/tuning-query-dengan-explain-plan/
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!
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/
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?
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
MAS SAYA SDH COBA QUERY UTK YG DI ORACLE TETAPI MSH ERROR…
NOT A GROUP BY EXPRESSION
KR2 APANYA YG SALAH YA?
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;
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
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.. 😀