The Oracle (tm) Users' Co-Operative FAQ
How long is a 'short' tablescan ?
| Author's name: Jonathan Lewis Author's Email: Jonathan@jlcomp.demon.co.uk |
Date written: 26th July 2001 Oracle version(s): 7.0 - 8.1.7 |
| Oracle's view v$sysstat has two interesting statistics about the number of table scans that have taken place; these are table scans (short tables) and table scans (long tables). These are of particular interest because a scan of a long table loads the blocks into the bottom end of the data buffer with a discard fast flag set, whereas a scan of a short table loads the blocks into the top end of (the cold portion of) the data buffer, whence they can follow.a normal aging out algorithm. |
Once upon a time, posisbly in Oracle 5 but perhaps as late as Oracle 6.0.29, a short table was defined to be one that was four blocks or less. Unfortunately this number has stuck in the collective subconscious of DBAs the world over, long after it ceased to be true.
There is a parameter, once visible but now hidden, that is particularly relevant to this question. It is the _small_table_threshold, and its value defaults to two percent of the db_block_buffers parameter.
Given the common occurence of very large buffers these days (64,000 blocks is not uncommon), this means a small table can be very big. Be careful when you interpret the statistic regarding table scans (short tables) - short tables are not as innocent as you might think.
Further reading: N/A
