Memory Structures - Expert Oracle Database Architecture - page 128

■ It should be noted that in Oracle9 i , when using a shared server connection, you only can use manual pGa
memory management. This restriction was lifted with Oracle 10 g release 1 (and above). Since that release, you can
use either automatic or manual pGa memory management with shared server connections. If you're using Oracle 10 g
release 1 (and above) then you should be using automatic pGa memory management.

Note

PGA memory management modes are controlled by the database initialization parameter WORKAREA_SIZE_POLICY
and may be altered at the session level. This initialization parameter defaults to AUTO , which sets automatic PGA
memory management when possible in Oracle9 i Release 2 and above. In Oracle9 i Release 1, the default setting
was MANUAL .
In the sections that follow, we'll take a look at each approach.

Manual PGA Memory Management
In manual PGA memory management, the following are the parameters that have the largest impact on the size of
your PGA, outside of the memory allocated by your session for PL/SQL tables and other variables:

SORT_AREA_SIZE : The total amount of RAM that will be used to sort information before
swapping out to disk (using disk space in the temporary tablespace the user is assigned to).

•

SORT_AREA_RETAINED_SIZE : The amount of memory that will be used to hold sorted data after
the sort is complete. That is, if SORT_AREA_SIZE is 512KB and SORT_AREA_RETAINED_SIZE is
256KB, your server process would use up to 512KB of memory to sort data during the initial
processing of the query. When the sort is complete, the sorting area would “shrink” down
to 256KB, and any sorted data that does not fit in that 256KB would be written out to the
temporary tablespace.

•

HASH_AREA_SIZE : The amount of memory your server process can use to store hash tables
in memory. These structures are used during a hash join, typically when joining a large set
with another set. The smaller of the two sets would be hashed into memory and anything that
didn't fit in the hash area region of memory would be stored in the temporary tablespace by
the join key.

•

These parameters control the amount of space Oracle will use to sort or hash data in memory before using the
temporary tablespace on disk, and how much of that memory segment will be retained after the sort is done.
The SORT_AREA_SIZE-SORT_AREA_RETAINED_SIZE calculated value is generally allocated out of your PGA, and the
SORT_AREA_RETAINED_SIZE value will be in your UGA. You can discover your current use of PGA and UGA memory
and monitor its size by querying special Oracle V$ views, also referred to as dynamic performance views .
For example, let's run a small test whereby in one session we'll sort lots of data and, from a second session, we'll
monitor the UGA/PGA memory use in that first session. To do this in a predictable manner, we'll make a copy of the
ALL_OBJECTS table, with about 72,000 rows in this case, without any indexes (so we know a sort has to happen when
we use ORDER BY on this table):

EODA@ORA12CR1> create table t as select * from all_objects;
Table created.

EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.