It is important to understand that the value of the initialization parameter pga_aggregate_
target is not a hard limit but is rather a target. As a result, if too small a value is specified, the
database engine is free to allocate more memory than the value specified by the initialization parameter pga_aggregate_target. To illustrate this behavior, I executed a query requiring about
60MB of the PGA, with an increasing number of concurrent users (from 1 to 50). The initialization
parameter pga_aggregate_target was set to 1GB. This means at most 17 users (1GB/60MB) should
be able to get the PGA necessary to execute the whole statement in memory. Figure 5-5 shows
the results of the test. As you can see, the system PGA increased up to 1.6GB, which is higher
than the configured value. As expected, the system PGA increases proportionally with the number
of users only up to 17 concurrent users. With more than 17 users, the system started reducing
the amount of the PGA provided to each user.
Figure
Partager