-
Gerwin Hendriksen
-
Hotsos 2012
-
Company profile
-
Data Mining
AWR Data further mined with Method-GAPP
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:
Breakdown of all the involved components for the end-user response time
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. Read more
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
The Official Method-GAPP Whitepaper can be downloaded
After a long time of not able to finish my whitepaper, I finally finished it. Just struggling with time constraints made it hard to get my whole method on paper. I really wanted to have it finished before I would present the new improvements on the method at the HOTSOS Symposium 2011. In a couple of hours at 13:00 Dallas time I will do my talk based on the whitepaper and really hope I get a packed room of people.
Of course I hope the audience will see it’s potential and I will be able to put the message in the presentation as good as possible. I am just nervous on the demo I try to give… As some people may recall from HOTSOS 2009 I had a big issue with my laptop and in the end started 10 minutes late without a demo. So really hope this time everything will go smoothly.
The presentation will also become available on the blog, but for now you can download the official Method-GAPP whitepaper in the download section. As a last note I like to thank Cary Millsap and Dr. Neil Gunther for their inspiration and support.
Regards,
Gerwin
Method-GAPP in Real system modelling and making predictions before investment
Since I am working on my method-GAPP (see method-GAPP overview presentation) I have been challenged with the task to model a real system and not a Lab system with a programmed load profile. The big issue with a real system is that the load profile is changing all the time and the only thing we can recognize are periods of time we have a not to changing workload profile. For example an OLTP system will do during production hours from 9:30 in the morning till 11:30 and from 14:00 till 16:00 in the afternoon comparable things, but will do from 01:00 till 06:00 in the night something totally different. The given example could match maybe some OLTP systems but could be totally different for your OLTP production system. Read more
GAPP version 2 will be present at HOTSOS 2011
The last couple of months I have worked very hard on method GAPP and have finally made a very big improvement to it. In the past GAPP was only able to pin point where in the architecture the biggest variance in response time was caused. The improvement to GAPP makes it now also possible to find within certain error also the service time per measured component in the architecture. The point is that sometimes the component causing the biggest variance in end user response time is not always the component responsible for the most service time of the total response time.
The second version of GAPP has now an extra step inside the method, which is “data modeling”, the data is first modeled by using normalized response times for different amount of servers by using the Erlang C formula. Next to this data mining is used with a generalized linear model and ridge regression, to solve near collinearities in the data. With this extra step in place the prediction of service time and wait time per measured component became possible. When I first verified it against real system data I was really happy to find out that it works very well. More information will follow soon in blogs and hopefully for the end of this year in a white paper.
I am very happy I get the opportunity from Hotsos to be able to present it next year in march 2011. Via this way I also like to thank everybody who inspired me and made this possible, especially Cary Millsap and Dr. Neil Gunther.
The link to the presentation abstract: http://www.hotsos.com/sym11/sym_speakers_hendriksen.html
Regards,
Gerwin
Erlang formula’s programmed in PL/SQL
Since a long time I am busy using queuing formula’s to be able to calculate cpu queue’s and I/O queues. One of the big problems I was facing that the formula’s I like to use on big data sets with my GAPP analysis were only available in perl. For a long time I was using proximity functions to avoid the perl programmed Erlang-C formula and some other. Last weekend I just had the time to start programming the formula’s in PLSQL, just to have them easily accessible in my database. After finish programming the package I realized that the package can also be very handy for other people, so I decided to create this blog. The created package has the following important functions: Erlang-C, Erlang-B, Response Time in multi-server environments (ErlangR in the package), Queue length in multi-server environments (ErlangQ in the package) and Response Time in multi-queue environments like IO (paratqr in the package).
The formula’s are described in the book “Analyzing Computer System Performance with Perl::PDQ” from Dr. Neil J. Gunther 2005. In the source of the package are the exact locations in the documentation documented. Read more
Back to HOTSOS, HOTSOS 2010 Day 1
As always Hotsos started off with a nice keynote, this time done by Tom Kyte. Tom Kyte was introduced by Hotsos president Gary Goodman after the HOTSOS 2010 opening. Tom’s keynote theme was “Should we be less smart some times”. Tom told about own experiences, that he in the past gave sometimes too fast an answer. It is very important to think about an answer before giving it… Why? Well some things applied in the past or for a specific version, and now they don’t anymore… this can be a problem, a real issue. Always make sure you talk about the same definitions, and agree on them. Make sure talking about the same version and of course about similar circumstances. When you start giving answers in general be sure to work with facts and not some assumptions which might be wrong. So you should always think about the information, about the circumstances and the assumptions you do, it means “Continuous Thinking”.
/DSC04855.JPG)
How to change an outerjoin query to one without
Some time ago I encountered an issue with an outerjoin query. Although the execution plan was not that bad the respons time was really bad. I found out that the outerjoin in the query was causing the biggest problem. After doing some easy research I checked out the performance of a query with a direct join (a.col = b.col) and one without a join (a.col is not joined). Even if you would execute them seperate you would be much faster as doing the outerjoin. This brought me to the idea of doing these two queries for the sake of the data be retrieved by the outerjoin. By doing a union between these two queries and to get rid of the double records, I would have the same result as with the outerjoin. This is what I did, a collaegae of mine Jorrit Nijssen changed the code to a emp/dept example (thanks Jorrit). The base case looks like: Read more
ORA-01422 while drop table (even DUAL looks fine)
Today on the OTN forum a question was comming up about an ORA-01422, in combination of a table drop.
SQL> drop table xxx
2 /
drop table xxx
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
Due to the fact that this looked a bit weird, I did a small test on my 11.1.0.7 database. The following test case I did: Read more
Enhancing fast queries using “Tapio-Indexes”
During the years I have a lot of times encountered performance problems that ended up to be fast queries (less than 10ms) which are executed very much. In these situations the execution plans, from such queries can look like:
OPERATION OPTIONS OBJECT# NAME
——————– ——————– ———- ———————————–
SELECT STATEMENT .
SORT ORDER BY .
TABLE ACCESS BY INDEX ROWID 120713 XXX.TABLE_WITH_MANY_COLUMNS
INDEX RANGE SCAN 121558 XXX.INDEX_WITH_FEW_COLUMNS
In a lot of cases we deal with a query for example which is returning fewer columns than exists in the involved table like four, from an involved table having twenty columns. Although the execution plan looks already pretty “OK”, it still results in a query which is in top three most resource taking queries.
To enhance the query we can use “Fat Indexes” or nowadays better known “Tapio Indexes” (I call them like that nowadays). What are these kind of indexes… In principle nothing really new, but for me a couple of years ago an (re)eye opener by Tapio Lahdenmaki. This Finish independent database performance consultant and instructor gave a presentation at Miracle Open World 2007 in Denmark, I attended. Tapio can present this topic in a very special way, and in my opinion “his message” should be known by all developers and dba’s.
The most important thing of “his message” is the fact that we should create an index which is highly selective and contains all the columns asked in the query. So in principle we create an index with forexample two selective columns, with the most selective one in front and behind these two columns the other selected columns. When creating such an index an execution plan would look like:
OPERATION OPTIONS OBJECT# NAME
——————– ——————– ———- ——————————–
SELECT STATEMENT .
SORT ORDER BY .
INDEX RANGE SCAN 128140 XXX.IND_WITH_ALL_SELECT_COLS
The big advantage is that only the index is accessed and not the table anymore, what can result in a decrease of elapsed time for the query with a factor four or even more. Why this is much better? Let we go back how indexes and tables are stored on the physical storage.
An index is stored in an ordered way on the storage, so an index range scan will mean that data is retrieved from the storage in a sequential read from a storage point of view and so a read-head from a spindle can stay on almost the same place. This eliminates seek times and latency on the physical storage spindles.
A Table is stored in a non ordered way on the storage, so accessing table rows will almost always result in accessing the data in a scattered read from a storage point of view. This implies that a lot of seek and read times are wasted.
Of course the existence of caches makes it a bit different but still the basic principles are valid, this means also for striping.
So when only an index is accessed instead of index and table, we don’t waste read times for columns we don’t need (table rows), and we eliminate a lot of seek times for retrieving the different table rows.
If you want to know more about this kind of index design, you can read the book “Relational Database Index Design and the Optimizers” from Tapio Lahdenmaki.