Description

Dokumen ini memberikan panduan langkah demi langkah (step-by-step) untuk mengidentifikasi, menganalisa, dan mengoptimasi performance query yang lambat (long-running/slow query) pada database Oracle menggunakan fitur Oracle SQL Tuning Advisor (DBMS_SQLTUNE) serta penerapan SQL Profiling sebagai workaround perbaikan tanpa mengubah kode aplikasi.  


Symptom

Petunjuk dalam dokumen ini perlu dieksekusi apabila ditemukan kondisi/gejala sebagai berikut:

  • Terdapat job/batch process atau transaksi di aplikasi yang mengalami penurunan performa drastis, slow response, menggantung (stuck), atau timeout.  

  • Hasil analisa AWR Report atau active session database menunjukkan adanya query spesifik (SQL ID) dengan Elapsed Time, CPU Time, atau I/O Wait Time yang sangat tinggi.  

  • Terjadi indikasi Full Table Scan pada tabel berukuran besar yang membutuhkan optimasi execution plan secara cepat.


Resolution

Lakukan eksekusi SQL Tuning Advisor dan SQL Profiling dengan tahapan berikut 


Step 1 Buat Tuning Task (Create Tuning Task)

Jalankan blok PL/SQL berikut untuk mendaftarkan query yang ingin di-tune berdasarkan SQL ID-nya.  

SQL
DECLARE  l_sql_tune_task_id VARCHAR2(100);
BEGIN  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (    
sql_id      => 'NAMA_SQL_ID_ANDA',            -- Ganti dengan SQL ID target (contoh: '8duv0vck864vh')    
scope       => DBMS_SQLTUNE.scope_comprehensive,    
time_limit  => 3600,                           -- Batas waktu tuning dalam detik (1 jam)    
task_name   => 'NAMA_SQL_ID_ANDA_tuning_task', -- Nama task yang bebas ditentukan    
description => 'Tuning task for statement'  ); 
DBMS_OUTPUT.put_line('Task ID: ' || l_sql_tune_task_id);
END;
/

(Ganti 'NAMA_SQL_ID_ANDA' dengan SQL ID spesifik yang mengalami slow query). 


Step 2 Eksekusi Tuning Task (Execute Tuning Task)

Jalankan task yang baru saja dibuat agar Optimizer menganalisa eksekusi query tersebut:  

SQL
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'NAMA_SQL_ID_ANDA_tuning_task');


Step 3 Cek Laporan Tuning Advisor (Get Tuning Report)

Tampilkan laporan rekomendasi hasil tuning untuk memastikan apakah SQL Profile memang disarankan:  

SQL
SET LONG 65536
SET LONGCHUNKSIZE 65536
SET LINESIZE 2000

SELECT DBMS_SQLTUNE.report_tuning_task('NAMA_SQL_ID_ANDA_tuning_task') FROM DUAL;


Step 4 Terapkan SQL Profile (Accept SQL Profile)

Jika pada laporan di Step 3 terdapat rekomendasi penggunaan SQL Profile, terapkan profile tersebut dengan query:  

SQL
BEGIN  DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(    
task_name  => 'NAMA_SQL_ID_ANDA_tuning_task',   
task_owner => 'SYS',    
replace    => TRUE  );
END;
/

Catatan: Setelah SQL Profile diterapkan, Oracle DB akan otomatis menggunakan execution plan baru yang telah dioptimasi tanpa perlu mengubah kode query/aplikasi.  


(Opsional) Cara Hapus / Rollback SQL Profile

Jika suatu saat Anda ingin menghapus SQL Profile yang sudah diterapkan (misal setelah pembuatan indeks baru selesai):  

SQL
BEGIN  DBMS_SQLTUNE.DROP_SQL_PROFILE(    
name => 'NAMA_SQL_ID_ANDA_tuning_task' -- Atau nama profile yang terbuat  );
END;
/