FAQ
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

Original Message -----
From: DENNIS WILLIAMS
Date: Thursday, August 5, 2004 9:56 am
Subject: RE: Transaction table- Keep Pool
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
-----------------------------------------------------------------

Search Discussions

  • DENNIS WILLIAMS at Aug 5, 2004 at 9:18 am
    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
    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

    Original Message -----
    From: DENNIS WILLIAMS
    Date: Thursday, August 5, 2004 9:56 am
    Subject: RE: Transaction table- Keep Pool
    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
    -----------------------------------------------------------------

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 5, '04 at 9:09a
activeAug 5, '04 at 9:18a
posts2
users2
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase