Home » RDBMS Server » Server Administration » tables KEEP in KEEP POOL have lot of DML transactions (Performance)
tables KEEP in KEEP POOL have lot of DML transactions [message #608788] Mon, 24 February 2014 20:26 Go to next message
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.

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 Go to previous messageGo to next message
BlackSwan
Messages: 22712
Registered: January 2009
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.

 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?
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 58913
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Forget this pool just use the default one.

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 Go to previous message
John Watson
Messages: 4508
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.
Previous Topic: Index problem
Next Topic: SGA estimation
Goto Forum:
  


Current Time: Wed Aug 27 20:52:42 CDT 2014

Total time taken to generate the page: 0.12787 seconds