On Apr 23, 3:53=A0pm, kerravon <kerra...@[EMAIL PROTECTED]
> wrote:
> We have an insert/update transaction that normally takes 0.2 seconds
> but
> sometimes takes as long as 16 seconds. =A0The granularity of statspack
> is 30
> minutes and that is too long to see if there was anything causing
> delays.
>
> Is there a way of getting Oracle to re****t a reason why any query
> takes
> more than 1 second?
>
> Thanks. =A0Paul.
Sybrand gave the correct response to find what a particular problem
really is.
You can also perhaps get some clues from various tools in OEM,
particularly the lock and wait screens, and some clues from views like
(select view_name from dba_views where view_name like '%WAIT%' or
view_name like '%LOCK%';)
Many things can cause a large variance in a particular queries'
response time, and they are often something outside of that particular
sessions control. Some possibilities are things like:
Something actually overloads your I/O, such as what happens in a
misconfigured or overloaded system doing a log switch - when you can't
finish switching logs quickly, the db stops until it can finish the
switch. This can also happen for various reasons with a SAN,
including some resilvering operations, and sometimes people don't even
think about what traffic is going through their controllers or
network. Is there any pattern to the slowdowns (yes, I understand
trying to determine that would be why you'd ask about the 1 second
thing, but have you noticed anything)? Anything in the alert log?
The session wants something that something else has. This can be due
to multiuser issues, such as everyone wants to update the same block
in memory. It can be due to locking. Much of this is usually
application design or programming issues.
Sometimes there are simple answers, such as your buffers are getting
thrashed by certain processes, and separating some things into their
own buffers can make all of them more orderly. Google v$bh for
that.
A common cause of your type of problem is a batch process that
periodically updates transaction tables. At an extreme, they can pile
up on each other.
It may be worth a gander at an explain plan - could the plan be way
off for certain uses of the code?
There are bugs and misfeatures, that's why it is usually a good reason
to state the exact version (like 9.2.0.4) and the exact OS and
hardware you are using. Sometimes everyone just goes "oh, you should
do such-and-such because of this reason." Sometimes there are myths,
too, so show proper scepticism of those kinds of claims. If it is
entirely on the Solaris side, Oracle may not know much about that. Do
you see anything like swapping or some high priority process taking
over all the cpu's?
Statspacks, traces, knowledge of how your app works and
instrumentation you put in the code are tools you use to point your
quest in the proper direction. There are a few methodologies to
choose from, a lot of people like method-r. There are just too many
things happening to generically spit out a reason a query takes more
than an arbitrary time - there may be more than one reason, it may be
normal, it may be stupid...
So, even if the granularity of the statspack is large, there may be a
clue in the waits there. The trace file may tell you you are waiting
on disk I/O or some such. You still need to figure out what is going
on, because you are still smarter than the db.
Sometimes a problem is obscure, like something causing a procedure or
everything to hard parse. Are you seeing anything else besides this
one problem?
jg
--
@[EMAIL PROTECTED]
is bogus.
http://www.theopenforce.com/2008/04/burning-the-boa.html
|
