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

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle RAC instance affinity tips

Oracle Tips by Burleson Consulting
August 8,  2008

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)

SELECT
  a.inst_id "Instance",
  (A.VALUE+B.VALUE+C.VALUE+D.VALUE)/(E.VALUE+F.VALUE) "global cache hit ratio"
FROM
  GV$SYSSTAT A,
  GV$SYSSTAT B,
  GV$SYSSTAT C,
  GV$SYSSTAT D,
  GV$SYSSTAT E,
  GV$SYSSTAT F
WHERE
  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'
  AND B.INST_ID=A.INST_ID
  AND C.INST_ID=A.INST_ID
  AND D.INST_ID=A.INST_ID
  AND E.INST_ID=A.INST_ID
  AND F.INST_ID=A.INST_ID;

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 2008) 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:

begin
dbms_adadm.create_queue_table
(
   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')

 

References:

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

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

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 performance tuning software 
 
oracle dba poster
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 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.