Oracle Database / SQL Performance & Memory
Detect many literal variants of Oracle SQL
An application may issue nearly identical statements with values embedded in the text, reducing reuse and increasing parsing work.
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 force_matching_signature, COUNT(*) AS variants FROM v$sql WHERE force_matching_signature <> 0 GROUP BY force_matching_signature HAVING COUNT(*) > 10 ORDER BY variants DESC FETCH FIRST 20 ROWS ONLY;Read the result
Signatures group statements by normalized shape, while each row still represents a cached child. A large count can include children as well as literal variants.
Resolve and verify
Inspect sanitized samples from the group and correct the application's bind strategy. Measure hard parses and execution latency after a controlled release.
Operational precautions
Do not apply global cursor-sharing settings without plan and correctness testing.