Strong doubt on small table(_small_table_threshold)

It is well known that the table is called “small” when the size is smaller than 2% of the buffer cache. That size is calculated on startup into _small_table_threshold parameter. Some links about this:

1. http://jonathanlewis.wordpress.com/2007/01/29/small-partitions/
2. http://www.ixora.com.au/q+a/cache.htm

For instance, in my local database whose db_cache_size is 200m, the _db_block_buffers is 24950. Hence _small_table_threshold should be 24950*2% = 499.

SYS@ukja10> @para _db_cache_size

NAME                           VALUE
------------------------------ --------------------
__db_cache_size                209715200

SYS@ukja10> @para _db_block_buffers

NAME                           VALUE                
------------------------------ --------------------
_db_block_buffers              24950

SYS@ukja10> @para small_table_threshold

NAME                           VALUE                
------------------------------ --------------------
_small_table_threshold         499

Why is this 2% threshold so important? Because it affects the efficiency of LRU algorithm of the buffer cache.

• Table < (2%*buffer_cache) is located into the middle point of LRU list when loaded into the buffer cache.
• Table > (2%*buffer_cache) is located into the cold end of the LRU list when loaded into the buffer cache.

This simple rule enables Oracle to prevent the buffer cache from being flooded by the full scan on big table. But the negative effect is that the full table scan on the big table is always physical(when it is read after the buffer cache got full). No matter how often the table is read.

Okay, so far so good.

A couple of days ago, I was asked the exactly same question from my customer.

What is the threshold of being small table?

I was just about to say that the threshold is 2% of the buffer cache. But wait… I’ve never proved it myself. So I tried to make a simple test case to demonstrate the 2% rule.

But, to my surprise, the famous 2% rule never worked! The more surprising thing is that in my local 10gR2 database, the threshold seems to be around 25% of the buffer cache.

Here goes the test case.

UKJA@ukja102> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

-- When the db_cache_size = 100m (without ASMM)
__db_cache_size                104857600
_db_block_buffers              12475
_small_table_threshold         249

-- When the db_cache_size = 200m (without ASMM)
__db_cache_size                209715200
_db_block_buffers              24950
_small_table_threshold         499


-- a bit big table (a bit bigger than 25% of db_cache_size)
UKJA@ukja102> create table t1(c1 char(2000), c2 char(2000), c3 char(2000)) nologging;

Table created.

UKJA@ukja102> insert /*+ append */ into t1
  2  select 'x', 'x', 'x'
  3  from dual
  4  connect by level <= 1*3400    -- when db_cache_size = 100m
  -- connect by level <= 2*3400    -- when db_cache_size = 200m 
  5  ;

3400 rows created.

-- a bit small table(a bit smaller than 25% of db_cache_size)
UKJA@ukja102> create table t2(c1 char(2000), c2 char(2000), c3 char(2000)) nologging;

Table created.

UKJA@ukja102> insert /*+ append */ into t2
  2  select 'x', 'x', 'x'
  3  from dual
  4  connect by level <= 1*3100    -- when db_cache_size = 100m 
--  connect by level <= 2*3100    -- when db_cache_size = 200m 
  5  ;

3100 rows created.

-- big table(which a bit bigger than db_cache_size)
UKJA@ukja102> create table big_table(c1 char(2000), c2 char(2000), c3 char(2000)) nologging;

Table created.

UKJA@ukja102> insert /*+ append */ into big_table
  2  select 'x', 'x', 'x'
  3  from dual
  4  connect by level <= 1*13000    -- when db_cache_size = 100m
-- connect by level <= 2*13000    -- when db_cache_size = 200m
  5  ;

13000 rows created.


-- Read table big_table after flushing buffer cache.
-- This would fill out the buffer cache.

UKJA@ukja102> alter system flush buffer_cache;

System altered.

UKJA@ukja102> -- read big_table
UKJA@ukja102> select count(*) from big_table;

  COUNT(*)                                                                      
----------                                                                      
     13000         


-- Read table t1(a bit bigger than 25% of db_cache_size). 
-- See that physical reads is equal to logical reads?
-- This means that table t1 is always located to the cold end of LRU list.

UKJA@ukja102> select count(*) from t1;

  COUNT(*)                                                                      
----------                                                                      
      3400                                                                      

UKJA@ukja102> select count(*) from t1;

  COUNT(*)                                                                      
----------                                                                      
      3400                                                                      

UKJA@ukja102> set autot on
UKJA@ukja102> select count(*) from t1;

  COUNT(*)                                                                      
----------                                                                      
      3400                                                                      

Statistics
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
       3407  consistent gets                                                    
       3376  physical reads                                                     
          0  redo size                                                          
        411  bytes sent via SQL*Net to client                                   
        400  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
          1  rows processed                                                     

UKJA@ukja102> set autot off


-- Read table t2(a bit smaller than 25% of db_cache_size). 
-- See that physical is almost gone?
-- This means that table t1 is always located to the mid point of the LRU list.

UKJA@ukja102> select count(*) from t2;

  COUNT(*)                                                                      
----------                                                                      
      3100                                                                      

UKJA@ukja102> select count(*) from t2;

  COUNT(*)                                                                      
----------                                                                      
      3100        

UKJA@ukja102> set autot on
UKJA@ukja102> select count(*) from t2;

  COUNT(*)                                                                      
----------                                                                      
      3100                                                                      

Statistics
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
       3106  consistent gets                                                    
         32  physical reads                                                     
          0  redo size                                                          
        411  bytes sent via SQL*Net to client                                   
        400  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
          1  rows processed                                                     

UKJA@ukja102> set autot off

Small table threshold is just around 25% of db_cache_size with both of 100m and 200m size!

This result gave me a strong doubt on 2% rule.

• Oracle would have unknown internal threshold. This threshold would vary by version and/or db_cache_size. Memory management algorithm would be another factor.
• 2% rule is poorly documented and it has never been fully verified.

Well, from now on, I would not trust the 2% stuff. But I strongly hope someone would have better answer and test case than mine.