Oracle Tips by Burleson

10g Grid Computing with RAC
Real Application Cluster Architecture

Multiple Block Sizes in RAC environment

It is recommended that we start all the instances with the same set of non-standard block size buffers (sub-cache) with DB_nK_CACHE_SIZE parameter. Note that Oracle is not enforcing the existence of same set of sub-cache when the instance joins the cluster. It is better to create sub-cache with all possible cache (2K, 4K, 8K, 16K, 32K etc) to avoid run time errors.

Redo Log Buffers

Redo log buffers are used to hold the redo records generated by each data changing transaction. It is a circular buffer. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, and DROP operations.

A redo log buffer is written out to the online redo log by the log writer process when:

• The buffer becomes one third full
   

• Three seconds have elapsed
   

• When a DBWn process writes modified buffers to disk
   

• On commit record - when a user process commits a transaction

While it is acceptable to have different sized redo log buffers and redo logs on each instance in a RAC database, this is not a suggested configuration, as it can lead to confusion and misunderstandings during recovery operations. Each instance in a RAC database must have its own thread of redo logs.

JAVA Pool

The JAVA Pool holds the JAVA execution code in a similar manner to the PL/SQL cache in the shared pool. The JAVA pool is used by many internal routines, such as import and export, and should be sized at approximately 60 megabytes if no other JAVA will be utilized in the user applications.

Shared Pool

The shared pool holds the library cache, dictionary cache, message queues, latch and lock areas, buffers for parallel execution messages, and control structures. The shared pool also contains the RAC lock areas known as the Global Resource Directory. The total size of the shared pool is determined by the initialization parameter SHARED_POOL_SIZE.

The library cache includes the shared SQL areas, private SQL areas (in the case of a shared server configuration), PL/SQL procedures and packages, and control structures such as locks and library cache handles. Shared SQL areas are accessible to all users, so the library cache is contained in the shared pool within the SGA.

The data dictionary is accessed very often by various Oracle processes. Dictionary Cache is the memory areas designated to hold dictionary data. It is also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data).

Large Pool

This area is only used if shared server architecture (multi-threaded server - MTS) is used, or if parallel query is utilized. The large pool holds the user global areas when MTS is used and holds the parallel query execution message queues for parallel query.

In general, large pool provides memory allocations for:

• Session memory for the shared server and the Oracle XA interface (used where transactions interact with more than one database)
   

• I/O server processes
   

• Oracle backup and restore operations

Streams Pool

This is a new area in Oracle Database 10g that is used to provide buffer areas for the streams components of Oracle.  To configure the Streams pool explicitly, you need to specify the size of the pool in bytes using the STREAMS_POOL_SIZE initialization parameter. If the size of the Streams pool is greater than zero, then any SGA memory used by Streams is allocated from the Streams pool. If the size of the Streams Pool is zero or not specified, then the memory used by Streams is allocated from the shared pool and may use up to 10% of the shared pool.

Fixed SGA

A portion of the SGA contains general information about the state of the database and the instance, which the background processes need to access; this is called the Fixed SGA. No user data is stored here. The SGA also includes information communicated between processes, such as locking information.

Automatic Shared Memory Management

Oracle 10g, with its emphasis on self-management and self-tuning, introduces many features that operate automatically, as well as in an advisory role. Since we fix the values for the SGA components at instance start time, we are constrained to use them as they are during the instance runtime (with some exceptions).

Often it happens that a certain component’s memory pool is never used and it is not available for another component, which is in need of extra memory. Under-sizing can lead to poor performance and out-of memory errors (ORA-4031), while over-sizing can waste memory.

With the Database 10g, we can employ the Automatic Shared Memory Management feature. This feature enables the Oracle database to automatically determine the size of each of these memory components within the limits of the total SGA size. This solves the allocation issues that we normally face in a manual method.

This feature enables us to specify a total memory amount to be used for all SGA components. The Oracle Database periodically redistributes memory between the components above according to workload requirements.

Using the sga_target initialization parameter configures automatic Shared Memory Management. If you specify a non-zero value for sga_target, the following four memory pools are automatically sized by Oracle.

• Database Buffer cache (Only the Default pool)
   

• Shared Pool
   

• Large Pool
   

• Java Pool

