 |
|
Oracle Dynamic RAM and
UNIX
Oracle Tips by Burleson
|
Prior to Oracle, the Oracle DBA could only
control UNIX memory for Oracle at database start time. Oracle provided
several Oracle parameters to determine the RAM size of the SGA, and
once the database was started, the SGA size and configuration could
not be modified.
The movement of
Oracle toward a 24x7 database has created the need for the Oracle DBA
to adjust the size of the UNIX memory regions without stopping and
restarting the database. More important, the dynamic SGA features of
Oracle allow the DBA to monitor RAM memory usage within the SGA and
adjust the SGA memory regions based on the existing demands on the
Oracle database.
Oracle has
also introduced a new RAM memory management technique whereby the DBA
can preallocate all PGA memory and allow Oracle to distribute the RAM
memory to connections according to the sorting demands of the
connections. This is a radical departure from traditional Oracle
databases, and it has made the sort_area_size and other PGA
parameters obsolete. Also, it is no longer necessary to issue alter
session commands to change the sort_area_size for
connections that require a large sort area.
Rather than
allocate just the SGA, the Oracle DBA must fully allocate all of the
RAM memory on the UNIX Oracle server, reserving 20 percent of the RAM
memory for UNIX overhead (see Figure 9-18).
Figure 9-72: Preallocation of SGA and PGA memory
in Oracle
Prior to
Oracle, it was not uncommon for the Oracle DBA to have several
copies of their Oracle parameter file, and then “bounce” the database
daily to reconfigure the SGA for different processing modes. For
example, the allocations of an SGA for online transaction processing (OLTP)
is quite different than the processing mode for an Oracle data
warehouse (see Figure 9-19).
Figure 9-73: Comparison of RAM for DSS and OLTP
applications
Oracle recommends a different RAM memory
configuration for OLTP databases and decision support applications (DSS)
such as an Oracle data warehouse (Table 1). OLTP systems should
allocate the majority of total UNIX RAM to the SGA, while data
warehouse and DSS applications that are RAM memory intensive should
allocate the majority of RAM for PGA connections.
|
Processing Mode |
SGA RAM |
Total PGA RAM |
UNIX RAM Overhead |
|
OLTP |
65% |
15% |
20% |
|
Data warehouse and DSS |
30% |
50% |
20% |
Table 9-4: RAM Memory Changes Depending on Type
of Application
Of course, if a single database operates in a
dual modality, the Oracle DBA can run a script to dynamically change
this memory allocation. For example, if a database runs in OLTP mode
during the day and DSS mode at night, the DBA can run a script to
steal RAM from the SGA and reallocate this RAM memory to the PGA
region:
alter
system set pga_aggregate_target = 3g;
Starting in Oracle, Oracle has provided the
ability to grow or shrink the following components of the SGA RAM
memory:
-
Data buffer
size alter system set db_cache_size=300m;
-
Shared pool
size alter system set shared_pool_size=200m;
-
Total PGA RAM
memory size alter system set pga_aggregate_target=2000m;
In UNIX, Oracle achieves the dynamic memory
allocation by modifying the physical address space inside the UNIX
memory region. This is done in UNIX by issuing malloc() and
free() commands.
The new dynamic SGA features also allow the
Oracle SGA to start small and grow on an as-needed basis. A new
parameter called sga_max_size has been created to facilitate
this process.
On many platforms the sga_max_size is totally
allocated on startup with the excess going into the miscellaneous
section of the shared pool. In order to reallocate this memory, it
must be deallocated from the shared pool and then reallocated.
Oracle and UNIX Granules
Starting with Oracle, Oracle expands the term
granule to include a reserved region of RAM memory for SGA
growth. A memory granule should not be confused with Oracle parallel
query block range partition granules. Block range partition granules
are used by OPQ to determine the block ranges for parallel query
slaves, while a RAM memory granule is a unit of contiguous virtual
memory allocation. If the current amount of SGA memory is less than
the value of the sga_max_size Oracle parameter, Oracle is free
to allocate more granules until the SGA size reaches the
sga_max_size limit.
In Oracle,
the DBA “reserves” granules for use by the dynamic SGA feature. When
the DBA issues an alter system command to increase the size of
a RAM memory region of the SGA, Oracle passes the command to a
background process that allocates the RAM memory from the reserved
space, adding the memory to the desired SGA component (see Figure
9-20).
Figure 9-74: Dynamic memory allocation
Note: At this time, Oracle does not support
dynamic modification of the large_pool_size parameter or the
sga_max_size parameter.
The v$process view can be used to display the
existing values for the new Oracle parameters:
column name
format 999,999,999,999
select
name,
value
from
v$parameter
where
name in
(
'sga_max_size',
'shared_pool_size',
'db_cache_size',
'large_pool_size',
‘pga_aggregate_target’
)
;
NAME VALUE
-------------------- -------------
shared_pool_size 40,362,826
sga_max_size 5,392,635,193
large_pool_size 1,048,576
db_cache_size 4,194,304
pga_aggregate_target 2,403,628,363
This query gives you the current values of the
dynamic memory parameters and allows the DBA to compare existing
demands with current instance settings.
Changing Dynamic SGA and PGA
Components
As we have noted, Oracle provides alter
system commands to allow the DBA to change the configuration of
the Oracle RAM memory. Before we look at using these features for
automatic tuning, let’s briefly review the main parameters and see how
they operate. The following output is an example of a change that has
been rejected by Oracle because there is insufficient UNIX memory to
expand the selected pool:
SQL> alter
system set shared_pool_size=64m;
alter system set shared_pool_size=64m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is
invalid
ORA-04033: Insufficient memory to grow pool
In the case of the preceding example, the error was
generated because the command would have exceeded the value of the
sga_max_size parameter. In the example that follows, you expand
the shared pool to 300 megabytes, and we can immediately confirm the
change with the show parameters command:
SQL>
connect system/manager as sysdba;
Connected.
SQL> alter system set shared_pool_size=300m;
System altered.
SQL> show parameter shared_pool
NAME TYPE VALUE
------------------------------------ ----------- -----------
shared_pool_reserved_size big integer 1258291
shared_pool_size big integer 33554432
Now that you see how you can alter the
individual SGA components, let’s move on and take a look at how
Oracle manages dynamic RAM memory in a UNIX environment.
This is an excerpt from "Oracle
High Performance tuning with STATSPACK" by Oracle Press.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts. |
|
