• Different RAID configurations (Read and Write penalties)
• SSD usage
• SAN configuration
• Block alignment and recommendation

George Djerdj Srdanov presenting at Hotos 2013

The presentation was good and triggered me to check some things at a customer I am currently working for. In the customer case the block alignment in regard of the online redolog files might be a point of investigation.

After the presentation from George, I attended a presentation of John Clarke with a presentation titled “Exadata Storage Indexes, Inside and Out”. In this presentation John showed how Exadata is dynamically creates indexes at Exadata level and how the queries benefit of these indexes. It was pretty cool to see in his demo’s how the indexes actually work and how the queries were executed faster. You can find more info on the subject at: http://www.oaktable.net/blog/when-exadata’s-storage-indexes-used

John Clarke presenting at Hotsos 2013

When looking at presentations as from John I always get a little jealous knowing I am not able to test the demos easy myself, simply due to the fact I can’t put my hands on an Exadata storage myself on the moment. The technology though is really cool and picking up knowledge about them is for me a great thing to do…

After the Exadata presentation it was time to attend the second presentation from Gwen Shapira titled “Building an Integrated Data Warehouse with Oracle Database and Hadoop”. This presentation was more the introduction to Hadoop, how Hadoop is working and how BIG data is getting more important for Oracle. Gwen had a very strong statement regarding the usage of Hadoop when it should be chosen above an Oracle database, it said as how I got it:

“Database server cpu’s are very expensive, due to the Oracle database licensing. So if your data is almost not mutated, you have to store very much (unstructured) data to search through and data availability is not very critical, the data is probably a very good candidate to store in Hadoop.”

Gwen Shapira presenting at Hotsos 2013

The presentation further showed how Oracle can use Hadoop and how data can be exchanged between them. The presentation gave a very clear overview of the usage and possibilities of Hadoop. For me personal it is good to see Oracle making the connection with the BIG data world. On the long run I am sure Oracle has to do this to be able to stay a very big player in the future market. To read Gwen her vision, you can read: http://www.pythian.com/blog/hadoops-everywhere/

After the lunch break I had some doubts, which presentation I should attend. On one track there was Toon Koppelaars with his presentation titled “SQL Plan Management” and on the other track the presentation titled “Is Your Database Stressed? Load Testing for Peak Performance” from Noelle Stimely. I choose for the presentation from Noelle, because of the fact I will be able to see the presentation from Toon probably in the Netherlands.

The presentation from Noelle started a bit difficult due to some technical difficulties, according some of the attendees it had to do with some kind of curse of this room. I guess I was very lucky because I had my presentation in the same room without any trouble…. Anyway Noelle used an emergency laptop and further more “the bug” from Hotsos bugged her, like it had bugged me the night prior to my presentation.

Noelle Stimely presenting at Hotsos 2013

Noelle was due to her laptop trouble not able to show the live demo of the load testing she wanted to show, but overall she did a good job by showing how a load test should look like and what important things are to consider and think about. She also discussed Oracle Real Application Testing (RAT) and what is important to know about it when used for testing. If you like to read more you can checkout an older presentation from Noelle here: http://www.nocoug.org/download/2012-08/Noelle_Stimely_Load_Testing_Presentation.pdf

After the presentation from Noelle I attended the presentation from Kerry Osborne titled “Adaptive Optimization”. The presentation showed a new feature of the still not released 12c database. Kerry had to be very careful what to tell and what not to tell to the audience, of course very closely watched by Maria Colgan and Tom Kyte. The feature involved “Adaptive Optimization” looks to be pretty cool. It basically checks at runtime if the calculated execution plan is not sub optimal and if so it can adapt by exchanging a NESTED LOOP join by a HASH join or the other way around.

Kerry Osborne presenting at Hotsos 2013

It is always fun to get a presentation from Kerry because of his special way of presenting and interaction with the audience. When the 12c database version has been released more of his presentation material will be available.

After the presentation from Kerry the two rooms were consolidated to one big hall again and Maria Colgan gave her presentation titled “What to Expect from the Latest Version of the Oracle Optimizer”. Maria as being the Senior Principal Product Manager for the Oracle database optimizer presented some of the new optimizer features in 12c what was awesome to hear from the first hand. Because I also would be present the next day for the training day with Maria I enjoyed the moment…

