Advanced Tuning ofthe Shared Pool

Advanced Tuning ofthe Shared Pool

Perhaps one of theleast-understood aspects of Oracle Shared Global Area tuning is tuning theshared pool. The generally accepted tuning methodology involves throwing memoryinto the pool until either the problem goes under or the problem is masked.Here we will examine the shared pool and define a method for tuning it thatuses measurement, not guesswork, to drive the tuning methodologies. Numerousscripts for examining the shared pool are provided.

What Is the sharedpool?

Many people knowthat the shared pool is a part of the Oracle SGA but little else, so to beginthis discussion it’s necessary to answer exactly,What is the shared pool? Theshared pool contains several key Oracle performance-related memory areas. Ifthe shared pool is improperly sized, then overall database performance willsuffer, sometimes dramatically. Figure 13.1 diagrams the shared pool structurelocated inside Oracle 8i and 9i SGAs.

As you can seefrom the structures pictured in Figure 13.1, the shared pool is separated intomany substructures. The substructures of the shared pool fall into two broadareas: the fixed-size areas, which, for a given database at a given point intime stay relatively constant in size, and the variable-size areas, which growand shrink according to user and program requirements.

In Figure 13.1,the areas inside the library caches’ substructure are variable in size, whilethose outside the library caches (with the exception of the request andresponse queues used with MTS) stay relatively fixed in size. The sizes aredetermined based on an Oracle internal algorithm that ratios out the fixedareas based on overall shared pool size, a few of the initializationparameters, and empirical determinations from previous versions. In earlyversions of Oracle (notably 6.2 and earlier), the dictionary caches could besized individually allowing a finer control of this aspect of the shared pool.With Oracle 7, the internal algorithm for sizing the data dictionary cachestook control from the DBA.

The majordifference between the shared pools in Oracle8i and Oracle9i is that any excessmemory specified by the SGA_MAX_CACHE parameter and not used in the actualcache and buffer definitions will be placed in the miscellaneous area of theshared pool.

The shared pool isused for objects that can be shared among all users, such as table definitions,reusable SQL (although nonreusable SQL is also stored there), PL/SQL packages,procedures, and functions. Cursor information is also stored in the sharedpool. At a minimum, the shared pool must be sized to accommodate the needs ofthe fixed areas, plus a small amount of memory reserved for use in parsing SQLand PL/SQL statements. If this is not done, ORA-04031 and ORA-07445 errors willresult.