Posts Tagged ‘full table scan’
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:
- http://jonathanlewis.wordpress.com/2007/01/29/small-partitions/
- 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.
Dion Cho - Oracle Performance Storyteller 