Oracle Database Smart Flash Cache (DSFC)

The Oracle Database Smart Flash Cache solution, or simply DSFC, accelerates primarily OLTP workloads by caching frequently accessed blocks in a hybrid memory area that spans from the Oracle database buffer cache to one or more flash memory storage devices.  The feature is recommended for any Oracle database stored entirely on disk, which is well known for higher wait times and lower throughput than flash.

This feature is only available on Oracle branded operating systems including Oracle Linux and Solaris.  It does not require the Oracle branded kernel known as UEK.  If you are not using an Oracle operating system, then consider moving specific tablespaces to a flash storage device.

Physically, the DSFC is just a file.  The file can span one or more flash storage devices or partitions.  This spanning of devices is inherent to DSFC, which acts like software defined RAID 0.  You may use logical volume management or Oracle ASM disk groups to do the same thing, but that's overhead. 

The Oracle Database Block Writer process stores clean blocks in the DSFC file, and those blocks can be retrieved by Server Processes (i.e., your user shadow process) from the DSFC file into the Oracle database block buffer cache just as they can fetch blocks from any storage device.  One major difference is the DSFC file is stored on low latency flash, allowing random reads to occur at incredible speed compared to conventional reads.

This type of cache is actually storage.  Blocks in the DSFC are physically stored on NAND flash chips.  The cache can be many times larger than your SGA but will most like be many times smaller than your database files.  Thus, Oracle cannot cache all of your data, and it doesn't want to.  Oracle intelligently determines which blocks to keep in the flash cache.

Traditional caching software uses a solid-state device or flash drive as the "backing store".  A backing store is any storage device that transparently sits in back of the caching software and is dedicated to storing the cache.  The first read of data will be copied from primary storage to both the user and the cache's backing store.  Subsequent reads pull from the backing store only.  DSFC integrates this design into the Oracle stack using proprietary technology.

Oracle will only write clean data blocks to the DSFC.  DML will reads blocks from the DSFC into the database buffer cache -- these reads are technically physical I/O from flash, but reading from flash is many times faster than reading from disk-based datafiles.  Eventually, a checkpoint will persist the dirty blocks from the buffer cache to the database files, and those blocks are marked clean and aged out of the buffer cache back to the flash cache.

The storage devices do not need a filesystem.  That is, you don't need to mount an EXT4 filesystem or create an ASM disk group.  DSFC can simply use the devices in their native state.  However, you may pool storage devices into an ASM disk group if you like.

RAC Notes for DSFC

Each RAC instance must point to a separate DSFC file or storage partition.  RAC instances cannot share a DSFC area.  Generally, a separate flash storage device is installed in every RAC node and is dedicated to the instance running on that node.  If you have multiple instances on a node, you can either install a separate device per instance, or partition the device and give one partition to each instance.

Most Oracle RAC databases run on Oracle ASM.  To use the DSFC with Oracle ASM and RAC requires a separate diskgroup for each RAC instance and you must add the SID clause when setting the init.ora parameter.  For example:

ALTER SYSTEM SET DB_FLASH_CACHE_FILE='+FLASH1/MYDB/FLASHFILE/fc.ora' SID='SALES1';
ALTER SYSTEM SET DB_FLASH_CACHE_FILE='+FLASH2/MYDB/FLASHFILE/fc.ora' SID='SALES2';
ALTER SYSTEM SET DB_FLASH_CACHE_FILE='+FLASH3/MYDB/FLASHFILE/fc.ora' SID='SALES3';

Performance Expectations

Real world performance gains using DSFC have been extraordinary: up to 40x faster transactions simply by adding one flash storage device to your server and enabling DSFC.  That's the top-end.  More realistic is 3x faster transactions. 

Of course, DSFC does not accelerate all database operations like auditing and RMAN backups.  There were several case studies published circa 2010 to 2015 that showed over the course of a 24x7 period your database might run 3 to 5 times faster.  That's well short of the 40X potential.  Still, imagine a $5,000 hardware investment making your million dollar Oracle system run 3 times faster.  That's just adds one-half of 1% cost to the system (with software licenses being the bulk of cost).

I had some customers who got no benefit at all.  Oracle didn't run any slower, but it didn't run any faster.  That was due to their database workload and the ratio of SGA memory to active data blocks.

Enabling the Oracle Database Smart Flash Cache

