Oracle7 Server Tuning
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:
- Your applications can pass multiple SQL statements to Oracle at once.
- Your Oracle Forms applications can perform procedural operations without calling Oracle.
Stored procedures further improve performance by eliminating parsing and automatically taking advantage of shared PL/SQL areas.
Packages further improve performance in these ways:
- Storing related procedures and functions together in a package reduces the amount of I/O necessary to read them into memory.
- Storing a procedure in a package allows you to redefine the procedure without causing Oracle to recompile other procedures that call it.
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: