Hmm... Oracle Database Users Experience The Average SQL Elapsed Time
You're joking. Right?
Oracle Database performance tuning and analysis has come a long way in the last 20 years. First there was the "just add more resources" approach and tuning the blatantly poor SQL. Then there was ratio analysis, followed by wait event analysis, time based analysis, and unit of work time based analysis. In addition to the performance diagnosis and analysis evolution, the Oracle Database as a product has changed, and architectures are more diverse. Yet with all this change, some things are in many ways timeless. They relate to complexity, basic mathematical statistics, efficiency, and doctrinal purity. Over the next few weeks, I'll post four different "myths." Last week I posted about increased complexity. This posting centers on a user's experience.
The second myth is users experience the average SQL elapsed time. Concurrency, multiple execution plans, different bind variables, and the current cache situation make the average elapsed time less relevant-and perhaps even misleading. Let me explain.
If I tell someone the average elapsed time for their key SQL statement is 10 seconds, 99.9% of the time they will picture in their minds a bell curve. They will think to themselves, "OK. This means that usually the statement runs for about 10 seconds, sometimes less and sometimes more." Unless verified, my research clearly shows that there is likely to be a significant difference between the average and the typical elapsed time(s). This means we are missetting expectations and flat-out misleading users. Not the place we want to be!
It is very simple to calculate the average SQL statement elapsed time. Even a Statspack report will show you the total elapsed time and the total number of executions over the report interval. The average elapsed time is simply the total elapsed time divided by the total number of executions. So it's very enticing to make a quick statement about the elapsed time using the average.
Now suppose this SQL statement has two execution plans: one typically completes in 2 seconds and the other completes in 18 seconds. Now also assume that they both get executed the same number of times (don't count on this in your environment!). The average elapsed time would then be 10 seconds. Now I picture myself telling my client that I had discovered the key SQL statement and its average elapsed time is 10 seconds. The "2 seconds" group would think I'm making the situation look worse than it is so I can claim an amazing performance improvement. The "12 seconds" group would think I'm simply an idiot. Either way, I lose. So I needed a way to find out the truth.
What I learned through my research is that if the statement is important to the users and I care about not misleading them, I need to collect some data, calculate the statistics, and create a histogram. I even blogged about the problem HERE and provided solutions here!
My research clearly showed three reliable ways to collect elapsed times: Oracle tracing by SQL_ID, instrumenting application code, and sampling running SQL. I have created a low overhead tool called "SQL Sampler" to sample SQL statement elapsed times. You can download it for free from my website HERE. The moral of this story is do not expect users to experience average elapsed times, and if appropriate, provide details about the typical elapsed times.
Thanks for reading!
If you have any questions or comments, feel free to email me directly at craig at orapub.com.
|Tool Options: Detailing Oracle Database Process CPU Consumption||Oracle Database Buffer Cache Visualization And Tool||The OS CPU Run Queue; Not What It Appears|