Multiple buffer pools
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Multiple buffer pools

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Question

    Hi,

    Is anybody using multiple buffer pools (KEEP,RECYCLE,DEFAULT)?

    Is there any practical benefit seen by anybody after using this?

    How to check whether there is any real advatage of these pools?

    Pl. throw some light on this.

    TIA
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Mar 2002
    Posts
    301
    Hi ,

    You can gather information about these multiple buffer pools from:

    V$BUFFER_POOL
    V$BUFFER_POOL_STATISTICS.

    Vijay.
    Say No To Plastics

  3. #3
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Thanks Vijay,

    I also wanted to know...

    Is there any practical benefit seen by anybody after using this?

    How to check whether there is any real advatage of these pools?

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    BUFFER_POOL KEEP is a very good option introduced in 8/8i.
    If the appln system has 100s of look up tables, then use KEEP option while creating the tables. The data cached on KEEP buffer will not be flushed out due to aging mechanism.


  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    U can use different parts of db cache, if u know characteristics of ur applications.

    DEFAULT pool (~ 50% of all of cache) for "usuall" transaction process
    KEEP pool (~20% of of size of cache) for lookup operation on small tables
    RECYCLE for full scan table operations

    But how u going to divide db cache, depend from application.
    This is only example.

  6. #6
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    [QUOTE]Originally posted by Shestakov
    RECYCLE for full scan table operations

    Is there any table, which gives list of tables which perform full table scans?

    I read somewhere that RECYCLE buffer pool is for large frequently used tables.

    Can I only use KEEP and DEFAULT buffer pool to begin with?

    Pl. advise.

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  7. #7
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Yes u can.

    "I read somewhere that RECYCLE buffer pool is for large frequently used"

    RECYCLE pool --> for rarely segments of blocks or for large segments of blocks.
    (usually it happend when we have full scan table operations)

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Sizing the recycle pool is not easy: personal experience. Even after using TKPROF and SQL Trace one might end with poorly sized R-pool. Especially, if the application is "rich" in SQL-statements: then you'll do better without the R-pool.

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

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