You may recall that when I described the example, I unrealistically assumed away the effects of indexes, rollback segments, temporary tablespace, and referential integrity issues on locking. In reality, PCM locks are required to read and write blocks belonging to indexes, rollback segments, and temporary tablespaces as well. In addition, referential integrity rules cause reads, and in some cases writes (in the case of DELETE CASCADE rules for foreign keys) on other tables or indexes. Reads and writes caused by referential integrity are no different than any other application reads and writes.

If the column(s) in the where clause of the update statement in steps 1, 4, and 7 was indexed, reads on those index blocks would increase the number of PCM locks needed for each of the updates. Furthermore, if the columns changed by the update statement were foreign keys, PCM locks on the relevant table and index blocks of the parent tables would also be required. Either one of these situations would drastically increase the number of PCM locks required and would drastically increase the relative time required for the operation.

To illustrate, consider the following example of an update statement:

update purchase_order_line set po_nbr = 666 where po_nbr = 555 and

po_line_nbr = 2

Assume that there is an index on po_nbr and po_line_nbr. Also assume that po_nbr is a foreign key to the table purchase_order, and purchase_order has an index on po_nbr. As a result of the update statement, PCM locks to consult the index on purchase_order_line to obtain the RID of the row to be updated. Furthermore, both table purchase_order and its index are read to validate the new value for po_nbr. PCM locks are necessary to accomplish this.

Although this example does not use temporary tablespace, PCM locks apply to temp space just like any other kind of space. You can minimize contention by assigning different temporary tablespaces to users of different instances. Because rollback segments can be assigned to one instance, PCM locking problems associated with RBS space can be minimized relatively easily.

This example illustrates why PCM locking considerations deserve the attention we’re giving them in this chapter and why OPS applications do not necessarily scale, even though they have more hardware resources. In fact, you may be wondering why you even should consider using OPS at this point.

Later in the chapter, we will discuss a design technique called application partitioning that can drastically reduce the number of PCM locks acquired, as well as some physical I/O and pinging that are required. We will revisit this example later in the chapter as an illustration of the effects of application partitioning.

Parallel Server Design Practices

Despite the preceding locking example, which might make you apprehensive about using OPS, there are ways to design OPS applications in ways that minimize locking issues. Furthermore, for OLTP applications with high availability requirements and a high cost to downtime, OPS is a necessity. The single most effective design technique for making OPS applications scalable is application partitioning.

Application Partitioning

Application partitioning works by having all users writing to a given set of tables connect to the same instance. To use the preceding example, if clerks and supervisors who were connected issued updates from the same instance, only one PCM lock for block 12345 would be needed. This is because PCM locks are assigned at an instance level, not a user level. When we look at the relative cost of locking, as we did when we walked through the example, we would only have one PCM lock instead of three. In other words, our relative cost for locking would have been 103,003 instead of 303,003, an improvement of approximately 67%.

Effectively partitioning requires more in-depth knowledge of your end users’ requirements than is necessary in a non-OPS world. The first step in partitioning is to estimate the number of select, update, insert, and delete operations per table. An example of this type of research is provided in Table 31.3.

After identifying the most heavily hit tables, do some analysis to determine which types of users are causing the load. Make sure that those users log in through the same instance. An example of this type of analysis is presented in Table 31.4.

Given the information in Table 31.4, we would place all ATM transactions in one instance and all tellers and managers in another. In this way, we minimize PCM locking issues.