Oracle Database / SQL Performance & Memory
Investigate bind-sensitive cursor behavior
Different bind values may have very different selectivity. An average execution time can hide that only a narrow input range is slow.
Environment and inputs: Oracle Database 19c baseline · SQL*Plus-compatible SQL unless RMAN, DGMGRL, ADRCI or a host shell is specified. Check release, patch level, edition and container first.
Start with this diagnostic
Use the stated SQL, RMAN, DGMGRL or host shell and replace sample names with approved targets. DBA and V$ views need authorized catalog access in the correct container. Do not grant broad privileges for an example.
SELECT sql_id, child_number, plan_hash_value, is_bind_sensitive, is_bind_aware, executions FROM v$sql WHERE sql_id = 'REPLACE_SQL_ID';Read the result
The flags and plan hashes show cached child behavior. They do not guarantee that the slow execution used the child currently displayed.
Resolve and verify
Compare the failing bind category, datatype and actual plan with a normal case. Test statistics and query changes with both selective and nonselective inputs.
Operational precautions
Protect sensitive bind values. Do not force one plan globally from a single successful test.