CACHE Tables

I've been thinking of Caching our heavily used tables in memory, but is there any type of tables which aren't suited to caching?

The table most heavily accessed at the moment, is a combination of lookups, inserts, deletes, updates - would this be suitable? or is it purely reference/lookup tables which should be cached?

Ideally I'd like to get the main 1.2Gb table in memory so I can drop all the indexes on it, but since it's so heavily updated during batch processing I'm not sure if it's viable.

(One other thing.. were running SunOS 5.6 - I have no access to the server, so does anyone know the max SHMMAX I can request to be set? I presume this is what I need to set to create a very large SGA - what is the max SGA Oracle can address on this platform?)

Thanks.

Terry

One can use CACHE the tables that are doing full table scan and more over they should be small in size .

if you use Cache for a large table then there will not be enough space for other objects(your db hit ratio will come down like mad).

even though if a table is frequently assessed but if it is large it is not a candidate for CACHE.

hope this helps..

But if I create a 2Gb SGA then surely a 1Gb Table would fit quite nicely and still leave room for adequate buffer cache?

My SGA is currenltly ~600Mb which is was way more than is required anyway (hit ratio's were good at approx 110Mb).

The table I'm thinking of currently doesn't do many Full Scans since it is heavily indexed. Problem is, it has about 20 bitmap indexes and is also heaviliy updated. We have to commit very frequently to prevent locking on the indexes. What I was thinking is dropping all the indexes on this table (with exception of the primary key). Then caching it - this would result in Full tablescans done in memory which I would have thought to be at least as fast as doing index scans on disk? (Most selects bring back a large number of rows).

What is the syntax for cacheing tables?

i feel that your table need not be cached at all. rather you can just stripe the table acroos multiple disks where I/O is not much and see the performance . You will be wasting the cache unnecessarily caching the table , i guess .!!!!

What type of Hardware you got. i thing cache the table is not required.

Hardware is fine - The striping is done automatically and I have no knowledge or control as to what disks etc. my data is on.

Basically we have a Sun E10k connected to an EMC disk array through 3 controllers. This contains lots of striped disk modules. All I get presented with is a large veritas disk group which can then be divided into volumes so data can be grouped logically. The volumes bear no resemblence to the physical disks.

There is 32Gb of memory on the server, and currenltly on 2Gb is used by about 6 Oracle database that leaves around 14Gb spare to play with. That's why I was thinking about cacheing tables.

mb - I think the syntax is alter table table_name CACHE;
The first time it is accessed after startup, it is placed in the mru part of the memory.

why not create cache index on your table (both in cache).
P.s I don't think having bitmap index on your table heavily updated is a good idea.

If you are using O8, you might also want to look at splitting up your buffer cache so you have a keep, recycle, and default pool of buffers.

Were still on 7.3.4 on production, but will be moving to 8.1.7 at some point in the future.

I don't know much about that at the moment, but I remember a post by Tamil once explaining it - I printed it off, but have never read it through properly... Time to dig it out I think. Thanks though, it's definately another angle to consider.

The feedback I'm getting seems to be negative relating to cacheing big tables in memory... even when memory size is not an issue. I've read elsewhere that the ultimate aim for a database is to get the whole thing cached in memory (since this is much much faster than disks). I've also remember reading that Amazon.com has it's entire product catalog cached (which gave me the idea).. the difference being that amazon is mainly lookup wheras my table is heavily updated.

aracnid - sorry missed your post then - I'll look into that too, thanks.
I know about the bitmap problem, but it's been inherited from a year ago due to performance - therfore it's not something I can quickly fix without having our application re-coded. In the mean time I have to live with them :(

u can go for table partitioning if u r moving to 8i......

If a table is heavily inserted/updated, then it may not be suitable for caching.

At the same time if it is queried frquently and a full table scan is needed, then caching is the best option. Since you are using 7.3, you do not have the option of setting to Buffer Pool Keep. Alternatively you can try with "ALTER TABLE...CACHE". If 20 indexes on the table are already used by many SQL, then it shows that this is one of the most critical table in the application. It is better to drop those indexes, go for "ALTER TABLE...CACHE". This will reduce the number of disk I/Os needed for the indexes.

Since, your OS 5.6 (32 bit) , you cannot create a SGA > 4GB.
What is your total RAM size?

Hi Tamil,

Thanks... Exactly what I'm thinking - Getting rid of those dreaded Bitmap indexes on this heavily updated table!.

Total server RAM size is 32Gb.

I've been told that the applications/databases on the server are using approx. 2Gb. I imagine I'd be able to make a case for an SGA of around 2-4Gb.
Btw. we will be moving to 8.1.7 in the forseeable future.

Been Away btw?!

Terry.

I just had problems with CACHE.
Even when my db_block_buffers is large enough I 've got a lot of latches in cache buffers chains with misses in 100%.
I took away CACHE and everything was ok.
I don't know what did I do worng? :)

whatever the reason a table with a size of 1Gb is not a right candidate for cache.this you can take it for granted.

I may be shooting around in the dark here... mainly because I don't know all that much about cahing tables in memory... But I would think that since RAM reads, writes, and performs much faster than hard drives that putting a table in RAM, if you have the space, would be a great idea... I mean what would be the downfall of doing this? Other then of course power failure... because that would be... well... problematic...

I have a little over a gig of RAM on my personal PC at work... it's an AMD Athlon 900 Mhz... I was curioius if I could create a temporary RAM drive and trick windows into using it as it's temporary swap file... to take the load off my hard drive... I've noticed a speed increase of about 45% across the board... so I would imagine the same would hold true for a table that was moved to RAM?