Oracle8i Parallel Server Administration, Deployment, and Performance
Release 2 (8.1.6)

Part Number A76970-01





Go to previous page Go to next page

Application Analysis and Partitioning

This chapter explains application design optimization techniques for Oracle Parallel Server. It includes the following sections:

Overview of Development Techniques

Application deployment for Oracle Parallel Server requires that you consider special topics and use particular techniques beyond those you would use to develop applications in single-instance environments. This chapter explains these issues and provides a high-level development methodology for deploying parallel server-based applications to optimize Oracle Parallel Server's features.

A poorly written application that provides sub-optimal performance on a single instance will likely run even worse in an Oracle Parallel Server environment. Before moving an application to Oracle Parallel Server from a single instance, tune it as much as possible. Note that even a well-tuned application may run worse on Parallel Server. This is usually due to excessive pinging.


Use the information in the discussions of how to deploy Oracle Parallel Server applications in this section as well as discussions for single-instance application development as described in the Oracle8i Application Developers' book set.  

Before You Begin, Determine Your Application's Suitability

Before developing Oracle Parallel Server applications, determine whether your system is suitable for multi-instance environments. In general, if you can easily partition your data to avoid inter-instance contention, the more likely Oracle Parallel Server is a suitable solution for you.

The optimal type of application to deploy on Oracle Parallel Server is one that only uses read-only tables, such as Decision Support Systems (DSS). These applications are "perfectly partitionable", thus, inter-instance contention is minimal.

The majority of OLTP applications, however, can be partitioned to varying degrees. Because of the introduction of Cache Fusion, you may experience significant performance gains despite being unable to partition your data to create strict data-to-instance affinities. However, partitioned applications are more scalable than non-partitioned applications.


Simple, updatable star schemas and non-partitionable applications can experience performance problems when deployed with Oracle Parallel Server.  

How Detailed Must Your Analysis Be?

To use Oracle Parallel Server to improve overall database throughput, conduct a detailed analysis of your database design and application's workload. This ensures that you fully exploit the additional processing power provided by the additional nodes for application processing. Even if you are using Oracle Parallel Server only for high availability, careful analysis enables you to more accurately predict your system resource requirements.

A primary characteristic of high performance Oracle Parallel Server systems is that they minimize the computing resources used for Parallel Cache Management. This means they minimize the number of instance lock operations. In addition, the machine-to-machine high speed interconnect traffic should remain within, or preferably well below, the design limitations of the cluster.

Application Transactions and Table Access Patterns

Before beginning your analysis, you must understand how Oracle Parallel Server processes various transactions within tables based on transaction types such as:

Read-Only Tables

With tables that are predominantly read-only, all Oracle Parallel Server nodes quickly initialize the PCM locks to shared mode and very little lock activity occurs. Ideally, each read-only table and its associated index structures should require only one PCM lock. This is why read-only tables offer better performance and scalability with Oracle Parallel Server.

Also consider putting read-only tables into read-only tablespaces by using the SQL statement ALTER TABLESPACE READ ONLY. This has several advantages:

Scalability of parallel execution in Oracle Parallel Server is subject to the interconnect speed between the nodes. You may also need to use higher degrees of parallelism just to keep the processors busy. It is not unusual to run a degree of parallelism equal to three times the number of nodes or processors.

See Also:

Oracle8i Data Warehousing Guide for information about setting the degree of parallelism.  

Random SELECT and UPDATE Tables

Random SELECT and UPDATE tables have transactions that may read and then update any rows in your tables. This type of access requires multiple lock conversions. First, the instance executing the transaction must obtain a shared PCM lock on one or more data blocks. This lock request may cause lock downgrade operations on another node. The instance executing the transaction must finally obtain an exclusive mode PCM lock when the UPDATE is actually performed.

If user transactions on different nodes modify the same range of data blocks concurrently and frequently, there can be a noticeable response time performance penalty. In some cases you can reduce contention by controlling the lock granularity or the access frequency.

In large tables, however, hardware and practical limitations may mean that the number of fixed PCM locks you can effectively use is limited. In these cases, releasable locks may be a good alternative.

See Also:

"Implementing High or Low Granularity Locking" in the Case Study.  


Transactions on random INSERT, UPDATE and DELETE tables require reading a number of data blocks and then modifying some or all of the data blocks read. This process for each of the data blocks specified again requires converting the PCM lock to shared mode and then converting it to exclusive mode upon block modification. This process has the same performance issues as random SELECT and UPDATE tables mentioned in the previous section.

