Re: Queueing Theory in Oracle

In theory we can obtain Service Time from this formula, (little's law)

system utilization = (arrival rate * service time) / number of servers

Queue time (Qt) is

N = Number of servers
p = system utilization
C = ErlangC
s = Service Time

Then Response Time as we all know is Service Time + Queue Time

But I havent get there yet because I a still seeing how can these formulas be used in Oracle

On Tue, Mar 11, 2014 at 8:43 PM, Martin Berger <martin.a.berger_at_gmail.com>wrote:

> 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.
>
> hth,
> Martin
>
> [1]
> http://www.perfdynamics.com/Manifesto/USLscalability.html
> http://www.perfdynamics.com/iBook/gcap.html
>
> http://aberdave.blogspot.co.at/2011/08/universal-scalability-law-usl-in-oracle.html
>
>
>
>
>
>
> On Tue, Mar 11, 2014 at 7:34 PM, Stéphane Faroult <sfaroult_at_roughsea.com>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 <http://www.roughsea.com>
>> Konagora <http://www.konagora.com>
>> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
>> Author, SQL Success<http://www.amazon.com/SQL-Success-Database-Programming-Proficiency/dp/1909765007/>,
>> The Art of SQL<http://www.amazon.com/Art-SQL-Stephane-Faroult/dp/0596008945/>,
>> Refactoring SQL Applications<http://www.amazon.com/Refactoring-SQL-Applications-Stephane-Faroult/dp/0596514972/>
>>
>>
>> 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 <karlarao_at_gmail.com> wrote:
>>
>>> I've read both of the books, and I love them. On this link are my
>>> notes - http://goo.gl/b8aNOj
>>> 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 http://www.slideshare.net/karlarao/where-didmycpugo 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 <exriscer_at_gmail.com> 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 <
>>>> jonathan_at_jlcomp.demon.co.uk> 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
>>>>> http://jonathanlewis.wordpress.com
>>>>> _at_jloracle
>>>>> ------------------------------
>>>>> *From:* oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org]
>>>>> on behalf of Ls Cheng [exriscer_at_gmail.com]
>>>>> *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 <
>>>>> Paul.Houghton_at_admin.cam.ac.uk> wrote:
>>>>>
>>>>>> Craig Shallahamer talks about queuing theory in the following blog
>>>>>> post.
>>>>>>
>>>>>>
>>>>>> http://shallahamer-orapub.blogspot.co.uk/2011/08/why-tuning-oracle-works-and-modeling-it.html
>>>>>>
>>>>>> I hope this is helpful
>>>>>>
>>>>>> PaulH
>>>>>>
>>>>>>

--
http://www.freelists.org/webpage/oracle-l