Translation

The oldest posts, are written in Italian. If you are interested and you want read the post in English, please use Google Translator. You can find it on the right side. If the translation is wrong, please email me: I'll try to translate for you.

lunedì, aprile 04, 2016

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(*) 
  from 
       dba_hist_active_ess_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.
  • 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

Nessun commento: