Friday, May 26, 2017

Ready for some automatic big table caching?




A new feature in 12.1.0.2 is automatic big table caching.  This is pretty cool.  The key parameter for it is db_big_table_cache_percent_target. This more or less sets aside some of the buffer cache to be used for full table scans.  It’s not like the KEEP or RECYCLE pool, those have dedicated object to them.  And it’s not really setting up a set off buffer that can’t be used for anything else.  The idea is that up to this percent of the buffer cache cold be used for big table full table scans.

Now a little history on full table scans in Oracle.  Prior to 11 full table scans (of any table) were always in the buffer cache.  However if the table was of significant size, the Oracle wouldn’t even try to pull in the entire table during the scan.  It would in effect, cycle thru a set of buffers for the whole table.  In 11 Oracle stated to move this work out of the SGA (System Global Area) and into the PGA (Program Global Area).   The basic idea was that these block were highly unlikely to be shared so why even try, let's just do that work in an area for the user.  The same thing basically happen, the table was cycled thru a set of buffers in the PGA space. 

But what about tables that have full scans on them often?  Even if the entire table couldn’t be cached, if a good portion of it could be that would likely be faster and all that.  So in a sorta-kinda-way oracle is going back to the old way of doing full scans, but with a twist.  The twist is decided which tables to keep in the cache.  A new characteristic is now tracked with tables in the buffer cache, temperature. 

Temperature is at the object level not the block level.  Oracle uses the good old LRU (Least Recently Used) algorithm at the block level to decide when to age out blocks.  But with automatic big table caching things change.  Now when full scans happen on a table, the more then happen, the hotter the table becomes and the more likely it will stay in cache.

So here’s a simple test to show it in action.  The two scripts (ahwm.sql and bigtab_cnt.sql) run queries on two different tables that are both big enough to be big tables for my instance.   Also I have a script (hbtcache.sql) to show some stats.  The code for this script will be at the bottom of this post.

SQL>
SQL> alter system set db_big_table_cache_percent_target=40;

System altered.

SQL> SQL> @hbtcache
********************************************************
****** Over all automatic big table caching stats ******

BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP
--------------- ------------ ---------------- ---------------
             40            0                0            1000

********************************************************
**** Object level automatic big table caching stats ****

no rows selected

SQL>
SQL>
SQL> set termout off
SQL> @ahwm
SQL> set termout on
SQL> @hbtcache
********************************************************
****** Over all automatic big table caching stats ******

BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP
--------------- ------------ ---------------- ---------------
             40            1           43,787            1000

********************************************************
**** Object level automatic big table caching stats ****

  DATAOBJ# OBJECT_NAME                    POLICY      TEMPERATURE SIZE_IN_BLKS CACHED_IN_MEM
---------- ------------------------------ ---------- ------------ ------------ -------------
     73914 AHWM                           MEM_ONLY          1,000       43,787        43,787

SQL> set termout off
SQL> @bigtab_cnt
SQL> set termout on
SQL> @hbtcache
********************************************************
****** Over all automatic big table caching stats ******

BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP
--------------- ------------ ---------------- ---------------
             40            2           86,416            1000

********************************************************
**** Object level automatic big table caching stats ****

  DATAOBJ# OBJECT_NAME                    POLICY      TEMPERATURE SIZE_IN_BLKS CACHED_IN_MEM
---------- ------------------------------ ---------- ------------ ------------ -------------
     73914 AHWM                           MEM_ONLY          1,000       43,787        43,787
     73488 BIG_TAB                        MEM_PART          1,000       43,161        42,629

SQL>

What we see is that at 40% of the cache it was able to put the entire table AHWM in the cache, but couldn’t fit all of BIG_TAB.  But it did put in as much of it as it could.   So latter scans on AHWM will all be in the buffer cache (no physical reads) but on BIG_TAB some physical reads will still happen.

A couple things, it’s not real clear to me at this point how the temperature goes up.  Clearly the more times you access it the hotter it gets, but what 1000 means is not clear and I supposed it’s not critical for us to know how Oracle measures this.  


Also the MIN_CACHED_TEMP doesn’t appear appear to be modifiable.  I suspect that we will be able to modify this at some point. 


Over all this looks like a cool thing, or is it a hot thing?  Hummm…


Code for hbtcache.sql  

rem hbtcache.sql
rem MAY2017 RVD
rem shows stats on automatic big table caching
rem
column memory_buf_alloc format 999,999,999
column object_name      format a30 wrap
column temperature      format 999,999,999
column size_in_blks     format 999,999,999
column cached_in_mem    format 999,999,999
set lines 200
prompt ********************************************************
prompt ****** Over all automatic big table caching stats ******
select bt_cache_target, object_count, memory_buf_alloc, min_cached_temp
from v$bt_scan_cache
/

prompt ********************************************************
prompt **** Object level automatic big table caching stats ****
select dataobj#, object_name, policy, temperature, size_in_blks, cached_in_mem
from v$bt_scan_obj_temps btsot, dba_objects dbaobj
where btsot.dataobj#=dbaobj.object_id(+)
order by temperature
/

1 comment: