Re: Performance Diff between 12 and 19

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 13 Mar 2020 10:30:10 +0000
Message-ID: <LNXP265MB1562EC58361E1561FBE610B4A5FA0_at_LNXP265MB1562.GBRP265.PROD.OUTLOOK.COM>

Clay,

I wrote up a quick note to describe and demonstrate the problem. Someone left a comment suggesting I set audit_trail=none (I had the default audit_trail=db).

With audit_trail=none the recursive SQL disappears and performance is on a par with 12.2

Regards
Jonathan Lewis

---

From: Clay Jackson (cjackson) <Clay.Jackson_at_quest.com> Sent: 12 March 2020 17:41
To: Jonathan Lewis; oracle list
Subject: RE: Performance Diff between 12 and 19

Thanks, Jonathan - not sure how I missed that difference. Anyway, will start running that down.

Clay Jackson
Database Solutions Sales Engineer
clay.jackson_at_quest.com
office 949-754-1203 mobile 425-802-9603

-----Original Message-----
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Jonathan Lewis Sent: Thursday, March 12, 2020 10:35 AM
To: oracle list <oracle-l_at_freelists.org> Subject: Re: Performance Diff between 12 and 19

CAUTION: This email originated from outside of the organization. Do not follow guidance, click links, or open attachments unless you recognize the sender and know the content is safe.

I got a significant difference in workload, probably driven by 6,000 executions in 19.3 of:

select count(1)
from
 sys.idnseq$ where seqobj# = :1

For the 1,000 in a loop I have mechanism that can call a simple script from SQL*Plus 1,000 times. The effects may vary if you have a PL/SQL loop, or (e.g.) a java loop.

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Clay Jackson (cjackson) <Clay.Jackson_at_quest.com> Sent: 12 March 2020 16:31
To: oracle list
Subject: Performance Diff between 12 and 19

Good day!

We're doing some testing of Oracle 19C for our products, and working with a customer who saw some significant differences with performance of sequences between Oracle 12.2.0 and 19.3.0. We've looked at plans (identical) and trace files (no differences other than expected 12 vs 19). Before we open an SR, I was wondering if anyone here has seen similar issues or can give us an idea of where to start debugging.

I know this is "suboptimal" in terms of caching and multiple commits - but, we're looking at "legacy code" that cannot be easily changed.

Here's simple test case that runs anywhere from 20% to 70% slower in similar (RHEL 7.3, X86) hardware. In fact, in one case, the 12c hardware had fewer cores (12 vs 32) and less ram (8G vs 32G) than the 19c environment.

create table test_tab1 (

   id number primary key,
   c1 varchar2(1000)
);

create table test_tab2 (

   id number,
   t1_id number,
   c2 varchar2(1000),
   constraint test_tab2_fk foreign key (t1_id) references test_tab1 (id) on delete cascade ); create table test_tab1 (    id number primary key,
   c1 varchar2(1000)
);

create table test_tab2 (

   id number,
   t1_id number,
   c2 varchar2(1000),
   constraint test_tab2_fk foreign key (t1_id) references test_tab1 (id) on delete cascade );

Then run 10000 of these transactions in a loop: insert into test_tab1 values (test_seq1.nextval,'hello'); insert into test_tab2 values (test_seq2.nextval, test_seq1.currval, 'byebye'); commit;

Clay Jackson

--
https://nam05.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l&data=02%7C01%7Cclay.jackson%40quest.com%7Ce55b760ef6574321db4e08d7c6abc74a%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C637196313388027389&sdata=S0ekInboxDmYH%2BRgiaNUWUTk8xTw9qoxcAafQTgJFFc%3D&reserved=0


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

Received on Fri Mar 13 2020 - 11:30:10 CET