Re: oracle 9 on solaris 8 - exception reporting

On Apr 23, 3:53 pm, kerravon <kerra...@xxxxx> wrote:
We have an insert/update transaction that normally takes 0.2 seconds
but
sometimes takes as long as 16 seconds.  The 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 report a reason why any query
takes
more than 1 second?

Thanks.  Paul.

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
--
@home.com is bogus.
http://www.theopenforce.com/2008/04/burning-the-boa.html
.

Relevant Pages

  • Re: gottlieb 77 pop bumpers not "retracting" all the time
    ... Raise the field, lean it on the backbox and pull the ... could the mylar be causing the problem? ... i cant help but think that for some ... reason the coil is registering the pull down, ...
    (rec.games.pinball)
  • Re: Failure of Pics.
    ... environment. ... a mini board with a pic, on a rotating item of a machine. ... We are still looking at the reason why. ... I believe it maybe gravity waves that's causing the problem. ...
    (sci.electronics.design)
  • Re: Date last Played
    ... There's something strange going on with WMP11's date calculations, ... but whatever is causing it probably has good reason). ... I thought the date-wandering was fixed in WMP11 beta? ...
    (microsoft.public.windowsmedia.player)
  • Re: error on windows xp home startup
    ... turned off - just no reason apart from that they are loading by themselves. ... could these be causing the problems? ... > Sharpvision simply the best http://www.seedsv.com ...
    (microsoft.public.windowsxp.basics)
  • Re: WorldCon 2006 (was: The Mohammud Cartoons)
    ... A great deal of silliness that amply demonstrates that one reason he ... got dumped by his former wife (causing him to have a large and ...
    (rec.arts.sf.fandom)