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.

mercoledì, aprile 06, 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.

  from x$ksppi ash_name 
      ,x$ksppsv ash_value 
       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.

      ,snap_id, count(*)
      ,snap_id, 10*count(*) 
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:

      ,(value - lag(value) over (partition by instance_number order by snap_id))/1000000 diff 
      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


Nessun commento: