Oracle7 Server Application Developer's Guide

Tuning an Application

There are two important areas to think about when tuning your database application:

tuning your SQL statements

tuning your application design

Information on tuning your SQL statements, including how to use the cost-based optimization method, is included in the Oracle7 Server Tuning manual. Tuning your application design ideally occurs before you begin to implement your application. Before beginning your design, you should carefully read about each of the features described in this document and consider which features best suit your requirements. Some design decisions that you should consider are outlined below.

Where possible, enforce business rules with integrity constraints rather than programmatically. See for a discussion of when to use integrity constraints.

To improve performance, use PL/SQL. A description of how PL/SQL improves performance is included in the Oracle7 Server Tuning manual.

Use packages to further improve performance and reduce runtime recompilations. Packages are described in Chapter 7.

Use cached sequence numbers to generate primary key values; see .

Use array processing to reduce the number of calls to Oracle; see the Oracle7 Server Tuning manual.

Use VARCHAR2 to store character data instead of CHAR, which blank-pads data; see .

Store LONG and LONG RAW data in tables separate from related data and use referential integrity to relate them. This allows you to access the related data without having to read the LONG or LONG RAW data; see .

Use the SET_MODULE and SET_ACTION procedures in the DBMS_APPLICATION_INFO package to record the name of the executing module or transaction in the database for use later when tracking the performance of various modules. Registering the application allows system administrators and performance tuning specialists to track performance by module. System administrators can also use this information to track resource usage by module. When an application registers with the database, its name and actions are recorded in the V$SESSION and V$SQLAREA views. Registering applications is described in the Oracle7 Server Tuning manual.

You should also work with your database administrator to determine how the database can be tuned to accommodate your application. More detailed information on tuning your application, as well as information on database tuning, is included in the Oracle7 Server Tuning manual.