Memanage History Perkembangan Data

Database Administrator (DBA) HARUS aware (tahu) berapa perkembangan data (database) yang tengah dimaintainnya. Ini penting sekali untuk:

  • Dari sisi aplikasi (user), memprediksi jumlah data pada waktu mendatang. Apakah satu, dua, atau sepuluh tahun ke depan aplikasi masih mampu mensupport proses data sebesar itu?
  • Dari sisi infrastruktur, memprediksi kebutuhan storage
  • Bagi manajemen, tentu saja untuk memprediksi budget buat pengadaan storage

Dari view yang by default disediakan Oracle, kita bisa mengetahui pemakaian space/disk/storage. Namun view tersebut hanya untuk mengetahui kondisi saat ini, tidak mencatat history pemakaian waktu-waktu yang lalu.

Filosofinya adalah, setiap hari kita mengcapture pemakaian space saat ini. Nah dengan mencatat pemakaian space tiap hari, akhirnya kita khan bisa melihat perkembangan datanya.

Melihat pemakaian space saat ini (current space utilization)

  • Untuk tablespace dengan content PERMANENT dan UNDO, view-view yang di-query adalah:
    1. DBA_TABLESPACES. Untuk mengambil informasi TABLESPACE_NAME dan CONTENTS. Kolom CONTENTS punya nilai PERMANENT dan UNDO
    2. DBA_DATA_FILES
    3. DBA_FREE_SPACE

    select tablespace,CONTENTS,datafile,freespace
    from
    (select tablespace_name tablespace, CONTENTS from dba_tablespaces) a,
    (select tablespace_name tablespace1, sum(bytes) datafile from dba_data_files group by tablespace_name) b,
    (select tablespace_name tablespace2, sum(bytes) freespace from dba_free_space group by tablespace_name) c
    where tablespace=tablespace1 and tablespace1=tablespace2(+);

  • Untuk tablespace dengan content TEMPORARY, view yang di-query adalah V$TEMP_SPACE_HEADER
    select tablespace_name tablespace,'TEMPORARY' CONTENTS, sum(BYTES_USED+BYTES_FREE) datafile,sum(BYTES_FREE) freespace from V$TEMP_SPACE_HEADER df group by tablespace_name;
  • Untuk melihat pemakaian space secara keseluruhan, union (gabung) dua query di atas. Agar lebih mudah, saya buatkan VIEW saja. Penting: User yang membuat VIEW ini harus punya privilege untuk mengakses secara langsung. Kalau tidak punya privilege akses langsung, tidak akan bisa, bahkan user DBA (system) sekalipun. Dalam contoh ini, administrasi saya lakukan dengan user SYSTEM. Jadi, user SYSTEM harus di beri privilege langsung:
    conn / as sysdba
    grant select on sys.dba_data_files to system;
    grant select on sys.dba_free_space to system;
    grant select on sys.dba_tablespaces to system;
    grant select on sys.V_$TEMP_SPACE_HEADER to system;
  • Berikut ini script untuk membuat VIEW tersebut.
    CREATE OR REPLACE VIEW v_current_space
    (TABLESPACE,CONTENTS, DATAFILE, FREESPACE)
    as
    select tablespace,CONTENTS,datafile,freespace
    from
    (select tablespace_name tablespace, CONTENTS from dba_tablespaces) a,
    (select tablespace_name tablespace1, sum(bytes) datafile from dba_data_files group by tablespace_name) b,
    (select tablespace_name tablespace2, sum(bytes) freespace from dba_free_space group by tablespace_name) c
    where tablespace=tablespace1 and tablespace1=tablespace2(+)
    union
    select tablespace_name tablespace,'TEMPORARY' CONTENTS, sum(BYTES_USED+BYTES_FREE) datafile,sum(BYTES_FREE) freespace from V$TEMP_SPACE_HEADER df group by tablespace_name;

    Kalau user SYSTEM (yang membuat VIEW) ini tidak diberi (grant) privilege untuk query langsung (direct query), akan muncul error berikut ketika mencoba membuat VIEW di atas:

    ORA-01031: insufficient privileges
  • Untuk melihat current space ulitization, cukup query ke view V_CURRENT_SPACE saja
    col TABLESPACE for a20;
    select * from V_CURRENT_SPACE order by CONTENTS,TABLESPACE;

    TABLESPACE           CONTENTS    DATAFILE  FREESPACE
    -------------------- --------- ---------- ----------
    SYSAUX               PERMANENT  387973120    8847360
    SYSTEM               PERMANENT  492830720   30867456
    TEST                 PERMANENT    4194304    4063232
    USERS                PERMANENT   13107200    2359296
    TEMP                 TEMPORARY   20971520   16777216
    UNDOTBS1             UNDO       209715200  202637312

Langkah Selanjutnya, Buat tabel untuk menampung data history
Tabel ini mempunyai kolom yang isinya sama persis dengan VIEW v_current_space, kecuali dengan penambahan beberapa kolom.

create table t_history_space
(NOMOR NUMBER default 0,
WAKTU DATE,
TABLESPACE VARCHAR2(30),
CONTENTS VARCHAR2(20),
TOTAL NUMBER,
FREE NUMBER,
USED NUMBER,
perubahan NUMBER) tablespace users;

Berikutnya, buat prosedur untuk mengcapture pemakaian space saat ini
Proses kerjanya adalah:

  1. Lihat pemakaian space saat ini, pakai VIEW v_current_space
  2. Dapatkan space yang dipakai (USED) saat ini.
    USED = DATAFILE - FREESPACE
  3. Lihat space yang dipakai (USED) yang lalu, di tabel t_history_space
  4. Dapatkan nilai perubahan data (PERUBAHAN)
    PERUBAHAN = USED{current} - USED{yang lalu}
  5. Insert data-data dari VIEW v_current_space, USED, dan PERUBAHAN ke tabel t_history_space

create or replace procedure p_history_space as
v_NOMOR NUMBER;
begin
select nvl(max(nomor),0)+1 into v_NOMOR from t_history_space;
insert into t_history_space
select v_NOMOR, sysdate, A.TABLESPACE, A.CONTENTS, A.DATAFILE, A.FREESPACE, A.USED,(A.USED-B.USED) PERUBAHAN from
(select TABLESPACE,CONTENTS, DATAFILE, FREESPACE,(DATAFILE-FREESPACE) USED from v_current_space) A,
(select TABLESPACE,USED from t_history_space where nomor=v_NOMOR-1) B
where A.TABLESPACE=B.TABLESPACE(+);
commit;
EXCEPTION
WHEN OTHERS THEN -- handles all other errors
ROLLBACK;
end;
/

Berikutnya, buat job yang mengeksekusi prosedur tiap hari
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,'p_history_space;',trunc(SYSDATE+1), 'TRUNC(SYSDATE + 1)');
commit;
END;
/

Gunakan command berikut untuk mengetahui job no yang baru kita bikin tersebut. Hanya valid bila dijalankan si SESSION yang membuat job tersebut.
SQL> print jobno
Atau gunakan command berikut. Bisa dijalankan kapan saja.
SQL> select job from user_jobs where upper(what) like '%P_HISTORY_SPACE%';

Misalkan job no adalah 4. Job ini akan dijalankan mulai besok jam 00.00, dan selanjutnya dijalankan otomatis setiap hari jam 00.00. Jadi sekarang belum dijalankan, makanya tabel t_history_space masih kosong

SQL> select * from t_history_space;
no rows selected

Biar data hari ini terisi, jalankan job tersebut secara manual.
SQL> EXECUTE DBMS_JOB.RUN(4);

Sekarang lihat, sudah ada datanya
SQL> set lines 120
SQL> select * from t_history_space;

     NOMOR WAKTU     TABLESPACE           CONTENTS                  TOTAL       FREE       USED  PERUBAHAN
---------- --------- -------------------- -------------------- ---------- ---------- ---------- ----------
         1 09-JUL-08 TEST                 PERMANENT               4194304    4063232     131072
         1 09-JUL-08 SYSAUX               PERMANENT             387973120    7667712  380305408
         1 09-JUL-08 SYSTEM               PERMANENT             492830720   25231360  467599360
         1 09-JUL-08 USERS                PERMANENT              13107200    2359296   10747904
         1 09-JUL-08 TEMP                 TEMPORARY              20971520   16777216    4194304
         1 09-JUL-08 UNDOTBS1             UNDO                  209715200  201261056    8454144

Melihat perkembangan data
Misalkan setelah 3 hari, kita dapat data history untuk 3 hari. Berikut ini history pemakaian space untuk semua tablespace
SQL> set lines 120
SQL> select * from t_history_space;

     NOMOR WAKTU     TABLESPACE           CONTENTS                  TOTAL       FREE       USED  PERUBAHAN
---------- --------- -------------------- -------------------- ---------- ---------- ---------- ----------
         1 07-JUL-08 TEST                 PERMANENT               4194304    4063232     131072
         1 07-JUL-08 SYSAUX               PERMANENT             387973120    7667712  380305408
         1 07-JUL-08 SYSTEM               PERMANENT             492830720   25231360  467599360
         1 07-JUL-08 USERS                PERMANENT              13107200    2359296   10747904
         1 07-JUL-08 TEMP                 TEMPORARY              20971520   16777216    4194304
         1 07-JUL-08 UNDOTBS1             UNDO                  209715200  201261056    8454144
         2 08-JUL-08 TEST                 PERMANENT               4194304    4063232     131072          0
         2 08-JUL-08 SYSAUX               PERMANENT             387973120    7667712  380305408          0
         2 08-JUL-08 SYSTEM               PERMANENT             492830720   25231360  467599360          0
         2 08-JUL-08 USERS                PERMANENT              24903680    1572864   23330816   12582912
         2 08-JUL-08 TEMP                 TEMPORARY              20971520   16777216    4194304          0
         2 08-JUL-08 UNDOTBS1             UNDO                  209715200  201261056    8454144          0
         3 09-JUL-08 TEST                 PERMANENT               4194304    4063232     131072          0
         3 09-JUL-08 SYSAUX               PERMANENT             387973120    7667712  380305408          0
         3 09-JUL-08 SYSTEM               PERMANENT             492830720   25231360  467599360          0
         3 09-JUL-08 USERS                PERMANENT              24903680    7864320   17039360   -6291456
         3 09-JUL-08 TEMP                 TEMPORARY              20971520   16777216    4194304          0
         3 09-JUL-08 UNDOTBS1             UNDO                  209715200  201261056    8454144          0

Untuk melihat perkembangan data khusus tablespace USERS
SQL> select * from t_history_space where TABLESPACE ='USERS' order by nomor;

     NOMOR WAKTU     TABLESPACE           CONTENTS                  TOTAL       FREE       USED  PERUBAHAN
---------- --------- -------------------- -------------------- ---------- ---------- ---------- ----------
         1 07-JUL-08 USERS                PERMANENT              13107200    2359296   10747904
         2 08-JUL-08 USERS                PERMANENT              24903680    1572864   23330816   12582912
         3 09-JUL-08 USERS                PERMANENT              24903680    7864320   17039360   -6291456

Untuk melihat perkembangan datatabase
SQL> select min(WAKTU) MULAI, max(WAKTU) SAMPAI, sum(TOTAL) TOTAL, sum(FREE) FREE, sum(USED) USED, sum(PERUBAHAN) PERUBAHAN from t_history_space;

MULAI     SAMPAI         TOTAL       FREE       USED  PERUBAHAN
--------- --------- ---------- ---------- ---------- ----------
07-JUL-08 09-JUL-08 3409969152  776798208 2633170944    6291456

Secara intriksik, data yang riel itu disimpan di tablespace PERMANENT. Tablespace UNDO dan TEMPORARY hanya berisi segment-segment yang berfungsi untuk mendukung kinerja database, bukan berisi data riel. Kita bisa menambah clause where contents=’PERMANENT’ pada query di atas.
SQL> select min(WAKTU) MULAI, max(WAKTU) SAMPAI, sum(TOTAL) TOTAL, sum(FREE) FREE, sum(USED) USED, sum(PERUBAHAN) PERUBAHAN from t_history_space where contents='PERMANENT';

MULAI     SAMPAI         TOTAL       FREE       USED  PERUBAHAN
--------- --------- ---------- ---------- ---------- ----------
07-JUL-08 09-JUL-08 2717908992  122683392 2595225600    6291456
This entry was posted in Administration and tagged , , , , , , . Bookmark the permalink.

15 Responses to Memanage History Perkembangan Data

  1. joe says:

    Saya melakukan instal oracle 10g R.2 di Windows Vista Basic tetapi gagal.
    Ada pesan :….. ((Abnormal Program Termination.
    An internal error has occured.
    Please provide the following files to oracle support :
    “Unknown”
    “Unknown”
    “Unknown”))…..
    Bagaimana cara mengatasinya, karena laptop saya OS-nya Genuine Windows VIsta Basic.
    Dan saya ingin segera bisa belajar oracle. Tolong ya Pak… Terima kasih banyak.

  2. rohmadne says:

    Anda pakai source (media installer) yang mana? Di windows Vista, ada source tersendiri yaitu “Oracle Database 10g Release 2 (10.2.0.3/10.2.0.4) for Microsoft Windows Vista and Windows 2008”.

    Mungkin anda menggunakan source “Oracle Database 10g Release 2 (10.2.0.1.0) for Microsoft Windows”

    Coba cek di http://www.oracle.com/technology/software/products/database/index.html

  3. andre says:

    artikelnya bagus sekali mas,sangat bermanfaat

  4. Benny Kusno B says:

    salut buat anda walau sederhana dan side effect yang luas deh buat pengembangan yang lain

  5. Sigit says:

    Mas, kalo cuma mau liat pemakaian space oleh user2(schema) yang ada dalam suatu instance gimana?
    (tidak termasuk system/sys)

  6. rohmadne says:

    Gunakan view dba_extents. Informasi di sini sangat lengkap, misalnya:
    – OWNER (schema)
    – SEGMENT_TYPE (Table, Index, dll)
    – SEGMENT_NAME (nama tabel, nama index, dll)
    – sum (BYTES)

  7. sheandayani says:

    Assalamu’alaikum

    Mas, history di oracle ituw bisa bertahan sampai berapa jam / hari /bulan?
    Apa setelah server dimatikan history perintah sql langsung hilang??

    aq tunggu balesannya. Thanks b4..

  8. rohmadne says:

    History pemakian data yang saya capture ini tentu saja tidak akan hilang selama tidak kita hapus.

    Tentang history perintah SQL, karena disimpan di memory, tentu akan hilang ketika server dimatikan. Namun SQL yang ter-capture oleh AWR (atau perfstat) tidak akan hilang karena disimpan di table.

  9. joko says:

    Mas rohmad, saya mau tanya, saya coba lakukan step2 yang diatas pada beberapa server DB saya, yaitu 9i dan 10g. Untuk yang 9i dengan mengganti tablespace-nya menjadi SYSTEM (bukan USERS), schedule-nya berjalan dengan baik. Namun ada juga 9i lain dengan konfigurasi yang sama tapi schedule-nya juga tidak jalan. Sedangkan untuk 10g, schedule-nya tidak jalan sama sekali. Mas rohmad bisa bantu, kira2 apa yang masih kurang dari step by step yang saya lakukan? trims.

    FYI: saya menggunakan OS windows, dan sudah saya jalankan service2 yang terkait dengan oracle (Scheduler, console, dan instance)

  10. rohmadne says:

    Hallo Mas Joko. Kalau job tidak jalan, cek parameter job_queue_processes
    SQL> show parameter job_queue_processes

    Kalau nilainya 0, tentu saja tidak jalan. Kalau nilainya 2 sementara pada waktu tsb ada job-job lain yang jalan bersamaan, ya bisa jadi job anda ini tidak mendapatkan proses. Naikkan parameter tersebut, contoh:
    SQL> alter system set job_queue_processes=10;

    Kalau parameter job_queue_processes sudah cukup tapi job tidak jalan, coba deh check, jalankan prosedur tersebut secara manual, bisa jalan gak? Contoh:
    SQL> exec p_history_space;

  11. joko says:

    OK mas, sekarang sudah bisa jalan dengan baik. Memang nilainya masih 0 dan sekarang sudah saya ganti.
    Pengetahuan saya tentang oracle masih kurang mas, maklum masih newbie.
    Anyway terima kasih mas rohmad atas infonya.

  12. joko says:

    mas Rohmad, mau tanya lagi nih :), bagaimana kita bisa memanage perkembangan UP or DOWN dari DB server kita? maksud saya lebih pada report statistik dalam 1 bulan misalnya, berapa kali server terjadi down. Darimana saya mendapatkan informasi tsb? apakah dari log file? thanks before.

  13. rohmadne says:

    Hallo Mas Joko.
    Tentang history startup dan shutdown database saya bahas di sini http://rohmad.net/2008/08/07/trigger-history-startup-shutdown-db/

  14. joko says:

    Wah…ok mas rohmad, terima kasih….sudah saya jalankan pada server db saya. Mudah2an bisa berguna buat yang lain juga…:)

  15. maymen says:

    Hallo mas rohmad, saya mau tanya, untuk query diatas yg view tablespace temporary nya, kok ga bisa ya?

    select tablespace_name tablespace,’TEMPORARY’ CONTENTS, sum(BYTES_USED+BYTES_FREE) datafile,sum(BYTES_FREE) freespace from V$TEMP_SPACE_HEADER df group by tablespace_name;

    keluarnya:
    ERROR at line 1:
    ORA-00904: “???TEMPORARY???”: invalid identifier

    Mohon pencerahannya.
    Best regards,

    Maymen

Leave a Reply

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