Re: Finding cause and fix for Idle wait event

Hello Lok,
please let me just add my 2 cents to this discussion.

I am ignoring the "SQL*Net more data from client" wait for now as the total wait time is ignorable. However "SQL*Net message from client" can be the application OR the network - you can't tell by just looking at this wait.

I am quoting Tanel Poder for a more detailed explanation ( https://tanelpoder.com/2008/02/10/sqlnet-message-to-client-vs-sqlnet-more-data-to-client/ ): "Thanks to that, all of the time a TCP packet spent “flying” towards the client is actually accounted in SQL*Net message from client wait statistic. The problem here is though, that we don’t know how much of this time was spent on the wire and how much of it was application think time."

You can approach this unsureness by looking at the wait times itself (e.g. like every single "SQL*Net message from client" wait that takes longer than 1 second is probably application think/work time and everything below 1 second is probably the network) - tools like the Method-R profiler by Cary Millsap are doing this work for you (for every single wait) and are showing you the final result in a HTML report.

Anyway if you want to be absolutely sure if it is the application or the network (that causes this high "SQL*Net message from client" wait time) you can TCPDUMP the network traffic (on application side) and then process this TCPDUMP file with the free tool STADO ( http://blog.ora-600.pl/2020/01/22/when-awr-is-not-enough-a-k-a-why-your-app-is-not-cloud-ready-smartdb/ ) by Kamil Stawiarski.

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> Lok P <loknath.73_at_gmail.com> hat am 25.02.2023 07:58 CET geschrieben:
>
>
> Thank you so much Pap and Kyle.
>
> Exactly what I wanted to know. Thank you for the clarification and apologize for my confusion. Actually I also shared the trace file in the post earlier just to be sure we are going in the right direction. And that's because I may saw in past in some blogs , this waits can be network related.
>
> However now as you mentioned, it's clear that the issue is not in the network but in the app layer, I will follow up with the app/informatica admin/team. Thank you again for all the guidance.

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