Last week I got the great opportunity to present on Method-GAPP again at the UKOUG 2011 (see presentation of the UKOUG2011). This time the focus in the presentation was partly on the multi linear regression and for the other part especially on AWR data. The multi linear regression makes it possible to get a linear equation to calculate the end user response time, what makes it possible to get a complete breakdown of all involved components in the end user response time as show in the graph below. In the graph the test and modelling from the white paper is shown:
In the breakdown, the UTILR80 is the utilization of the I/O and the UTILRAU is the utilization of the CPU. The breakdown shows that basically the REST is time which is always there but might be split out in more components if the involved model is enhanced. So more time is explained from the found variance of the end-user (R) response time.
So Method-GAPP is in a way always working in two important steps:
1. Which components are involved in the technical infra structure (mostly based on primairy components). So is it for example the database server, application server, etc.
2. Which details are involved in the found component from step 1. For example involved SQL, involved waitevents in the database.
To continue with the second step as we already know from step 1 that the database server is the most involved, we can start focussing on AWR data. As shown in the whitepaper and in earlier blog post, the AWR data can be seen as secondary component data in the method.
While gathering the end-user response time (R) data from the client, the gathered data in AWR from the tables DBA_HIST_SQLSTAT (columns elapsed_time_delta/executions_delta) and DBA_HIST_SYSTEM_EVENT (column time_waited_micro_fg) can help to find relations between the involved SQL and involved events. First I show a graph with all the involved SQL statements.
The graph shows that the involved statements in the transactions in the researched case are indeed statements which are doing things for the transactions. The found statements are also important in the AWR report, so this shows that Method-GAPP gives via statistics similar information as when hard measured like in AWR reporting. Method-GAPP will get the correct SQL out, which is involved in your researched end-user process and will not only show the sql which was the most used on that specific database.
If we look a the wait events than we can find also a similar picture, the graph is shown below.
The found SQL and wait events can help to optimize the end-user process, by tuning SQL or make other changes. In general we can say that there are three ways to optimize a system:
1. Schedule, do task on other moments. In the end a system is always tuned for the top utilization, so by schedule tasks better we have our workload better spread over the available capacity. The amount of resources per task doesn’t change but because work is better distributed the current capacity can be used better and so more tasks can in the end be handled by the system than before.
2. Optimalization, change application / sql code so less resources are used. In other words more work can be done with the same capacity. Because one task cost less resources as before.
3. Capacity, put more hardware in. Most of the time this solution is fast, but most of the time far from the best solution. Nowadays a lot of customers have good agreements with hardware vendors. So in case a customer needs capacity for a short period of time, the capacity is only added for this period and given back afterwards.
In general can be said that optimizing and scheduling tasks is the best way forward. This is most of all due to the fact that more licence fees have to be paid when added more machine capacity. Method-GAPP can help a lot with a good way to do performance management and find out where in a complicated technical infrastructure problems are. Further more it can help with capacity planning by doing performance predictions before changing or adding extra hardware.