Re: Finding cause and fix for Idle wait event

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_at_gmail.com> 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_at_gmail.com> 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_at_gmail.com> 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_at_gmail.com>
>>> 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
>>>>
>>>>

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