Zurück

How to combine Automatic Memory Management
(AMM) with manually sized SGA

Martin Zahn, Akadia AG
Information Technology, CH-3672 Oberdiessbach

15.09.2007

Overview

If you have one or more very wide tables with more than 255 columns you may encounter massive row chaining on this table. All other tables are small, so the standard blocksize is 4K or 8K, but for this extra table it can be advantageous to have a blocksize of 16K or even 32K to avoid the row chaining.

Oracle Database 10 automates the management of shared memory (AMM) used by an instance and liberates administrators from having to manually configure the sizes of shared memory components.

So the question is - how can we combine AMM with a manually sized SGA.

Oracle SGA

The Oracle system global area (SGA) is just shared memory structures that are created at instance startup, hold information about the instance and control its behavior.

Simply stated, the system global area (SGA) is just shared memory structures that are created at instance startup, hold information about the instance and control its behavior. The following figure gives a brief synopsis of the particular components of the SGA. Note, that the blue components are managed by AMM, whereas the red component is manually managed.

What can be seen from this figure, is that there are many options available to setup the SGA and without a complete understanding of what the applications are doing in the background, the ability to guess the appropriate amount of memory to give each of these individual components is not always optimal. AMM can solve this complex configuration part - but gives the possibility to size an extra, manually sized buffer cache for objects within the extra tablespace. In our example, we used 16K, but Oracle offers additional blocksizes of 2K, 4k, 8k, 16k and 32K.

Components of the SGA

SGA Component

Size controlled by

Areas of Influence

Description

Shared
Pool

SHARED_POOL_SIZE

Library Cache

- Shared SQL areas
- Private SQL areas
- PL/SQL procedures
   and packages
- Various control
   structures

Oracle needs to allocate & deallocate memory as SQL or procedural code is executed based on the individual needs of users' sessions and in accordance to the LRU algorithm.

Dictionary Cache

- Row cache
- Library cache

Highly accessed memory structures that provide information on object structures to SQL statements being parsed.

Java
Pool

JAVA_POOL_SIZE

- Run state
- Methods
- Classes
- Session code
- Data in JVM

Memory available for the Java memory manager to use for all things Java.

Streams
Pool

STREAMS_POOL_SIZE

- Stream activity

New to Oracle 10g, memory available for stream processing.

Redo Log Buffer

LOG_BUFFER

- Redo entries

Holds changes made to data and allows for the reconstruction of data in the case of failure.

Database
Buffer
Cache

DB_2K_CACHE_SIZE DB_4K_CACHE_SIZE DB_8K_CACHE_SIZE DB_16K_CACHE_SIZE DB_32K_CACHE_SIZE DB_KEEP_CACHE_SIZE DB_RECYCLE_CACHE_SIZE

- Write list
- LRU list

Holds copies of data requested by SQL and reduces requests to disk by having data in memory. You may have many different buffer caches that help segregate on usage patterns.

Large
Pool

LARGE_POOL_SIZE

- Shared server
- Oracle XA
- I/O server
   processes
- Backup & restore

For large memory allocations.

You can look at the size of your SGA by looking at the initialization parameters that control its size. Here is a simple query and its output.

Automatically managed SGA

There is really nothing to switching into automatic shared memory tuning. You only need to set the SGA_TARGET parameter in the INIT.ORA.

# Using Automatic SGA Management

sga_target        = 300M
sga_max_size      = 400M
shared_pool_size  = 0
java_pool_size    = 0
large_pool_size   = 0

Multiple Block Sizes

Oracle supports multiple block sizes as follows.

DB_BLOCK_SIZE It has a standard block size, as set by the DB_BLOCK_SIZE initialization parameter. The standard block size is used for the SYSTEM tablespace and most other tablespaces.

The most commonly used block size should be picked as the standard block size. In many cases, this is the only block size that you need to specify. Typically, DB_BLOCK_SIZE is set to either 4K or 8K. If not specified, the default data block size is operating system specific, and is generally adequate.

The block size cannot be changed after database creation, except by re-creating the database. If a database's block size is different from the operating system block size, make the database block size a multiple of the operating system's block size. 

For example, if your operating system's block size is 2K (2048 bytes), the following setting for the DB_BLOCK_SIZE initialization parameter is valid:

DB_BLOCK_SIZE = 4096
Non Standard
Block Sizes

Additionally up to 4 nonstandard block sizes. Nonstandard block sizes are specified when creating tablespaces. Multiple block size support allows for the transporting of tablespaces with unlike block sizes between databases.

Tablespaces of non-standard block sizes can be created using the CREATE TABLESPACE statement and specifying the BLOCKSIZE clause. These non-standard block sizes can have any power-of-two value between 2K and 32K: specifically, 2K, 4K, 8K, 16K or 32K. Platform-specific restrictions regarding the maximum block size apply, so some of these sizes may not be allowed on some platforms.

To use non-standard block sizes, you must configure sub-caches within the buffer cache area of the SGA memory for all of the non-standard block sizes that you intend to use

Configure sub-caches for non-standard block sizes

If you intend to use multiple block sizes in your database, you must have the DB_CACHE_SIZE and at least one DB_nK_CACHE_SIZE parameter set. Oracle assigns an appropriate default value to the DB_CACHE_SIZE parameter, but the DB_nK_CACHE_SIZE parameters default to 0, and no additional block size caches are configured. 

DB_CACHE_SIZE Initialization Parameter

The DB_CACHE_SIZE initialization parameter replaces the DB_BLOCK_BUFFERS initialization parameter that was used in previous releases. The DB_CACHE_SIZE parameter specifies the size of the cache of standard block size buffers, where the standard block size is specified by DB_BLOCK_SIZE. However, this have only be done if you don't use AMM.

DB_nK_CACHE_SIZE Initialization Parameters

The sizes and numbers of non-standard block size buffers are specified by the following initialization parameters: 

DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE

Each parameter specifies the size of the buffer cache for the corresponding block size.

INIT.ORA example

DB_BLOCK_SIZE    = 4096
DB_CACHE_SIZE    = 12M
DB_2K_CACHE_SIZE = 8M
DB_8K_CACHE_SIZE = 4M

In the above example, the parameters specify that the standard block size of the database will be 4K. The size of the cache of standard block size buffers will be 12M. Additionally, 2K and 8K caches will be configured with sizes of 8M and 4M respectively.

Note:

These parameters cannot be used to size the cache for the standard block size. For example, if the value of DB_BLOCK_SIZE is 2K, it is illegal to set DB_2K_CACHE_SIZE. The size of the cache for the standard block size is always determined from the value of DB_CACHE_SIZE.

Example

Database 10.2.0.3, Standard Blocksize with 4K, automatically managed SGA (AMM) but with extra tablespace with 16K Blocksize for very wide tables.

Setup INIT.ORA

### Basic Configuration Parameters
### ------------------------------

compatible                        = 10.2.0
db_block_size                     = 4096

### Database Buffer Cache, I/O
### --------------------------
# The Parameter SGA_TARGET enables Automatic Shared Memory Management

# Using Automatic SGA Management

sga_target                        = 300M
sga_max_size                      = 400M
shared_pool_size                  = 0
java_pool_size                    = 0
large_pool_size                   = 0
db_16k_cache_size                 = 50M

Creating the tablespace with nonstandard block sizes

CREATE TABLESPACE wide_tab
  DATAFILE '/u01/oracle/db/AKI1/tab/wide_tab.dbf' SIZE 50M REUSE
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
  SEGMENT SPACE MANAGEMENT AUTO
  BLOCKSIZE 16K
  PERMANENT
  ONLINE;

Check Sizes in Oracle Enterprise Manager

You can see, the manually configured 16K buffer cache and all other automatically sized components.