Parallel queries waiting on a session in WAIT EVENT "PGA memory operation"
Does this sound familiar to anyone?
Problem Description
---------------------------------------------------
When running a query with in PARALLEL, on occasion,
- a bunch of Slaves are created
- most of them are waiting on "cursor: pin S wait on X" events. The Final Blocking session for all of these sessions points to a session with an event of "PGA Memory Operation"
- The Session waiting on PGA Memory Operation keeps changing
- The PGA Memory Operation is holding locks for minutes in some cases
- Queries that run in seconds are taking 5-10 minutes
- When the same query is ran in multiple sessions, they all get backed up