Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: Transaction table- Keep Pool

RE: RE: Transaction table- Keep Pool

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Thu, 5 Aug 2004 09:17:25 -0500
Message-ID: <0186754BC82DD511B5C600B0D0AAC4D60E9AE693@EXCHMN3>


Sami

   The sentence that grabs my attention is "While reading the query is getting timedout." I think Niall has posted some very useful suggestions. First diagnose exactly what is happening, then worry about fixes like the KEEP pool. My guess is that the process is wrapped up doing a whole lot of logical reads. A quick check if you haven't done it would be to do an explain plan on the SQL statement. If that looks okay (doesn't show you the problem), consider doing a 10046 trace on the process.

Dennis Williams
DBA
Lifetouch, Inc.

"We all want progress, but if you're on the wrong road, progress means doing an about-turn and walking back to the right road; in that case, the man who turns back soonest is the most progressive." -- C.S. Lewis

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of jaysingh1_at_optonline.net
Sent: Thursday, August 05, 2004 9:13 AM
To: oracle-l_at_freelists.org
Subject: Re: RE: Transaction table- Keep Pool

Dennis,

Thanks for your response.

The OLTP application is updating the transaction tables.

Before they update records in OLTP application, the end user will use another OLTP application which is reading(searching) set of records based on some conditions (userstatus, update date,etc).

While reading the query is getting timedout.

Thanks
Sami

> Sami
> The general advice on the KEEP pool is to look for objects you
> want to
> keep in memory. This means they should be fairly small and
> frequently used.
> I don't know what you mean by a transaction table. To me a
> transaction table
> is something you are continually appending new transactions to,
> and rarely
> reading. My largest table is a transaction table, labeled as such
> by the
> application vendor. The very opposite of what you'd want cached.
>
> Dennis Williams
> DBA
> Lifetouch, Inc.
>
> "We all want progress, but if you're on the wrong road, progress means
> doing an about-turn and walking back to the right road; in that case,
> the man who turns back soonest is the most progressive."
> -- C.S. Lewis
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [oracle-l-bounce_at_freelists.org]On Behalf Of
> jaysingh1_at_optonline.net
> Sent: Thursday, August 05, 2004 8:08 AM
> To: oracle-l_at_freelists.org
> Subject: Transaction table- Keep Pool
>
>
> 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
> executedwithin 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
> buffercache(flushed out by someother query/data) it is going for
> physical read and
> that may be the reason for longer execution time.
>
> We are trying to two things
>
> 1) Increase the size of the DB buffer cache .
> Currently DB buffer cache is 2GB and SGA is 3.5 GB.
>
> 2) Assign all the objects(indexes and tables) invoved in timeout
> queries to
> KEEP POOL
>
> Is it okay to assign tansactions tables to KEEP POOL?
>
> Thanks
> Sami
>
>
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>



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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
Received on Thu Aug 05 2004 - 09:18:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US