Memeriksa Waktu Eksekusi SQL Di Oracle TimesTen

Pada artikel sebelumnya, saya menuliskan bagaimana memantau kinerja database TimesTen melalui procedure di package TT_STATS.   Pada artikel ini, saya akan mencoba memeriksa kinerja eksekusi masing-masing SQL dengan menggunakan procedure bawaan TimesTen seperti ttStatsConfig, ttSQLCmdCacheInfo dan sebagainya.

Saya akan mulai dengan mengambil sampel untuk setiap eksekusi SQL di TimesTen dengan memberikan perintah berikut ini:

Memulai proses pengambilan sampel

Memulai proses pengambilan sampel

Pada anonymous PL/SQL di atas, saya mengaktifkan pengambilan sampel untuk setiap kali eksekusi SQL dengan memanggil ttStatsConfig("SQLCmdSampleFactor", 1).   Selain itu, saya juga menghapus statistik yang sudah diambil sebelumnya dengan memanggil ttStatsConfig("SQLCmdHistogramReset", 0).   Saya menentukan jenis statistik yang akan diambil dengan memanggil ttStatsConfig("StatsLevel", "TYPICAL").

Setelah mengaktifkan proses pengambil sampel, saya perlu menjalankan aplikasi dan melakukan operasi yang berkaitan dengan database di aplikasi saya.   TimesTen akan mengambil statistik untuk setiap SQL yang dikerjakan oleh aplikasi  secara otomatis.   Setelah selesai, saya mematikan aplikasi dan kembali ke SQL Developer.

Saya dapat menampilkan statistik yang telah terkumpul dengan memberikan perintah seperti berikut ini:

CALL ttSQLCmdCacheInfo;

Untuk melihat informasi khusus untuk SQL tertentu, saya dapat memanggil ttSQLCmdCacheInfo dengan melewatkan parameter berupa id dari perintah SQL tersebut, seperti yang terlihat pada gambar berikut ini:

Melihat statistik untuk SQL tertentu

Melihat statistik untuk SQL tertentu

Saya juga dapat memanggil ttSQLCmdCacheInfo2 yang menyediakan informasi tambahan berupa fetchCount, startTime, maxExecuteTime, lastExecuteTime, dan minExecuteTime.

Untuk melihat histogram yang menunjukkan persebaran waktu eksekusi untuk perintah SQL yang ada, saya memberikan perintah seperti yang terlihat pada gambar berikut ini:

Melihat histogram waktu eksekusi SQL

Melihat histogram waktu eksekusi SQL

Pada hasil di atas, terlihat bahwa terdapat 10.971 eksekusi SQL yang dikerjakan sejak saya memulai proses pengambilan sampel.   Total waktu yang dibutuhkan untuk mengerjakan seluruh SQL tersebut adalah 1,0693366 detik.   Dari seluruh SQL tersebut, terdapat 9.669 eksekusi yang membutuhkan waktu lebih dari 0,00001562 detik hingga 0,000125 detik.   Terdapat 1.207 eksekusi yang membutuhkan waktu lebih dari 0,000125 detik hingga 0,001 detik.   Dan seterusnya.

Berdasarkan hasil histogram, terlihat bahwa kebanyakan eksekusi SQL di aplikasi saya memiliki waktu eksekusi antara 0.00001563 detik hingga 0.000125 detik.   Tidak ada SQL yang waktu eksekusinya mencapai hingga 1 detik.   Ini adalah hasil yang cukup memuaskan mengingat isi tabel yang diproses adalah kumpulan transaksi jual beli selama beberapa tahun.

Memantau Kinerja Oracle TimesTen Dengan Package TT_STATS

Sebuah sepeda motor perlu dirawat secara berkala.   Perawatan yang dikenal sebagai engine tune-up ini berguna untuk menjaga agar kinerja mesin motor tetap optimal dengan cara melakukan kalibrasi ulang karbulator, mengganti spark plug (busi) yang usang, dan sebagainya.   Hampir semua pemilik motor mengerti bahwa sepeda motor yang mereka beli tidak bisa dipakai selamanya tanpa di-tune-up di bengkel.   Begitu juga dengan sebuah database; ia perlu dirawat dan dipantau secara berkala.   Bedanya, tidak seperti pemilik sepeda motor yang menyadari pentingnya tune-up, beberapa programmer pemula menganggap bahwa setelah program selesai dibuat, maka database dapat dibiarkan begitu saja hingga suatu hari nanti menjadi penuh.

Lalu, bagaimana cara memantau kinerja database TimesTen?   Saya dapat menggunakan tool bawaan ttStats atau langsung memanggil procedure yang ada di package TT_STATS.   Pada artikel ini, saya akan mencoba memakai beberapa procedure yang ada di package TT_STATS.

Saya akan mulai dengan memanggil procedure TT_STATS.CAPTURE_SNAPSHOT() seperti yang terlihat pada gambar berikut ini:

Memulai proses capture

Memulai proses capture

Pada pemanggilan TT_STATS.CAPTURE_SNAPSHOT di atas,  saya melewatkan nilai parameter capture_level berupa 'ALL' sehingga seluruh metrik yang ada akan di-capture.   Pilihan lainnya adalah 'NONE', 'BASIC' dan 'TYPICAL'.

Selain itu, saya juga menggunakan TT_STATS.SHOW_SNAPSHOTS untuk menampilkan seluruh snapshot yang tersedia.   Terlihat bahwa id snapshot yang barusan dibuat adalah 8.

Saya kemudian menjalankan aplikasi dan mengerjakan beberapa operasi yang melibatkan database.   Setelah selesai, saya kembali membuat sebuah snapshot baru di TimesTen.   Karena perintah tersebut sudah ada di worksheet SQL Developer, saya hanya perlu men-klik icon Run Script atau menekan tombol F5.

Sekarang, procedure TT_STATS.SHOW_SNAPSHOTS akan menampilkan dua buah snapshot berbeda, yaitu snapshot dengan id 8 dan id 9, seperti yang terlihat pada gambar berikut ini:

Men-capture snapshot kedua

Men-capture snapshot kedua

Output yang saya harapkan adalah laporan yang membandingkan kedua snapshot tersebut.   Saya dapat menggunakan procedure TT_STATS.GENERATE_REPORT_HTML untuk menghasilkan laporan dalam bentuk HTML, atau TT_STATS.GENERATE_REPORT_TEXT untuk menghasilkan laporan dalam bentuk teks biasa.   Sebagai latihan, saya akan menampilkan laporan dalam bentuk teks dengan perintah seperti berikut ini:

Membuat laporan teks

Membuat laporan teks

Pada gambar di atas, saya membuat sebuah anonymous PL/SQL yang memanggil TT_STATS.GENERATE_REPORT_HTML.   Parameter pertama dan parameter kedua adalah id dari snapshot yang hendak dibandingkan.   Parameter ketiga adalah sebuah associative array yang menampung isi laporan yang dihasilkan baris per baris.   Karena nilai associative array tidak dapat ditampilkan secara langsung, saya kemudian menggunakan looping for untuk mencetak isi associative array tersebut.

Sebagai langkah penutup, saya akan menghapus yang telah dihasilkan guna menghemat memori dengan memberikan perintah berikut ini:

Menghapus snapshot

Menghapus snapshot

Procedure TT_STATS.DROP_SNAPSHOTS_RANGE menerima parameter berupa id snapshot pertama hingga id snapshot terakhir yang akan dihapus.   Bila keduanya bernilai 0, maka seluruh snapshot yang ada akan dihapus.

Memakai Index Advisor Di TimesTen

Pada query SQL yang mengandung klausa WHERE, database perlu menyaring data yang harus dikembalikan.   Pencarian isi tabel yang dilakukan berdasarkan seluruh data yang ada, baris per baris, disebut sebagai full table scan.   Pada full table scan, semakin banyak baris pada tabel maka semakin lambat eksekusi query tersebut.

Full table scan dapat dihindari bila kolom dalam klausa WHERE sudah di-index sebelumnya.   Index adalah struktur data yang mewakili kolom di tabel yang dirancang untuk mempercepat pencarian.   Sebagai contoh, bila saya mendefinisikan sebuah range index pada kolom NILAI, maka database dapat menentukan dengan cepat baris mana yang memenuhi kondisi SQL WHERE NILAI > 60,  tanpa harus menelusuri seluruh baris yang ada (full table scan).   Walaupun penggunaan index meningkatkan kinerja query,  ia akan mempengaruhi proses seperti INSERT dan UPDATE karena pada saat isi tabel berubah, maka index juga perlu diperbaharui.   Oleh sebab itu, penggunaan index perlu dipertimbangkan secara seksama 🙂

Oracle TimesTen memiliki fasilitas index advisor yang dapat memberikan saran kolom mana yang perlu di-index bagi database administrator (atau developer, terutama yang ingat merawat database!).   Index advisor hanya memberikan rekomendasi saja;  database administrator perlu mempertimbangkan lagi apakah index perlu dibuat atau tidak.

