How to Detect Plan Instability

Use AWR or SQL history views to spot plan changes:

SELECT sql_id, plan_hash_value, COUNT(*)
FROM dba_hist_sql_plan
WHERE sql_id = 'your_sql_id'
GROUP BY sql_id, plan_hash_value
ORDER BY COUNT(*) DESC;

• If there are multiple plan_hash_values for the same sql_id, you’ve had a plan change.
• Look at performance metrics for each plan: which one was faster? More consistent?

SELECT is_bind_sensitive, is_bind_aware, is_shareable
FROM v$sql
WHERE sql_id = 'your_sql_id';

If is_bind_sensitive = Y but is_bind_aware = N, you may be suffering from bind peek instability.