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 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:
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, 2015
Oracle RAC & Grid Tuning with Solid State Disk,
Donald K. Burleson et al, Rampant TechPress, 2015
Other notes:
