_small_table_threshold on Exadata
                                                 Last update (2012-09-18 10:05:42)
                                                                                                                   Date added (2012-06-30 19:41:37)

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%
Reviews
Filters
Search