Saya akan mencoba memakai index advisor untuk melihat rekomendasi index untuk sebuah aplikasi yang memproses faktur.   Langkah pertama yang saya lakukan adalah memperbaharui statistics untuk tabel.   Di SQL Developer, saya men-klik kanan Tables, lalu memilih Statistics, Update.   Setelah itu, saya men-klik tombol Apply.

Memperbaharui statistik

Memperbaharui statistik

Pada langkah berikutnya, saya memanggil procedure ttIndexAdviceCaptureStart() untuk menjalankan index advisor seperti yang diperlihatkan pada gambar berikut ini:

Memulai proses capture

Memulai proses capture

Agar dapat bekerja dengan baik, index advisor perlu mengamati SQL yang diberikan selama proses capture sebagai bahan pertimbangan dalam merekomendasikan index.

Saya memberikan nilai 1 sebagai nilai parameter pertama, captureLevel, karena SQL sebagai akan dikerjakan oleh aplikasi melalui koneksi yang berbeda.   Bila SQL akan diberikan oleh koneksi yang sama, saya dapat menggunakan nilai 0.

Setelah memulai proses capture, saya kemudian menjalankan aplikasi dan melakukan operasi dari aplikasi seperti layaknya saat dipakai oleh pengguna.   Saya mencoba mensimulasi beberapa aktifitas yang paling sering dilakukan oleh pengguna.   Saya juga mencoba menampilkan laporan, terutama yang membutuhkan waktu lama.

Setelah selesai dengan aplikasi, saya perlu mematikan proses capture dengan memanggil procedure ttIndexAdviceCaptureEnd()seperti yang terlihat pada gambar berikut ini:

Menyelesaikan proses capture

Menyelesaikan proses capture

Untuk melihat apa saja SQL yang berhasil diperoleh oleh index advisor, saya memanggil procedure ttIndexAdviceCaptureInfoGet()seperti pada gambar berikut ini:

Melihat informasi yang di-capture

Melihat informasi yang di-capture

Hasil dari procedure ttIndexAdviceCaptureInfoGet() memperlihatkan bahwa sebuah proses capture secara global (CAPTURELEVEL=1) telah selesai dilakukan (CAPTURESTATE=0).   Proses capture tersebut menampung 25.196 prepared statement dan telah mengerjakan 25.200 perintah SQL.

Sekarang, saya akan melihat apa rekomendasi dari index advisor berdasarkan hasil capture yang diperolehnya dengan memanggil procedure ttIndexAdviceCaptureOutput() seperti yang terlihat pada gambar berikut ini:

Menampilkan hasil capture

Menampilkan hasil capture

Pada hasil di atas, kolom createStmt berisi perintah SQL untuk membuat index, sementara kolom stmtCount menunjukkan jumlah perintah SQL berbeda yang akan diuntungkan oleh index tersebut.   Sebagai contoh, bila saya memberikan index yang berisi kombinasi kolom (DTYPE,TANGGAL) pada tabel FAKTUR,  maka akan ada 33 perintah SQL berbeda (di aplikasi) yang diuntungkan oleh index tersebut.

Saya tidak akan mengerjakan seluruh saran yang diberikan di createStmt karena operasi insert, update, dan delete akan menjadi lambat.  Semakin banyak index yang perlu diperbaharui, maka semakin lambat operasi perubahan data pada sebuah tabel.  Walaupun demikian, index advisor setidaknya sudah memberikan titik terang berupa kandidat kolom yang perlu di-index.

Mengakses TimesTen Dengan Oracle SQL Developer

Selama ini saya mengakses database TimesTen melalui tool bawaan yang berbasis command line, yaitu ttIsql.   Namun proses administrasi database akan lebih mudah bila seandainya terdapat tool berbasis GUI.   Sebagai contoh, di MySQL Server ada MySQL Workbench dan di Oracle Database ada SQL Developer, juga ada aplikasi komersial seperti Toad.   Lalu bagaimana dengan Oracle TimesTen?   Saya dapat menggunakan SQL Developer untuk mengakses database TimesTen.

Tapi saat pertama kali mencoba menghubungi TimesTen di SQL Developer, saya mengalami kendala.  Saat saya mengakses TimesTen melalui metode direct connection, SQL Developer menampilkan pesan kesalahan seperti berikut ini:

[TimesTen][TimesTen 11.2.2.5.0 ODBC Driver][TimesTen]TT0837:
Cannot attach data store shared-memory segment, error 8 -- file
"db.c", lineno 9934, procedure "sbDbConnect"

Error 8 adalah pesan kesalahan bahwa memori tidak cukup.   Dokumentasi TimesTen menyebutkan bahwa manajemen shared memory di Windows berbeda dengan sistem operasi Unix atau Linux.   Keterbatasan di Windows menyebabkan sulitnya memetakan seluruh isi database ke lokasi memori secara berlanjut (tanpa fragmentasi).   Dokumentasi juga menyarankan agar memakai Windows 64-bit untuk menghindari kesalahan ini.

Tapi bagaimana bila saya tidak akan beralih ke Windows 64-bit?  Saya tetap ingin mengakses TimesTen melalui SQL Developer di Windows 32-bit.   Salah satu solusinya adalah dengan menggunakan koneksi client server di komputer yang sama.   Btw, hanya DSN yang didefinisikan pada System DSN saja yang dapat diakses sebagai server.   Sebagai contoh, gambar berikut ini DSN di ODBC Data Source Administrator yang akan dijadikan sebagai server:

Server DSN

Server DSN

Saya akan membuat sebuah client DSN baru dengan men-klik tombol Add…, memilih TimesTen Client 11.2.2 lalu men-klik tombol Finish.

Pada kotak dialog yang muncul, saya men-klik tombol Servers….   Lalu, pada kotak dialog Oracle TimesTen Logical Server List, saya men-klik tombol Add… untuk mendefinisikan server baru, yang isinya seperti berikut ini:

Mendefinisikan server baru

Mendefinisikan lokasi server baru

Setelah itu, saya men-klik tombol OK. Saya memilih server yang barusan saya buat, lalu men-klik tombol Close.   Kemudian saya mengisi kotak dialog yang ada menjadi seperti pada tampilan berikut ini:

Atribut untuk client DSN

Atribut untuk client DSN

Saya men-klik tombol Test Oracle TimesTen Server Connection dan Test Data Source Connection untuk memastikan koneksi dapat dilakukan dengan baik.   Setelah itu, saya men-klik tombol OK dua kali untuk menyelesaikan proses pembuatan client DSN ini.

Sekarang, saya akan menjalankan SQL Developer.   Tapi sebelumnya, saya perlu memastikan terlebih dahulu bahwa library TimesTen yang dibutuhkan sudah terdaftar di environment variable CLASSPATH.   Agar lebih pasti, saya menjalankan SQL Developer dari command prompt dengan memberikan perintah seperti berikut ini:

C:\>c:\TimesTen\tt1122_32\bin\ttenv.bat
Setting environment variables for TimesTen

set PATH to "..."

set CLASSPATH to "..."

C:\>c:\sqldeveloper\sqldeveloper

Tidak lama kemudian SQL Developer akan muncul di layar.  Saya hanya perlu sekali saja untuk mengerjakan SQL Developer seperti di atas, setelah itu saya dapat menjalankan SQL Developer melalui shortcut.

Langkah pertama yang saya lakukan di SQL Developer adalah men-klik tombol new connection pada SQL Developer untuk membuat koneksi baru ke database TimesTen, seperti yang diperlihatkan oleh gambar berikut ini:

Membuat koneksi baru

Membuat koneksi baru

Saya kemudian mengisi kotak dialog New / Select Database Connection menjadi seperti pada gambar berikut ini:

Mengisi informasi koneksi ke TimesTen

Mengisi informasi koneksi ke TimesTen

Bila tab TimesTen tidak muncul seperti di gambar di atas, pastikan bahwa JDBC driver untuk TimesTen sudah didefinisikan di environment variable CLASSPATH.

Saya kemudian men-klik tombol Test dan memastikan bahwa terdapat indikator Status: Success.   Setelah yakin, saya men-klik tombol Save, lalu diikuti dengan men-klik tombol Connect.

Setelah ini, saya dapat melihat isi schema untuk LATIHAN_CLIENT melalui SQL Developer, seperti yang terlihat pada gambar berikut ini:

Melihat isi schema database

Melihat isi schema database

Pada bagian Reports, saya juga dapat mengerjakan beberapa query ‘siap jadi‘ yang berkaitan dengan administrasi database TimesTen, seperti yang diperlihatkan oleh gambar berikut ini:

Query siap jadi untuk administrasi TimesTen

Query siap jadi untuk administrasi TimesTen

Untuk melakukan operasi pada sebuah tabel, saya dapat men-klik kanan sebuah tabel dan memilih salah satu akses yang diinginkan, seperti yang diperlihatkan pada gambar berikut ini:

Aksi pada tabel

Aksi pada tabel

Pada SQL Worksheet, saya dapat memberikan query SQL ataupun mengerjakan PL/SQL, seperti yang diperlihatkan oleh gambar berikut ini:

Mengerjakan PL/SQL dari worksheet

Mengerjakan PL/SQL dari worksheet

Memakai Oracles TimesTen Dengan simple-jpa

Oracle TimesTen adalah sebuah database in-memory yang mendukung SQL.   Karena TimesTen masih mendukung SQL, maka ia dapat dipakai pada aplikasi yang menggunakan Java Persistence API (JPA) dalam mengakses database.   Oleh sebab itu, saya akan mencoba melakukan migrasi database pada sebuah aplikasi Griffon yang memakai plugin simple-jpa.   Aplikasi desktop tersebut sebelumnya memakai database MySQL, dan saya ingin mengubahnya agar memakai database Oracle TimesTen.   Apa saja perubahan yang harus dilakukan?

Langkah pertama adalah menambahkan driver JDBC ke proyek saya.   Driver JDBC untuk TimesTen tidak ada di repository global Maven (karena ia bukan produk open-source!) sehingga saya tidak bisa menambahkannya pada file BuildConfig.groovy seperti biasa.   Sebagai alternatifnya, saya akan men-copy file C:\TimesTen\tt1122_32\lib\ttjdbc7.jar secara manual ke folder C:\proyekGriffon\lib.

Langkah berikutnya adalah menyiapkan dialek TimesTen untuk Hibernate JPA.   Setiap database, walaupun mendukung SQL secara umum, masing-masing memiliki sedikit perbedaan.   Sebagai contoh, tipe data tulisan (karaketer) yang disarankan untuk TimesTen dan Oracle Database adalah VARCHAR2, sementara pada MySQL yang tersedia adalah VARCHAR.   Contoh lainnya, untuk melihat tanggal hari ini di MySQL, saya memakai function seperti SELECT CURRENT_DATE. Sementara itu, jika di TimesTen dan Oracle Database, saya akan memakai function SELECT SYSDATE FROM DUAL.

Hibernate JPA dapat mendukung berbagai variasi SQL pada masing-masing database, yang disebutnya sebagai dialek.   Secara bawaan, Hibernate memiliki beberapa dialek yang dapat dipilih dengan cara dilewatkan melalui property hibernate.dialect.  Contoh dialek bawaan adalah MySQLMyISAMDialect, MySQLInnoDBDialect, Oracle9Dialect, PostgreSQL81Dialect, SAPDBDialect, dan sebagainya.   Sayang sekali, dari seluruh dialek bawaan Hibernate, tidak ada dialek untuk TimesTen.  Tapi saya tidak perlu khawatir karena TimesTen telah menyediakan dialek untuk dipakai di Hibernate.   Lokasinya terletak di C:\TimesTen\tt1122_32\quickstart\sample_code\j2ee_orm\config\hibernate4\TimesTenDialect1122.java.   Saya segera membuat folder untuk package org.hibernate.dialect dan meletakkan file tersebut pada folder ini, seperti yang diperlihatkan oleh gambar berikut ini:

Menambah dialek TimesTen untuk Hibernate

Menambah dialek TimesTen untuk Hibernate

Ada sedikit kekurang TimesTenDialect1122 yang perlu saya perbaiki terlebih dahulu.   Sama seperti Oracle Database, TimesTen tidak mendukung tipe data boolean di SQL.   Sementara itu, di MySQL, bila saya mendefinisikan sebuah kolom dengan tipe data boolean, ia akan diterjemahkan menjadi tinyint(1).   Oleh sebab itu, saya menambahkan baris berikut ini pada class TimesTenDialect1122:

registerColumnType(Types.BOOLEAN, "TT_TINYINT");

Sekarang, saya akan melakukan perubahan pada file persistence.xml sehingga terlihat seperti berikut ini:

<persistence ...>
  <persistence-unit name="default" transaction-type="RESOURCE_LOCAL">
    ...
    <properties>
      <property name="javax.persistence.jdbc.driver" value="com.timesten.jdbc.TimesTenDriver"/>
      <property name="javax.persistence.jdbc.url" value="jdbc:timesten:direct:LATIHAN"/>
      <property name="javax.persistence.jdbc.user" value="solid"/>
      <property name="javax.persistence.jdbc.password" value="snake"/>
      <property name="hibernate.connection.autocommit" value="false"/>
      <property name="hibernate.hbm2ddl.auto" value="create-drop"/>
      <property name="hibernate.dialect" value="org.hibernate.dialect.TimesTenDialect1122"/>
      <property name="jadira.usertype.autoRegisterUserTypes" value="true"/>
    </properties>
  </persistence-unit>
</persistence>

Saya mengubah nilai property hibernate.hbm2dll.auto menjadi create-drop supaya Hibernate membuat tabel baru di database TimesTen yang masih kosong.   Baris ini boleh dihapus bila tidak dibutuhkan lagi.

Lalu, apa langkah berikutnya? Cukup sampai disini!   Saya tidak perlu mengubah kode program karena saya tidak memberikan perintah SQL secara langsung, melainkan melalui melalui dynamic finders dan JP QL (bahasa query untuk JPA).   Dengan demikian, saya tidak perlu khawatir dengan SQL yang tidak compatible antara MySQL dan TimesTen (selama dialek Hibernate-nya tidak bermasalah!).

Tapi ada satu pandangan naif yang perlu dihindari, terutama bagi programmer yang tidak mau berurusan dengan cara kerja database, yaitu menganggap bahwa dengan memakai sebuah database ‘keren’ maka kinerja aplikasi secara otomatis akan meningkat.   Ini adalah sebuah pandangan yang salah karena masing-masing database memiliki karakteristik tersendiri.   Terkadang aplikasi harus menyesuaikan dengan karakteristik database.   Sebagai contoh, ada dua ‘aliran‘ dalam mengakses data di memori.   Sebuah tabel terdiri atas dua dimensi (baris dan kolom) dengan data seperti:

        Kolom1   Kolom2   Kolom3
---------------------------------
Baris1   A1      B1       C1
Baris2   A2      B2       C2
Baris3   A3      B3       C3   
---------------------------------

Skema di atas adalah visualisasi tabel dalam benak manusia. Tapi saat disimpan di memori dan piringan harddisk, segala sesuatunya disimpan dalam blok per blok (anggap setiap blok adalah sebuah kotak yang dapat diisi).  Setiap blok di memori (atau blok di sektor harddisk) memiliki alamat berupa posisi offset, misalnya dari  0 hingga ke byte terakhir di offset 0xffffffff.   Jadi, di media penyimpanan, struktur tabel yang dua dimensi (baris dan kolom) harus disimpan ke dalam struktur satu dimensi (kumpulan blok).  Perancang database (pembuat DBMS) dapat memilih menyimpan data tabel per baris sehingga data akan terlihat seperti: A1 B1 C1 A2 B2 C2 A3 B3 C3.   Tapi ia juga dapat memilih menyimpan per kolom sehingga data di memori akan terlihat seperti: A1 A2 A3 B1 B2 B3 C1 C2 C3.   Tabel yang disimpan per kolom memungkinkan query yang cepat (seperti agregasi per kolom) dan memungkinkan kompresi kolom.   Tapi kelemahannya adalah proses penambahan data baru menjadi lebih lambat.   Hal ini menyebabkan DBMS yang menyimpan data per baris lebih tepat dipakai untuk aplikasi OLTP (pemrosesan transaksi), sementara DBMS yang menyimpan data per kolom lebih tepat dipakai untuk aplikasi OLAP (analisa dan laporan).  Contoh sederhana ini menunjukkan bahwa sebaiknya aplikasi mengerti karakteristik database sehingga dapat menggunakannya secara maksimal.

Kembali ke aplikasi saya, saat setelah mengganti database dari MySQL ke TimesTen, saya mencoba menguji perbedaan kecepatannya.   Saya membuat kode program yang menambah 94.521 faktur (termasuk diantaranya query untuk mencari konsumen berdasarkan kode, mencari barang berdasarkan kode, dan menambah pembayaran).   Saat memakai database MySQL, transaksi tersebut selesai dalam waktu 13 menit. Sementara bila memakai TimesTen, dibutuhkan waktu hingga mencapai 14 menit!   Mengapa TimesTen lebih lambat dibanding MySQL??

Investigasi lebih lanjut menunjukkan bahwa TimesTen kesulitan menangani transaksi yang isinya sangat besar.   Pada percobaan pertama, 94.521 faktur tersebut merupakan bagian dari sebuah transaksi tunggal.   Untuk percobaan kedua, saya menjadikan setiap proses penyimpanan masing-masing faktur menjadi sebuah transaksi tunggal.   Hasilnya? Oracle TimesTen berhasil memproses seluruh faktur dalam waktu 10 menit!   MySQL malah kewalahan melakukan banyak commit sehingga pada percobaan kedua ini, ia membutuhkan waktu hingga 49 menit.   Kali ini, TimesTen hampir lima kali lebih cepat dibandingkan dengan MySQL.

Membuat Aplikasi Database Tanpa Coding Dengan NetBeans

P.S.: Pada artikel ini, saya memakai database Oracle TimesTen, akan tetapi langkah yang sama juga dapat diterapkan pada database lainnya asalkan memilih driver JDBC yang tepat untuk database tersebut.

Saya akan mencoba membuat sebuah program Java sederhana yang mengakses dan melakukan query pada database TimesTen.   Saya akan memakai Java 7 di NetBeans 7.3.   Karena NetBeans menyediakan fitur GUI editor yang lumayan canggih, pada artikel ini saya akan menempuh cara ang drag-n-drop (di balik layar NetBeans tetap memakai JPA!).   Saya tidak perlu mengetik kode program, bahkan satu baris kode program pun tidak perlu.   Seorang teman saya sangat tergila-gila pada fasilitas GUI yang drag-n-drop di NetBeans, membuatnya enggan beralih ke IDE lain.   Saya secara pribadi tidak akan pernah 100% bergantung pada visual editor, karena biasanya akselerasi hanya terasa di awalnya tapi sering kali ribet di kemudian hari 😉   Tapi mungkin ini hanya masalah selera; produktifitas seorang developer akan tinggi bila ‘selera’-nya terpenuhi.

Saya akan mulai dengan membuat sebuah proyek baru.   Untuk itu, saya memilih menu File, New Project….   Pada dialog yang muncul, saya memilih Java, Java Application kemudian men-klik tombol Next.   Saya mengisi nama proyek dengan LatihanTimesTen, kemudian menghilangkan tanda centang pada Create Main Class.   Setelah itu saya menyelesaikan pembuatan proyek baru dengan men-klik tombol Finish.

Sama seperti database lainnya yang berbasis SQL, untuk mengakses TimesTen di Java harus melalui JDBC API.   Btw, JDBC adalah sebuah ukan merupakan sebuah singkatan.   Walaupun demikian, nama JDBC sepertinya agak mirip dengan ODBC (Open Database Connectivity) buatan Microsoft, bahkan sekilas terlihat ada persamaan diantara mereka.   Untuk memakai ODBC, dibutuhkan driver yang disediakan oleh pembuat database.   Begitu juga, untuk memakai JDBC dibutuhkan driver JDBC dalam bentuk file JAR yang disediakan oleh pembuat database.   Pada instalasi saya, lokasi driver ini terletak di C:\TimesTen\tt1122_32\lib.   Untuk Java 7, saya akan memakai file bernama ttjdbc7.jar.   Pada dasarnya file ttjdbc7.jar dan ttjdbc6.jar adalah file yang sama (duplikat)!

Saya akan memakai fasilitas GUI dari NetBeans untuk menjelajahi database.   Tapi sebelumnya saya memastikan terlebih dahulu bahwa TimesTen Data Manager sudah dijalankan.   Untuk itu, saya memberikan perintah ttDaemonAdmin -start. Bagi   yang tidak ingin memakai perintah Command Prompt bisa langsung memerika di Control Panel, Administrative Tools, Services.   Setelah itu, saya juga melakukan koneksi pertama kali ke database melalui ttIsql agar database di-load ke memori sehingga dapat dipakai oleh user lainnya.

Lalu, saya memilih menu Window, Services di NetBeans.   Pada Databases, saya men-klik kanan dan memilih New Connection… seperti yang terlihat pada gambar berikut ini:

Mendaftarkan koneksi baru

Mendaftarkan koneksi baru

Setelah itu, pada Driver, saya memilih New Driver….   Akan muncul dialog New JDBC Driver. Saya mengisinya seperti dengan yang terlihat pada gambar berikut ini:

Mendaftarkan driver JDBC baru

Mendaftarkan driver JDBC baru

Pastikan pada Driver Class, yang dipilih adalah com.timesten.jdbc.TimesTenDriver dan bukan com.timesten.jdbc.TimesTenClientDriver.   Hal ini karena saya melakukan direct connection, bukan client server.

Setelah itu, saya men-klik tombol OK.   Kemudian, saya men-klik tombol Next untuk melanjutkan ke tahap berikutnya.

Saya mengisi dialog Customize Connection seperti pada gambar berikut ini:

Menambahkan informasi koneksi

Menambahkan informasi koneksi

Setelah itu, saya men-klik tombol Next.   Saya membiarkan schema SOLID terpilih (sesuai dengan nama user yang saya berikan) dan langsung men-klik tombol Next.   Saya kemudian mengisi Input connection name dengan TimesTen Database LATIHAN (boleh di-isi dengan nama apa saja), kemudian men-klik tombol Finish.

NetBeans tidak dapat menampilkan daftar tabel untuk database TimesTen, tetapi ia tetap dapat membantu saya dalam mengerjakan perintah SQL.   Saya men-klik kanan nama koneksi TimesTen Database LATIHAN, kemudian memilih menu Execute Command….   Akan muncul sebuah editor baru dimana saya bisa mengerjakan perintah SQL untuk koneksi tersebut, seperti yang terlihat pada gambar berikut ini:

Mengerjakan SQL dari NetBeans

Mengerjakan SQL dari NetBeans

Ok, sekarang saya mendefinisikan sebuah koneksi database.   Saya dapat mulai membuat kode program tanpa perlu ‘mengetik‘ (baca: sihir).   Pada window Projects, saya men-klik kanan Source Packages, kemudian memilih New, JFrame Form… seperti yang diperlihatkan oleh gambar berikut ini:

Membuat JFrame baru

Membuat JFrame baru

Bila menu ini tidak terlihat, saya dapat mencarinya dengan memilih Other….

Pada dialog yang muncul, saya mengisi Class Name dengan ProdukView dan mengisi package dengan com.wordpress.thesolidsnake.view.   Setelah itu, saya men-klik tombol Finish.

Pada visual editor yang muncul, saya men-drag komponen Table ke layar utama seperti yang terlihat pada gambar berikut ini:

Menambahkan komponen tabel

Menambahkan komponen tabel

Kemudian, saya men-klik kanan pada komponen yang baru saja saya tambahkan dan memilih menu paling awal yaitu Table Contents….   Pada kotak dialog Customizer Dialog yang muncul, saya memilih Bound, kemudian men-klik tombol Import Data to Form… seperti yang diperlihatkan oleh gambar berikut ini:

Melakukan binding data dari database

Melakukan binding data dari database

Pada dialog Import Data To Form, saya memilih koneksi database dan tabel seperti yang terlihat pada gambar berikut ini (saya mengandaikan bahwa tabel PRODUK sudah dibuat sebelumnya):

Mengambil informasi dari database

Mengambil informasi dari database

Setelah itu, saya men-klik tombol OK.   NetBeans akan memunculkan pesan menunggu untuk proses importing….   Setelah selesai, Binding Source secara otomatis akan terisi dengan produkList.

Saya akan berpindah ke tab Columns untuk mendefinisikan kolom yang akan ditampilkan.   Saya menambahkan dua buah kolom untuk tabel tersebut, sesuai dengan kolom yang ada di database, seperti yang terlihat pada gambar berikut ini:

Menambahkan kolom untuk tabel

Menambahkan kolom untuk tabel

Setelah itu, saya men-klik tombol Close untuk menutup dialog.

Sekarang, saya akan menjalankan aplikasi dengan men-klik tombol Run Project (atau menekan F6).   Pada dialog Run Project yang muncul, saya memastikan bahwa com.wordpress.thesolidsnake.view.ProdukView terpilih sebagai main class, lalu men-klik tombol OK.   Tabel akan muncul terisi dengan data dari database, seperti yang diperlihatkan oleh gambar berikut ini:

Tampilan program saat dijalankan

Tampilan program saat dijalankan

Sesuai dengan janji saya, tidak ada satu barispun kode program yang saya ketik disini.   Tapi dibalik layar, NetBeans akan memakai JPA untuk mengakses database!   Yup, JPA seperti pada plugin simple-jpa yang saya buat untuk Griffon, bukan plain SQL di JDBC.   Dengan demikian, NetBeans juga menghasilkan kode program yang membuat dan memakai EntityManager.   Bukan hanya itu, NetBeans juga menghasilkan sebuah domain class (atau tepatnya JPA Entity) dengan nama Produk yang sudah lengkap dengan named query, seperti yang diperlihatkan pada gambar berikut ini:

JPA Entity bernama Produk yang dihasilkan NetBeans

JPA Entity bernama Produk yang dihasilkan NetBeans

Kode program yang dihasilkan oleh NetBeans memakai org.jdesktop.swingbinding.JTableBinding untuk melakukan binding. Sebagai perbandingan, pada simple-jpa, binding dilakukan melalui SwingBuilder (bawaan Groovy) dan GlazedLists.   Untuk menentukan ekspresi setiap kolom, NetBeans memakai Expression Language (seperti pada JSP dan JSF).   Sebagai perbandingan, pada simple-jpa, saya memakai Groovy template engine yang menerima seluruh ekspresi Groovy.

Memakai Materialized View Di Oracle TimesTen

Satu kekurangan dari MySQL yang terasa saat saya beralih ke database tersebut adalah tidak adanya fasilitas materialized view.   Fasilitas materialized view pertama kali diperkenalkan oleh Oracle Database, baru kemudian diikuti oleh database lainnya.   Hingga saat ini, Oracle MySQL masih belum mendukung materialized view (beberapa berusaha mensimulasikannya melalui trigger!).

Lalu, apa itu materialized view?   Apa bedanya dengan view biasa?

Untuk menunjukkan perbedaannya, saya akan membuat tabel seperti berikut ini:

Command> CREATE TABLE produk (
       >   kode CHAR(3) PRIMARY KEY,
       >   nama VARCHAR2(50) NOT NULL
       > );

Command> CREATE TABLE stok (
       >   tanggal DATE NOT NULL DEFAULT SYSDATE PRIMARY KEY,
       >   nomorFaktur CHAR(3) NOT NULL,
       >   kodeProduk CHAR(3) NOT NULL,
       >   jumlah NUMBER NOT NULL,
       >   FOREIGN KEY (kodeProduk) REFERENCES produk(kode)
       > );

Command> INSERT INTO produk VALUES ('AAA', 'Produk A');
1 row inserted.

Command> INSERT INTO produk VALUES ('BBB', 'Produk B');
1 row inserted.

Command> INSERT INTO produk VALUES ('CCC', 'Produk C');
1 row inserted.

Command> INSERT INTO produk VALUES ('DDD', 'Produk D');
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '001', 'AAA', 10);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '001', 'BBB', 20);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '001', 'CCC', 30);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '002', 'AAA', -5);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '003', 'BBB', -10);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '003', 'CCC', -20);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '004', 'AAA', 5);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '004', 'BBB', 4);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '004', 'CCC', 3);
1 row inserted.

Pada contoh tabel di atas, saya memiliki daftar perubahan produk (misalnya, bertambah akibat pembelian dan berkurang akibat penjualan) pada tabel stok.   Biasanya, saya sering kali menampilkan nama produk beserta jumlah yang tersedia berdasarkan perubahan stok tersebut.   Untuk itu, saya dapat menggunakan query SQL berikut ini:

Command> SELECT p.kode, p.nama, COALESCE(SUM(s.jumlah),0) FROM 
       > produk p LEFT JOIN stok s ON p.kode = s.kodeProduk GROUP BY p.kode, p.nama;
< AAA, Produk A, 10 >
< BBB, Produk B, 14 >
< CCC, Produk C, 13 >
< DDD, Produk D, 0 >
4 rows found.

Banyak database sudah mendukung apa yang disebut sebagai view (atau disebut juga non-materialized view).   Biasanya view dipakai untuk menyederhanakan SQL yang menggabungkan beberapa tabel sehingga dapat diakses melalui view tersebut.   Sebagai contoh, untuk membuat sebuah view berdasarkan query di atas, maka saya dapat memberikan perintah seperti berikut ini:

Command> CREATE VIEW persediaan AS
       > SELECT p.kode, p.nama, COALESCE(SUM(s.jumlah),0) jumlah FROM
       > produk p LEFT JOIN stok s ON p.kode = s.kodeProduk
       > GROUP BY p.kode, p.nama;

Command> SELECT * FROM persediaan;
< AAA, Produk A, 10 >
< BBB, Produk B, 14 >
< CCC, Produk C, 13 >
< DDD, Produk D, 0 >
4 rows found.

Pada contoh di atas, saya sudah membuat sebuah view baru yang diberi nama persediaan.   Sifat view adalah non-materialized sehingga tidak ada data yang ‘disimpan’ pada view tersebut.   Setiap kali query dilakukan pada view persediaan, maka SQL asalnya yang melakukan agregasi SUM akan selalu dikerjakan kembali!   Sebagai buktinya, lihat hasil explain yang men-select dari view persediaan:

Command> EXPLAIN SELECT * FROM persediaan;

Query Optimizer Plan:

  STEP:                1
  LEVEL:               3
  OPERATION:           TblLkRangeScan
  TBLNAME:             SOLID.PRODUK
  IXNAME:              PRODUK
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

  STEP:                2
  LEVEL:               3
  OPERATION:           TblLkRangeScan
  TBLNAME:             SOLID.STOK
  IXNAME:              TTFOREIGN_2
  INDEXED CONDITION:   S.KODEPRODUK >= P.KODE
  NOT INDEXED:         <NULL>

  STEP:                3
  LEVEL:               2
  OPERATION:           MergeJoin(Left OuterJoin)
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   P.KODE = S.KODEPRODUK
  NOT INDEXED:         <NULL>

  STEP:                4
  LEVEL:               1
  OPERATION:           GroupBy
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

Berbeda dengan view, sebuah materialized view akan ‘menyimpan’ nilai dari tabel sumber (disebut jadi detail table).   Sebagai contoh, kode SQL berikut ini akan membuat sebuah synchronous materialized view:

Command> CREATE MATERIALIZED VIEW sync_persediaan AS
       > SELECT kodeProduk, SUM(jumlah) jumlah, COUNT(*) entri
       > FROM stok
       > GROUP BY kodeProduk;
3 rows materialized.

Command> SELECT * FROM sync_persediaan;
< AAA, 10, 3 >
< BBB, 14, 3 >
< CCC, 13, 3 >
3 rows found.

Karena beberapa keterbatasan, saya tidak menghasilkan materialized view yang sama seperti non-materialized view di atas.   Setiap kali saya men-select dari sync_persediaan, saya akan memperoleh hasil yang sama tanpa adanya query ulang dari tabel sumber.   Dengan demikian, proses membaca dari materialized view bukanlah sesuatu yang berat, seperti yang ditunjukkan oleh hasil explain berikut ini:

Command> EXPLAIN SELECT * FROM sync_persediaan;

Query Optimizer Plan:

  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkSerialScan
  TBLNAME:             SYNC_PERSEDIAAN
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

Lalu, kapan sebuah materialized view diperbaharui?   Setiap kali saya melakukan perubahan pada tabel sumber (disebut juga detail table). Sebagai contoh, bila saya menambahkan sebuah item baru untuk stok, maka sync_persediaan akan diperbaharui, seperti yang ditunjukkan pada hasil explain berikut ini:

Command> EXPLAIN INSERT INTO stok VALUES (SYSDATE, '005', 'DDD', 10);

Query Optimizer Plan:

  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkInsert
  TBLNAME:             STOK
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

  STEP:                2
  LEVEL:               2
  OPERATION:           RowLkSerialScan
  TBLNAME:             SOLID.SYNC_PERSEDIAAN
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         SOLID.SYNC_PERSEDIAAN.KODEPRODUK = 'DDD'

  STEP:                3
  LEVEL:               1
  OPERATION:           OneGroupGroupBy
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

  STEP:                4
  LEVEL:               1
  OPERATION:           RowLkUpdView
  TBLNAME:             SOLID.SYNC_PERSEDIAAN
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

Kesimpulannya: Gunakan materialized view bila query untuk pada materialized view tersebut sering dilakukan.   Bila perubahan pada tabel sumber (detail table) terlalu sering, lebih baik menggunakan view (disebut juga non-materialized view).

Untuk mengatasi masalah kinerja yang timbul akibat perubahan tabel sumber (materialized table),  TimesTen memungkinkan apa yang disebut sebagai asynchronous materialized view.   Sebagai contoh, saya membuat sebuah asynchronous materialized view dengan memberikan perintah berikut ini:

Membuat asynchronous materialized view

Membuat asynchronous materialized view

Materialized view bernama async_persediaan di atas akan diperbaharui setiap 10 menit.   Bila tidak ada perubahan tabel sumber pada saat materialized view diperbaharui, maka tidak akan ada yang dikerjakan.   Dengan demikian, perubahan pada tabel sumber tidak akan langsung mempengaruhi asynchronous materialized view.

Pada contoh ini, bila pengguna melakukan perubahan jumlah stok (akibat menambah atau menghapus faktur), maka isi dari async_persediaan tidak akan berubah selama interval 10 menit belum tercapai.   Hal ini biasanya tidak menjadi masalah, kecuali bila pengguna tiba-tiba membuka halaman untuk melihat persediaan (atau mencetak laporan)!   Untuk mengatasi hal tersebut, saya dapat memaksa agar asynchronous materialized view segera diperbaharui saat halaman persediaan akan ditampilkan, dengan menggunakan perintah SQL REFRESH MATERIALIZED VIEW seperti berikut ini:

Command> SELECT * FROM async_persediaan;
< AAA, 30, 4 >
< BBB, 14, 3 >
< CCC, 13, 3 >
3 rows found.

