Hari-hari Kepo

Advanced Tuning Oracle Database: Strategi Pro untuk Performa Maksimal (19c/21c)

24 Oct 2025 6 menit baca
Advanced Tuning Oracle Database: Strategi Pro untuk Performa Maksimal (19c/21c)

Di dunia enterprise, Oracle Database masih menjadi tulang punggung banyak sistem kritis:

  • ERP (seperti SAP, Oracle E-Business Suite)
  • Core banking
  • Data warehouse besar
  • Sistem logistik & supply chain
 

Tapi seiring data tumbuh eksponensial, performa database sering jadi botleneck:

Query lambat.
Locking muncul tiba-tiba.
CPU server selalu 90%.
User complain: “Kenapa laporan butuh 30 menit?”

 

Padahal… masalahnya bukan hardware, melainkan konfigurasi, SQL yang tidak optimal, atau struktur storage yang kurang tepat.

 

Inilah saatnya melakukan advanced tuning — bukan sekadar index here and there, tapi pendekatan sistematis untuk mengoptimalkan performa Oracle dari dalam ke luar.

 

Di artikel ini, kamu akan pelajari strategi tuning tingkat lanjut yang digunakan oleh DBA profesional di perusahaan skala enterprise, lengkap dengan:

 
  • 🔍 Identifikasi bottleneck
  • 🛠️ Teknik SQL & Instance tuning
  • 💾 Optimasi I/O & Memory
  • 📊 Tools bawaan Oracle
  • ✅ Best practices 2025
 
 

🧭 Roadmap Advanced Tuning Oracle

TAHAP
TUJUAN
1. Diagnosa Awal
Cari penyebab utama kemacetan
2. SQL Tuning
Perbaiki query paling boros
3. Instance Tuning
Atur memory & proses
4. Storage & I/O
Optimasi tablespace & disk
5. Concurrency Control
Minimalkan lock & latch contention
6. Monitoring & Preventif
Gunakan AWR, ASH, ADDM
 

🔎 Langkah 1: Diagnosa – Temukan "Penyakit" dengan Tools Oracle

Sebelum obat, harus tahu dulu apa yang salah.

 

Gunakan 3 tools utama Oracle:

 

1. AWR (Automatic Workload Repository)

Rekaman kinerja database setiap 60 menit (default).

 
sql
 
-- Generate AWR Report
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Pilih:

  • Format: html
  • Begin/End Snap ID (lihat dari DBA_HIST_SNAPSHOT)
 

🔍 Fokus pada:

  • Top 5 Timed Foreground Events → tung tung tung!
  • SQL with highest Elapsed Time, CPU Time, Gets
  • Wait events seperti db file sequential read, log file sync
 

2. ASH (Active Session History)

Detail aktivitas sesi aktif per detik.

 
sql
 
-- Cek session aktif boros
SELECT sql_id, event, COUNT(*)
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24 -- 1 jam terakhir
GROUP BY sql_id, event
ORDER BY COUNT(*) DESC;
 

3. ADDM (Automatic Database Diagnostic Monitor)

Oracle yang langsung kasih saran!

 
sql
 
-- Jalankan ADDM
DECLARE
task_name VARCHAR2(30) := 'ADDM_Task_1';
BEGIN
DBMS_ADVISOR.CREATE_TASK (
advisor_name => 'ADDM',
task_name => task_name);
DBMS_ADVISOR.SET_TASK_PARAMETER(task_name, 'START_SNAPSHOT', 100);
DBMS_ADVISOR.SET_TASK_PARAMETER(task_name, 'END_SNAPSHOT', 101);
DBMS_ADVISOR.EXECUTE_TASK(task_name);
END;
/
 

Lihat hasil di:

sql
 
SELECT message FROM dba_advisor_findings WHERE task_name = 'ADDM_Task_1';
 
 

🧩 Langkah 2: SQL Tuning – Musuh Utama Performa

80% masalah performa datang dari SQL yang tidak efisien.

 

🔹 Identifikasi SQL Problematic

sql
 
-- Cari SQL dengan logical reads tinggi
SELECT sql_id, executions, elapsed_time, buffer_gets, cpu_time
FROM v$sqlarea
ORDER BY buffer_gets DESC
FETCH FIRST 10 ROWS ONLY;
 

🔹 Gunakan SQL Tuning Advisor

sql
 
-- Buat tuning task
DECLARE
task_name VARCHAR2(30);
BEGIN
task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => 'abc123def456',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'TUNE_BAD_SQL');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name);
END;
/
 
-- Lihat rekomendasi
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_BAD_SQL') FROM dual;
 

💡 Rekomendasi umum:

  • Tambah composite index
  • Ubah query pakai partition pruning
  • Hindari SELECT \*, gunakan kolom spesifik
  • Batasi ROWNUM atau LIMIT di query besar
 

🔹 Force Plan Stability (SQL Plan Management)

Kalau optimizer tiba-tiba ganti execution plan → performa anjlok.

 

Solusi: SQL Plan Baseline

 
sql
 
-- Load plan ke baseline
BEGIN
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => 'abc123def456');
END;
/
 
 

🧠 Langkah 3: Instance Tuning – Atur Memori & Proses

🔹 Optimasi SGA & PGA

Pastikan memori cukup dan proporsional.

 
sql
 
-- Cek alokasi memori
SHOW PARAMETER sga_target;
SHOW PARAMETER pga_aggregate_target;
 

🎯 Rule of thumb:

  • SGA: 60–70% RAM fisik (untuk OLTP)
  • PGA: 20–30% RAM fisik
  • Jika pakai Automatic Memory Management (AMM), nonaktifkan jika ada contention
 
sql
 
ALTER SYSTEM SET memory_target = 0 SCOPE=SPFILE; -- Nonaktifkan AMM
ALTER SYSTEM SET sga_target = 12G SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target = 4G SCOPE=SPFILE;
 

Restart instance.

 

🔹 Shared Pool & Library Cache

Kalau sering lihat library cache lock, artinya SQL belum ter-cache.

 

✅ Solusi:

  • Gunakan bind variables
  • Tingkatkan shared_pool_size
  • Kurangi CURSOR_SHARING = FORCE hanya jika perlu
 
 

💾 Langkah 4: Storage & I/O Optimization

🔹 Tablespaces & Datafiles

Gunakan OMF (Oracle Managed Files) atau atur manual untuk kontrol lebih.

 
sql
 
-- Buat tablespace dengan extent management lokal
CREATE TABLESPACE app_data
DATAFILE '/u01/oradata/PROD/app_data01.dbf' SIZE 10G
AUTOEXTEND ON NEXT 1G
SEGMENT SPACE MANAGEMENT AUTO;
 

🔹 Partitioning untuk Tabel Besar

Bagi tabel > 10 juta baris dengan partition.

 

Contoh: Partition by range (tanggal)

sql
 
CREATE TABLE sales (
id NUMBER,
sale_date DATE,
amount NUMBER
) PARTITION BY RANGE (sale_date) (
PARTITION p_q1 VALUES LESS THAN (TO_DATE('2025-04-01','YYYY-MM-DD')),
PARTITION p_q2 VALUES LESS THAN (TO_DATE('2025-07-01','YYYY-MM-DD'))
);
 

Manfaat:

  • Query lebih cepat (partition pruning)
  • Backup/restore per partisi
  • Mudah manage data lama
 
 

🔐 Langkah 5: Kendali Concurrency – Lock & Latch

🔹 Cek Locking

sql
 
-- Cari session yang block
SELECT blocking_session, sid, serial#, wait_class, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;
 

🔹 Reducing Enqueue Contention

  • Hindari hot blocks dengan INITRANS tinggi
  • Gunakan ASSM (Automatic Segment Space Management)
  • Untuk sequence, tambahkan CACHE 1000 NOORDER
 
sql
 
CREATE SEQUENCE ord_seq START WITH 1 INCREMENT BY 1 CACHE 1000 NOORDER;
 
 

📈 Langkah 6: Monitoring & Preventif

🔹 Jadwalkan AWR Report Harian

bash
 
# Contoh script otomatis via cron
sqlplus / as sysdba @generate_awr_daily.sql
 

🔹 Gunakan EM Express atau OEM

  • Oracle Enterprise Manager Express (gratis)
  • OEM Cloud Control (enterprise)
  • Pantau real-time: session, wait events, I/O
 

🔹 Alert Thresholds

Set warning jika:

  • Tablespace > 85%
  • Long-running SQL > 300 detik
  • Undo usage tinggi
 
sql
 
 
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GT,
warning_value => '85',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GT,
critical_value => '95',
observation_period => 1,
consecutive_occurrences => 1,
instance_name => '',
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => 'APP_DATA');
END;
/
 
 

🏁 Penutup: Tuning Bukan Satu Kali, Tapi Budaya

Advanced tuning bukan proyek sekali jalan.
Ia adalah proses berkelanjutan yang harus jadi bagian dari budaya operasional IT.

 

Karena:

Database yang sehat = aplikasi yang responsif = user yang puas = bisnis yang lancar.

 

Ingat:

  • Gunakan AWR, ASH, ADDM sebagai "alat dokter"
  • Prioritaskan SQL tuning — itu dampaknya paling besar
  • Atur memory & storage sesuai beban kerja
  • Dan selalu monitor preventif, jangan menunggu down
 

Dengan pendekatan ini, Oracle-mu bukan cuma hidup —
tapi berlari kencang di lintasan enterprise.

BAGIKAN WhatsApp Facebook X Telegram

Baca Juga

Sebelumnya
Android Bisa Jadi Server Linux? Bisa Banget! Pakai Termux & Ini Caranya (Lengka…

Komentar

2
ZAP 09 Aug 2026 14:05
Zaproxy dolore alias impedit expedita quisquam.
ZAP 09 Aug 2026 14:05
Zaproxy dolore alias impedit expedita quisquam.

Tinggalkan Komentar

Kembali ke Blog