Ash data summary by partition

Because sometimes ash blows out to 16Million rows, 99% in one partition and none of the regular tools work.

Still a work in progresss.. posting so I can find it.


with xml as (
  select dbms_xmlgen.getxmltype('select table_name, partition_name, high_value from dba_tab_partitions where table_name = ''WRH$_ACTIVE_SESSION_HISTORY''') as x
  from   dual
)
select a.*
, ( select /*+ index(b) */  min(b.SAMPLE_TIME) from  sys.wrh$_active_session_history b where b.snap_id = a.ls and a.dbid = b.dbid  ) first_time
, ( select  /*+ index(b) */  max(b.SAMPLE_TIME) from  sys.wrh$_active_session_history b where b.snap_id =a.hs and a.dbid = b.dbid  ) last_time
from (
select /*+ no_merge  index( a)  */
parts.dbid, partition,  snapid_low , decode( snapid_high, -1, 'MAXVALUE', to_char(snapid_high)) snapid_high,
min(snap_id) ls , max(snap_Id) hs, count( *) num_rows
from (
    select /*+ no_merge materialize  */ parts.partition,
    parts.dbid_high dbid ,
    --lag(parts.dbid_high) over ( order by ,
    nvl(lag(parts.snapid_high) over (  partition by dbid_high order by snapid_high desc ),0) snapid_low ,
    parts.snapid_high
    from (
      select extractValue(rws.object_value, '/ROW/TABLE_NAME') table_name,
             extractValue(rws.object_value, '/ROW/PARTITION_NAME') partition,
             nvl(to_number(regexp_substr(extractValue(rws.object_value, '/ROW/HIGH_VALUE'),'[0-9]+',1,1)),-1) dbid_high,
             nvl(to_number(regexp_substr(extractValue(rws.object_value, '/ROW/HIGH_VALUE'),'[0-9]+',1,2)),-1) snapid_high
      from   xml x,
             table(xmlsequence(extract(x.x, '/ROWSET/ROW'))) rws
    ) parts
) parts ,   sys.wrh$_active_session_history a
where ( a.snap_id >= parts.snapid_low and parts.snapid_low >= 0 )
and ( a.snap_id < parts.snapid_high  or parts.snapid_high = -1 )
and ( a.dbid >= parts.dbid and parts.dbid >=0 )
--and a.dbid = parts.dbid
group by
parts.dbid, partition, snapid_high, snapid_low
) a ;

DBID	PARTITION	SNAPID_LOW	SNAPID_HIGH	LS	HS	NUM_ROWS	FIRST_TIME	LAST_TIME
379250195	WRH$_ACTIVE_379250195_39963	37299	MAXVALUE	46313	63497	11666011	02/MAR/2016 16:23:16.407000000	16/FEB/2018 09:59:48.245000000
379250195	WRH$_ACTIVE_379250195_36987	0	37299	14454	21204	38002	10/JUL/2012 22:59:35.669000000	18/APR/2013 05:58:51.921000000