Re: Transaction table- Keep Pool

On Thu, 05 Aug 2004 09:08:05 -0400, jaysingh1_at_optonline.net <jaysingh1_at_optonline.net> 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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------