In exadata cell server,RS process which is responsible to monitor other background process has detected a hang due to noticed bug in the kernel
Trace file reveal useful information about sql query which is the reason for the hang
IORM (0x6002d2948d18): dbid 1285798055 cdbid 0 instnum 2 cgid 12889 cgtyp 1 standby 0 reason 16640
sessid 775 sessser 3295 bgid 0 sqlid 1xa235hseiopr objnum 274209 asmfilenum 0
asmfincnum 0 tlbspcnum 43 asmgrpnum 0 caching 1 flag1 1 storclause 3
hardblkno 0 hardchk 0 hardftyp 0 hardblksz 0
I checked the offload group information of the particular cell node and found one running
CellCLI> list offloadgroup detail;
name: SYS_112331_180220
autoStart: true
comment:
isSystemGroup: true
package: cellofl-11.2.3.3.1_LINUX.X64_180220.1
runtimeState: running
I dig deeper and found a regex function RegexpLike() used in the where predicate clause of the sql query which is the main culprit causing noise
evarxnlik()+521 call lxkRegexpLike() 000000000 ? 6012BFA04263 ?
00000002E ? 60031FDA6FB0 ?
60031FDA6F30 ? 000000000 ?
lxkRegexpLike()+65 call lxregexec() 000000000 ? 6012BFA04263 ?
00000002E ? 000000000 ?
000000000 ? 000000000 ?
The sql query is quite complex
SELECT customer_id,first_name,last_name
FROM customers
WHERE NOT REGEXP_LIKE(first_name, '[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}', 'i');
There is a hidden parameter which has a heartbeat timeout threshold for cell offload
90 seconds is cell induced limit which is defined by the cell level parameter _cell_oflsrv_heartbeat_timeout_sec.
One of the solution is to disable Regular expression offloading at the DB level
Identify the function id of the regexplike() first
The function id details looks something like this but the below output is not exact function id of regexplike
SQL> select name,offloadable,datatype,func_id from v$sqlfn_metadata where name like '%REGEXP%';
NAME OFF DATATYPE FUNC_ID
------------------------------ --- -------- ----------
REGEXP_LIKE YES STRING 534
Disable only the particular function
SQL>alter system set cell_offload_parameters="OPT_DISABLED={534}" scope=both sid='*' ;
Another efficient solution would be to rewrite the query with different logic without using regex function
