Questions You Asked: What is CELLMEMORY, and how do I know if it is being used?

October 11, 2023 | 10 minute read
Andy Rivenes
Product Manager
Text Size 100%:

Columnar formatted data was first introduced for Exadata Smart Flash Cache in Oracle Database Release 12.1.0.2. This initial format supported HCC objects and “… rewrote 1 MB chunks of HCC format blocks in the flash cache into pure columnar form in a way that allowed us to only do the I/O for the columns needed but also to recreate the original block when that was required.” [1]

In Oracle Database Release 12.2 a second format was introduced that stored columnar formatted data in Exadata Smart Flash Cache with the enhancement that it leverages all Database In-Memory optimizations. This was called CELLMEMORY and was further enhanced in Oracle Exadata System Software 18c (18.1.0) to also support non-HCC objects. You can read more about the details in my post: Database In-Memory Columnar Format in Exadata Flash.

For the rest of this post, I will refer to the first version of the Exadata columnar cache as HCC columnar cache and the second version as CELLMEMORY.

The reason for this post is to expand on how to tell if you actually used the columnar cache format in Exadata Flash and if so, which one. My colleague Markus Kissling, wrote a post called How to Determine if Columnar Format on Exadata Flash Cache is Being Used back in August of 2018, and since then there have been many changes and new statistics added to Oracle Database so I thought it would be a good time for an update.

First some background. The columnar cache is part of Exadata Smart Flash Cache and is a smart I/O optimization. This basically means that the Exadata columnar cache must be accessed through a Smart Scan. An Exadata Smart Scan offloads data search and retrieval processing to the Exadata storage servers. You can read about what a Smart Scan is in detail at this link in the System Software User’s Guide for Exadata Database Machine.

One of the key things about a Smart Scan is that it optimizes predicate and column filtering. This means that it can evaluate filter predicates from the WHERE clause during the access of the data. It can also filter out columns so that only requested columns are returned instead of all columns. There are other functions that can be performed as well, but these two basic functions are important to understand as we look at how to tell if HCC columnar cache or CELLMEMORY were used.

How do you know if you used a Smart Scan? The first prerequisite is that you see a TABLE ACCESS STORAGE FULL operation in your execution plan. Here is an example:

----------------------------------------------------------------------------------------------
| Id  | Operation                  | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                 |       |       | 28288 (100)|          |
|   1 |  SORT AGGREGATE            |                 |     1 |     6 |            |          |
|   2 |   TABLE ACCESS STORAGE FULL| LINEORDER       |    11M|    68M| 28288  (15)| 00:00:02 |
----------------------------------------------------------------------------------------------

This doesn’t guarantee that a Smart Scan will occur, since there are many reasons that a Smart I/O operation won’t be performed. You can find more information in this link to the System Software User’s Guide for Exadata Database Machine. However, if you do see a TABLE ACCESS STORAGE FULL then you can find out more details, and whether Exadata columnar cache was used, by looking at the session-level statistics for the query.

The following table lists the relevant statistics that are important when evaluating whether a Smart Scan was used and whether the columnar cache was accessed. These statistics and their meanings can also be found in the System Software User’s Guide for Exadata Database Machine and the Oracle Database Reference. When evaluating these statistics at the session, or system level, the important thing to know is (a) how much space the table/segment consumes on storage and (b) how much of it is cached in Flash using the HCC columnar cache or CELLMEMORY data formats.

Note that I have indented some of the statistics in order to show that some of the statistics are a subset of a parent statistic, and I have altered some of the descriptions to try and be more clear about what the statistic means.

Statistic Meaning
cell physical IO bytes eligible for predicate offload The number of bytes on-disk that are eligible for a Smart Scan
cell physical IO bytes eligible for smart IOs The number of actual bytes eligible for a Smart Scan. For example, when using columnar cache, this is the size of the columnar cache instead of the on-disk size.
  cell physical IO bytes saved by storage index The number of bytes saved by using a storage index
  HCC scan cell CUs pruned For HCC objects, the number of Compression Units that were pruned, or skipped over, based on Min/Max value checks
  cell physical IO bytes saved by columnar cache Bytes not read because columns were skipped in the columnar cache (i.e. column filtering)
  cell physical IO bytes processed for no memcompress HCC columnar cache format
  cell physical IO bytes processed for IM capacity The number of bytes in CELLMEMORY using memcompress for capacity format (This is the default)
  cell physical IO bytes processed for IM query The number of bytes in CELLMEMORY using memcompress for query compression
  cell scan CUs pcode aggregation pushdown* Number of CUs that benefitted from aggregation or group by pushdown
  cellmemory IM scan column CUs format dict* CUs with optimal In-Memory dictionary format
  cellmemory IM scan column CUs format rle dict* CUs with optimal In-Memory run length encoded dictionary format
cell physical IO interconnect bytes returned by smart scan The number of I/O bytes that were returned by the cell for Smart Scan operations

* - Specific CELLMEMORY enhancements

When evaluating CELLMEMORY usage, the key statistics to look at are "cell physical IO bytes processed for IM capacity" and "cell physical IO bytes eligible for smart IOs". If the IM capacity, or query if using that compression level, statistic is a large percentage of the bytes eligible for smart IOs statistic, then it means that CELLMEMORY was used for a majority of the dataset, and this will give maximum performance.

There are also three additional CELLMEMORY statistics indented below the MEMCOMPRESS statistics that will show if additional In-Memory benefits were leveraged during the Smart Scan. There are many more, but these three will provide the biggest clue as to whether In-Memory optimizations were also used with CELMEMORY.

Now let’s take a look at an example. The following shows a simple query that scans just the LINEORDER table, part of the SSB schema that we use for Database In-Memory examples, looking for the most expensive order with two WHERE clause filter predicates. It also displays the execution plan and the session level statistics, and I ran this query several times to ensure that I got consistent results.

SQL> select  max(lo_ordtotalprice) most_expensive_order From LINEORDER
  2  where lo_shipmode = 'SHIP' and lo_orderpriority = '5-LOW';

MOST_EXPENSIVE_ORDER
--------------------
            53060444

SQL>

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID  1805gvxmtb2r8, child number 2
-------------------------------------
select  max(lo_ordtotalprice) most_expensive_order From LINEORDER where
lo_shipmode = 'SHIP' and lo_orderpriority = '5-LOW'

Plan hash value: 2609824775

----------------------------------------------------------------------------------------------
| Id  | Operation                  | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                 |       |       | 32344 (100)|          |
|   1 |  SORT AGGREGATE            |                 |     1 |    33 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| LINEORDER       | 78559 |  2531K| 32344  (26)| 00:00:02 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage(("LO_SHIPMODE"='SHIP' AND "LO_ORDERPRIORITY"='5-LOW'))
       filter(("LO_SHIPMODE"='SHIP' AND "LO_ORDERPRIORITY"='5-LOW'))

NAME                                                                        VALUE
------------------------------------------------------------ --------------------
cell physical IO bytes eligible for predicate offload                   729382912
cell physical IO bytes eligible for smart IOs                           513335296
cell physical IO bytes processed for IM capacity                        299892736
cell physical IO bytes saved by columnar cache                           57540608
cell physical IO interconnect bytes returned by smart scan               33492480
cell scan CUs pcode aggregation pushdown                                      635
cell scan CUs pcode pred evaled                                              1270
cell scan rows pcode aggregated                                            269537

You might notice that in the Predicate Information section it shows:

storage(("LO_SHIPMODE"='SHIP' AND "LO_ORDERPRIORITY"='5-LOW'))

this is an indication that the two filter predicates will be pushed down, or offloaded, to the Smart Scan.

In the statistics section I just included the statistics that were non-zero from the statistic table above. My intent was to show you how to tell that a Smart Scan occurred (i.e., cell physical IO bytes eligible for smart IOs) and that CELLMEMORY was used (i.e., cell physical IO bytes processed for IM capacity). The Smart Scan was also able to avoid scanning unnecessary columns in the LINEORDER table as shown in the statistic “cell physical IO bytes saved by columnar cache”. And finally, we see that we took advantage of some additional CELLMEMORY optimizations with the three “pcode” statistics listed at the end:

  1. cell scan CUs pcode aggregation pushdown – Number of CUs that benefitted from aggregation or group by pushdown.
  2. cell scan CUs pcode pred evaled – Number of CUs that were evaluated for predicate pushdown.
  3. cell scan rows pcode aggregated – Number of rows that passed the predicates and thus got aggregated.

The bottom line is that not only was CELLMEMORY used, but we also to see that In-Memory optimizations for filtering (WHERE clause filter predicates) and aggregation (MAX function) were also used during the Smart Scan.

You might be thinking that this seems like a lot of work, after all who wants to look at every SQL query and run it to find out if it is taking advantage of CELLMEMORY? In most cases I think you would be right. Since we are using statistics to evaluate usage, these statistics are also aggregated at the database level in the Automatic Workload Repository (AWR). You could just look at an AWR report for a specific time period, see that the CELLMEMORY statistics are being incremented, and recognize that you are taking advantage of CELLMEMORY. The main reason to look at individual SQL statements is if there is a specific performance concern, or as an exercise to further your learning about how the Exadata columnar cache is used.

 

[1] SmartScan: What’s New in 12.2 CELLMEMORY, Part 1

 

 

Andy Rivenes

Product Manager

Andy Rivenes is a Senior Principal Product Manager at Oracle Corporation and is a Product Manager for Database In-Memory. Andy has been working with Oracle products since 1992 and spent a large part of his career managing Oracle databases for both custom and E-Business Suite applications. Andy now spends his time helping customers implement Database In-Memory and providing information about Database In-Memory through blogs, conferences, and customer visits.


Previous Post

Oracle Database 23c Fast Ingest Enhancements

Andy Rivenes | 2 min read

Next Post


23c Deep Dive - In-Memory Vector Join Enhancements

Andy Rivenes | 14 min read