Re: REGEXP_LIKE performance regression after upgrading from 12cR1 to 18c

It would appear that there is a similar bug related to this very issue (even the full tablescan aspect):

        Patch 27115422: PERFORMANCE ISSUE ON 12.2.0.1 DB WHEN USING REGEXP_LIKE—

The issue does not manifest itself in 12.1.0.2 but in higher versions only. It appears that there is a patch for 18, may want to give that a try.

Thanks,

Shane Borden
sborden76_at_gmail.com

> On Apr 10, 2019, at 4:18 AM, Andrea Monti <ilsuonogiallo_at_gmail.com> wrote:
>
> 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)
> ^^^^^^^^^^^^^^^^^^^^^^
>
>
> while on 12cR1 we are waiting less than 10'' to fetch the same data and the execution plan contains:
>
> TABLE ACCESS FULL HOST00006695623_TESTCASE (cr=41 pr=0 pw=0 time=9316225 us cost=13 size=2002 card=1)
> ^^^^^^^^^^^^^^^^
>
> I am playing with a small, identical testcase on the same (poor) VirtualBox box: I swear that the table are identical by size, by structure and by number of rows: they both have 1000 rows only and are 384 kb big.
> However, I am unable to find out what is causing the differences in "size" and "card" shown by tkprof, and how can I have card=4970 when doing a FTS against 1000 rows.
>
> I'm sending a bigger tkprof extract at the bottom
>
> Thanks for every help.
> Andrea
>
>
>
> 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(*)
> ----------
> 1000
>
> SQL>
> SQL> SELECT BYTES/1024 FROM DBA_SEGMENTS WHERE OWNER = 'MY_USER_NAME' AND SEGMENT_NAME = 'HOST00006695623_TESTCASE' ;
>
> BYTES/1024
> ----------
> 384
>
> SQL>
>
> ############################################################################
>
> call count cpu elapsed disk query current rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> 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(*)
> ----------
> 1000
>
> SQL>
> SQL> SELECT BYTES/1024 FROM DBA_SEGMENTS WHERE OWNER = 'MY_USER_NAME' AND SEGMENT_NAME = 'HOST00006695623_TESTCASE' ;
>
> BYTES/1024
> ----------
> 384
>
> 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