Oracle 参数之_small_table_threshold

从Oracle 7.3.4开始引进该参数以来，至目前的Oracle 11g还在使用，首先来看一下某生产系统该参数的情况：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
SQL> set linesize 120
SQL> col name for a30 trunc
SQL> col value for a20
SQL> col pdesc for a50 trunc
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.KSPPDESC PDESC
  2    FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3  WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%';
Enter value for par: small
old   3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new   3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%small%'

NAME                           VALUE                PDESC
------------------------------ -------------------- --------------------------------------------------
_small_table_threshold         3731                 threshold level of table size for direct reads

缺省情况下Oracle认为在2%的cache buffer以下的表格认为是小表，在FTS操作中被放到MRU end。
在_small_table_threshold以上的表格标记为大表，FTS操作结果被放置到LRU end。也就是说Oracle仅仅缓冲一次操作的结果。

以下为ixora对该参数的说明：

引用

The default is not 5 blocks; it is 2% of db_block_buffers with a minimum of 4 blocks. The effect is exactly as you describe. Small tables are cached at the MRU end of the cache by default. The statistic is incremented for all logical small table scans, even against fully cached tables. It is also incremented for scans of small tables for which the NOCACHE keyword has been specified. It is not however incremented for large tables for which the CACHE keyword has been specified.

This parameter is session modifiable and system modifiable with deferred semantics. If changed dynamically, the parameter name needs to be enclosed in quotes to protect the leading underscore, as follows.

alter session set "_small_table_theshold" = 100;

需要指出的是该参数可以动态调整，其默认值不是业界所认为的5 blocks，而是小于等于db_cache_size*2%或者是4*blocksize（当db_cache_size小于200个blocks时），假设2G的Cache buffer，那么2G*2%=40m，这样在40m以下的表格都可能被认为小表。假设表格平均行长为200字节，8k块，具有5120个块。平均可用空间8000，这样40m的表格有204800行。这个数字还是颇为可观的。