Re: Finding cause and fix for Idle wait event

Stefan,

The reason you shouldn't ignore the "more data" waits completely is that they're telling you that the problem isn't the network.

If you had random variation in the "message" AND "more data" then you could point the finger at the network - but since the 3 "more data" are always very fast and the "message" are always slow then the gap is the application.

Regards
Jonathan Lewis

On Sat, 25 Feb 2023 at 11:58, Stefan Koehler <contact_at_soocs.de> wrote:

> 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
>
>
>

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