Oracle7 Server Tuning

Tuning Private SQL and PL/SQL Areas

In this section you learn how to tune private SQL and PL/SQL areas. Tuning private SQL areas involves identifying unnecessary parse calls made by your application and then reducing them. To reduce parse calls, you may have to increase the number of private SQL areas that your application can have allocated at once. Throughout this section, information about private SQL areas and SQL statements also applies to private PL/SQL areas and PL/SQL blocks.

Identifying Unnecessary Parse Calls

To identify unnecessary parse calls, run your application with the SQL trace facility enabled. For each SQL statement in the trace output, examine the count statistic for the Parse step. This statistic tells you how many times your application makes a parse call for the statement. This statistic includes parse calls that are satisfied by access to the library cache as well as parse calls that result in actually parsing the statement.

Note: This statistic does not include implicit parsing that occurs when an application executes a statement whose shared SQL area is no longer in the library cache. For information on detecting implicit parsing, see the section "Examining Library Cache Activity" .

If the count value for the Parse step is near the count value for the Execute step for a statement, your application may be deliberately making a parse call each time it executes the statement. Try to reduce these parse calls through your application tool.

Reducing Unnecessary Parse Calls

Depending on the Oracle application tool you are using, you may be able to control how frequently your application performs parse calls and allocates and deallocates private SQL areas. Whether your application reuses private SQL areas for multiple SQL statements determines how many parse calls your application performs and how many private SQL areas the application requires.

In general, an application that reuses private SQL areas for multiple SQL statements does not need as many private SQL areas as an application that does not reuse private SQL areas. However, an application that reuses private SQL areas must perform more parse calls because the application must make a new parse call whenever an existing private SQL is reused for a new SQL statement.

Be sure that your application can open enough private SQL areas to accommodate all of your SQL statements. If you allocate more private SQL areas, you may need to increase the limit on the number of cursors permitted for a session. You can increase this limit by increasing the value of the initialization parameter OPEN_CURSORS. The maximum value for this parameter depends on your operating system. The minimum value is 5.

The means by which you control parse calls and allocation and deallocation of private SQL areas varies depending on your Oracle application tool. The following sections introduce the means used for some tools. Note that these means apply only to private SQL areas and not to shared SQL areas.

Reducing Parse Calls with the Oracle Precompilers With the Oracle Precompilers, you control private SQL areas and parse calls with these options:

HOLD_CURSOR

RELEASE_CURSOR

MAXOPENCURSORS

These options can be specified in two ways:

on the precompiler command line

within the precompiler program

With these options, you can employ different strategies for managing private SQL areas during the course of the program.

For information on these options, see the Programmer's Guide to the Oracle Precompilers.

Reducing Parse Calls with the Oracle Call Interfaces (OCIs) With the Oracle Call Interface (OCI), you have complete control over parse calls and private SQL areas with these OCI calls:

OSQL3 OPARSE An OSQL3 or OPARSE call allocates a private SQL area for a SQL statement.
OCLOSE An OCLOSE call closes a cursor and deallocates the private SQL area of its associated statement.

For more information on these calls, see the Programmer's Guide to the Oracle Call Interface.

Reducing Parse Calls with Oracle Forms With Oracle Forms, you also have some control over whether your application reuses private SQL areas. You can exercise this control in three places:

at the trigger level

at the form level

at runtime

For more information on the reuse of private SQL areas by Oracle Forms, see the Oracle Forms Reference manual.

OSQL3 OPARSE	An OSQL3 or OPARSE call allocates a private SQL area for a SQL statement.
OCLOSE	An OCLOSE call closes a cursor and deallocates the private SQL area of its associated statement.