Re: Queueing Theory in Oracle

From: Martin Berger <>
Date: Tue, 11 Mar 2014 20:43:16 +0100
Message-ID: <>

I think, there is a big difference between a simple M/M/m model and Oracle RDBMS:
In Queueing theory there is a number of queues where requests wait, and a number of processors where they are served afterwards. - Oracle is quite different: requests never (yes, seldom they do) queue, they start processing when arrived. So I don't think the whole DB can be modelled as a simple M/M/m system. But when you start to modell sub-queues and sub-processors in the DB, it's getting tremendous complex. Also all metrics in the DB are an aggregate of ServiceTime+QueueTime - "ON CPU" does not tell us if the process is really doing something in the CPU, or in an OS-runqueue, or even on SWAP. Maybe some wait events about latches/mutex/... can be seen as pure QueueTime, but even there the corresponding ServiceTime is not available easily.

In the past I used Neil Gunthers "Universal Scalability Law" [1] to predict performance of databases (and even sub components) with acceptable precision. Maybe this helps more than pure M/M/m queues.



On Tue, Mar 11, 2014 at 7:34 PM, Stéphane Faroult <>wrote:

> Well, I think that you can use database metrics, it's just a question of
> making reasonable assumptions that may or may not apply to your case. In a
> queuing system you basically have servers that provide a service, which
> requires some time to be provided, and clients that ask for that service
> and arrive at some rate, according to what is usually assumed to follow
> Poisson's law (in simple terms, the interval between two requests is
> random). Trouble starts when requests arrive faster than they can be
> serviced, because then queues build up. As Jonathan stated, all this
> started to be seriously studied with telephone exchanges (which were
> Erlang's object of study).
> If your system is mostly CPU-bound, your servers are your processors. CPU
> time (better than DB time in this specific context IMHO) gives you a global
> time of service, and number of executions the number of requests you got.
> Related wait times can probably help refine the picture. Of course you need
> to work on averages, which may or may not be severely misleading. If,
> however, the workload has specific patterns at different times of the day,
> you may probably be able to predict when you'll certainly have issues. Just
> take your results for what they will be, guidelines and warning flashes.
> Avoid numerical results with four decimals.
> I have never had the opportunity to work seriously on this, but I
> certainly have been tempted.
> HTH,
> --
> Stéphane Faroult
> RoughSea Ltd <>
> Konagora <>
> RoughSea Channel on Youtube <>
> Author, SQL Success<>,
> The Art of SQL<>,
> Refactoring SQL Applications<>
> On 03/11/2014 05:03 PM, Ls Cheng wrote:
> Hi Karl
> So I guess you agree that Queueing Theory is not applicable using
> database metrics, which is basically I am looking for
> I've got the statistics without tears book as well, it is good about
> normal distribution.... :-)
> On Tue, Mar 11, 2014 at 4:51 PM, Karl Arao <> wrote:
>> I've read both of the books, and I love them. On this link are my notes
>> -
>> Although queueing theory is pretty cool, I find the chapter on regression
>> analysis more suited for real world but the queueing theory chapter builds
>> a nice foundation of ResponseTime=ServiceTime+QueueTime which is from a
>> practical use case point of view the Performance Page you've got all of
>> that components in a nice pretty dashboard broken down into wait class with
>> CPU as your service time and everything else is QueueTime.
>> For the regression analysis on the link (r2project), what I did is rank
>> the independent values (x) that has the high correlation coefficient and
>> make use of that to forecast the dependent value (y). Also the page 8 of
>> this paper also
>> gives you more insights about the "more CPUs, faster CPUs, more and faster
>> CPUs" section of the book, I think it's page 32.
>> -Karl
>> On Tue, Mar 11, 2014 at 9:46 AM, Ls Cheng <> wrote:
>>> Hi
>>> I have been reading Craig's book and Cary's book (chapter 9) for the
>>> last 2 weeks, the theory in the books look great but when I tried to start
>>> using in the real world it the questions started to appear.
>>> For example, arrival rate, what arrival rate in Oracle is exponentially
>>> distributed......? Cary says logical reads in his book but I just dont see
>>> how can that be possible by using the database metric (for example system
>>> metric such as Logical Reads Per Sec or Logical Reads Per User Call).
>>> Craig's book I dont even mention a useful database metric (I havent
>>> finished the book yet so I might have missed if he has said so), the book
>>> just uses all the time the work unit transaction per second.
>>> Both book provide a queueing theory workbook but they are useless from
>>> database metric point of view since no metric is poisson or exponential
>>> distributed (again I am not able to see it, if someone can please advice).
>>> But Jonathan you just mention "buffer gets per user call" which is
>>> similar to Logical Reads Per User Call from v$sysmetric, why do you think
>>> that is exponentially distributed :-?
>>> Thanks
>>> On Tue, Mar 11, 2014 at 3:30 PM, Jonathan Lewis <
>>>> wrote:
>>>> It's an interesting question - and I don't think you can find a
>>>> current metric that would help unless you started doing something a little
>>>> clever with ASH.
>>>> In an OLTP system something like 'buffer gets per user call" would
>>>> probably be a reasonable fit - but there's no capture at that granularity.
>>>> Similarly disc I/O requests per call might be appropriate. Then there are
>>>> things like disk I/O requests per disc per second. But every possibility I
>>>> think of requires too fine a level of granularity unless you can find a way
>>>> to construct a valid model from the samples in v$active_session_history.
>>>> Regards
>>>> Jonathan Lewis
>>>> _at_jloracle
>>>> ------------------------------
>>>> *From:* []
>>>> on behalf of Ls Cheng []
>>>> *Sent:* 11 March 2014 14:20
>>>> *To:* Paul Houghton
>>>> *Cc:* Oracle Mailinglist
>>>> *Subject:* Re: Queueing Theory in Oracle
>>>> Hi
>>>> I have had a quick read, I think the link you posted talks about queue
>>>> time but not about queueing theory such as a M/M/n model. The problem is I
>>>> am not able to find a database metric that is exponential distributed which
>>>> allows us to use the M/M/n queueing theory.
>>>> Thanks
>>>> On Tue, Mar 11, 2014 at 3:16 PM, Paul Houghton <
>>>>> wrote:
>>>>> Craig Shallahamer talks about queuing theory in the following blog
>>>>> post.
>>>>> I hope this is helpful
>>>>> PaulH

Received on Tue Mar 11 2014 - 20:43:16 CET

Original text of this message