Oracle Database / SQL Performance & Memory
Investigate many child cursors for one SQL statement
A single SQL ID can have many children because its executions cannot share one cursor environment. Collect the mismatch reasons before changing caches.
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, bind_mismatch, optimizer_mismatch, auth_check_mismatch FROM v$sql_shared_cursor WHERE sql_id = 'REPLACE_SQL_ID';Read the result
Y flags indicate reasons associated with child non-sharing, not a complete ranking of performance cost. Match them to the children and application environments.
Resolve and verify
Compare bind metadata, optimizer settings and parsing identities. Standardize the demonstrated mismatch and measure parse behavior after correction.
Operational precautions
Do not flush the shared pool as a routine fix; it destroys useful evidence and can cause a hard-parse surge.