Re: Transaction table- Keep Pool
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 5 Aug 2004 15:06:30 +0100
On Thu, 05 Aug 2004 09:08:05 -0400, jaysingh1@xxxxxxxxxxxxx
<jaysingh1@xxxxxxxxxxxxx> wrote:
> Hi All,
>
> Few queries are taking longer time (40 sec =10*normal time) to execute in
> intermittent manner.For example, 99 percent of the time it got executed
> within 3 to 4 secs and 1 percent of the time it is taking more than 35 secs.
> (35 secs is the application timeout limit)
>
> What we are think is that if the requested data block is not in DB buffer
> cache(flushed out by someother query/data) it is going for physical read and
> that may be the reason for longer execution time.
Why do you think this? 30seconds is a *lot* of elapsed time, I can do
many thousands of disk reads in 30 seconds - especially if those disk
reads come from some cache somewhere in the system. I'd be strongly
tempted to trace the session that is experiencing the problem, or at
least query v$session_wait for that sid whilst you are experiencing
the hang.
One reason for this sort of behaviour *may* be that you are using bind
variables and the execution plan chosen is excellent for all but a
very small number of values for one of the bind vars.
One reason may be that something else is happening on the box at the same time.
Another reason might be indeed that you are reading from disk, and
that disk is swamped.
Unless you have diagnosis rather than symptoms applying a cure might
be rather unhelpful
> Is it okay to assign tansactions tables to KEEP POOL?
Yes, but it would be somewhat unusual.
--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
