Summary
Start investigation - experiments on Exadata
_small_table_threshold
select name, blocks, mb_size, mb_size*5 threshold, isdefault from
(select name, value blocks, round((value *
(select value from v$parameter where name = 'db_block_size'))/(1024*1024),2) MB_SIZE, isdefault
from (select x$ksppi.ksppinm name, x$ksppsv.ksppstvl value, x$ksppsv.ksppstdf isdefault
from x$ksppi, x$ksppsv
where x$ksppi.indx = x$ksppsv.indx
and x$ksppi.ksppinm = '_small_table_threshold'));
NAME BLOCKS MB_SIZE THRESHOLD ISDEFAULT
------------------------------ ---------- ---------- ---------- ---------
_small_table_threshold 10485 327,66 1638,3 TRUE
The value of the hidden parameter _small_table_threshold is in blocks. From what i read it is almost
the 2% of the db_cache_size. From what i have seen when the buffer_cache it has a fixed size then indeed
it is approximately 2% of the buffer cache. When the buffer cache resizes, then
the _small_table_threshold it is resized, but not equally and not immediately.
In Oracle 9i, it is almost 2% (fixed buffer_cache)
SELECT NAME, blocks, mb_size, mb_size*5 threshold, isdefault
FROM
(SELECT NAME, VALUE blocks, ROUND((VALUE *
(SELECT VALUE FROM v$parameter WHERE NAME = 'db_block_size'))/(1024*1024),2) MB_SIZE, isdefault
FROM (SELECT x$ksppi.ksppinm NAME, x$ksppsv.ksppstvl VALUE, x$ksppsv.ksppstdf isdefault
FROM x$ksppi, x$ksppsv
WHERE x$ksppi.indx = x$ksppsv.indx
AND x$ksppi.ksppinm = '_small_table_threshold'))
"UNION ALL"
SELECT 'buffer_cache', NULL, SUM(ROUND(bytes/(1024*1024),2)) size_mb, NULL, NULL
FROM v$sgastat WHERE NAME = 'buffer_cache';
NAME BLOCKS MB_SIZE THRESHOLD ISDEFAULT
------------------------- ---------- ---------- ---------- ------
_small_table_threshold 3731 29,15 145,75 TRUE
buffer_cache 1504
In Oracle 10g, (fixed buffer_cache, started with 17408MB and resized to 26624MB)
NAME BLOCKS MB_SIZE THRESHOLD ISDEFAULT
------------------------- ---------- ---------- ---------- ------
_small_table_threshold 12305 384,53 1922,65 TRUE
buffer_cache 26624
the _small_table_threshold did not increased equally. In fact, it didn't increased immediately after resizing buffer cache
After waited for some time from the increase of the buffer cache at the end the _small_table_threshold was the 2%.
And finally, Oracle11g
Exadata (Automatic Memory Management)
NAME BLOCKS MB_SIZE THRESHOLD ISDEFAULT
------------------------- ---------- ---------- ---------- ------
_small_table_threshold 34317 1072,41 5362,05 TRUE
buffer_cache 54400
1072,41/54400 = 0,0197 ~ 2%
