10
Managing SQL and Shared PL/SQL Areas

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 age out of the shared pool according to a "least recently used" (LRU) 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.

This chapter explains the use of shared SQL to improve performance. Topics in this chapter include:

Comparing SQL Statements and PL/SQL Blocks

This section describes

Testing for Identical SQL Statements

Oracle automatically notices when two or more applications send identical SQL statements or PL/SQL blocks to the database. It does not have to parse a statement to determine whether it is identical to another statement currently in the shared pool. Oracle distinguishes identical statements using the following steps:

The text string of an issued statement is hashed. If the hash value is the same as a hash value for an existing SQL statement in the shared pool, Oracle proceeds to Step 2.
The text string of the issued statement, including case, blanks, and comments, is compared to all existing SQL statements that were identified in Step 1.
The objects referenced in the issued statement are compared to the referenced objects of all existing statements identified in Step 2. For example, if two users have EMP tables, the statement
```
SELECT * FROM emp;
    

         
```
is not considered identical because the statement references different tables for each user.

The bind types of bind variables used in a SQL statement must match.

Note:
Most Oracle products convert the SQL before passing statements to the database. Characters are uniformly changed to upper case, white space is compressed, and bind variables are renamed so that a consistent set of SQL statements is produced.

Aspects of Standardized SQL Formatting

It is neither necessary nor useful to have every user of an application attempt to write SQL statements in a standardized way. It is unlikely that 300 people writing ad hoc dynamic statements in standardized SQL will generate the same SQL statements. The chances that they will all want to look at exactly the same columns in exactly the same tables in exactly the same order is remote. By contrast, 300 people running the same application--executing command files--will generate the same SQL statements.

Within an application there is a very minimal advantage to having 2 statements almost the same, and 300 users using them; there is a major advantage to having one statement used by 600 users.

Keeping Shared SQL and PL/SQL in the Shared Pool

This section describes two techniques of keeping shared SQL and PL/SQL in the shared pool:

Reserving Space for Large Allocations

A problem can occur if users fill the shared pool, and then a large package ages out. If someone should then call the large package back in, a significant amount of maintenance is required to create space for it in the shared pool. You can avoid this problem by reserving space for large allocations with the SHARED_POOL_RESERVED_SIZE initialization parameter. This parameter sets aside room in the shared pool for allocations larger than the value specified by the SHARED_POOL_RESERVED_SIZE_MIN_ALLOC parameter.

Note:
Although Oracle uses segmented codes to reduce the need for large areas of contiguous memory, performance may improve if you pin large objects in memory.

Preventing Objects from Aging Out

The DBMS_SHARED_POOL package lets you keep objects in shared memory, so they do not age out with the normal LRU mechanism. The DBMSPOOL.SQL and PRVTPOOL.PLB procedure scripts create the package specification and package body for DBMS_SHARED_POOL.

By using the DBMS_SHARED_POOL package and by loading the SQL and PL/SQL areas before memory fragmentation occurs, the objects can be kept in memory; they do not age out with the normal LRU mechanism. This procedure ensures that memory is available and prevents sudden, 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, user response time is affected because of the large number of smaller objects that need to age out of the shared pool to make room. This is 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. You may want to keep compiled triggers on frequently used tables in the shared pool.

DBMS_SHARED_POOL also supports sequences. Sequence numbers are lost when a sequence ages out of the shared pool. DBMS_SHARED_POOL is useful for keeping sequences in the shared pool and thus preventing the loss of sequence numbers.

How to Use DBMS_SHARED_POOL

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

Decide which packages or cursors to pin in memory.
Start up the database.
Make the call to DBMS_SHARED_POOL.KEEP to pin it.

This procedure ensures that your system does not run out of the shared memory before the object is loaded. Finally, by pinning the object early in the life of the instance, you prevent memory fragmentation that could result from pinning a large portion of memory in the middle of the shared pool.

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.

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

   SET SERVEROUTPUT ON SIZE minsize

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

For example, to show the objects in the shared pool that are larger than 2000 kilobytes, issue the following SQL*Plus commands:

   SET SERVEROUTPUT ON SIZE 2000
   EXECUTE DBMS_SHARED_POOL.SIZES(2000);

DBMS_SHARED_POOL.KEEP

This procedure lets you keep an object in the shared pool.

   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 to be kept in the shared pool. This is the value displayed when you call the SIZES procedure.

type

The type of the object to be kept in the shared pool. Types include:

P

package

C

cursor

R

trigger

Q

sequence

When you pin ADT types, the type body as well as the specification is pinned in shared memory so the LRU mechanism does not age them out. You must have execute privilege on a type to be able to pin it in shared memory.

For example, an attempt to keep a type 'TY' whose body does not exist results in only keeping the type spec TY. Once you create the type body for TY, you must keep the type again to also pin the type body for 'TY' in shared memory.

The following example demonstrates how user 'user2' keeps type 'TY' in user1's schema pinned in shared memory. This example assumes user2 has execute privilege on user1.TY:

   BEGIN 
    SYS.DBMS_SHARED_POOL.KEEP('user1.TY', 'T'); 
   END;

Note:
The flag can be either 'T' or 't'.

DBMS_SHARED_POOL.UNKEEP

This procedure allows an object that you have pinned in the shared pool to age out of the shared pool.

   DBMS_SHARED_POOL.UNKEEP(OBJECT IN VARCHAR2,
   [TYPE IN CHAR DEFAULT P])