12 Replies Latest reply: Jan 17, 2014 1:49 PM by 923751 RSS

db file sequential reads on full table scan and LRU (again)

923751 Newbie
Currently Being Moderated

I would like to add a question concerning the topic

full table scan and LRU

According to MOS document 1457693.1

"...large multiblock reads against cached blocks can be broken up into a number of smaller multiblock and single block reads."

The question is whether db file sequential reads submitted by a FULL SCAN operation will be cached on LRU or MRU list ?

I am afraid of flushing/flooding the buffer cache with lots of FULL SCAN db file sequential reads.

For whatever reason serial direct path reads will not be applied, so using this new 11g feature is out of scope for this question.

Thank you for your interest,

Rainer Stenzel

  • 1. Re: db file sequential reads on full table scan and LRU (again)
    sybrand_b Guru
    Currently Being Moderated

    A full scan issues scattered reads, not sequential reads.

    Sequential reads are single block reads.

     

    Can you post something which indicates you are actually suffering from sequential reads in full table scans (output from a trace file preferred)

     

    --------------

    Sybrand Bakker

    Senior Oracle DBA

  • 2. Re: db file sequential reads on full table scan and LRU (again)
    923751 Newbie
    Currently Being Moderated

    Having sequential reads in FULL SCAN operations is confirmed expected (mis)behavior by Oracle (support).

  • 3. Re: db file sequential reads on full table scan and LRU (again)
    Martin Preiss Expert
    Currently Being Moderated

    I don't think there is a lot you can do to prevent this behaviour. Oracle excludes the blocks that are already in the cache and reads the remaining blocks accordingly by multiblock or single block reads. I think Jonathan Lewis describes the details in Oracle Core (where he also states that the blocks read by an FTS go not to the MRU end but to the mid point of the list). I could imagine (but don't remember) that the single blocks also go to the mid point of the list - but again: I don't think there is much to do to prevent this situation. So if you need FTS you have to use Oracle's implementation.

  • 4. Re: db file sequential reads on full table scan and LRU (again)
    Aman.... Oracle ACE
    Currently Being Moderated

    The full table scans are linked to the LRU list only. Caching them at the MRU point would flush a lot of already cached buffers and that's not going to be a good thing. But it's strange to see the sequential access for FTS.

     

    Aman....

  • 5. Re: db file sequential reads on full table scan and LRU (again)
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    There are a few different patterns of behaviour dependent on the size of the table (relative to the size of the buffer cache), but the key question is probably "will the reads increment the touch count" - because if they don't the blocks will fall off the LRU list fairly promptly, if they do then the blocks could (after a couple of tablescans) end up being promoted into the hot half of the cache.

     

    I've done a couple of quick tests (requiring a little care in setup) that suggested the touch count was not incremented so had no effect on whether the blocks would get preferential treatment when they reached the end of the LRU.

     

    I am a little puzzled by your expression: "cached on LRU or MRU list" - it's not two different lists; but people do talk about "the MRU end of the LRU list".

     

    Regards

    Jonathan Lewis

  • 6. Re: db file sequential reads on full table scan and LRU (again)
    923751 Newbie
    Currently Being Moderated

    Sorry for the imprecise terminology.

    The more general question might have been:

    Will blocks read by 'db file sequential read' instead of 'db file scattered read'

    during FULL SCAN operations ((LARGE) TABLE FULL SCAN and INDEX FAST FULL SCAN)

    be handled differently in matters of caching (LRU insertion point and touchcount setting) and therefore more likely flood/flush the buffer cache ?

    As far as I understood your answers this should not be the case.

    Thank you for participating,

    Rainer Stenzel

  • 7. Re: db file sequential reads on full table scan and LRU (again)
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    I suspect that these 'db file sequential read' block gets would be treated as would any other 'db file sequential read' block gets.

     

     

    Hemant K Chitale


  • 8. Re: db file sequential reads on full table scan and LRU (again)
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    Hemant,

     

    You didn't read the "correct answer" - which is correct.

    I've just written a little blog note inviting people to think about proof: Single block reads | Oracle Scratchpad

     

    Regards

    Jonathan Lewis

  • 9. Re: db file sequential reads on full table scan and LRU (again)
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    Aah .  Another forums bug.  The "correct" answer is shown as "correct" when I view the thread as https://community.oracle.com/thread/2612840     However, when I view the thread from the communications tab, it doesn't show which post is marked as correct !

    I read 927351's followup to your "correct" answer as a fresh question raised as a correct answer "hasn't been received yet !"

    I wanted to say that irrespective of whether a 'db file sequential read' is part of a Full Table Scan or any other type of lookup (e.g. an Indexed Read), it is treated in the same manner in the buffer cache.

     

    Hemant K Chitale


  • 10. Re: db file sequential reads on full table scan and LRU (again)
    923751 Newbie
    Currently Being Moderated

    puzzled again.

    As I did understand the discussion so far newly read blocks will be cached in a different/special way (LRU position, touch count and other flags) to avoid buffer cache clean out when submitted during FULL TABLE SCANS, scattered reads as well as sequential reads.

    Addendum 1

    I still consider the performance degradation of (scattered reads enabled) FULL SCAN operations with bigger caches as a serious misbehavior.

    It makes little sense (to me) to split multi block reads into even more physical read operations just to save some bandwith resources or whatever.

    Addendum 2

    I have been surprised to see that INDEX FAST FULL SCAN read blocks will NOT be handled in the same way as TABLE FULL SCAN operations to avoid buffer cache clean outs, which I was presuming probably because of its scattered read usage, though the Oracle documentation clearly describes the caching exception explicitly and only for Full Table Scans. Beside setting “flag = 524288″ (only_sequential_access) they seem rather to be handled as simple INDEX FULL SCAN operations.

    Addendum 3

    Additionally I was surprised about a good performance of INDEX FULL SCAN operations during my tests.

    I guess these might benefit considerably from a filesystem (prefetch) cache and a "well organized" index as I got very

    low average wait times for db file sequential reads in this case.

    Regards,

    Rainer Stenzel

  • 11. Re: db file sequential reads on full table scan and LRU (again)
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    A1:  It's got to be done that way - the in-memory copy of the block might have been updated since it was last read and contain data that is needed but not available in the on-disk copy. I have seen some clues, though, that there is work being done on a mechanism that uses maximum reads then throws away the unwanted blocks.

     

    A2 The last time I checked the index fast full scan was handled in exactly the same was as the tablescan. Since indexes are generally much small than the tables they index it is easy to be fooled into thinking that something completely different has happened because the index just happens to belong to the next size down from the table. You may also see the activity looking like a FULL SCAN because tests which create a new large index will tend to have the leaf blocks in order in the data segment, so the physical difference between a "fast full scan" and a "full scan with readahead (prefetch)" is marginal.

     

    A3 There is an index prefetch option available - Oracle can do "db file scattered read" and "db file parallel read" while doing an index full scan.

     

    Regards

    Jonathan Lewis

  • 12. Re: db file sequential reads on full table scan and LRU (again)
    923751 Newbie
    Currently Being Moderated

    Thank you Jonathan,

    appreciate your engagement and enthusiasm.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points