| Previous | Table of Contents | Next |
OLTP applications with large numbers of users present design considerations other than that of OPS. This section will describe some of these considerations and provide recommendations on OLTP design.
With every type of application, designers must decide which processing should occur on the server and which should occur on the client. In recent years, we have seen the development of multitier architectures in which application processing occurs on a server separate from the server that supports the database. In OLTP applications that support high transaction volumes with large numbers of servers, our main application design goal is to offload work from the server. Depending on your application architecture, this may or may not be the same server that’s supporting application batch processing. The reason that offloading work from the database server is paramount for high-transaction OLTP applications is that database server capacity is more likely to be the source of performance bottlenecks than any other portion of the system.
Large OLTP applications have special development considerations. In this section, we will detail those considerations and present a number of development tips. We will also discuss application security for OLTP applications.
This section will detail a series of development tips for large OLTP applications.
Tip:
Avoid using PL/SQL, including triggers, stored procedures, functions, and packages, for large OLTP applications.
Even though Oracle8 makes several performance improvements in PL/SQL, it is still too slow for large numbers of transactions. Given that in large OLTP applications you want to conserve your CPU cycles, avoid PL/SQL as much as possible with this type of application. This means that you will not want to use database triggers either. Have this work performed by the application(s) directly. PL/SQL has very legitimate uses in many types of applications, but not for large OLTP applications.
Tip:
Avoid using external procedures for large OLTP applications.
When Oracle8 came out, I was excited about trying out external procedures as a possible performance improvement over PL/SQL. It turns out that even though compiled C code is more efficient than PL/SQL, the overhead involved in calling external procedures often makes them slower to execute than PL/SQL. Furthermore, a process (called extproc) is run for each user calling an external procedure, which effectively means that they aren’t usable for OLTP applications with large numbers of users. Please refer to Chapter 15, “Object-Oriented Extensions in Oracle8,” for a detailed discussion about external procedures.
Tip:
Avoid using column functions for large OLTP applications.
Although column functions are programmatically convenient, they are resource intensive. Instead of using column functions such as substr, do it in the application. I’ve experienced a 50% performance improvement by doing an equivalent of substr in C, as opposed to a column function. There are exceptions, of course. The following functions are often necessary for datatype conversions or selects with GROUP BY clauses: count, sum, min, max, to_date, and to_char. It is often more efficient to group results (use count, sum, min, max in a SELECT statement with a GROUP BY clause) with SQL than within the application.
Tip:
Qualify searches by rowid whenever possible.
Frequently in OLTP applications, developers “select for update” to place a row lock on a row to ensure that it doesn’t change between selected transactions. In the select, also pick up rowid and use it in the update statement. By using rowid, you bypass the index and do at most one physical I/O to update or delete the row.
Here’s an example update statement that uses the rowid effectively:
Update account set balance=:new_balance where rowid = :host_rowid
Tip:
Use host variables instead of literals in WHERE clauses whenever possible.
When SQL statements are submitted to Oracle, they are parsed (access path is determined) and placed into the shared pool. If like SQL statements are submitted, there is no need to reparse the statement. Using literals instead of host variables makes SQL statements that would otherwise be identical look different. Thus, by using literals, Oracle is forced to parse more SQL statements than it otherwise would.
For example, Listing 31.1 contains C code with two like update statements. These update statements would otherwise be identical except for the literals in the set and where clauses. These literals will cause Oracle to parse these update statements twice.
Listing 31.1. Badly written C code.
char sqlstmt[1024];
EXEC SQL connect scott/tiger;
if SQLCODE == 0) {
EXEC SQL
Update account set balance=10000 where cust_nbr = ‘111-11-1111’;
EXEC SQL
Update account set balance=20000 where cust_nbr = ‘222-22-2222’;
EXEC SQL commit;
}
…….
Listing 31.2 contains C code that’s functionally identical to Listing 31.1, but that illustrates host variable usage. In Listing 31.2, Oracle will only have to parse the statement once. Although the example illustrates the savings of one reparse, potential savings from host variable usage is substantially greater.
Listing 31.2. Example C code illustrating host variable usage.
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR h_cust_nbr[12];
Int h_balance;
EXEC SQL END DECLARE SECTION;
EXEC SQL connect scott/tiger;
if SQLCODE == 0) {
h_balance = 10000;
h_cust_nbr.len = 11;
sprintf(h_cust_nbr.arr, “111-11-1111”);
EXEC SQL
Update account set balance=:h_balance where cust_nbr =
:h_cust_nbr;
h_balance = 20000;
sprintf(h_cust_nbr.arr, “222-22-2222”);
EXEC SQL
Update account set balance=:h_balance where cust_nbr =
:h_cust_nbr;
EXEC SQL commit;
}
…….
| Previous | Table of Contents | Next |