The content of the PGA memory varies, depending on whether or not the instance runs the shareed server option, Generally, the PGA memory divided into session memory, cursors, and SQL workarea.
Session memory is the memory allocated to hold a session's variables (logon information) and other information related to the session. For a shared server, the session memory is shared and not private.
Cursor are handles to private memory structure or specific SQL statement
SQL Workarea, for complex queries that use memory intensive operators like following:
- Sort-based operators, such as ORDER BY, GROUP BY, ROLLUP. and window functions
- Hash-join
- Bitmap merge
- Bitmap create
- Write buffers used by bulk load operations
Generally bigger work areas can significantly improve performance of a particular operator at the cost of higher memory consumption.
If you are using automatic memory management to manage the database memory on your system, then you do not need to manually tune the PGA.