Maria Colgan presenting at Hotsos 2013

After the presentation from Maria… I became a bit sad knowing the official Hotsos 2013 conference part was over…

Gary Goodman Founder/CEO from Hotsos thanked all the speakers and gave his farewell words to the attendees… Luckily the there was still a training day, so for me it was not the official farewell yet…

Gary Goodman giving his farewell at Hotsos 2013

After this conference day I was invited by Cary Millsap to join them for a dinner. It was really great to get this invitation (Thanks again Cary for the invitation!).

When we went to the restaurant Cary made a very true remark regarding Texas, when we saw some kind of bird, I guess a wild turkey. He remarked “In Texas everything is bigger, even the pigeons are”…. You can look for yourself, the so-called Texan pigeon….

Texan Pigeon – Hotsos 2013

At the restaurant it was quite awesome to have conversations with so many experts and at the same time enjoy the food and the drinks…

Dinner at Rafas 1 – Hotsos 2013

Dinner at Rafas 2 – Hotsos 2013

After this great evening, this concluded an awesome third, but official last day of Hotsos 2013….

At 13:00 I attended the presentation of Dr. N.J.G. Gunther titled “Superlinear Scalability: The Perpetual Motion of Parallel Performance”.  Because of his subjects I like to be present at his presentations, although on the other track Gwen Shapira had her first presentation titled “Visualizing Database Performance Using R” which was for me also a subject I would love to be present. The presentation from Neil discussed an important topic regarding the effect of increasing the amount of servers giving better throughput than expected on linearity, this phenomenon has been baptized by Neil as “Superlinear Scalability”. During the past couple of years he struggled to have his USL to fit with this phenomenon and at first he just ignored it, but after seeing the phenomenon more he had to admit the fact it really exists and his USL should be able to cope with it. After a long process he came to the conclusion that his USL is still able to apply if he would loosen the limitation of accepting negative numbers for his alpha parameter (Contention) in his USL. It basically means that by increased number of servers you get a kind of hybrid effect temporary (the throughput has increased with a factor more than expected on the number of added threads based on linear scalability). On a certain moment you still have to face the music and throughput degradation starts to appear due to coherency (Beta parameter in the USL formula). Based on the gathered proof, based on different data sets, he concluded that his USL still is valid, also in situations the “Superlinear Scalability” phenomenon is occurring. As usual Neil really showed in a very good scientific way that his claims were accurate and as he always says, “Models come from God and data comes from the devil!”. If you like to read more you can checkout his blog at: http://perfdynamics.blogspot.nl/2012/11/hotsos-2013-superlinear-scalability.html

Dr. N.J.G. Gunther at Hotsos 2013

After the presentation from Neil it was my turn to give my own presentation as I mentioned earlier, titled “”Method GAPP” Used to Mine OEM 12c Repository and AWR Data”. In my presentation I showed how Method-GAPP could use data from the Oracle Enterprise Manager 12c Repository to find out bottlenecks in your end-user process response times. OEM is most of the time very centrally placed in the system and so the repository contains almost all the input you need to use the method. In the method it is important to have your data synchronized at timestamps and basically this job is done for you by using the data from the OEM repository. In most of the Method-GAPP projects I have been the data collection and synchronization steps from the method took the most time, so by have this done by OEM you can gain a lot of valuable time in this kind of projects. Next to the usage of OEM I gave examples of the power of the method by making factorial analyses of AWR, SQL-statement response time data and AWR, event data. In the graph below is shown what Method-GAPP in base can do with the primary components, as defined in the method, in a very simplified way.

 Next to this Method-GAPP can use secondary components, as defined in the method, to find for example which SQL is involved in causing biggest amount of variance in the end user response time (as Cary said the variance is much more important as the mean). The picture below shows how the factorial analyses to explain the response time of “Warehouse Query”, which was using data of a test when different transactions where running are compared with the Top Activity SQL when a test was done with only the “Warehouse Query” running.

 The picture shows clearly that although the factorial analyses was done on data with a mixed workload of transactions, the factorial analyses still can find the SQL involved in one specific transaction. This shows how powerful the data mining actual is.

At the end of the presentation I got several questions from the audience locally present and from the audience remotely present. One of the questions I got a lot of times is what to use to get your end-user response time measurement. One of the tools I have seen to be very helpful with this is Oracle Real User Experience Insight (RUEI). The newest version has a very good integration with OEM 12c, what makes it even better to be used in Method-GAPP.

Based on the reactions I got from the audience and the people I spoke afterwards, like Neil Gunther and Cary Millsap, and of course my own feeling, I guess the presentation just contained too much information for one hour to explain the whole method in this detailed way. In the future I will break down my presentation into smaller presentations to only deal with a part of the whole method and be able to explain the steps in higher detail. The presentation can be found here, and the new white paper can be found here.

After my presentation I attended the presentation from Kellyn Pot’Vin titled “EM12c: Metric Extensions — Designing, Deploying and Dynamic Success”. In her presentation she showed how the new OEM 12c feature “Metric Extensions” could be used. Basically the “Metric Extensions” are replacing the good old “User Defined Metrics”. The big advantages of “Metric Extensions” over “User Defined Metrics” are:

• Library to store and view.
• Development cycle support, (dev., test, deploy)
• Versioning.
  

  Kellyn Pot’Vin presenting at Hotsos 2013

To get more information on the “Metric Extensions”, see also the Oracle documentation: http://docs.oracle.com/cd/E25178_01/doc.1111/e24473/metric_extension.htm#autoId0

After attending the presentation from Kellyn I attended the presentation from Kyle Hailey with his presentation titled “Database Virtualization Comes of Age”. In this presentation Kyle talked about Database Virtualization, which is a technology to reduce the amount of storage for (virtual) copies of the database. It basically only stores the original database and the changed blocks in comparison of the original database. In lots of organizations a lot of copies of the production database exist for test purposes. The database virtualization concept is basically used to help in this kind of situations. Kyle showed that CloneDB is a very cool tool to use, you can read more about it at: http://www.oracle-base.com/articles/11g/clonedb-11gr2.php

Kyle Hailey presenting at Hotsos 2013

After this long second day I had a nice talk with Kyle Hailey, Neil Gunther and Karl Arao. Karl explained about his extensive testing on some big systems. It was great to see that Karl gathered so much data from different systems, including Exadata systems. You can read more about it in the blog post “cores vs threads, v2 vs x2” at http://karlarao.tiddlyspot.com

Dr. Neil Gunther, Karl Arao and me

For the dinner that evening I was invited by Neil to join him for Japanese. The dinner was great and we had a good chance to catch up with each other again as old friends (Thanks again Neil for the invitation!).

After having dinner we peeked in into the on going party from Hotsos. Hotsos organized this time a kind of retro evening with all kinds of games. After the party we had with several other speakers a drink. It is always fun to be able to have fun and great discussion in the field with so many of you…

It is the first of March 2013 finally… I will travel to Dallas for (one of) the best Oracle performance symposia in the world, Hotsos 2013. The flight to get there will be from Amsterdam to Philadelphia and from Philadelphia to Dallas. Against all odds I will not travel alone but an old Amis colleague and friend Marco Gralike with his colleague will be on the same flight (even going back). After departure from Schiphol at 13:00 in the afternoon, and having our stop in Philadelphia we arrive at the Omni Mandalay Hotel in Las Colinas (Irving / Dallas) at 22:30 local time… This is the real start of an awesome time at this great symposium…

Hotsos 2013 a great start !

The weekend I work on my still not finished white paper what I have promised myself to release prior to my presentation at Tuesday 5 march… In the meanwhile I met and ate with old friends and it is great to meet so many of you again after a long time… At Sunday evening most of the attendees and speakers are in and including me, they collected their Hotsos batch…

Gerwin Hendriksen Speaker Batch Hotsos 2013

To have something different than other years… I had Hotsos placed the text as shown instead of my old text saying “Mind the GAPP !!”. Based on the reactions and the fact that on a lot of stations I got free advertisement the best one would be the old one…

First Day

Anyway Monday morning Hotsos starts with as keynote speaker Tom Kyte… At such moments I remember how each start of Hotsos is a very special event, and how this 2013 one can be extra special due to the big amount of Dutch speakers. Toon Koppelaars even proposed to Hotsos to create a special Dutch track next time… As some of the other speakers mentioned… we were part of the Dutch invasion this time, consisting of Toon Koppelaars, Frits Hoogland, Marco Gralike, Jacco Landlust and myself…

Tom Kyte speaking at Hotsos 2013 as keynote speaker

