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