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
PoolSHARED_POOL_SIZE
Library Cache
- Shared SQL areas
- Private SQL areas
- PL/SQL procedures
and packages
- Various control
structuresOracle 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 cacheHighly accessed memory structures that provide information on object structures to SQL statements being parsed.
Java
PoolJAVA_POOL_SIZE
- Run state
- Methods
- Classes
- Session code
- Data in JVMMemory available for the Java memory manager to use for all things Java.
Streams
PoolSTREAMS_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
CacheDB_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 listHolds 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
PoolLARGE_POOL_SIZE
- Shared server
- Oracle XA
- I/O server
processes
- Backup & restoreFor 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 SizesAdditionally 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_SIZEEach 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 = 4MIn 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.
