|Oracle8i Designing and Tuning for Performance
Release 2 (8.1.6)
Part Number A76992-01
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
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.
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:
This section describes the following:
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:
emptables, then the statement
is not considered identical, because the statement references different tables for each user.
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.
This section describes two techniques of keeping shared SQL and PL/SQL in the shared pool:
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
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.
For more information on using
DBMS_SHARED_POOLpackage may be useful when loading large PL/SQL objects, such as the
DIUTILpackages. 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_POOLis useful for frequently executed triggers. You may want to keep compiled triggers on frequently used tables in the shared pool.
DBMS_SHARED_POOLalso supports sequences. Sequence numbers are lost when a sequence ages out of the shared pool.
DBMS_SHARED_POOLkeeps sequences in the shared pool, thus preventing the loss of sequence numbers.
To use the
DBMS_SHARED_POOL package to pin a SQL or PL/SQL area, complete the following steps.
KEEPto 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.
For specific information on using