If you set sga_target to 0, the Automatic Shared Memory Management is disabled. The default value of sga_target is 0. When sga_target is not set, or equal to zero, auto-tuned SGA parameters behave as in previous releases of the Oracle database. In Oracle 10g, the initialization parameters such as db_cache_size, shared_pool_size, large_pool_size, and java_pool_size are referred to as 'auto-tuned sga’ parameters.

Configuration of the following buffers still remains manual and they are now referred to as manually sized components:

• Log Buffer
   

• Other Buffer Caches (KEEP/RECYCLE, other block sizes)
   

• Streams Pool (Newly introduced in Oracle Database 10g)
   

• Fixed SGA and other internal allocations

The user specifies manual SGA parameters, and parameter sizes precisely control the sizes of their corresponding components. When sga_target is set, the total size of manual SGA parameters are subtracted from the sga_target value and the balance is given to the auto-tuned SGA components.

sga_target is also a dynamic parameter and can be changed through Enterprise Manager or with the ALTER SYSTEM command. However, the sga_target can be increased only up to the value of sga_max_size.

You may be wondering how this auto management is possible. A new background process named Memory Manager (MMAN) manages the automatic shared memory. MMAN serves as the SGA Memory Broker and coordinates the sizing of the memory components.

Important: You must set statistics_level to TYPICAL (default) or ALL to use Automatic Shared Memory Management.

Program Global Area (PGA)

A program global area (PGA) is a memory region that stores the data and control information for the server processes.  Each Server process has a non-shared memory created by Oracle when a server process is started.  Access to the PGA is exclusive to that server process and it is read and written only by Oracle code acting on its behalf.  Broadly speaking, PGA contains a private SQL area and a Session memory area.

A private SQL area contains data such as bind information and runtime memory structures.  Each session that issues a SQL statement has a private SQL area.  Note that the location of a private SQL area depends on the type of connection established for a session.  If a session is connected through a dedicated server, private SQL areas are located in the server process’s PGA.  However, if a session is connected through a shared server, part of the private SQL area is kept in the SGA.

Session memory is the memory allocated to hold a session’s variables (logon information) and other information related to the session.  For a shared server, the session memory is shared and not private.

With the initialization parameter “PGA_AGGREGATE_TARGET”, sizing of work areas for all dedicated sessions is made automatic and all *_AREA_SIZE parameters are ignored for these sessions.  At any given time, the total amount of PGA memory available to active work areas on the instance is automatically derived from the parameter PGA_AGGREGATE_TARGET.

RAC Additional SGA Areas

RAC Database System has two important services namely, Global Cache Service (GCS) and Global Enqueue Service (GES).  These are basically a collection of background processes.  These two processes together cover and manage the total Cache Fusion process, resource transfers and resource escalations among the instances.

Global Resource Directory

GES and GCS together maintain a Global Resource Directory (GRD) to record the information about the resources and the enqueues.  The GRD remains in the memory and is stored on all the instances.  Each instance manages a portion of the directory.  This distributed nature is a key point for fault tolerance of the RAC.  The GRD functions as the internal database that records and stores the current status of data blocks.  Whenever a block is transferred out of a local cache to another instance’s cache, the GRD is updated.  The following resource information is available in the GRD.

• Data Block Identifiers (DBA)
   

• Location of most current version 
   

• Modes of the data blocks ( (N)Null, (S)Shared, (X)Exclusive )
   

• The Roles of the data blocks (local or global) held by each instance
   

• Buffer caches on multiple nodes in the cluster

The GRD is akin to the previous version of Lock Directory in the functionality perspective but has been expanded with more components.  The GRD accurately measures and tracks an inventory of resources and their status and location.

Oracle Consulting Services

Burleson Oracle Consulting offers Senior Oracle consultants which are available for all areas of Oracle support.  Common Oracle consulting support activities include short-term Oracle tuning, Oracle database troubleshooting, Oracle9i and Oracle10g migration, Oracle9iAS App Server consulting, Oracle design reviews and Oracle requirements evaluation support. Oracle support and Oracle consulting services are priced by the hour, so you only pay for what you need. These one-time Oracle consulting services commonly include:

• Answering questions from your Oracle DBA technical staff

• Repairing down production Oracle database systems

• One-time Oracle tuning evaluation

• Guru support for your Oracle DBA

• Installation of Oracle application packages

For more information, please visit www.dba-oracle.com or email info@remote-dba.net.