Sinan -- Thanks for the question regarding "How to load entire database into memory", version 8.1.7 Standard Edition

Submitted on 30-Apr-2008 18:25 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 2-May-2008 10:37

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 we 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.

Reviews

if you have 1gb of data - you might need 100gb of ram to "cache" it. May 2, 2008 - 3am Central time zone

Bookmark | Bottom | Top

Reviewer: Kasseria from Kayseri

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?

Followup May 2, 2008 - 7am Central time zone:

the database does not grow at all.

multiversioning is a function of undo

May 2, 2008 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: Sinan Topuz from New York, NY USA

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.

Followup May 2, 2008 - 10am Central time zone:

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 May 2, 2008 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Satya from Dover, NH

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.

May 3, 2008 - 7am Central time zone

Bookmark | Bottom | Top

Reviewer: Robert C

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!

Write a Review