SGA contains many memory areas:
- Database Buffer Cache
- In-Memory Column Store
- Redo Log Buffer
- Shared Pool
- Large Pool
- Java Pool
- Streams Pool
- Fixed SGA
The two major areas are shared pool and large pool in SGA. According to Oracle 12c Database Documentation:
Memory Architecture : Large Pool. The large pool is used in 3 major areas.
- RMAN buffer
- For I/O server processes, backup, and restore operations, Oracle Database allocates buffers that are a few hundred kilobytes in size.
- UGA for shared servers, which contains:
- Private SQL Area (private cursors, not shared cursors)
- SQL Work Areas (hash joins)
- Session Variables
- Message buffer for parallel queries
Is the private SQL area always in UGA? The answer is yes. May check the following quote from the same documentation.
Do not confuse a private SQL area, which is in the UGA, with the shared SQL area, which stores execution plans in the SGA.
But the location of UGA depends on the sessions mode, that is, dedicated or shared server mode.
- Dedicated server: PGA
- Shared server: SGA
- Large pool
- Shared pool, if there's no large pool