Forgot your password?    
+ Reply to Thread
Results 1 to 1 of 1

Thread: RAC Specific Performance Tuning Techniques

  1. #1
    Expert Oracle Administrator
    Join Date
    Oct 2011
    New Delhi, India

    RAC Specific Performance Tuning Techniques

    Improving the buffer cache hit ratio

    Accessing data that is already in cache is always more efficient than performing a disk I/O. On top of the disk I/O overhead, there may be additional overhead due to global cache operations when running RAC. Whenever a buffer to be read is not present in the local cache, and the correspondent global cache resource is not locally mastered, a message needs to be sent to the resource master to check if any other instance has the buffer. In the case no other instance has it, the master grants permission for
    the disk read to take place. That means inter-node communication has to take place before the I/O is performed.

    Improving the buffer cache hit ratio may involve:
    - Resizing and tuning the buffer cache.
    - Avoiding and if possible eliminating long full table scans in OLTP environments.

    Avoiding unnecessary parsing

    In RAC, library cache and row cache operations are globally coordinated, so excessive parsing means additional interconnect traffic.

    Minimizing locking usage

    As transaction locks are globally coordinated, they also deserve special attention in RAC. For example, using tables instead of Oracle sequences to generate unique numbers is not recommended because it may cause severe contention even for a single instance system. That kind of contention will be more critical in RAC because enqueues need global coordination.

    Removing unselective indexes
    Indexes that are not selective will not improve query performance, but can degrade DML
    performance. In RAC, unselective index blocks may be subject to inter-instance contention, increasing the frequency of cache transfers for indexes belonging to INSERT intensive tables.

    Improving local cache access to buffers

    It is more efficient to access data locally, minimizing the need for cache transfers.
    Using automatic segment space management or freelist groups can provide instance affinity to table blocks.
    Increasing sequence caches improves instance affinity to index keys deriving their values from sequences. That technique may result in significant performance gains for multiinstance insert intensive applications.

    Using table partitioning to alleviate inter-instance contention.

    Using data dependent routing techniques

    This can be achieved by workload division through the use of Services.
    This means directing some application modules or particular programs to particular instances in order to reduce the frequency of cache transfers.

    Identifying read only tables and moving them to read-only tablespaces

    In a read-only tablespace, reading from disk does not involve any global cache operation, so that interconnect traffic can be reduced.

+ Reply to Thread

Similar Threads

  1. Oracle Database Performance Tuning Training in Delhi or Online (Advanced)
    By Hemant in forum Oracle Training Modules - Database RAC RMAN DataGuard GoldenGate Apps FMW Weblogic OAM OIM ODS ..
    Replies: 0
    Last Post: 04-20-2013, 09:51 AM
  2. How to use hints in Oracle sql for performance
    By devesh_ocp in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 04-25-2012, 03:19 PM
  3. SQL Tuning Advisor in 10G
    By devesh_ocp in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 04-25-2012, 03:16 PM
  4. Requests run by a specific Concurrent Manager/Queue within the date range
    By Amit in forum SCRIPTS : Concurrent Requests & Concurrent Request Sessions
    Replies: 0
    Last Post: 11-04-2011, 12:29 PM
  5. Oracle database background processes specific to RAC
    By Hemant in forum RAC Concepts and Administration
    Replies: 0
    Last Post: 11-04-2011, 12:18 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training

Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics