Oracle7 Server Tuning

Contents Index Home Previous Next

Shared SQL and PL/SQL

Oracle compares SQL statements and PL/SQL blocks issued directly by users and applications as well as recursive SQL statements issued internally by a DDL statement. If two identical statements are issued, the SQL or PL/SQL area used to process the first instance of the statement is shared, or used for the processing of the subsequent executions of that same statement.

Shared SQL and PL/SQL areas are shared memory areas; any Oracle process can use a shared SQL area. The use of shared SQL areas reduces memory usage on the database server, thereby increasing system throughput.

Shared SQL and PL/SQL areas are aged out of the shared pool by way of a least recently used algorithm (similar to database buffers). To improve performance and prevent reparsing, you may want to prevent large SQL or PL/SQL areas from aging out of the shared pool. The method for doing this is described in "Keeping Shared SQL and PL/SQL in the Shared Pool" [*].

Comparing SQL Statements and PL/SQL Blocks

Oracle automatically notices when two or more applications send identical SQL statements or PL/SQL blocks to the database. Oracle identifies identical statements using the following steps:

Notice that Oracle does not have to parse a statement to determine if it is identical to another statement currently in the shared pool.

Keeping Shared SQL and PL/SQL in the Shared Pool

The DBMS_SHARED_POOL package allows objects to be kept in shared memory, so that they will not be aged out with the normal LRU mechanism. The DBMSPOOL.SQL and PRVTPOOL.SQL procedure scripts create the package specification and package body for DBMS_SHARED_POOL.

By using the DBMS_SHARED_POOL package and by loading these SQL and PL/SQL areas early (before memory fragmentation occurs), the objects can be kept in memory, instead of aging out with the normal LRU mechanism. This procedure ensures that memory is available and prevents sudden, seemingly inexplicable slowdowns in user response time that occur when SQL and PL/SQL areas are accessed after aging out.

When to Use DBMS_SHARED_POOL

The procedures provided with the DBMS_SHARED_POOL package may be useful when loading large PL/SQL objects, such as the STANDARD and DIUTIL packages. When large PL/SQL objects are loaded, users' response time is affected because of the large number of smaller objects that need to be aged out from the shared pool to make room (due to memory fragmentation). In some cases, there may be insufficient memory to load the large objects.

DBMS_SHARED_POOL is also useful for frequently executed triggers. Because triggers are compiled each time they are aged out of the shared pool, you may want to keep compiled triggers on frequently used tables in the shared pool.

Usage Instructions

To use the DBMS_SHARED_POOL package to pin a SQL or PL/SQL area, complete the following steps.

This procedure ensures that the object is already loaded; otherwise, pinning may not be very useful. It also ensures that your system has not run out of the shared memory before the object is loaded. Finally, by pinning the object early in the life of the instance, this procedure prevents the memory fragmentation that could result from pinning a large chunk of memory in the middle of the shared pool.

Syntax

The procedures provided with the DBMS_SHARED_POOL package are described below.

DBMS_SHARED_POOL. SIZES

This procedure shows the objects in the shared pool that are larger than the specified size.

dbms_shared_pool.sizes(minsize IN NUMBER)

Input Parameter:

minsize

Display objects in shared pool larger than this size, where size is measured in kilobytes.

Output Parameters:

To display the results of this procedure, before calling this procedure issue the following command using Server Manager or SQL*Plus:

SET SERVEROUTPUT ON SIZE minsize

You can use the results of this command as arguments to the KEEP or UNKEEP procedures.

Example

To show the objects in the shared pool that are larger than 100 you would issue the following Server Manager or SQL*Plus commands:

SQL> SET SERVEROUTPUT ON SIZE 100
SQL> EXECUTE DBMS_SHARED_POOL.SIZE(100);

DBMS_SHARED_POOL. KEEP

This procedure lets you keep an object in the shared pool. This procedure may not be supported in future releases.

dbms_shared_pool.keep(object IN VARCHAR2,
                      [type IN CHAR DEFAULT P])

Input Parameters:

object

Either the parameter name or the cursor address of the object that you want kept in the shared pool. This value is the value displayed when you call the SIZES procedure.

type

The type of the object that you want kept in the shared pool. The types recognized are listed below:
P the object is a procedure
C the object is a cursor
R the object is a trigger

DBMS_SHARED_POOL. UNKEEP

This procedure allows an object that you have requested to be kept in the shared pool to now be aged out of the shared pool. This procedure may not be supported in the future.

dbms_shared_pool.unkeep(object IN VARCHAR2,
                      [type IN CHAR DEFAULT P])

Input Parameters:

object

Either the parameter name or the cursor address of the object that you no longer want kept in the shared pool. This value is the value displayed when you call the SIZES procedure.

type

The type of the object that you want aged out of the shared pool. The types recognized are listed below:
P the object is a procedure
C the object is a cursor
R the object is a trigger


Contents Index Home Previous Next