REGEXP_LIKE performance regression after upgrading from 12cR1 to 18c

Hi all,

I am facing a performance regression after upgrading from 12cR1 to 18c, and I am looking for help to better diagnose this situation. CPU usage grows abnormally when extensively using REGEXP_LIKE.

I reproduced the problem running

SELECT COUNT(*) FROM MY_USER_NAME.HOST00006695623_TESTCASE  WHERE REGEXP_LIKE()... OR REGEXP_LIKE()... OR REGEXP_LIKE()... OR REGEXP_LIKE()... OR REGEXP_LIKE()... OR ... ; in order to reproduce slowness from REGEXP_LIKE.

I traced such testcase and I noticed quite different outputs from tkprof:

on 18c, according to tkprof we are waiting 22'' to FETCH data and the excution plan says

TABLE ACCESS FULL HOST00006695623_TESTCASE (cr=40 pr=0 pw=0 time=22026024 us starts=1 cost=57 *size=1028790 card=4970*)

^^^^^^^^^^^^^^^^^^^^^^

^^^^^^^^^^^^^^^^

18c tkprof output and table checks:

SQL> select banner_full from v$version ;

BANNER_FULL

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0

SQL>
SQL> SELECT COUNT(*) FROM MY_USER_NAME.HOST00006695623_TESTCASE ; COUNT(*)

SQL>
SQL> SELECT BYTES/1024 FROM DBA_SEGMENTS WHERE OWNER = 'MY_USER_NAME' AND SEGMENT_NAME = 'HOST00006695623_TESTCASE' ; BYTES/1024

SQL>

############################################################################


call     count       cpu    elapsed       disk      query    current

Parse        1      0.09       0.09          0          0
0           0
Execute      1      0.00       0.00          0          0
0           0
Fetch        2     21.83      22.02          0         40
0           1

total        4     21.92      22.11          0         40
0           1   <== 21.11, all on Fetch.

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 102 (MY_USER_NAME)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ----------

        1 1 1 SORT AGGREGATE (cr=40 pr=0 pw=0 time=22026034 us starts=1)

        0 0 0 TABLE ACCESS FULL HOST00006695623_TESTCASE (cr=40 pr=0 pw=0 time=22026024 us starts=1 cost=57 size=1028790 card=4970)

12c table checks and tkprof output:

SQL> select banner from v$version ;

BANNER

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production NLSRTL Version 12.1.0.2.0 - Production

SQL>
SQL> SELECT COUNT(*) FROM MY_USER_NAME.HOST00006695623_TESTCASE ; COUNT(*)

SQL>
SQL> SELECT BYTES/1024 FROM DBA_SEGMENTS WHERE OWNER = 'MY_USER_NAME' AND SEGMENT_NAME = 'HOST00006695623_TESTCASE' ; BYTES/1024

SQL> call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0
0           0
Execute      1      0.00       0.00          0          0
0           0
Fetch        2      9.24       9.31          0         41
0           1

total        4      9.24       9.31          0         41
0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 81 (MY_USER_NAME)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ----------

        1 1 1 SORT AGGREGATE (cr=41 pr=0 pw=0 time=9316233 us)

        0 0 0 TABLE ACCESS FULL HOST00006695623_TESTCASE (cr=41 pr=0 pw=0 time=9316225 us cost=13 size=2002 card=1)

Il contenuto e le informazioni di questo messaggio di posta elettronica sono riservate, confidenziali e non vincolanti nè impegnative per Cedacri s.p.a., ne è vietata pertanto la diffusione o divulgazione in qualunque modo eseguita. Qualora Lei non fosse la persona a cui il presente messaggio è destinato La invitiamo ad eliminarlo e a non leggerlo, dandocene gentilmente comunicazione. The content, informations and any attachments of this e-mail are classified, confidential and not binding neither impegnative for Cedacri S.P.A., the spread or spreading in any executed way is prohibited therefore. If you are not named recipient, please notify the sender immediately and do not disclose the contents to another person, use it for any purpose, or store or copy the information in any medium.

--
http://www.freelists.org/webpage/oracle-l