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.

Continue reading Gather statistic untuk Performance

Partitioning Table: Informasi Segment & Tablespace

Kadang kita bingung, ketika query TABLESPACE_NAME di view DBA_TABLES, kok nilai TABLESPACE_NAME ada yang NULL (blank/kosong).

SQL> select distinct TABLESPACE_NAME from dba_tables;
TABLESPACE_NAME
------------------------------
SYSTEM
                                            < --- Kosong/blank/NULL
USERS
SYSAUX

Tidak perlu bingung, itu tandanya ada tabel berpartisi di database tersebut. Pada tabel yang berpartisi, data disimpan di dalam partisinya. Contoh, lihat tabel berpartisi PENJUALAN_RANGE yang pernah dibahas di Partitioning Table: Definisi dan Contoh.

CREATE TABLE penjualan_range
( no_invoice  NUMBER,
tgl_jual    DATE NOT NULL,
area varchar2(10))
PARTITION BY RANGE (tgl_jual)
(
PARTITION jual_kw1 VALUES LESS THAN (TO_DATE('01-APR-2008','DD-MON-YYYY')) TABLESPACE users,
PARTITION jual_kw2 VALUES LESS THAN (TO_DATE('01-JUL-2008','DD-MON-YYYY')) TABLESPACE users,
PARTITION jual_kw3 VALUES LESS THAN (TO_DATE('01-OCT-2008','DD-MON-YYYY')) TABLESPACE users,
PARTITION jual_kw4 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY')) TABLESPACE users
);

Continue reading Partitioning Table: Informasi Segment & Tablespace

Dasar-dasar Tuning

Berikut ini pertanyaan-pertanyaan seputar tuning:

  1. Bagian mana yang perlu dituning. Siapa yang melakukan?
  2. Dari sisi aplikasi, apanya yang perlu dituning?
  3. Bagian database yang mana yang perlu dituning?
  4. Darimana kita bisa belajar banyak tentang tuning?

Bagian mana yang perlu dituning? Oleh siapa?

  1. Aplikasi, oleh application designer dan developer
  2. Database, oleh database administrator (DBA)
  3. System Operasi (OS), oleh system administrator (sysadmin)

Tuning Aplikasi

Tuning aplikasi memberikan kontribusi tersebar terhadap performa sistem secara keseluruhan. Termasuk bagian-bagian tuning aplikasi adalah:

  1. Normalisasi yang tepat
  2. Pemakaian index
  3. Pemakaian hint dalam query SQL atau PL/SQL
  4. Pemanfaatan sharing cursor
  5. dan lain-lain

Bagian database yang mana yang perlu dituning?

  1. Memory -> PGA dan SGA SGA (shared pool, large pool, buffer cache, redo log buffer, sort area size)
  2. I/O -> distributing I/O, striping, multiple DBWn processes, DBWn I/O slaves.
  3. CPU -> CPU utilization.
  4. Network
  5. Space management -> extent allocation dan Oracle block efficiency.
  6. Redo log dan checkpoint -> redo log file configuration, redo entries, dan checkpoint.
  7. Rollback segment -> sizing rollback segments. Mulai versi 9i, banyakbagian yang dibuat automatic
  8. dan lain-lain

Darimana kita bisa belajar banyak tentang tuning?

Continue reading Dasar-dasar Tuning

Partitioning Table: Definisi dan Contoh

Filosofi partisi adalah memecah tabel ke dalam beberapa segment (partisi atau subpartisi), di mana tabel konvensional hanya mempunyai satu segment.

Misalkan kita punya tabel PENJUALAN dengan 8 juta records, kita ingin query data untuk quartal pertama tahun ini. Pada tabel konvensional (non partition), query akan men-scan keseluruhan 8 juta records data tersebut karena berada dalam 1 segment. Nah, kalau tabel itu dipartisi (by range untuk kolom tanggal penjualan) maka query akan men-scan khusus segment di mana data itu berada; tidak semua 8 juta records data itu di-scan, sehingga proses query lebih cepat.

Manfaat lain dari partitioning adalah tiap-tiap segment (partisi atau subpartisi) bisa ditaruh di tablespace yang berbeda, sehingga kita mendapat manfaat dari spreading (menyebar) tablespace, yaitu penyebaran I/O dan mengurangi resiko loss data karena tablespace corrupt.

Ada 3 metode utama partisi, dan ada 2 macam composite (gabungan):

  1. Range partitioning
  2. List partitioning
  3. Hash partitioning
  4. Composite range-list partitioning
  5. Composite range-hash partitioning

Misalkan saya punya tabel penjualan yang punya kolom no_invoice, tgl_jual, dan area.

CREATE TABLE penjualan
    ( no_invoice 	NUMBER,
      tgl_jual    	DATE,
      area 		varchar2(10));

Dalam artikel ini saya juga akan memberi contoh macam-macam partisi yang bisa dilakukan pada tabel penjualan tersebut.

Continue reading Partitioning Table: Definisi dan Contoh

Tuning Query dengan SQL Trace dan tkprof

Beberapa waktu yang lalu saya membahas tuning query dengan explain plan. Dengan explain plan kita bisa tahu (meng-estimate) nantinya query kita itu memakai “execution plan” yang mana. Sementara dengan SQL trace kita bisa mengetahui query yang sedang berjalan ini menggunakan “execution plan” yang mana. Jadi “explain plan” adalah untuk meramalkan, sedangkan “sql trace” untuk melihat kejadian yang sesungguhnya.

Kelebihan SQL trace adalah SQL trace menampilkan informasi yang lebih banyak. Lebih detail tentang informasi yang bisa digali dari sql trace, silahkan lihat referensi di akhir tulisan ini. Berikut ini langkah-langkah (step-step) untuk mengaktifkan SQL trace:
Continue reading Tuning Query dengan SQL Trace dan tkprof