Re: Can Bind variables be an issue with respect to network transfer?

Hi Sayan,

that stuff was already optimized extensively. BTW:_Version 12.2.

Regards

Lothar

Am 14.10.2020 um 11:26 schrieb Sayan Malakshinov:
> Hi Lothar,
>
> 4700 bind variables look really pretty high. What is your
> default_sdu_size? Have you tried to increase it and compare timing?
> https://docs.oracle.com/database/121/NETAG/performance.htm#NETAG1476
>
>
> On Wed, Oct 14, 2020 at 12:04 PM Lothar Flatz <l.flatz_at_bluewin.ch
> <mailto:l.flatz_at_bluewin.ch>> wrote:
>
> Hi,
>
> I investigate a software that gets single rows in a loop.
> In this case it is 2400 iterations each retrieving one or no row.
> Access
> is quick, via primary key index but the execution time is
> suspiciously high.
> My investigation shows in the runtime stats that the statement is
> executed on the DB in a fraction of the time.
> The total run time for the statement is 8 minutes, the total database
> execution time for 2400 executions is about 10 seconds.
> Did iterative stack on the oracle process and it show that the
> time was
> spend on network transfer.
> But the question still remains why network transfer is done in the
> execution phase of a statement.
> I guess the reason could be bind variables since there are over 4700
> bind variables and the binding is done in the execution phase.
> I wonder if that idea is reasonable. Even if there are many bind
> variables I wonder if it can make up to that amount of time.
> The bind variables are all rather short.
> ...
> That seems to be sending a message. What kind of Message could
> that be?
> An acknowledge?
>
> Regards
>
> Lothar
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org

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