5 Replies Latest reply: Mar 12, 2011 5:53 AM by Jonathan Lewis RSS

    full table scan and LRU

    user88831
      Hi Guys,

      Will like to understand more on this.

      For eg, my db cache size is 800m. I have a table which is 400m.

      When doing a full scan, will the whole 400m of data cache in the memory?

      If this is the case, if my cache size is 800m, but my table is 1GB. How will the whole data be cache?

      Can explain?

      thanks
        • 1. Re: full table scan and LRU
          846716
          Hi dbaing,
          dbaing wrote:
          For eg, my db cache size is 800m. I have a table which is 400m.

          When doing a full scan, will the whole 400m of data cache in the memory?
          http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/memory.htm#CNCPT1224

          Buffers and Full Table Scans
          When buffers must be read from disk, the database inserts the buffers into the middle of the LRU list. In this way, hot blocks can remain in the cache so that they do not need to be read from disk again.

          A problem is posed by a full table scan, which sequentially reads all rows under the table high water mark (see "Segment Space and the High Water Mark"). Suppose that the total size of the blocks in a table segment is greater than the size of the buffer cache. A full scan of this table could clean out the buffer cache, preventing the database from maintaining a cache of frequently accessed blocks.

          Blocks read into the database cache as the result of a full scan of a large table are treated differently from other types of reads. The blocks are immediately available for reuse to prevent the scan from effectively cleaning out the buffer cache.

          In the rare case where the default behavior is not desired, you can change the CACHE attribute of the table. In this case, the database does not force or pin the blocks in the buffer cache, but ages them out of the cache in the same way as any other block. Use care when exercising this option because a full scan of a large table may clean most of the other blocks out of the cache.


          dbaing wrote:

          If this is the case, if my cache size is 800m, but my table is 1GB. How will the whole data be cache?
          Obviously some of data will be flushed, dirty blocks will be written in datafiles. The mechanizm of flushing blocks from buffer cache is based on LRU and touch count.


          http://www.juliandyke.com/Presentations/LogicalIO.ppt
          [url http://www.dbguide.net/upload/20060316/1142482749257.pdf]Touch count
          • 2. Re: full table scan and LRU
            jgarry
            It depends. In 11g, there is a thing called 'direct path reads' which means it gets read into your pga, bypassing the SGA altogether. Unless of course, someone is updating at the same time, then it will be read into the pga and read changed blocks with db file sequential reads.

            So some people see slower filling of the sga after upgrading, since in previous versions scans would fill up the sga, but now it doesn't.

            Googling finds all sorts of interesting things like http://dioncho.wordpress.com/2009/07/21/disabling-direct-path-read-for-the-serial-full-table-scan-11g/ and follow the links.
            • 3. Re: full table scan and LRU
              user88831
              Hi Guys,

              I'm uising 10G. So i supposed in 10G, the whole table won't be cache?

              thanks
              • 4. Re: full table scan and LRU
                846716
                [url http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/memory.htm]10.2 Memory Architecture

                >
                The LRU Algorithm and Full Table Scans

                When the user process is performing a full table scan, it reads the blocks of the table into buffers and puts them on the LRU end (instead of the MRU end) of the LRU list. This is because a fully scanned table usually is needed only briefly, so the blocks should be moved out quickly to leave more frequently used blocks in the cache.

                You can control this default behavior of blocks involved in table scans on a table-by-table basis. To specify that blocks of the table are to be placed at the MRU end of the list during a full table scan, use the CACHE clause when creating or altering a table or cluster. You can specify this behavior for small lookup tables or large static historical tables to avoid I/O on subsequent accesses of the table.
                >

                Which minor version do you have?
                Notice, that serial direct path reads have been back-ported in 10.2.0.5.
                • 5. Re: full table scan and LRU
                  Jonathan Lewis
                  Alexander Anokhin wrote:
                  [url http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/memory.htm]10.2 Memory Architecture
                  The LRU Algorithm and Full Table Scans
                  When the user process is performing a full table scan, it reads the blocks of the table into buffers and puts them on the LRU end (instead of the MRU end) of the LRU list. This is because a fully scanned table usually is needed only briefly, so the blocks should be moved out quickly to leave more frequently used blocks in the cache.
                  >

                  Another post from Dion Cho which helps to highlight the inadequacy of the manuals in this respect:
                  http://dioncho.wordpress.com/page/2/?s=small_table_threshold

                  Hair-splitting here, but blocks don't go to the MRU end of the cache and haven't since 8.1 - they go to the mid point.

                  More importantly from the viewpoint of correcting the manual - how close to the LRU end do blocks go when tablescans ? The answer (from Dion's tests on the default cache) is that you can get about 25% of the cache wiped by a large tablescan. If your table is larger than that it will start flushing itself from the cache as you go on scanning.

                  But there's more - even if your table is smaller than 25% of the cache, if it is larger than 10% of the cache size then the touch count of the buffers you load won't be incremented by subsequent tablescans, which means they will fall off the end of the cache every time they get there. So any table larger than 10% of the cache size can't stay cached (unless you do things withe CACHE option, or create a KEEP cache - and neither of those features works quite as well as you might like).
                  Notice, that serial direct path reads have been back-ported in 10.2.0.5.
                  Thanks for that - it's nice to know about these things before you walk into them.

                  Regards
                  Jonathan Lewis