Guidelines for Tuning the Oracle Shared Pool

Perhaps one of the least-understood aspects of Oracle Shared Global Area tuning is tuning the shared pool. The generally accepted tuning methodology involves throwing memory into 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 that uses measurement, not guesswork, to drive the tuning methodologies. Numerous scripts for examining the shared pool are provided.

What Is the shared pool?

Many people know that the shared pool is a part of the Oracle SGA but little else, so to begin this discussion it’s necessary to answer exactly,What is the shared pool? The shared pool contains several key Oracle performance-related memory areas. If the shared pool is improperly sized, then overall database performance will suffer, sometimes dramatically.

The shared pool is separated into many substructures. The substructures of the shared pool fall into two broad areas: the fixed-size areas, which, for a given database at a given point in time stay relatively constant in size, and the variable-size areas, which grow and shrink according to user and program requirements.

The areas inside the library caches’ substructure are variable in size, while those outside the library caches (with the exception of the request and response queues used with MTS) stay relatively fixed in size. The sizes are determined based on an Oracle internal algorithm that ratios out the fixed areas based on overall shared pool size, a few of the initialization parameters, and empirical determinations from previous versions. In early versions of Oracle (notably 6.2 and earlier), the dictionary caches could be sized individually allowing a finer control of this aspect of the shared pool. With Oracle 7, the internal algorithm for sizing the data dictionary caches took control from the DBA.

The major difference between the shared pools in Oracle8i and Oracle9i is that any excess memory specified by the SGA_MAX_CACHE parameter and not used in the actual cache and buffer definitions will be placed in the miscellaneous area of the shared pool.

The shared pool is used 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 shared pool. At a minimum, the shared pool must be sized to accommodate the needs of the fixed areas, plus a small amount of memory reserved for use in parsing SQL and PL/SQL statements. If this is not done, ORA-04031 and ORA-07445 errors will result.



GUIDELINES FOR TUNING SHARED POOL:

I hope you now understand that the old "just increase the shared pool" answer isn't good enough anymore when it comes to tuning problems. You must take an in depth look at your shared pool and tune what needs to be tuned, not just throw memory at a problem until it submerges. Indeed, I have shown that in some cases increasing the size of the shared pool may harm performance and decreasing the size may be advisable. The shared pool is vital to the proper performance of your Oracle database, you must have it properly tuned or drown in bad performance. Next we will cover what to pin, the shared pool and multi-threaded server, hashing and generalized library and dictionary cache tuning. We have also discussed ways to monitor for what objects should be pinned, discussed multi-threaded server, looked at hashing problems and their resolution as well as examined classic library and data dictionary cache tuning. We have established 8 guidelines for tuning the Oracle shared pool:

Guideline 1: If gross usage of the shared pool in a non-ad-hoc environment exceeds 95% (rises to 95% or greater and stays there) establish a shared pool size large enough to hold the fixed size portions, pin reusable packages and procedures. Gradually increase shared pool by 20% increments until usage drops below 90% on the average.

Guideline 2: If the shared pool shows a mixed ad-hoc and reuse environment, establish a shared pool size large enough to hold the fixed size portions, pin reusable packages and establish a comfort level above this required level of pool fill. Establish a routine flush cycle to filter non-reusable code from the pool.

Guideline 3: If the shared pool shows that no reusable SQL is being used establish a shared pool large enough to hold the fixed size portions plus a few megabytes (usually not more than 40) and allow the shared pool modified least recently used (LRU) algorithm to manage the pool. (also see guideline 8)

Guideline 4: Determine usage patterns of packages, procedures, functions and cursors and pin those that are frequently used.

Guideline 5: In Oracle7when using MTS increase the shared pool size to accommodate MTS messaging and queuing as well as UGA requirements. In Oracle8 use the Large Pool to prevent MTS from effecting the shared pool areas.

Guideline 6: Use bind variables, PL/SQL (procedures or functions) and views to reduce the size of large SQL statements to prevent hashing problems.

Guideline 7: In a system where there is no flushing increase the shared pool size in 20% increments to reduce reloads and invalidations and increase object cache hit ratios.

Guideline 8: In any shared pool, if the overall data dictionary cache miss ratio exceeds 1 percent, increase the size of the shared pool.

Using these guidelines and the scripts and techniques covered in this lesson, your should be well on the way towards a well tuned and well performing shared pool.

Initialization Parameters That Effect The Shared Pool

NAME


DESCRIPTION

shared_pool_size


size in bytes of shared pool (7 and 8)

shared_pool_reserved_size


size in bytes of reserved area of shared pool (7 and 8)

shared_pool_reserved_min_alloc


minimum allocation size in bytes for reserved area of shared pool (7 and 8)

large_pool_size


size in bytes of the large allocation pool (8 only)

parallel_max_servers


Maximum number of parallel query slaves, if set forces calculation and setting of large pool size parameter (8i)

parallel_adaptive_multi_user


If set forces calculation of large pool size is over-ridden if size manually set(8i). Oracle DOES NOT recommend setting parallel_adaptive_multi_user.

parallel_automatic_tuning


If set forces calculation of large pool size is over-ridden if size is manually set (8i)

large_pool_min_alloc


minimum allocation size in bytes for the large allocation pool (8 only, obsolete in 8i)

parallel_min_message_pool


minimum size of shared pool memory to reserve for pq servers (8 only, obsolete in 8i)

backup_io_slaves


Number of backup IO slaves to configure (8 only)

temporary_table_locks


Number of temporary table locks to configure (7 and 8)

dml_locks


Number of DML locks to configure (7 and 8)

sequence_cache_entries


Number of sequence numbers to cache (7 and 8)

row_cache_cursors


Number of row caches to set up (7 and 8)

max_enabled_roles


Number of role caches to set up (7 and 8)

mts_dispatchers


Number of MTS dispatcher processes to start with (7 and 8)

mts_max_dispatchers


Maximum number of dispatcher processes to allow (7 and 8)

mts_servers


Number of MTS servers to start with (7 and 8)

mts_max_servers


Maximum number of MTS servers to allow (7 and 8)

open_cursors


Maximum number of open cursors per session (7 and 8)

Cursor_space_for_time


Hold open cursors until process exits (7 and 8)
note:Initialization Parameters Used In Tuning Shared Pool

Views Dealing With Shared Pool and Buffer Tuning


VIEW NAME


PURPOSE

V$PARAMETER


Contains current settings for all documented initialization parameters

V$SGASTAT


Contains sizing information for all SGA areas

V$SQLAREA


Contains information and statistics on the SQL area of the shared pool

V$DB_OBJECT_CACHE


Contains information on all cached objects in the database shared pool area

V$LIBRARYCACHE


Contains statistics on the library caches

V$ROWCACHE


Contains statistics on the data dictionary caches

DBA_USERS


Contains database user information

V$BUFFER_POOL


Oracle8 view showing pool areas

V$BUFFER_POOL_STATISTICS


Oracle8 buffer pool statistics

V$BH


View that monitors every buffer in buffer pool