Re: Queueing Theory in Oracle
- From: Ethan Post <post.ethan@xxxxxxxxx>
- To: Ls Cheng <exriscer@xxxxxxxxx>
- Date: Thu, 13 Mar 2014 15:38:05 -0500
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
>
>
>
Other related posts:
