Re: Finding cause and fix for Idle wait event

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