CACHE Tables

[TerryD]

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 :(

[pao]

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

[tamilselvan]

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?

[TerryD]

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.

[rcaballe]

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? :)

[omegamark]

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

[sirhaggus]

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?