B.I.G Cache

A new 12c feature came to my attention recently on a new system I was looking after.

db_big_table_cache_percent_target

https://docs.oracle.com/database/121/REFRN/GUID-122865EE-4589-434D-8DD5-4E201C6CC739.htm#REFRN10340

This parameter control the percentage of you db cache reserved for the scanning of ‘big’ tables, So if db cache was 1000M , and we set parameter to 50, that would be 50 Percent of cache reserved for big cache so 500M.

So I naturally assumed if set we would start to see blocks cached in this memory area from scans of large tables.

Well I was wrong there are certain rules for this parameter to be used and these differ between a Oracle RAC database and a single instance NON RAC database;

Going back to the documentation this is specified:

Starting in Oracle Database 12c Release 1 (12.1.0.2), table scans can use a different algorithm in the following scenarios:

  • Parallel queries: In single-instance and Oracle Real Application Clusters (Oracle RAC) databases, parallel queries can use the automatic big table cache when the DB_BIG_TABLE_CACHE_PERCENT_TARGET initialization parameter is set to a non-zero value, and the PARALLEL_DEGREE_POLICY initialization parameter is set to AUTO or ADAPTIVE.
  • Serial queries: In a single-instance configuration only, serial queries can use the automatic big table cache when the DB_BIG_TABLE_CACHE_PERCENT_TARGET initialization parameter is set to a non-zero value.

I was working in Oracle RAC, as above we need another parameter set for the big cache to be used ‘PARALLEL_DEGREE_POLICY’.

We can view the usage of the big cache using view : v$BT_SCAN_CACHE;

SQL> select * from v$BT_SCAN_CACHE;

BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP	 CON_ID
-------------- --------------- ------------ ---------------- --------------- ----------
    .500010207		    50		  0		   0		1000	      0

Above nothing is in use , object count is 0, memory_buf_allocated is 0

What’s interesting is this parameter should be dynamic and if set with PARALLEL_DEGREE_POLICY should use big cache, I found I needed a db bounce for the database to start using the big cache. Also as per documentation only certain SQL is eligible to use the cache PQ in RAC.

1. Check usage: 0 is used
select * from  v$BT_SCAN_CACHE;

BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP	 CON_ID
-------------- --------------- ------------ ---------------- --------------- ----------
    .500010207		    50		  0		   0		1000	      0

2. Run a big select
SQL> select count(*) from part_num_tab;

  COUNT(*)
----------
  93968384

3. Check usage 0 is used
SQL> select * from  v$BT_SCAN_CACHE;

BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP	 CON_ID
-------------- --------------- ------------ ---------------- --------------- ----------
    .500010207		    50		  0		   0		1000	      0

4. Run same select using PQ
SQL> select /*+ parallel(2) */ count(*) from part_num_tab;

  COUNT(*)
----------
  93968384

5. Memory Area now used

SQL> select * from  v$BT_SCAN_CACHE;

BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP	 CON_ID
-------------- --------------- ------------ ---------------- --------------- ----------
    .499979585		    50		  4	       97966		1000	      0