Database Reference
In-Depth Information
So, the entire goal of automatic PGA memory management is to maximize the use of RAM while at the same time
not using more RAM than you want. Under manual memory management, this was a virtually impossible goal to
achieve. If you set SORT_AREA_SIZE to 10MB, when one user was performing a sort operation that user would use up
to 10MB for the sort work area. If 100 users were doing the same, they would use up to 1,000MB of memory. If you had
500MB of free memory, the single user performing a sort by himself could have used much more memory, and the
100 users should have used much less. That is what automatic PGA memory management was designed to do. Under
a light workload, memory usage could be maximized as the load increases on the system, and as more users perform
sort or hash operations, the amount of memory allocated to them would decrease—to reach the goal of using all
available RAM, but not attempting to use more than physically exists.
Determining How the Memory Is Allocated
Questions that come up frequently are “How is this memory allocated?” and “What will be the amount of RAM used
by my session?” These are hard questions to answer for the simple reason that the algorithms for serving out memory
under the automatic scheme are not documented and can and will change from release to release. When using things
that begin with “A”—for automatic—you lose a degree of control, as the underlying algorithms decide what to do and
how to control things.
We can make some observations based on information from MOS notes 147806.1 and 223730.1:
The
PGA_AGGREGATE_TARGET is a goal of an upper limit. It is not a value that is preallocated
when the database is started up . You can observe this by setting the PGA_AGGREGATE_TARGET
to a value much higher than the amount of physical memory you have available on your
server. You will not see any large allocation of memory as a result (one caveat, if you've set
MEMORY_TARGET , and then set PGA_AGGREGATE_TARGET to a value larger than MEMORY_TARGET , on
instance startup Oracle throws an ORA-00838 error and won't let you start your instance).
The amount of PGA memory available for a given session is derived from the setting of
PGA_AGGREGATE_TARGET . The algorithm for determining the maximum size used by a process
varies by database version. The amount of PGA memory a process is allocated is typically a
function of the amount of memory available and the number of processes competing for space.
As the workload on your instance goes up (more concurrent queries, concurrent users), the
amount of PGA memory allocated to your work areas will go down. The database will try to
keep the sum of all PGA allocations under the threshold set by PGA_AGGREGATE_TARGET . This
is analogous to having a DBA sit at a console all day, setting the SORT_AREA_SIZE and HASH_
AREA_SIZE parameters based on the amount of work being performed in the database. We will
directly observe this behavior shortly in a test.
OK, so how can we observe the different work area sizes being allocated to our session? By applying the same
technique we used earlier in the manual PGA memory management section to observe the memory used by our
session and the amount of I/O to temp we performed. I performed the following test on an Oracle Linux machine with
four CPUs using Oracle 12.1.0.1 and dedicated server connections. We begin by creating a table to hold the metrics
we'd like to monitor (the following code is placed in a file named stats.sql ):
create table sess_stats
as
select name, value, 0 active
from
(
select a.name, b.value
from v$statname a, v$sesstat b
 
Search WWH ::




Custom Search