Performance issues for randomly modified tables may arise when indexes need to be updated or maintained. This is especially true when inserts are performed or when Oracle searches for free space and then allocates it to a table or index.

For INSERT, DELETE, and UPDATE transactions that modify indexed keys, you need to maintain the table's indexes. This process requires access to multiple index blocks such as root, branch, and leaf blocks. Thus, the number of potential lock conversions increases. The branch or leaf blocks of an index may split, requiring multiple locks to be held simultaneously. This increases the probability of conflicts across instances. The dispersion of key values in an index can be very important. With monotonically increasing index keys, a hot spot may be created in the right edge of the index key.

If the INSERT and DELETE operations are subject to long-running transactions, then there is a greater chance that another instance will require read consistency information to complete its transactions. This type of concurrence is handled efficiently by Cache Fusion for reads.

Index block contention can be problematic when using a sequence number generator to generate unique keys for a table from multiple Oracle Parallel Server nodes. When generating unique keys, make the instance number part of the primary key so each instance performs INSERTs into a different part of the index. Spreading the INSERT load over the full width of the index can improve both single and multiple instance performance. Do this using reverse key indexes.

Creating Reverse Key Indexes

Creating reverse key indexes can improve performance in an Oracle Parallel Server environment where modifications to the index are concentrated on a small set of leaf blocks. A reverse key index reverses the bytes of each indexed column (except the rowid) while keeping the column order. By reversing the keys of the index, the insertions become distributed across all leaf keys in the index.

For example, to create a reverse key index use the syntax:


Where "a" is the instance number and "b" is a generated unique key.

In INSERT operations, allocation of free space within an extent may also cause high lock convert rates. This is because multiple instances may wish to insert new rows into the same data blocks or into data blocks that are arranged closely together. Contention occurs if these data blocks are managed by the same PCM lock. To avoid this, either partition the tables and indexes so different instances use them, or create tables to allow use of multiple free lists and multiple free list groups.

See Also:


Selecting A Partitioning Method

To implement applications that optimize Oracle Parallel Server, use one of these partitioning methods:

Partitioning is an important part of Oracle Parallel Server deployment because it is the best way to avoid global data block contention or "pinging". With Cache Fusion, partitioning becomes less critical since the number of forced writes to disk significantly decreases.

Partitioning tables to increase Oracle Parallel Server performance has various development and administration implications. From a development perspective, when you partition a table, the quantity and complexity of application code required for that table may increase. In addition, partitioning a table may compromise the performance of other application functions, such as batch and data warehouse queries.

You must also understand your system's performance implications and be aware of the design trade-offs due to partitioning. Your goal is to minimize the need for synchronization. With minimal lock conversions, and the resulting decrease in Distributed Lock Manager activity, Oracle Parallel Server performance is predictable and scalable.

By partitioning applications and data, you can maintain data-to-node affinities. If your system experiences excessive Distributed Lock Manager lock activity, your partitioning strategy may be inappropriate, or the database creation and tuning process was ineffective.

Regardless of the method you use, the method must be "data-centric" to achieve optimal results, as described in the next section.

Partitioning Based on Data, Not Function

Focus your partitioning strategy on the data and how it is used, not on the application's functions. When you partition, load balancing is not necessarily the primary objective.

To determine which partitioning method to use, examine the data access properties of your business function, for example, the locality, type, and frequency. Group them into a few main categories and determine the locking strategy and configuration.

Using this method to set up partitioning creates data block-to-cache affinities across all instances. In addition, Distributed Lock Manager activity is more predictable so you can more easily achieve:

If the methodologies described in this chapter do not provide adequate performance, consider doing one of the following:

Note that the more blocks you cover with a single lock, the greater the likelihood that your application will experience excessive false pings.

Application Partitioning Techniques

One of the simplest ways to partition your database load is to run subcomponents of applications that access the same database on different nodes of the cluster. For example, one subcomponent may only reference a fixed set of tables residing in one set of data files. Another application may reference different tables residing in a different set of data files.

In this example, you can run these applications on different nodes of a cluster and achieve good performance. Moreover:

This scenario is particularly applicable to applications that during the day support many users and high OLTP workloads, and during the night run high batch and decision support workloads. In this case, you can partition applications among the cluster nodes to sustain good OLTP performance during the day.

