Notes

Outline

Tuning the Shared Pool

Objectives After completing this lesson, you should be able to do the following: Tune the library cache and the data dictionary cache Measure the shared pool hit ratio Size the shared pool appropriately Pin objects in the shared pool Tune the shared pool reserved space Describe the User Global Area (UGA) and session memory considerations Configure the large pool

The Shared Global Area

The Shared Pool Size defined by SHARED_POOL_SIZE Library cache contains statement text, parsed code, and execution plan Data dictionary cache contains table, column definitions, and privileges from the data dictionary tables UGA contains MTS users’ session information

The Library Cache Used to store SQL statements and PL/SQL blocks to be shared by users Managed by an LRU algorithm Used to prevent statements reparsing

The Library Cache

Tuning the Library Cache Reduce misses by keeping parsing to a minimum: Make sure that users can share statements Prevent statements from being aged out by allocating enough space Avoid invalidations that induce reparsing

Tuning the Library Cache Avoid fragmentation by: Reserving space for large memory requirements Pinning frequently required large objects Eliminating large anonymous PL/SQL blocks Reducing UGA consumption of MTS connections

Terminology GETS: The number of lookups for objects of the namespace PINS: The number of reads or executions of the objects of the namespace RELOADS: The number of library cache misses on the execution step, causing implicit reparsing of the statement and block

Diagnostic Tools for Tuning the Library Cache V$LIBRARYCACHE

Are Cursors Being Shared? Find out which statements users are running:

Guidelines: Library Cache Reloads Reloads should: Ideally be 0 Never be more than 1% of the pins

Guidelines: Library Cache Reloads If the reloads-to-pins ratio is greater than 1%, increase the SHARED_POOL_SIZE parameter.

Invalidations This column represents the number of times objects of the namespace were marked invalid, causing reloads.

Sizing the Library Cache Define the global space necessary for stored objects (packages, views, and so on). Define the amount of memory used by the usual SQL statements. Reserve space for large memory requirements, to avoid misses and fragmentation. Keep frequently used objects. Convert large anonymous PL blocks into small anonymous blocks that call packaged functions.

Global Space Allocation SQL statements:

Large Memory Requirements Satisfy requests for large contiguous memory Reserve unfragmentable memory within the shared pool

Tuning the Shared Pool Reserved Space Diagnostic tools for tuning: The V$SHARED_POOL _RESERVED dictionary view The supplied package and procedure: DBMS_SHARED_POOL ABORTED_REQUEST_THRESHOLD Guidelines: Set the SHARED_POOL_RESERVED_SIZE parameter

Keeping Large Objects Pin large packages in the library cache:

Anonymous PL/SQL Blocks

Other Parameters Affecting the Library Cache OPEN_CURSORS CURSOR_SPACE_FOR_TIME SESSION_CACHED_CURSORS

The Data Dictionary Cache, Terminology, and Tuning Content: Definitions of dictionary objects Terminology: GETS: Number of requests on objects GETMISSES: Number of requests resulting in cache misses Tuning: Avoid dictionary cache misses

Diagnostic Tools for Tuning the Data Dictionary Cache

Tuning Data Dictionary Cache Keep the ratio of the sum of GETMISSES to the sum of GETS less than 15%:

Guidelines: Dictionary Cache Misses If there are too many cache misses, increase the parameter SHARED_POOL_SIZE.

UGA and MTS

Sizing the User Global Area

Large Pool Can be configured as a separate memory area in the SGA, used for memory with: I/O server processes: DBWR_IO_SLAVES Oracle backup and restore operations Session memory for the multithreaded servers Parallel query messaging Is useful in these situations to avoid performance overhead caused by shrinking the shared SQL cache Is sized by the LARGE_POOL_SIZE parameter

Summary In this lesson, you should have learned about: The shared pool, which is made up of: The shared SQL and PL/SQL areas (library cache) The data dictionary cache or row cache The User Global Area, if connections are multithreaded server connections, unless the large pool is configured Tuning the library cache Configuring the large pool