Previous | Table of Contents | Next

Page 691

If you can implement a physical table partitioning approach, you will probably be able to apply a hash PCM locking scheme. You will not need to use multiple freelist groups in this case because each partition is truly a separate table and should be covered by locks only needed by its assigned instance. The initial storage and all subsequent operations on a row should be performed by the same instance so you can use a single lock on the datafiles containing the partitions for the instance. As with department or LOB partitioning, however, you might need more locks if there are occasional requirements for rows to be used by another instance.

If you cannot find a way to manually partition your tables that are needed on multiple instances, and none of the other partitioning options work for you either, you may need to attempt transaction partitioning. This is the most complex partitioning scheme, as the next section describes.

Transaction Partitioning

Transaction partitioning requires very complex applications, probably used in conjunction with a TP monitor. The approach requires that each transaction is routed to the best instance to process it, based on the nature of the transaction and the tables involved. If all the tables are covered by locks assigned to one instance, the coding is fairly simple, but when a transaction involves tables that are assigned to locks on different instances, the decision tree can be very complex. This requirement makes the approach a difficult and expensive option.

One benefit to transaction partitioning is that there is no need to make changes to the table structure should you need to add more instances to support growth. With the other partitioning schemes, there is typically a need to repartition either the tables or the users when a new instance is added. With transaction partitioning, you modify the code, not the database. While this may sound like an onerous task, the time required to move large amounts of data around can be sufficient to exceed service level agreements for availability. On the other hand, with transaction partitioning, code changes can be made in anticipation of a new instance in a test environment and can be implemented with a minimum of downtime.

Indexes and Scalability Considerations

While determining the best partitioning option for your database tables, you also need to plan for any indexes you might need. To reduce disk contention, it is generally recommended that you place your indexes in separate tablespaces from their parent tables, employing different disks for their datafiles. Accomplishing this in a Parallel Server environment, with multiple tablespaces already employed to enable table partitioning, can make for an administrative headache. It might be beneficial, therefore, to place a table and its indexes in the same tablespace in some cases.

Another problem you need to anticipate with index processing in multi-instance databases is associated with the use of linearly increasing primary keys. This is typically a numeric key that is provided through an Oracle sequence generator or some other mechanism that uses an increasing integer value. If two or more instances insert records into a table using such a primary key mechanism, they will also need to insert entries into the same index leaf block, causing continual pinging of that block. To avoid this problem, you have two strategies available. First, you can modify the value by reversing its characters and consequently causing the entries to no longer

Page 692

behave as sequential. Or you can create instance specific ranges of values by modifying the value with a multiplier based on the instance number. You can use a formula such as the following for this purpose:

instance_number * 10000000 + sequence_number

Either of these options require changes in the statements that you code in order to access rows using the index. The same conversion operation you apply to the data when you add the row has to be applied when you try to retrieve it.

A final option is to use the reverse key index feature of Oracle8 that reverses the bit pattern in each column of your index. The benefit to this approach is that you can still use the original value from the column in the predicate of a SQL statement because the optimizer knows the structure of the index. Create a reverse key index using the keyword REVERSE as shown in the following example:

SQL> CREATE INDEX busy_table_ix
 2   ON busy_table (id) REVERSE;

Index created.
CAUTION
Although reverse key indexes are offered as an option to avoid block pinging when using a sequential primary key, they have limited utility for such a function. The primary use of a reverse key index is to reduce the number of times an index needs to be rebuilt. They do this by distributing data around the leaf blocks to avoid a concentration of active records at the high end of the index. Such concentrations, along with deletions of lower values in a sequentially ordered index, make an index inefficient, requiring rebuilds.
Oracle's algorithm to avoid data concentration is not designed to distribute index entries for parallel instances. There is no guarantee that two consecutive entries from one instance will be stored in the same block, or range of blocks, as each other. Similarly, two entries from two different instances may well be stored in the same block using reverse key indexing. Consequently, a reverse key index will not necessarily avoid pinging of index leaf blocks.

Sequence Generators and Multiple Instances

If you decide to use sequence generators in a Parallel Server database, you need to be aware of a couple of limitations. One of the ways sequence generators can speed up processing is through their ability to keep a few numbers cached in the SGA of an instance. This enables most transactions that need a sequence number to find it already available in memory rather than having to perform a disk read. The cache also avoids the need to lock a resource while the next number is generated and saved.

The ORDER option for sequence generators ensures that the numbers are assigned in sequential order. This means that the first process to request a number is given the lowest unused number. You may use this feature in a Parallel Server database, but Oracle is only able to provide this capability by assigning each number from the data dictionary on disk. It cannot use number caching because the instances do not have the ability to transfer cached numbers

Previous | Table of Contents | Next