Finding cause and fix for Idle wait event
Date: Fri, 24 Feb 2023 01:40:36 +0530
Message-ID: <CAKna9Vax57s0FLP0BkKhg5YAra9z9f-JAZWZNUdwmRiADeN3Nw_at_mail.gmail.com>
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,:29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42,
:15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28,
: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-lReceived on Thu Feb 23 2023 - 21:10:36 CET