Tushar's Top Ten Tuning Tips for Oracle Parallel Server

Oracle Parallel Server (OPS) provides several features you can use to achieve higher performance and availability in your OPS database. However, successfully leveraging these features requires careful planning on your part. In addition to the normal tuning activities required for a standalone Oracle instance (and which are still required under OPS), there are a number of OPS-specific tuning activities you should perform.

Sanjay Mishra, coauthor of Oracle Parallel Processing, has written Oracle Parallel Execution Tuning Tips, which offers ten tips on when and how to use Oracle's parallel execution features. Parallel execution--the spreading of work over multiple processors--can significantly improve the performance of data-intensive jobs.

With OPS, multiple instances share one database. Each instance has its own set of background processes, and its own shared global area (SGA). Figure 1 shows a typical OPS configuration with two instances accessing one shared database. If both instances are accessing the same database object, copies of database blocks from that object will end up in the buffer cache of both instances. This can lead to contention between instances, especially if both instances need to update the object in question.

Figure 1: An OPS database with two instances

Because the possibility for contention exists, a synchronization mechanism is necessary. This mechanism will maintain consistency among the various copies of an object that may exist in the buffer cache of each OPS instance. Oracle uses instance-level locks, referred to as Parallel Cache Management (PCM) locks, to maintain consistency when multiple instances access a shared database. The use of PCM locks entails a performance overhead that you need to manage. The ten tuning tips in this article discuss different approaches you can take to minimize PCM locking overhead, thus improving your OPS database's performance.

1. Partition Your Applications.
Application partitioning refers to the practice of designing and deploying your applications in such a manner as to minimize the need for two instances to access the same table, index, or other database object. For example, one instance may be used for a payroll application while another instance is used for an inventory application. Since these applications are likely to access different sets of tables, the need for synchronization between instances is minimized. Application partitioning normally must be done when you first design your database and its applications.
2. Allocate More Locks to "Hot" Datafiles.
PCM locks are allocated on a per file basis. Each data file can be assigned a specific number of locks. PCM locks consume memory in the SGA--the more locks you allocate, the more memory is required. You also pay a penalty at instance startup time, because all those locks must be created. The trade-off is that with more locks, you have better protection against conflicts between instances. The number of database blocks protected by each PCM lock is a function of file size and the number of locks allocated to the file. The more locks you have, the fewer blocks each lock must protect. Conflicts between instances occur when they all need to access blocks protected by the same PCM lock. Thus, a lower number of blocks per lock reduces (but does not eliminate) the likelihood of such contention. This is an especially important issue for "hot" datafiles containing objects that are frequently modified by more than one instance. You should allocate a higher than normal number of locks to such datafiles.
3. Separate Read-only Data From Modifiable Data.
Database objects that are read-only do not require synchronization, because these objects are not modified by even one instance, much less by multiple instances. A reference table is a good example of such an object. Place such objects into a tablespace of their own, and allocate only one PCM lock for the datafiles in that tablespace. One lock is enough, because all instances can hold that one lock in shared mode for the purpose of reading data from the read-only objects protected by that lock.
4. Plan For More Datafiles.
In an OPS environment, it is better to plan for more tablespaces and datafiles than you would plan for when running a standalone Oracle instance. If you have more datafiles, then you have more control over the number of PCM locks used to protect different datafiles (based on the database objects in them). To take advantage of this, group together database objects with similar access patterns, and place them in tablespaces that correspond to those patterns. For example, you might have one tablespace for read-only data, another for frequently updated data, and one or more for objects that fall in between those two extremes. You can then allocate one lock to the datafiles in the read-only tablespace, and many locks to the datafiles in the high-update tablespaces. The more tablespaces you have, the greater your flexibility in terms of grouping your objects.
5. Add Locks to Accommodate New Datafiles.
Remember that PCM locks are allocated on a per-datafile basis. When adding a new datafile, it's easy to forget to add additional PCM locks for use in synchronizing access to the objects in that datafile. Don't forget this important detail. When you add new datafiles in an OPS environment, remember to allocate more locks.
6. Use a High PCTFREE Value.
You can sometimes use a high PCTFREE value to reduce contention between two or more instances for the same table. The PCTFREE value tells Oracle the amount of free space that you want to maintain in a block to accommodate future row expansion. The higher the value of PCTFREE, the fewer rows you'll have in a block, and the more spread out your data becomes. As long as two instances aren't contending for the same row, spreading out your data like this reduces the likelihood of contention for a block. In effect, you are trading space for performance.

In extreme cases where PCM lock contention is severe, you can set PCTFREE high enough so that each database block contains only one row. You can then allocate enough PCM locks so that each PCM lock protects only one block, and consequently only one row. This is practical only for small tables as it wastes a lot of space, but it ensures that contention never occurs unless two instances are really modifying the exact same row.

7. Use Free List Groups.
Use free list groups to reduce contention for segment header blocks. Segment header blocks are used to record free space in a segment, and get modified when new data is inserted into a table (or an index or a cluster). When two instances concurrently insert high volumes of data into a single table, they will end up contending for the segment header, which contains the master free list. Partitioning free space into groups improves performance by reducing contention for the list of free data blocks in a segment. Essentially, given enough groups, each instance can access a different list. Use the FREELIST GROUPS storage parameter to set the number of master free lists to match the number of instances accessing your OPS database.
8. Preallocate Extents.
When you create multiple, free-list groups for a segment, preallocate extents for that segment, and assign each one to a specific OPS instance. By specifying the datafile from which to allocate an extent, and by allocating separate PCM locks to each such datafile, you can reduce the overhead of contention for subsequent Insert and Update operations. This is because each instance will be inserting into an extent that is separate from that used by other instances. In addition, there will be fewer subsequent conflicts if the instance that originally inserts data also performs any subsequent DML (Data Manipulation Language) operations on that data.
9. Use Reverse Key Indexes.
An index block typically references more rows than are contained in each data block for the corresponding table. When an index is based on a column that increases in a sequential fashion, and two or more instances are inserting data into the underlying table, there is a strong likelihood that both instances will be contending for the same index block. This is because sequential index entries are likely to be in the same block. Reverse key indexes reverse the bytes in each index entry, causing sequential entries to be dispersed across the index tree. Hence, there is less chance of contention for the same index block. One trade-off involved with using this technique is that by its nature, reverse key indexes cannot be used as the basis for an index range scan.
10. Use OPS Dynamic Performance Views.
Finally, don't forget to monitor your OPS performance using the many V$ and GV$ dynamic performance views that return statistics related to OPS synchronization. You should analyze these OPS-related statistics to identify objects and datafiles for which a high degree of contention exists. After identifying sources of contention, you can restructure tablespaces and fine-tune PCM lock allocation in order to reduce it.

It is not possible, of course, to discuss all of these tuning tips in detail in a short article. You'll find a more extensive discussion of these issues in our book, Oracle Parallel Processing, which I cowrote with Sanjay Mishra. The Oracle OPS manuals are also a valuable source of good information. By paying attention to the ten issues I've described, you can minimize contention between instances, and maximize the performance of your OPS database.