First, you must be running an Oracle branded operating system such as Oracle Linux or Solaris.  You do not need to use the Oracle branded kernel known as UEK.

Second, install one or more flash storage devices.  These can be flash adapter cards (i.e., /dev/fioa), or SSD using SATA or SAS protocols (i.e., /dev/sdx), or modern NVMe devices (i.e., /dev/nvme0n1).

Third, set ownership and permissions on the storage devices.  Use the same settings as your regular database storage devices.

Finally, set two database initialization parameters: DB_FLASH_CACHE_FILE and DB_FLASH_CACHE_SIZE.  Both parameters support an ordered list of comma-separated values.  The below example allocates 400 GB from devices fioa and fiob, and 1280 GB from device fioc, to create a single 2 TB flash cache:

alter system set db_flash_cache_file='/dev/fioa,/dev/fiob,/dev/fioc' scope=spfile;
alter system set db_flash_cache_size='400g,400g,1280g' scope=spfile;
shutdown immediate;
startup;

The first time you set these parameters you must use scope=spfile and then bounce the instance to pick up both changes simultaneously. 

Oracle supports up to 16 devices in the Flash Cache, which can be any combination of local or attached storage.  The parameters use an ordered list so that the first device is mapped to the first size and so on.

Configuring the Database Smart Flash Cache

By default, all tables and indexes can use the DSFC and you don't need to configure anything special.  This is because all tables and indexes have attribute STORAGE(FLASH_CACHE DEFAULT). 

However, you may find that only DML statements are benefiting from DSFC.  And, you may have tables that you don't want cached for some reason.  For those reasons I describe optional configuration tasks.

Optionally, and at any time, you can re-configure each table or index’s storage attribute to change how it interacts with DSFC.  When it set to DEFAULT all blocks fetched into the buffer cache by a “db file sequential read” operation can use the DSFC.  If you would also like to include blocks fetched by scan operations simply alter the property to KEEP, or to prevent a table/index from using DSFC set the property to NONE. 

The STORAGE() clause is shown below (ref):

