Scenario: The view CHPOZF_IC_UI_SEARCH_V runs in ~4 seconds on P04 and PRD, but hangs on P05. The fix involves identifying the bad execution plan, capturing the good plan from the healthy instance, and pinning it on P05 using SQL Plan Baselines (SPM) — Oracle's preferred plan stabilization mechanism in 19c.
Phase 1
Identify the SQL on P05 (Slow Instance)
Run the following queries on P05 as apps or system.
Step 1.1 — Find SQL_ID of the slow view query
col sql_id for a15
col plan_hash_value for 9999999999
col avg_sec for 999.99
col last_active for a20
SELECT sql_id,
child_number,
plan_hash_value,
executions,
ROUND(elapsed_time/GREATEST(executions,1)/1e6, 2) avg_sec,
TO_CHAR(last_active_time,'YYYY-MM-DD HH24:MI:SS') last_active
FROM v$sql
WHERE UPPER(sql_text) LIKE '%CHPOZF_IC_UI_SEARCH_V%'
AND sql_text NOT LIKE '%v$sql%'
ORDER BY last_active_time DESC;Step 1.2 — Get full execution plan with actuals
-- Replace &sql_id and &child_number with values from Step 1.1
SELECT * FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
'&sql_id',
&child_number,
'ALLSTATS LAST +PEEKED_BINDS +OUTLINE'
)
);⚠ Save this output! You need the Outline Data section for the SQL Profile in Phase 5.
Step 1.3 — Note the bad plan_hash_value
SELECT plan_hash_value FROM v$sql
WHERE sql_id = '&sql_id' AND child_number = &child_number;
Phase 2
Capture the Good Plan from P04 / PRD
Step 2.1 — Find SQL_ID on P04/PRD
SELECT sql_id,
child_number,
plan_hash_value,
ROUND(elapsed_time/GREATEST(executions,1)/1e6,2) avg_sec
FROM v$sql
WHERE UPPER(sql_text) LIKE '%CHPOZF_IC_UI_SEARCH_V%'
AND sql_text NOT LIKE '%v$sql%'
ORDER BY elapsed_time/GREATEST(executions,1);Step 2.2 — Capture good plan with OUTLINE
SELECT * FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
'&good_sql_id',
&good_child_number,
'ALLSTATS LAST +PEEKED_BINDS +OUTLINE'
)
);Copy the Outline Data hints block from the output — you will use these hints in Phase 5 for SQL Profile creation.
Phase 3
Root Cause Diagnosis on P05
Step 3.1 — Compare optimizer parameters (run on BOTH instances)
SELECT name, value
FROM v$parameter
WHERE name IN (
'optimizer_mode',
'optimizer_features_enable',
'db_file_multiblock_read_count',
'optimizer_adaptive_plans',
'optimizer_adaptive_statistics',
'_optimizer_use_feedback',
'statistics_level'
)
ORDER BY name;
Step 3.2 — Check stats on base tables of the view
-- Find base tables first
SELECT referenced_name AS table_name
FROM dba_dependencies
WHERE name = 'CHPOZF_IC_UI_SEARCH_V'
AND type = 'VIEW'
AND referenced_type = 'TABLE';
-- Check stats freshness
SELECT table_name,
num_rows,
blocks,
last_analyzed,
stattype_locked,
stale_stats
FROM dba_tab_statistics
WHERE table_name IN (
SELECT referenced_name FROM dba_dependencies
WHERE name = 'CHPOZF_IC_UI_SEARCH_V'
AND type = 'VIEW'
AND referenced_type = 'TABLE'
)
ORDER BY last_analyzed NULLS FIRST;Step 3.3 — Check indexes on P05
SELECT i.table_name, i.index_name, i.status, i.visibility,
i.last_analyzed, i.num_rows
FROM dba_indexes i
WHERE i.table_name IN (
SELECT referenced_name FROM dba_dependencies
WHERE name = 'CHPOZF_IC_UI_SEARCH_V'
AND type = 'VIEW'
AND referenced_type = 'TABLE'
)
ORDER BY i.table_name, i.index_name;Step 3.4 — Check adaptive plan / cardinality feedback (19c specific)
SELECT sql_id, child_number, is_resolved_adaptive_plan,
is_reoptimizable, use_feedback_stats
FROM v$sql_shared_cursor
WHERE sql_id = '&p05_sql_id';| Root Cause | P05 Symptom | Fix |
|---|
| Stale / missing stats | Full table scans, bad cardinality estimates | Phase 4 — Gather stats |
Different optimizer_mode | Different plan shape vs P04 | Align parameter in P05 |
| Missing index on P05 | Full scan where P04 uses index range scan | Rebuild index |
| Bind variable peeking mismatch | Different plan_hash_value per execution | Phase 5 — Pin via SPM |
| 19c Adaptive Plans interference | is_reoptimizable = Y | Disable feedback or pin plan |
Phase 4
Fix Option A — Gather Fresh Stats (Try First)
Step 4.1 — Gather stats on base tables
-- Replace 'APPS' / 'YOUR_BASE_TABLE' with actual schema and table name
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'APPS',
tabname => 'YOUR_BASE_TABLE',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
degree => 4,
cascade => TRUE,
no_invalidate => FALSE
);
END;
/Step 4.2 — Flush the specific cursor (preferred over full shared pool flush)
DECLARE
l_address RAW(8);
l_hash_val NUMBER;
BEGIN
SELECT address, hash_value
INTO l_address, l_hash_val
FROM v$sqlarea
WHERE sql_id = '&p05_sql_id';
DBMS_SHARED_POOL.PURGE(
l_address || ',' || l_hash_val, 'C'
);
END;
/Re-run the view query and check avg execution time. If still slow — proceed to Phase 5.
Phase 5
Fix Option B — Pin Good Plan via SQL Plan Baseline (SPM)
Step 5A — Load good plan into SPM on P04/PRD
Run as SYS or user with ADMINISTER SQL MANAGEMENT OBJECT privilege.
DECLARE
l_cnt PLS_INTEGER;
BEGIN
l_cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '&good_sql_id',
plan_hash_value => &good_plan_hash_value
);
DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_cnt);
END;
/Step 5B — Verify baseline created on P04/PRD
col sql_handle for a30
col plan_name for a35
col origin for a20
SELECT sql_handle, plan_name, enabled, accepted, fixed, origin,
TO_CHAR(created,'YYYY-MM-DD HH24:MI') created
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%CHPOZF_IC_UI_SEARCH_V%';Step 5C — Create staging table on P04/PRD
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => 'SPM_XFER_STAGE',
table_owner => 'SYSTEM',
db_version => '19.1.0.0.0'
);
END;
/Step 5D — Pack the baseline into staging table
DECLARE
l_cnt PLS_INTEGER;
BEGIN
l_cnt := DBMS_SPM.PACK_STGTAB_BASELINE(
table_name => 'SPM_XFER_STAGE',
table_owner => 'SYSTEM',
sql_handle => '&sql_handle_from_5B'
);
DBMS_OUTPUT.PUT_LINE('Plans packed: ' || l_cnt);
END;
/Step 5E — Export staging table via DataPump (OS level on P04/PRD)
expdp system/password \
tables=SYSTEM.SPM_XFER_STAGE \
directory=DATA_PUMP_DIR \
dumpfile=spm_xfer_p04.dmp \
logfile=spm_xfer_p04.log
Step 5F — Transfer dump file to P05 server
scp spm_xfer_p04.dmp oracle@p05_host:/u01/app/oracle/admin/P05/dpdump/
Step 5G — Import on P05
impdp system/password \
tables=SYSTEM.SPM_XFER_STAGE \
directory=DATA_PUMP_DIR \
dumpfile=spm_xfer_p04.dmp \
logfile=spm_xfer_p05_import.log \
remap_schema=SYSTEM:SYSTEM
Step 5H — Unpack baseline into P05 SPM repository
DECLARE
l_cnt PLS_INTEGER;
BEGIN
l_cnt := DBMS_SPM.UNPACK_STGTAB_BASELINE(
table_name => 'SPM_XFER_STAGE',
table_owner => 'SYSTEM'
);
DBMS_OUTPUT.PUT_LINE('Plans unpacked: ' || l_cnt);
END;
/Step 5I — Verify and mark baseline as FIXED on P05
-- Confirm it's accepted
SELECT sql_handle, plan_name, enabled, accepted, fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%CHPOZF_IC_UI_SEARCH_V%';
-- Mark as FIXED so optimizer always uses this plan
DECLARE
l_cnt PLS_INTEGER;
BEGIN
l_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle => '&sql_handle',
plan_name => '&plan_name',
attribute_name => 'FIXED',
attribute_value => 'YES'
);
END;
/
Phase 6
Validate the Fix on P05
-- Step 1: Purge old cursor
DECLARE
l_address RAW(8);
l_hash_val NUMBER;
BEGIN
SELECT address, hash_value
INTO l_address, l_hash_val
FROM v$sqlarea
WHERE sql_id = '&p05_sql_id';
DBMS_SHARED_POOL.PURGE(l_address||','||l_hash_val,'C');
END;
/
-- Step 2: Re-run view query, then check new plan
SELECT * FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
'&new_sql_id', NULL,
'ALLSTATS LAST +PEEKED_BINDS'
)
);
-- SUCCESS: Look for this line in output:
-- "SQL plan baseline SQL_PLAN_xxx used for this statement"✓ Confirmation: If the plan output contains SQL plan baseline SQL_PLAN_xxx used for this statement — the fix is successful.
Summary
Decision Flow — Which Fix to Apply
P05 view query is slow? │ ├── Stats stale/missing? ──YES──► Gather stats (Phase 4) → retest │ ├── optimizer params differ? ──YES──► Align parameter with P04 │ or set at session level │ ├── Index missing on P05? ──YES──► Rebuild index → gather stats │ └── Plan just wrong? ──────────► SPM Baseline transfer (Phase 5)
