One of the joys of regexp is that you can write a pattern that is painfully expensive to match and offloading these to the cell can cause significant impact on other users and overall throughput (including heartbeat issues). If you have a user who is prone to writing bad regexp expressions you as DBA can prevent regexp (or any other operator) from being offloaded to the cells.
Let’s take a very simple example using a cut down version of TPC-H Query 16 and a NOT LIKE predicate:
SQL> explain plan for select p_brand, p_type, p_size
from part
where p_brand <> 'Brand#45'
and p_type not like 'MEDIUM POLISHED%'
and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
group by p_brand, p_type, p_size;
SQL> select * FROM TABLE(DBMS_XPLAN.DISPLAY);
|* 3 | TABLE ACCESS STORAGE FULL| PART | 29833 | 1048K| | 217 (2)| 00:00:01 | 1 | 8
------------------------------------------------------------------------------------------------------------
3 - storage(("P_SIZE"=3 OR "P_SIZE"=9 OR "P_SIZE"=14 OR "P_SIZE"=19
OR "P_SIZE"=23 OR "P_SIZE"=36 OR "P_SIZE"=45 OR "P_SIZE"=49)
AND "P_BRAND"<>'Brand#45' AND "P_TYPE" NOT LIKE 'MEDIUM POLISHED%')
Here we see all the predicates get offloaded as expected. So, for example, to stop NOT LIKE being offloaded we would need to find the operator in v$sqlfn_metadata
SQL> column descr format a18
SQL> select func_id, descr, offloadable from v$sqlfn_metadata where descr like '%LIKE%';
FUNC_ID DESCR OFF
---------- ------------------ ---
26 LIKE YES
27 NOT LIKE YES
99 LIKE NO
120 LIKE YES
121 NOT LIKE YES
...
524 REGEXP_LIKE YES
525 NOT REGEXP_LIKE YES
537 REGEXP_LIKE YES
538 NOT REGEXP_LIKE YES
we can ignore all but the two basic LIKE operators in this case, so to disable the offload of our LIKE predicates we use:
FUNC_ID DESCR OFF
---------- ------------------ ---
26 LIKE YES
27 NOT LIKE YES
99 LIKE NO
120 LIKE YES
121 NOT LIKE YES
...
524 REGEXP_LIKE YES
525 NOT REGEXP_LIKE YES
537 REGEXP_LIKE YES
538 NOT REGEXP_LIKE YES
we can ignore all but the two basic LIKE operators in this case, so to disable the offload of our LIKE predicates we use:
SQL> alter session set cell_offload_parameters="OPT_DISABLED={26,27};";
and we see this reflected in the offloadable column in v$sqlfn_metadata.
SQL> select func_id, descr, offloadable from v$sqlfn_metadata where descr like '%LIKE%';
FUNC_ID DESCR OFF
---------- ------------------ ---
26 LIKE NO
27 NOT LIKE NO
99 LIKE NO
120 LIKE YES
121 NOT LIKE YES
To re-enable them you would use:
SQL> alter session set cell_offload_parameters="OPT_DISABLED={};";
One thing to note about this param is that it doesn’t work like events (whose settings are additive), here it replaces the previous value and so every operator you want disabled has to be included in the same alter session (and the param is limited to 255 maximum characters limiting the number of operators that can be disabled). With the offload of LIKE and NOT LIKE disabled we can see the impact on the plan:
SQL> explain plan for select p_brand, p_type, p_size
from part
where p_brand <> 'Brand#45'
and p_type not like 'MEDIUM POLISHED%'
and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
group by p_brand, p_type, p_size;
SQL> select * FROM TABLE(DBMS_XPLAN.DISPLAY);
|* 3 | TABLE ACCESS STORAGE FULL| PART | 29833 | 1048K| | 217 (2)| 00:00:01 | 1 | 8
------------------------------------------------------------------------------------------------------------
3 - storage(("P_SIZE"=3 OR "P_SIZE"=9 OR "P_SIZE"=14 OR "P_SIZE"=19 OR "P_SIZE"=23
OR "P_SIZE"=36 OR "P_SIZE"=45 OR "P_SIZE"=49) AND "P_BRAND"<>'Brand#45')
and the NOT LIKE is no longer in the storage filter. Now lets say that you as DBA are faced with a more complex problem and want to halt all complex processing on the cells temporarily. There is a parameter that will disable everything except the simple comparison operators and NULL checks:
SQL> alter session set "_cell_offload_complex_processing"=FALSE;
Now lets see what happens:
SQL> explain plan for select p_brand, p_type, p_size
from part
where p_brand <> 'Brand#45'
and p_type not like 'MEDIUM POLISHED%'
and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
group by p_brand, p_type, p_size;
SQL> select * FROM TABLE(DBMS_XPLAN.DISPLAY);
|* 3 | TABLE ACCESS STORAGE FULL| PART | 29833 | 1048K| | 217 (2)| 00:00:01 | 1 | 8
------------------------------------------------------------------------------------------------------------
3 - filter(("P_SIZE"=3 OR "P_SIZE"=9 OR "P_SIZE"=14 OR "P_SIZE"=19 OR "P_SIZE"=23
OR "P_SIZE"=36 OR "P_SIZE"=45 OR "P_SIZE"=49) AND "P_BRAND"<>'Brand#45'
AND "P_TYPE" NOT LIKE 'MEDIUM POLISHED%')
Well we got no storage predicates at all and we didn’t expect that because we had one simple predicate namely p_brand != 'Brand#45' and the IN predicate had been rewritten to a series of OR’ed comparisons so what happened? This parameter only permits simple predicates that are linked by AND’s and can be attached directly to one column. Disjuncts are not pushable so they are normally evaluated by an eva tree or by pcode neither of which are sent to the cell with this parameter set to FALSE. So why wasn’t our one simple predicate offloaded. Well, note where it is in the explain plan. It comes after the rewritten the IN and since the predicates are sorted by the optimizer on effectiveness we stop looking as soon as we see one that can’t be offloaded. Let’s remove the IN and see what happens:
SQL> explain plan for select p_brand, p_type, p_size
from part
where p_brand <> 'Brand#45'
and p_type not like 'MEDIUM POLISHED%';
|* 2 | TABLE ACCESS STORAGE FULL| PART | 190K| 6686K| 217 (2)| 00:00:01 | 1 | 8 |
---------------------------------------------------------------------------------------------------
2 - storage("P_BRAND"<>'Brand#45')
filter("P_BRAND"<>'Brand#45' AND "P_TYPE" NOT LIKE 'MEDIUM POLISHED%')
as expected the simple predicate is now offloaded. If you look at v$sqlfn_metadata you’ll see this param is reflected in the offloadable column:
SQL> select func_id, descr, offloadable from v$sqlfn_metadata where descr like '%LIKE%';
FUNC_ID DESCR OFF
---------- ------------------ ---
26 LIKE NO
27 NOT LIKE NO
99 LIKE NO
120 LIKE NO
...
121 NOT LIKE NO
524 REGEXP_LIKE NO
525 NOT REGEXP_LIKE NO
537 REGEXP_LIKE NO
538 NOT REGEXP_LIKE NO
I hope you never need any of this in real life but it’s good to have it in the toolbag.