Scaling for Database Expansion

When your large database application needs to allocate a large piece of contiguous memory in the shared pool for the batch job or updates, Oracle has to flush all unused objects from the memory pool and merge the resulting free memory chunks. The unavailability of a large piece of memory would result in ORA-04031 errors. This could still happen with Oracle Database 10g if the database cannot allocate enough free memory for the shared pool or large pool as needed. If you have sufficient physical memory on the server and enable automatic memory management, the database can get rid of most of the ORA-04031 errors.

In an ASMM-enabled database, when the database wants to allocate a large object into the shared pool but does not find enough contiguous space, it will automatically increase the shared pool size using free space from other SGA structures up to the maximum available limit. Oracle helps to reduce the DBA intervention for memory monitoring with automatic memory management. The current setting for ASMM can be obtained from the V$SGA_DYNAMIC_COMPONENTS view.

Allocating a large amount of memory for the system and enabling the ASMM will help the DBA to manage the database environment better than with a manually tuned environment. Consider any large retail database, which has OLTP jobs or a large number of concurrent transactions during the day and parallel batch jobs at night. In a manually tuned environment, the DBA would have to allocate large buffer caches for the OLTP jobs and a large pool for the nightly batch jobs.

You can use V$DB_CACHE_ADVICE view to get simulated miss rates for a range of potential buffer cache sizes. This view is populated when the DB_CACHE_ADVICE initialization parameter is set to ON. This is a dynamic parameter, which you can turn on and off dynamically to collect advisory data for a specific workload. When you turn on the advisory, there is a small increase in CPU usage for the sampling operation.

When you enable ASMM, all statistics are captured in the system and different memory advisors are invoked to get the best distribution for memory. MMAN coordinates the memory-resize operations to allocate different components with appropriate sizings as needed. With memory auto-tuning, the buffer cache gets more memory allocation during daily OLTP transactions. At night, the memory automatically shifts to the large pool to accommodate the parallel batch job runs. ASMM uses MMAN as the SGA memory broker to keep track of the component sizes and resize operations.