13
Managing Shared SQL and 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 exact statements are issued, then the SQL or PL/SQL area used to process the first instance of the statement is shared. This means that it is used for the processing of the subsequent executions of that same statement. Similar statements also share SQL areas when the CURSOR_SHARING parameter is set to FORCE.

See Also:
For more information on similar SQL statements, see Chapter 19, "Tuning Memory Allocation".

Shared SQL and PL/SQL areas are shared memory areas. Any Oracle process can use a shared SQL area. Shared SQL areas reduce 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.

Note:
Shared SQL is not recommended with data warehousing applications. Use literal values in these SQL statements, rather than bind variables. If you use bind variables, then the optimizer makes a blanket assumption about the selectivity of the column. However, if you specify a literal value, then the optimizer can use value histograms and provide a better access plan.

This chapter contains the following sections:

Comparing SQL Statements and PL/SQL Blocks

This section describes the following:

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 need 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, then 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 each have emp tables, then 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 generate the same SQL statements. The chances that they 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 300 users use two identical statements; however, 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 calls the large package back in, then 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.

See Also:
For more information on the SHARED_POOL_RESERVED_SIZE parameter, see "Tuning the Shared Pool" in Chapter 19, "Tuning Memory Allocation".

Preventing Objects from Aging Out

The DBMS_SHARED_POOL package lets you keep objects in shared memory, so that they do not age out with the normal LRU mechanism. 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. This ensures that memory is available, and it prevents the sudden, inexplicable slowdowns in user response time that occur when SQL and PL/SQL areas are accessed after aging out.

See Also:
For more information on using DBMS_SHARED_POOL, see Oracle8i Supplied PL/SQL Packages Reference.

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. This is because of the large number of smaller objects that need to age out of 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 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 keeps sequences in the shared pool, 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 your objects.

This procedure ensures that your system does not run out of shared memory before the objects are loaded. By pinning the objects 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.

See Also:
For specific information on using DBMS_SHARED_POOL procedures, see Oracle8i Supplied PL/SQL Packages Reference.

13Managing Shared SQL and PL/SQL Areas