Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle RAC instance affinity tips

Oracle Database Tips by Donald BurlesonAugust 8,  2015

While Oracle has touted the approach is sending incoming transactions to the least-loaded node, many Oracle RAC experts agree that RAC load balancing is critical to reduce overhead on the global cache fusion layer and keep performance at acceptable levels.

In general, ?alike? queries should be segregated by node (i.e. customer queries to node 1, product queries to node 2), unless you have a high-speed I/O sub-system like SSD.  See the book ?Oracle RAC on SSD for details.

Oracle RAC and instance affinity

The process of assigning specific users and transactions to specific RAC instances is called ?instance affinity?.  For sophisticated Oracle shops, you can take this assignment concept even further and deploy Oracle's CPU affinity as well.

In general, contention for shared resources manifests itself as waits for blocks in the data buffer, similar to buffer busy waits on a single instance database.  You can see these metrics in gv$sysstat, and here is a sample query (see the Oracle code depot for full scripts)

  a.inst_id "Instance",
  (A.VALUE+B.VALUE+C.VALUE+D.VALUE)/(E.VALUE+F.VALUE) "global cache hit ratio"
  A.NAME='gc gets'
  AND B.NAME='gc converts'
  AND C.NAME='gc cr blocks received'
  AND D.NAME='gc current blocks received'
  AND E.NAME='consistent gets'
  AND F.NAME='db block gets'

Traditionally on a single instance databases, we could fix contention for a single data block with several methods:

  • Reorg the table and adjust PCTFREE to a small number (say 5) to spread the table rows across many data blocks.

  • Reorg the table into a very small (2k) blocksize.

  • Increase freelists or freelist groups (for RAC).

  • Move the tablespace to LMT's or deploy ASSM (note: this has shortcomings).

  • In RAC, use instance affinity to make all requests go to the same node.

Using RAC instance affinity for advanced queuing

In the IOUG Select Journal (3rd quarter 2015) we see the report of Han Xie, a RAC DBA whose database experienced severe performance problems relating to advanced queuing.  In his case, the shared object was causing high cache buffer chain (CBC) latches, and excessive logical I/O (consistent gets).

In his case, he used instance affinity with advanced queue table, creating them with the primairy_instance and secondary_instance parameters to associate the queue with node 5 of his RAC cluster:

   queue_table          => 'my_tbl_01', . . .
   primary_instance     => 5
   secondard_instance   => 2
); end

He then created five queue tables, one for ach node.  Han notes that this removed the overhead of block pinging with cheaper messaging:

"With this setup, the data blocks of this table will be moved to other instances for enqueue/dequeue.  If other instances need to enqueue/dequeue on this queue, the requesting instance will send a message to this instance.

Upon receiving this message, this instance will perform the enqueue/dequeue operation and inform the request instance that it's done.  Sending such messages is much cheaper than sending the current data block"

Han claims that this technique reduced his logical read by over 99.95%, and it cut in half his log file sync waits.  Impressive.

Han also explains a technique for job scheduling with instance affinity, whereby scheduled services will be run on a designated instance, and no others:

svrctl add service -d PUMAP ia AQ_01 -r pumae1 -a pumae2

dbms_scheduler.create_job_class(job_class_name=> 'AQ01', service=> 'AQ_01');

dbms_scheduler_create_job (job_name => v_jobname. . .
   job_class => 'AQ_01')



Oracle 11g Grid & Real Application Clusters, Steve Karam and Bryan Jones, Rampant TechPress, 2015

Oracle RAC & Grid Tuning with Solid State Disk, Donald K. Burleson et al, Rampant TechPress, 2015

Other notes:

Oracle Grid and Real Application Clusters

See working examples of Oracle Grid and RAC in the book Oracle Grid and Real Application Clusters.

Order directly from Rampant and save 30%. 



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.