table to be kept in buffer at instance startup
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: table to be kept in buffer at instance startup

  1. #1
    Join Date
    Jun 2003
    Posts
    3

    Exclamation table to be kept in buffer at instance startup

    Hi all,
    there was one question in the interview.
    they asked me that "when ever the intstance is started always that table is to be brought into the cache automatically."
    i have told that db_keep_cache in 9i, but he was telling there is one more method to keep that in the buffer cache.
    can anybody can help me.


    thanks,
    Sateesh

  2. #2
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    In the interview I'd have said the way in which you could MAXIMISE the a table data being cached in the buffer, is to create the table as CACHE, and force a full table scan via a DB_STARTUP trigger of the table... pretty stupid thing to do I've have thought though, considering a cached table would be bought to the MRU end of the buffer cache on it's first FTS anyway. Mind you CACHING should only be used on small look up tables... and this still doesn't garantee the table is ALWAYS cache, it can still age out like all other blocks in the Buffer Cahce. I'd have love to know the intervers answer.

    To me, that's a smart arse question some DBA is trying to make themselves feel good...
    OCP 8i, 9i DBA
    Brisbane Australia

  3. #3
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    You can also specify CACHE clause when creating the table.
    create table emp(ename varchar2(10)) cache;

    And make user u use this only on small tables




    Regards
    anandkl
    anandkl

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Was someone getting confused with pinning procedures?

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You could prevent it from being aged out by dedicating a cache to just that table. you could either direct it the the keep cache, or even create it as the onlyobject in a tablespace of a unique size -- say your db is generally operating on 4k blocks, and you create an 8k block tablespace with this table in it.

    It would be a dumb thing to do, though it's possible.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Originally posted by DaPi
    Was someone getting confused with pinning procedures?
    Quite possible...that'd make more sense.

    Originally posted by SlimDave
    It would be a dumb thing to do, though it's possible.
    No arguments here with that one. Possibly, but why, which of course related back to the initial question... why?
    OCP 8i, 9i DBA
    Brisbane Australia

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