Don’t trigger to code Triggers

Dulu waktu kuliah saya sempat belajar mengenai trigger, apa itu trigger, bagaimana trigger digunakan dan sebagainya. Setelah saya lulus, dan bukan kebetulan saya bekerja menggunakan Oracle database, saya merupakan pengguna trigger yang tanpa tahu efek buruk yang ditimbulkannya, bahkan saya sempat berpikir kalau trigger merupakan salah satu feature yang keren.

Efek pertama, yang terasa bagi saya sendiri setelah cukup banyak mengerjakan program adalah repotnya memaintain program lama jika mengalami perubahan. Kalau saya tidak lupa tentang keberadaan trigger, saya akan menelusuri kembali trigger apa saja yang ada pada module tersebut, karena sifat trigger yang transparent terhadap aplikasi, dan bayangkan saja jika event (insert, update, delete, dst) satu table men-trigger event table lain dan event table lain ini juga men-trigger event table lainnya lagi, sungguh rumit dan butuh ketelitian apakah perubahan tersebut juga harus mengubah source trigger tersebut.

Efek kedua, berhubungan dengan performance. Sekarang ini setiap project yang saya tangani, saya kerjakan dengan menggunakan PL/SQL package, dimana package ini berupa API (Application Programming Interface). Berikut ini saya akan membandingkan eksekusi SQL yang berada dalam trigger dan yang berada dalam stored procedure (PL/SQL) dari segi performance. Test saya kerjakan di Oracle Database 10g Enterprise Edition Release 10.2.0.1.0.

create table t (col varchar2(30));

create or replace procedure sql_in_proc
as
begin
  for i in (select /*+ stored procedure */ * from dual)
  loop
    null;
  end loop;
end;
/

create or replace trigger sql_in_trig
before insert on t
for each row
begin
  for i in (select /*+ trigger */ * from dual)
  loop
    null;
  end loop;

  sql_in_proc;
end;
/

alter session set timed_statistics = true;

alter session set events '10046 trace name context forever, level 1';

insert into t values ('TEST1');

insert into t values ('TEST2');

insert into t
select owner from all_objects
where rownum < 11;

commit;

alter session set events '10046 trace name context off';
&#91;/sourcecode&#93;

Dengan menggunakan <a href="https://rudikristanto.wordpress.com/2008/09/28/reading-tkprof-output/">TKPROF</a> saya memformat <em>raw trace file</em> yang dihasilkan, dan berikut adalah bagian yang perlu dianalisa lebih lanjut.


SELECT /*+ trigger */ *
FROM
 DUAL

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute     12      0.00       0.00          0          0          0           0
Fetch       12      0.00       0.00          4         36          0          12
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       27      0.00       0.00          4         36          0          12

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 74     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL DUAL (cr=3 pr=2 pw=0 time=175 us)

********************************************************************************

SELECT /*+ stored procedure */ *
FROM
 DUAL

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     12      0.00       0.00          0          0          0           0
Fetch       12      0.00       0.00          0         36          0          12
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       25      0.00       0.00          0         36          0          12

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 74     (recursive depth: 1)

Dari hasil test di atas dapat dilihat bahwa keduanya sama-sama melakukan sekali hard parsing, statistik misses in library cache during parse yang keduanya bernilai satu. Statistik lain yang menunjukkan perbedaan ialah jumlah soft parse, diperoleh dari statistik parse dikurangi jumlah hard parse. Untuk SQL yang berada dalam stored procedure mengalami soft parse yang lebih sedikit (0) jika dibandingkan dengan SQL yang berada pada trigger (2). Dari sini dapat saya simpulkan bahwa SQL pada trigger hanya di-cache selama triggering statement berlangsung. Sedangkan SQL pada PL/SQL (stored procedure/function), mulai Oracle 9i, akan di-cache sebanyak jumlah yang dispesifikasikan dalam parameter session_cached_cursors. Detail dari statement saya yang terakhir akan menjadi topik blog saya yang lain. Jumlah execute call yang tidak didahului parse call disebut juga sebagai no parse.

Jadi dari segi performance, SQL dalam PL/SQL lebih baik jika dibandingkan SQL dalam trigger karena seperti kita ketahui jika parameter session_cached_cursors dikonfigurasi dengan benar akan mengurangi jumlah soft parse yang terjadi.

Mulai Oracle 11g, SQL yang berada pada trigger diperlakukan sama seperti SQL yang berada dalam PL/SQL. Untuk itu saya ulangi lagi test di atas hanya saja saya kerjakan di Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 dan saya peroleh hasil dari TKPROF sebagai berikut.

SQL ID : a8w9mrw70zb7k
SELECT /*+ trigger */ *
FROM
 DUAL

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     12      0.00       0.00          0          0          0           0
Fetch       12      0.00       0.00          0         36          0          12
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       25      0.00       0.00          0         36          0          12

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 95     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=0 us cost=2 size=2 card=1)

********************************************************************************

SQL ID : 610w27avd3dts
SELECT /*+ stored procedure */ *
FROM
 DUAL

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     12      0.00       0.00          0          0          0           0
Fetch       12      0.00       0.00          0         36          0          12
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       25      0.00       0.00          0         36          0          12

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 95     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=0 us cost=2 size=2 card=1)

Walaupun sudah diperlakukan sama di Oracle 11g, saya cenderung tidak menggunakan trigger karena alasan yang saya jelaskan pada efek pertama di atas.