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 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


	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


		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


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


	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


	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


		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


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


	If the reloads-to-pins ratio is greater than 1%, increase the SHARED_POOL_SIZE parameter.


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


		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.


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


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


		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


		Keep the ratio of the sum of GETMISSES to the sum
		of GETS less than 15%:


	If there are too many cache misses, increase the parameter SHARED_POOL_SIZE.


		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


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