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 1x2x3x4x5 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

This entry was posted in Performance Tuning, SQL and PL/SQL and tagged , , , , , , . Bookmark the permalink.

17 Responses to Tuning Query dengan Explain Plan

  1. harry says:

    mas,,saya mo tanya mengenai optimasi query pake teknik cost based optimization,,
    bisa g jelasin ttg teknik itu n kasih contoh sekalian,,,trus kira2 dimana aku bisa dapetin referensi ttg teknik itu??? trus cara ngeliat execution plan pada mysql gimn??oke itu aja thanks

  2. rohmadne says:

    Hallo Mas Harry,

    Yang saya bahas di artikel ini sebenarnya juga berkaitan dengan cost based optimization (CBO). Inti CBO adalah kita memberikan kekuasaan penuh kepada Oracle untuk menentukan “execution plan” terhadap proses (query-query) di database.

    Cara mengimplementasikan CBO:
    1. Setting parameter OPTIMIZER_MODE (misal: CHOOSE) yang penting nilainya tidak RULE.
    2. Gather statistics (analyze) tabel pakai DBMS_STATS.GATHER_TABLE_STATS

    Dengan statistic yang di-collect itu, Oracle menghitung cost pada alternatif-alternatif “execution plan” yang mungkin.

    Bila data di tabel senantiasa berubah, maka gather statistics juga mesti sering dijalankan untuk mengupdate statistiknya.

    Referensi: http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm

    Tentang “execution plan” di MySQL, saya tidak familiar.

  3. HARRY says:

    JADI BAGAIMANA CARA menerapkan cbo dimysql?

  4. rohmadne says:

    Saya juga tidak familiar dengan implementasi cbo di mysql 🙂

  5. harry says:

    mas,saya mau tanya lagi bagaimana cara menginstal oracle 9i di windows xp,trus bagaimana juga cara menerapkan cbo di oracle 9i trs,apakah sama sprti oracle 10g?

  6. rohmadne says:

    Secara umum, baik versi 9i maupun 10g itu sama. Untuk instalasi, silahkan ikuti wizard-nya, sangat sederhana; just do it.

  7. Homsan says:

    Mas, kalao misal statistic dari table tsb di delete (analyze table dengan option delete statistic ) kira2 gimana yach .. biar perintah pl/sql yang sudah saya bikin bisa maksimal ..?? soalnya kalau saya analyze , perintah query yang sudah saya buat ternyata hasilnya sangat lama.

    kira2 ada enggak cara membuat cepet query tanpa harus melihat statistic dari table tsb / tanpa harus analyze dahulu ( analyze dengan delete option )

    thank’s

  8. rohmadne says:

    Sangat direkomendasikan menggunakan DBMS_STATS, jadi sebaiknya jangan gunakan ANALYZE.

    Untuk membuat statistic
    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME, TABNAME, …)

    Untuk menghapus statistic, gunakan DBMS_STATS.DELETE_TABLE_STATS(OWNNAME,TABNAME)

    ***
    Cara tuning query tanpa melihat statistic:

    Kalau anda tahu karakteristik query anda, misalkan jumlah rows dsb, kita bisa menggunakan hint. Pemakaian hint akan memaksa Oracle mamakai PLAN yang kita minta. Ini referensinya: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm

  9. HARRY says:

    mas.sya pnya query utk mysql:
    utk left join:
    select a.berita_id,a.judul,a.berita, coalesce (count(b.komentar_id),0)as total_komentar from berita a left join komentar b using (berita_id)group by a.berita_id order by berita_id desc
    dengan subquery:
    select a.berita_id,a.judul,a.berita coalesce (b.total_komentar,0)as total_komentar from berita a left join (select berita_id, count (komentar_id)as total_komentar from komentar group by berita_id) b using(berita_id)group by a.berita_id order by berita_id desc
    setelah ke 2 query trbt saya coba di oracle teryata error! kr2 syntak yg bnr utk oracle gmna ya?

  10. rohmadne says:

    Hallo Rekan Harry,
    Jawaban saya ada di komentar untuk artikel berikut
    http://rohmad.net/2008/06/03/tuning-query-dengan-sql-trace-dan-tkprof/

  11. UH says:

    pak,,
    klo step2 untuk liat explain plan bisa munculin view di toad v 9.0.1.8 gimana yak?

    koq saya ndak munculin view apa2..

    tq

  12. rohmadne says:

    Hallo Rekan UH. Saya belum pernah mencobanya lewat TOAD.
    Kalo lewat SQLPlus, pasti bisa.

  13. UH says:

    hehe
    ok makasi pak,,,

    sekarang saya lagi mau tau kalo by tools toad ^^

  14. egha says:

    master rohmad, saya mau tanya tentang teknik join-join di oracle…
    terlebih hash join dan merge join…apa perbedaan keduanya dengan nested join????
    kl boleh saya minta pencerahan beserta contoh query nya…
    0;)

  15. imron says:

    mas rohmad , boleh saya tau bagaimana cara mengidentifikasi query mana yang memakai resources yang besar, sehingga kita bisa mentuning query tersebut

  16. agung says:

    mas saya mau tnya gimana select data dengan jumlah row 6jt sekaligus menjoin nya,
    saya sudah pakai index waktunya bisa 2 menitan.
    bisa ga yah dilakukan lebih cepat lagi select nya, dan pakai apa?

  17. agung says:

    data lama setelah dilakukan groping data.

Leave a Reply

Your email address will not be published. Required fields are marked *