Rudi Kristanto’s BLOG

August 21, 2008

Activate Extended SQL Trace

Filed under: Trace File,Troubleshooting — Rudi Kristanto @ 5:40 am
Tags: ,

Kadang saat troubleshooting dibutuhkan tracing session (event 10046) yang mengalami masalah, karena dengan cara ini kita dapat menemukan SQL statement yang tidak efisien. Event-event apa saja yang dapat diaktifkan tersembunyi dalam file $ORACLE_HOME/rdbms/mesg/oraus.msg, yaitu file yang berisi error message dari kernel Oracle. Dapat ditemukan dalam file tersebut, bahwa error codes 10000 .. 10999 bukan merupakan error message tetapi event. Jika aktif, event ini digunakan oleh Oracle developer untuk salah satu dari ketiga hal berikut:
– Meng-enable atau disable suatu feature
– Simulasi crash/corruption
– Mencatat informasi trace atau debug

Debug atau trace event yang sering saya pakai untuk troubleshooting adalah:
– 10046 (enable SQL statement timing)
– 10053 (CBO enable optimizer trace)

Yang akan saya bahas di sini adalah bagaimana cara untuk mengaktifkan tracing pada debug event 10046. Perlu diketahui hasil tracing dari event ini adalah input file untuk TKPROF (Trace Kernel Profiler) yang merupakan salah satu tool yang digunakan untuk performance tuning – akan saya bahas penggunaannya di artikel selanjutnya.

Ada dua cara yang sering saya pakai untuk mengaktifkan debug event tergantung dari program yang akan kita trace,
1. Tracing program yang source code-nya dapat kita edit
2. Tracing program dimana kita tidak memiliki akses ke source code program tersebut

Untuk tipe pertama, langkah-langkahnya sebagai berikut:

1. Pastikan session tersebut memiliki hak untuk alter session.

2. Untuk mengetahui nama trace file yang akan dibentuk jalankan salah satu SQL berikut.

--Oracle 10g dan 11g, untuk 8i, 9i belum saya coba.
select rtrim(a.value,'/')||'/'||b.instance_name||'_ora_'||
       ltrim(to_char(d.spid))||
       decode(d.traceid,null,null,'_'|| d.traceid)||
       '.trc' trace_file_name
from v$parameter a, v$instance b, v$session c, v$process d
where a.name = 'user_dump_dest' and
      c.audsid = sys_context('userenv','sessionid' ) and
      d.addr = c.paddr;

--Oracle 11g
select value
from v$diag_info
where inst_id = (select instance_number from v$instance) and
      name = 'Default Trace File';

Pastikan session ini memiliki hak akses ke v$ view di atas untuk dapat menjalankan query tersebut. Tanpa memiliki hak akses ke v$ view, pencarian trace file yang dihasilkan dapat lebih mudah dengan menjalankan perintah berikut.

alter session set tracefile_identifier = XXX;

Perintah ini akan menambahkan string XXX pada nama trace file.

3. Pada source code yang akan di-trace tambahkan statement berikut.

alter session set timed_statistics = true;
alter session set max_dump_file_size = unlimited;
alter session set events '10046 trace name context forever, level 12';

-- source code yang akan di-trace

alter session set events '10046 trace name context off';

4. Selesai.

Tracing tipe kedua, langkah-langkahnya sebagai berikut:

1. Login dengan user yang memiliki hak untuk mengeksekusi package sys.dbms_system.

2. Pastikan SID session yang akan di-trace sudah diketahui. Jika SID tidak diketahui, maka cari lewat v$session kemudian difilter dengan field yang nilainya sudah diketahui, misalkan osuser, machine, program, module. Cara ini berlaku untuk aplikasi client server yang menggunakan dedicated server.

3. Dari SID yang diperoleh, jalankan query berikut untuk memperoleh serial# dan lokasi file trace.

select serial#, audsid
from v$session where sid = :sid

--Oracle 10g dan 11g, untuk 8i, 9i belum saya coba.
select rtrim(a.value,'/')||'/'||b.instance_name||'_ora_'||
       ltrim(to_char(d.spid))||
       decode(d.traceid,null,null,'_'||d.traceid)||
       '.trc' trace_file_name
from v$parameter a, v$instance b, v$session c, v$process d
where a.name = 'user_dump_dest' and
      c.audsid = :audsid and
      d.addr = c.paddr;

4. Gunakan nilai sid dan serial# yang telah diperoleh dari langkan no 2 dan 3 sebagai input statement berikut.

execute sys.dbms_system.set_bool_param_in_session(:sid,:serial#,'timed_statistics',true);
execute sys.dbms_system.set_ev(:sid,:serial#,10046,12,''); 

-- jalankan action yang akan di-trace (session berbeda) sampai selesai

execute sys.dbms_system.set_ev(:sid,:serial#,10046,0,'');

5. Selesai

Ada empat nilai level yang valid untuk debug event 10046, level 1 = enable sql trace, level 4 = enable sql trace dan capture bind variable value, level 8 = enable sql trace dan capture wait event dan level 12 = enable sql trace, capture bind variable value dan capture wait event.

Kedua cara tersebut bukan merupakan satu-satunya cara untuk mengaktifkan sql trace, hanya yang sering saya pakai saja. Masih ada package-package lain seperti DBMS_MONITOR, DBMS_SESSION, DBMS_SUPPORT ataupun dengan menggunakan fasilitas ORADEBUG. Beragam cara yang ditawarkan ini memiliki kelebihan dan kekurangan masing-masing dan tentunya ada yang di-support dan ada yang tidak di-support oleh Oracle.

1 Comment »

  1. [...] TKPROF, Trace File, Troubleshooting — Rudi Kristanto @ 5:56 am Dari topik sebelumnya yaitu Activate Extended SQL Trace, kita telah memperoleh sebuah file yang berisi history tentang apa saja yang dikerjakan oleh Oracle [...]

    Pingback by Reading TKPROF Output « Rudi Kristanto’s BLOG — September 29, 2008 @ 7:10 am | Reply


RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: