Regresi execution plan terjadi ketika Oracle Cost-Based Optimizer (CBO) mengganti jalur eksekusi query yang efisien dengan plan baru yang suboptimal. Akibatnya, query yang sebelumnya selesai dalam hitungan milidetik mendadak mengonsumsi 100% CPU atau I/O storage. Fitur SQL Plan Management (SPM) menyediakan mekanisme proteksi untuk menjaga stabilitas performa query melalui SQL Plan Baseline.

Root Cause Regresi Plan pada Oracle Database

CBO menentukan access path berdasarkan kalkulasi biaya (cost). Perubahan cost dipicu oleh beberapa faktor utama:

  • Perubahan Optimizer Statistics: Pengumpulan statistik baru (via DBMS_STATS) dengan data skewness ekstrem atau perubahan estimasi kardinalitas akibat update histogram.
  • Perubahan Skema: Penambahan index baru yang tampak optimal di teori, namun memicu Index Skip Scan atau Index Full Scan yang lambat dibanding Full Table Scan paralel.
  • Patching dan Upgrade Database: Perubahan internal optimizer code antar patch set atau rilis Oracle (misal parameter OPTIMIZER_FEATURES_ENABLE).
  • Modifikasi Konfigurasi Environment: Perubahan parameter level sistem seperti OPTIMIZER_INDEX_COST_ADJ atau memory sizing (SGA/PGA).

SPM mencegah CBO langsung mengeksekusi plan baru sebelum plan tersebut terbukti lebih cepat dibanding baseline yang tercatat.

Workflow Verifikasi Stabilitas Query via SPM

Implementasi SPM yang aman tidak mengandalkan auto-capture global di produksi, melainkan proses capture terkontrol, evolusi, dan validasi staging.

1. Capturing Baseline Awal dari Cursor Cache

Langkah pertama adalah mengunci plan yang saat ini berstatus optimal langsung dari SGA (Cursor Cache). Cari nilai SQL_ID dan PLAN_HASH_VALUE dari view V$SQL.

-- Periksa SQL_ID dan PLAN_HASH_VALUE yang stabil
SELECT sql_id, plan_hash_value, executions, elapsed_time/executions as avg_elapsed
FROM v$sql
WHERE sql_id = 'g5q8h4k91z3yx'
ORDER BY avg_elapsed ASC;

Muat plan tersebut ke dalam SQL Plan Baseline menggunakan package DBMS_SPM:

DECLARE
  v_loaded PLS_INTEGER;
BEGIN
  -- ponytail: capture manual per SQL_ID; hindari OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES global pada OLTP tinggi
  v_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id          => 'g5q8h4k91z3yx',
    plan_hash_value => 1982736450,
    fixed           => 'NO',
    enabled         => 'YES'
  );
  DBMS_OUTPUT.PUT_LINE('Baseline loaded: ' || v_loaded);
END;
/

Plan ini otomatis berstatus ACCEPTED = YES, sehingga CBO wajib menggunakannya setiap kali query tersebut dieksekusi.

2. Simulasi Perubahan di Environment Staging

Terapkan perubahan skema (misalnya penambahan index composite) atau regenerasi statistik di database staging yang memiliki volume data representatif. Pastikan parameter OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE aktif.

Ketika query dijalankan di staging setelah perubahan struktur:

  1. CBO menyusun plan baru.
  2. Karena plan baru berbeda dari baseline yang ada, Oracle menyimpannya sebagai plan kandidat dengan status ACCEPTED = NO.
  3. Query tetap dieksekusi menggunakan baseline lama (stabil).

3. Verifikasi dan Perbandingan Plan via Evolve

Jalankan fungsi DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE. Package ini mengeksekusi kedua plan secara berdampingan (test execution), membandingkan elapsed time, CPU time, dan buffer gets.

SET SERVEROUTPUT ON;
DECLARE
  v_report CLOB;
BEGIN
  -- Test eksekusi plan baru vs accepted baseline
  v_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
    sql_handle => 'SQL_8a7d2f9b1c3e4a5d',
    verify     => 'YES',
    commit     => 'NO' -- Evaluasi laporan terlebih dahulu sebelum menerima plan
  );
  
  DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(v_report, 4000, 1));
END;
/

Periksa output laporan. Jika plan baru menunjukkan peningkatan performa signifikan (misal: elapsed time turun 50% tanpa lonjakan buffer gets), plan aman diterima:

DECLARE
  v_report CLOB;
BEGIN
  v_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
    sql_handle => 'SQL_8a7d2f9b1c3e4a5d',
    verify     => 'YES',
    commit     => 'YES' -- Plan baru berubah status menjadi ACCEPTED = YES
  );
END;
/

Otomasi Acceptance Check untuk Pipeline CI/CD Database

Untuk mengamankan rilis database, migrasi baseline yang terverifikasi dari staging ke produksi menggunakan tabel staging.

-- 1. Export Baseline di Staging ke Tabel Perantara
BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE('SPM_STAGING_MIGRATION', 'ADMIN_DBA');
  
  DBMS_SPM.PACK_STGTAB_BASELINE(
    table_name  => 'SPM_STAGING_MIGRATION',
    table_owner => 'ADMIN_DBA',
    sql_handle  => 'SQL_8a7d2f9b1c3e4a5d'
  );
END;
/

-- 2. Transfer tabel SPM_STAGING_MIGRATION ke Produksi via Data Pump (expdp/impdp)

-- 3. Import dan Enforce Plan di Produksi
BEGIN
  DBMS_SPM.UNPACK_STGTAB_BASELINE(
    table_name  => 'SPM_STAGING_MIGRATION',
    table_owner => 'ADMIN_DBA'
  );
END;
/

Gunakan flag FIXED = YES jika Anda ingin menginstruksikan CBO untuk memprioritaskan plan tersebut dan berhenti mengevaluasi kandidat plan baru sama sekali.

Evaluasi Trade-off: Overhead SPM vs Stabilitas

Menerapkan SPM memerlukan pertimbangan arsitektur:

  • Overhead Storage: Data plan baseline disimpan di tablespace SYSAUX. Sistem dengan jutaan SQL unik yang menggunakan auto-capture tanpa bind variable dapat menyebabkan bloating pada SYSAUX.
  • Parse Overhead: Hard parse memakan sedikit lebih banyak siklus CPU karena CBO harus mencocokkan plan yang baru digenerate dengan daftar baseline yang diterima.
  • Stagnasi Plan: Plan baseline yang terlalu ketat (misalnya berstatus FIXED) mencegah query memanfaatkan indeks baru yang sebenarnya lebih menguntungkan, kecuali proses evolve rutin dijalankan.

Rekomendasi Operasional: Hindari menyalakan OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE secara instan di database produksi dengan beban transaksi ad-hoc. Batasi SPM hanya untuk query tier-1 (critical path) dengan metode manual capture via SQL ID atau AWR snapshots.