Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    370

    Question Unanswered: Multiple Buffer pool question

    Hi,
    I want to mark a certain table and index to be cached in a specific Buffer pool but I don't know how, and what is the benefit of this work?
    -thanks in advance
    PM: Are there many buffer pools in Oracle architecture?!

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    With Oracle 8.1 and above you have the following buffer pools

    DEFAULT
    KEEP
    RECYCLE

    The default buffer pool in for "normal" data block processing ... data blocks
    being moved to the LRU list and read from there... If you happen to do a
    large table scan, the data in the LRU may be flushed out to make room for
    the data blocks from the table scan. This will cause the "normal" data blocks
    to have to be reloaded in the LRU the next time their needed. (expensive processing ... potentially causing physical IO's)

    You can use the recycle pool for data blocks that you don't want to affect the "normal" operations.

    KEEP pool ... data blocks that you want to ensure stay in the buffer pool and not get aged out (still can be aged out, but not likely)..

    ex:
    sql> alter table hr.jobs storage (buffer_pool keep);

    HTH
    Gregg

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    The way I use the buffer pools is to assign your small or lookup tables to the KEEP pool and your very large tables to the recycle pool. Anything in between goes to the DEFAULT pool.

    You can write a simple query against dba_tables to look at num_rows to write the sql to assign the tables to the write pools. Same for your indexes.

    Alan

Posting Permissions

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