How to tune interconnet activity on RAC
I want to identify which queries cause most interconnect related waits on 2-node RAC cluster and which object-blocks are participated. I wrote following query for this purpose. Can you suggest any other idea or do you think sql below is a correct way to do this?
select /*+ rule */
ash.sql_id, de.owner, de.segment_name, de.segment_type,
ash.event,
sum(ash.time_waited) total_time,
count(*) count,
trunc(sum(ash.time_waited)/count(*)) Avg_wait
from
gV$ACTIVE_SESSION_HISTORY ash,
dba_extents de
where
ash.event like 'gc%' and
ash.P1TEXT='file#' and
ash.P2TEXT='block#' and
select /*+ rule */
ash.sql_id, de.owner, de.segment_name, de.segment_type,
ash.event,
sum(ash.time_waited) total_time,
count(*) count,
trunc(sum(ash.time_waited)/count(*)) Avg_wait
from
gV$ACTIVE_SESSION_HISTORY ash,
dba_extents de
where
ash.event like 'gc%' and
ash.P1TEXT='file#' and
ash.P2TEXT='block#' and
0