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).
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.