Oracle Database / SQL Performance & Memory
Check whether SQL workareas spill to temporary space
Sorts and hash joins can move from memory to disk when memory or concurrency is insufficient. Inspect workarea execution modes for the affected SQL.
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, operation_type, total_executions, optimal_executions, onepass_executions, multipasses_executions FROM v$sql_workarea WHERE sql_id = 'REPLACE_SQL_ID';Read the result
One-pass and multipass executions indicate workarea spill behavior for cached operations. They do not alone prove that the statement is slow.
Resolve and verify
Compare the plan, input volume and concurrent memory demand. Reduce unnecessary rows or sorts before deciding on a controlled PGA adjustment.
Operational precautions
More memory for one workload can harm others. Preserve a representative concurrency level when testing.