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.
Pingback: JSON support in Exadata 12.1.2.1.0 and later « Martins Blog