Draw an GANTT chart of how your workload fits together

In this Post I try tho provide a different view on ASH.
Again I'm focussed more on a specific user experience than general system overview.
The Idea again is from Cary Millsap - for details see this Youtube Video.

This time I rely on a script from my post Oceans, Islands, and Rivers in ASH and will show how it can provide a GANTT diagram of a specific user experience.

I created 2 dummy experiences - both by a user A. With a small extension of the yesterdays script and a filter on only this user_id and only current samples, 

...
WHERE 1=1 
  AND USER_ID=111
  AND SAMPLE_ID > 293910
...

the result is

It shows 17 different sessions which were active at some time. And with some experience and good imagination, the 2 different experiences can be separated. 
But as my testcases are properly instrumented, with an additional filter it's easy to show only the one experience I'm interested in.

...

WHERE 1=1 
  AND USER_ID=111
  D SAMPLE_ID > 293910
    AND CLIENT_ID='R1'
...

This shows only those 7 lines which matches my CLIENT_ID='R1' user experience. 
It also shows 4 parallel proceses in lines 3-6. 2 of them completed slightly faster. 

Because of these parallel processes, the user experience is 816 seconds, but DB-time is 1195 seconds! (Another reason why simple aggregates on ASH are often misleading). 

This result can be used as a starting point for further investigation - often together with the applications responsible. 

The full statement is here, the GANTT_LENGTH defines how many characters the Gantt diagram should have for better visibility.

WITH CONST as (
SELECT /*+ qb_name(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('FILTER_FIRST') */  ash.* 
FROM gv$active_session_history ash
WHERE 1=1 
--  AND USER_ID=123
--  AND ....
--  AND SAMPLE_ID < 487871 -->
--  AND CLIENT_ID='R1'
), ISLANDS as (
SELECT /*+ qb_Name('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)  -->
       )  
), DERIVED as (
SELECT /*+ qb_name (DERIVED) */ 
 (select (total_max_sample_id - total_min_sample_id) / ( SELECT GANTT_LENGTH FROM CONST)
   FROM ISLANDS
   WHERE rownum =1) as divisor
FROM DUAL
)
SELECT i.begin_sample_id
     , i.end_sample_id
     , i.inst_id
     , i.session_id
     , i.session_serial#
     , rpad('>', trunc( (begin_sample_id - total_min_sample_ID)/ d.divisor ,1)+1, ' ') || 
         rpad('*',ceil(island_length/ d.divisor) ,'*') AS GANTT
from ISLANDS i
   , DERIVED d
WHERE     END_SAMPLE_ID - BEGIN_SAMPLE_ID +1 > 2   
ORDER BY BEGIN_SAMPLE_ID, ISLAND_LENGTH, INST_ID, SESSION_ID
/

and is also availalbe on github.