ASMM Features

Oracle 10g Oracle10g Automated Memory Management

Oracle Database 10g now provides for automated management of SGA Memory Areas, relieving the DBA from having to worry over database block buffers, shared pools, and other internal memory structures.

In addition, there are a number of other new automation features in Oracle Database 10g, let's take a quick look at them.

Oracle Database 10g Automation Features

Oracle Database 10g provides a wealth of features that can be used to automate almost every aspect of Oracle Database 10g database administration. It is important to note that these automation features are OPTIONAL, and they are not intended to replace standard DBA activities. Rather, the Oracle Database 10g automation features are aimed at shops that do not have the manpower or expertise to manually perform the tasks.

The automation features of Oracle Database 10g appear to offer two products bundled into a single package. The 10g customer can have the flexible (and complex) Oracle Database 10g database, or they can use the automated memory, storage, and SQL features for a simple (alBCt sub-optimal) database.

So, if your 10g database does not require detailed, expert tuning, then the automated features might be a good choice. The automated features are targeted at these market segments:

• Small shops – Small installations that cannot afford a trained Oracle DBA.
   
• Shops with over-worked DBAs - Large shops with hundreds of instances where the DBA does not have time to properly tune each system

Remember, the automated features are not for every shop, and a human DBA will almost always perform better than the automated Oracle features.

There are some duplicate acronyms that may be problematic because it is not clear which component is being used. The duplicate Oracle Database 10g acronyms include:

SGA – System Global Area
SGA – Server Generated Alerts
ASM – Automated Segment Management (bitmap freelists)
ASM – Automatic Storage Management

Whenever possible we have tried to avoid the use of conflicting acronyms and we have avoided the temptation to use too many acronyms. For example, it is perfectly valid in Oracle Database 10g to state “The AWR gives data to AMT for the US to re-analyze statistics”.

How to disable AMM:  See these important notes on disabling AMM (Automatic Space Memory Management)

Automatic Shared Memory Management (ASMM)

Oracle Database 10g now has Automatic Memory Management (ASMM) in the form of the one-size-fits-all parameter called sga_target, which replaces many individual parameters and automates the allocation of RAM between the data buffers, shared pool, and log buffers.

The tuning of these SGA memory regions used to be complex and time consuming, until ASMM automated the tuning. Using predictive models derived from v$db_cache_advice and v$shared_pool_advice, Oracle automatically monitors changing demands on the SGA regions and re-allocates RAM memory based on the existing workload.

Let’s take a close look at the ASMM simplification. When using ASMM, we only have three instance parameters:

sga_max_size -- This parameter sets the hard limit up to which sga_target can dynamically adjust sizes. Usually, sga_max_size and sga_target will be the same value, but there may be times when you want to have the capability to adjust for peak loads. By setting this parameter higher than sga_target, you allow dynamic adjustment of the sga_target parameter.

sga_target -- This parameter is new in Oracle Database 10g and reflects the total size of memory footprint a SGA can consume. It includes in its boundaries the fixed SGA and other internal allocations, the (redo) log buffers, the shared pool, Java pool, streams pool, buffer cache, keep/recycle caches, and if they are specified, the non-standard block size caches.

pga_aggregate_target -- This parameter defines the RAM area reserved for system-wide sorting and hash joins.

If you like Oracle tuning, you might enjoy my 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.