 |
|
Oracle Dynamic RAM and UNIX Administration
Oracle UNIX/Linux Tips by Burleson Consulting |
Oracle Dynamic RAM and UNIX
Prior to Oracle9i, the Oracle DBA could only
control UNIX memory for Oracle at database start time. Oracle
provided several init.ora 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 re-starting 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 upon the existing demands on the Oracle database.
Oracle has also introduced a new RAM memory
management technique whereby the DBA can pre-allocate 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% of the RAM memory for UNIX overhead
(Figure 7-1).
Figure 1: Pre-allocation of SGA and PGA memory
in Oracle
Prior to Oracle9i, it was not uncommon for the
Oracle DBA to have several copies of their init.ora parameter file,
and then “bounce” the database daily to re-configure 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 (Figure 7-2).
Figure 2: 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 a Oracle data warehouse. OLTP system 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 & DSS |
30% |
50% |
20% |
Table 1: RAM memory changes depending on type
of application
Starting in Oracle9i, Oracle provided the
ability to grow or shrink the following components of the SGA RAM
memory.
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.
Oracle and UNIX Granules
Starting with Oracle9i, Oracle expanded 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 init.ora parameter, then Oracle is free
to allocate more granules until the SGA size reaches the sga_max_size
limit.
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 (Figure 7-3).
Figure 3: Dynamic memory allocation
NOTE: Early versions of Oracle 9i did not
allow dynamic modification of the large_pool_size parameter and 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 us 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 output below is an example of a change which 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 above example, the error
was generated because the command would have exceeded the value of
sga_max_size parameter. In the example below, we 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 we see how we 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.
 |
If you like Oracle tuning, see the
book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|