Re: Finding cause and fix for Idle wait event

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