Little's Law example: Oracle shared pool

Objective

This post illustrates Little's Law using example data from a production Oracle system. The goal is to demonstrate how easy it is to use and interpret. In fact, it is so easy that it seems more like intuition than a Law, and is easy to overlook its usefulness.

Little's Law

Little's Law expresses a simple relationship between request arrival rate, residence time and the number of requests either in service or in a queue. Whenever you know two of these quantities, Little's Law allows you to calculate the third one.  Little's Law only holds for averages in a stable system.

Little's Law is intuitive and easy to understand. It can be applied to many situations, and provides an easy and powerful sanity check for many database performance metrics. To illustrate this law, I show an example using Oracle's shared pool and query hard parse rate.

For more information, see Dr. Neil Gunther's The Practical Performance Analyst (Author's Choice Press 2000, p. 44)  or Craig Shallahamers's Forecasting Oracle Performance (Apress 2007, p. 99).

The long-term average number of customers in a stable system L is equal to the long-term average arrival rate, λ, multiplied by the long-term average time a customer spends in the system, W, or:

L = λW

Oracle shared pool

Remember that an Oracle hard parse converts a string of SQL text into a new executable cursor and puts it into the cache of cursors in the shared pool. Consider the following substitutions to Little's Law above as we apply it to cursors in the Oracle shared pool.

Little's Law	Oracle
customer	cursor
system	shared pool
long-term average number of customers in a stable system L	number of cursors already in the shared pool plus those getting hard parsed right now
long-term average arrival rate, λ	hard parse rate
long-term average time a customer spends in the system, W	average cursor retention time in shared pool

We can ignore the number of cursors "getting hard parsed right now" since that number will be so much smaller than the number of cursors already in the shared pool. With this simplification, Little's Law when applied to Oracle hard parsing becomes the following:

The average number of cursors in the shared pool is equal to the average hard parse rate multiplied by the average time a cursor spends in the shared pool.

Assume that we want to estimate the average retention time of a parsed SQL statement in the shared pool. Oracle's instrumentation makes it easy to measure both the rate of hard parsing and the count of cursors in the shared pool.

If we rearrange Little's Law, we get:

W = L/λ

which means that:

mean retention time in shared pool = (number of cursors in the shared pool)/(hard parse rate)

Using values of 5,000 cursors (rows in V$SQL) and 100 hard parses per second, we get

mean retention time in shared pool = (5000)/(100 hard parses per second)

mean retention time in shared pool = 50 seconds

Q-Q plots to examine SQL execution time distributions

Introduction

The purpose of this post is to introduce the use of Q-Q plots for comparing distributions of sampled observational data to standard, reference distributions. For example, we often assume that our sampled observations are normally distributed. A Q-Q plot can quickly show whether this assumption is justified. A Q-Q plot can be used with other distributions beside normal. For example, both log-normal and Poisson distributions are demonstrated below. I also show an estimated density plot, which is similar to a histogram, but is "more robust and easier to read" (R in a Nutshell, Joseph Adler, O'Reilly Media 2009, p.238).

Performance data gathered from a production Oracle database are used to illustrate the technique. Since these data fit none of these standard distributions, the Q-Q plots all look rather strange. To illustrate a Q-Q plot for a good fit, I generate random log-normal data, and compare them to normal and log normal distributions.

A secondary purpose of this post is to show how Q-Q plots can be generated quickly and easily using the R programming language, which is free and open source. The R programming language has become the lingua franca of data science and statistics.

The data and R code I used for this analysis are available for download (links below).

Q-Q Plot Overview

The "Q" in Q-Q plot comes from "quantile." You are already familiar with quantiles if you understand medians, quartiles, and percentiles. Assume you have a large set of X values. The median is the specific value of X where half of the other values are less that the median value, and half are greater. Quartiles are similar: three-fourths of the X values are less than the third quartile. Of course, percentiles are the same: only ten percent of the students score greater that the 90th percentile.

The term "quantile" simply generalizes this concept to an arbitrary number of partitions, rather than just two,four, or one hundred partitions assumed by medians, quartiles, and percentiles.

To generate a Q-Q plot, you first divide the sampled observational data into k quantiles. To compare these to a known, reference distribution, you also divide it into k quantiles. You then work your way through each value of k, plotting each quantile value of the observational data as the x-coordinate, and the corresponding reference distribution quantile as the y-coordinate. 

Bottom line: If the distributions are the same, you will end up with a straight line. Deviations from a straight line suggest differences between the sampled and reference distributions. 

A common practice is to standardize each dataset by subtracting its mean and dividing by its standard deviations (i.e., "z score"). With this transformation, the straight line would have a slope of one and pass through the origin; this standardization can simplify the plot.

Much more thorough discussion, and more complete advice on distributional analysis in R is available at the following links. If you really want to learn this stuff, you should read these sources and not this blog!

• Bill Huber: http://www.quantdec.com/envstats/misc/Fitting data to distributions.pdf
• Vito Ricci: http://cran.r-project.org/doc/contrib/Ricci-distributions-en.pdf

as linked from here:

• http://www.statmethods.net/advgraphs/probability.html

In the R programming language, Q-Q plots are prepared using functions like qqplot() and qqnorm(). In Mathematica, they are prepared using QuantilePlot[]

Data source

Earlier this year, Craig Shallahamer blogged about distributions of elapsed times of SQL query executions in the Oracle Database:

• http://shallahamer-orapub.blogspot.com/2011/02/sql-statement-elapsed-times.html
• http://shallahamer-orapub.blogspot.com/2011/02/sql-arrival-patterns-and-impact.html
• http://shallahamer-orapub.blogspot.com/2011/02/important-statistical.html

Craig noted the difficulty of comparing distributions by visually examining histograms: "Just because a data set visually looks very much like another data set or statistical distribution, does not mean it statistically matches...be careful." I completely agree, and my goal here is to show how distribution differences are much easier to see with Q-Q plots that they are with histograms.

For the analysis below, I used one of Craig's datasets, which he refers to as "Garr 8q"and it is in file Garret8qtkxy0g5d1p3_1.txt within his AnalysisPack.zip, I have have included a copy of these data for download. Here is Craig's histogram:

Unusual distributions common in Oracle database studies

As a side note, many data distributions observed in Oracle database performance studies are highly skewed or bimodal, obviously not normally distributed. In fact, most of the interesting Oracle database performance problems I encounter are in large part caused by strange distributions. Weird distributions are a fact of life for the database professional. I chose this particular dataset since it was less pathological than the others in Craig's study. 

By the way, I am ignoring here the basic question: why should I care about the distribution of SQL query execution times? See Craig's posts for his discussion on this topic. Bill Huber addresses this general question in detail in his PDF presentation.

Results - actual database performance data

I encourage you to look at the R source code to see how easy it is to do this type of analysis. However, I will not include this code within the text of this post.

Craig concluded that his example SQL query execution elapsed time data did not seem to be coming from normal, log-normal, or Poisson distributions. I agree. Let's first look at the estimated density function,which is similar to a histogram.

The next plot (below) is a normal Q-Q plot. We want to graphically compare the sample quantiles to the expected quantiles. If samples were taken from a normal distribution, the points would line up with a slope of 1 and and intercept of zero. Significant deviations from this line indicate a lack of fit to the normal distribution. We see significant deviations below, so we discard the notion that the samples came from a normal distribution. 

Lets try a log transform of these data to see if they came from a log-normal distribution . We again see significant deviations from a straight line (below), so these data were not sampled from a log-normal distribution.

We now compare the Oracle SQL elapsed time data to a randomly generated Poisson distribution. Once again, we see significant deviations from the line, so we conclude that the observations were not sampled from a Poisson distribution.

Results - randomly generated data

The above Q-Q plots are so completely different from straight line good fits. So let's see a Q-Q plot for a good fit. I generated some random log-normal data to illustrate a good fit, and here is what the distribution looks like.

But first, let's compare these random log-normal data to a normal distribution.  Again, not a good fit, as expected:

Now let's take the log() of the  random log-normal data and compare the transformed data to a normal distribution. This is the near perfect fit we expect.

Conclusions

I have demonstrated the use of Q-Q plots to compare the distribution of sampled observations to known distributions. Deviations between sample data distributions and three reference distributions were obvious. The deviations were easier to see in a Q-Q plot that in histograms. Q-Q plots can be generated quickly and easily using the R programming language, which is free and open source. 

Code and data

• Actual data, R source code only: Garret8q.QQ-plot.R.txt
• Actual data, R source, data, and output: Garret8q.QQ-plot.tar
• Random log normal data, R source code only: log-normal.QQ-plot.R.txt
• Random log normal data, R source and output: log-normal.QQ-plot.tar

My first issue of the NoCOUG Journal as Editor

I am thrilled and honored to take over the reins of the NoCOUG Journal! 

My first issue as Editor was just published and can be downloaded from the NoCOUG website at http://www.nocoug.org/Journal/NoCOUG_Journal_201105.pdf. It kicks off a new series of short articles called "Ask the Oracle ACEs" where you can read diverse advice from several experts in response to a single question. The question for this issue was intentionally vague: "Why is my database slow?" This issue also continues regular features such as an interviews and book reviews. For this May 2011 issue, our interview is with Greg Rahn, a database performance engineer in the Real-World Performance group at Oracle Corporation, and Brian Hitchcock reviews the new book "Achieving Extreme Performance with Oracle Exadata" by Rick Greenwald, et al.

I especially want to thank Iggy Fernandez who has been the editor for the last five years. He set very high standards, and I'll be working very hard to follow in his footsteps. NoCOUG's speakers, authors, and vendors have given us all a tremendous amount of practical skills and real-world advice for the last 25 years. Their generosity and commitment have benefited us all. Even the end users of our databases are happier because of the skills that have been passed on to us! I am convinced that sharing and collaboration are the keys to professional growth and success. With this inspiration in mind, I'll do my best to ensure that this Journal continues to be a vital source of knowledge.

Also, the NoCOUG spring conference is this Thursday, May 19 at the Oracle Corporation in Redwood Shores. See the agenda at http://nocoug.org/next.html. Presentation materials will be available for download at http://www.nocoug.org/presentations.html.

Referential integrity and optimizer shortcuts

I overheard again just the other day that database performance can be improved by eliminating foreign keys and doing integrity constraint checking in the application. This fallacy is based on the notion that there must be some database overhead for enforcing foreign key constraints, so the database will always be faster if it can avoid this overhead. In fact, Oracle's foreign key implementation is very efficient, and in real world cases, the overhead of foreign keys is negligible, and can sometimes even provide slightly faster DML (see, for example, Tom Kyte).

Moreover, referential integrity can provide huge performance gains in situations where Oracle's optimizer can use it to devise better execution plans. I stumbled upon an example of this recently in a production system and thought I'd share it.

The query below involves five tables related by foreign keys. The joins follow these foreign keys.

select count(*)
 from goats g,
      properties p,
      doelings d,
      goat_versions v,
      farm_animals f
where g.type_id = :1
  and g.property_id = p.property_id
  and g.goat_id = d.doeling_id
  and d.status = 'alive'
  and g.goat_id = v.alive_id
  and f.animal_id = g.goat_id
;

The execution plan with predicates is shown below. In this farm animal database, index names start with their table name prefix. Please do not spend too much time studying this plan; I'll walk you through the important parts below.

-----------------------------------------------------------------------
| ID  | OPERATION                       | NAME                        |
-----------------------------------------------------------------------
|   1 |  SORT AGGREGATE                 |                             |
|   2 |   NESTED LOOPS                  |                             |
|   3 |    NESTED LOOPS                 |                             |
|   4 |     NESTED LOOPS                |                             |
|*  5 |      TABLE ACCESS BY INDEX ROWID| GOATS                       |
|*  6 |       INDEX RANGE SCAN          | GOATS_TYPE_IDX              |
|*  7 |      INDEX UNIQUE SCAN          | GOAT_VERSIONS_ALIVE_ID_UNIQ |
|*  8 |     INDEX UNIQUE SCAN           | FARM_ANIMALS_PK             |
|*  9 |    TABLE ACCESS BY INDEX ROWID  | DOELINGS                    |
|* 10 |     INDEX UNIQUE SCAN           | DOELINGS_DOELING_ID_PK      |
-----------------------------------------------------------------------

PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------

   5 - FILTER("G"."PROPERTY_ID" IS NOT NULL)
   6 - ACCESS("G"."TYPE_ID"=:1)
   7 - ACCESS("G"."GOAT_ID"="V"."ALIVE_ID")
       FILTER("V"."ALIVE_ID" IS NOT NULL)
   8 - ACCESS("G"."GOAT_ID"="F"."ANIMAL_ID")
   9 - filter("D"."STATUS"='alive')
  10 - ACCESS("G"."GOAT_ID"="D"."DOELING_ID")

First, notice how the PROPERTIES table (P) is not accessed, nor or any of its indexes! Second, notice how the predicate for operation #5 (g.property_id is not null) is not in the original SQL above; the Oracle optimizer added it on its own!

What is going on?

The Oracle optimizer knows that the PROPERTY_ID column in child table GOATS (G) is a foreign key to parent table PROPERTIES (P). The query SQL text includes the join condition "G.PROPERTY_ID = P.PROPERTY_ID." But due to referential integrity, all values of G.PROPERTY_ID must also exist in P.PROPERTY_ID. Therefore, if G.PROPERTY_ID is not null, then this join condition must be satisfied.

The Oracle optimizer has used this knowledge to eliminate the need to access parent table PROPERTIES (P) or any of its indexes. This provides a significant cost savings. This shortcut would not have been available if referential integrity had been enforced by the application.

A small data mining library

In my quest to learn data mining, I have studied others' book reviews in order to assemble a small data mining library. I share the results of this quest in this blog post. I've already finished reading some of these books, am studying others, and will probably never finish some of them. Enjoy!

This list is divided into several sections: 

• R
• Data Mining
• Regression, ANOVA, and Statistics
• Applications

R

R in a Nutshell (Joseph Adler. O'Reilly 2010). A basic, general introduction to the R language. It is comprehensive, but necessarily thin in spots. For example, it devotes over one hundred pages to graphics, but less than a page to support vector machines. Those new to R should probably start with this book.

Data Manipulation with R (Phil Spector. Springer 2008). One the beautiful things about R is how it handles datasets. This book explains all that you need to know about "data frames", and goes into detail about how to use "factors," how to access databases, etc. We often spend more time managing data than analyzing it, and this book show you how to use R effectively. I especially like the lessons on R's "melt" and "cast" functions: they let you "denormalize" data, like you can now do with Oracle 11g's pivot() function.

Modern Applied Statistics with S (W.N. Venables, B.D. Ripley. Springer 2010). This essential book focuses on practical use of R to analyze data. It includes some statistical theory, but only just enough to provide context and clarity. It emphasizes practical examples, and has a very wide scope.

Statistical Models in S (J. M. Chambers, T.J. Hastie. Chapman and Hall/CRC 1991) This reference book on statistical modeling goes into much more implementation detail that Venables and Ripley (above). It provides a firm foundation for understanding R functions and models.

Data Mining

All of Statistics: A Concise Course in Statistical Inference (Larry Wasserman. Springer 2010). This book provides a crash course on statistics. It starts with a basic definition of probability, where the basic problem is "given a data generating process, what are the properties of the outcomes?" contrasted with inference: "given a set of outcomes, what can we say about the process that generated the data?" It provides a firm mathematical foundation: for example it clearly defines samples spaces, random variables, cumulative distribution functions (CDF), and probability density functions (PDF). The reader is assumed to be very comfortable with calculus and linear algebra, and is expected to follow proofs of its many theorems. Bibliographic notes are provided, as well as homework exercises.

The Elements of Statistical Learning: Data Mining, Inference, and Prediction (Trevor Hastie, Robert Tibshirani and Jerome Friedman. Springer 2009). A complete guide to data mining techniques, with eighteen chapters and seven hundred pages. The first several chapters are considered essential, while the rest can be read independently. It includes computational considerations and extensive bibliographies. Used as the textbook for Machine Learning 201 and 202 taught at the Hacker Dojo.

Data Mining: Practical Machine Learning Tools and Techniques (Ian Witten. Morgan Kaufmann 2011). A basic introduction to data mining. It uses Weka rather than R.

Introduction to Data Mining (Pang-Ning Tan, Michael Steinbach, Vipin Kumar. Addison Wesley 2005). A very accessible, yet comprehensive introduction to data mining. It requires only a minimal mathematical background, and includes data pre- and post-processing. Used as the textbook for Machine Learning 101 and 102 taught at the Hacker Dojo.

Regression, ANOVA, Statistics

Applying Regression and Correlation: A Guide for Students and Researchers (Dr. Jeremy Miles, Dr. Mark Shevlin. Sage 2000). A lovely introduction to regression, targeted to the non-mathematician. I enjoy how it starts by showing how the mean is a least-squares model: the mean minimizes the sum of squared residuals. Starting with the mean and progressing to simple linear regression makes it easier to understand multiple regression. This approach also makes it easier to see analysis of variance (ANOVA) as a form of regression.

Nonlinear Regression with R (Christian Ritz, Jens Carl Streibig. Springer 2008). This book focuses on nonlinear regression, going into deep practical detail about the R functions nls() and model diagnostics. It is not a book on theory. It is designed for self study for those familiar with, but not yet experts in regression and R.

Statistical Methods, 8th Edition (George Snedecor, William Cochran. Iowa State University Press 1989). A classic, covering the basics from a very practical perspective.

Analysis of Variance (Henry Scheffé. Wiley 1959). A complete mathematical foundation for the analysis of variance; another classic. It assumed the reader is comfortable with calculus and linear algebra. It is designed for self-study.

Applications

Profiles in Performance: Business Intelligence Journeys and the Roadmap for Change (Howard Dresner, Wiley 2009). While not a book on data mining per se, it outlines the organizational issues necessary for success with business intelligence technologies. He defines four levels of organizational maturity as measured by six "performance-directed culture criteria." These include "transparency and accountability", and "availability and currency of information." The book is based on very detailed, insightful, and inspiring case studies.

Smart Enough Systems: How to Deliver Competitive Advantage by Automating Hidden Decisions

(James Taylor, Neil Raden. Prentice Hall 2007), This book focuses on the small descions made every day by an organization, such as those made by front-line customer service representatives. While each of these decissins may be very small, their cumulative effect is greater than the strategic descions that get most upper management attention. The author advocates for "enterprise decision management" to optimize precision, consistency, agility, and speed of these routine operational decisions. This can be accomplished by making suitable predictive models easily available and automatic.

PMML in Action (Alex Guazzelli, Wen-Ching Lin, Tridivesh Jena. CreateSpace 2010), The Predictive Model Markup Language (PMML) is an industry standard way of sharing predictive models between software applications. This book provides very practical advice for using and understanding PMML illustrated with thorough examples.

Response time analysis based on AWR data

Summary

The basic idea here was to do a database CPU response time analysis using a whole month's worth of hourly observations of a busy production system. Craig Shallahamer provides an online tool that does a curve-fit of a single observation to the basic response time formula (http://filezone.orapub.com/cgi-bin/msolve.cgi). However, it seems like a better model might be obtained if one could do the curve-fitting with hundreds of observations. For fun, I did the analysis twice: once in using R (http://www.r-project.org/), and once using Mathematica (http://www.wolfram.com/); source code and example data for each program are available for download below. I developed an excellent response time model, and had a few other insights:

• Observational periods where the CPU utilization is much higher than predicted by a linear regression of utilization versus workload (i.e., outliers) are very interesting from a trouble shooting perspective. This suggests the possibility of monitoring for such outliers.
• Observational periods where the response time is much higher than predicted based on comparable workload are very interesting from a trouble shooting perspective. Again, this suggests the possibility of monitoring for such outliers.

Data source, metrics, and model

I used hourly snapshots of V$SYSSTAT data: foreground CPU utilization, DB time, buffer gets and user calls. I gathered these data for daytime, weekdays only, one a busy OLTP-like production system. I computed deltas for each metric, so these deltas represent hourly averages. I could have written my own tool to gather these data, but found it more convenient to simply use AWR's snapshots. My query is available for download (below); it uses subquery factoring for easy review and maintenance, the analytic function LEAD() to compute deltas without a self-join, and the max-decode denormalization trick.

I modeled this 4-CPU Oracle database as follows:

• workload = buffer gets per millisecond
• service time = milliseconds of foreground CPU per buffer get
• response time = millisecond of DB time per buffer get

Utilization as function of workload

We expect CPU utilization to be a linear function of workload (Gunther 1998/2000 p. 51, Shallahamer 2007 p. 26, Shallahamer 2010 p. 51). As shown below most of the observations fit very well to a linear regression. Yet we do have some apparent outliers, above and to the right.

Outliers - linear model - practical diagnostic value

Although the above scatter plot visually suggests the presence of outliers, it would be nice to have an objective, quantitative approach to identifying them. A common solution is to use a histogram of the residuals (Miles 2009 p. 88, http://www.jeremymiles.co.uk/regressionbook/extras/appendix2/R/).

The outliers are over on the right side of the histogram, with residuals above about 8 or so. The remaining residuals have a nice, normal-like distribution, as expected.

One of the beautiful and convenient features of the R language is the ability to easily combine regression residuals with the raw data, which allows them to be easily managed and reviewed. These steps were much more cumbersome in Mathematica or Excel. My R source code, available for download below, demonstrates this technique in detail. Here is how this was done with a single R command:

db9.c.cleaned <- subset( cbind(db9.b.milliseconds, residuals(db9.b.milliseconds.lm)),

                         residuals(db9.b.milliseconds.lm) < 8.0 )

I want to emphasize that I did not simply remove these outliers so that the rest of the analysis would have prettier, better fitting lines. Instead, these outliers were important from a diagnostic and database management perspective. A review of database activity during the hours represented by the outliers revealed that bulk operations intended to run at night were mistakenly running during business hours. These bulk operations required more CPU time per buffer get than typical for business hour activity. The reason for this difference was not investigated in detail, yet this regression analysis brought it to our attention so we can now manage it. This insight had escaped identification via routine database monitoring. This discovery opens up new ways of monitoring database health!

Not shown here, but available for download, are the regression model, plots, and residual histograms after removal of outliers. As you can imagine, the regression was quite tight: R-squared was 0.945, and the residual histogram was very normal looking.

Response and service time scatter plot

I show below a scatter plot of response time (i.e., circles, DB time) and service time (i.e., crosses, foreground CPU time) as a function of workload (i.e., buffer gets per millisecond), after removal of the above outliers. 

As expected, the service time was nearly constant with workload. This constancy of service time is related to the linear relationship between CPU utilization and workload. If service time per buffer get was not constant, then we would not expect utilization to be linear.

This plot also shows apparent response time outliers, hours with much more wait time than the workload would suggest. As with the utilization outliers above, I objectively identified them by reviewing a histogram of residuals from a regression (although I cheated by using a linear rather than non-linear regression, details in downloadable source code). And as with the utilization outliers above, these had significant diagnostic value. We had occasional trouble with excessive non-idle waits that stand out much more dramatically and obviously than with conventional database monitoring. Again, this discovery opens up yet another potential new way of monitoring database health!

Response time model - nonlinear regression

This database system can be modeled as a single queue (i.e., the CPU run queue) with multiple "servers" (i.e., CPUs).  An approximate formula for this model is shown below (Gunther 1998/2000 p. 62, equation 2-36, Shallahamer 2007 p. 26, Shallahamer 2010 p. 51).

I was able to simplify this model further, by assuming a constant value of service time, using its average value of 0.009295 milliseconds per buffer get. My goal was to solve for the number of effective "servers" on this 4-CPU database host. I gave this formula to R's nonlinear regression function as shown below (download source code to see in it context).

db9.f.cleaned.nls <- nls(DB_TIME_MSPMS/BUFFER_GETS_PMS~0.009295/(1-(((0.009295*BUFFER_GETS_PMS)/num.servers)^num.servers)),

   data = db9.f.cleaned,

   start=list(num.servers=2.5),

   trace=TRUE)

Results and conclusion

A plot of the fitted model is shown below; a pretty good fit was obtained. As you can see, this system was often running pretty close to the "knee in the curve." Little excess CPU capacity was available, and a CPU upgrade was in order (SQL tuning and workload reduction techniques had already been exhausted). 

Regarding CPU upgrades, a question naturally arises: is it better to add more CPUs, or to increase the speed of the CPUs? The following Mathematica plot compares these two alternatives, assuming that we can either double the number of (effective) CPUs or double their speed (i.e., halve their service time).

Now that the CPUs have been upgraded on this host, a future blog post will compare these predictions to post-upgrade observations. Future posts will also describe the results of practical database monitoring based on finding regression model outliers. 

Downloads

AWR SQL only (txt)

R source code only (txt)

R source code, plots, SQL, and data (zip)

Mathematica notebook and data (zip)

Mathematica PDF (pdf)

References

Gunther, N. J. (1998, 2000). The Practical Performance Expert. Lincoln, NE: Author's Chioce Press (originally published by McGraw Hill).

Miles, J and Shevlin S. (2001, ... 2009). Applying Regression and Correlation. London, Thousand Oaks CA: SAGE publications. See also http://www.jeremymiles.co.uk/regressionbook/extras/appendix2/R/

Shallahamer, C. (2007). Forecasting Oracle Performance. Berkeley, CA: Apress (distributed by Springer-Verlag).

Shallahamer, C. (2010). Orapub's 2020 Performance Seminar, Northern California Oracle Users' Group (NoCOUG) Training Day, August 18 2010.

Institute of Arctic Biology at the University of Alaska Fairbanks. R tutorial, especially non-linear regression: http://mercury.bio.uaf.edu/mercury/R/R.html#nonlinear

Variance to Mean Ratio for finding brief 'cursor: pin S' spikes

Tanel Poder has a recent blog posting that describes technical details about 'cursor: pin S' waits. He mentions "if your problem is measured in few seconds, then your performance data should also be sampled at least every few seconds in order to be fully systematic." In Tanel's example, his "20 minute AWR reports still showed IO wait times as main DB time consumers, but that was averaged over 20 minutes. But [Tanel's] problem happened severely and shortly within few seconds in that 20 minutes, so the averaging and aggregation over long period of time did hide the extreme performance issue that happened in a very short time." I show below a method for quickly finding and characterizing such short but severe problems.

Averaged metrics - AAS 
Most database monitoring systems rely upon averaging some metric of a period of time, or its equivalent of calculating the difference in some metric over a time interval. If the duration of the problem is significantly less that the monitoring time interval, the problem might not be detected.

The Average Active Session metric (AAS) is a very useful general-purpose performance metric. It is an estimate of the number of sessions that are on the CPU, or are in active wait states, as averaged over a specific time interval. In general, if the AAS greatly exceeds the number of database host CPUs, users will experience response time delays. I have previously detailed AAS definition and derivation.

Since AAS is based on overall user response time, it can detect a wide variety of problems, regardless of wait event. However, as an average, it has very limited ability to detect response time spikes that are much shorter than its averaging period.

Variance and Variance to Mean Ratio
As often explained by Robyn Sands, the statistical concept of variance can help to detect a metric's brief deviations from typical values, especially when divided by the average to form the Variance to Mean Ratio (VMR). The example below illustrates this technique with a previously described SQL script for hourly AAS. Previous experience with this particular database showed that user response time would suffer at an AAS above twenty. Looking only at hourly AAS in the example below, it would seem that no response time problems were present. However, by including VMR, a problem around 14:00 is quite evident.

SAMPLE_HOUR         AAS      VMR-AAS
----------------- ----- ------------
2008-04-16 07:00    1.8          0.6
2008-04-16 08:00    2.3          1.0
2008-04-16 09:00    2.8          1.3
2008-04-16 10:00    3.2          2.3
2008-04-16 11:00    4.1          2.3
2008-04-16 12:00    3.0          1.1
2008-04-16 13:00    2.9          1.0
2008-04-16 14:00    6.4         95.4   <== spike in variance
2008-04-16 15:00    3.8          1.9
2008-04-16 16:00    3.6          1.6
2008-04-16 17:00    2.7          0.9
2008-04-16 18:00    2.7          2.6

The problematic hour of 14:00 can be examined in more detail by averaging over one minute rather than one hour, using a previously described SQL script. The very brief spike at around 14:08 is very clearly visible. User response time suffered greatly during this brief spike.

SAMPLE_MINUTE       AAS  VMR-AAS
---------------- ------ --------
2008-04-16 13:54    4.0        0
2008-04-16 13:55    3.5      0.7
2008-04-16 13:56    4.5      3.4
2008-04-16 13:57    4.7      0.7
2008-04-16 13:58    7.3      1.6
2008-04-16 13:59    3.8      0.2
2008-04-16 14:00    8.8      1.8
2008-04-16 14:01   12.8      0.5
2008-04-16 14:02    4.2      0.5
2008-04-16 14:03    3.8      1.5
2008-04-16 14:04    4.5      1.3
2008-04-16 14:05    8.8      2.1
2008-04-16 14:06    8.0      1.1
2008-04-16 14:07    7.8      3.7
2008-04-16 14:08  130.3      170  <== spike in AAS
2008-04-16 14:09    7.3     10.3
2008-04-16 14:10    3.0      0.8
2008-04-16 14:11    4.3      1.1
2008-04-16 14:12    6.5      1.6
2008-04-16 14:13    3.3      0.7
2008-04-16 14:14    2.5      0.6
2008-04-16 14:15    5.3      2.2
2008-04-16 14:16    4.2      0.4
2008-04-16 14:17    5.2      1.9

Find brief spikes over a long period
 The following chart shows how the above SQL script for hourly AAS can be used to examine a full month's data to quickly find very brief ASS spikes. None of these spikes were apparent in the hourly AAS data itself, but the variance to mean ratio shows these spikes very dramatically. By the way, each of these spikes turned out to be a 'cursor: pin S' wait pile-up, lasting only a minute or two each, and including up to several hundred sessions.

One-second resolution 
To examine brief ASS spikes like the above in more detail, I would query one-second resolution Active Session History (ASH) data, mapping over each EVENT name to custom categories. In the example below, EVENT 'cursor: pin S' was mapped over to category "MUTEX1", while 'cursor: pin S wait on X' was mapped to "MUTEX2". The count of sessions in each category was then counted within each one second ASH observation sample. These time-series data provided clues about the evolution of this event over its brief one-minute duration. These clues would not have been visible in a simple aggregation over this interval; a high-resolution time-series view was essential for troubleshooting.

Conclusion
Using the Variance-to-Mean Ratio (VMR) for the Average Active Session (AAS) metric allows quick and reliable identification of brief spikes, lasting one minute or less. Such spikes are invisible using only averages.