Database Reference
In-Depth Information
To know whether a system experienced an overallocation of PGA, you can use the following query against the
v$pgastat
view. (Note that the query's output shows the final status of the database instance after running the test
shown in Figure
9-6
). If, as shown, the value of
maximum PGA allocated
is much higher than the value of
aggregate
PGA target parameter
, then the value of the
pga_aggregate_target
initialization parameter isn't suitable. In such
a case, it's important to know at which frequency the overallocation happens. For that purpose, the
over allocation
count
statistic indicates the number of times since the last database instance start-up that the system had to allocate
more PGA than specified through the
pga_aggregate_target
initialization parameter. Ideally, that value should be 0:
SQL> SELECT name, value, unit
2 FROM v$pgastat
3 WHERE name IN ('aggregate PGA target parameter',
4 'maximum PGA allocated',
5 'over allocation count');
NAME VALUE UNIT
------------------------------ ---------- -----
aggregate PGA target parameter 134217728 bytes
maximum PGA allocated 418658304 bytes
over allocation count 94
You can also get information through the
v$pgastat
view about the amount of currently allocated PGA and how
much of it's used for auto or manual work areas. The following query illustrates that. Notice that although the statistics
with the
total
prefix provide the current utilization, the statistics with the
maximum
prefix provide the maximum
utilization since the last database instance start-up:
SQL> SELECT name, value, unit
2 FROM v$pgastat
3 WHERE name LIKE '% PGA allocated' OR name LIKE '% workareas';
NAME VALUE UNIT
------------------------------------- ---------- -----
total PGA allocated 999358464 bytes
maximum PGA allocated 1015480320 bytes
total PGA used for auto workareas 372764672 bytes
maximum PGA used for auto workareas 614833152 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 0 bytes
Also notice in this output that only part of the allocated PGA is used for work areas. Obviouly, something else is
stored in the PGA. The point is that every process that requires some memory to execute a SQL statement or a PL/SQL
program is able to allocate part of the PGA configured through the
pga_aggregate_target
initialization parameter.
And that can be done even though the memory isn't used for a work area. Because the memory manager has no
control over the size of these additional memory structures (also known as
untunable memory
), part of the PGA is
also not under the memory manager's control. As a result, the amount of PGA available for work areas changes over
time, depending on the system load. At any given moment you can see the amount of available memory through the
aggregate PGA auto target
statistics. The following example, which is an excerpt of the output generated by the
pga_auto_target.sql
script, shows how a collection defined through a PL/SQL call can allocate 500MB of PGA and,
as a result, reduce the amount of memory available for work areas:
SQL> SELECT name, value, unit
2 FROM v$pgastat
