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