Oceans, Islands, and Rivers in ASH

The Idea of Oceans, Islands and Rivers in performance data from Oracle sessions comes from Cary Millsap. I recommend reading his Presentation - especially page 33+. 

To summarize it very short: in an ocean of idle states (where the application doesn't interact with the session at all) there are islands of activity, but within these islands, there still can be rivers of idle events - when there is communication between the server process and the application. 

Even the algorithm was defined for trace data, I'm sure it can be implemented based on ASH data. Of course, the resolution isn't as fine as trace data, but if only big islands are of interest, and these islands doesn't have to many rivers, sampled ASH sould be acceptable.

This can be done quite easy with a statement like this.

I tried to keep it simple by having a CONST CTE at the beginning. there the RIVER_WIDTH can be set. It defines how many idle (non visible) samples define the widest river. Everything greater is an ocean which separates islands. 

In the 2nd CTE FILTER_FIRST, additional filters can be applied to ASH. Everything which narrows the focus can help, like USER_ID, SAMPLE_ID or timestamps. The idea matches proper scoping in SQL*Trace.

WITH CONST as (
SELECT /*+ qb_name(QB_CONST) */
    100 as GANTT_LENGTH -- unit: characters
  , 1   as RIVER_WIDTH  -- unit: sample_periode of ASH. --everything else IDLE is an OCEAN
FROM DUAL  
) ,FILTER_FIRST as (
SELECT /*+ qb_name(QB_FILTER_FIRST) */  ash.* 
FROM gv$active_session_history ash
WHERE 1=1 
--  AND USER_ID=123
--  AND ....
--  AND SAMPLE_ID < 487871
), ISLANDS as (
SELECT /*+ qb_Name(QB_ISLANDS) */
      min(BEGIN_SAMPLE_ID) OVER () total_min_sample_ID
   ,  max(END_SAMPLE_ID) OVER () total_max_sample_ID      
   ,   BEGIN_SAMPLE_ID
   ,  END_SAMPLE_ID
   ,  END_SAMPLE_ID - BEGIN_SAMPLE_ID +1  as ISLAND_LENGTH
   ,  ACTIVE_COUNT
   ,  inst_id
   ,  session_id
   ,  session_serial#
FROM FILTER_FIRST ff
       MATCH_RECOGNIZE(
         PARTITION BY inst_id, session_id, session_serial#
         ORDER BY SAMPLE_ID 
         MEASURES 
           first(SAMPLE_ID) as BEGIN_SAMPLE_ID,
           LAST(sample_id)  as END_SAMPLE_ID,
           COUNT(sample_id) as ACTIVE_COUNT
         ONE ROW PER MATCH
         PATTERN( frst cont*)
         DEFINE cont as SAMPLE_ID - prev(SAMPLE_ID) <= (SELECT RIVER_WIDTH FROM CONST) 
       )  
)
SELECT /*+ qb_name(QB_MAIN)*/ isl.begin_sample_id
     , isl.end_sample_id
     , isl.island_length
     , isl.active_count
     , isl.inst_id
     , isl.session_id
     , isl.session_serial#
FROM ISLANDS isl
Order by isl.begin_sample_id
        , isl.inst_id
        , isl.session_id
/

The result on my sandbox is like this:

BEGIN SAMPLE_ID	END SAMPLE_ID	ISLAND LENGTH	ACTIVE COUNT	INST ID	SESSION ID	SESSION SERIAL#
487760	487776	17	17	1	21	6605
487777	487780	4	4	1	274	7693
487781	487782	2	2	1	264	65268
487785	487785	1	1	1	278	22792
487791	487792	2	2	1	22	17104
487794	487794	1	1	1	22	37292
487795	487795	1	1	1	284	36814
487796	487797	2	2	1	278	22792
487798	487798	1	1	1	22	37292
487800	487800	1	1	1	31	44098
487800	487803	4	4	1	278	22792
487802	487875	74	71	1	22	37292
487807	487807	1	1	1	282	7351

BEGIN_SAMPLE_ID, END_SAMPLE_ID, INST_ID, SESSION_ID and SESSION_SERIAL# together describe every single island. It can be userd later on to do specific analysis on this island. 

ISLAND_LENGTH show the total length of the island, and ACTIVE_COUNT is the amount of "land" on this island. The lower this number, the more rivers were found. 

This can be a total different approach to analyse ASH data.