Command> REFRESH MATERIALIZED VIEW async_persediaan;

Command> SELECT * FROM async_persediaan;
< AAA, 50, 5 >
< BBB, 14, 3 >
< CCC, 13, 3 >
3 rows found.

Memakai ttIsql di Oracle TimesTen

ttIsql adalah sebuah tool bawaan Oracle TimesTen yang dapat dipakai untuk berinteraksi dengan database.   Tool ini bekerja mirip seperti SQL*Plus untuk Oracle Database.   Bagi pengguna MySQL Server, tool ini mirip seperti client CLI mysql.

Pada saat menjalankan ttIsql, saya dapat menyertakan DSN untuk database yang akan dipakai, seperti:

C:\>ttIsql LATIHAN

Pada perintah di atas, saya akan melakukan koneksi ke DSN bernama LATIHAN (tidak case-sensitive di Windows).   Bila ini adalah koneksi pertama kalinya, maka TimesTen akan me-load database dari file ke memori.

Bila ingin menyertakan atribut koneksi, maka pada saat menjalankan ttIsql, saya dapat memberikan parameter -connStr seperti berikut ini:

C:\>ttIsql -connStr "DSN=LATIHAN;UID=SolidSnake"

Pada contoh di atas, saya melakukan koneksi ke DSN LATIHAN dengan menyertakan atribut UID (nama user) berupa seorang external user bernama SolidSnake.   Bila connection attribute sudah didefinisikan saat membuat DSN, maka connection attribute yang saya atur saat membuat DSN yang akan dipakai.

Setelah itu akan muncul prompt Command> dimana saya dapat mengetikkan perintah khusus ttIsql dan juga perintah SQL.   Sebagai contoh, bila ingin membuat sebuah tabel baru, saya dapat memberitan perintah SQL berupa CREATE TABLE seperti berikut ini:

Command> CREATE TABLE sensus (
       >   age NUMBER,
       >   workclass VARCHAR(20),
       >   fnlwgt NUMBER,
       >   education VARCHAR(20),
       >   education_num NUMBER,
       >   marital_status VARCHAR(20),
       >   occupation VARCHAR(20),
       >   relationship VARCHAR(20),
       >   race VARCHAR(20),
       >   sex CHAR(6),
       >   capital_gain NUMBER,
       >   capital_loss NUMBER,
       >   hours_per_week NUMBER,
       >   native_country VARCHAR(20)
       > );

Selain mengetik perintah secara satu per satu, saya juga dapat menyuruh ttIsql untuk mengerjakan seluruh perintah dalam file yang telah saya siapkan sebelumnya.   Sebagai contoh, perintah SQL untuk mengisi tabel di atas terletak pada sebuah file bernama data.sql.   Untuk mengerjakan file tersebut, saya memberikan perintah seperti berikut ini:

Command> run E:\data.sql

Untuk melihat daftar tabel yang dimiliki oleh user, saya dapat memberikan perintah berikut ini:

Command> tables;
  SOLIDSNAKE.SENSUS
1 table found.

Untuk melihat seluruh tabel yang ada, saya menggunakan perintah berikut ini:

Command> alltables;
  SOLIDSNAKE.SENSUS
  SYS.ACCESS$
  SYS.ARGUMENT$
  SYS.CACHE_GROUP
  SYS.COLUMNS
  SYS.COLUMN_HISTORY
  SYS.COL_STATS
  SYS.DEPENDENCY$
  SYS.DIR$
  SYS.DUAL
  SYS.ERROR$
  SYS.IDL_CHAR$
  SYS.IDL_SB4$
  SYS.IDL_UB1$
  SYS.IDL_UB2$
  SYS.INDEXES
  SYS.MONITOR
  SYS.NCOMP_DLL$
  SYS.OBJ$
  SYS.OBJAUTH$
  SYS.OBJERROR$
  SYS.PLAN
  SYS.PLSCOPE_ACTION$
  SYS.PLSCOPE_IDENTIFIER$
  SYS.PROCEDURE$
  SYS.PROCEDUREINFO$
  SYS.PROCEDUREPLSQL$
  SYS.REPSTATS
  SYS.SEQUENCES
  SYS.SETTINGS$
  SYS.SNAPSHOT_DESCRIPTION
  SYS.SNAPSHOT_INFO
  SYS.SNAPSHOT_LATCH_INFO
  SYS.SNAPSHOT_REPL_PEER
  SYS.SNAPSHOT_VALUE_CGGROUP
  SYS.SNAPSHOT_VALUE_CKPTHIST
  SYS.SNAPSHOT_VALUE_CONFIG
  SYS.SNAPSHOT_VALUE_GENERIC
  SYS.SNAPSHOT_VALUE_GRIDMEMBER
  SYS.SNAPSHOT_VALUE_GRIDNODE
  SYS.SNAPSHOT_VALUE_LATCH
  SYS.SNAPSHOT_VALUE_LOGHOLD
  SYS.SNAPSHOT_VALUE_PARAWT
  SYS.SNAPSHOT_VALUE_PLSQL
  SYS.SNAPSHOT_VALUE_REPL
  SYS.SNAPSHOT_VALUE_SQL
  SYS.SNAPSHOT_VALUE_XLA
  SYS.SOURCE$
  SYS.STATNAMES
  SYS.SYN$
  SYS.SYSAUTH$
  SYS.SYSTEMSTATS
  SYS.SYSTEM_PRIVILEGE_MAP
  SYS.TABLES
  SYS.TABLE_HISTORY
  SYS.TABLE_PRIVILEGE_MAP
  SYS.TAB_SIZES$
  SYS.TBL_STATS
  SYS.TCOL_STATS
  SYS.TINDEXES
  SYS.TRANSACTION_LOG_API
  SYS.TTABLES
  SYS.TTBL_STATS
  SYS.TT_STATS_PARAM
  SYS.USER$
  SYS.USER_ASTATUS_MAP
  SYS.UTL_RECOMP_COMPILED
  SYS.UTL_RECOMP_ERRORS
  SYS.UTL_RECOMP_SORTED
  SYS.VIEWS
  SYS.WARNING_SETTINGS$
  SYS.XLASUBSCRIPTIONS
  TTREP.CLIENTFAILOVER
  TTREP.REPELEMENTS
  TTREP.REPLICATIONS
  TTREP.REPNETWORK
  TTREP.REPPEERS
  TTREP.REPSTORES
  TTREP.REPSUBSCRIPTIONS
  TTREP.REPTABLES
  TTREP.TTSTORES
81 tables found.

Tabel yang ada di schema SYS dan TTREP dipakai untuk keperluan internal TimesTen.   Satu-satunya tabel yang dibuat oleh user hanya tabel Sensus.

Untuk melihat struktur sebuah tabel, saya dapat menggunakan perintah describe atau sering kali disingkat menjadi desc seperti pada contoh berikut ini:

Command> desc SYS.USER$;

System table SYS.USER$:
  Columns:
    USER#                           TT_INTEGER NOT NULL
    NAME                            TT_CHAR (30) NOT NULL
    TYPE#                           TT_TINYINT NOT NULL
    DEFROLE                         TT_TINYINT NOT NULL
    PASSWORD                        TT_CHAR (60)
    CTIME                           TT_TIMESTAMP NOT NULL
    PTIME                           TT_TIMESTAMP
    LTIME                           TT_TIMESTAMP
    USER$ID                         TT_INTEGER NOT NULL
    IDENTIFICATION                  TT_TINYINT NOT NULL
    ASTATUS                         TT_TINYINT NOT NULL
    SYS1                            BINARY (2) NOT NULL
    SYS2                            BINARY (16) NOT NULL

1 table found.
(primary key columns are indicated with *)

Saya dapat menggunakan perintah history untuk melihat daftar perintah yang pernah diberikan.   Dengan menggunakan tanda seru (!) yang diikuti nomor history,  saya bisa mengerjakan kembali perintah tersebut.   Tanda seru dua kali (!!) akan mengerjakan perintah terakhir.

History di ttIsql

History di ttIsql

Gunakan perintah dssize untuk melihat status memori, seperti contoh berikut ini (satuan dalam MB):

Command> dssize;

  PERM_ALLOCATED_SIZE:      262144
  PERM_IN_USE_SIZE:         17767
  PERM_IN_USE_HIGH_WATER:   17767
  TEMP_ALLOCATED_SIZE:      131072
  TEMP_IN_USE_SIZE:         7931
  TEMP_IN_USE_HIGH_WATER:   7994

Untuk melihat ukuran sebuah tabel,  saya dapat memanggil procedure ttComputeTabSizes() diikuti dengan perintah tablesize seperti yang diperlihatkan oleh contoh berikut ini (satuan dalam bytes):

Command> call ttComputeTabSizes('sensus');
Command> tablesize sensus;

