caching tables

[diwakar]

Dear Sir,
Many of us may be aware that caching tables in memory would increase performance. Out of the several tables that may be present in a tablespace, some of them may be big . Out of the several tables in the tablespace, how does one find out as to how much memory may be required to cache a particular table alone??

Thanks and regards,

K.Diwakar

[kgb]

If you cach a table, it does not mean that it will be placed to memory immidiately, only blocks you query will be placed in memory and leave there untill you reach limits of memory allocated for your cach, after that blocks that you did not use for a last time will be shift by blocks you use very oftern (in your case).
I would not say that it is good idea to cach all tables, becuase it may degradate performance. Usually only small tables are cached.

[Edited by kgb on 06-12-2002 at 09:25 AM]

[btrigg01]

I have found the alter table {~~~} cache command issued against key tables can imporve the cache performance. As said above it won't cache the whole table just improve its placement in the least recently used chain for flushing out of the cache.

If I can have a cache 10% of the current live data in the database objects then I'm a happy DBA.... 50% and well i'm over the moon. Depends on your ammount of physical memory. Sorry if this is teaching you to suck eggs.....

The data warehouse I look after has season partitioned tables so verifying the size of the current data is simple.

ive used this SQL to identify the tables being accessed to then prioritise by cache command above:-
select * from v$access where sid='25' and owner='USERNAME'
/