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

venerdì, aprile 01, 2016

ASH. Like a movie (2/3)

Here the Italian version

ASH. Like a movie (1/3)
ASH. Like a movie (3/3)


In order to show how ASH is like the DB Time, I made a chart reporting both metrics in the same place.

However, some measures are "strange". I show this in this post.

The first anomaly is one in which there is a negative peak.



Snap_id.Sample Time Sample DB Time
92722.28-MAR-16 07.00.43.468 AM 5980 -7166541

This is due to restart of the instance, so I delete this point.

The second anomaly is of 3 peaks. These are a surge of Time DB.


Snap_id.Sample Time Sample DB Time
92614.26-MAR-16 12.00.15.371 AM 72870 78461
92626.26-MAR-16 06.00.46.878 AM 75420 100475
92677.27-MAR-16 08.30.58.334 AM 44570 124111

After I deleted this three points, the chart is like this...


...and you can see that the sampling approximates the real time.

The cause of the first point is the restart of the instance that drops all statistics. I'm investigating why sometimes exists (in this case there are 3 points) the difference between ASH and DB Time.

ASH. Like a movie (1/3) ASH. Like a movie (3/3)

ASH. Come un film (2/3)

Qui la versione Inglese

ASH. Come un film (1/3)
ASH. Come un film (3/3)

Nell'ottica di mostrare che ASH è come il DB Time, ho graficato l'andamento di entrambi in un solo posto.

Tuttavia le misure hanno alcuni punti "anomali". Mostro questi punti di seguito.

La prima anomalia è quella in cui è presente un picco negativo.


Snap_id.Sample Time Sample DB Time
92722.28-MAR-16 07.00.43.468 AM 5980 -7166541

Questo è dovuto al riavvio dell'istanza. Allora io cancello questo punto.

La sesconda anomalia è costituita da 3 picchi. Questi corrispondono ad un'impennata del DB Time.



Snap_id.Sample Time Sample DB Time
92614.26-MAR-16 12.00.15.371 AM 72870 78461
92626.26-MAR-16 06.00.46.878 AM 75420 100475
92677.27-MAR-16 08.30.58.334 AM 44570 124111

Una volta eliminati anche questi tre punti, il grafico risultante è quello mostrato di seguito...


...da cui si deduce come nonostante il campionamento, ASH approssima bene il DB Time.

Eccetto il primo punto dovuto al riavvio dell'istanza che azzera tutte le statistiche, sto ancora indagando il motivo per cui in alcuni casi, esiste questa differena tra ASH e DB Time.

ASH. Come un film (1/3) ASH. Come un film (3/3)