Re: Finding cause and fix for Idle wait event

You may try to check with informatica admin for the cpu or memory utilization trends.

On Fri, 24 Feb, 2023, 10:19 am Lok P, <loknath.73_at_gmail.com> wrote:

> Thank you Maris. So I think it means, say the etl was doing a certain
> amount of work in x time and now it's doing the same work in a lot more
> time and thus these high idle/"SQL * net messages from client waits".
> Correct me if I'm wrong. Can we rule out the network settings like sdu size
> or tdu etc to fix this issue here? Or is there any sort of oracle net
> tracing we can do to dig more into this issue, wanting to
> explore all possible options at hand?
>
> As we talked to etl team, they said its cached lookup which is happening
> on ~200million rows and no transformation as such is happening in the ETL
> code during this insert. We asked to check the cache lookup performance
> trend over the past few days but they said no such instrumentation
> available to check that trend.
>
>
>
> On Fri, 24 Feb, 2023, 2:13 am Maris Elsins, <elmaris_at_gmail.com> wrote:
>
>> Hey,
>>
>> Did you already connect to your client machine where Informatica
>> is running, and check if it's doing fine?
>> I wouldn't be surprised if there was something "stuck" using lots of CPU
>> or creating some other bottleneck, which would be slowing down the ETL
>> process' ability to push the data to the DB a lot.
>>
>> ---
>> Māris Elsiņš
>>
>>
>>
>>
>> On Thu, Feb 23, 2023 at 10:11 PM Lok P <loknath.73_at_gmail.com> wrote:
>>
>>> Hello Friends, It's a 19c database on exadata machine. We have a simple
>>> insert as below executed from an ETL tool(Informatica). Suddenly the
>>> application team complained of slowness in that INSERT query. It used to
>>> run for 2-3hrs and now started running for 7-8hrs. While checking the query
>>> during run time in v$session, we see the query is getting inactive
>>> frequently and the event is showing as "SQL*Net message from client".
>>> However the team is saying no changes have been made to the ETL code. And
>>> it used to execute in similar fashion. Also no changes have been made in DB
>>> side or in network too.
>>>
>>> Looking more into the historical execution from dba_hist_sqlstat , we
>>> saw the elapsed_time of the query remains the same since the last couple of
>>> months i.e ~5ms/execution. And also the rows_processed/executions is
>>> showing consistently ~110 which means it's a batched insert getting
>>> executed from the etl tool and batch size also remains the same.
>>>
>>> After monitoring it for ~5minutes duration, we see it inserted
>>> ~1.6million rows with ~14829 executions and the total elapsed_time was
>>> ~1.3minutes which means, rest of the time(~3.7minutes) it was doing
>>> something at client side only. I collected the non zero stats difference
>>> from gv$sesstat for the ~5minutes run duration of the insert query and
>>> published it below gist. I can't figure out anything odd from these stats.
>>>
>>> https://gist.github.com/oraclelearner/0bcab0a80c53a66b999b4d2a696d2974
>>>
>>> Is there any way we can debug more into this "SQL*Net message from
>>> client" , wait through any trace etc, to see what exactly it's doing in the
>>> network in the client? And we see nothing is mentioned in the sqlnet.ora so
>>> it's the default sdu_size it's using in DB side, so is there any chance of
>>> by tweaking the sdu_size, it will help better data packet transfer and thus
>>> we will get better performance? Or any other way out to speed up the Insert
>>> query performance?
>>>
>>> The avg_row_len is 222 and it's a daily partitioned table with 4 indexes
>>> on it.
>>>
>>> INSERT INTO TAB1(c1,c2..........c45)
>>> VALUES ( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14,
>>> :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28,
>>> :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42,
>>> :43, :44, :45)
>>>
>>> -------------------------------------------------
>>> | Id | Operation | Name | Cost |
>>> -------------------------------------------------
>>> | 0 | INSERT STATEMENT | | 1 |
>>> | 1 | LOAD TABLE CONVENTIONAL | | |
>>> -------------------------------------------------
>>>
>>> Query Block Name / Object Alias (identified by operation id):
>>> -------------------------------------------------------------
>>>
>>> 1 - INS$1
>>>
>>> Note
>>> -----
>>> - cpu costing is off (consider enabling it)
>>>
>>

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