"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@xxxxxxxxx> 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@xxxxxxxxx>
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 likeInformatica can also start spilling data to files.
200million and joining can be resource intensive.
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.
