Re: Finding cause and fix for Idle wait event

Thank you so much Pap and Kyle.

Exactly what I wanted to know. Thank you for the clarification and apologize for my confusion. Actually I also shared the trace file in the post earlier just to be sure we are going in the right direction. And that's because I may saw in past in some blogs , this waits can be network related.

However now as you mentioned, it's clear that the issue is not in the network but in the app layer, I will follow up with the app/informatica admin/team. Thank you again for all the guidance.

On Sat, 25 Feb, 2023, 12:19 pm 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