DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: CACHE Tables

  1. #1
    Join Date
    Sep 2000
    Posts
    128
    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

  2. #2
    Join Date
    Sep 2000
    Posts
    384
    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..
    Radhakrishnan.M

  3. #3
    Join Date
    Sep 2000
    Posts
    128
    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).

  4. #4
    Join Date
    Aug 2000
    Posts
    143

    Question

    What is the syntax for cacheing tables?

  5. #5
    Join Date
    Jul 2000
    Posts
    119
    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 .!!!!

  6. #6
    Join Date
    Dec 1999
    Location
    Alpharetta, GA, US
    Posts
    192
    What type of Hardware you got. i thing cache the table is not required.
    Chan
    OCP7.3/8.0/8i/9i
    Sun Certified Sys. Admin

  7. #7
    Join Date
    Sep 2000
    Posts
    128
    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.

  8. #8
    Join Date
    Apr 2000
    Location
    roma
    Posts
    131
    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.

  9. #9
    Join Date
    Nov 1999
    Location
    Elbert, Colorado, USA
    Posts
    81
    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.

  10. #10
    Join Date
    Sep 2000
    Posts
    128
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width