RS-700 [Offload server hang detected. It will be terminated]

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
isSystemGroup: true
package: cellofl-
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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s