Wrong Results when SQL offloaded with Smart Scan - case study

Running a database on an Exadata Database Machine can occasionally reveal unexpected challenges, such as an offloaded SQL query returning incorrect results. This kind of behavior, as you can imagine, can have severe consequences — especially when the query is part of a critical reporting system or serves as the single source of truth for your organization.
The hard part at first place is to get aware that the result is innacurate, but that is another part of the story, not covered here.
In the case I’m about to describe, the issue was brought to us by the developers, who were confident that a specific query was returning incorrect results in the production database. The problem surfaced when querying a particular table in the application schema:

SQL> desc F.CUSTOM_LOG;
 Name						           Null?	Type
 ------------------------------------- -------- -----------------
 ID_OP      							        NUMBER
 TABLES               							VARCHAR2(30)
 COL                							VARCHAR2(30)
 OLD_VAL            							CLOB
 NEW_VAL            							CLOB
 OP                 							CHAR(1)
 IDENTITFIER        							CLOB
 USER               							VARCHAR2(30)
 TIME           								TIMESTAMP(6)

with:

SQL> select count(*) from F.CUSTOM_LOG where TABLES = 'zobo' and IDENTITFIER like 'ID=899691%' and COL = 'CANCEL_DATE';
  COUNT(*)
----------
     21312

but when compering above result with simillar SQL, result is different:

SQL> select count(*) from F.CUSTOM_LOG where TABLES = 'zobo' and to_char(IDENTITFIER) like 'ID=899691%' and COL = 'CANCEL_DATE';
  COUNT(*)
----------
        3

Interestingly, the query results on the TEST database were consistent and correct in both cases, returning the expected value of 3.
Both the PROD and TEST databases run on the same Exadata X10M platform, with identical database versions and patch levels. Despite this, the TEST environment consistently provided correct results, while PROD returned incorrect ones. It’s worth noting that both databases are running on the legacy 12.2 release, and PROD was recently upgraded from 12.1 to 12.2 following successful testing in the TEST environment.

To begin the investigation, my first step was to compare the Execution Plans for the queries on PROD and TEST. I aimed to analyze the optimizer’s behavior, the access paths being used, and identify any differences that might exist.

PROD:

SQL> select count(*) from F.CUSTOM_LOG where TABLES = 'zobo' and IDENTITFIER like 'ID=899691%' and COL = 'CANCEL_DATE';
  COUNT(*)
----------
     21312

Execution Plan
----------------------------------------------------------
Plan hash value: 3967806800
---------------------------------------------------------------------------------------------
| Id  | Operation		            | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	        |		    |	  1 |	212 |	142K (11)| 00:00:06 |
|   1 |  SORT AGGREGATE 	        |		    |	  1 |	212 |		     |	        |
|*  2 |   TABLE ACCESS STORAGE FULL | CUSTOM_LOG |  3478 |	720K|	142K (11)| 00:00:06 |
---------------------------------------------------------------------------------------------

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

   2 - storage("COL"='CANCEL_DATE' AND "TABLES"='zobo' AND "IDENTITFIER" LIKE 'ID=899691%')
       filter("COL"='CANCEL_DATE' AND "TABLES"='zobo' AND "IDENTITFIER" LIKE 'ID=899691%')

TEST:

SQL> select count(*) from F_TEST.CUSTOM_LOG where TABLES = 'zobo' and IDENTITFIER like 'ID=899691%' and COL = 'CANCEL_DATE';
  COUNT(*)
----------
     3

Execution Plan
----------------------------------------------------------
Plan hash value: 3967806800
---------------------------------------------------------------------------------------------
| Id  | Operation		            | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	        |		    |	  1 |	241 |	337K (11)| 00:00:14 |
|   1 |  SORT AGGREGATE 	        |		    |	  1 |	241 |		     |	        |
|*  2 |   TABLE ACCESS STORAGE FULL | CUSTOM_LOG |  4289 |  1009K|	337K (11)| 00:00:14 |
---------------------------------------------------------------------------------------------

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

   2 - storage("COL"='CANCEL_DATE' AND "TABLES"='zobo' AND "IDENTITFIER" LIKE 'ID=899691%')
       filter("COL"='CANCEL_DATE' AND "TABLES"='zobo' AND "IDENTITFIER" LIKE 'ID=899691%')

The Execution Plans on both PROD and TEST appeared nearly identical. Key indicators for offloading the query to the storage layer via Smart Scan were present, such as TABLE ACCESS STORAGE FULL and the filtering offload in Predicate Information: storage("COL"='CANCEL_DATE' AND "TABLES"='zobo' AND "IDENTITFIER" LIKE 'ID=899691%').

A quick check of session statistics confirmed that the Offload indeed took place. So why offload on PROD missbehaved ?

Digging deeper into the issue, I came across this MOS note: “Exadata: How to Diagnose Smart Scan and Wrong Results” (Doc ID 1260804.1). This document provides a test script designed to troubleshoot wrong results scenarios with Smart Scan. The script executes the problematic SQL multiple times, each iteration toggling different Smart Scan mechanisms on and off. This approach can helps narrow down the scope of potential issues within the storage software “smart” layers. In this case I was able to confirm that when predicate filtering was not performed on storage layer: _kcfis_cell_passthru_enabled=TRUE ( kcfis = kernel cache file management intelligent storage_, _cell_passthru_enabled will not use Filter Processing Library on cell ) or filtering has been emulated within database by setting _rdbms_internal_fplib_enabled=TRUE results are ok:

'alter session set "_kcfis_cell_passthru_enabled"=TRUE;'
' : see if this is fplib-related'

Session altered.

  COUNT(*)
----------
         3

1 row selected.

Elapsed: 00:01:20.46

'alter session set "_serial_direct_read"=TRUE'
'enable serial direct read for fplib test'

'alter session set "_rdbms_internal_fplib_enabled"=TRUE;'
' : see if this happens with fplib emulation'

Session altered.

  COUNT(*)
----------
         3

1 row selected.

Elapsed: 00:00:27.71

By modifying the query to apply the to_char() function on the IDENTITFIER column, the DEV team achieved the same effect as the test script mentioned earlier — preventing the offloading of predicate filtering to the storage layer:

SQL> select count(*) from F.CUSTOM_LOG where TABLES = 'zobo' and to_char(IDENTITFIER) like 'ID=899691%' and COL = 'CANCEL_DATE';
  COUNT(*)
----------
     3

Execution Plan
----------------------------------------------------------
Plan hash value: 3967806800
---------------------------------------------------------------------------------------------
| Id  | Operation		            | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	        |		    |	  1 |	212 |	142K (11)| 00:00:06 |
|   1 |  SORT AGGREGATE 	        |		    |	  1 |	212 |		     |	        |
|*  2 |   TABLE ACCESS STORAGE FULL | CUSTOM_LOG |  3478 |	720K|	142K (11)| 00:00:06 |
---------------------------------------------------------------------------------------------

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

   2 - storage("COL"='CANCEL_DATE' AND "TABLES"='zobo')
       filter("COL"='CANCEL_DATE' AND "TABLES"='zobo' AND "IDENTITFIER" LIKE 'ID=899691%')

Execution Plan confirms that - Predicate Information section does not contain filter on IDENTIFIER in storage("COL"='CANCEL_DATE' AND "TABLES"='zobo').

So, why does predicate filtering for IDENTIFIER when offloaded to storage on PROD database generates wrong result but not on TEST, and why is the to_char() not offloaded at first place, as according to Exadata Storage Software documentation:
“In Oracle Exadata Storage Server 12.2.1.1.0, offload support has been extended for the following LOB operators: LENGTH, SUBSTR, INSTRM CONCAT, LPAD, RPAD, LTRIM, RTRIM, LOWER, UPPER, NLS_LOWER, NLS_UPPER, NVL, REPLACE, REGEXP_INSTR, TO_CHAR. Exadata smart scan offload evaluation is supported only on uncompressed inlined LOBs (less than 4 KB in size).”

Compression can be the key to understand the issue (as LOBs are IN_ROW stored). Let’s verify whether the object is compressed and, if so, identify the type of compression used. Checking both databases revealed the same outcome::

SQL> select table_name, compress_for from dba_tables where table_name='CUSTOM_LOG';

TABLE_NAME	    COMPRESS_FOR
------------    ---------------- 
CUSTOM_LOG		QUERY HIGH

Yes, HCC - Hybrid Columnar Compression with Warehouse compression is enabled for that table and this is the reason why to_char() predicate is not offloaded to storage and why the query results are correct on both databases when the offload does not occur. This answers one of the questions regarding the behavior of to_char().

However, a key question remains:
If the compression type is the same on TEST and PROD, why does the query return correct results on TEST without to_char() but fails on PROD when Smart Scan kicks in? Is everything truly identical between the two environments?

