Release 2 (9.2)
Part Number A96598-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Real Application Clusters Deployment and Performance Release 2 (9.2) Part Number A96598-01 |
|
This chapter describes database deployment techniques for Oracle Real Application Clusters environments. The topics in this chapter are:
When deploying databases for Real Application Clusters, use the same methodologies that you would use for single-instance databases. If you have an effective single-instance design, then your application will run well on Real Application Clusters.
Single-instance Oracle database tablespace usage methodologies also apply to tablespace use in Real Application Clusters databases. You control the objects that reside in specific tablespaces in Real Application Clusters using the same methods that you use to control objects in tablespaces in single-instance Oracle databases.
To simplify tablespace administration, Oracle Corporation strongly recommends that you use automatic segment-space management in Real Application Clusters environments. Automatic segment-space management greatly improves extent management and reduces the overhead associated with searching for free space and allocating it when inserting new data.
If you cannot use locally managed tablespaces which are required for automatic segment-space management, then refer to the discussion about using free list groups as described in Appendix B, "Using Free Lists and Free List Groups in Real Application Clusters (Optional)". You may, also want to configure sequence number generation if every node uses sequence numbers.
| See Also:
Oracle9i Database Performance Planning for more information about tablespaces and performance methodologies |
As a general rule, only use DDL statements for maintenance tasks and avoid executing DDL statements during peak system operation periods. In most systems, the amount of new object creation and other DDL statements should be limited. Just as in single-instance Oracle databases, excessive object creation and deletion can increase performance overhead.
Cache Fusion eliminates most of the costs associated with globally shared database partitions by efficiently synchronizing this data across the cluster. However, object partitioning, without changing your application, can improve performance for hot blocks in tables and indexes. This is done by re-creating objects as hash or composite partitioned objects.
For example, consider a table that has a high insert rate which also uses a sequence number as the primary key of its index. All sessions on all nodes access the right-most index leaf block. Therefore, unavoidable index block splits can create a serialization point that results in a bottleneck. To resolve this, rebuild the table and its index, for example, as a 16-way hash partitioned object. This evenly distributes the load among 16 index leaf blocks.
|
Note: These techniques also apply to single-instance environments; they are not specific to Real Application Clusters. |
When deploying applications for Real Application Clusters, cache the Oracle sequence numbers whenever possible. To optimize sequence number use, each instance's cache must be large enough to accommodate the sequences. The default cache size holds 20 sequence numbers. To increase this, for example to hold 200, use this syntax:
ALTER SEQUENCE sequence_name CACHE 200;
To suppress caching, however, use the ordering feature. It is normal to lose some numbers after instance failures or after executing the SHUTDOWN command. This is true even in single-instance configurations. If you cannot avoid ordering, then you may need to disable sequence caching. In this case, expect some performance overhead.
| See Also:
Oracle9i Database Concepts for more information about sequences |
If sequences are insufficiently cached or not cached at all, then performance problems can result in an increase in wait times. In this case, examine the statistics in the V$SYSTEM_EVENT view to determine whether the problem is due to the use of sequences.
In such situations, the DC_SEQUENCES parameter's ratio of DLM_CONFLICTS to DLM_REQUESTS will be high. If this ratio exceeds 10 to 15%, and the row cache lock wait time is a significant portion of the total wait time, then it is likely that the service time deterioration is due to insufficiently cached sequences.
If your application cannot afford to lose sequence numbers, then implement sequences by storing them in database tables. However, there can be some performance overhead associated with implementing this strategy. This is true even in single-instance environments. As a general recommendation, rows storing sequence numbers should be locked for only a brief period.
Real Application Clusters can experience a minor amount of additional overhead as a result of the cache coherence needed for storing sequence numbers. If a single data block stores several sequence numbers and if more than one instance needs those sequence numbers, then the data block can be frequently transferred among the instances.
To minimize the adverse effects of frequent block transfers, set PCTFREE to a high value so that Oracle stores only a single row of the table containing the sequence numbers in each data block. In this case, the cache transfers only occur when the instances concurrently request the same sequence number.
This section explains how to identify and resolve performance issues in Real Application Clusters-based applications. It contains the following topics:
Query-intensive applications benefit from tuning techniques that maximize the amount of data for each I/O request. Begin monitoring performance before attempting to use these techniques and continue monitoring performance afterward to assess their effectiveness. The techniques are:
Use a large block size to increase the number of rows that each operation retrieves. This also reduces the depth of your application's index trees. Your block size should be at least 8K if your database is used primarily for processing queries.
Also set the value for DB_FILE_MULTIBLOCK_READ_COUNT to the largest possible value. Doing this improves the speed of full table scans by reducing the number of reads required to scan a table. Note that system I/O is limited by the block size multiplied by the number of blocks read.
If you use operating system striping, then set the stripe size to DB_FILE_MULTIBLOCK_READ_COUNT multiplied by the DB_BLOCK_SIZE multiplied by 2. If your system can differentiate index stripes from table data stripes, then use a stripe size of DB_BLOCK_SIZE multiplied by 2 for indexes.
Also consider:
TEMPORARY
| See Also:
Oracle9i Database Performance Planning for more information about using parallelism for improving query performance in Data Warehouse environments |
Transaction-based applications generally write more data to disk than other application types. To improve the ability of the database writer processes (DBWRn) to write large amounts of data quickly, use asynchronous I/O. If the access is random, then consider using a smaller block size. Monitor your application's performance both before and after initiating this method to make sure your system's performance is acceptable.
| Note:
You cannot use this technique on all systems types and not all platforms support asynchronous I/O. |
Using advanced queuing in Real Application Clusters environments can introduce functionality and performance-related issues as described in this section under the following topics:
Queue table instance affinity enables you to assign primary and secondary instance properties to queue tables. This enables automatic assignment of queue table ownership when instances shut down and restart. You can evaluate queue table instance affinity by querying the following views:
Global Cache Service resource acquisition is more expensive than local resource or local enqueue acquisition. If you improperly deploy advanced queuing, then its resource control behavior can adversely affect performance in Real Application Clusters environments. To avoid this, consider doing the following:
If you cannot use automatic segment-space management, then increase the number of blocks that are added to a free list when advancing the high water mark.
In general, cache transfers of queue table data blocks and queue table index blocks can occur under the following circumstances:
The frequency of cache transfers for queue table blocks can be reduced by creating queue tables with affinity to a particular instance, and then accessing the queue or queue table from this instance.
| See Also:
Oracle9i Application Developer's Guide - Advanced Queuing for general information about using Advanced Queuing |
Real Application Clusters and Cache Fusion introduce an improved diskless algorithm that efficiently manages cache coherency and enables you to deploy less complicated database designs and still achieve optimal performance. Real Application Clusters with Cache Fusion eliminates most of the processing overhead that might normally exist when multiple instances contend for resources.
Generally speaking, 80% or more of any performance issues result from 20% or less of a given workload. If you first attempt to deal with the 20% by observing some simple guidelines, then you can produce tangible benefits with minimal effort. You can address these problems by implementing any or all of the following:
|
 Copyright © 1999, 2002 Oracle Corporation. All Rights Reserved. |
|