Re: Finding cause and fix for Idle wait event

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'
        );

> 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