In the past few years, client/server design has gone from miraculous cure-all to administrator's nightmare to its rightful place as one of several mature alternatives available to system architects. During that time, two enduring questions have arisen again and again:
· "How should I best divide the processing between the client and the server?"
· "How can I ensure that an application that is adequate for the workgroup will be satisfactory for the enterprise?"
These two questions are intertwined since one of the biggest influences on the scalability of a system is the partitioning of its processing. In this paper we will attempt to provide a little background about what these questions mean for the designer, and then take a look at some of the innovations in Developer/2000 Release 2.0 that address these issues.

Application Partitioning

Application partitioning determines the amount of processing performed on the client; the amount of processing performed on the server; and, perhaps most significantly, the amount of network traffic between the two. Network capacity is often the most scarce resource. Although PC processing power is not unlimited or free, PCs are dramatically more powerful than they were a decade ago, and continue to double in power every 18 months. Servers can be configured in parallel and cluster configurations of bewildering power such that only the very largest, most demanding organizations in the world are concerned that there might not be a powerful enough server configuration for their needs. And, as with PCs, Unix and NT servers have gone from 16 bit to 32 bit to 64 bit hardware - an exponential growth in capability - in a breathtakingly short span of time.

In that same decade, LANs have gone from 10 Mbps to ... well, in most organizations, to 10 Mbps. We are perhaps now on the threshold of a widespread move to 100 Mbps LANs internally, coupled with a growing demand for applications that can satisfy the Internet-based consumer connected at 28.8 Kbps or less. At the same time, the competition for bandwidth has never been more intense: sound, images, video-conferencing,, net telephony and other bandwidth "hogs" are making a scarce resource even scarcer. We know that the next two generations of Intel chip are already in progress; but nobody can say where the next big leap in bandwidth might come from.

There is no simple answer to the question of application partitioning, because the right answer depends entirely on what you mean by "best". By putting more processing on the server you may be trading off greater efficiency against less interactivity. For example, by passing all validation to the server you can minimize the amount of work required of the client, at the cost of an often tedious wait for the network round trip. (Anybody who has ever used an HTML form-based application on the World Wide Web can attest to just how tedious a network round trip can be!)

For these reasons, we usually suggest that the most important goal for application partitioning is to minimize the amount of network traffic: both the number of round trips performed, to eliminate the latency; and the volume of traffic, to reduce contention. In this respect it is worth noting that there really only two kinds of application: the system is either "fast enough" or "too slow" (in the particular case of a system that replaces a legacy system, this becomes "fast enough" or "slower than the old system").

Scalability Example
One of the greatest difficulties with partitioning and scalability is that a large system can exhibit completely opposite behavior to a small system. Consider a simple example of a screen that presents a list of employee records, which the user can choose to sort by name, employee number, department or so on. (Picture a Windows 95 file folder, if you are familiar with that system). Where would it be best to sort the records? For a small number of records - say, 100 - it would probably be best to fetch all the records to the client and sort them there. Then, each time the user wants to see the records in a different order, they can be re-sorted on the client. They do not need to be fetched across the network again.

For a large number of records however, it would be unwise to fetch say, 10,000 records if the user only needs to see the first ten before deciding that they prefer to see the records in a different order. This would produce both unnecessary network traffic as well as an unacceptable delay. Far better to sort the records on the server and transfer only the first ten. Of course, each time the user wants to see the records in a different order, we will need to re-sort on the server and send over another ten records. But the user would have to try an awful lot of different sorts before the network traffic matched fetching all 10,000 records!

Somewhere in between there is a number of records where it is hard to say which is better; the amount of traffic will depend on just how often the users like to play around with the sequence of the records. And of course, you really won't know the answer to this until the application goes into production.

The moral of this example is that for many questions of scalability the best partitioning is very hard to determine by pure reasoning and deduction. This means that it is important to have a tool that allows you to easily adjust your partitioning and scalability decisions.

Developer/2000 and Scalability

One of the strengths of Developer/2000 has always been features that help to address scalability and ease of partitioning. We will describe some of the enhancements in Developer/2000 Release 2.0 which directly reduce network traffic and others which increase the developer's available choices about partitioning. In the tradition of Developer/2000, most of the features we will discuss require little or no coding; and because they are implemented by setting declarative properties, they can easily be integrated into existing applications. We will begin with a couple of features for tuning the performance of existing blocks.

Reducing Server Resources

Developer/2000 has long allowed the developer to specify the array size for fetching records into a data block. This allowed the developer to balance the immediate response of fetching a small number of records with the network efficiency of fetching large numbers of records. In Release 2.0 we have added a new property to data blocks which allows you to specify that you want to Query All Records. Forms then fetches all of the records in the block rather than as needed. The tradeoff here is that the increase in initial delay and possibly unnecessary network traffic allows the server to free up resources that it was using, for example to maintain the state of the cursor and cache fetched rows.

Reducing Network Traffic

In Release 2.0 we have extended this capability to perform Array Fetches to Inserts, Updates, and Deletes. Previously, Developer/2000 would take one network round trip for each such DML statement. Now, it will automatically bundle up an array size of statements into a single network round trip. This "Array DML" gives an immediate and often dramatic reduction in network traffic.
Array DML is implemented in an application simply by setting the "DML Array Size" property for the data block. This makes it very easy to adjust the array size to balance network traffic against memory usage on the PC. The vast majority of applications will be able to benefit from this enhancement.

Data Blocks Based on Stored Procedures

The most significant enhancement we have made for partitioning and scalability is to provide declarative support for basing a data block on a stored procedure. Stored procedures are the most direct way of moving processing to the server. When correctly designed, they can also eliminate many network round trips.

For example, by basing a query on a stored procedure the foreign key lookups and calculations can be performed on the server rather than in Post-Query triggers. Such triggers typically add at least one round trip per row, so losing the benefit of array fetches.

Similarly, by performing updates through a stored procedure, audit trails or denormalized data can be written without an additional network round trip; so can validations that might be necessary before attempting to perform the DML. This eliminates network round trips that previously might have occurred in Pre-Update, Pre-Insert, and Pre-Delete triggers.

Stored Procedures for Query

While it has been possible in the past to manually build a data block on a stored procedure by writing transactional triggers, such as On-Select and On-Fetch, this technique sacrificed the benefits of array fetches. The new declarative approach in Release 2.0 is not only substantially easier to build, it also allows Forms to perform array fetches through stored procedures.
There are two options for queries through stored procedures in Release 2.0. The first option is to base a data block's query on a stored procedure that returns a Ref Cursor; the other is a stored procedure that returns a Table of Records.

Query Based on Ref Cursor

A Ref Cursor is a PL/SQL release 2.2 construct that allows the stored procedure to open a cursor, and return to the client a "pointer", or reference, to the cursor. The client can then fetch records from the cursor just as if the client had opened the cursor itself. Records are fetched through the Ref Cursor using array fetches exactly as if Forms had opened the cursor itself for a data block based directly on a table or view.

A data block based on a Ref Cursor has many similarities to a data block based on a view, but there are two major advantages to a Ref Cursor. First, a stored procedure provides better encapsulation of the data. By denying direct query access to the tables, you can ensure that applications query the data only in ways that are meaningful (for example, a set of tables might be designed to be joined in a specific way to produce a particular set of information) or only in ways that are efficient (for example, queried in such a way that the best indexes can be used).

The second advantage is that the stored procedure can be more flexible. The procedure can determine at runtime which one of several Select statements to execute in opening the cursor. This decision might depend on the role or authority of the user, for example: a manager might see all of the columns in the Emp table, but a clerk would be shown blanks for the salary. Or it might depend on a parameter so that a different set of data - historical versus current, for instance - can be displayed in a single data block. This decision can be as complex as you wish, providing you can write it in PL/SQL. The only limitations are that all of the different Select statements must return a compatible set of columns; and the Select statement cannot be composed dynamically at run time. (The database doesn’t yet support Ref Cursors with dynamic SQL).

Query Based on Table of Records

Introduced with PL/SQL release 2.3, a Table of Records is an in-memory structure similar to a database table. The stored procedure can build an in-memory table consisting of, quite literally, any data at all you can construct, row by row. Whereas a Ref Cursor allows you to return anything that you know how to construct in SQL, such as a tree walk, a Table of Records allows you to return anything that you know how to construct in PL/SQL, such as a tree walk with a join. Not only can you perform lookups and calculations on the server side, you can also make complex decisions about which records to include or exclude from the returned record set. One example of something relatively easy to do in PL/SQL and very hard to do in SQL would be to return the Employees in each department whose salary is in the top 5 salaries for their department. (What makes this hard in SQL is that several people could have the equal fifth high salary. In PL/SQL, it's a relatively simple loop.)

When called in response to a Forms query, the stored procedure builds the Table of Records on the server side. It then returns the whole result set to the client at once, using as few physical network round trips as the network packet size allows. Each record in the Table becomes a row in the Forms block. This frees up server resources and uses the network bandwidth very efficiently, at the cost of client resources and potentially wasting network traffic for unneeded records. So although a Table of Records allows the procedure the greatest flexibility in determining the result set, it should be used with care.

Stored Procedures for DML

In Release 2, you can also use a Table of Records returned to a stored procedure to perform inserts, updates and deletes from a block. The stored procedure can then "fan out" your changes to as many tables as necessary, potentially saving many network round trips if your data model is highly normalized. Writing audit trails is another possible use.

This technique requires that you provide a procedure for each of Insert, Update and Delete. As with a block based on a regular table, Forms automatically maintains the state of each record to determine if it is an inserted, updated or deleted record. At commit time, Forms constructs a Table of Records for all of the inserted records, another for the updated records and another for deleted records. It then calls each of the procedures, passing it the relevant Table of Records. As with a query, the Table of Records is passed in a "single shot", using as few network packets as physically possible. In contrast with the situation for a Query, there is no wasted traffic in sending the whole Table of Records, since all the records have to be sent to the server to be committed anyway.

Finally, it is worth noting that you might combine these other techniques in any way, for example you might choose to query through a Ref Cursor while performing DML through a Table of Records, giving you the best of both worlds.

Summary

Partitioning and reduction of network traffic are crucial factors in the scalability of a client/server application. Developer/2000 release 2.0 enables the developer to exploit a wide range of techniques for distributing processing and minimizing network traffic. The use of declarative properties rather than code makes it easy to use and easy to tune these sophisticated capabilities in pursuit of the best partitioning scheme at any scale, from workgroup to enterprise.

Partitioning and Scalability
October 1996
Author: Carl Zetie
Reviewer: Vicki Shimizu
Copyright © Oracle Corporation 1996
All Rights Reserved Printed in the U.S.A.

This document is provided for informational purposes
only and the information herein is subject to change
without notice. Please report any errors herein to
Oracle Corporation. Oracle Corporation does not
provide any warranties covering and specifically
disclaims any liability in connection with this document.

Oracle is a registered trademark and Oracle7, PL/SQL and
Developer/2000 are trademarks of Oracle Corporation.


Oracle Corporation
World Headquarters
500 Oracle Parkway
Redwood Shores, CA 94065
U.S.A.

Worldwide Inquiries:
415.506.7000
Fax 415.506.7200

Copyright © Oracle Corporation 1996
All Rights Reserved