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