Sizes of SOLID.SENSUS:

  INLINE_ALLOC_BYTES:   10565856
  NUM_USED_ROWS:        32129
  NUM_FREE_ROWS:        127
  AVG_ROW_LEN:          327
  OUT_OF_LINE_BYTES:    0
  METADATA_BYTES:       5328
  TOTAL_BYTES:          10571184
  LAST_UPDATED:         2013-09-14 10:17:37.000000

1 table found.

Untuk melihat nilai dari seluruh connection attribute yang ada, gunakan perintah show all. Untuk mengubah nilai sebuah connection attribute, gunakan perintah set, seperti yang diperlihatkan pada contoh berikut ini:

Command> show autocommit
autocommit = 1 (ON)
Command> set autocommit 0

Command> show autocommit
autocommit = 0 (OFF)

ttIsql mendukung definisi variabel dan dapat mengerjakan PL/SQL anonymous block, seperti yang ditunjukkan pada gambar berikut ini:

Variabel dan PL/SQL di ttIsql

Variabel dan PL/SQL di ttIsql

Untuk mengukur waktu eksekusi sebuah query (berdasarkan pemanggilan ODBC),  saya dapat menggunakan perintah timing.   Hal ini sebaiknya diikuti dengan perintah verbosity 0 sehingga waktu selama mencetak ke layar tidak akan ikut dihitung.   Berikut ini adalah contoh tampilan waktu eksekusi untuk data sensus yang terdiri atas 32.129 baris atau sekitar 4.6 MB bila berdasarkan ukuran file backup SQL INSERT-nya:

Command> timing 1;

Command> verbosity 0;

Command> SELECT * FROM sensus;
Execution time (SQLExecute + Fetch Loop) = 0.120389 seconds.

Command> SELECT COUNT(sex) FROM sensus WHERE sex = 'Female';
Execution time (SQLExecute + Fetch Loop) = 0.003853 seconds.

Command> SELECT COUNT(age) FROM sensus WHERE age < 20; 
Execution time (SQLExecute + Fetch Loop) = 0.003528 seconds. 

Command> SELECT SUM(fnlwgt) FROM sensus;
Execution time (SQLExecute + Fetch Loop) = 0.027481 seconds.

Mengelola User Di Oracle TimesTen

Hari ini saya mendapatkan sebuah trophy yang mengingatkan bahwa usia blog ini sudah mencapai lima tahun:

Happy anniversary!

Happy anniversary!

Lima tahun yang lalu, saya berhenti menulis blog subjektif yang penuh lelucon, komentar dan opini pribadi 🙂  Saya kemudian mendaftar di WordPress.com dan membuat blog TheSolidSnake ini.  Saya mulai belajar menulis secara serius dan objektif.   Apalagi, seorang sosok eksentrik di Indonesia, R*y S*ryo, pernah meremehkan blogger dengan menyatakan bahwa: “Blog tidak bertanggung jawab, bahkan blogger itu tukang tipu”.  Oleh sebab itu, saya tidak ingin menjadi blogger penerjemah yang hanya menerjemahkan dokumentasi dari bahasa Inggris menjadi bahasa Indonesia.   Visi saya saat itu adalah menuliskan informasi yang sulit diperoleh dalam bahasa Indonesia atau sesuatu yang menarik bagi saya.   Kini, hasilnya setelah lima tahun, adalah sebuah blog yang membosankan dan terkadang sulit dicerna.  Tulisan pertama saya di blog ini adalah mengenai salah satu produk Oracle yang sangat populer di lingkungan kerja, Oracle Database.  Hari ini saya kembali lagi bereksperimen dengan produk database lainnya dari Oracle yang bernama Oracle TimesTen.

Oracle TimesTen mengenali dua jenis user, yaitu internal user dan external user.   Seorang external user yang men-install TimesTen boleh melakukan apa saja terhadap database tersebut sehingga ia disebut juga sebagai instance administrator.   Untuk melihat seluruh user yang ada, saya dapat menggunakan perintah SQL berikut:

C:\>ttIsql LATIHAN

Command> SELECT * FROM SYS.ALL_USERS;
< SYS, 0, 2013-09-13 22:36:07.442000 >
< TTREP, 2, 2013-09-13 22:36:07.442000 >
< SYSTEM, 3, 2013-09-13 22:36:07.442000 >
< GRID, 4, 2013-09-13 22:36:07.442000 >
< SOLIDSNAKE, 10, 2013-09-13 22:36:07.442000 >
5 rows found.

Internal user adalah user yang dibuat dan disimpan di dalam database TimesTen itu sendiri melalui perintah SQL CREATE USER.   Pada hasil query di atas, terlihat bahwa Oracle TimesTen secara otomatis membuat 4 internal user, yaitu SYS, TTREP, SYSTEM dan GRID.   Mereka adalah user yang dipakai oleh TimesTen itu sendiri.

External user adalah user yang disimpan di sistem operasi.   Pada hasil query di atas, user SOLIDSNAKE adalah external user.   TimesTen tidak menyimpan password untuk external user karena mereka dikelola oleh sistem operasi.   TimesTen juga tidak meminta password untuk external user karena sebelum menjalankan TimesTen pastinya mereka sudah login terlebih dahulu di sistem operasi.

Bila saya melakukan koneksi tanpa memberikan atribut UID (nama user), maka user yang dipakai adalah external user dengan nama yang sama sesuai dengan user di sistem operasi.   Hanya external user selaku instance administrator yang boleh melakukan koneksi ke database untuk pertama kalinya (ingat bahwa koneksi pertama kali akan memindahkan file database ke memori).    Sebagai contoh, pada perintah di atas, saya melakukan koneksi ke database sebagai instance administrator karena saya sedang login di Windows 7 sebagai user yang men-install Oracle TimesTen.

Untuk membuat sebuah internal user baru, saya dapat menggunakan perintah SQL seperti berikut ini:

C:\>ttIsql LATIHAN

Command> CREATE USER solid IDENTIFIED BY "snake";

User created.

Command> GRANT CREATE SESSION TO solid;

Untuk mendaftarkan sebuah external user baru, saya dapat menggunakan perintah SQL seperti berikut ini:

C:\>ttIsql LATIHAN

Command> CREATE USER solid IDENTIFIED EXTERNALLy;

User created.

Untuk login sebagai user solid, saya dapat memberikan atribut UID yang mewakili nama user dan PWD yang mewakili password, seperti berikut ini:

C:\>ttIsql -connStr "DSN=LATIHAN;UID=solid;PWD=snake"

Sama seperti di Oracle Database, user memiliki schema masing-masing.   User solid tidak dapat mengakses tabel milik user liquid dan sebaliknya.   Pengguna yang berlatar belakang MySQL mungkin dapat membayangkan bahwa sebuah user di TimesTen sebagai sebuah user plus sebuah database dengan nama yang sama bila di MySQL.

Setiap user dibatasi oleh privilege yang dimilikinya.   Privilege ADMIN adalah sebuah system privilege yang merupakan hak akses paling tinggi.   User yang memiliki privilege ini boleh melakukan apa saja!   Secara default, hanya instance administrator dan user internal dari TimesTen yang memiliki privilege ini.   Nantinya, instance administrator boleh memberikan hak ADMIN ke user lain bila dirasa perlu.

Untuk melihat privilege yang dimiliki oleh user, saya memberikan query SQL berikut ini:

C:\>ttIsql LATIHAN

Command> SELECT * FROM SYS.DBA_SYS_PRIVS;
< SYS, ADMIN, YES >
< SYSTEM, ADMIN, YES >
< SOLIDSNAKE, ADMIN, YES >
< SOLID, CREATE SESSION, NO >
4 rows found.

Terlihat bahwa user SOLIDSNAKE memiliki privilege ADMIN sementara user SOLID memiliki privilege CREATE SESSION.   Seorang user minimal harus memiliki privilege CREATE SESSION agar bisa melakukan koneksi ke database.

Instance administrator SOLIDSNAKE dapat memberikan hak akses ADMIN ke SOLID dengan memberikan perintah berikut ini:

Command> GRANT ADMIN TO SOLID;

Command> SELECT * FROM SYS.DBA_SYS_PRIVS;
< SYS, ADMIN, YES >
< SYSTEM, ADMIN, YES >
< SOLIDSNAKE, ADMIN, YES >
< SOLID, CREATE SESSION, NO >
< SOLID, ADMIN, YES >
5 rows found.

Untuk membuang privilege ADMIN milik user SOLID, saya dapat memberikan perintah berikut ini:

Command> REVOKE ADMIN FROM SOLID;

Command> SELECT * FROM SYS.DBA_SYS_PRIVS;
< SYS, ADMIN, YES >
< SYSTEM, ADMIN, YES >
< SOLIDSNAKE, ADMIN, YES >
< SOLID, CREATE SESSION, NO >
4 rows found.

Membuat Database Oracle TimesTen Baru

