Guenadi N Jilevski's Oracle BLOG


Most common Oracle 11g RAC tuning tactics

Most common Oracle 11g  RAC tuning tactics

Application tuning and modifying schema design is often the most beneficial although can be more expensive solution as well.

Resizing and tuning the buffer cache.

Reducing full table scans in OLTP systems while doing updates and separate batch from OLTP at the same time. In RAC when we do a full table scan the instance doing them needs to get every single block and get the right version of the block images and if those blocks are being modified in other instances there is a lot of undo that have to be built. It is recommended if possible that operationally OLTP and DSS to be scheduled so that they do not coincide to improve performance.

Using Automatic Segment Space Management. Helps for RAC high insert intensive applications avoiding issues previously encountered and associated with free lists and free list groups and older way which Oracle used to manage space in segments.

Increasing sequence caching in RAC. It is good to cache and to cache with noorder clause.

Using partitioning to reduce inter-instance traffic. Partitioning using services allow us to avoid a lot of cache fusion overhead by having partitions accessed by users connected to some instances and other partitions accessed by users connected to other instances. It cannot be guaranteed but helps under certain circumstances.

Avoiding unnecessary parsing see above how to avoid parse overhead with application design and cursor sharing.

Minimizing locking usage. Contention is going to be exacerbated in RAC because apart from the contention available in a single instance database in RAC we have also an interconnect overhead so minimizing the locking overhead is very important in RAC environments. Typically this occurs with third party applications that does a lot of locking overhead due to the fact that their code is the same so that they can work on many database platforms.  This is worth investigating further.

Removing unselective indexes in OLTP as they are not used by CBO for access method but while executing DML statements they need to be maintained. That is an overhead generally speaking but in RAC we might end up having those index blocks transferred by cache fusion. Removing unselective indexes will improve performance. Index splits is another important thing that we must be aware and look at.

Configure interconnect properly with high bandwidth and low latency.

December 13, 2009 - Posted by | oracle

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: