Suatu proses (query) sebelum dijalankan, database Oracle menentukan dulu mana langkah-langkah yang paling optimal (efektif dan efesien) yang akan dipilih. Contoh query yang melibatkan 5 tabel, paling tidak ada 1×2x3×4x5 pilihan langkah (execution plan) tabel-table mana yang akan di-joint terlebih dahulu. Urutan join tentu saja menentukan resource (cost) yang akan dipakai.

Untuk database dengan query yang kecil, tuning query dengan explain plan mungkin tidak begitu kelihatan manfaatnya. Namun untuk query yang melibatkan data besar-besaran, wow… benar-benar terasa.

Sebelum menjalankan query, kita bisa melihat “execution plan” mana yang akan dipilih oleh Oracle. Caranya adalah dengan menjalankan “explain plan”. Untuk dapat memanfaatkan feature explain plan ini, berikut langkah-langkahnya:

1. Pastikan bahwa instance parameter OPTIMIZER_MODE tidak sama dengan RULE. (Pilihan value untuk OPTIMIZER_MODE adalah rule, choose, all_rows, first_rows , first_rows_n). Kalau nilainya RULE, maka Oracle tidak akan menentukan execution plan berdasarkan cost-nya, tapi berdasarkan aturan (rule) default-nya Oracle.
2. Jalankan script utlxplan.sql untuk membuat table plan. Ini dijalankan satu kali saja oleh user yang akan melakukan Explain Plan.
   SQL> @?/rdbms/admin/utlxplan.sql
3. Berikut ini contoh command untuk membuat plan dari suatu query
   SQL> explain plan for
select * from b where owner='ROHMAD'
union select * from c where owner='ROHMAD';
4. Setelah itu, lihat execution plan-nya
   Di Oracle 8i
   SQL> @?/rdbms/admin/utlxplsDi Oracle 9i ke atas
   SQL> select * from table(dbms_xplan.display);

Contoh kasus. Saya punya tabel A, B, dan C di schema TEST yang strukturnya sama persis

• Tabel A yang berisi data TABEL dan INDEX dari database.
• Tabel B yang berisi data TABEL dari database.
• Tabel C yang berisi data INDEX dari database.

Jadi content (isi) tabel A adalah sama dengan content table B ditambah content tabel C. Masing-masing tabel punya index untuk kolom OWNER. Kalau saya ingin query data dengan OWNER=’ROHMAD’, mana yang lebih cepat?

1. query di A saja?
   SQL> select * from a where owner='ROHMAD';
2. atau query di tabel B kemudian di UNION (gabung) dengan query di tabel C?
   SQL> select * from b where owner='ROHMAD' union select * from c where owner='ROHMAD';

Untuk mengetahuinya, kita perlu membuat explain plan untuk kedua pilihan query di atas.

1. Buat ketiga tabel dan index contoh tersebut
   SQL> create table a as select * from dba_objects
where OBJECT_TYPE in ('TABLE','INDEX');
SQL> create table b as select * from dba_objects
where OBJECT_TYPE in ('TABLE');
SQL> create table c as select * from dba_objects
where OBJECT_TYPE in ('INDEX');
SQL> create index a_owner on a (owner);
SQL> create index b_owner on b (owner);
SQL> create index c_owner on c (owner);
   Kemudian buat statistiknya (gather statistic)
   SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'TEST', TABNAME => 'A', CASCADE => TRUE, ESTIMATE_PERCENT => 5 , METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', DEGREE => 4, GRANULARITY=> 'DEFAULT');SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'TEST', TABNAME => 'B', CASCADE => TRUE, ESTIMATE_PERCENT => 5 , METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', DEGREE => 4, GRANULARITY=> 'DEFAULT');SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'TEST', TABNAME => 'C', CASCADE => TRUE, ESTIMATE_PERCENT => 5 , METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', DEGREE => 4, GRANULARITY=> 'DEFAULT');
2. Jalankan explain plan untuk query pertama
   SQL> explain plan for
select * from a where owner='ROHMAD';
   Lihat execution plan-nya
   SQL> set lines 120
SQL> select * from table(dbms_xplan.display);
   Jalankan explain plan untuk query kedua
   SQL> explain plan for
select * from b where owner='ROHMAD' union select * from c
where owner='ROHMAD';
   Lihat execution plan-nya
   SQL> set lines 120
SQL> select * from table(dbms_xplan.display);
3. Bandingkan kedua execution plan tersebut. OO… ternyata query kedua lebih besar cost-nya dibandingkan query pertama. Perintah union ternyata menambah pekerjaan tambahan yaitu SORT UNIQUE.

Kesimpulannya, pilih query pertama yang cost-nya lebih kecil

Referensi
Oracle9i Database Performance Tuning Guide and Reference