Re: Transaction table- Keep Pool
- From: jaysingh1@xxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 05 Aug 2004 10:21:57 -0400
Hi Niall,
I did SQL trace. I see almost 20,000 lines of WAIT event repeated with
'db file sequential read'
'rdbms ipc reply'
'global cache freelist wait'
but most of them with elapsed time ZERO.
BTW., we use raw device, 8i 2 node OPS.
WAIT #3: nam='db file sequential read' ela= 1 p1=7 p2=42790 p3=1
WAIT #3: nam='rdbms ipc reply' ela= 0 p1=33 p2=120 p3=0
WAIT #3: nam='global cache freelist wait' ela= 0 p1=0 p2=0 p3=0
WAIT #3: nam='db file sequential read' ela= 1 p1=11 p2=538089 p3=1
WAIT #3: nam='rdbms ipc reply' ela= 0 p1=33 p2=120 p3=0
WAIT #3: nam='global cache freelist wait' ela= 0 p1=0 p2=0 p3=0
WAIT #3: nam='db file sequential read' ela= 1 p1=11 p2=538234 p3=1
WAIT #3: nam='rdbms ipc reply' ela= 0 p1=33 p2=120 p3=0
WAIT #3: nam='global cache freelist wait' ela= 0 p1=0 p2=0 p3=0
WAIT #3: nam='db file sequential read' ela= 0 p1=7 p2=834182 p3=1
WAIT #3: nam='rdbms ipc reply' ela= 0 p1=33 p2=120 p3=0
WAIT #3: nam='global cache freelist wait' ela= 0 p1=0 p2=0 p3=0
WAIT #3: nam='global cache cr request' ela= 0 p1=7 p2=42807 p3=39341
WAIT #3: nam='db file sequential read' ela= 0 p1=7 p2=42807 p3=1
WAIT #3: nam='rdbms ipc reply' ela= 0 p1=33 p2=120 p3=0
WAIT #3: nam='global cache freelist wait' ela= 0 p1=0 p2=0 p3=0
WAIT #3: nam='db file sequential read' ela= 0 p1=11 p2=538218 p3=1
WAIT #3: nam='db file sequential read' ela= 1 p1=7 p2=833709 p3=1
WAIT #3: nam='rdbms ipc reply' ela= 0 p1=33 p2=120 p3=0
WAIT #3: nam='global cache freelist wait' ela= 1 p1=0 p2=0 p3=0
Thanks
Sami
----- Original Message -----
From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
Date: Thursday, August 5, 2004 10:06 am
Subject: Re: Transaction table- Keep Pool
> 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
> -----------------------------------------------------------------
>
----------------------------------------------------------------
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:
