Monday, July 13, 2009

How to determine database segments which are participated in global cache wait events.

Here is a sql script to determine database segments which are participated in global cache wait events.

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
ash.p1=de.file_id and
ash.time_waited > 0 and
ash.p2 between de.block_id AND (de.block_id+de.blocks-1)
group by ash.sql_id,de.owner, de.segment_name, de.segment_type, ash.event
order by 6 desc;