Re: oracle 9 on solaris 8 - exception reporting

On Apr 23, 3:53 pm, kerravon <kerra..._at_w3.to> 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