Oracle TimesTen adalah sebuah database yang dioptimalkan untuk penggunaan in-memory.   Berdasarkan apa yang diiklankan, algoritma yang dipakai TimesTen mengandaikan bahwa seluruh data terdapat di memory sehingga dioptimalisasikan berdasarkan karakteristik memori bukan hard drive.   Kode program aplikasi pun dapat mengambil keuntungan dari database in-memory.   Sebagai contoh, masalah yang sering dihadapi dalam aplikasi bisnis adalah perhitungan total (misalnya total untuk nilai dari transaksi atau total untuk jumlah item yang tersedia).   Umumnya nilai total tersebut disimpan dan diperbaharui secara bertahap setiap kali ada perubahan transaksi (baik itu tambah, edit, atau hapus).   Agregasi seperti ini kerap membuat kode program menjadi lebih rumit dan rentan kesalahan.   Bila memakai database in-memory, kode program dapat melakukan perhitungan total untuk seluruh data yang ada kapan saja tanpa harus terlalu mengkhawatirkan masalah kinerja.

Seperti database in-memory lainnya, Oracle TimesTen tetap membutuhkan hard drive untuk keperluan logging/backup dan untuk menyimpan data secara permanen.   Sebuah database TimesTen diwakili oleh direktori yang berisi file data.   Database ini akan dirujuk oleh sebuah Data Manager DSN (Data Source Name).   Koneksi pertama kali ke sebuah DSN akan memindahkan database TimesTen yang dirujuknya ke memori.

Untuk membuat sebuah Data Manager DSN baru, saya membuka Control Panel, Administrative Tools, dan Data Sources (ODBC).   Pada dialog yang muncul, di tab User DSN, saya men-klik tombol Add… seperti yang terlihat pada gambar berikut ini:

Membuat DSN Baru

Membuat DSN Baru

Pada dialog Create New Data Source yang muncul, saya memilih TimesTen Data Manager 11.2.2, lalu men-klik tombol Finish seperti yang diperlihatkan pada gambar berikut ini:

Memilih Driver

Memilih Driver

Akan muncul sebuah dialog TimesTen ODBC Setup dimana saya bisa melakukan beberapa pengaturan.   Sebagai contoh, saya melakukan pengaturan pada tab Data Store menjadi seperti yang terlihat pada gambar berikut ini:

Atribut Data Store

Atribut Data Store

Data Source Name adalah DSN yang dipakai sebagai pengenal dalam melakukan koneksi nantinya.   Saya meletakkan lokasi dimana file database akan disimpan pada atribut Data Store Path.   Pada konfigurasi di atas, file database akan disimpan di folder C:\latihan dan setiap file database akan diawali dengan nama test.   Pada Database Character Set, saya memilih AL32UTF8 untuk memakai unicode.

Tab berikutnya, First Connection, adalah daftar atribut yang dipakai hanya pada saat koneksi pertama kali ke database (dimana database akan dipindahkan dari file ke memori).   Saya mengisinya seperti terlihat pada gambar berikut ini:

Atribut First Connection

Atribut First Connection

Nilai Permanent Data Size dan Temporary Data Size menentukan jumlah memori yang dipakai oleh database TimesTen ini.   Permanent data adalah bagian memori yang mewakili tabel dan index.   Isi dari permanent data akan dibaca dari file pada saat koneksi pertama kali dan akan disimpan ke file pada proses checkpoint.   Sebaliknya, isi dari temporary data akan hilang pada saat database dibersihkan dari memori.   Temporary data menampung cursor dan struktur data lainnya yang diperlukan pada saat mengerjakan query.   Pada database yang saya buat ini, ukuran permanent data adalah 256 MB dan ukuran temporary data adalah 128 MB.

Sebuah database yang tidak dapat menyimpan data secara permanen tidak akan banyak berguna.   Lalu, bila seluruh data ada di memori, kapan perubahan di memori akan ditulis (disimpan) ke file?   Aplikasi dapat memberikan perintah untuk mengerjakan operasi checkpoint, atau menunggu terjadinya operasi checkpoint.   Checkpoint adalah sebuah proses dimana isi database yang ada di memori akan dipindahkan ke file.   TimesTen mengerjakan checkpoint di balik layar secara otomatis sesuai dengan periode yang telah ditentukan.   Karena checkpoint merupakan operasi yang berat (menulis ke file), maka sebaiknya checkpoint tidak terlalu sering dilakukan.   Jangan lupa bahwa operasi checkpoint yang jarang dikerjakan meningkatkan resiko kehilangan data bila terjadi kegagalan sistem.   Khusus untuk menghindari hilangnya data akibat pemadaman listrik, gunakan UPS.   Untuk mengatur kapan operasi checkpoint dikerjakan, saya mengisi nilai atribut Ckpt Frequency (secs) (per detik) atau Ckpt Log Volume (per jumlah data yang telah diproses).   Sebagai contoh, pada database percobaan ini, saya mengatur agar operasi checkpoint bekerja setiap 1.800 detik atau 30 menit.

Pastikan bahwa tanda centang pada Auto create telah diberikan.   Konfigurasi ini menyebabkan TimesTen akan membuat file database secara otomatis bila sebelumnya tidak ada.   Walaupun demikian, saya tetap perlu membuat folder database (pada konfigurasi saya adalah c:\latihan) secara manual.

Pada tab berikutnya, General Connection, saya tidak melakukan perubahan.   Atribut yang menarik pada tab ini adalah durable commits. Saya tidak memberikan tanda centang di durable commits.   Ini menyebabkan seluruh transaksi saya adalah non-durable commit.   Bila perubahan data bersifat kritis (tidak boleh sampai hilang!), maka saya perlu memberikan tanda centang pada pilihan durable commits sehingga transaksi yang di-commit akan langsung ditulis ke log file pada saat itu juga.   Hal ini akan berdampak pada kinerja database in-memory, tetapi resiko kehilangan data akibat kegagalan sistem juga ikut berkurang.   Selain itu, aplikasi tetap mendapatkan keuntungan kinerja pada operasi SELECT yang hanya membaca data.

Pada tab NLS Connection,  saya memastikan bahwa character set yang dipakai untuk koneksi database sama dengan character set yang dipakai oleh database sehingga TimesTen tidak perlu melakukan konversi.

Atribut NLS Connections

Atribut NLS Connections

Saya tidak akan menjalankan database ini sebagai IMDB Cache (cache bagi Oracle Database) sehingga saya tidak mengubah tab IMDB Cache.   Saya akan melakukan direct connection di komputer yang sama dan tidak melakukan koneksi client-server sehingga saya juga tidak perlu mengatur tab Server.   Demikian juga saya juga membiarkan tab PL/SQL begitu saja tanpa perubahan.

Setelah selesai mengisi seluruh atribut yang ada, saya men-klik tombol OK.   Sampai disini, saya telah membuat Data Manager DSN bernama LATIHAN.

Langkah berikutnya, saya membuka Control Panel, Services untuk memastikan bahwa service TimesTen Data Manager 11.2.2 telah dijalankan.   Alternatif lainnya, saya dapat menjalankan TimesTen Data Manager melalui Command Prompt seperti yang terlihat pada gambar berikut ini:

Menjalankan TimesTen Data Manager dari Command Prompt

Menjalankan TimesTen Data Manager dari Command Prompt

Setelah itu, saya akan memakai tool ttIsql untuk mencoba melakukan koneksi ke database.   Tool ini bekerja mirip seperti mysql di database MySQL Server atau sqlplus di Oracle Database.   Satu hal yang penting adalah koneksi pertama ke database akan membuat file database bila belum dibuat sebelumnya dan memindahkan file database ke memori.   Untuk saat ini, hanya instance administrator saja yang berhak untuk menjalankan database saya pertama kalinya.   Instance administrator adalah user di sistem operasi yang memiliki akses untuk mengelola Oracle TimesTen.

Saya tidak menentukan UID (nama user) di Data Manager DSN yang saya buat sehingga nama user akan diambil dari nama user sistem operasi yang sedang saya pakai saat ini.   Jadi, saya hanya perlu memastikan bahwa user ini adalah user yang sama dengan yang men-install Oracle TimesTen.   Setelah itu, saya memberikan perintah seperti yang terlihat berikut ini:

C:\>ttIsql LATIHAN

Copyright (c) 1996, 2013, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.

connect "DSN=LATIHAN";
Connection successful: DSN=LATIHAN;UID=Snake;DataStore=C:\latihan\test;Dat
abaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;DRIVER=C:\TimesTen\TT
1122~1\bin\ttdv1122.dll;PermSize=256;TempSize=128;LogFlushMethod=1;CkptFrequency
=1800;TypeMode=0;PLSCOPE_SETTINGS=IDENTIFIERS:NONE;RangeIndexType=1;
(Default setting AutoCommit=1)
Command>

Setelah perintah di atas, file database akan dibuat dan isinya akan dipindahkan ke memori.   File database tersebut adalah test.ds0, test.ds1, test.log0, test.res0, test.res1, dan test.res2.   Saya juga dapat melihat status memori dengan memberikan perintah dssize pada ttIsql seperti yang terlihat berikut ini:

Memeriksa status memori

Memeriksa status memori