This model is similar to a distributed database model where tables that are accessed together are stored together. At night, when it is necessary to access tables that may be partitioned for OLTP purposes, you still can exploit the advantages of a single database: all the data is stored effectively within a single database. This should provide improved batch and decision support performance, better overall SQL performance, reduced network traffic, and fewer data replication issues.

With this approach, ensure that each application's tables and indexes are stored such that PCM locks do not cover data blocks used by both applications. Otherwise the benefit of partitioning is lost. To do this, store each application's table and index data in separate data files.

Applications sharing SQL statements perform best when they run on the same instance. Because shared SQL areas are not shared across instances, similar sets of SQL statements should run on one instance to improve memory usage and reduce parsing.

Methodology for Application Partitioning

This section describes the following five steps for application partitioning:

Step 1: Define the Major Functional Areas of the System

Identify the major functions of the application. For example, a major hotel chain might develop a system to automate the following high-level functions:

Also determine which users are going to access the data from each of the functional areas.

Step 2: Identify Table Access Requirements and Define Overlaps

Determine which tables each functional area accesses and identify the overlaps. Overlaps are simply tables that users from more than one functional area access. Table 5-1 shows the overlapping tables from this example in bold; the remaining tables are accessed exclusively by the listed functions denoted by the column headings.

Table 5-1 Example of Overlapping Tables
Hotel Reservation Operations  Front Desk Operations 

Table 1 

Table 12 

Table 7 

Table 14 

Table 15 

Table 15 

Table 11 

Table 16 

Table 19 

Table 19 

Table 20 

Table 20 

The objective is to identify overlaps that can cause global conflicts and thus adversely affect application performance. In this example, both functions concurrently access three tables. The remaining tables that are accessed exclusively require fewer locks.

Step 3: Define the Access Type for Each Overlap

Determine the access type for each overlap.

Table 5-2 Example of Table Access Types
Hotel Reservation Operations  Overlap Access Type by Reservations  Overlaps  Overlap Access Type by Front Desk  Front Desk Operations 

Table 1 

S (Select)

I (Insert)

U (Update) 

Table 15

Table 19

Table 20 



Table 12 

Table 7 

Table 14 

Table 11 

Table 16 

In this example, both functions access:

Step 4: Identify Transaction Volumes

Estimate the number of transactions you expect the overlaps to generate.

Table 5-3 Example of Table Transaction Volumes
Hotel Reservation Operations  Transaction Overlap by Reservations  Overlaps  Transaction Overlap by Front Desk  Front Desk Operations 

Table 1 

S (10 per second)

I (100 per second)

U (50 per second)  

Table 15

Table 19

Table 20  

S (50 per second)

I (10 per second)

U (90 per second)  

Table 12 

Table 7 

Table 14 

Table 11 

Table 16 

Given these transaction volumes, the overlap tables may prove to be a performance problem. However, if the application infrequently accesses the tables, the volumes shown in Table 5-3 may not be a problem.

Step 5: Classify Overlaps

Use the following criteria to determine how to deal with the tables:

Index-only Tables and Their Indexes

For these tables, you can assign extents by instance to acquire an exclusive lock. Conflicts for these tables and their indexes are relatively easy for Oracle Parallel Server to resolve.

Resulting Example Configuration

Figure 5-1 illustrates the resulting configuration of the tables:

Figure 5-1 Classifying Overlaps for Application Partitioning

If you cannot use application partitioning to resolve conflicts within these tables, consider departmental partitioning as described in the next section.

Departmental and User Partitioning

An alternative partitioning method than can help minimize contention is departmental or user partitioning. There are several methods of implementing departmental and user partitioning.

For one type of departmental partitioning, separate the tables by access groups based on geographic location. For example, assume the hotel reservation system processes room requests for hotels around the world. In this case, you might partition the application by geographic markets such as:

This configuration might resemble the partitioning illustrated in Figure 5-2:

Figure 5-2 Overlaps for Geographic Partitioning

In addition to geographic partitioning, you can also use the advanced partitioning options of the Oracle8i Enterprise Edition. These include three table partitioning methods:

Each method has a different set of advantages and disadvantages. Thus, each method is appropriate for a particular situation where the others are not.

Range Partitioning

Range partitioning maps data to partitions based on boundaries identified by ranges of column values that you establish for each partition. This feature is generally useful only for Decision Support Systems applications.

Hash Partitioning

Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to a partitioning key. This feature is primarily useful for DSS applications.

Composite Partitioning

