Re: Finding cause and fix for Idle wait event

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