Oracle AWR & ADDM report

Sebelum Oracle versi 10g, kita dapat menggunakan statpack untuk melihat report performansi database. Tapi sejak database Oracle versi 10g, kita dapat dengan mudah menggunakan AWR ((Advanced Workload Repository) report untuk melakukan tuning performance dan melakukan analisis terhadap masalah yang ada dalam sistem
database.

Untuk membuat AWR report sangat mudah, tinggal eksekusi script awrrpti.sql kemudian ikuti perintahnya:

  oracle$ sqlplus / as sysdba
  SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql

Setiap database biasanya secara default melakukan snapshot dari performansi sistem setiap satu jam sekali dan kemudian menyimpannya dalam repository statistik. Repository tersebutlah yang digunakan untuk membuat AWR report.

AWR dapat dibuat berdasarkan satu atau beberapa snapshot. Contoh list snapshot yang bisa dipilih ketika akan men-generate AWR report.

                                                           Snap
   Instance     DB Name        Snap Id    Snap Started    Level
   ------------ ------------ --------- ------------------ -----
   mydb1        MYDB             14897 15 Apr 2007 22:00      1
                                 14898 15 Apr 2007 23:00      1
                                 14899 16 Apr 2007 00:00      1

Kita dapat juga melakukan snapshot secara manual kapanpun diinginakan dengan perintah berikut

  oracle$ sqlplus / as sysdba
  SQL> exec dbms_workload_repository.create_snapshot;

Referensi:

• Oracle Database Performance Tuning Guide

Selain AWR, tool yang penting untuk mengecek atau menganalisis performansi adalah ADDM (Automated Database Diagnostic Monitor), yang memberikan saran untuk tuning pada sistem database misalnya

• CPU bottlenecks
• Undersized Memory Structures
• I/O capacity issues
• Beban (load) yang tinggi pada SQL statements, kompilasi dan eksekusi PL/SQL atau Java
• Masalah pada cluster (RAC)
• Masalah yang berhubungan dengan aplikasi yang menggunakan database
• Masalah pada konfigurasi database
• Masalah concurrency (busy buffer)
• Masalah pada object-object database lainnya.

Untuk menjadlankan ADDM gunakan perintah berikut:

  oracle$ sqlplus / as sysdba    
  SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql

Referensi:

• Automatic Performance Diagnostics

Catatan: Kedua tools tersebut dapat digunakan lewat antarmuka web yaitu pada Oracle Enterprise Manager (OEM) console yang biasanya bisa diakses lewat URL

  http://localhost:5500/em

Link berikut berguna untuk menganalisis hasil ADDM:

• Statspack 101: Interpreting Your Statspack Report
  
• Interpreting the Statspack report
  

Update atau insert di ORACLE

Kasus sinkronisasi data pada database sering kali kita hadapi. Kita perlu memasukan data baru atau mengubah/memperbarui data yang sudah ada.

Dalam kasus ini jika kita menggunakan ORACLE, kita dapat menggunakan perintah SQL 'MERGE'. Dengan perintah MERGE kita tidak perlu mengecek data yang ada apakah akan di-insert atau di-update karena data sudah ada.

Dibawah ini contoh query untuk update atau insert ke database yang diimplementasikan pada class PreparedStatement:

MERGE INTO app_user dest
USING (SELECT ? AS userid, ? AS first_name FROM dual) src
  ON (dest.userid = src.userid)
WHEN MATCHED THEN
     UPDATE SET dest.first_name = src.first_name
WHEN NOT MATCHED THEN
     INSERT (dest.userid, dest.first_name)
     VALUES (src.userid, src.first_name) 

Pada query diatas akan dilihat apakah dalam tabel app_user sudah ada userid yang dimasukan dalam variabel pertama (tanda tanya pertama), jika ada maka field first_name akan di-update dengan variabel kedua (tanda tanya kedua) tapi jika tidak ada maka varibel pertama dan varibel kedua akan di-insert pada tabel.

SID/Service name/TNS name untuk Oracle XE

Sudah coba Oracle XE (Express Edition)?

Setelah instalasi saya bingung: SID/Service name/TNS name apa yang digunakan Oracle XE karena saya perlu tau untuk melakukan koneksi dari client menggunakan JDBC driver.

Ternyata Oracle XE menggunakan 'XE' sebagai SID. Ini bisa dilihat di file tnsnames.ora
Jadi kalau kamu pakai Oracle XE dan menggunakan JDBC untuk mengaksesnya, gunakan URL berikut:

jdbc:oracle:thin:@localhost:1521:XE

Oracle database link

Oracle database link sederhanya adalah untuk melakukan proses (query) dari saru koneksi database ke database lain. Hari ini karena keperluan tidak sengaja saya membutuhkan database link dan mendapatkan caranya.

Dokumentasinya bisa dilihat disini.

Sebenarnya saya hanya ingin membandingkan dua data dari tabel yang berada pada database yang berbeda. Kasusnya kedua tabel bernama REFERENCES pada schema bernama DATA, yang satu berada di mesin production dengan service name "production" dan satu lagi di mesin yang lain dengan service name "development".

Query yang diperlukan adalah seperti ini:

SELECT description FROM data.REFERENCES@production
MINUS
SELECT description FROM data.REFERENCES@development;

Pertama lakukan koneksi ke mesin development kemudian:

CREATE DATABASE LINK prod_link CONNECT TO data IDENTIFIED BY datapasswd USING 'production';

Setelah selesai matikan session:

ALTER SESSION CLOSE DATABASE LINK prod_link;

Dan hapus database link:

DROP DATABASE LINK prod_link;

Selesai.

Gratis: IBM DB2 Express-C Database

Seperti Oracle, IBM juga memberikan versi gratis dari produk databasenya yaitu DB2 Express.
Database DB2 Express-C (community edition) adalah versi gratis dari DB2 Express, bedanya DB2 Express-C hanya boleh digunakan pada mesin sampai dengan 2 CPU dan memory 4 GB saja, dan tentu tidak mendapatkan support komersial dari IBM. DB2 Express-C juga memiliki beberapa batasan fitur dibanding versi yang tidak gratis.

DB2 Express-C is completely "free" to download, develop, deploy, test, run, embed, and redistribute. For more information click here

Dengan adanya versi gratis seperti ini, ISV tidak perlu beli untuk menggunakan dan mempaketkannya bersama software yang dibuatnya. Dan jika konsumen yang membeli software membutuhkan database yang lebih powerfull, maka ISV tinggal memberikan opsi untuk updgrade databasenya ke versi yang lebih besar, dalam hal ini untuk DB2 bisa menggunakan DB2 Enterprise Server Edition (ESE).

Gratis: Oracle Database 10g Express Edition

Oracle Database 10g XE benar-benar gratis, dan dapat didistribusikan secara bebas. Jadi kita bisa buat aplikasi dan menjualnya dalam satu paket dengan Oracle Database 10g XE.

Hanya saja Oracle edisi ini memiliki batasan seperti yang tertera di situsnya:

"Oracle Database XE can be installed on any size host machine with any number of CPUs, but this free version of the world's leading database will store up to 4GB of user data, use up to 1GB of memory, and use one CPU on the host machine."

Oracle: Hati-hati dengan data NULL

Jika kita jalankan procedure dibawah, apa yang akan kita dapatkan?

  CREATE OR REPLACE PROCEDURE example AS

     init 	   NUMBER;

     result	   NUMBER;

  BEGIN

     init   := 25;

     result := init + NULL;

     dbms_output.put_line('Result = '|| result);

  END;

Suatu operasi aritmetika pada varibel not null dengan varibel null, hasilnya null.

Apa yang kita dapatkan jika kita menjalankan query dibawah ini dan field column1 pada tabel my_table memiliki data NULL?

  SELECT * FROM my_table WHERE column1 <> 0;

Kita tidak mendapatkan record yang column1 berisi NULL.

Karena saya bukan DBA atau database programmer, sempat juga membuat kesalahan . Jadi hati-hati dengan NULL. Baca Oracle "PL/SQL User's Guide and Reference" bagian Expressions and Comparisons, Handling Nulls:

• comparisons involving nulls always yield NULL
• applying the logical operator NOT to a null yields NULL
• in conditional control statements, if the condition yields NULL, its associated sequence of statements is not executed

Oracle: Membuat synonym

Kadang kita perlu mempunyai user lain yang memiliki otoritas/role yang lain untuk mengakses suatu schema. Misalnya saja kita butuh user yang memiliki hak akses read only pada suatu schema.

Misalkan kita buat user FOO adalah user yang memiliki akses read only ke schema BAR. User A akan kerepotan dalam mengakses object di schema BAR, karena setiap dia akan mengakses object di schema BAR maka harus menuliskan BAR.NAMA_OBJECT.

Untuk memudahkannya kita bisa buat sysnonym dari table, view, procedure, function dan package object yang ada di schema BAR untuk si FOO.


set heading off;
set linesize 200;
set feedback off;

spool c:\create_synonym.sql

SELECT 'CREATE SYNONYM ' || object_name || ' FOR ' || owner ||'.'|| object_name || ';' script
FROM all_objects
WHERE owner = 'BAR'
AND object_type IN ('PROCEDURE','FUNCTION','TABLE','VIEW','PACKAGE');

spool off;


Java Date().getTime() dan TO_DATE Oracle

Di Java kita biasa menggunakan getTime() dari class java.util.Date untuk mendapatkan nilai long dari waktu saat ini. Method getTime() akan menghasilkan nilai (number) milidetik yang merupakan selisih dari tanggal saat ini dengan tanggal 1 Januari1970, 00:00:00 GMT. Hal yang sama juga kita dapatkan dengan memanggil System.currentTimeMillis().

Jika kita menggunakan nilai tersebut untuk menyipan informasi tanggal pada database oracle, bagaimana kita mendapatkan tanggal dari nilai tersebut?

Misalkan kita punya tabel DATETABLE dan memiliki kolom DATEMILLIS yang berisi nilai long dari getTime(), maka kita bisa mendapatkan tanggal dengan cara :

SELECT TO_DATE('01011970 00:00:00','DDMMYYYY HH24:MI:SS') + DATEMILLIS/86400000 FROM DATETABLE


Jangan percaya cost dari oracle optimizer?

Consider Cost or Time - trying to compare the COST of two queries. Diskusi dari website asktom ini cukup menarik, karena kita biasanya Oracle DBA menggunakan cost untuk menentukan query mana yang performancenya bagus (bisanya berarti query yang eksekusinya cepat).

Diskusinya sangat panjang, sudah ada sejak tahun 2000 tapi sedikit potongan komentar tom ini mungkin bisa jadi acuan :

You cannot compare costs across queries, even if the queries are "identical".



You cannot make any judgments about the relative performance of a query based on

the cost (queries with tiny costs may take days to run, queries with huge costs

could run in milliseconds).

Result Set dari Oracle function/procedure

Oracle procedure ataupun function dapat mengebalikan nilai dengan tipe cursor, tipe data cusrsor mirip dengan ResultSet di Java, jadi kita bisa menggunakan nilai balikan function/procedure cursor menjadi ResultSet pada program Java.

Dibawah ini contoh Oracle function dan procedure yang menghasilkan curson

  create or replace package types as

     type cursorType is ref cursor;

  end;

  /



  create or replace function sp_ListEmp return types.cursortype as

     l_cursor    types.cursorType;

  begin

     open l_cursor for select ename, empno from emp order by ename;

     return l_cursor;

  end;

  /



  create or replace procedure getemps( p_cursor in out types.cursorType ) as

  begin

      open p_cursor for select ename, empno from emp order by ename;

   end;

  /

Penggunaan pada program Java-nya seperti ini:

   java.sql.Connection conn = null;

   ...

    String query = "begin :1 := sp_listEmp; end;";      

   CallableStatement cstmt = conn.prepareCall(query);

   cstmt.registerOutParameter(1,OracleTypes.CURSOR);

   cstmt.execute();

   ResultSet rset = (ResultSet)cstmt.getObject(1); 

Referensi: http://asktom.oracle.com/~tkyte/ResultSets/

Oracle: Function Based Indexes bisa mempercepat eksekusi function

Beberapa waktu yang lalu proyek saya mempunyai masalah pada performance database. Ini pertama kalinya saya berhadapan dengan masalah performance yang cukup besar. Masalah adalah pada proses batch, proses untuk mengambil data dari file-file teks, parsing, validasi, dan memasukannya ke database Oracle dalam jumlah yang sangat besar.

Proyek selesai, masalah performansi dapat diatasi dengan tuning pada parameter database dan sedikit pada query. Tapi setelah beberapa waktu, database production mengalami masalah yang sama, data semakin besar dan tim mulai melakukan perombakan-perombakan besar pada query.

Database specialist mulai melihat procedure/function yang ada di Oracle database. Salah satu komentarnya ketika melihat banyakannya function TRUNC() digunakan adalah hal itu bisa dipercepat dengan membuat indeks pada fungsi. Aneh kedengarannya, fungsi bisa di-indeks? Tapi memang benar, fungsi pada suatu kolom pada tabel tertentu bisa di-indeks. Jadi intinya, kita membuat indeks untuk suatu kolom dengan menggunakan function pada nilai-nilai kolom tersebut. Dengan demikian, execution plan pada query dengan menggunakan function yang tadinya "TABLE ACCESS (FULL)" akan menjadi "INDEX (RANGE SCAN)" yang berarti akan lebih cepat.

Contoh dibawah ini adalah cara untuk membuat function based indexes pada kolom FIRST_NAME di tabel EMPLOYEE dengan function upper(FIRST_NAME) :

   create index EMP_UPPER_INDEX on EMPLOYEE(upper(FIRST_NAME));

Tapi untuk menggunakan Function Based Indexes, kita perlu hati-hati karena ada kelemahannya juga yaitu proses insert dan update akan menjadi lebih lambat kira-kira 5 kali. Untuk proses insert yang banyak dalam satu waktu seperti pada proses batch, akan menjadi delay yang sangat signifikan. Jadi perlu pertimbangan yang detail untuk menerapkan stategi baru, karena biasanya tidak terlepas dengan pertimbangan keuntungan dan kerugiannya. Artikel referensi ini harus dibaca untuk pengertian yang lebih baik tentang function based indexes :

• Function Based Indexes
  

PL/SQL coding standard

Minggu ini adalah minggu pertama kali saya benar-benar harus coding dengan PL/SQL. Membaca code orang memang cukup melelahkan apalagi dengan gaya yang sedikit acak-acakan, identasi tiap line yang kurang bagus, penamaan variabel yang tidak mudah dimengerti (hati-hati memberi nama varibel di PL/SQL jangan sampai sama dengan nama kolom dalam query select yang ada di procedure/function yang dibuat), dan cara-cara yang tidak efektif.

Memang tidak mudah untuk membuat program pertama kali langsung bagus, maka dari itu kita perlu belajar dulu bagaimana membuat program yang baik bukan sekedar membuat program yang berjalan dengan benar (hasil outputnya benar). Ciri utama program yang baik adalah selalu memperhatikan berbagai aspek. Dari sisi bisnis program itu haruslah benar, dan ini hanya terjadi jika program kita telah ditest. Program yang benar belum bisa dikatakan benar walaupun kelihatannya begitu. Dari sisi sistem, program yang baik haruslah memperhatikan aspek-aspek seperti performance, security, maintainability dan lain-lain.

Itulah sebabnya diawal pekerjaan kita, kita perlu membuat coding style standard atau mengikuti standar yang sudah dibuat orang. Kita juga perlu mencari tips/tricks bagaimana menulis program yang baik.

Tidak mudah untuk menemukan PL/SQL coding standard. Masih lebih mudah untuk mencari tips/tricks untuk coding yang baik, yang meperhatikan performance (kecepatan mempereroleh/memanipulasi data) pada database, karena performance untuk data yang besar pada database selalu jadi masalah. Website dibawah ini adalah salah satu dokumen PL/SQL coding standard yang bisa jadi referensi untuk membuat program dengan PL/SQL di Oracle :

• Some Naming and Coding Standards for SQL and PL/SQL