Re: Queueing Theory in Oracle
- From: Ls Cheng <exriscer@xxxxxxxxx>
- To: Henry Poras <hrp@xxxxxxxxxx>
- Date: Fri, 14 Mar 2014 20:20:59 +0100
Just downloaded them Henry!
Thanks, will have a long read this weekend
On Fri, Mar 14, 2014 at 5:15 PM, Henry Poras <hrp@xxxxxxxxxx> wrote:
> Thanks to Iggy for posting these. All comments are welcome.
>
> Applying Queueing Theory Analysis to Oracle Statspack Data
> (2009)<http://www.nocoug.org/download/misc/Poras_StatspackQueueingTheory_2009.pdf>
>
> Determining Resource Utilization and Saturation Limits Using AWR history
> and Queueing Theory
> (2010)<http://www.nocoug.org/download/misc/Poras_DeterminingResourceUtilizationAndSaturationLimitsUsingAWRHistoryAndQueueingTheory_2010.pdf>
>
> Determining Resource Utilization and Saturation Limits in a Multi-User,
> Mixed Workload Environment (or When Shouldn't I Rely Only on Elapsed
> Time?)<http://www.nocoug.org/download/misc/Poras_DeterminingResourceUtilizationAndSaturationLimitsinaMulti-UserMixedWorkloadEnv_2011.pdf>
>
>
> "Queueing Theory and Oracle: Interesting? Scary? Somewhere on my list of
> things to
> look at? As is probably the case for a lot of people here, my first
> exposure to this field
> came from Cary Millsap"s book Optimizing Oracle Performance Tuning
> (Chapter 9).
> Intrigued, I followed his advice and read some of the authors he
> referenced. Ultimately,
> though, I always felt like I was missing something. The techniques and
> theory seemed
> powerful, but how could I apply this to my database backed applications?"
>
> "My goal is to determine resource utilization needs and chokepoints within
> a system. We
> will look at resource demands as a function of time and of load. Part of
> the analysis will
> be to determine the bottleneck, the first resource to saturate. We will
> see if the bottleneck
> is the database and if so, where in the database. We can experimentally
> determine, for
> example, the number of disks needed to achieve necessary throughput
> levels, as well as
> the load at which CPU will saturate."
>
> Hope this adds at least something to the discussion.
>
> Henry
>
>
>
>
>
>
>
>
> On Fri, Mar 14, 2014 at 8:38 AM, Patterson, Joel <jpatterson@xxxxxxxxxx>wrote:
>
>> Thanks to Stéphane Faroult:
>>
>>
>>
>> Craig Shallahammer :-)
>>
>>
>>
>> *From:* Patterson, Joel
>> *Sent:* Friday, March 14, 2014 8:08 AM
>> *To:* 'exriscer@xxxxxxxxx'; Ethan Post
>> *Cc:* Oracle Mailinglist
>> *Subject:* RE: Queueing Theory in Oracle
>>
>>
>>
>> One of the issues I have is that the machines I work on are sharing CPU
>> amongst multiple database, along with storage, and memory. There are
>> multiple ways to break that up including virtualization.
>>
>>
>>
>> I read a book by schalenheimer on predicting performance, (see
>> disclaimer), and it was devoted to one database on one server. Sure, if
>> you can get it.
>>
>>
>>
>> Disclaimer:
>>
>> (I know I have the spelling wrong, and I even googled it, but it is so
>> wrong I'm not getting any hits, but people on this list will probably
>> recognize who I'm trying to reference).
>>
>>
>>
>> *From:* oracle-l-bounce@xxxxxxxxxxxxx [
>> mailto:oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx>] *On
>> Behalf Of *Ls Cheng
>> *Sent:* Friday, March 14, 2014 5:44 AM
>> *To:* Ethan Post
>> *Cc:* Oracle Mailinglist
>> *Subject:* Re: Queueing Theory in Oracle
>>
>>
>>
>> Hi
>>
>> To explain something we need a solid fundation, to get some meaningful
>> output.
>>
>> To get the meaningful output we need to get some proper data input and
>> that is where I have doubts, what data can we use as input? Which database
>> metrics could be used and if they could be used are they exponentially
>> distributed? If not we arent suppose to use queueing theory because it is
>> based on exponentially distributed data.
>>
>> We could use user transactions per sec for example but I am not sure if
>> it is always directly proportional to cpu usage, if transaction arrival
>> rate is up 10% cpu usage is up 1%, if transaction rate is 20% up then CPU
>> is up 0.2%. Not sure if I have explained. The good metric which I think is
>> directly related to the CPU usage is logical reads per sec, more logical
>> reads more cpu usage, it has direct relationship, user transaction for
>> instance doesnt always have that direct relationship because we can have
>> different type of transactions, say T1, T2, T3, if T1 will have 50% more
>> arrival rate it doesnt mean CPU usage would be higher, it may be simply 50
>> commits more, but logical reads is different. But can we use logical reads
>> as arrival rate?
>>
>> Thanks
>>
>>
>>
>> On Thu, Mar 13, 2014 at 9:38 PM, Ethan Post <post.ethan@xxxxxxxxx> wrote:
>>
>> IMHO the practical use of Q'ing theory is to explain to IT managers why
>> system performance goes from wonderful to full suck when CPU is 80-90
>> utilization. When they say, yeah but we still have 10% left you tell them
>> to go Google Q'ing theory, beyond that I see better ways to answer your
>> question, which leads me to ask what is the question your trying to answer?
>> Taking a guess that you want to know when service times aren't par?
>>
>> Seems to me in 90% of the cases the issue, if in the database, is going
>> to be reflected in your SQL's and it will effect most SQL's, i.e. the
>> average elapsed times will increase for a larger group of SQL's. I keep a
>> table which groups SQL performance in hourly buckets for SQL's which
>> actually do something, either high execute or high elapsed times, doesn't
>> take up much space and I can go back 1 plus year. The breakout is also in a
>> histogram sort of fashion so I can see how often it executed under 1 sec,
>> 1-5 secs and so on....very easy to query, very easy to quickly determine if
>> avg elapsed times are not the norm, very easy to show history. I know the
>> same sort of stuff is stored in oracle tables someplace and you could also
>> run scheduled checks from ASH against history to look for >N SQL's with
>> service times >X avg or what have you. Using ASH also makes it easy to
>> filter by program, module, client_id and such, since in most cases when you
>> are interested in this sort of detail you are interesting in certain
>> programs/apps/transactions.
>>
>> Just thinking this as I type here, but I am always interested in better
>> diagnosing app server and web server performance from the interaction with
>> the database, just thought that when the app server is sick it would
>> usually be reflected in time between executes or SQL*Net waits. So take for
>> example an app which executes SQL foo 1000 times per hour, but that may all
>> occur in a ten minute period, let's say all of a sudden the SQL's still
>> execute just as fast, but it takes 20 minutes to run the 1000 executes
>> because of the latency on or between the app server, well the avg time
>> between executes would increase, if this occurred across multiple app
>> server related sessions you likely have some sort of latency issue on the
>> app server or web server.
>>
>> Fun thinking about this stuff.
>>
>> - Ethan Post
>>
>>
>>
>>
>>
>> On Tue, Mar 11, 2014 at 8:57 AM, Ls Cheng <exriscer@xxxxxxxxx> wrote:
>>
>> Hi all
>>
>> The question is probably a bit vaue but has anyone succesfully applied
>> Queueing Theory in an Oracle Database using database metrics such as
>> Logical Reads, User Calls, Executes? (Not application metrics such as
>> business transaction per sec, queries run per second etc.) I wonder if
>> these metrics can be used in Queueing Theory since they dont look like
>> following exponential distribution which is a requirement in M/M/n.
>>
>> Thanks
>>
>>
>>
>>
>>
>> --
>> *Joel Patterson*
>> *Sr. Database Administrator** |* Enterprise Integration
>> Phone: 904-928-2790 | Fax: 904-733-4916
>> www.entint.com
>>
>> <http://www.entint.com/>
>>
>> [image:
>> http://www.facebook.com/pages/Enterprise-Integration/212351215444231]<http://www.facebook.com/pages/Enterprise-Integration/212351215444231>
>> [image: http://twitter.com/#!/entint] <http://twitter.com/#!/entint> [image:
>> http://www.linkedin.com/company/18276?trk=tyah]<http://www.linkedin.com/company/18276?trk=tyah>
>> [image:
>> http://www.youtube.com/user/ValueofIT]<http://www.youtube.com/user/ValueofIT>
>>
>> This message (and any associated files) is intended only for the use
>> of the addressee and may contain information that is confidential,
>> subject to copyright or constitutes a trade secret. If you are not the
>> intended recipient, you are hereby notified that any dissemination,
>> copying or distribution of this message, or files associated with this
>> message, is strictly prohibited. If you have received this message in
>> error, please notify us immediately by replying to the message and
>> deleting it from your computer. Messages sent to and from us may be
>> monitored. Any views or opinions presented are solely those of the
>> author and do not necessarily represent those of the company. [v.1.1]
>>
>
>
Other related posts:
