Oracle Performance: Checking RAC Internode Time

I always blame the server

It is not unusual to blame "the network" for RAC performance issues. The only problem with that idea is that it's often tough to prove.

A Better Way

Here is an easy way to check the RAC internode time.  One of the quickest events that Oracle uses to communicate is called the "2-way gc grant."  It's normally very fast (typicall 1 ms or less.)

We can get an historical chart, by snapshot, of this fast event.  In this way, you can see if RAC has been having trouble communicating amongst the nodes.

Plug-in Power

WITH BASE AS (SELECT instance_number, SNAP_ID, TOTAL_WAITS, time_waited_micro/1000 timemsec,
 LAG(time_waited_micro/1000, 1) OVER (ORDER BY snap_id) AS PREV_TIME_MSEC,
 LAG(total_waits, 1) OVER (ORDER BY snap_id) AS PREV_waits
FROM dba_hist_system_event
WHERE event_name ='gc cr grant 2-way'
and instance_number = 1
and snap_id between tbd and tbd
)
SELECT b.SNAP_ID, b.instance_number NODE,
to_char(begin_interval_time, 'dd-mon-yy-hh24:mi') BEG,
 (TOTAL_WAITS-PREV_WAITS) "#WAITS",
ROUND((TIMEMSEC-PREV_TIME_MSEC)/(.001+TOTAL_WAITS-PREV_WAITS), 1) "RATE" FROM BASE b,
dba_hist_snapshot S
where b.instance_number = s.instance_number
andb.snap_id = s.snap_id
and (total_waits-prev_waits) > 99900
ORDER BY 1
/

Expected Output

On most systems I analyze, the internode time is 1 ms or less.  In the output below, you can see that the internode time is rock-steady at just .3 ms.  In my experience, that is about the best possible.

  SNAP_ID       NODE BEG                 #WAITS       RATE
--------- ---------- --------------- ---------- ----------
    17236          7 13-apr-09-05:00    1942375         .3
    17237          7 13-apr-09-06:00    1913682         .3
    17238          7 13-apr-09-07:00    3763238         .3
    17239          7 13-apr-09-08:00    2360403         .4
    17240          7 13-apr-09-09:00    1694804         .3
    17241          7 13-apr-09-10:00    1564779         .3
    17242          7 13-apr-09-11:00     551387         .3

Customize as Needed

In the script above, be sure to put in your own snapshot_id's.  Also, you may want to check the internode performance among all the nodes--not just node 1, as shown in the script above.

Snappy Interviews: 100 Questions to Ask Oracle DBAs



$33.99



By Christopher Lawson
















[image error]

Using a Network "sniffer"