OraFAQ Forum: Server Administration » tables KEEP in KEEP POOL have lot of DML transactions

Home » RDBMS Server » Server Administration » tables KEEP in KEEP POOL have lot of DML transactions (Performance)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

tables KEEP in KEEP POOL have lot of DML transactions [message #608788]

Mon, 24 February 2014 20:26

sivanarayana
Messages: 2
Registered: February 2014
Location: United States

Junior Member

I would like to add few tables to KEEP POOL in SGA, But they have lot of DML(delete/update/insert) operations.
And table have 4 million records.
Delete or Insert or update translations happen on this table per every 2 millisecond.

Can you please help me to understand how this work, if i keep this kind of table in KEEP pool to reduce DISK I/O and improve the sql performance.

Thanks
chekuri.

Report message to a moderator

Re: tables KEEP in KEEP POOL have lot of DML transactions [message #608792 is a reply to message #608788]

Mon, 24 February 2014 20:41

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Your implicit assumption is that you know better how to manage SGA space than Oracle.
If the data within these 4 tables are frequently accessed, then those rows will remain in the SGA & won't be paged out.
If you force tables into the KEEP POOL, then there is less room in SGA for rows from other tables to be kept in the SGA which results in MORE disk I/O.

1 DML every 2 millisecond results in 500 rows every second are changed.

Code: [Select all] [Show/ hide]

 1* select (4000000/500)/(60*60) from dual
SQL> /

(4000000/500)/(60*60)
---------------------
           2.22222222

which means every 2.22 hours every row gets changed.

What kind of application does such nonsense?

Report message to a moderator

Re: tables KEEP in KEEP POOL have lot of DML transactions [message #608803 is a reply to message #608792]

Tue, 25 February 2014 01:00

sivanarayana
Messages: 2
Registered: February 2014
Location: United States

Junior Member

My database have enough SGA, almost 15GB space. Table size i would like to move to KEEP POOL is 40MB.

Sorry I have wrong assumption that 1 DML for every 2 msec, But all 4 million records will update once per day.

Is it good idea to move this kind of table to KEEP POOL, since all records update once per day ?.

but this table accessed by entire application. most of the sql's mapped with this table.

Report message to a moderator

Re: tables KEEP in KEEP POOL have lot of DML transactions [message #608806 is a reply to message #608803]

Tue, 25 February 2014 01:06

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Forget this pool just use the default one.

Report message to a moderator

Re: tables KEEP in KEEP POOL have lot of DML transactions [message #608811 is a reply to message #608788]

Tue, 25 February 2014 02:22

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

You didn't provide the release of Oracle that you are running. I do not have a reference for this, but it is said that Oracle has improved the buffer replacement algorithm hugely in recent releases with the result that a keep pool is unlikely to be of value any more. Also the use of latches has changed, and latches used to be a good reason for creating a keep pool.
That having been said, I would think a keep pool is unlikely to cause a problem, if it is big enough to cache the entire four tables. You would probably want to cache the indexes there as well. 40M is not much.
I am of course open to correction on this.

Report message to a moderator

Previous Topic:	Index problem
Next Topic:	SGA estimation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 05 12:32:28 CDT 2025