Oracle Database / SQL Performance & Memory
Find cached SQL with high total elapsed time
A slow system needs a shortlist of statements consuming time, with execution count kept in view. Cached totals are not a historical workload report.
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, executions, elapsed_time, cpu_time, buffer_gets FROM v$sql WHERE executions > 0 ORDER BY elapsed_time DESC FETCH FIRST 20 ROWS ONLY;Read the result
Elapsed and CPU times are cumulative microseconds for each child cursor. A frequently executed cheap query can rank above one individually slow execution.
Resolve and verify
Compare deltas during the affected interval and investigate the application's actual SQL ID. Separate per-execution cost from total load before tuning.
Operational precautions
Cursor aging and restarts reset evidence. Do not infer an individual request's duration directly from cumulative totals.