Oracle Automatic Big Table Caching (ABTC)

This blog post describes the Oracle Automatic Big Table Caching feature, first introduced in patchset 12.1.0.2 back in July 2014.  The feature really hasn't changed since then, so this post is relevant to Oracle versions 12c R2, 18c, 19c, and 21c.

What is Automatic Big Table Caching, and Why Do I Care?

Oracle's Automatic Big Table Caching solution, or simply ABTC, accelerates DSS, Warehouse, and Analytics workloads by allowing data fetched by table scans to be cached in the SGA and shared by all users.  Without this feature, each user must fetch their own copy of the data using costly physical I/O.

Here's why you might need ABTC.  By default in Oracle, a scan of any table larger than 2% of database initialization parameter db_cache_size will skip the SGA entirely and go across the network to the client's private memory are called PGA.  Since there's no shared cache involved, every user who wants to see the same data must fetch a private copy from storage.  If 100 users each run select * from products you'll have 100 copies of the table in the PGAs of those 100 clients. 

Lookup tables like lists of countries or languages are often below the 2% threshold so their data is cached in the database buffer cache.  Fact tables in a data warehouse can be many gigabytes and considered "big" by Oracle, so their data is not cached.  All data tables are generally considered "big".

ABTC attempts to solve the problem by allowing you to alter Oracle's default behavior and cache data from "big" tables.  Then, repeat reads can be serviced from memory instead of physical reads from storage. 

For fun, you could shrink db_cache_size to a minimum size and there would be no impact on performance since you're not really using it.  Well, you are using the database cache for small lookup tables, but you could probably read those from storage so fast compared to the larger tables that you cannot perceive the additional time.  If it takes an hour to run a full table scan on a data table, what's a few more seconds fetching the lookup tables.

I put air quotes around "big" when I talk about this issue because 2% of db_cache_size is generally a very small value.  "Big" tables that skip caching could be any table with more than a few megabytes of data, or several gigabytes might be the threshold.  It depends on your server.  Regardless, it doesn't leave a lot of "small" tables that can use the database buffer cache unless you enable ABTC.

Let's calculate "big" and "small".  It's a simple three-step process:

  1. A typical medium size database server has 512 GB memory where up to 50% is allocated to the Oracle SGA, and 90% of the SGA is allocated to the database buffer cache.  In this case your maximum value of db_cache_size is 230 GB (512 * 0.5 * 0.9 = 230). 
  2. Convert the size from DRAM's base-10 number to Oracle's base-2 requirement.  230000000000 / 1024 / 1024 / 1024 = 214 GiB.  This means we can set parameter db_cache_size=214G. 
  3. Finally, we apply Oracle's 2% rule and we see that 2% of 214G which is just 4.6G.  Any table up to that size can be cached in the database buffer cache, but reads from all other tables skip caching and go directly across the wire to PGA memory on a client computer.

There is a hidden init.ora parameter to control the 2% rule, but changing it without written consent from Oracle Support creates an unsupported configuration.

ABTC allows you to change Oracle's default behavior and cache data from all tables regardless of size.  And, unlike the init.ora hack, ABTC is fully supported by Oracle.

Even when ABTC is enabled, there's no requirement for Oracle to cache entire tables.  Oracle can perform hybrid reads.  Suppose the first user performs a full table scan and Oracle knows there is only enough free memory to cache 80% of the table, then as it reads the whole table from storage it will cache the first 80%.  When a second user queries the same table Oracle can pull that 80% from memory and only need to do physical I/O for the other 20%.  Hybrid scans are super important when you think about the number of tables that might need caching.

Does Automatic Big Table Caching Help?

But, the question remains, does ABCT really help performance?  

Performance gains from ABTC depend greatly on your storage and workload.  The slower your storage layer, the more room there is to gain performance.  Recall this feature is not intended for use with transactional systems.

My database on flash went 4X faster after enabling ABTC.  An identical database on an array of 8 internal hard drives went nearly 20X faster after enabling ABTC.  These results were observed using the HammerDB implementation of a TPC-H-like Power User Test.

Enabling Automatic Big Table Caching

To enable ABTC set database initialization parameter DB_BIG_TABLE_CACHE_PERCENT_TARGET to a non-zero value (it is 0 by default).  The parameter specifies the percentage of the database buffer cache’s default pool to be sectioned off for use by the Big Table Cache.  The maximum allowed value for this parameter is 90, because Oracle requires at least 10% of the cache be left for not-big-table-query results such as routine DML.

