Partitioning tables reduces inter-instance block contention in RAC installations?

Chapter 3 of the Oracle 9i (Release 1) Real Application Clusters Deployment and Performance guide contains a section on reducing the overhead involved with inter-instance communication over the high-speed interconnect when one RAC instance attempts to retrieve a block that is "mastered" by another instance.

The section mentions that one way to do this is by implementing table partitioning by range. However I don't understand how this works.

Assume I have two RAC instances on two nodes, i.e. Instance1 on Node1 and Instance2 on Node2. My EMP table is range-partitioned on the first letter of the employee's surname, so that Partition1 on Instance1 has all names starting with A through M, and Partition2 on Instance2 has all names starting with N through Z. If Instance1 receives a DML request for employee Smith, won't it (via the Global Cache Service) ping Instance2 for the appropriate block?

I assume that the use of table partitioning will ensure that most DML requests for a particular block will be handled by the RAC instance that is associated with the appropriate table partition. How is this achieved? Does the dynamic resource remastering notice, over time, that due to locality the cost associated with Instance1 accessing Smith is higher than the cost of Instance2 accessing Smith, and so inform the Global Resource Directory to route all future "Smith" requests to Instance2? So, initially, DML requests for Smith may be evenly handled by both instances, but over time subsequent requests will be routed to Instance2 exclusively?

I also assume that this is independent of whether or not the client submitting the DML is connected to one node or all.

Any help will be appreciated.

Thanks, Carey Received on Tue Oct 21 2003 - 09:17:21 CDT