Hi Martin 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 Qt = (Cs / N(1-p)) + s 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@xxxxxxxxx>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@xxxxxxxxxxxx>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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxxx> 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 >>>>> @jloracle >>>>> ------------------------------ >>>>> *From:* oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] >>>>> on behalf of Ls Cheng [exriscer@xxxxxxxxx] >>>>> *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@xxxxxxxxxxxxxxx> 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 >>>>>> >>>>>>