STORAGE
({ INITIAL size_clause
 | NEXT size_clause
 | MINEXTENTS integer
 | MAXEXTENTS { integer | UNLIMITED }
 | maxsize_clause
 | PCTINCREASE integer
 | FREELISTS integer
 | FREELIST GROUPS integer
 | OPTIMAL [ size_clause | NULL ]
 | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
 | FLASH_CACHE { KEEP | NONE| DEFAULT }
 | ENCRYPT
 )

Notice the FLASH_CACHE clause has three settings, which are described below:

  • DEFAULT is the default setting.  It tells Oracle you want blocks to be written to the flash cache when they are aged out of the database buffer cache, and they can be aged out of the flash cache according to Oracle’s LRU algorithm.  Since DEFAULT is the default, you can omit it entirely.
  • KEEP tells Oracle to cache the object’s blocks in Flash as long as space permits.  It also allows blocks fetched by sequential reads to populate the cache, subject to Oracle's 2% rule.
  • NONE tells Oracle you do not want blocks for this table to use the flash cache. 

Example 1, you do not want table EMP to use DSFC:

ALTER TABLE EMP STORAGE (FLASH_CACHE NONE);

Example 2, you want table EMP to use the DSFC regardless of how the blocks were fetched into memory:

ALTER TABLE EMP STORAGE (FLASH_CACHE KEEP);

Example 3, you want to return table EMP to the default use of DSFC:

ALTER TABLE EMP STORAGE (FLASH_CACHE);

Monitoring the DSFC

The Oracle AWR Report includes a few metrics for the Oracle Database Smart Flash Cache.  The DSFC’s activity is listed as Optimized Physical Reads.  However, using the AWR Report requires software licenses, so I cannot expect everyone to use them.

Metrics can also be obtained easily from the view V$SYSSTAT like this:

select name, value from v$sysstat
   where lower(name) like 'flash cache%'
   order by value desc;

The results of the above query might look like this:

NAME                                                  VALUE
 ------------------------------------------------ ----------
 flash cache inserts                                47846483
 flash cache insert skip: not useful                 3392522
 flash cache insert skip: exists                     2039562
 flash cache eviction: invalidated                   1182304
 flash cache insert skip: modification                   103
 flash cache insert skip: not current                      5
 flash cache insert skip: corrupt                          0
 flash cache eviction: aged out                            0
 flash cache eviction: buffer pinned                       0
 flash cache insert skip: DBWR overloaded                  0
 10 rows selected.

To see which segments and blocks are in the DSFC use the view V$BH like this:

select
  OBJECT, TOTAL_BLOCKS, FREE_BLOCKS, FLASH_BLOCKS,
  ( TOTAL_BLOCKS - FREE_BLOCKS - FLASH_BLOCKS ) CACHE_BLOCKS
 from
 ( select
       a.owner || '.' || a.object_name OBJECT,
       count(*) TOTAL_BLOCKS,
       SUM (CASE WHEN b.status LIKE '%free%' THEN 1 ELSE 0 END) FREE_BLOCKS,
       SUM (CASE WHEN b.status = 'flashcur'  THEN 1 ELSE 0 END) FLASH_BLOCKS
    FROM v$bh b, dba_objects a
    WHERE b.objd = a.object_id
    AND   a.owner NOT LIKE '%SYS%'
    AND   a.owner NOT IN ('DBSNMP','XDB')
    AND   a.owner NOT LIKE 'GSMADMIN%'
    GROUP BY a.owner, a.object_name
    order by 4 desc
 )
 /

The results of the above query might look like this:

OBJECT               TOTAL_BLOCKS FREE_BLOCKS FLASH_BLOCKS CACHE_BLOCKS
-------------------- ------------ ----------- ------------ ------------
TPCC.CUSTOMER             7261053           0      5674298      1586755
TPCC.IORDL                3434848           0      2755665       679183
TPCC.STOCK                2321380           0      1243214      1078166
TPCC.CUSTOMER_I2           996159           0       716345       279814
TPCC.CUSTOMER_I1           674051           0       400996       273055
TPCC.STOCK_I1             1545589           0       218922      1326667
TPCC.HISTORY               172640           0       121279        51361
TPCC.ORDERS                177717           0        98497        79220
TPCC.ORDERS_I1             135469           0        82113        53356
TPCC.DISTRICT               49558           0        47819         1739
TPCC.ORDERS_I2             122907           0        45245        77662
TPCC.WAREHOUSE               4999           0         4825          174
TPCC.INORD                   8472           0         2881         5591
TPCC.DISTRICT_I1              297           0          224           73
TPCC.ITEM                    1085           0           16         1069
TPCC.ITEM_I1                  246           0            7          239
TPCC.WAREHOUSE_I1              11           0            1           10

17 rows selected.

The view V$FLASHFILESTAT shows the cumulative latency and read counts for each file associated with the DSFC.  Latency is measured in microseconds.  You can manually calculate average latency by dividing cumulative latency by cumulative reads.  Please note this view has a separate line for each device in the DSFC so you need to sum the rows like this:

SQL> select sum(singleblkrds) TOTAL_READS,
       sum(singleblkrdtim_micro) / sum(singleblkrds) AVG_LAT_US
 from v$flashfilestat;
 TOTAL_READS AVG_LAT_US
 ----------- ----------
    3056021 125.972532

Where's the Database Smart Flash Cache Now?

DSFC is still available and used by some customers, but implementation rates have fallen sharply over the past decade.  

More and more Oracle databases are run entirely on flash storage, which is faster than using the same flash technology for a caching service.  If your database is stored on flash, then you cannot make it faster by putting a flash-based cache in front of it.  In fact, it will be slower due to the added overhead of managing the cache.

The DSFC is still a viable option for significantly boosting the performance of Oracle databases stored on disk arrays.

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 flat files, with one file for each of
Read more

Understanding TPC and TPC-like Database Benchmarks

This post is Part 1 in a series about running benchmarks to measure the performance of relational databases.  This post provides a very brief introduction to the popular industry standard benchmarks TPC-C, E, H, and DS and the software programs you can use to run a variation of each benchmark. The discussion here is generic and can be applied to any relational database like Oracle, SQL Server, MySQL, and PostgreSQL.   The TPC has many benchmarks for non-relational databases not discussed here.   The discussion here is also intended for informal benchmarking without publication.   Why Benchmark? Benchmark tools can answer a variety of questions and help predict success or failure.   Consider the following examples of why organizations perform internal benchmarking of database systems: Predict change impact .   Organizations run benchmarks to see how a system change impacts performance.   Run a benchmark to establish a baseline, make a system or database change, and then re-run t
Read more