Analyzing Oracle SGA Large Pool

Posted: November 4th, 2008 | Author: TnT Admin | Filed under: Analyze | Tags: | No Comments »

Oracle provides the ability to create a optional area in the SGA (System Global Area), called the Large Pool to provide large memory allocations for the following. By allocating session memory from the large pool for shared server, Oracle XA, or parallel query buffers, Oracle can use the shared pool primarily for caching shared SQL and avoid the performance overhead caused by shrinking the shared SQL cache.

Some of the uses of Large Pool are listed below:

  • Session memory for the shared server and the Oracle XA interface
  • I/O server processes (DBW0)
  • Oracle backup and restore operations
  • Parallel execution message buffers, if the initialization parameter PARALLEL_AUTOMATIC_TUNING is set to true (otherwise, these buffers are allocated to the shared pool)

(Source: Oracle Memory Architecture)

The counters that we should be watching are as followed:

  • V$SGASTAT: Large Pool bytes

What we want to see of the counters are:

  • Watch the value for free memory when the results returned. If high or increasing values for free memory, you have over allocated space to the Large Pool. If low or decreasing values for free memory, you may need to consider increasing the Large Pool size.

The following queries will be used to determine the hit-ratio:

  • SELECT name, bytes FROM V$SGASTAT WHERE pool =’large pool’;

Real world scenario

When using the Recovery Manager feature in Oracle, RMAN will buffer its I/O activity in Shared Pool. If database activity is high during the time that the RMAN backup is running, “ORA-04031 unable to allocate x bytes of shared memory” errors can result. This error indicates that a large piece of contiguous memory was requested in the Shared Pool, but could not be obtained. If this request is coming from RMAN, errors in the backup and poor application performance can result.  When the Large Pool is present, RMAN will use the Large Pool to buffer this I/O instead of the Shared Pool. This will result in not only better RMAN performance but also in improved Shared Pool hit-ratios.

(Source: OCP Oracle 9i Performance Tuning Study Guide)

Related Posts

Leave a Reply