Re: How does a table scan effect the cache?

As usual there are a couple of 'special cases' which mess this up, but broadly speaking you are correct.

There will actually be 17 blocks in the perfect case - the segment header which will become hot, and 16 blocks flagged as 'scanned only' which will be 'at the end of the LRU'.

However, other activity on the database, particularly concurrent scans on OTHER tables could result in the number of buffers holding blocks from your tablescan begin higher than the perfect case.

e.g. Scan on table X pins buffers 1 to 16 Your scan on table Y pins 17 to 32
The first scan completes, buffers 1 to 16 are the oldest on the LRU list, so your
next scan uses buffers 1 to 16 and you end up with 32 blocks from your table in the buffer.

In other words - your understanding is nominally correct, but in practice it may not appear to be so.

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html

Screensaver or Lifesaver: http://www.ud.com
Use spare CPU to assist in cancer research.




Chuck Hamilton wrote in message ...


>When a full scan is done on a table, it's my understanding that the

>blocks read go to the least recently used end of the LRU list. Does

>that mean that every time the scan reads a group of blocks

>(db_file_multiblock_read_count to be exact), that the same blocks at

>the end of the LRU list keep getting replaced on each read? So

>assuming my dbfmbrc = 16, and no other access is happening against the

>table, and the table is large enough to not be cached at the top of

>the list, I would never have more than 16 blocks of that table in the

>cache? Is my understanding on this correct?

>--

>Chuck Hamilton

>chuck_hamilton_at_yahoo.com

>

>"Do not be deceived, God is not mocked;

>for whatever a man sows, this he will also

>reap." (Gal 6:7 NASB)