Oracle 参数之ITeyecsdn - 乐橙lc8

Oracle 参数之ITeyecsdn

2018-09-18 10:38:51 | 作者: 曜曦 | 标签: 参数,表格,状况 | 浏览: 3104

从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行。这个数字仍是较为可观的。
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表乐橙lc8立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章