Every start of Hotsos makes you remind about the ones you did attend before and how the current symposium will give you new ideas and new knowledge. Hotsos is special due to its limited amount of just two tracks and the level of the presentations. The presentations are most of the time of deep technical level and are not sales oriented, so you hear the real story, the story that can help you do a better job…

Tom Kyte and all the Hotsos 2013 attendees

After the keynote, Tom Kyte gave a presentation called “Five Things You Want to Know “Go Faster” “. The important things I got from it are:

• Queue on your application server tier and not on your database tier. Tom Kyte gave in his presentation the link to the following Youtube video: http://www.youtube.com/watch?v=xNDnVOCdvQ0
• In general use especially bind variables in highly frequently executed queries and think about using literals in your queries if a lot of skew in your data is expected. By using in these cases no-binds the optimizer is better able to create a good plan due to the fact you actually give more data to the optimizer.

After the presentation the hall was split-up for the two tracks and the track I followed started with the first Dutch speaker Marco Gralike… Marco presented on of course XML-DB (see: http://www.liberidu.com/blog/2013/03/05/hotsos-2013-presentation-creating-structure-in-unstructured-data/). In the presentation Marco mentioned some XML-DB 12c features and syntaxes, which made the presentation extra refreshing.

Marco Gralike giving his XML-DB presentation at Hotsos 2013

At 12:00 noon we had lunch and at such times it is awesome to connect with new people and talk with old friends… I am sure this is one of the most important reasons it is so important to go to conferences in the first place. Learn something, bring something and make connections with people.

At 13:00 I went to a presentation of Cary Millsap, which is titled “Performance is a Feature: Here Is the Specification”. As usual Cary gave a mind tickling presentation and for me the highlights were:

• “Our customers feel the variance, not the mean”. Cary’s idea to deal with this phenomenon is to use quantile and not the mean. So it is of less importance to have a nice mean of the response time (R), it is more important to have an acceptable 95% quantile. This is something I am using a long time in my own performance related performance projects. The variance in the response time is the real problem within of course some boundaries. That is why my own method, method-GAPP is focusing on the variance of the end-users response time. This is the real pain the end-user is feeling.
• Start tracing individual users, by putting hooks in your application code and identify each transaction with its own unique experience id. In this way you can find specific patterns, which are present for a specific user or location. In a way it is a way to find skew and why the skew is there in your response-time data.

Cary Millsap getting questions after his presentation at Hotsos 2013

After Cary I attended the presentation from Alex Gorbachev called “Data Science for the Oracle DBA”. The presentation of Alex started with the story of the gift Cary gave to Alex, and due to this Alex asked Cary to come forward. At this point Alex gave Cary a wooden device to build histograms. To get a bit of the funny situation you can find a small movie below of the actual event.

Alex Gorbatchev giving Cary Millsap the wooden histogram builder at Hotsos 2013

Alex Gorbachev giving Cary Millsap the wooden histogram builder!

After the gift to Cary, Alex carried on with his presentation. In his presentation he talked about mean vs. median and some other useful statistical terminologies (see also the free courses on Http://Coursera.org). Further more he showed demos of Project-R visualization and Oracle Data Mining (ODM). I personal was surprised to see Alex doing some demoing on ODM. As you probably know I am using ODM inside Method-GAPP for Oracle oriented systems. It was really nice to see other people starting to see the great power of this Oracle software. I will try to post in the near future some post on Project-R used with Method-GAPP.

After the presentation from Alex I went to the presentation from the second Dutch man, Frits Hoogland. Frits presentation was titled “About Multiblock Reads”. In this presentation Frits showed in a very clear way, how multiblock reads actually are done in different versions of Oracle. The most important things for me were:

• strace lies ! I always thought that strace was showing every system call the process was doing, but after seeing the presentation and the discussion we had earlier at breakfast. I only can conclude that Frits is very right with his statements.
• The wait event for “Direct path read” does not show the actual time the read used. In real the direct path read is far more complicated and is basically putting I/O’s in flight. The wait event is reporting time only in some cases, in other cases it is not reporting at all and the wait event is not triggered at all. This makes the wait event not reliable to find out how many reads actually were done.
• To see in the strace where a wait event was starting it is calling Oracle kernel function “kslwtbctx” (begin), when the event is stopped it is calling Oracle kernel function “kslwtectx” (end).

Frits Hoogland giving his presentation at Hotsos 2013

In general I want to give compliments to Frits for his very in-depth presentation. It showed very good what is really happening and it gives a lot of ideas to think about. For me it was also nice to remember the old days when I was working for Oracle and I had to solve problems by using truss, strace, etc. like tools.

After the presentation from Frits I went to the presentation from Kyle Towle, with a presentation titled “Successful Performance Testing and Benchmarking Practices”. It is always nice to see how Kyle is dealing with the big systems he is working in at PayPal. Kyle gave a nice presentation on testing in this large kind of systems.

Kyle Towle giving his presentation at Hotsos 2013

At 18:00 the first Hotsos day was finished… a lot of information to deal with, but a satisfied feeling to be part of such a great day and able to see and hear the information from the first hand.

After a quick drink and having some food I went to my hotel room (the 27^th floor)…. enjoyed the view…

Nice evening view from the 27th floor – 1

nice evening view from the 27th floor – 2

…and started working on my white paper again, the next day I would present my own presentation called “”Method GAPP” Used to Mine OEM 12c Repository and AWR Data”…

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.

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.

Mining SQL information via Method-GAPP

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.

Wait events mined by Method-GAPP

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.

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

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

When busy modelling response times from end-user processes you need to take in account that the above fact is the case in real systems. In method-GAPP I am trying to map known queueing model curves to the data. For example the application server cpu metric can be modelled with an M/M/n queings model curve. So it could be very well possible that the application server has 8 cores available, but that in real the M6 curve will fit on average the best to the data during production hours, and the M2 curve will fit on average the best to the data during night hours. This difference in modelling of the data can be explained by the fact that certain cores are on average occupied by long running processes and not available to your OLTP business process, leaving you in effect with less cores than you think looking at the specs.

The great power of using data mining within Method-GAPP, makes it possible to calculate the best fitting average curve to each component in the architecture for a given time period. It stays very important to know your production system and to know when your production system has a comparable load profile. The better you can differentiate similar workload profiles for your analysis the better in the end the predicting curves and formula’s will be. Due to the fact that for the data mining a linear ridge regression model is used and that the queings models are known you are able to calculate easy what the effect will be if the application server now is modified from 8 cores to 16 cores. You can judge based on the new calculated model graph if an investment in the hardware will have the desired effect for your end-user performance.

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

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.

The code of the package looks like (erlangghh_pkg_v1_0):

CREATE OR REPLACE PACKAGE GHH_PKG AS
/* ==### Package GHH_PKG 12-10-2010 by G. Hendriksen v1.0 ###==
**
** Formula’s from book:
** “Analyzing Computer System Performance with Perl::PDQ”
** by Dr. Neil J. Gunther 2005
**
** Initial creation 10-10-2010 G.Hendriksen v0.1
** Updated formula 11-10-2010 G.Hendriksen v0.2
** Updated formula 12-10-2010 G.Hendriksen v1.0
*/

/* Function erlangb is used to get the Erlang-B (multiserver queueing system)
** probability that all the servers are busy and calls are dropped completely
** rather than being queued up. The function has input values number of servers
** (m) and utilization (p) — See pag. 83 formula 2.67
*/
function erlangb (m number, p number) return number;

/* Function erlangc is used to get the Erlang-C, this is the probability that
** all the servers are busy and therefore calls arriving causing utilization p
** will have to wait for service. The function has input values number of
** servers (m) and utilization (p) — See pag. 81 formula 2.66
*/
function erlangc (m number, p number) return number;

/* Function erlangr is used to calculate the exact multiserver residence time.
** The function has input values number of servers (m), utilization (p) and
** service time (s) — See pag. 81 formula 2.64
*/
function erlangr (m number, p number, s number) return number;

/* Function erlangq is used to calculate the queue length in a multiserver
** environment. The function has input values number of servers (m) and
** utilization (p) — See pag. 81 formula 2.65
*/
function erlangq (m number, p number) return number;

/* Function PDF is used to calculate the Discrete Probability Function of a
** Poison distribution with mean equal to the traffic intensity mp. The function
** has input values number of servers (m) and utilization (p) — See pag. 83
** formula 2.68
*/
function PDF (m number, p number) return number;

/* Function CDF is used to calculate the Cumulative Distriburion Function of a
** Poison distribution with mean equal to the traffic intensity mp. The function
** has input values number of servers (m) and utilization (p) — See pag. 83
** formula 2.68
*/
function CDF (m number, p number) return number;

/* Function PoisonR is used to calculate the Poison Ratio. The function has
** input values number of servers (m) and utilization (p) — See pag. 83
** formula 2.69
*/
function PoisonR (m number, p number) return number;

/* Function Utilp is used to calculate the utilization (p). The function has
** input values calls per time lambda (l), service time (s) and number of queues
** (q) — See pag. 74 formula 2.45
*/
function Utilp (l number, s number, q number) return number;

/* Function Paralqr is used to calculate the response time (r) for parallel
** queues, with utilization per queue. The function has input values
** utilization (p) and service time (s) — See pag. 75 formula 2.46
*/
function Paralqr (p number, s number) return number;

/* Function Paratqr is used to calculate the response time (r) for parallel
** queues, with utilization over all queues. The function has input values
** number of queues (q), utilization (p) and service time (s) — See pag. 75
** formula 2.46
*/
function Paratqr (q number, p number, s number) return number;

/* Function fact is used to get the factorial value with given number
*/
function fact (v number ) return number;

END GHH_PKG;
/

CREATE OR REPLACE PACKAGE BODY GHH_PKG AS
/* ==### Package GHH_PKG 12-10-2010 by G. Hendriksen v1.0 ###==
**
** Formula’s from book:
** “Analyzing Computer System Performance with Perl::PDQ”
** by Dr. Neil J. Gunther 2005
**
** Initial creation 10-10-2010 G.Hendriksen v0.1
** Updated formula 11-10-2010 G.Hendriksen v0.2
** Updated formula 12-10-2010 G.Hendriksen v1.0
*/

/* Function erlangb is used to get the Erlang-B (multiserver queueing system)
** probability that all the servers are busy and calls are dropped completely
** rather than being queued up. The function has input values number of servers
** (m) and utilization (p) — See pag. 83 formula 2.67
*/
function erlangb (m number, p number) return number AS
b number;
– Sigma inside formula is calculated here:
function sigm (v_m number, v_p number) return number AS
s number;
begin
s := 0;
for n in 0..(v_m)
loop
s := s + ( power((v_m*v_p),n)/fact(n) );
end loop;
return s;
end sigm;
BEGIN
b:=( power((m*p),m)/fact(m) ) / ( sigm(m,p) );
RETURN b;
END erlangb;

/* Function erlangc is used to get the Erlang-C, this is the probability that
** all the servers are busy and therefore calls arriving causing utilization p
** will have to wait for service. The function has input values number of
** servers (m) and utilization (p) — See pag. 81 formula 2.66
*/
function erlangc (m number, p number) return number AS
c number;
function sigm (v_m number, v_p number) return number AS
s number;
begin
s := 0;
for n in 0..(v_m-1)
loop
s := s + ( power((v_m*v_p),n)/fact(n) );
end loop;
return s;
end sigm;
BEGIN
c:=( power((m*p),m)/fact(m) ) /( ( (1-p)* sigm(m,p)) + ( (power((m*p),m)/fact(m)) ) );
RETURN c;
END erlangc;

/* Function erlangr is used to calculate the exact multiserver residence time.
** The function has input values number of servers (m), utilization (p) and
** service time (s) — See pag. 81 formula 2.64
*/
function erlangr (m number, p number, s number) return number AS
r number;
BEGIN
r := ( ( erlangc(m,p)*s ) / ( m*(1-p) ) ) + s;
RETURN r;
END erlangr;

/* Function erlangq is used to calculate the queue length in a multiserver
** environment. The function has input values number of servers (m) and
** utilization (p) — See pag. 81 formula 2.65
*/
function erlangq (m number, p number) return number AS
q number;
BEGIN
q := ( ( p*erlangc(m,p) ) / ( m*(1-p) ) ) + (m*p);
RETURN q;
END erlangq;

/* Function PDF is used to calculate the Discrete Probability Function of a
** Poison distribution with mean equal to the traffic intensity mp. The function
** has input values number of servers (m) and utilization (p) — See pag. 83
** formula 2.68
*/
function PDF (m number, p number) return number AS
pdf number;
BEGIN
pdf := ( exp(-m*p)* ( power((m*p),m) / fact(m) ) );
RETURN pdf;
END pdf;

/* Function CDF is used to calculate the Cumulative Distriburion Function of a
** Poison distribution with mean equal to the traffic intensity mp. The function
** has input values number of servers (m) and utilization (p) — See pag. 83
** formula 2.68
*/
function CDF (m number, p number) return number AS
cdf number;
function sigm (v_m number, v_p number) return number AS
s number;
begin
s := 0;
for n in 0..(v_m)
loop
s := s + ( power((v_m*v_p),n)/fact(n) );
end loop;
return s;
end sigm;
BEGIN
cdf := ( exp(-m*p)*(sigm(m,p)) );
RETURN cdf;
END CDF;

/* Function PoisonR is used to calculate the Poison Ratio. The function has
** input values number of servers (m) and utilization (p) — See pag. 83
** formula 2.69
*/
function PoisonR (m number, p number) return number AS
r number;
BEGIN
r := ( 1 – (pdf(m,p)/cdf(m,p)) );
RETURN r;
END PoisonR;

/* Function Utilp is used to calculate the utilization (p). The function has
** input values calls per time lambda (l), service time (s) and number of queues
** (q) — See pag. 74 formula 2.45
*/
function Utilp (l number, s number, q number) return number AS
p number;
BEGIN
p := ( (l*s)/q );
RETURN p;
END Utilp;

/* Function Paralqr is used to calculate the response time (r) for parallel
** queues, with utilization per queue. The function has input values
** utilization (p) and service time (s) — See pag. 75 formula 2.46
*/
function Paralqr (p number, s number) return number AS
r number;
BEGIN
r := ( s / (1-p) );
RETURN r;
END Paralqr;

/* Function Paratqr is used to calculate the response time (r) for parallel
** queues, with utilization over all queues. The function has input values
** number of queues (q), utilization (p) and service time (s) — See pag. 75
** formula 2.46
*/
function Paratqr (q number, p number, s number) return number AS
r number;
BEGIN
r := ( s / (1-(p/q)) );
RETURN r;
END Paratqr;

/* Function fact is used to get the factorial value with given number
*/
function fact (v number ) return number AS
min1 number;
prod number;
begin
min1 := v – 1;
if ( min1 > 0 )
then
prod := v*fact(min1);
return prod;
end if;
– important correction factorial value of zero is one.
if v=0 then return 1;
else return v;
end if;
END fact;

END GHH_PKG;
/

All the formula’s have been checked and the output is provide in this excel file (erlangpacktest) . When checking the output of the functions, the response time in multiple servers situation will look like the picture:

I hope I made some people happy by programming the functions in PLSQL. If something is wrong with them, please comment on the blog or of course for any suggestion. I will probably enhance the package in future, if so I will keep you posted.

Regards, Gerwin

After the keynote Tom Kyte had a talk on “All about Metadata”. Tom Kyte told about how wrong metadata in the database is influencing the performance. So actual explaining the different reasons the performance can be wrong only due to wrong metadata or the lack of it in the database. It is important to feed the database with data you know of the data, because you know your database the best. That can mean adding dimensions, adding materialized views, etc. His most important points were, “Datatypes Count”, “Proper Lengths matter”, “Constraints are important” and “Metadata Matters”, so ensure they are right and there, and the optimizer can do its job the best way possible.

Tom Kyte’s talk was followed up by a talk from Richard Foote. His talk subject was “Oracle Indexing Myths”. When he started his presentation he seemed to be a big fan from “David Bowie” and “Radio Head”, from who he used a lot of times, names to refer to database objects, like table “BOWIE”. Richard told about a lot of different index myths and some of them where quite interesting. The myths he talked about were:
•    Deleted Index Space Is Deadwood
•    Indexes Always Prevent Sort Operations
•    CBO won’t consider an index when retrieving > x% of rows
•    Put Most Discriminating Column 1st In Concatenated Index
•    Small tables Don’t Benefit From Being Indexed
•    Bitmap Indexes Help With Low Cardinality Conditions
•    Bitmap Indexes Inefficient With High Cardinality Columns

Just to give a small impression I will give a few of the answers. In general we can say that deleted index space will be reused, so the idea of ending up with a lot of deadwood in our indexes is really a myth. For indexes and sorts it is always important to realize that “nulls” in columns are not put in the index and so having the index is not always a guarantee that the sorting will actually happen. For indexes it is important to know that the clustering factor is highly involved in choosing an index for an execution plan. Also the amount of rows which will be expected to be retrieved from the index in compare with the table are from influence. So will the index actually been used or not?

The myth of putting the most discriminating column first in a concatenated index, looks very challenging, but in fact it is really a myth. I was even a bit surprised to find this out, so why was my second thought. From base the most discriminating column should be first because of the to be accessed leaf blocks in the index are less, but in fact this doesn’t matter. So if another column is first in the index it will not influence the amount of the to be accessed leaf blocks. Richard showed with some small test cases that it really doesn’t matter, index performance stayed the same. Ok, this said what will happen if we place the most discriminating column at the first, it will actually give problems, like the “Index skip scan”, it will work only efficient as a little distinctive column is first in the index, instead of the other way around. Another index option like index compression will only work as the little distinctive column is first. This has to do with the way an index compression works.

This was just a few examples of the myth coverage Richard did. I think it is very good before starting designing index to check out the work Richard did in this area.

After having had lunch together and making new contacts the presentation room was split in half for two tracks of presentations. I had the choose to go to Alex Gorbachev with “Battle Against any Guess” or Dan Norris with “Consolidation Strategies for Oracle Database Machine”. I choose for Dan and got a good impression on how the Exadata was operating within the database machine. Also things like Oracle I/O Resource Manager and Oracle Database Resource Manager were discussed. After the presentation from Dan I had a small discussion with Christian Antognini, regarding the way a “smart scan” was operating. In my opinion the database is sending a “Bloom Filter” signature to the disk controllers were with the “Bloom Filter” a lot of the not interesting data is pre-filtered before sending it to the database instance. The sent data to the database instance will not be a normal database block format, so it will pass the database buffer cache. The passing of the database buffer cache was also mentioned by Dan in his presentation.

My next presentation I attended was done by Doug Burns with “How I Learned to Love Pictures- Oracle 10g/11g Performance Analysis Using OEM”. Doug tried to do a lot of demos, but due Murphy’s Law he had a lot of issues giving them. To do the load testings on his laptop system he used “Swingbench” as the load-tool. The demo showed how you can use in a very clear way Oracle Enterprise Manager (Grid). Doug showed that the first “Performace Home” screen was based on measured data and the “Top Activity” screen was based on sampled data (ASH). Although a lot of the demos were able to be shown Doug really tried hard to give the message, and in my opinion he did.

Next stop was Kevin Closson with “Ten Years After Y2K, and We Still ‘Party Like It’s 1999’”. Kevin showed a lot of development over the past ten years. He explained about how the techniques over the years have changed and how bottlenecks changed for cpu, memory and I/O. He talked about experiences with Oracle Database File System (DBFS) and how he actually had installed on this file system a complete database including Oracle Home. So he built a database in a database, from a geek point of view like me that is pretty cool. While presenting he had a little buddy with him, the pics will show you how…

The final presentation of the day was for me Kerry Osborne with “Controlling Execution Plans (Without Touching the Code)”. In his presentation he talked about the possibilities of influencing the execution plan of a query, without actual change the query itself. A suggestion could be changing database parameters, well this can be seen as “Big Knob Tuning” and would be very unadvisable.  Some other ideas are start adding new access paths, or remove some, in plain Oracle language this means add indexes or remove some. The other idea could be by start changing stats on objects by the DBMS_STATS package. Well all these suggestion will not be very specific and a lot of different queries are influenced. Lucky Oracle did some other things too. Kerry told that Oracle made three ways, Outlines, Sql profiles and Sql Baselines. These three ways where created with different goals by Oracle, but do all basically the same, with some small twists. The use of outlines was a half baked product, one of the issues was to set every time “use_stored_outlines” to true, with every restart of the database instance. SQL profiles were three quarter baked but are not perfect either, one of the advantages over outlines is the fact that they can be applied to multiple statements. The SQL Tuning Advisor (STA) Profiles (SQL Baselines), are still not perfect but do multiple things for the stability, first of all they can add hints and second they can store metadata for the query to give the optimizer a better understanding of the data and data distribution.

The way Kerry showed the possibilities were very nice and a very good closing of HOTSOS day 1.

Regards,

Gerwin