Database Reference
In-Depth Information
Figure 9-5.
The memory manager automatically adjusts the amount of the PGA provided to sessions
It's important to understand that the value of the
pga_aggregate_target
initialization parameter isn't a hard
limit, but is rather a target. As a result, the database engine is free to allocate more memory than the value specified
in the event the value specified is too low. This is allowed because otherwise operations requiring memory that can't
be allocated would fail. You can set a hard limit though, using the
pga_aggregate_limit
initialization parameter
(covered in the next section). That parameter becomes available in version 12.1. It's not available in prior releases.
To show a case of a database instance overallocating the PGA, I executed the previous test by setting the
pga_aggregate_target
initialization parameter to 128MB. In other words, I specified a value that's way too low to
run 50 concurrent sessions requiring about 60MB each. Figure
9-6
shows the results of the test. As you can see, even a
single session wasn't able to get sufficient PGA to execute the query in memory. In fact, that session got only half the
required memory. As the number of concurrent sessions increased, more and more PGA was allocated. At 50 sessions,
about 400MB of PGA was in use—more than three times the configured target.
Figure 9-6.
If the target set through the
pga_aggregate_target
initialization parameter (128MB in this case) is too low,
the memory manager doesn't honor it
