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

Thread: buffer cache

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi

    I wonder how Oracle manage internally the buffer cache in following situation

    The buffer cache is small, so small that if someone do a

    select * from table_name


    table which has 100 million rows all the data wont fit in the buffer cache, how Oracle manage this? Let´s put some figures, the buffer cache can store data blocks of 10 million rows of this table, will Oracle first return 10 million rows then remove them from the buffer cache then read from disk and put another 10 million rows into buffer cache and remove again etc until all 100 million rows have been fetched and returned to user? i.e 10 fetches instead of one

    [Edited by pando on 01-31-2001 at 08:30 AM]

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    anyone got a point about this :-?

  3. #3
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234
    Hi Pando,

    I think this problem can be compared with configuring a recycle buffer pool which is to small to cache the table which is asigned to it.

    When performing a full table scan the table does not fit in the recycle pool and I/O is heavely increased.
    While oracle builds the result set

    HTH
    tycho

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    yes but how Oracle builds the results is my question, in 10 fetches and return to user 10 times or how?

  5. #5
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    I think it goes somehow like this.

    You do a full table scan.

    Oracle reads a set of blocks ( i think using db_file_multiblock_read_count ) and puts the data in the db buffer cache.

    Next oracle reads then next set of blocks and puts them in the db buffer cache.
    Now the blocks in the buffer cache are put on the list of free/available blocks for the buffer cache.( do not remember the exact name )

    So,

    when you are alone on the db, the buffer cache will fill with the data from the table, but will be replaced by new blocks during the following reads.

    when you are not alone on the db, the select will not monopolise the db buffer cache. It will fill it, but when other sessions need db buffer cache blocks, they will use the blocks used by the previous read.

    Regards
    Gert

  6. #6
    Join Date
    Nov 2000
    Posts
    212

    I think this answers your question(Oracle v816, Concepts guide, Oracle intstance->MemoryArchitecture->SystemGlobalArea(SGA)):

    "it must copy the data block from a datafile
    on disk into a buffer in the cache before accessing the data."


    "The first time an Oracle user process requires a particular piece of data, it searches
    for the data in the database buffer cache. If the process finds the data already in the
    cache (a cache hit), it can read the data directly from memory. If the process cannot
    find the data in the cache (a cache miss), it must copy the data block from a datafile
    on disk into a buffer in the cache before accessing the data. "

  7. #7
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234
    I think it is not 10 times fetching but more a continious process of reading blocks and processing the result.

    tycho

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    ""So,

    when you are alone on the db, the buffer cache will fill with the data from the table, but will be replaced by new blocks during the following reads. ""


    ok, will the data that fills the buffer first send to user then those buffer are overwritten by more data and then send back again to user again then be overwritten again and so on until all data are sent to user? (10 million rows each go)

    OR

    All data will send in a go to user, i.e all 100 million rows

  9. #9
    Join Date
    Nov 2000
    Posts
    212
    if I would be Oracle programmer, I would have little choise than to:

    read data into memory array, i.e buffer
    send data from buffer to pipe/socket/etc. (i.e. to user)

    The only question is: does it makes sence to search for a free space in a LRU buffer, as it consumes some time (LRU latch contention, search for free space in buffer, etc, etc).

    If I would be an Oracle programmer, I would do this processing because of probability that the data in cache may be hit by another user/query.

    However, there are hints, that may tell Oracle a little bit how to do the processing of storing data in a chache:
    CACHE, NOCACHE, FULL

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hm my question is not about cache, nocache or full, my question is how Oracle manage this kind of situation internally. How does Oracle send the data back to user if we are selefcting 100 million rows and the buffer cache cant fit more than 10 million row data, does oracle send 10 million/time or how?

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