Gather statistic untuk Performance

Cost based optimizer (CBO) menggunakan statistic untuk menentukan execution plan yang paling optimal. Saya pernah membahas sekilas tentang CBO ini di artikel Tuning Query dengan Explain Plan.

DI versi 9i ke bawah, gather statistic dilakukan secara manual dengan package DBMS_STATS. By default di database Oracle 10g, gather statistic ini dilakukan oleh Oracle secara otomatis; selanjutnya kitapun bisa memilih cara melakukan gather statistic ini, apakah secara otomatis atau manual.

Agar Database melakukan gather statistic secara manual, jalankan command berikut di SQL:

conn system
BEGIN
  DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/

Untuk mengembalikan gather statistic berjalan otomatic, jalankan command berikut di SQL:

BEGIN
  DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB');
END;
/

Untuk mempermudah pekerjaan, saya menyarankan untuk menjalankan gather statistic secara otomatis. Untuk database versi 9i, mau tidak mau kita harus melakukan secara manual karena di 9i belum ada feature gather statistic otomatis. Walaupun manual, kita bisa mensiasatinya dengan menjalankan DBMS_STATS lewat DBMS_JOB.

Menggunakan DBMS_STATS

Berikut ini procedure yang ada di dalam package DBMS_STATS yang sering digunakan:

Procedure 			Collects 
========================================================================
GATHER_INDEX_STATS		Index statistics 
GATHER_TABLE_STATS		Table, column, and index statistics 
GATHER_SCHEMA_STATS		Statistics for all objects in a schema 
GATHER_DATABASE_STATS		Statistics for all objects in a database 
GATHER_SYSTEM_STATS		CPU and I/O statistics for the system

Untuk menjalankan GATHER_INDEX_STATS, GATHER_TABLE_STATS, dan GATHER_SCHEMA_STATS bisa dilakukan oleh user (schema) yang bersangkutan. Kalau saya lebih suka memakai user SYSTEM, untuk mempermudah saja.

Contoh:

  1. Gather satistic untuk semua object milik schema DBMON,sample yang diambil statistic 20%.
    DBMS_STATS.GATHER_SCHEMA_STATS('DBMON',20);
    

    Kalau ingin statistic benar-benar valid, kita bisa mengambil sampel 100%. Namun bila tabelnya sangat besar, dan panjang data tiap kolomnya seragam, sebaiknya sampel bisa diperkecil. Sampel yang besar akan memakan waktu gather statistic lebih lama. Mulai versi 9i, berapa besarnya sampel ini bisa kita serahkan ke Oracle; gunakan DBMS_STATS.AUTO_SAMPLE_SIZE.

    DBMS_STATS.GATHER_SCHEMA_STATS('DBMON',DBMS_STATS.AUTO_SAMPLE_SIZE);
    
  2. Gather statistic tabel TAB_DATAHIST_XLDM milik schema DBMON, partisi NULL karena tidak mempunyai partisi, sample yang diambil statistic adalah 20%, index juga diikutsertakan
    DBMS_STATS.GATHER_TABLE_STATS('DBMON','TAB_DATAHIST_XLDM',null,20,cascade=>TRUE);
    
  3. Contoh lebih lanjut dan pembahasan lebih detail, silahkan lihat di referensi

Beberapa informasi statistic tersebut bisa dilihat di view DBA_TABLES dan DBA_INDEXES.

Referensi
Oracle┬« Database Performance Tuning Guide 10g Release 2 (10.2) – Managing Optimizer Statistics
Oracle┬« Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) – DBMS_STATS

This entry was posted in Administration, Performance Tuning and tagged , , , , , , . Bookmark the permalink.

4 Responses to Gather statistic untuk Performance

  1. nunu says:

    mas Rohmad, smoga kabarnya lg baik dan fresh slalu, he…
    gini mas saya punya problem dengan pekerjaan saya dan database oracle, saya ingin melakukan eksport dan import database oracle! permasalahannya data yang sudah terbungkus dalam database oracle ini tidak hanya berupa data teks dan huruf tetapi juga berupa data geometri spasial, saya sudah melakukan eksport dan import apa yang mas publish tapi data geometri/spasialnya ga bisa diimport, jadi databasenya terintegrasi dengan file/data arcgis desktop, mohon petunjuk mas! terimakasih

  2. rohmadne says:

    Export & importnya pakai apa?
    SqlLoader apa exp & imp?
    Apa error message-nya?

  3. wawan says:

    Saya ingin memindahkan database oracle 9i ke komputer lain. Bagaimana caranya?

    Terima kasih

  4. A-JoE says:

    Pagi Pak Rohmad..
    terimakasih artikel2 bapak sangat berguna, ilmu saya jadi bertambah hehehe..
    sy mau bertanya bagaimana cara melihat hasil ( counter ), dari gather schema yg saya jalankan..
    sy jalan Gather modul XLA 10%, tp smpe comment sy buat
    gather statistic sdh running lebih dari 13 jam, akibatnya user2 pd mengeluh lambah :(..

    mohon pencerahan dari bapak terimakasih

Leave a Reply

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