Re: Finding cause and fix for Idle wait event

"lookup cache is finished" means nothing to anyone who doesn't work with Informatica, and (based on previous experience) doesn't necessarily mean much to most people who do work with Informatica.

I think you said this process is inserting 200M rows. Does that mean that somehow informatica is reading 200M rows from somewhere and then doing some sort of lookup to translate some incoming values to outgoing values. Does that "lookup" operate as something like a nested loop, or does it operate as a hash join, or as a merge join. Where do the 200M rows go as there "looked-up" values are found?

Has Informatica spilled the rows to disc, and has some change in the lookup method meant that it now takes more time for informatica to find the next batch of rows. The time loss is entirely due to what Informatica is doing, and if you don't have the exact details of what it's doing and how it's doing it there's nothing you can do at the Oracle end of the system (which is just waiting for Informatica to supply data) to get rid of the waits. (You have to get the Informatica person to explain carefully how a "lookup" works, and maybe you can ask a few "what if" questions that might prompt him/her to come up with a scenario that the pair of you can agree is consistent with what Oracle is seeing.

Regards
Jonathan Lewis

On Fri, 24 Feb 2023 at 12:48, Lok P <loknath.73_at_gmail.com> wrote:

> Thank you Nenad. I just checked again with the ETL dev and saw there is
> actually lookup cache finished completely, before these batch INSERT
> started. But still why there is so much 'sql * net message from client'
> wait coming within each batch of INSERT submitted to the database? I keep
> on checking gv$session when the insert is running and seeing its active and
> then goes inactive with 'sql * net message from client' for 2-3 seconds.
>
> So in above scenario where no look up cache is happening but it's just
> the batch insert running from etl, will it be network related issue then?
> Also would it be good idea here to trace the session and serial#(like
> below) to get any packet transfer clue?
>
> begin
> dbms_monitor.session_trace_enable(
> session_id => &m_sid,
> serial_num => &m_serial,
> waits => true,
> bind => true,
> plan_stat => 'all_executions'
> );
> end;
> /
>
> begin
> dbms_monitor.session_trace_disable(
> session_id => &m_sid,
> serial_num => &m_serial
> );
> end;
> /
>
> On Fri, 24 Feb, 2023, 2:10 pm Nenad Noveljic, <nenad.noveljic_at_gmail.com>
> wrote:
>
>> I was under impression that look ups means just filters/read from the
>>> informatica cache which would be fast, but I agree sorting rows like
>>> 200million and joining can be resource intensive.
>>>
>> Informatica can also start spilling data to files.
>>
>> Is there any instrumentation available st informatica which shows how
>>> much time/resources does those cached lookups take for a workflow?
>>
>> Not that I'm aware of. I don't, however, know the product. But you could
>> measure CPU/memory/IO load to get an idea how intensive the processing is.
>>
>>
>>> And do you say, modifying sdu_size/tdu in client side or database is not
>>> going to make any difference in this case?
>>
>> Not if the primary cause is the Informatica process being busy with
>> data crunching.
>>
>>

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