Re: Insert query slowness

A couple of thoughts:
a) Andy's comment about the supplemental logging bug 32992004 may be the most significant thing - it's possible that when supplemental logging is enabled your array processing effectively turns into single-row processing when it reaches the database. The nature of the data also makes a difference to what supplemental logging will have to do - you may be generating a lot more undo and redo because of it and that might be slowing you down. Supplemental logging is one of the features that disables "private redo / in-memory undo", so if you're currently committing every 60 or 70 rows then you may have been working completely (or mostly) in private in 11g, and will have switched to competing for the public redo thread in 19c.

b) You've mentioned SQL*Net more data to client - that could only happen if the database were sending more data to the Informatica than could fit into a single SQL*Net packet - but if you're just doing a basic INSERT VALUES() there shouldn't be any significant volume of data returned to Informatica, so has there been a change in the Informatica configuration that makes it add a RETURNING clause to the insert? You did say, though, that the extra time was CPU time, so this wait might be irrelevant; as Lothar said, checking SDU_SIZE settings may help anyway.

c) Since you've said the extra time is CPU that means Oracle must be doing some "extra" work with your insert. Do you have any reports of session activity stats (v$sesstat) for the 11g system that you can compare with the 19c stats. If (for example) Informatica processed a job lot of 100,000 rows in both versions could you find ANY statistic name that had changed by approximately 100,000 (or a multiple of 100,000) between the two versions.

Regards
Jonathan Lewis

P.S. I didn't see a message from PAP coming through - what did it say?

On Wed, 2 Nov 2022 at 06:46, yudhi s <learnerdatabase99_at_gmail.com> wrote:

> Thank you Lothar and Andy. The details pap posted is exactly matching our
> issue. We also see during checking the v$sesion at runtime of the insert
> query, the event it logs as 'sql * net more data to client' . And can this
> point to some network config issue/change happened as part of grid patch as
> because nothing has changed from application point. And in that case if
> changing SDU size is an option?
>
>

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