ASH. Like a movie (3/3)

In the previous post [1a] [1b], I shown a chart that you can see here

This picture show how the sampling (blue) and real time analysis (read) are intimately linked each other. In order to obtain that, I used the query [2] based on what I explain now.
 
The master document [3] was published by John Beresniewicz, Graham Wood, Uri Shaft. For it, exist also a video on You Tube [4].

The main formula is

DB Time ~ sample_nr * (rows in ASH)

where the "sample_nr" is the sample frequency. It's value, depend from row surce.

* V$ACTIVE_SESSION_HISTORY,     sample_nr => 1

* DBA_HIST_ACTIVE_SESS_HISTORY, sample_nr => _ash_disk_filter_ratio (default is 10)

In my query, there are three main components. They are in the "with" section: "awr", "ash" and "db_time".

• AWR Here 

I select the sample frequency of DBA_HIST_ACTIVE_SESS_HISTORY view.

select 
       ash_value.ksppstvl 
  from x$ksppi ash_name 
      ,x$ksppsv ash_value 
 where 
       ash_name.indx    = ash_value.indx 
   and ash_name.ksppinm = '_ash_disk_filter_ratio'; 

• ASH (Y-axis) 

The "rows in ASH" values, are the rows in DBA_HIST_ACTIVE_SESS_HISTORY.

select 
       instance_number
      ,snap_id, count(*)
      ,snap_id, 10*count(*) 
  from 
       dba_hist_active_ess_history
       dba_hist_active_sess_history  
group by 
       instance_number, snap_id; 

• DB_Time (Y-axis) 

The last piece of my query is the value of DB Time. For it I used the LAG analytic function in order to obtain the previous row:

select 
       snap_id 
      ,instance_number 
      ,(value - lag(value) over (partition by instance_number order by snap_id))/1000000 diff 
  from 
      dba_hist_sys_time_model 
 where 
      stat_name='DB time'; 


As last step I join this three query with DBA_HIST_SNAPSHOT, in order to get the "sample time" (X-axis). See [2] to get the full statement.

• Update

06/Oct/2017 - modified dba_hist_active_ess_history in dba_hist_active_sess_history

06/Oct/2017 - modified count(*) in 10*count(*)

• References

[1a] http://orasal.blogspot.it/2016/03/ash-like-movie-13.html
[1b] http://orasal.blogspot.it/2016/03/ash-come-un-film-13.html
[2] https://www.dropbox.com/s/gp4fh6soz0erwvq/ashvsdbtime.txt?dl=0
[3] http://www.slideshare.net/jberesni/ash-architecture-and-advanced-usage-rmoug2014-36611678
[4] https://www.youtube.com/watch?v=rxQkvXIY7X0