Oracle Sequence Basic

Oracle sequence (selanjutnya saya sebut sequence saja, untuk mempermudah) merupakan object dalam database yang berfungsi sebagai sequence number generator, nilai yang dihasilkan ini biasanya disimpan dalam kolom yang berfungsi sebagai primary key pada suatu table. Fungsionalitas sequence ini hampir sama seperti kolom autonumber di Microsoft Access hanya saja cara pemakaiannya berbeda, nanti pada pertengahan artikel ini akan saya sertakan bagaimana cara memiliki kolom autonumber di Oracle seperti yang dimiliki Microsoft Access dengan menggunakan sequence.

Developer yang tidak mengetahui adanya sequence atau karena hal lain yang membatasinya untuk tidak menggunakan sequence, menggantikannya dengan membuat sendiri sequence number (selanjutnya saya sebut m-sequence, untuk membedakannya dengan Oracle sequence) layaknya Oracle sequence. Teknik ini direalisasikan dengan menyimpan nilai maksimum dari m-sequence pada sebuah table. Saat aplikasi meminta nilai untuk m-sequence berikutnya, nilai terakhir yang disimpan dalam table tersebut dibaca kemudian ditambah satu (atau bilangan tertentu) dan disimpan kembali dalam table tersebut. Pada mekanisme ini, jika setelah table yang menyimpan nilai terakhir m-sequence ini di-update dan kemudian transaksi tidak segera diakhiri (commit atau rollback) maka aplikasi lain yang juga membutuhkan nilai m-sequence ini harus menunggu sampai transaksi yang memegang row level lock tersebut berakhir. Untuk aplikasi single user mekanisme ini tidak menimbulkan masalah, sedangkan dalam situasi multiuser, aplikasi yang seperti ini dikatakan tidak scalable. Pada m-sequence developer juga harus menangani sendiri mekanisme locking untuk mencegah terjadinya lost update, karena jika mekanisme locking tidak diimplementasikan dengan benar maka terdapat kemungkinan dua session yang me-request m-sequence secara bersamaan memperoleh nilai sama.

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.

Reading TKPROF Output

Dari topik sebelumnya yaitu Activate Extended SQL Trace, kita telah memperoleh sebuah file yang berisi history tentang apa saja yang dikerjakan oleh Oracle kernel untuk memenuhi permintaan aplikasi terhadap database. Menurut pengalaman saya dalam tracing ini, file yang dihasilkan dari SQL tracing ini berukuran besar, dan tidak mudah untuk langsung dianalisa. Untuk itulah TKPROF dibuat supaya mempermudah kita melakukan analisa.

TKPROF ini akan mem-format trace file yang dihasilkan ke dalam bentuk standard dengan tujuan mudah dimengerti. Saya tidak akan menjelaskan seluruh option yang dimiliki oleh TKPROF karena Oracle telah mendokumentasikannya dengan baik, hanya yang sering saya pakai saja. Untuk lebih dimengerti, penjelasan akan saya sertai dengan contoh. Berikut saya bentuk dulu test case-nya, saya jalankan dengan menggunakan Oracle 11g, karenanya saya memakai v$diag_info untuk mencari nama trace file yang terbentuk.

Activate Extended SQL Trace

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)

Intro

BLOG ini akan berisi dokumentasi tetang feature-feature Oracle yang saya peroleh baik dari eksperimen sendiri atau dari sumber lain misalnya Dokumentasi Oracle atau buku-buku Oracle. Tujuan pertama tentunya supaya mempermudah saya mengakses dokumen tersebut jika diperlukan. Kedua, mungkin juga berguna bagi siapa saja yang mengunjungi blog ini.

Script atau artikel yang berkaitan tetang Oracle yang akan saya posting di sini pasti telah saya test dan berhasil. Jika yang terjadi sebaliknya, artinya anda mencoba dan gagal, saya tidak bertanggung jawab atas dampak apapun yang ditimbulkan.

Silahkan beri komentar untuk ralat, kritik dan saran.