This content has been marked as final. Show 3 replies
If you want to be scientific about it, run your queries with various hinted plans, and query v$sql_workarea to see what memory they need to run optimally.
Here is an excellent discussion of the problem you may be facing:
This is undocumented parameter and you can use it after Oracle Support suggestion. PGA limitations is 100 Mb per process, so if you use Parallel Execution, you can involve more PGA.
Instead of using hints or undocumented parameters even, you may consider to use WORKAREA_SIZE_PLOLICY=MANUAL in your critical sessions and give them a large SORT_AREA_SIZE then.
"Don't believe it, test it!"