View waits per Segment for a specific session

set timing on
col event format a30
col owner format a10
col segment_type format a20
col segment_name format a32
set linesize 400
set pagesize 2000

select distinct
       a.sql_id           as SQL_ID
      ,a.segment_name     as Segment_Name
      ,a.owner            as Owner
      ,a.segment_type     as Segment_Type
      ,a.event            as Event
      ,a.count            as Count
from
(
  select /*+ all_rows */
          de.Owner
         ,de.Segment_Name
         ,de.Segment_Type
         ,ash.Event
         ,ash.SQL_ID
         ,round(sum(ash.time_waited)/100000)    as Total_Time
         ,count(*)                              as Cnt
         ,round( (sum(ash.time_waited)/100000)
          / count(*), 2)                        as Avg_wait
  from
          v$active_session_history  ash
         ,dba_extents               de
  where
          ash.p1           = de.file_id
  and     ash.time_waited  > 0
  and     ash.p2           between de.block_id
                           and (de.block_id+de.blocks-1)
  and     ash.session_id   = &sid
  and     de.owner         not in ('SYS', 'SYSTEM')
  group by de.owner
          ,de.segment_name
          ,de.segment_type
          ,ash.event
          ,ash.sql_id
  order by 6 desc
) a
where rownum <= 30 -- Only look at worst performing
order by 6 desc, 4 asc
/