Tuning Oracle 10gR2 Databases

This section provides information about tuning Oracle 10g databases.

Automatic Shared Memory Management

Oracle 10g utilizes Automatic Shared Memory Management (ASMM) of individual SGA components like shared pool, java pool, large pool and db cache. You do not need to estimate when setting the size of SGA components. In fact, there is no need to set any parameters defining SGA size.

All you have to do is to set a new parameter called SGA_TARGET. The parameter SGA_TARGET takes a value which indicates the maximum size of SGA required for your instance.

Consider that you set SGA_TARGET to say 800MB. This indicates that maximum size to which SGA can grow is 800MB. All the SGA components like shared pool, buffer cache, large pool, java pool will be allocated from this 800M maximum SGA. Oracle will automatically calculate the initial size of these components and resizes it as per the requirement without any manual intervention.

You do not have to explicitly define values for shared pool, buffer cache, large pool and java pool if you set SGA_TARGET. The SGA_TARGET will be limited by the SGA_MAX_SIZE value. The SGA_MAX_SIZE cannot be modified dynamically. If SGA_MAX_SIZE is not set, both the parameters have the same value and it will be not possible to increase the size of SGA_TARGET dynamically.

Automatic Segment Space Management

The Automatic Segment Space Management (ASSM) feature allows Oracle to use bitmaps to manage the free space within segments. The bitmap describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. The current status of the space available in a data block is reflected in the bitmap allowing Oracle to manage free space automatically with ASSM.

ASSM tablespaces automate freelist management and remove the ability to specify PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for individual tables and indexes created in ASSM tablespaces. The values for parameters PCTUSED and FREELISTS are ignored and Oracle automatically manages the space for these tables and indexes inside the tablespace using bitmaps. PCTFREE can still be specified and is used with ASSM.