Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2886806
  • 博文数量: 599
  • 博客积分: 16398
  • 博客等级: 上将
  • 技术积分: 6875
  • 用 户 组: 普通用户
  • 注册时间: 2009-11-30 12:04
个人简介

WINDOWS下的程序员出身,偶尔也写一些linux平台下小程序, 后转行数据库行业,专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群:58308065,23618606

文章分类

全部博文(599)

文章存档

2014年(12)

2013年(56)

2012年(199)

2011年(105)

2010年(128)

2009年(99)

分类: Oracle

2013-01-06 17:02:06

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.
阅读(936) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~