Mining AWR data with Method-GAPP, profiling response time of end-user processes

In a lot of cases you like to know which SQL, wait-events, metrics, etc. in AWR is important for your specific end-user process response time. So it could be very well possible that the most important SQL, wait-events, metrics, etc. are show-in up in your “Top Activity” in your OEM grid control and AWR reports are actually not the most important for your end-user process response time.

After you know the share of time of your end-user process is taken by the database server (Method-GAPP primary components),  you actual can use all the AWR (and ASH) information as secondary components as input in Method-GAPP (see the white paper). Basically we simply can use the “Data Mining – Explain” step in the method and create a factorial analyses as shown below (see the white paper).

AWR data used with Method-GAPP

AWR data used with Method-GAPP

The created bar graph shows which SQL, wait-events, metrics, etc are the most important for your end-user response time. If you know which SQL it is, you can just start tuning that specific piece of code. The same is the fact which wait-event is important for your end-user process response time, this can be an indication where to look further. You can also go a step further and go on with the “Data Mining – Model” step in the method and create a linear equation of the different secondary components involved. So you can also see mathematically how the different secondary components have impact on your end-user response time.

I got the question what “Method-GAPP” would do with skew in the data. Basically you could calculate of every averaged metrics (ASH level data) the variance inside the data (I got this idea from a presentation of Robyn Sands, back in 2008/2009) and have the variance of that specific secondary component as an extra component in the factorial analyses. This will make it possible to find out if a spike in your end-user response time data be caused by skew in the data, basically the variance secondary component would come up as being of high importance explaining the variance in the end-user response time. In this case with skew in the data I mean the fact that we have big outliners in the data.

Just to summarize:

  • Method-GAPP will determine with the primary components the most important tier influencing the specific end-user process response time.
  • Method-GAPP will determine with the secondary components (AWR / ASH input) the most important SQL, wait events, metrics, etc. influencing the specific end-user process response time.
  • Method-GAPP will be able to see if skew in the data is of high importance for the end user process, by using as a secondary component the variance of e.g. SQL-elapsed times, events, metrics, etc.

The AWR data used in the database could be things like Heap size, java treads, etc on the application server, so basically be able to drill down on the application server the same way as in the database tier. So I can really claim to say that Method-GAPP is a real general approach.

As a final note: If you want to know more please read the white paper. The white paper will be revised and updated with new examples to improve it, so please check the web site of new versions of the white paper.

Regards,
Gerwin

4 thoughts on “Mining AWR data with Method-GAPP, profiling response time of end-user processes

  1. I’ve arrived here after following Dr. Gunther’s blog entries after Hotsos 2011, and I’m looking forward to reading the white paper. Cary Millsap tried to get me to come this year, but I couldn’t make it. Next year maybe!

  2. Thank you for checking out on this.. I really hope you enjoy reading the white paper. If you have any feedback, I like to get it. It was a pity you were not able to make it to Hotsos 2011. I personally think it is one of the best conferences in the world. For me Hotsos is also a very important place to network with all the other performance minded people and basically get some hybrid effect of having 1+1 to be 3 instead of 2.

  3. Really enjoyed your presentation at ukoug 2011. Your stuff is simply amazing and promising. Will for sure spend much time on testing your method. Thanks again for sharing all this.

Leave a Reply to Gerwin Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>