Composite partitioning combines the features of range and hash partitioning. With composite partitioning, Oracle first distributes data into partitions according to boundaries established by the beginnings and ends of the partition ranges. Then Oracle further divides the data and distributes it with a hashing algorithm into subpartitions within each range partition.

See Also:

Oracle8i Data Warehousing Guide for more information about partitioning.  

The remaining two partitioning methods discussed in this chapter require significant programming effort and should be used only when the previously described methods do not provide optimal performance.

Physical Table Partitioning

Physical table partitioning involves the division of one table into two or more smaller tables. This requires application changes to use the new names of the smaller tables. Report programs must also change so they can join the smaller tables as needed to provide data.

However, Oracle can automatically manage partition independence for you. That is, if you use the same table name for all the smaller tables across the application, Oracle automatically segregates the data.

If you have adequate resources, you can use transaction partitioning. However, this method is quite complex and it takes much longer to implement than any of the methods described previously.

Transaction Partitioning

Transaction partitioning is the lowest level partitioning method. This method requires a three-tiered architecture where clients and servers are separated by a transaction monitor processing layer. Based on the content of a transaction, the transaction monitor routes transactions that act on specific tables to specific nodes. Using this method, you can create and load your tables using any method because the transaction monitor determines which node processes a particular transaction.

This method also allows you to achieve fine-grained transaction control. This makes transaction processing monitors very scalable. However, significant development effort is required to deploy this method.

The correct node for execution of the transaction is a function of the actual data values being used in the transaction. This process is more commonly known as data-dependent routing.

You can accomplish data-dependent routing in one of two ways: if the partitioning of the tables fits well within actual partition usage patterns, in other words, you partitioned the table by state or call center, and users are similarly partitionable, then you can accomplish manual routing by having users connect to the instance that is running the relevant application. Otherwise, the administration of data-dependent routing may be complex and can involve additional application code.

You can simplify the process if the application uses a transaction processing monitor (TPM) or remote procedure call (RPC) mechanism. It is possible to code into the configuration of the TPM a data-dependent routing strategy based on the input RPC arguments. Similarly, this process could be coded into procedural code using a case statement to determine which instance should execute the transaction.

Scaling Up and Partitioning

If you have properly partitioned your application for Oracle Parallel Server, as the size of your database increases, you should be able to maintain the same partitioning strategy and simultaneously have optimal performance.

The method to use when adding new functionality is dependent upon the types of data the new functions access. If the functions access disjoint data, your existing partitioning scheme should be adequate. If the new functions access the same data as the existing functions, you may need to change your partitioning strategy.

If your application is popular and it attracts more users than you expected, you may need to add more instances. Adding a new instance may also require that you repartition your application. You may also need to repartition if you add new instances in response to your application experiencing increased transaction rates.

Adding Instances

Before adding instances to your Oracle Parallel Server environment, analyze the new instance's data access requirements. If the new instance accesses its own subset of data, or data that is not accessed by existing instances, your current partitioning strategy should adequately prevent data contention. However, if the new instance accesses existing data, consider the following issues.

If you are adding new functionality to the new instance and the new functionality requires access to existing tables, consider revising your partitioning strategy. You may also need to alter your partitioning strategy if you reassign some users of an existing application to the additional instance.

You must also consider how adding additional instances to accommodate unexpected growth can result in complex, expensive re-partitioning tasks. This is why your initial planning and design process should take long-term growth into account.

Design-Related Batch Processing Issues

When scheduling batch operations, do not assume you can separate batch jobs from online processing and run batch jobs on a separate instance. Instead, consider the batch jobs as part of the normal daily load.

When developing a partitioning strategy, include you application's batch processing needs. Attempt to run batch jobs when there are not a lot of interactive users, such as a night or during off-peak hours.


Beware of batch jobs that perform full scans on shared tables.  

Using the DBMS_JOB Package to Manage Batch Job and Instance Affinity

Use this package to control which instances process which jobs. This package allows you to distribute job processing across a cluster in a manner that makes the most sense given each job's functions. This improves load balancing and limits block contention since only the SNP processes of the selected instance can execute the job.

As an example, simultaneously using Oracle Parallel Server and replication often results in pinging on the deferred transaction queue if all instances in a clustered environment propagate transactions from the deferred transaction queue. To limit activity against tables to only one instance, use DBMS_JOB to assign the work of processing jobs in the queue to a particular Oracle Parallel Server instance.

Although the following examples use replication to illustrate job affinity, you can use this feature for other scenarios.

See Also:


Go to previous page Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.