Digging more arround table compression, I discovered that the CUSTOM_LOG is compressed differently on TEST compered to PROD
Despite being configured for QUERY HIGH Warehouse compression, CUSTOM_LOG appears to be a table with frequent INSERT operations. Just a few days after PROD upgrade from 12.1 to 12.2 and fresh statistics gathering:

SQL> SELECT TABLE_NAME, INSERTS, UPDATES, DELETES, TIMESTAMP FROM DBA_TAB_MODIFICATIONS WHERE TABLE_NAME = 'CUSTOM_LOG' AND TABLE_OWNER = 'F';

TABLE_NAME		          INSERTS	 UPDATES    DELETES     TIMESTAMP
------------------------- ---------- ---------- ----------  ---------
CUSTOM_LOG		          159891	 0 	        0           09-DEC-24

Theory says:

• HCC is triggered when INSERTs are done via direct-path load or with array insert (from 12.2), but typical, simple INSERT will cause that database will place data in row format
• For HCC UPDATED data, database moves the updated rows to row format with reduced compression level using COMP_BLOCK format, type 64 (check Martins Bach post)

Thanks to the fact of having access to TEST database and inspired by aproach in this Martins Bach post I queried CUSTOM_LOG table to determine the exact compression types applied to the rows. It took a while to get the answer on TEST, but results were unexpected:

SQL> with comptypes as (
select rownum rn, dbms_compression.get_compression_type('F_TEST', 'CUSTOM_LOG', rowid) ctype
from F_TEST.CUSTOM_LOG
)
select count(*), ctype from comptypes group by ctype;

  COUNT(*)      CTYPE
---------- ----------
  35507960         64
  18642218          1 

Only two types of compression were detected in this table, and surprisingly, neither of them is Hybrid Columnar Compression (HCC):

• type 64 (former HCC, but now COMP_BLOCK)
• type 1 (No Compression)

To avoid querying the table in the same way on PROD, I opted for a different approach: by checking the FLASHCACHECONTENT on CELLs. Since the CUSTOM_LOG table is heavily accessed (hot data), it should reside in the Flash Cache.
To verify this on TEST, I needed the data_object_id for CUSTOM_LOG from DBA_OBJECTS to reference the objectNumber on the Exadata storage cells. Each cell needs to be checked for a complete picture, but for clarity, here is the output from a single cell (others were similar):

CellCLI> LIST FLASHCACHECONTENT WHERE objectNumber=5481235 detail;
	 cachedKeepSize:         0
	 cachedSize:             7322836992
	 cachedWriteSize:        5535588352
	 clusterName:
	 columnarCacheSize:      0
	 columnarKeepSize:       0
	 dbID:                   401830952
	 dbUniqueName:           TEST
	 hitCount:               135037
	 missCount:              95814
	 objectNumber:           5481235
	 tableSpaceNumber:       18

On TEST, the columnarCacheSize value for CUSTOM_LOG was 0, confirming that no data was cached in HCC columnar format — even though the table resides in Flash Cache.
How about PROD:

CellCLI> LIST FLASHCACHECONTENT WHERE objectNumber=5133442 detail;
	 cachedKeepSize:         0
	 cachedSize:             25897451520
	 cachedWriteSize:        3570089984
	 clusterName:
	 columnarCacheSize:      200933376
	 columnarKeepSize:       0
	 dbID:                   772460701
	 dbUniqueName:           PROD
	 hitCount:               3860427
	 missCount:              43421
	 objectNumber:           5133442
	 tableSpaceNumber:       47

It’s different. columnarCacheSize shows some values which means that on PROD CUSTOM_LOG in some parts is presenet in Flash Cache in HCC format.

This is our key difference

we’ve uncovered that:

• on TEST despite QUERY HIGH compression on table, due to DMLs, table doeas not exists in HCC format any more
• on PROD table is also using QUERY HIGH compression but some part of it exists in that format.

To complete the picture, we must highlight one critical fact::
HCC has been applied to a table containing LOB data, which Oracle explicitly does not recommend. According to the Oracle Hybrid Columnar Compression Guide:

“Hybrid Columnar Compression is designed for relational data, not for unstructured data in BLOBs (or CLOBs).”

The implications are severe:

Offloding quries on HCC objects that include inlined LOBs via Smart Scan to storage layer can produce incorrect results. What’s more concerning is that the database allows HCC compression to be applied to such tables without any warnings, potentially introducing unexpected behavior, as we’ve observed.