In a RAC database the DBA must also set parameter PARALLEL_DEGREE_POLICY to either AUTO or ADAPTIVE.  That's because in a RAC database only parallel queries may use the Big Table Cache, but in non-RAC databases all queries can use it.

That's also true for non-RAC databases, but Oracle doesn't prevent you from skipping this parameter.  Parallel queries will only use the Big Table Cache if you set PARALLEL_DEGREE_POLICY to either AUTO or ADAPTIVE regardless if you are using RAC or not.  The difference is, RAC databases require this parameter whether you use parallelism or not, while single instance databases only require this parameter to run parallel queries.

Please note you cannot use Force Full Database Caching (FFDC) and Automatic Big Table Caching (ABTC) at the same time.  This is because FFDC disables in-memory PX affinity which is required by ABTC.  Given a choice between the two you should enable FFDC because it will provide much better performance than ABTC.  However, most systems lack the DRAM required to enable FFDC, while ABTC allows partial table caching and can be enabled on any system.  I have another blog post on FFDC.

Monitoring ABTC basically involves querying two Oracle-supplied dynamic performance views:

  • V$BT_SCAN_CACHE shows the parameters and status of the Big Table Cache.
  • V$BT_SCAN_OBJ_TEMPS lists the active objects currently tracked by the Big Table Cache.

For more information about Oracle Automatic Big Table Caching and the Big Table Cache, please see the following Oracle 21c documentation online: Oracle Concepts Guide, Oracle Database Reference, and Oracle VLDB Guide.


Comments

Post a Comment

Popular posts from this blog

Using DBGen to Generate TPC-H Test Data

Image
In this post I describe the Data Base Generator (DBGen) utility in terms of what it is, how to install it, and how to use it.   This post assumes a Linux operating system.   There are different build procedures for DBGen on Windows, but the concepts carry over. DBGen is used to create a TPC-H schema (i.e., it provides the CREATE TABLE statements) and to generate the data.   DBGen is used in official TPC-H benchmarking, but it can also be used in informal TPC-H-like benchmarking.   The schema and data is compatible with most 3 rd party benchmarking tools like HammerDB, so even if you plan to run a TPC-H-like test using HammerDB you may still use DBGen to create the schema and data. The  CREATE TABLE   statements are ANSI standard SQL and can be run in any DBMS without editing.   There are no SQL statements for indexes or foreign key constraints provided by DBGen since those types of objects are not required.   The data is created as delimited...
Read more

Oracle 21c Caching Solutions

This blog post reviews all of the caching solutions available in Oracle Database 21c.  I dive into several of the caching solutions, but I do try to keep things relatively short-and-sweet here.  I have other blogs dedicated to each caching solution. I don't mean the Oracle SGA or PGA.   I mean optional features you can enable to enhance performance. The content of this blog post is applicable to Oracle 12g, 18c, 19c, and 21c.   Some of the caching solutions were available in Oracle 11g, but I did not regression test my examples back that far. There have been no new caching solutions in Oracle 12g R2 through 21c.   One solution in particular, the In-Memory Column Store, has received some major enhancements.   I briefly describe it here, but the deep dive is in another blog post. Summary of Oracle Caching Solutions Oracle 21c includes six caching features as listed in my table below.   I've listed them in order from newest on top to oldest at t...
Read more

TPC-like Database Benchmarking Tools

Image
  This post describes five popular tools for benchmarking relational databases: HammerDB, Quest Benchmark Factory, SwingBench, pgbench, and Oracle RAT.  Previously, I described TPC and TPC-like benchmarks for relational databases.   Recall a benchmark is "like" a TPC benchmark if it follows some of the rules but not all of them.   The tools described in this post follow some of the TPC's rules so we call them "TPC-like".   Publishing results is generally not allowed by commercial DBMS vendors, and use of the benchmark trademark is also restricted. Ok then, let's look at the most commonly used benchmarking tools for relational databases. HammerDB is my #1 choice for benchmarking relational databases.   HammerDB supports every major DBMS on Linux and Windows including bare metal, VM, and cloud.   This means you can use one tool for all environments.   You can even compare performance across platforms or DBMS, such as comparing Oracle on Linux...
Read more