Skip to Main Content
  • Questions

  • How to load entire database into memory

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sinan.

Asked: April 30, 2008 - 6:25 pm UTC

Last updated: May 02, 2008 - 10:37 am UTC

Version: 8.1.7 Standard Edition

Viewed 1000+ times

You Asked

Hi Tom,

I have an Oracle Database 8.1.7.4 Standard Edition installed on a Windows 2000 Server.

My question is for curiosity more than need.

Let's assume that we have enough physical memory and that we can guarantee that the server will never lose power.

Is it possible to run (load) the entire or a big portion of a database in (to) the memory?

select sum(bytes) from dba_data_files;

SUM(BYTES)
----------
4648337408

Is it something that one can do by simply adjusting some init parameters?

Thanks,
Sinan

and Tom said...

you just use it, as you use it, we cache it. If you don't use it, we won't

it is the way the database works.

If you really wanted an "in memory" database - we have one of those - it is called Times Ten - it does load the database into memory.


In order - remember - we are multi-versioning - if you have 1gb of data - you might need 100gb of ram to "cache" it.


but in general, just let the database do what it does - access the data and it'll be cached, if you have plenty of ram, it'll stay cached. If you don't touch it or you have insufficient memory for us to keep it all - it'll go away.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

if you have 1gb of data - you might need 100gb of ram to "cache" it.

Kasseria, May 02, 2008 - 3:18 am UTC

Hi Tom,
You said: " if you have 1gb of data - you might need 100gb of ram to "cache" it".

Is it possible or is it a usual situation when database grows ten times because of multiversioning?

Do we have to consider ten times bigger space of complete datafile size for disks?
Tom Kyte
May 02, 2008 - 7:17 am UTC

the database does not grow at all.


multiversioning is a function of undo

Sinan Topuz, May 02, 2008 - 10:12 am UTC

Tom, thank you for your time again. You stated that "if you have plenty of ram, it'll stay cached." I have let Oracle know that I have plenty of ram in the server, correct? Let's say I have 3GB ram and want to allocate 2/3 of it to Oracle, so it uses it as it wishes. On this version of DB (I know that it is not possible to set it to a one-fits-all value, and it depends on the actual data and many other aspects of the application), how would I allocate this memory to Oracle? In the task manager, is it expected to see the total allocated memory value next to "oracle process" or that reaches to it when it needs it? I hope the question is clear :)

Thank you for sharing your expertise with us.
Tom Kyte
May 02, 2008 - 10:37 am UTC

if you are using task manager, you are windows.

if you are windows, you might be 32 bit

if you are 32bit, you probably are not going to be able to use 2/3 of 3gb - maybe 1.8gb of ram.

Of which you have to fit both PGA and SGA.

How to size that will depend on your concurrent users - lots of sessions - larger PGA needs. Fewer sessions - lesser PGA needs.

You'll take part of that 1.8 gb and give it to the SGA target and the rest and give it to the pga aggregate target.


If you are 64bit, you can in fact give 2gb to the sga and 3/4 of a gb or so to the PGA.

Thanks for explaining PGA, and SGA on 32-bit

Satya, May 02, 2008 - 11:37 am UTC

Thanks for explaining PGA, and SGA on 32-bit windows. I am sure most of development servers, servers created by budding DBAs and students are on Windows 32-bit. Many of these DBAs (me including) are learning/practicing on these databses. Your brief answer would indeed help lot of them.

I appreciate you in taking all questions equally.

Robert C, May 03, 2008 - 7:08 am UTC

A reviewer to this thread said earlier:

"Is it possible or is it a usual situation when database grows ten times because of multiversioning? "

Well it cannot be a usual situation, if it is not possible can it?

Keep the jokes coming!


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.