As part of the 12c release of the database and cellos Oracle introduced the ability to perform Smart Scans on inline LOBs. The enhancement is certainly for Secure Files only. And as always, if you would like to read up about Secure Files you can head over to Tim Hall’s website for the details.
To demonstrate the new behaviour I have used an 11.2.0.3 BP 22 database on Exadata 11.2.3.3.1 and 12.1.0.2.2 database on Exadata 12.1.2.1.0. The test case evolves around the following table:
CREATE TABLE loboffload ( id number primary key, comments clob) enable row movement tablespace users lob (comments) store as securefile ( enable storage in row );
This is the short version, Oracle fills in the blanks and converts the table DDL to
SQL> select dbms_metadata.get_ddl('TABLE','LOBOFFLOAD') from dual;
DBMS_METADATA.GET_DDL('TABLE','LOBOFFLOAD')
--------------------------------------------------------------------------------
CREATE TABLE "MARTIN"."LOBOFFLOAD"
( "ID" NUMBER,
"COMMENTS" CLOB,
PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
LOB ("COMMENTS") STORE AS SECUREFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ENABLE ROW
MOVEMENT
The idea is that I am using the comments lob column to simulate a common approach in application design where you define a free text field on a GUI screen for the user to provide additional information about the record to be stored in the database. The design is not very practical though.
The important information is in the section about the LOB. Here you find the request to store the LOB as a SECUREFILE in-line with the table. Out of line LOBs are not offloadable as of 12.1.2.1.0 as far as I know.
To feed some data into the table I copied added a bit of data and then used the insert … append technique to populate it:
alter session enable parallel dml;
-- with nods to Jonathan Lewis who published this in one of his presentations on generating data
insert /*+ append parallel(4) */ into loboffload
with v1 as (
SELECT rownum n FROM dual CONNECT BY level <= 10000
)
SELECT rownum id,
CASE
WHEN mod(rownum,100000) = 0 THEN 'THIS WAS EXCEPTIONAL'
WHEN mod(rownum,10000) = 0 THEN 'THIS WAS VERY GOOD'
WHEN mod(rownum,1000) = 0 THEN 'THIS WAS GOOD'
WHEN mod(rownum,100) = 0 THEN 'OK'
ELSE 'NO COMPLAINTS'
END
FROM v1,
v1
WHERE rownum <= 1e6;
create sequence loboffload_s cache 10000 start with 1000001;
insert /*+ append parallel(4) */ into loboffload
select /*+ parallel(4) */ loboffload_s.nextval, comments from loboffload;
I ended up with 16 million rows in the end. I used impdp over a network link to move the table to the other database.
Test with 11.2.0.3
The test in 11.2.0.3 is used to confirm that inline LOBs are not offloadable. Here is the query and result:
MARTIN@ora11203> select /*+ monitor noparallel full(t) */ count(*) from loboffload t where comments like '%GOOD%';
COUNT(*)
----------
15840
Elapsed: 00:01:33.48
MARTIN@ora11203> -- removing irrelevant statistics from the output
MARTIN@ora11203> @mystats stop t=1
==========================================================================================
MyStats report : 25-MAY-2015 02:33:24
==========================================================================================
------------------------------------------------------------------------------------------
1. Summary Timings
------------------------------------------------------------------------------------------
Type Statistic Name Value
------ ---------------------------------------------------------------- ----------------
TIMER snapshot interval (seconds) 95.28
TIMER CPU time used (seconds) 93.00
------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------
Type Statistic Name Value
------ ---------------------------------------------------------------- ----------------
STAT CPU used by this session 9,303
...
STAT DB time 9,353
...
STAT cell flash cache read hits 9,454
STAT cell physical IO interconnect bytes 1,233,436,672
...
STAT consistent gets 150,794
STAT consistent gets - examination 3
STAT consistent gets direct 150,558
STAT consistent gets from cache 236
STAT consistent gets from cache (fastpath) 231
STAT cursor authentications 3
...
STAT execute count 13
...
STAT logical read bytes from cache 14,393,344
STAT no work - consistent read gets 150,558
...
STAT parse count (hard) 1
STAT parse count (total) 13
STAT parse time cpu 1
STAT parse time elapsed 1
STAT physical read IO requests 9,459
STAT physical read bytes 1,233,436,672
STAT physical read requests optimized 9,454
STAT physical read total IO requests 9,459
STAT physical read total bytes 1,233,436,672
STAT physical read total bytes optimized 1,233,395,712
STAT physical read total multi block requests 9,255
STAT physical reads 150,566
STAT physical reads cache 8
STAT physical reads direct 150,558
...
STAT table scan blocks gotten 150,558
STAT table scan rows gotten 16,000,000
STAT table scans (direct read) 1
STAT table scans (long tables) 1
...
------------------------------------------------------------------------------------------
3. About
------------------------------------------------------------------------------------------
- MyStats v2.01 by Adrian Billington (http://www.oracle-developer.net)
- Based on the SNAP_MY_STATS utility by Jonathan Lewis
==========================================================================================
End of report
==========================================================================================
So none of the essential cell% events recorded except for Flash Cache read hits. Without the cell scans statistics incremented by 1 (for the table segment) you can conclude that no Smart Scan happened here.
Test on 12.1.0.2/12.1.2.1.0
The first result was not too surprising, as it is the documented behaviour. The second test should hopefully reveal some offloading.
MARTIN@ORA12c> select /*+ monitor noparallel full(t) */ count(*) from loboffload t where comments like '%GOOD%';
COUNT(*)
----------
15840
Elapsed: 00:00:01.65
MARTIN@ORA12c> @mystats stop t=1
==========================================================================================
MyStats report : 25-MAY-2015 02:29:46
==========================================================================================
------------------------------------------------------------------------------------------
1. Summary Timings
------------------------------------------------------------------------------------------
Type Statistic Name Value
------ ---------------------------------------------------------------- ----------------
TIMER snapshot interval (seconds) 14.61
TIMER CPU time used (seconds) 0.03
------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------
Type Statistic Name Value
------ ---------------------------------------------------------------- ----------------
STAT CPU used by this session 9
...
STAT cell IO uncompressed bytes 1,234,296,832
STAT cell blocks helped by minscn optimization 150,666
STAT cell blocks processed by cache layer 150,671
STAT cell blocks processed by data layer 150,671
STAT cell blocks processed by txn layer 150,671
STAT cell flash cache read hits 1,143
STAT cell num smartio automem buffer allocation attempts 1
STAT cell physical IO bytes eligible for predicate offload 1,234,296,832
STAT cell physical IO interconnect bytes 6,273,368
STAT cell physical IO interconnect bytes returned by smart scan 6,273,368
STAT cell scans 1
...
STAT consistent gets 151,053
STAT consistent gets direct 150,671
STAT consistent gets examination 3
STAT consistent gets examination (fastpath) 3
STAT consistent gets from cache 382
STAT consistent gets pin 379
STAT consistent gets pin (fastpath) 379
...
STAT parse count (total) 13
STAT physical read IO requests 1,205
STAT physical read bytes 1,234,296,832
STAT physical read requests optimized 1,143
STAT physical read total IO requests 1,205
STAT physical read total bytes 1,234,296,832
STAT physical read total bytes optimized 1,171,423,232
STAT physical read total multi block requests 1,189
STAT physical reads 150,671
STAT physical reads direct 150,671
...
STAT table scan blocks gotten 15,744
STAT table scan disk non-IMC rows gotten 1,670,692
STAT table scan rows gotten 1,670,692
STAT table scans (direct read) 1
STAT table scans (short tables) 1
...
------------------------------------------------------------------------------------------
3. About
------------------------------------------------------------------------------------------
- MyStats v2.01 by Adrian Billington (http://www.oracle-developer.net)
- Based on the SNAP_MY_STATS utility by Jonathan Lewis
==========================================================================================
End of report
==========================================================================================
Good news, what Oracle said is true. The same query that did not offload in 11.2.0.3 is now offloaded in 12.1.0.2 with Exadata 12.1.2.1.0. You find proof in the existence of all the cell% statistics, especially cell scans plus the … processed by … layer statistic.
Martins Blog 
Pingback: JSON support in Exadata 12.1.2.1.0 and later « Martins Blog