Re: Queueing Theory in Oracle
Date: Thu, 13 Mar 2014 15:38:05 -0500
Message-ID: <CAMNhnU2o3VkXpFA=XXkCt7xxcFezZnc0oQ-6FS1pyAJ1EexmWA_at_mail.gmail.com>
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_at_gmail.com> 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
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 13 2014 - 21:38:05 CET