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
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)
select
instance_number
,snap_id, 10*count(*)
from
dba_hist_active_sess_history
group by
instance_number, snap_id;
- DB_Time (Y-axis)
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
