Oracle7 Server Tuning

Contents Index Home Previous Next

Client/Server Considerations

Client/server architecture distributes the work of a system between the client (application) machine and the server (in this case an Oracle Server). Typically, client machines are workstations that execute a graphical user interface (GUI) application that connects to a larger server machine that houses the Oracle Server.

Because the user typically interacts more heavily with the application, response time is maximized by executing the client on the user's local workstation. In this environment, you should keep communication with the server to a minimum because requests to the server must travel over a network. Too much network traffic can significantly decrease a client application's performance.

Minimize Network Traffic

The Oracle Server provides several ways to minimize the communication between the client application and the database server. Generally, your goal is to group several related server requests (SQL statements) into one request over the network. The following Oracle Server features can reduce network traffic by grouping related statements, or performing several client functions with one network call:

Refer to Oracle7 Server Concepts for more information about these features and how they can reduce network traffic. When possible, use these features when designing your client/server applications. Most of the features above use PL/SQL, Oracle's procedural extension to SQL. The next section describes how PL/SQL can minimize your network traffic.

PL/SQL

Anonymous blocks and stored procedures improve performance by reducing calls from your application to Oracle. Reducing calls is especially helpful in networked environments where calls may incur a large overhead. PL/SQL reduces calls in these ways:

Stored procedures further improve performance by eliminating parsing and automatically taking advantage of shared PL/SQL areas.

Packages further improve performance in these ways:

The PL/SQL Engine in the Oracle Server

Anonymous blocks and stored procedures allow applications to pass multiple SQL statements to Oracle at once. A block containing multiple SQL statements can be passed to Oracle in a single call. Without PL/SQL, applications must pass SQL statements one at a time, each with a separate call to Oracle. Figure 2 - 8 illustrates how PL/SQL reduces network traffic and boosts performance.

Figure 2 - 8. PL/SQL Boosts Performance

The PL/SQL Engine in Oracle Application Tools

Anonymous blocks allow Oracle Forms applications to perform procedural functions that might otherwise require calls to Oracle. Since a PL/SQL block can contain procedural statements, your application can use the PL/SQL engine incorporated into the Oracle application tool to execute such statements.

For example, you should perform data calculations in your Oracle Forms applications by passing procedural statements to the PL/SQL engine, rather than by issuing SQL statements. SQL statements require calls to Oracle, while the procedural statements can be processed by the PL/SQL engine in Oracle Forms itself. In this case, PL/SQL helps you avoid calling Oracle altogether.

Using PL/SQL in Your Applications

You should use PL/SQL for parts of your applications that perform a great deal of database access. Follow these guidelines for using PL/SQL with specific Oracle application tools to improve the performance of your applications:

Oracle Precompilers For database-intensive embedded SQL programs, pass multiple SQL statements to Oracle by issuing anonymous blocks or calling stored procedures rather than by issuing multiple embedded SQL statements.
SQL*Plus For small procedural tasks, issue anonymous blocks or call stored procedures from SQL*Plus rather than using Oracle Reports or Oracle Precompiler programs.
Oracle Forms Replace multiple trigger steps in Oracle Forms applications with single trigger steps containing anonymous blocks.


Contents Index Home Previous Next