Database Reference
In-Depth Information
However, the instance is aware of what we have done. It does not ignore the memory it can't control; it simply
recognizes that the memory is being used and backs off the size of memory allocated for work areas accordingly. So if
we rerun the same sort query, we see that this time we sorted to disk—the instance did not give us the 12MB or so of
RAM needed to do this in memory since we had already exceeded the
PGA_AGGREGATE_TARGET
:
EODA@ORA12CR1> set autotrace traceonly statistics;
EODA@ORA12CR1> select * from t order by 1,2,3,4;
72616 rows selected.
Statistics
----------------------------------------------------------
9 recursive calls
8 db block gets
986 consistent gets
2025 physical reads
0 redo size
3665844 bytes sent via SQL*Net to client
53795 bytes received via SQL*Net from client
4843 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
72616 rows processed
EODA@ORA12CR1> set autotrace off
So, because some PGA memory is outside of Oracle's control, it is easy to exceed the
PGA_AGGREGATE_TARGET
simply by allocating lots of really large data structures in our PL/SQL code. I am not recommending you do this by any
means. I'm just pointing out that the
PGA_AGGREGATE_TARGET
is more of a request than a hard limit.
Choosing Between Manual and Auto Memory Management
So, which method should you use, manual or automatic? My strong preference is to use the automatic PGA memory
management by default.
■
I'll repeat this from time to time in this topic: please do not make any changes to a production system—a
live system—without first testing for any side effects. For example, please do not read this chapter, check your system
and find you are using manual memory management—and then just turn on automatic memory management. Query
plans may change, and performance may be impacted. One of three things could happen:
Caution
•
Things run exactly the same.
•
Things run better than they did before.
•
Things run much worse than they did before.
exercise caution before making changes; test the proposed change first.
