The OP asked the same question multiple times, and was told multiple times
that it was a client application problem.
If the problem is a client application problem then it is possible to infer
that it is not a "tweak the parameters" problem.
Of course, we do note that about 1% of the time that the OP reported was
for "SQL*Net more data from client". So there may be an opportunity for
tweaking the SDU_SIZE at one of both ends of the connection, and one report
shows 3 "more data from client" waits for each "message from client", so
that 1% could be eliminated if the negotiated SDU_SIZE were 4 times as
large.
It MIGHT actually be a bad idea to have that discussion and get that change
made. If you need to push to get a change made then pushing for something
that makes virtually no difference often leads to resistance to any further
change you want to suggest - especially if it means that someone else has
to do some work.
Here's a useful link on tweaking activity by adjusting the SDU_SIZE - it's
focused on data passing through a db_link, but it's an excellent
demonstration of the effects of the setting:
https://jonathanlewis.wordpress.com/2019/07/03/db-links/
Regards
Jonathan Lewis
On Sat, 25 Feb 2023 at 06:50, Pap <oracle.developer35@xxxxxxxxx> wrote:
Correct me if wrong here. I think the OP asked multiple times here that if
the tweaking of network parameters like "sdu size" etc..will help. So I
think, this is what OP wanted a confirmation or assurance I. E, this issue
is definitely neither in the database nor in the network pipe connecting
database and informatica. So tweaking these said network settings won't
help.
And thus now, he/she can reach out to etl admins/teams asking the issue is
definitely in the etl tool/code layer itself, how it process the rows and
submit to database. So concentrate on that part of the application only.
On Sat, 25 Feb, 2023, 11:42 am kyle Hailey, <kylelf@xxxxxxxxx> wrote:
Enjoying this discussion. The SQL*Net waits sound like "network" waits
but almost never are, so it's great seeing this discussion and back and
forth.
On Fri, Feb 24, 2023 at 9:43 PM Lok P <loknath.73@xxxxxxxxx> wrote:
Thank you Mladen. Actually if i remember correctly, in past i have seen
some discussions in which waits like ' SQL*Net message from client' and '
SQL*Net more data from client" waits were actually attributed to
server/client network setups like sdu_size, send/receive_buffer_size , TDU
etc. So I wanted to know from experts here , if by any chance , it can be
the network which we should look at here by doing some oracle net trace
etc. Or the problem 100% lies in how the informatica tool is processing the
records here? And I agree in later cases we need to engage an informatica
admin to look into and utilize tracing facilities if any available in the
informatica tool/process itself.
Event waited on Times Max. Wait Total
Waited
---------------------------------------- Waited ----------
------------
SQL*Net message from client 2305 0.62
108.36
SQL*Net more data from client 6911 0.07
1.08
On Sat, Feb 25, 2023 at 8:32 AM Mladen Gogala <gogala.mladen@xxxxxxxxx>
wrote:
On 2/24/23 02:24, Nenad Noveljic wrote:
I've seen this behaviour with Informatica. The reason were the
workflows where the tool was unnecessarily grabbing large quantities of
data from the database and joining them on the Informatica server. In the
Informatica vernacular the joins are called "look-ups" and they require
sorting the selected data. As this was very intensive, the Oracle session
was idly waiting during that time. I worked together with the Informatica
specialist to select only a necessary subset of data and join them in the
database, which reduced the processing from hours to less than a minute.
So we're debugging Informatica now? On this list? Yes, "waiting for
more data from client" is a clear indication that maybe the application
encountering the event should be checked. That application is Informatica.
And that's the depth that I would go to on this list. If Informatica
doesn't do what you want, write a Python script and make sure it does what
you need with the speed you need. I will gladly discuss database aspects of
the application but not the programming aspects of the script. You should
trace Informatica and see where the time is spent. However, I am not sure
that we should discuss the results on this group.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217https://dbwhisperer.wordpress.com
