Tuning Query dengan Explain Plan


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×2×3×4×5 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s