修改时间:2010-11-4类型:HOWTO状态:PUBLISHED优先级:3    


In this Document
  Goal
  Solution

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 and later   [Release: 11.1 and later ]
Information in this document applies to any platform.

Goal

Sometimes there is no alert log available or the ORA-4031 is not reported in the alert log. The incident trace file is available but it does not include subpool information.

This note is intended to help in diagnosing these cases.

Solution

The incident trace file in 11g reports the parameter values including their current values achieved by ASMM/AMM (double underscore parameters) at the time of error.

Example 1
------------
ORA-04031: unable to allocate 2048024 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")

The trace file of above error contains:

sga_max_size=1610612736
sga_target=1610612736
__sga_target=1610612736
__shared_pool_size=369098752
__large_pool_size=989855744
__db_cache_size=218103808

The problem here is the low free memory in the large pool.

As we see in trace file the large pool has grown up to nearly 1GB dynamically (SGA_TARGET is 1.6GB).

The suggestion would be to increase the SGA_TARGET value. This will require an outage for the database.

Example 2
------------
ORA-04031 error on shared pool.

The trace file contains:

__shared_pool_size=2483027968
shared_pool_size=2147483648
__sga_target=4898947072
memory_target=10737418240
__db_cache_size=2147483648
db_cache_size=2147483648
cursor_sharing=SIMILAR
__pga_aggregate_target=9059696640

Determine the sum of auto tuned components of SGA and PGA_AGGREGATE_TARGET.   

Auto-tuned values for Shared Pool and Buffer Cache add up to 4,630,511,616 (just under SGA_TARGET). 

MEMORY_TARGET is used here so PGA and SGA are auo-tuned by the database. MEMORY_TARGET minus SGA_TARGET leaves 5,838,471,168.  The auto-tuner had the PGA_AGGREGATE_TARGET set up to 9,059,969,640 at some point.  That would be a problem trying to get that much PGA memory on top of the almost 5G SGA_TARGET.

MEMORY_TARGET needs to be increased to accommodate the workload needs for SGA and PGA.