When loading objects and data into the buffer cache of the instance there is a functionality which determines the best way to load for full scans. This functionality is called smallness logic and is driven by a hidden parameter called:_small_table_threshold.If the number of blocks to be read is lower than or equal to the setting of the parameter Oracle will load the object via the buffer cache as this is more efficient than doing a direct read operation. However, the parameter does not need to be set explicitly as there is also a dependency function calculating the cut over threshold if the parameter is unset. This calculation is roughly 2% of the buffer cache size to be used as the cut over threshold.
NOTE:
This means any object (table) smaller than 2% of the buffer cache will be read via the buffer cache and not using direct load。
So if the parameter is unset and the size of the buffer cache is reduced the smallness logic will trigger sooner for the same object, i.e. if the buffer cache is set to 1001 blocks and we have a table of 20 blocks the table will be considered small and read via the buffer cache. If we reduce the buffer cache to 1000 blocks or smaller the table will no longer be considered small and therefore a direct read is used to load the object.
If we compare this to the test case where we increase the size of the cache from 20 Mb to 30 Mb we see exactly this behavior, i.e. when the db cache is 20 Mb the object is too large to fall inside the 2% of the buffer cache and we see the direct reads. When the buffer cache is increased to 30 Mb the object now falls into the category of "small" and triggers the loading mechanism to use the standard buffer caching.
The decision whether to use direct load or db file sequential load is taken in the "Kernel Cache Buffer management" module and there is no other way to monitor this than to use standard SQL tracing to see the size of the data being loaded via tkprof and then look at the db cache size, check if the _small_table_threshold is set or unset and then use the 2% rule if it is unset.
阅读(934) | 评论(0) | 转发(0) |
