BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

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.

  • 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.

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.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter