Oracle Database / SQL Performance & Memory
Find logical-read-heavy Oracle SQL
Excessive logical reads can consume CPU even when storage latency is low. Inspect per-child buffer gets with execution counts.
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, buffer_gets, ROUND(buffer_gets / NULLIF(executions,0)) AS gets_per_exec FROM v$sql WHERE executions > 0 ORDER BY buffer_gets DESC FETCH FIRST 20 ROWS ONLY;Read the result
The average is based on all recorded executions of that cached child and can hide skew. Buffer gets are not physical disk reads.
Resolve and verify
Compare plans, predicates and expected result size for the affected statement. Test a targeted query or indexing change with representative data.
Operational precautions
Do not create an index solely because buffer gets are high; the access pattern may be an intentional large scan.